mysql语句查询优化技巧

检查问题常用的 12 个工具：

• MySQL
• mysqladmin：MySQL 客户端，可进行管理操作
• mysqlshow：功能强大的查看 shell 命令
• SHOW [SESSION | GLOBAL] variables：查看数据库参数信息
• SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息
• information_schema：获取元数据的方法
• SHOW ENGINE INNODB STATUS：Innodb 引擎的所有状态
• SHOW PROCESSLIST：查看当前所有连接的 session 状态
• explain：获取查询语句的执行计划
• show index：查看表的索引信息
• slow-log：记录慢查询语句
• mysqldumpslow：分析 slowlog 文件的工具

不常用但好用的 7 个工具：

• Zabbix：监控主机、系统、数据库（部署 Zabbix 监控平台）
• pt-query-digest：分析慢日志
• MySQL slap：分析慢日志
• sysbench：压力测试工具
• MySQL profiling：统计数据库整体状态工具
• Performance Schema：MySQL 性能状态统计的数据
• workbench：管理、备份、监控、分析、优化工具（比较费资源）

1、EXPLAIN

explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看

for example: explain select * from table

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

         type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
         一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

2、SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，如果是连续数字型的，如：select id from t where num in(1,2,3) 用between替代in了，减小内存消耗；where子句中使用in或not in关键字时，会导致全表扫描，能使用exists或between and替代就不使用in

3、SELECT语句务必指明字段名称

SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。少用select * ，进来用select columns1、columns2、columns3 from table

4、查看数据结构，使用limit 1，减少内存消耗

5、如果排序字段没有用到索引，就尽量少排序

6、如果限制条件中其他字段没有索引，尽量少用or

 

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果

7、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。在没有重复数据的前提下，用union all替代union。

8、不使用ORDER BY RAND()

9、区分in和exists、not in和not exists

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题

原SQL语句：select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

10、使用合理的分页方式以提高分页的效率

第一种 在索引上完成排序分页操作，然后根据主键关联回原表查询所需要的其他列的内容； 
第二种 在排序字段不会出现重复值的情况下，新增一个参数记录上次查询的最后一条记录，将limit m,n转化成limit n.

11、分段查询

12、避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描

13、不建议使用%前缀模糊查询，使用REGEXP，比如代替like

例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”,

建立索引会更加高效的查询

14、避免在where子句中对字段进行表达式操作

where子句中应尽量避免对索引字段操作（表达式操作或函数操作），比如select id from test where num/2 = 100应改为num = 200。

15、避免隐式类型转换

查询时必须使用正确的数据类型。数据库包含了自动了类型转换，比如纯数字赋值给字符串字段时可以被自动转换，但如果查询时不加引号查询，会导致引擎忽略索引。

16、对于联合索引来说，要遵守最左前缀法则

17、必要时可以使用force index来强制查询走某个索引

18、注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，应尽量避免在 where 子句中使用!=或<>操作符。

19、JOIN优化

20、避免大事务操作，提高并发能力

21、mysql执行顺序优化：from--where--groupby--having--select--orderby--limit

首先我是不是需要知道我要从哪个表去获取我想要的，也就是from；现在我知道从哪个表获取了，可是并不是这个表里面所有的信息都是我需要的，我需要把一些不需要的去掉(比如测试订单)，或是把一些我需要的筛选出来，这就是where；现在我把我需要的订单明细筛选出来，可是我想要每个品类的订单量，这个时候是不是需要做一个分组聚合，也就是group by；分组聚合后的结果也并不是我们全部都要，我们只要大于10的品类，所以需要把大于10的筛选出来，非大于10的品类过滤掉，这就是having；现在我们想要的大部分信息都已经出来了，我们就可以用select把他们查询出来了；因为我们最后需要取前三的品类，所以我们需要把查询出来的结果进行一个降序排列，即order by；最后一步就是只把前三显示出来，做一个限制就行，也就是limit。

参考文章1：19条效率至少提高3倍的MySQL技巧

参考文章2：mysql explain用法和结果的含义