Mysql调优篇

一、性能分析

1. 查看系统性能参数

语法如下：

show [global | session] status like ‘参数’

• connections 【连接mysql次数】
• uptime 【mysql服务上线时间】
• slow_queries 【共发生多少次慢查询 - 系统有阀值】采用show variables like 'long_query_time';查询阀值
• innodb_rows_read【服务至上线以来 共执行多少次查询】
• innodb_rows_delete【服务至上线以来 共执行多少次删除】
• innodb_rows_insert【服务至上线以来 共执行多少次插入】
• innodb_rows_update【服务至上线以来 共执行多少次更新】
• com_select 【本次连接共执行多少次查询】
• com_delete 【本次连接共执行多少次删除】
• com_insert 【本次连接共执行多少次插入】
• com_update 【本次连接共执行多少次更新】
• last_query_cost 【上一次查询读取的数据页数量】

2. 定位慢SQL

2.1 慢SQL日志

1. 查看慢SQL日志开启状态

查看慢SQL日志开启状态：show variables like ‘slow_query_log’;
开启慢SQL日志：set global slow_query_log = ON;

slow_query_log_file为慢SQL日志存储位置（默认存储在mysql下data目录中，默认名称为hostname-slow.log)

2. 慢SQL判定阀值

查看：show variables like ‘long_query_time’;
设置：set [global | session] long_query_time = 秒数;

tips: 除了long_query_time这个变量觉得SQL是否是慢SQL外，还有一个变量min_examined_row_limit，默认为0。
意思是若当前查询超过long_query_time的时间，且扫描了min_examined_row_limit条记录，才会被记录在日志当中。

补充：
可在mysql配置文件my.cnf中配置

[mysqld]
long_query_time=1 #慢查询阀值为1秒
slow_query_log=ON #开启慢SQL查询日志记录
slow_query_log_file=/data/mysql/data/xx-slow.log #指定日志存储位置
log_output=FILE	#日志输入为文件方式

3. 慢SQL日志分析工具

在Linux 命令行中执行mysqldumpslow- -help查看每个参数含义

mysqldumpslow -s t -a -t 5 xx-slow.log 
-s 使用t 执行时间进行排序显示
-a 不带该参数则打印出来的sql where条件数字会变成N 字符串变为S
-t 5 打印最慢的前5条sql

执行结果如下：

4. SQL分析工具：explain

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra

字段解释

① table

查询的表名

② id

每个Select关键字都对应一个唯一的ID(非100%)

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中 id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好

③ select_type

查询类型（请考虑优化器重构SQL情况 在执行完SQL后紧接着执行SHOW WARNINGS;可查看优化器重写SQL）

• 除了union和子查询 其余都是SIMPLE类型
• 对于包含UNION或UNION ALL 或子查询的大查询来说，最左边的是PRIMARY，其余是UNION
• MYSQL选择使用临时表来完成UNION查询去重工作，针对临时表的类型是UNION RESULT
• 子查询与主查询不相关 类型是SUBQUERY 相关则是 DEPENDENT SUBQUERY（有依赖性的）因为嵌套关系 DEPENDENT SUBQUERY可能会执行多次
• 在包含UNION或UNION ALL的大查询中 如果各个子查询都依赖于外层查询的话，那么除了最左边的子查询是DEPENDENT SUBQUERY外，其余都是DEPENDENT UNION
• 对于派生表对应的查询类型是derived+id
• 当优化器优化子查询变为物化表（常量）进行连接查询时，子查询对应的类型是MATERIALIZED

④ partitions

代表分区表命中情况，非分区表则显示null 可忽略

⑤ type ★

执行查询的访问方法
完整访问方法：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all (顺序：性能由高到低排序）

• system：表中只有一条数据，且该表使用的存储引擎的统计数据是精确（非近似）的，如MyISAM、Memory（在Innodb中对应是ALL 因为他没有变量去存储结果）
• const：根据主键或唯一索引列与常量进行等值匹配时，是const。如select * from tb1 where id = 1;
• eq_ref：在连接查询时，被驱动表是通过主键或唯一二级索引列等值匹配的方式进行访问的（如果涉及联合索引，所有索引列都必须参与等值匹配）
• ref：当通过某个普通索引和常量进行等值匹配来查询表时，则访问方法可能是ref
• ref_or_null：同上，但出现了查询条件为该普通索引列为null 如 key = ‘a’ or key is null
• index_merge：表示索引合并查询 如 where key1 = ‘a’ or key2 = ‘a’ (key1和key2都建立了索引) 这只有使用or才行 and将遵循最左原则
• unique_subquery：针对在一些包含in子查询的查询语句中 如果查询优化器决定将in子查询转换为exists子查询 且子查询可以使用到主键进行等值匹配的话，那么访问方法就是unique_subquery
• range：如果使用索引来获取某些范围区间的记录 则可能使用到range访问方法
• index：当我们可以使用索引覆盖（key2、key3均包含在联合索引中，不需要回表查询），但需要扫描全部索引记录时：（包含联合索引key1、key2、key3时）查询语句为select key2 from tb1 where key3 = ‘a’，这种情况下的访问方法为index
• all：全表扫描

SQL性能优化目标：最少达到range级别，要求是ref级别，最好是const级别。（来源：Alibaba开发手册）

⑥ possible_keys和key

• possible_key列显示在查询语句中，涉及到的字段上存在索引，则该索引会被列出，但不一定会被使用到。
• key表示实际用到的索引

⑦ key_len ★

实际使用到的索引长度（即：字节数）
针对联合索引有一定意义上的参考价值，与自身相比，值越大越好

⑧ ref

当使用索引列进行等值查询时，与索引列进行等值匹配的对象信息（字段、函数等）

⑨ rows ★

预估的需要读取的记录条数 值越小越好

⑩ filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

• 值越大越好，说明搜索出来的数据都是符合条件的
• 该字段主要针对联合查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数（即驱动表rows*filtered）

⑪ Extra

一些额外的信息 更准确的理解Mysql到底将如何执行给定的查询语句
以下列举一些比较重要的内容：

• using filesort : 文件排序；有些情况下，当我们使用order by时无法使用到索引，只能在内存中（数据量小）或磁盘中（数据量大）进行排序，MYSQL把这种排序方式称为文件排序（因为.ibd文件中不存在已经排好序的数据时会出现这种情况）
• using temporary：临时表；当查询时没使用到索引，MYSQL很可能会寻求通过建立内部的临时表辅助执行查询。建立和维护临时表需要很大成本，最好可以使用索引来替代掉临时表的创建。

5. 系统表分析

# 查询冗余索引
select * from sys.schema_redundant_indexes sri;

# 查看表索引
show INDEX from jrwms.wm_wv_header;

# 查询未使用过的索引
select * from sys.schema_unused_indexes sui ;

# 查询索引使用情况
select * from sys.schema_index_statistics sis where table_schema = 'jrwms';

# 查询表的访问量
select table_schema , table_name, sum(io_read_requests+io_write_requests) as io 
from sys.schema_table_statistics sts group by table_schema ,table_name order by io desc;

# 查询表占用bufferpool的情况
select object_schema , object_name , allocated , `data` 
from sys.innodb_buffer_stats_by_table ibsbt order by allocated limit 10;

# 查看全表扫描情况
select * from sys.schema_tables_with_full_table_scans stwfts ;

# 查看sql执行频率
select * from sys.statement_analysis sa order by exec_count desc;

# 查询使用了排序的SQL
select * from sys.statements_with_sorting sws limit 1;

# 查询使用了临时表或磁盘临时表的SQL
select * from sys.statement_analysis sa 
where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables + tmp_disk_tables) desc;

# 查看行锁
select * from sys.innodb_lock_waits ilw ;

风险提示：
通过sys库去查询时，MYSQL会消耗大量资源去收集信息，严重的可能会影响业务请求被阻塞，从而引发故障。建议生产环境下不要频繁查询这些系统表去来完成分析工作。

二、SQL性能调优

调优方向：

1. 索引
2. SQL
3. 服务器：缓存、线程等（my.cnf）
4. 分库分表：数据量大

1. 外连接和内连接的优化

优化器可决定谁是驱动表和谁是被驱动表，小结果集驱动大结果集。（度量单位：表行数 * 每行大小）
建议：在连接条件上将被驱动表的连接字段加上索引。

select * from tb1 left join tb2 where tb1.name = tb2.name;

原理：驱动表tb1全表查询，每循环到一条数据时，会根据该数据的name值去循环被驱动表tb2全表。

而对于内连接来说

select * from tb1 inner join tb2 where tb1.name = tb2.name;

如果tb2数据量大于tb1，则tb2作为驱动表，tb1作为被驱动表。
如果tb1.name上存在索引，则tb1会作为被驱动表，tb2作为驱动表。

小结：

1. 整体效率：INLJ > BNLJ > SNLJ
2. 小结果集驱动大结果集（其本质就是减少被驱动表的循环次数）
3. 为被驱动表的过滤条件增加索引（减少被驱动表匹配的次数）
4. 增大Join Buffer Size大小（一次缓存的数据越多，内层包的扫表次数就越少）
5. 减少驱动表的不必要字段的查询（字段越少，Join Buffer Size缓存数据越多）

2. 子查询优化

尽量不使用NOT IN 或 NOT EXISTS，使用LEFT JOIN xx WHERE xx IS NULL

3. 排序优化

创建组合索引:
create index idx_age_classid_name on student(age,classid,name)

1. 使用索引

• 不限制，索引失效：

select SQL_NO_CACHE * from student order by age,classid limit 10; 

对于上面的例子来说，如果没有limit，则mysql可能会使用filesort进行排序。因为如果数据量大的情况下，使用索引可能还需要回表查询，这样会导致开销比filesort还大，所以mysql会采用filesort。

• order by顺序错误，索引失效：

select SQL_NO_CACHE * from student order by classid, age limit 10;

对于这种sql来说不遵循最佳左前缀原则，索引失效。因为索引创建时，age顺序比classid靠前。

• order by方向反，索引失效：

select SQL_NO_CACHE * from student order by age ASC, classid DESC limit 10;

该例子，age正序排列，classid逆序排列。如果mysql使用索引，则还需要因为classid逆序排列而进行一次filesort，资源浪费。所以该例子索引失效。

• 数据量大小

select SQL_NO_CACHE * from student where age=45 order by classid; 

该例子中，因为age过滤后的结果集数据量较小，则仅使用了age索引，并直接回表，没有再使用到classid了。

其他请查看索引失效的情况

在查询中，mysql自动选择最优方案，但随着数据量的变化，是否使用索引还是优化器决定。
当【范围条件】和【group by 或 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上，反之，亦然。

2. 优化Filesort

2.1 filesort算法：双路排序和单路排序

1. 双路排序（慢）：
   在MYSQL 4.1之前使用的是双路排序。就是扫描两次磁盘，最终得到数据。第一次磁盘IO将行指针（指向行所在的磁盘地址）和order by列读取到内存Buffer中，对order by列进行排序，然后根据排好列的数据通过行指针找到对应数据第二次磁盘IO操作读取全部数据。
2. 单路排序（快）：
   单路排序仅进行一次磁盘IO。从磁盘读取查询需要的所有列，按照order by列在buffer中对他们进行排序，然后输出排序后的列表，它的效率更快一些，避免第二次磁盘IO，并把随机IO变为了顺序IO，但它会使用更多的空间，因为它把每一行都保存在了内存中。

单路排序的问题：
虽然单路排序总体而言好过双路排序
但是单路需要更大的内存空间，假设需要排序的数据大于内存buffer的大小，就会导致每次只能取buffer容量大小的数据进行排序（创建tmp文件缓存排序结果，多路合并），排完再次读取buffer大小数据，导致多次IO

优化策略：

1. 提高sort_buffer_size（1M~8M 默认1M）

不管使用两种算法之间的哪种算法，提高这个参数都会提高效率。但要根据系统的能力去提高。因为这个参数决定每个连接（mysql connection）所使用的内存大小，如果设置过大，在多个连接产生后可能会耗尽系统资源。

2. 提高max_length_for_fort_data（1024字节~8192字节 默认1k）

读取数据最大限制。但是如果设置的太高，数据总容量超出sort_buffer_size的概览就增大。
如果需要返回的列的总长度大于max_length_for_sort_data，则使用双路算法，否则使用单路算法。

3. order by时最好只查询需要的字段

当查询的字段大小总和小于max_length_for_sort_data，且排序字段不是text|blob类型，会使用单路算法，否则使用双路算法。

4. 减少使用order by
   order by、group by、distinct这些语句比较耗费CPU，数据库CPU资源极其宝贵。若允许，尽量放在程序端进行操作。

4. GROUP BY优化

• GROUP BY 使用索引原则几乎和ORDER BY一致，GROUP BY即时无过滤条件也可以直接使用索引。
• 先ORDER BY 再 GROUP BY遵循索引建的最佳左前缀原则
• 当无法使用索引列时，增加max_length_for_sort_data和sort_buffer_size大小
• where效率高于having
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则sql会很慢。

5. LIMIT 优化

select * from tb1 limit 200000,10;

针对以上SQL，有两种优化思路：主要是根据聚簇索引进行优化

1. 优化思路1

select * from tb1, (select id from tb1 order by id limit 200000,10) as tb2 where tb1.id = tb2.id;

2. 优化思路2
   该方案针对主键自增的表

select * from tb1 where id > 200000 limit 10;

6. 其他查询策略

6.1 EXISTS和IN

小结果集驱动大结果集：减少外层循环

select * from A where cc in (select cc from B)
select * from A where EXISTS (select cc from B where B.cc = A.cc)

① 当B小于A时 使用IN，因为实现的逻辑类似于

for i in B
	for j in A
		if i.cc = j.cc then ...

② 当A小于B时，使用EXISTS。因为实现的逻辑类似于

for i in A
	for j in B
		if i.cc = j.cc then ...

6.2 count(1)、count(*)、count(具体字段)

1. 可以认为count(1)和count()的执行效率几乎一致, 以下以count()为例。
2. 如果是MyISAM引擎，count(*)统计行数只需要O(1)的复杂度，这是因为每个MyISAM表中都有一个meta信息存储了row_count值，而一致性由表级锁决定。
   如果是Innodb引擎，需要全表扫描，进行循环+计数的方式来完成统计，O(n)的复杂度。
3. 在Innodb引擎中，如果采用Count(具体字段)来统计数据行数，尽量采用二级索引。因为主键采用聚簇索引，包含的信息多，加载进内存需要一定的时间和空间。而对于COUNT(1)和COUNT(*)来说，他们不需要寻找字段，只统计行数，mysql会自动选择一个占用空间最小的索引进行统计。

6.3 select *

尽量不使用select *的两点原因

1. MYSQL在解析过程中，会通过查询数据字典将*按序转换成所有的列名，这个过程占用资源极大
2. 无法使用覆盖索引

6.4 LIMIT 1

1. 如果对于结果集你可以确定只有一条的时候，使用LIMIT 1会加快查询速度，因为mysql在找到一条记录时就返回。
2. 如果对于一个已经建立了唯一索引的列来说，LIMIT 1意义不大。

6.5 多使用COMMIT

查看数据库是否开启自动COMMIT：show variables like ‘%autocommit%’;

COMMIT释放的资源：

1. 回滚段上用于恢复数据的信息
2. 被程序语句获得的锁
3. redo / undo log buffer中的空间
4. 管理上述3种资源所需要的内部花费也会减少

三、数据库调优

1. 优化表设计

• 遵循三范式原则
• 如果多表联查较多，采用反范式设置冗余字段提高查询效率
• 表字段的数据类型选择：关系到了查询效率的高低及存储空间的大小；字符长度尽可能设计得短一些。可以采用数值类型就不选择字符类型，字符长度确定选择char类型，长度不固定选择varchar类型。

2. 使用redis等缓存

3. 库级优化

4. 数据库结构优化

4.1 拆分表：冷热数据分离

思路是把1个包含了很多字段的表拆分成2个或多个相对较小的表。这样做的原因是，这些表中的某些字段操作频率很高（热数据），而另外一些字段使用频率很低（冷数据），冷热数据分离可以减少表的宽度，降低资源消耗。
表越宽（字段越多）把表装载进内存缓冲池所占用的内存也就越大，也会消耗很多的IO。
冷热数据分离可以① 减少IO，保证热数据的内存缓存命中率 ②更有效利用缓存，避免读入无用冷数据。

4.2 增加中间表

把经常需要联合查询的数据插入中间表中，然后将原来的联合查询改为中间表查询，提高查询效率。

4.3 增加冗余字段

4.4 优化数据类型

在数据量很大的时候，数据类型的定义很大程度上会影响到系统整体的执行效率。

1. 对整数类型数据进行优化
   对于非负型数据，优先使用无符号整型UNSIGNED来存储。因为无符号相对于有符号同样的字节数，存储数值范围更大。如tinyint有符号范围是-128-127，无符号的取值范围是0-255，多了一倍的存储空间。

2. 在文本类型和整数类型中，要选择整数类型
   跟文本类型数据相比，大整数往往占用更少的存储空间。

3. 避免使用TEXT、BLOB
   Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。并且对于这种数据，Mysql还是要进行二次查询，会使sql性能变差。
   如果一定要使用，建议把BLOB或TEXT列分离到单独的扩展表中，查询时一定不要使用select * ，而只需要取出必要的列，不需要使用TEXT列的数据时不要对该列进行查询

4. 避免使用ENUM类型
   修改ENUM类型需要使用ALTER语句
   ENUM类型的order by操作效率较低，需要额外操作，推荐使用TINYINT代替

5. 使用TIMESTAMP存储时间
   TIMESTAMP存储时间范围：1970-01-01 00:00:00 ~ 2033-01-19 03:14:07。TIMESTAMP采用4个字节，DATETIME使用8个字节。同时TIMESTAMP具有自动赋值及自动更新的特性。

6. 使用DECIMAL代替FLOAT和DOUBLE存储高精度浮点数
   尤其是财务相关的金融类数据，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可存储9位数字，并且小数点要占用一个字节。可用于存储比BIGINT更大的整数类型。

4.5 优化插入记录的速度

影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。

MyISAM表：
① 禁用索引
对于非空表，在大量插入数据前禁用索引，会因为维护索引数据而降低插入记录速度，待插入完毕后再开启索引。
对于空表来说则不需要禁用索引，因为MyISAM表是在导入数据之后才建立索引的。

禁用索引：ALTER TABLE table_name DISABLE KEYS;
开启索引：ALTER TABLE table_name ENABLE KEYS;

② 禁用唯一性检查
对于含有唯一性的列（如主键，唯一索引等），在插入数据时，Mysql会对插入的数据进行唯一性校验从而降低了插入数据的效率。当我们大批量插入数据时，可以禁用唯一性检查，待数据导入完毕后再开启。

禁用唯一性检查：SET UNIQUE_CHECKS=0;
开启唯一性检查：SET UNIQUE_CHECKS=1;

③ 使用LOAD DATA INFILE批量导入数据

LOAD DATA INFILE速度比INSERT快

Innodb表：

① 禁用唯一性检查
同上

② 禁用外键检查
因为Innodb支持外键，所以当插入数据涉及到外键时，mysql还会去对应的表中查找是否存在该条记录。

禁用外键检查：SET FOREIGN_KEY_CHECKS=0;
开启外键检查：SET FOREIGN_KEY_CHECKS=1;

③ 禁止自动提交
Innodb支持事务，所以在每次增删改查时都会commit一次进行刷盘，当大批量插入数据时会导致资源浪费，可在批量导入数据时关闭自动提交，待数据导入成功后再开启。

禁用自动提交：SET AUTOCOMMIT=0;
开启自动提交：SET AUTOCOMMIT=1;

4.6 大表优化

当一个表的数据量过大时，有以下几种调优方案：

1. 限定查询范围：如查询日志表，默认只查询一个月内的数据。
2. 读写分离 搭建集群+主从复制（使用Mycat等进行流量分发）
3. 垂直拆分：将数据分成多块放在不同的服务器上，减少单个服务器的访问压力
4. 水平拆分：将数据拆分成多个表但同时保持表结构一致，如将2017年数据放入一个表，2018年数据放入另一个表。这样做仅解决了单一表数据量过大的问题，并没有实质性作用，所以水平拆分最好分库。从而达到分布式目的。但水平拆分会带来分片事务难以解决，跨节点JOIN性能差，逻辑复杂等问题。所以尽量不要对数据进行分片。如果实在要分片尽量选择客户端分片架构，这样可以减少一次和中间件的网络IO。

数据库分片的两种常见方案：

1. 客户端代理：分片逻辑在应用端，封装在jar包里，通过修改或封装JDBC来实现。当当网Sharing-JDBC、阿里TDDL都是这种方式。
2. 中间件代理：在应用层和数据层中间加一个代理层，分片逻辑统一维护在中间件服务中。Mycat、360的Atlas及网易DDB都是这种架构实现。