Mysql数据库慢查询以及优化点

概要

Sql查询速度慢的原因很多，但是如果通过一定的优化，也可以使查询问题得到一定程度的解决

速度慢原因分析

1. 没有索引或者没有用到索引（如果数据量大的时候使用时间做索引超过一定的量就会不走时间索引）
2. I/O吞吐量小，形成瓶颈
3. 内存不足，会拖累查询效率，有时候会导致数据崩
4. 网络速度慢，传输率低
5. 查询出的数据量大（可以使用分页limit进行查询）
6. 发生死锁，可以使用命令强制进行关闭，当然这个是程序设计的缺陷需要修改
7. 返回了不必要的列或者行，尤其针对表字段很多的情况
8. 查询语句拼接语句大小表的位置也有关系
9. 还有其他的很多的原因，此处省略很多字

优化细节

1. 把数据、日志、索引放到不同的I/O设备，增加读取速度，也就是读写进行分离
2. 横向或者纵向的进行分表，减少表的（sp_spaceuse），需要根据业务或者技术点进行划分
3. 根据查询条件，建立索引、优化索引，限制结果集的数量。不要对单一的字段建立一个单一的索引比如性别（0跟1）起不到所以的效果。建立索引的个数最好不超过6个，不然对编辑数据有影响。
4. 如果当前的sql中有可能走多个索引可以使用：

   强制索引
  		select * from 表名 force index(索引名) where …
  		忽略索引
  		select * from 表名 ignore index(索引名) where …
  		关闭缓存
  		select SQL_NO_CACHE * from 表名
  		强制缓存
  		select SQL_CACHE * from 表名 ignore index(索引名) where …

5. 扩大硬件设备，比如扩大内存，配置虚拟内存，增加cpu个数等，当然这个要投入对应的成本。
6. 如果使用like进行查询的话，尽量避免全文索引入like ‘%a%’,如果真的要使用模糊查询可以使用 like ‘a%’进行查询可以走索引。不能使用CHAR类型，而要用VARCHAR 进行建立索引。
7. 没有必要是不要用DISTINCT和ORDER BY，这些动作会在客户端进行，会增加额外的开销。
8. Commit和rollback的区别。Rollback:回滚所有的事务；Commit:提交当前的事务。没有必要在动态SQL里写事务，如果要写请写在外面，如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。
9. 尽可能不适用光标，因为会占用大量的资源，如果需要执行row-by-row，尽量采用非光标技术，如：用临时表，Table变量，Case语句等。
10. 用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引。
11. 注意UNion和UNion all 的区别：UNION ALL好
12. 可以使用EXPLAIN进行语句的分析，可以查看当前sql使用索引已经Extra额外的开销，可以分析是否走索引或者全表查询
13. 用sp_configure 'query governor cost limit’或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。SET LOCKTIME设置锁的时间
14. 一般数据库的引擎使用InnoDB,字符集使用utf8mb4，可以存储中文中有表情的字符。
15. Between在某些时候比IN速度更快，Between能够更快地根据索引找到范围。用查询优化器可见到差别。
16. 用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用UNION ALL执行的效率更高。
17. 尽量少用视图，因为效率比较低，对视图操作比对标查询慢。
18. 在开发过程中尽量的不适用存储过程，因为存储过程维护成本高，调试不方便，尽量逻辑在代码中实现。
19. 在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。
20. 在mysql5.6以前的时候修改索引或者修改字段会进行锁表，操作时候需要注意，5.7版本之后就不会进行锁表了。
21. 在更新的时候如果有多条记录尽量使用批量更新，效率会比单条的高。