MySQL-SQL性能优化

性能下降 SQL执行慢

• 查询SQL写的烂

• 索引失效：没有建索引或索引没有使用，符合索引使用不正确

   - 单索引：CREATE INDEX t_sm_user_name ON user(name);
   - 复合索引: CREATE INDEX t_sm_user_name_age_tel ON user(name,age,tel);
  

• 关联查询join太多

SQL的加载顺序

 - 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_list>
 - LIMIT <limit_number>

7种JOIN

 - AB公共部分
    select <selevt_list> from tableA a inner join tableB b on a.key = b.key;
 - A的全部
    select <selevt_list> from tableA a left join tableB b on a.key = b.key;
 -  B的全部
     select <selevt_list> from tableA a rightjoin tableB b on a.key = b.key;   
 -  A独占
     select <selevt_list> from tableA a left join tableB b on a.key = b.key where b.key is null;
 -  B独占
     select <selevt_list> from tableA a right join tableB b on a.key = b.key where a.key is null;
 -  AB全部
     select <selevt_list> from tableA a full out join tableB b on a.key = b.key;
 -  AB独有
     select <selevt_list> from tableA a full out join tableB b on a.key = b.key where a.key is null or where b.key is null; 

索引

• 优势

  • 提高数据检索效率，降低数据库IO成本
  • 通过对索引列对数据进行排序，降低数据排序的成本，降低CPU的消

• 劣势

  • 保存了主键与索引字段，并指向实体的记录，占用空间
  • 更新表时，MySQL不仅要保存数据，还要保存索引每次更新添加了索引列的字段

索引分类

• 单值索引：一个索引包含单个列，一张表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引可以包含多个列

查看表的索引

SHOW INDEX FROM table_name;

BTree索引【其他索引结构不记录】

一颗b+树，浅蓝色的块称之为一个磁盘，每个磁盘包含几个数据项（深蓝色所示）和指针（黄色所示）。如磁盘块1包含数据项17和35，包含指针P1，P2，P3。P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实数据存在叶子节点53，5，9，10，13，28，29，36，60，75，79，90，99。非叶子节点不存储真是数据，只存储指引索引方向的数据项，如17，35不真实存在与数据表中。

索引创建原则

1. 主键自动建立索引
2. 频繁作为条件的字段应建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 查询中统计或分组的字段
5. where条件里用到的字段
6. 查询排序的字段，建索引讲大大提高排序速度
7. 频繁更新的字段不适合建索引。因为更新数据的同时还要更新索引

性能分析

EXPLAIN：执行计划
explain + sql语句

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而找到MySQL是如何处理SQL语句的。分析查询语句或表结构的性能瓶颈

EXPLAIN - id

• id相同，执行顺序由上而下。t_sm_paymode表先于t_sm_paymodetime执行

• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。t_sm_paymodetime表先于t_sm_paymode执行
  

EXPLAIN - select_type

select_type有6种值

• simple：select查询不包含子查询或union
• primary：查询中若包含复杂查询，最外层标记为
• subquery：在select或where列表中包含子查询
• derived：在from列表中包含的子查询标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表里
• union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
• union result：从union表获取结果的select

查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询。

EXPLAIN - table

表明这些数据是哪张表的

EXPLAIN - type

type：访问类型，常见几种值

• ALL：Full Table Scan，将遍历全表以找到匹配的行
• index：Full Table Scan，index与ALL区别为index类型只遍历索引树。index是从索引中读取，ALL是从硬盘中读取
• range：只检索给定范围的行，使用一个索引来进行选择。如where后使用between，<，>，in
• ref：非唯一索引扫描，返回匹配某个单独值的所有行
• eq-ref：唯一索引扫描，对于每个索引，表中只有一条与之匹配，常见于主键或唯一索引扫描
• const：表示通过索引一次就找到，用于比较primary key或者unique索引。因为只匹配一行，所以很快
• system：表只有一行记录（等于系统表），这是const类型的特例，一般不会出现
• NULL

从最好到最差

system > const > eq_ref > ref > range > index > ALL

一般查询SQL保证到达range以上级别

EXPLAIN - possible-keys、key

• possible-keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引则被列出，但不一定被查询实际使用

• key

实际使用的索引，如果为null，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中
覆盖索引：查询的字段中有复合索引的字段，且顺序一致。possible-keys值为null，key值为复合索引名

EXPLAIN - key-len

表示索引中使用的字节数，可以通过该列计算查询中使用索引的长度。在不损失精确度的情况下，长度越短越好。
key-len显示的值为索引字段的最大可能长度，并非实际使用长度，即key-len是根据表定义计算而得，不是通过表内检索出的

EXPLAIN - ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

EXPLAIN - rows

根据表统计信息及索引选用的情况，大致估算出找到所需的记录所需要读取的行数

EXPLAIN - Extra

包含不适合在其他列中显示但十分重要的额外信息

• Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为文件排序
• Using temporary【严重影响效率】：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
• Using index：表示相应的select操作中使用了覆盖索引，避免访问表的数据行，效率还不错。如果同时出现using where，表明索引被用来执行索引建值的查找。如果没有出现using where，表面索引用来读取数据而非执行查找动作。
• Using where：表明使用了where过滤
• using join buffer：使用了连接缓存
• impossible where：where 子句的值总是false，不能用来获取任何元祖
• select tables optimized away
• distinct

索引失效

• 全值匹配【推荐】
• 最佳左前缀法则【建议】
• 不在索引列上做任何操作（计算，函数，（自动或手动）类型转换），会导致索引失效而全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引
• mysql在使用!=,或者<>的时候无法使用索引导致全表扫描
• is null,is not null 无法使用索引
• like以通配符开头【"%abc…"】索引会失效【覆盖索引可以解决该问题，查询的字段是复合索引的字段，个数最好相同，顺序一致】
• 字符串不加单引号索失效
• 少用or,用它来连接时索引会失效

补充：复合索引【联合索引，组合索引】

• 创建复合索引并查看

CREATE INDEX wx_id_nickname_role on user(wx_id,wx_nickname,roles);
SHOW INDEX FROM USER;

• 在使用复合索引时，如果定义的索引为wx_id_nickname(wx_id,wx_nickname,roles)，在查询中**where**后面使用的顺序是roles,wx_nickname,wx_id。mysql会自己优化，因此索引也会生效。
  

• 使用单个字段或部分字段时，只要顺序一致，且最左边的字段不缺失，索引依然有效，出现断层则索引失效
  

查询优化

 -- 小表驱动大表：小的数据集驱动大的数据集【外层下，内层大】

	   SELECT * FROM A WHERE A.id IN(SELECT id FROM B);
	   --当B表的数据集大于A表时，用in优于exists
   
	   SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE B.id = A.id);
	   --A表的数据集小于B表的数据集时，用exists优于in

 -- 排序order by 优化
	--order by使用索引最左原则，排序尽量使用索引。使用索引排序时尽量使用默认的asc升序排序，降序
	  desc会产生filesort排序，效率低
 -- 分组group by 优化	
    -- 实质是先排序后进行分组，遵照索引最左原则。能写在where限定的条件就不要写在having中

慢查询日志

具体指运行时间超过long_query_time值的sql，会被记录到慢查询日志中。long_query_time的默认值是10秒，即运行10秒以上的sql会被记录到慢查询日志中。
MySQL数据库默认没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，不建议启动该参数，因为开启慢查询日志会带来一定的性能影响。

查看MySQL是否开启慢查询及慢查询日志地址

开启慢查询

此方法只对当前数据库有效，重启数据库会失效，如果想永久失效，需修改my.cnf配置文件，添加
SET GLOBAL slow_query_log = 1;
slow_query_log_file = 慢查询日志路径
强烈不建议永久开启慢查询日志，会影响MySQL性能

查看并设置慢查询时间

SHOW VARIABLES LIKE '%long_query_time%';
SET GLOBAL long_query_time= 2;
-- 注：重新设置值后，需重新连接或开启一个会话才能看到修改的值

模拟慢查询并查看慢查询日志

SELECT SLEEP(3); -- 类似于Java中sleep();

D:\develop\MySql\bin\mysqld, Version: 5.5.40 (MySQL Community Server
(GPL)). started with: TCP Port: 3306, Named Pipe: MySQL Time
Id Command Argument D:\develop\MySql\bin\mysqld, Version: 5.5.40
(MySQL Community Server (GPL)). started with: TCP Port: 3306, Named
Pipe: MySQL Time Id Command Argument
#Time: 191102 19:47:28
#User@Host: root[root] @ localhost [127.0.0.1]
#Query_time: 3.039607 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use datamange; SET timestamp=1572695248; SELECT
SLEEP(3);

MySQL慢查询日志的分析可借助自带的mysqldumpslo工具，这里不多做讲解。Show Profile以及全局日志查询也是SQL性能分析中的一部分，有兴趣的可自行查找资料。