450-MySQL(索引常见问题,慢查询日志)

索引常见问题

问题1

默认是在InnoDB存储引擎。
首先，最基本的，userid要加索引（因为一般用过滤条件加索引），此时创建的是二级索引树，select *还要涉及回表。而且还有order by addtime，如果addtime没有建索引的话，explain分析时会出现using filesort（涉及addtime的文件排序）！
所以，我们要加联合索引（多列索引）：userid+addtime
这样的话，选出来的addtime在二级索引树上就已经是有序的了

有using filesort的差别，数据的外排序。用userid=123选择出来的数据越多，耗费的排序性能差的越大。





例如，看下图，sex创建索引了，但是没有用到，这是MySQL server的一个优化，过滤出来的数据量已经占了整表的大部分，就没有必要用索引了。

问题2

一张表的一次查询只能用到1个索引，它看用a=1过滤的数据少还是b=2过滤的少，用拿个索引搜出来的数据少的话，就用哪个索引。

问题3

问题4

小表是整表扫描，每一个都要用到，大表相关联的字段没有索引的话，小表的每一个uid在大表中搜索都是整表搜索。如果大表相关联的字段有索引，小表的每一个uid在大表中就是走索引，搜的非常快。

问题5

第一个可以用前缀搜索。第二个就很清楚想明白，前面是通配符，不知道是什么数据，所以只能整张表搜索。

通配符出现在后面，还是可以用到索引的。

in可以用到索引

not in也会用到索引。这是MySQL的优化！！！

mysql server相当于优化成：

not in 可以转化为范围range搜索查询

or实际上也可以用到索引，MySQL会对or进行优化成union联合查询
age和name分别在两个select中，都可以用到索引的

所以说，走不走索引看mysql server的优化！！！

此时MySQL选择不优化成or的范围搜索，直接在主键索引树上整表搜索，它觉得这样更快！

总结

SQL和索引的优化问题

我们使用explain去分析SQL语句
但是真正的企业级项目有上千条万条SQL
我们不可能从头开始一条一条explain去分析。也不是所有的SQL都慢。
从什么地方可以获取那些运行时间长，耗性能的SQL，然后再用explain去分析它？？？
我们可以打开慢查询日志：
根据具体的业务和并发量来预估1个时间，比如说100毫秒，20毫秒，设置好后开启业务，压测过程中，如果打开慢查询日志，就会看到超过执行时间的SQL，然后就用explain
步骤如下：
1、设置合理的、业务可以接受的慢查询时间
2、压测执行各种业务
3、查看慢查询日志，找出所有执行耗时的SQL语句
4、用explain分析这些耗时的SQL语句
5、举例子
比如说：在where加order by，explain时发现有using filesort 外部排序，数据都是在磁盘上放，没有建立合适的索引的话，只能进行外部排序了。用where过滤条件和order by排序的字段建立联合索引。
没有加索引，索引没用到，类型强转，过滤条件用了MySQL的函数，都用不到索引，要优化。

慢查询日志

slow_query_log

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等。

慢查询日志相关的参数如下所示：
（MySQL定义的很多的全局的开关，都是在全局变量中存储，可以用show/set variables查看或者设置全局变量的值）

慢查询日志开关：默认是关闭的。
慢查询日志的路径：默认在数据路径。
慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日志，在MySQL上用命令可以查看，如下：

这个值是可以修改的，如下：

现在修改成超过1秒的SQL都会被记录在慢查询日志当中！可以设置为0.01秒，表示10毫秒。慢查询日志，默认名称是host_name-slow.log，存放在MySQL的数据路径下，内容格式显示大致如下：

通过查询慢查询日志，发现项目运行过程中，上面这条SQL语句的执行时间超过了设定的慢查询时间，那么接下来就需要用explain分析一下该SQL的执行计划了，根据具体情况找出SQL和索引该怎么去优化。

show profiles命令可有查看sql具体的运行时间，全局变量的名字是：profiling
举个例子：

像这种查询的时间在小数点后2位也体现不出来运行时间，如果想再优化它，就要使用profiling

没有要求是global，说明这个变量的设置只和当前的session有关而已。
可以看到：

会把之前所做的所有的SQL语句操作的耗时时间显示出来。精确度还是很高的。我们可以根据它来进行更加的优化措施。
Query_ID是查询的标号。duration是耗时的时间。query是具体的SQL语句。

实践过程展示

global就是全局的，只要和同一个mysql server连接，都是有影响的。


我们要更改一下：

我们在另外一个mysql client 查看一下：

慢查询日志的开关是全局的！！！

但是这样的时间变量的改动只是对当前的mysql client起作用而已
我们的t_user表有200万个数据，来测试看看：

3.21秒了。我们去查看慢查询日志：


耗时间的SQL显示在文件里了！！！
然后我们抽出来去explain一下：

做了整表的搜索。把主键索引树整个扫了一遍。
我们应该给password添加索引。然后记得password是字符串格式的书写哦。因为如果涉及类型转换是用不了索引的。