排查方法

1. 可以打开MySQL的慢查询日志

2. 或执行SHOW PROCESSLIST：

   
   
   SHOW PROCESSLIST

3. 关注连接数是否存在问题：show status；

   
   
   show status

explain语句结果

• consts（最优）：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
• ref：使用普通的索引
• range：对索引进行范围检索
• index（差）：索引物理文件全扫
• ALL（最差）：全表扫描

优化方法

1. 尽量避免使用子查询（创建临时表），如：
   SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name = 'chackca');
   虽然MySQL5.6版本采用join对其进行了优化，转化为：
   SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id
   但该优化仅对SELECT有效，UPDATE/DELETE子查询无效，建议使用inner join
2. 高效性：between>in>or
   SELECT * FROM t WHERE id = 1 OR id = 2 OR id = 3;
SELECT * FROM t WHERE id IN (1,2,3);
SELECT * FROM t WHERE id between 1 and 3;
   MySQL中between包含两个端点
3. limit优化
   select id,name from t limit 866613, 20
   改为
   select id,name from table_name where id> 866612 limit 20
4. 禁用不必要的Order By排序
   SELECT goods_id,count(*) FROM t GROUP BY goods_id;
   MySQL默认对Group By的字段进行排序，所以可以手动禁止排序：
   SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL
5. 确定数据不会重复时，union改为union all
   SELECT aid,title FROM article UNION SELECT bid,title FROM blog
   ↑↑↑MySQL需要对两张表的数据进行唯一性过滤
   SELECT aid,title FROM article UNION ALL SELECT bid,title FROM blog
   ↑↑↑不进行去重操作
6. 随机函数，无法用到索引
   SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
7. 将多次插入换成批量Insert插入(减少IO开销)
8. 只返回必要的列
   减少cpu、io、内存、网络带宽等消耗，如果查询的字段都在索引中，还可以直接从索引中获取字段信息直接返回
9. in和exists的选择
   select * from 表A where id in (select id from 表B)
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
   如果表B中结果少，则选择in，表B作为驱动表
   如果表A中结果少，则选择exists，表A作为驱动表
10. 优先选择使用数字类型
    引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
11. join：保证驱动表是小结果集，被驱动表的join字段加上索引

• left join 前面的表是驱动表，后面的表是被驱动表
• right join 后面的表是驱动表，前面的表是被驱动表
• inner join / join 会自动选择表数据比较少的作为驱动表
• straight_join(≈join) 直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）

索引失效的情况（仅列举了容易让人忽略的部分情况）

1. 字符串类型，必须带“”引号进行查询（即：不在索引列上做任何操作）
   select * from user where phone = 13030303030；
   相当于
   select * from user where CAST(phone AS signed int) = 13030303030;
   原因是：当遇到字符串和数字比较时，会将字符串转为数字
2. or导致索引失效
   select * from t_user where name = 'z' or age = 18;
   其中name有索引，age没有，则无法使用索引

MySQL优化五大方面：

1. 硬件及Server配置优化：连接池大小、缓存大小
2. 表结构优化：分库分表、读写分离
3. 加索引
4. SQL优化
5. 结合Redis、ES等其它中间件