关于 MySQL 优化答应我别再拉跨了好么
创建索引
#给city表创建一个名为 idx_city_name 的索引类型为BTree CREATE INDEX idx_city_name ON city(city_name);
#查询city表的索引 SHOW INDEX FROM city;
#删除索引 DROP INDEX idx_city_name ON city;
#创建唯一索引，允许null值 ALTER TABLE city ADD UNIQUE idx_city_name(city_name);
视图：
存储过程：
触发器：
存储引擎
InnoDB：支持事务、支持行锁、支持外键约束（
RESTRICT、NO ACTION：限制在子表有关联记录的情况下， 父表不能更新；
CASCADE：父表在更新或者删除时，更新或者删除子表对应的记录
SET NULL：父表在更新或者删除的时候，子表的对应字段被SET NULL ）
MyISAM：不支持事务、支持表锁、不支持外键约束
索引的使用
查看服务器状态信息：SHOW GLOBAL STATUS LIKE 'Com_______';
show status like 'Innodb_rows_%';
定位低效率执行SQL：
慢查询日志：用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件
show processlist：show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
explain分析执行计划：
explain 之 id：控制表结构执行顺序。id相同由上而下执行，id不同，数值越大执行优先级越高 explain 之 select_type：SELECT 的类型。SIMPLE（简单查询）、PRIMARY（包含子查询的最外层查询）、SUBQUERY（在SELECT 或 WHERE 列表中包含了子查询）、DERIVED（在FROM 列表中包含的子查询，衍生表）、UNION（出现在UNION后的查询）、UNION RESULT（从UNION表获取结果的SELECT） explain 之 table：查询的数据是来源于哪一张表 explain 之 type：NULL（不访问任何表、索引）、system（表只有一行记录）、const（通过索引一次就找到）、eq_ref（唯一索引扫描）、ref（非唯一索引扫描）、range（范围查询）、index（遍历整个索引树）、all（全表扫描） explain 之 key：possible_keys（可能使用的索引）、key（实际使用的索引）、key_len（索引长度，越短越好） explain 之 rows：扫描的行数 explain 之 extra：using filesort(文件排序，效率低)、using temporary（使用了临时表保存中间结果，效率低）、using index（使用了索引）
show profile分析SQL：
通过 have_profiling 参数，查看当前MySQL是否支持profile
（select @@have_profiling ;）
set profiling=1；开启profiling
show profiles；查看SQL执行的耗时
show profile for query query_id；查看SQL执行过程中每个线程的状态和耗时情况
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。
trace分析优化器执行计划：
打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;
查看优化日志：select * from information_schema.optimizer_trace\G;
避免索引失效：
全值匹配 ，对索引中所有列都指定具体值。 最左前缀法则，查询从索引的最左前列开始，并且不跳过索引中的列。 索引失效：1.联合索引中跳跃某一列，只有最左边的索引生效；2.范围查询，右边的列索引失效；3.索引列上进行运算操作，索引失效；4.字符串不加引号，索引失效。 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），查询的字段尽可能是有索引的字段，否则会回表查询，影响效率 如果or前的条件中的列有索引，后面的列中没有索引，那么涉及的索引都不会被用到。 模糊查询%在前，索引会失效，可以通过覆盖索引来解决 如果MySQL评估使用索引比全表更慢，则不使用索引 in 走索引， not in 索引失效 尽量使用复合索引，而少使用单列索引 。因为单列索引，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引
查看索引使用情况：
show status like 'Handler_read%'; 当前会话
show global status like 'Handler_read%'; 全局
SQL优化
导入大批量数据的优化：
主键顺序插入 关闭唯一性校验SET UNIQUE_CHECKS=0，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验 关闭自动提交，手动提交事务SET AUTOCOMMIT=0，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交
优化insert语句：
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。 手动开启关闭事务，分段提交 数据有序插入
优化order by语句：
两种排序：filesort 排序、using index排序
查询的列是有索引的列 where 条件和Order by 使用相同的索引 Order By 的顺序和索引顺序相同 Order by 的字段都是升序，或者都是降序
Filesort 的优化：
两次扫描算法：MySQL4.1 之前，根据条件取出排序字段和行指针信息在sort buffer 中排序，完成排序后，再根据行指针回表读取记录，会导致大量随机I/O操作。
一次扫描算法：一次性取出满足条件的所有字段，然后在sort buffer 中排序后直接输出结果集。内存开销较大，效率高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用一次扫描算法；否则使用两次扫描算法。可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。
优化group by 语句：
order by null 只进行分组，禁止排序 给分组列创建索引
优化嵌套查询：尽量避免使用子查询，改用连接查询
优化OR条件：
OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引 union 替换 or
优化分页查询：
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。SELECT * FROM tb_user u ,(SELECT id FROM tb_user ORDER BY id LIMIT 200000,10) a WHERE u.id = a.id 把Limit 查询转换成某个位置的查询 ,适用于主键自增且没有断层的表，SELECT * FROM tb_user WHERE id>200000 LIMIT 10
使用SQL提示:
USE INDEX：在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。 IGNORE INDEX：忽略指定的索引 FORCE INDEX：强制使用指定的索引（在表明后使用）
应用优化
使用连接池：频繁的创建关闭连接，比较耗费资源。使用连接池提高访问性能。 减少对MySQL的访问：避免对数据进行重复检索。 增加cache层 负载均衡 ：利用MySQL复制分流查询，主从复制实现读写分离；采用分布式数据库架构
查询缓存优化：
客户端发送一条查询给服务器； 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段； 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划； MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询； 将结果返回给客户端。
查询缓存配置：
查看当前的MySQL数据库是否支持查询缓存：SHOW VARIABLES LIKE 'have_query_cache';
查看当前MySQL是否开启了查询缓存 ：SHOW VARIABLES LIKE 'query_cache_type';
查看查询缓存的占用大小 ：SHOW VARIABLES LIKE 'query_cache_size';
查看查询缓存的状态变量：SHOW STATUS LIKE 'Qcache%';
Qcache_free_memory（缓存可用内存量）、Qcache_hits（命中数）、Qcache_inserts（查询数）、Qcache_not_cached（非缓存查询数）
开启查询缓存：OFF或0（缓存关闭）、ON或1（缓存开启，查询结果符合条件则缓存，SQL_NO_CACHE显式指定不缓存）、DEMAND（按需进行缓存，SQL_CACHE显式指定缓存）
在 /usr/my.cnf 配置中，增加query_cache_type=1，重启服务生效
查询缓存失效的情况：
SQL 语句不一致（包括大小写不一致，必须完全一致） 当查询语句存在不确定信息时，如 ：now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 不使用任何表查询语句，如：select 'A'; 查询 mysql数据库中的表时，如information_schema 在存储的函数，触发器或事件的主体内执行的查询。 更改表，会使该表所有高速缓存查询都将变为无效并从高速缓存中删除，如INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE
**InnoDB 内存优化：**innodb用一块内存区做IO缓存池，该缓存池用来缓存innodb的索引块h和innodb的数据块。
innodb_buffer_pool_size：在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高 innodb_log_buffer_size：日志缓存，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。
Mysql并发参数调整：
max_connections：设置允许连接到MySQL数据库的最大数量，默认值是 151，如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时可以考虑增大max_connections 的值。 back_log：设置等待连接的数量，MySql的连接数达到max_connections时，新的请求会被存在堆栈中，等待某一连接释放资源，该堆栈的数量即back_log table_open_cache：设置所有SQL语句执行线程可打开表缓存的数量，该值应该根据最大连接数以及每个连接执行关联查询中涉及的表的最大数量来设置 thread_cache_size：设置缓存客户服务线程的数量， innodb_lock_wait_timeout：设置行锁等待时间，默认50ms
Mysql锁
锁分类：
表锁：操作时，会锁定整个表。 行锁：操作时，会锁定当前操作行。 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
MyISAM 表锁：MyISAM 存储引擎只支持表锁
加表锁：MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁；显示加表锁语法：
加读锁 ：lock table table_name read; 加写锁 ：lock table table_name write；
读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
MyISAM 的读写锁调度是写优先，所以适合做读为主的表的存储引擎
查看锁的争用情况：
show open tables：
In_user ：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。 Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
Table_locks_immediate ：能够立即获得表级锁的次数，每立即获取锁，值加1。 Table_locks_waited ：不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。
InnoDB 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
事务及其ACID属性：
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。 隔离性（Isolation）： 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。 持久性（Durable）:事务完成之后，对于数据的修改是永久的。
并发事务处理带来的问题:
丢失更新（Lost Update）：当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。 脏读（Dirty Reads）：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。 幻读（Phantom Reads）：一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。
事务隔离级别：
读未提交（Read uncommitted ）：解决丢失更新 读已提交（Read committed）：解决丢失更新、脏读 可重复读（Repeatable read 默认）：解决丢失更新、脏读、不可重复读 串行化（Serializable）：解决丢失更新、脏读、不可重复读、幻读
InnoDB 的行锁模式：对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加 排他锁；
共享锁（S）：读锁，多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。 排他锁（X）：写锁，不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
显示给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
无索引行锁升级为表锁：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
间隙锁：使用范围条件进行检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据进行加锁；对于键值在条件范围内但并不存在的记录，叫做 "间隙（GAP）" ， InnoDB也会对这个 "间隙" 加锁。缩小条件筛选范围可以降低间隙锁的出现。
InnoDB 行锁争用情况：show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
优化建议：
尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。 合理设计索引，尽量缩小锁的范围 尽可能减少索引条件，及索引范围，避免间隙锁 尽量控制事务大小，减少锁定资源量和时间长度 尽可使用低级别事务隔离（但是需要业务层面满足需求）
常用SQL技巧
SQL执行顺序：
编写顺序：
SELECT DISTINCT
<select list>
FROM
<left_table> <join_type>
JOIN
<right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT
<limit_params>
执行顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select list>
ORDER BY <order_by_condition>
LIMIT <limit_params>
正则表达式使用：
^ 在字符串开始处进行匹配
$ 在字符串末尾处进行匹配
. 匹配任意单个字符, 包括换行符
select * from emp where name regexp '^T';
select * from emp where name regexp '2$';
select * from emp where name regexp '[uvw]';
MySQL 常用函数：
数字函数：
ABS ：求绝对值 MOD ：求余数 ROUND：对所传参数进行四舍五入
日期函数：
CURDATE、CURRENT_DATE ：两个函数作用相同，返回当前系统的日期 CURTIME、CURRENT_TIME ：两个函数作用相同，返回当前系统的时间 NOW、SYSDATE：两个函数作用相同，返回当前系统的日期和时间值
聚合函数：
MAX：查询指定列的最大值 MIN：查询指定列的最小值 COUNT：统计查询结果的行数 SUM：求和，返回指定列的总和 AVG：求平均值，返回指定列数据的平均值
Mysql 日志
错误日志：show variables like 'log_error%';查看错误日志位置
二进制日志：二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。
二进制日志默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。
#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
#配置二进制日志的格式
binlog_format=STATEMENT
日志格式：
STATEMENT：该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。 ROW：该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ：update tb_book set status='1' , 如果是STATEMENT 日志格式，在日志中会记录一行SQL文件；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。 MIXED：MySQL默认的日志格式，混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。
查看STATEMENT格式日志内容 ：mysqlbinlog mysqlbing.000001；
查看ROW格式日志：mysqlbinlog -vv mysqlbin.000002
日志删除：
Reset Master ：删除全部日志 purge master logs to 'mysqlbin.'：删除编号之前的所有日志 purge master logs before 'yyyy-mm-dd hh24:mi:ss'：删除yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志 --expire_logs_days=#：配置文件中配置日志过期时间
慢查询日志：
文件位置和格式：
# 该参数用来控制慢查询日志是否开启， 可取值：1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1
# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
long_query_time=10
Mysql复制
复制原理：主库数据有变更就会记录在Binarylog中，从库有一个线程时时读取Binarylog日志然后再写入RelayLog，之后会有一个线程将RelayLog中的sql语句反映到自己服务器的数据变化
主库db的更新事件(update、insert、delete)被写到binlog，binlog为一个二进制文件，记录了所有sql语句。 从库发起连接，连接到主库 此时主库创建一个binlog dump thread线程（binlog镜像输出线程），把binlog的内容发送到从库。 从库启动之后（binlog dump thread线程的复制开始时），创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log. 还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db.
复制优势:
主库出现问题，可以快速切换到从库提供服务。
可以主库负责写操作，从库负责读操作，实现读写分离，降低主库的访问压力。
可以在从库中执行备份，以避免备份期间影响主库的服务。
主从复制搭建步骤：
master主库：
在master 的配置文件（/usr/my.cnf）中，配置如下内容：
#mysql 服务ID,保证整个集群环境中唯一
server-id=1
#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin
#错误日志,默认已经开启
#log-err
#mysql的安装目录
#basedir
#mysql的临时目录
#tmpdir
#mysql的数据存放目录
#datadir
#是否只读,1 代表只读, 0 代表读写
read-only=0
#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01执行完毕之后，需要重启Mysql：
service mysql restart;创建同步数据的账户，并且进行授权操作：
#创建账户
grant replication slave on *.* to 'itcast'@'192.168.192.131' identified by 'itcast';
#刷新权限列表
flush privileges;查看master状态：
show master status;字段含义：
File : 从哪个日志文件开始推送日志文件 Position ：从哪个位置开始推送日志 Binlog_Ignore_DB : 指定不需要同步的数据库
slave从库：
在 slave 端配置文件中，配置如下内容：
#mysql服务端ID,唯一
server-id=2
#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin执行完毕之后，需要重启Mysql：
service mysql restart；执行如下指令 ：
#连接主库的ip
change master to master_host= '192.168.192.130',
#连接主库的用户名密码
master_user='itcast', master_password='itcast',
#以下通过show master status;命令查询得到
master_log_file='mysqlbin.000001', master_log_pos=413;
开启同步操作
#开启从库
start slave;
#查看从库状态
show slave status;重点关注Slave_io_Running和Slave_SQL_Running这两个线程状态
停止同步操作
stop slave;
DevX 不止于技术
原文作者：DevXTalk；如有侵权，请联系管理员删除。
