深⼊慢sql优化
深⼊SQL优化
前⾔
总结了Mysql数据库的基础知识及概念。本⽂针对慢SQL的识别与优化进⾏深度探究，正巧前段时间遇到了⼀个相关的业务问题，有少许⼼得，跟⼤家分享。
为什么要进⾏sql优化
对于⼀个新业务⽽⾔，数据量和⽤户量相对较⼩，很多情况下甚⾄单库单表就⾜以满⾜当下业务需求。⽽随着时间的积累，业务数据量的增多，可能会开始进⾏分库分表，当数据量持续增加后，架构不发⽣明显变化的前提下，SQL的执⾏效率对程序的运⾏效率的影响逐渐增⼤，系统可能出现卡顿、延迟甚⾄超时报错。此时对SQL的优化就很有必要。
慢sql的现象及原因总结
⼀个风和⽇丽的下午，⼩明突然接到，说接到⼤量⽤户投诉，页⾯打不开了，⼀直加载中。⼩明⼼⾥⼀紧，最近就⾃⼰发布了新代码，加了⼀个新功能，不会是那部分代码出现了问题了吧 ? ! !
赶紧切流到备库，回滚代码。然后查看错误⽇志，发现数据库连接池报了⼤量的超时错误，这种情况⼀般有两种可能：⼀种是数据库或者连接数据库的⽹络发⽣了某种意外，导致数据库连接不上了，达到超时时间了；
另⼀种可能是有⼤量线程执⾏慢查询，⽼线程还在执⾏查询，新线程只能陷⼊等待，等待太久达到超时时间了。
最终定位定位到是数据库慢查询的问题导致这个故障。⼀个⾼频查询“没有命中索引，导致全表扫描”，单个查询最少就需要⼀秒多，所以⼤量查询请求堆积，超时。
如何分析sql慢的原因
通过Explain判断是否⾛索引
display显示对于低性能的SQL语句的定位，最重要也是最有效的⽅法就是使⽤执⾏计划，MySQL提供了explain命令来查看语句的执⾏计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对⼀条SQL语句进⾏执⾏的过程中都会做很多相关的优化，对于查询语句，最重要的优化⽅式就是使⽤索引。 ⽽执⾏计划，就是显⽰数据库引擎对于SQL语句的执⾏的详细情况，其中包含了是否使⽤索引，使⽤什么索引，使⽤的索引的相关信息等。
id : 唯⼀标识，id不同，id值越⼤优先级越⾼，越先被执⾏
select_type : 表⽰执⾏类型。如这⾥的simple表⽰⽆其他复杂查询
type(⾮常重要，可以看到有没有⾛索引)：
ALL 扫描全表数据
index 遍历索引
range 索引范围查
index_subquery 在⼦查询中使⽤ ref
unique_subquery 在⼦查询中使⽤ eq_ref
ref_or_null 对Null进⾏索引的优化的
mysql存储过程造数
ref fulltext使⽤全⽂索引
ref 使⽤⾮唯⼀索引查数据python中zip()函数的用法
eq_ref 在join查询中使⽤PRIMARY KEY or UNIQUE NOT NULL索引关联。
possible_keys：表⽰可能⽤到的索引，但不⼀定真正会⽤到
key:(重要，真正⾛的索引)
extra:（重要，可以分析索引⾛的类型），常见的有：
Using index 使⽤覆盖索引
Using where 使⽤了⽤where⼦句来过滤结果集
Using filesort使⽤⽂件排序，使⽤⾮索引列进⾏排序时出现，⾮常消耗性能，尽量优化。
Using temporary 使⽤了临时表
Tips：优化⽬标
1如果explain判断，没有⾛索引，则尽量是sql满⾜索引要求，具体参考最左前缀原则
2尽量让extra字段中使⽤了Using Index，效果最佳，Using where次之，其他清况需要优化
3 type字段⾄少要达到 range 级别
使⽤show profile查询SQL执⾏细节
Show Profile是mysql提供的可以⽤来分析当前会话中sql语句执⾏的资源消耗情况的⼯具，可⽤于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运⾏结果。
具体查询⽅式：
show profile cpu,block io for query Query_ID;/*Query_ID为#3步骤中show profiles列表中的Query_ID*/
⽐如执⾏：show profile cpu,block io for query 9;
在分析过程中尤其需要注意以下⼏种status:
正则表达式匹配符简写
①converting HEAP to MyISAM：查询结果太⼤，内存不够，数据往磁盘上搬了。
②Creating tmp table：创建临时表。先拷贝数据到临时表，⽤完后再删除临时表。
③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险
④locked。
如果在show profile诊断结果中出现了以上4条结果中的任何⼀条，则sql语句需要优化。
问题分类
通过前两种⽅式已经可以出究竟存在哪些慢sql。接下来需要通过⼀些⽅式、⽅法论来解决这些问题，慢sql的优化⽅式是⼀个颇具艺术性的问题，笔者总结了⼏种常见的问题和解决⽅案供⼤家参考。
没有命中索引及索引失效原因分析
⾸先分析⼀下为什么没有命中索引会导致慢查询。
索引本质就是通过列数据构建成的B+树结构。构建索引的⽬的是将⽆序的数据有序化，尽量避免全⽂扫描，提⾼检索效率。
没有命中索引意味着在查询的过程中没有⽅向，全⽂检索，效率极差。
⽽判断出sql使⽤了索引，但是仍然慢则需要进⾏索引优化了。
索引优化如何⼊⼿
造成索引失效的可能原因
mysql语句的执行顺序⾸先熟悉⼀下可能造成索引失效的原因
- 1、如果条件中有or，即使其中有部分条件带索引也不会使⽤(这也是为什么尽量少⽤or的原因)，例⼦中user_id⽆索引
注意：要想使⽤or，⼜想让索引⽣效，只能将or条件中的每个列都加上索引
- 2、对于复合索引，如果不使⽤前列，后续列也将⽆法使⽤，类电话簿。
- 3、like查询是以%开头
- 4、存在索引列的数据类型隐形转换，则⽤不上索引，⽐如列类型是字符串，那⼀定要在条件中将数据使⽤引号引⽤起来，否则不使⽤索引
- 5、where ⼦句⾥对索引列上有数学运算，⽤不上索引
- 6、where ⼦句⾥对有索引列使⽤函数，⽤不上索引
- 7、如果mysql估计使⽤全表扫描要⽐使⽤索引快，则不使⽤索引（⽐如数据量少的表）
总结下来，索引会失效⼤部分原因都是由于破坏了索引本⾝的连续性所导致的。对于单索引⽽⾔，采⽤like以%开头，⽆法到索引字段前缀元素，因此⽆法使⽤到索引，⽽对于联合索引⽽⾔，or / where 中带有运算&函数等，均是破坏了联合索引字段中的连续性，导致后续索引不到。
索引优化的⼏种常见场景
以下将对⼏种常见的索引场景进⾏实际分析优化
对于单索引且索引字段内容很长时的优化
1、构建前缀索引（使⽤短索引）：
默认索引是选择字符列的全部，那可以只选择索引开始的部分字符，例如，如果有⼀个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟⼀的，那么就不要对整个列进⾏索引。短索引不仅可以提⾼查询速度⽽且可以节省磁盘空间和I/O操作。这样就减少了索引的空间，从⽽提⾼索引效率。但这⾥有⼀个关键，是什么呢？如何选择前缀长度， 这⾥涉及到索引选择性的问题。在选择前缀长度时要考虑选择⾜够长的前缀以保证较⾼的选择性，同时⼜不能太长（以便节约空间），最后还要考虑数据分布。
⼀般，选择索引长度的⽅式，先计算出完整列的选择性，再选择出合适的前缀长度。
创建语句ALTER TABLE cityT ADD KEY(city(7));
计算完整列的选择性：SELECT COUNT(DISTINCT cityname)/COUNT(*)FROM cityT;
计算不同前缀长度的选择性：SELECT COUNT(DISTINCT LEFT(cityname,3))/COUNT(*)FROM cityT;
SELECT COUNT(DISTINCT LEFT(cityname,4))/COUNT(*)FROM cityT;
2、加⼊伪hash字段
新建⼀列⽤于存储该字符列的hash值（哈希函数不要使⽤SHA1(),MD5()，因为会产⽣很长的字符串，浪费空间，⽐较也慢，最好是返回整数的hash函数），在该列建⽴索引，查询时必须在where⼦句中包含常量值，以避免hash冲突。
例如
SELECT  id  FROM urlT WHERE url_hash =hash('www.blog.csdn')AND url ='www.blog.csdn'）。
对于联合索引，需要特别注意索引顺序
最左前缀原则：索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使⽤索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，⽐如<col2,col3>，<col1,col3>，col2，col3等等都是不能使⽤索引的。
根据最左前缀原则，我们⼀般把排序分组频率最⾼的列放在最左边，以此类推。
带索引的模糊查询优化
在上⾯已经提到，使⽤LIKE进⾏模糊查询的时候，’%aaa%'不会使⽤索引，也就是索引会失效。如果是这种情况，只能使⽤全⽂索引来进⾏优化（上⽂有讲到）。
解决⽅案：
覆盖索引，所谓覆盖索引，简单理解，就是每次select出来的字段仅从索引列就可以到，不需要回表查询。⽐如select (主键ID)。
改造写法（翻转）
table样式的表单将前模糊进⾏反转，转化成⾛后模糊的语句（注意逻辑变化）。
select count(c.c1)as COUNT from table1 c  where c.c1 = i.c1 and reverse(i.c1)like reverse(‘%245′)
使⽤翻转函数+like前模糊查询+建⽴翻转函数索引=⾛翻转函数索引，不⾛全扫描。有效降低消耗值，io值，cpu值这三个指标，尤其是io值的降低
超⼤分页的处理⽅法
⼀般的查询分页通常使⽤的是limit，但是limit的实现原理是，假设给定了limit 1000，10，即从第1000条后取出之后的10条数据，那么该条语句将会从表 中查询 offset:1000开始之后的10条数据，也就是第1001条到第1010条数据（ 1001<=id<=1010）。这种⽅式本质上还是会从数据库第⼀条记录开始扫描，所以越往后，查询速度越慢，⽽且查询的数据越多，也会拖慢总查询速度。
解决⽅案：⼦查询优化
这种⽅式先定位偏移位置的 id，然后往后查询，这种⽅式适⽤于 id 递增的情况。
select*from table1 where type=8and id =(select id from orders_history where type=8limit100000,1)
限定ID(仅限已知想要查询id的范围)
这种⽅式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使⽤ id between and 来查询：
select*from table1 where type=2and id between1000000and1000100limit100;