【MySql性能优化专题】

MYSQL SQL调优

1. 思路

我们要进行SQL调优那么需要完成如下几个步骤：

1. 找到查询速度慢的SQL（慢查询日志）
2. 分析该SQL（explain）
3. 优化该SQL

2. 慢查询日志

• 开启慢查询日志

在my.ini中设置如下开启慢查询日志

slow-query-log=1（开启慢查询日志）

slow_query_log_file="mysql-slow.log"（慢查询日志的路径）

long_query_time=5（大于多少的才进行记录，单位是秒）
log_queries_not_using_indexes=1 (未使用索引的查询也被记录到慢查询日志中)
查看慢查询日志是否开启：
 show VARIABLES like '%slow%';
校验SQL:
select sleep(6)：睡眠6秒

• 筛选慢查询日志找到需要优化的SQL

  使用mysql提供的mysqldumpslow工具
  
  mysqldumpslow -s t -t 10 /database/mysql/mysql06_slow.log
  
  (获取按照耗时排序的前10条sql语句)
  备注：通常不需要开发人员去dump，因为一般只有运维的才有线上数据库的权限
  

  其他详细请参考：https://www.cnblogs.com/saneri/p/6656161.html

3. explain的作用

• 查看表的加载顺序。
• 查看sql的查询类型。
• 哪些索引可能被使用。
• 哪些索引又被实际使用了。
• 表之间的引用关系。
• 一个表中有多少行被优化器查询
• 其他额外的辅助信息。

4. explain 属性值解释

• id ：表读取顺序，id相同，执行顺序由上到下，id不同，值越大，执行的优先级越高

  •    explain select * from course_base cb ,category c   where  cb.st = c.id ;
       explain select * from course_base cb  where cb.id = (select id from course_base where id ='297e7c7c62b888f00162b8a7dec20000');
    

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

  • SIMPLE :简单的查询，不包含子查询或者union
  • PRIMARY ：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  • SUBQUERY ：该查询是在SELECT或WHERE列表中被包含的子查询

• table: 当前执行的表名

• type: 查询使用类型

  • 从最好到最差依次是：system > const > eq_ref > ref > range > index > all

  • 一般来说，得保证查询至少达到range级别，最好能达到ref。

    system ：表示只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

    const ：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。(单表，且条件值常量)

    explain select * from course_base cb where id ='297e7c7c62b888f00162b8a7dec20000';
    

    eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描（多表，条件值可以不为常量）

    explain select * from course_base cb ,category c   where  c.id = cb.st ;
    

    ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

    create INDEX inx_st on course_base(st);
    explain select * from course_base cb  where st = '1-6-1';
    

    range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

    explain select * from course_base cb  where id like '1%';
    

    index： Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（查询返回的列如果有索引那么就会用到index查询类型）

    explain select st from course_base ;
    

    all：Full Table Scan 将遍历全表以找到匹配的行

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

  explain select * from course_base cb ,category c   where  cb.st = c.id ;
  

• key: 实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

• key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，精确度越高,key_len值越大，在不损失精确度的情况下，长度越短越好。(也就是命中的索引越多key_len就越大，精确度也越高)

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

  explain select * from course_base cb ,category c   where  cb.st = c.id ;
  explain select * from course_base cb  where st = '1-6-1';
  

• rows: 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

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

  • Using filesort ：MySQL中无法利用索引完成的排序操作称为“文件排序”。(order by，group by没有使用到索引时就会出现)

    • 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；
    • 如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！

    explain select * from course_base cb ORDER BY name ;
    explain select name from course_base cb ORDER BY name ;
    

  • Using temporary:使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表.(group by没使用索引会出现)

    explain select * from course_base cb group BY name ;
    create INDEX inx_name on course_base(name);
    

  • Using index:表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错（也即是说查询返回的列使用了索引）

    explain select st from course_base ;
    

  • Using where：表示使用了where进行条件筛选

    explain select * from course_base where users='dd';
    

更多详细参考：https://blog.csdn.net/why15732625998/article/details/80388236

5. 优化SQL

目的就是提高explain中的type的等级，减少rows扫描行数和优化Extra信息中提及的信息

主要从以下几点入手：

1.对于type等级低(index,all)且rows大的表加索引

2.检查是否有sql写法不当导致索引失效的

3.优化sql写法减少查询次数,避免不必要的查询

4.尽量指明返回的列，避免使用select * from

6. 索引失效的几种情况：

• 最佳左前缀法则：如果索引了多列，查询要从最左前列开始且不跳过索引中的列,因为底层的B±Tree会从最左的索引开始找，找到之后再按照第二的索引开始，如果顺序反了，会导致索引失效从而全表查询（一般在使用组合索引的时候会出现这种情况）

  drop INDEX inx_name on course_base;
  drop INDEX inx_st on course_base;
  CREATE index inx_st_name on course_base(st,name);
  explain select * from course_base where st='1-3-2' and `name` like's%';
  

• 少用or，用它来连接时索引会失效（除非or的所有列都加索引）

  explain select * from course_base where name = 'ffff' or users='xx';
  explain select * from course_base where name = 'ffff' or st ='1-6-4';
  

• 不在索引列上做任何操作（计算、函数、（自动or手动）转化），会导致索引失效而转向全表扫描

• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

• is null ,is not null也无法使用索引

• like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作

   explain select * from course_base  where name like 's%';
  

• 字符串不加单引号索引失效

    CREATE index inx_companyid on course_base(company_id);
    explain select * from course_base where company_id =1;
  

7. 添加索引的两种方式：

• alter TABLE 表名ADD INDEX 索引名(列名);

   1.添加PRIMARY KEY（主键索引） 
   ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
   2.添加UNIQUE(唯一索引) 
   ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
   3.添加INDEX(普通索引) 
   ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
   4.添加FULLTEXT(全文索引) 
   ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
   5.添加多列索引 
   ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3`)
  
  

• create INDEX 索引名 on 表名(字段名);

8. Mysql sql调优总结

1. 从慢查询日志中找到慢的SQL（没问怎么开启慢查询就不说）

2. explain分析SQL，找到type是all的rows比较大的SQL，进行单独分析

3. 确认type是all的原因是没有索引还是索引失效，根据情况进行修改，没有索引就添加索引，索引失效就修改写法。

4. 如果有order by或者group by ，看看order by和group by是否有索引，没有可以加上试试

5. 尽量指明返回的列，避免使用select * from

6. like 使用的时候要采用’abc%'的这种格式 减少不必要的嵌套等等

9. 为什么要用 B+tree作为MySQL索引的数据结构

• B-tree索引能够加快访问数据的速度，因为存储引擎不再需要经行全表扫描来获取需要的数据，取而代之的是从根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下查找。通常比较节点页的值和要查找的值可以找到合适的指针进入下层子节点。B-tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。真实的数据放到叶子节点
• 问：为什么索引结构默认使用B+Tree，而不是hash，二叉树，红黑树？
  hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
  二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
  红黑树：树的高度随着数据量增加而增加，IO代价高

10.其他链接

百度Mysql相关面试问题

• MySQL优化，效率至少提高3倍https://www.toutiao.com/i6642573506674426371/]
• 你了解什么是B+Tree吗[https://www.cnblogs.com/dongguacai/p/7241860.html]