MySQL定位慢查询以及索引优化

(一)定位慢查询
1.通过explain，可以查看sql语句的执行情况（比如查询的表，使用的索引以及mysql在表中找到所需行的方式等），一般情况下，基本可以通过explain，主要是通过其查找行的方式和查找行数确定问题，因为全表扫描很浪费时间，占用cpu。

2.通过show profiles查找出执行sql语句的query_id，接着执行show profile for query_id 看到执行过程中线程的每个状态的消耗时间，可以获取最消耗时间的线程状态。在SQL优化中帮助我们了解时间消耗都去哪里了

3.trace命令,查看优化器是如何选择执行计划
1)设置格式为json和trace最大能够使用的内存大小

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=100000;

2)检查information_schema.optimizer_trace，可以得到mysql如何执行sql的

select * from information_schema.optimizer_trace\G

(二)索引优化
一般通过索引优化就能解决大多数的SQL性能问题
索引优化就是建立合理的索引，建立索引的几条策略(个人觉得更像是规定)
1)独立的列，即索引列不能是表达式的一部分，也不能是函数的参数
2)前缀索引，可以减少索引占用的内存。适用于like ‘xxx%’的sql语句优化
如何确定合适的前缀索引长度。
方法一：

select count(*)  as cnt,left(city,x) as pref from city_demo group by pref order by cnt desc limit

前十列的cnt 和

select count(*) as cnt,city as pref from city_demo group by pref order by cnt desc limit

的前十列的值接近,则x为最合适的前缀长度。x一般是不会从1开始，应该从一个相对较大的数开始增加，直到找到合适的x
方法二：

select count(distinct left(city, x)) /count(*) as sel1

的值等于

select count(distinct city) /count(*) as sel1

的值，则x为合适的x
其中count(distinct city) /count(*)为索引选择性。
文字描述什么是索引选择性：不重复的索引值和数据表的总记录数的比值
索引的选择性越高则查询效率越高。因为选择性高的索引可以让mysql在查询时过滤掉更多的行。
3)多列索引
在多个列上建立独立的单独索引并不能提高mysql的查询性能，合并索引，使用多个单列索引来定位指定行。但是又不能随便建立多个索引，索引的顺序也会影响到查询速度。
如果索引有多列，如何确定索引顺序？选择性高的作为索引第一列，如何计算选择性上面已介绍

（三）使用索引需要注意的问题
数据库默认使用InnDB作为存储引擎，而B-Tree是InnoDB索引的默认类型
支持：
1)全值匹配。与索引中的所有列进行匹配
2)匹配最左边 比如前缀索引
3)匹配范围值 即索引列在一个范围内
4)精确匹配某一列并范围匹配另外一列
5)特殊的覆盖索引
不支持：
1)最左原则：不是从最左列开始。比如索引为index1,index2,index3，而查询中为index2,index3，则优化器不会选择索引进行优化操作
2)不能跳过中间列，如果跳过则使用第一列的索引，后面的列将不会使用索引。比如index1,index3,中间跳过了index2
3)如果查询中有某个列的查询范围，则其右边的所有列都无法使用索引优化查询
4)以%开头的like不能使用，如果是这种sql语句，则推荐使用全文索引进行优化
5)数据类型出现隐式转化。一般当索引为数值类型的字符串时才会出现这种小错误
6)如果使用索引比全表扫描更慢，则不会使用索引。个人理解：优化器选择能匹配到sql列的优化语句，再试依次使用优化器去执行sql语句，记录执行时间，执行时间之间进行比较，同时，也会和不使用索引的sql语句执行时间进行比较。选择当中最小的一个。
7)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，则不会使用索引

(四)查看索引使用情况
通过命令：

show status like 'Handle_read'

衡量使用索引的指标，一是 Handler_read_key，代表一个行被索引值读的次数，越小则代表增加索引得到的性能改善不高，因为索引不常用到。二是Handler_read_rnd_next，越大，则代表查询运行低效，应该建立索引。