排查方法
- 可以打开MySQL的慢查询日志
-
或执行SHOW PROCESSLIST：
SHOW PROCESSLIST -
关注连接数是否存在问题：show status；
show status
explain语句结果
- consts（最优）：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
- ref：使用普通的索引
- range：对索引进行范围检索
- index（差）：索引物理文件全扫
- ALL（最差）：全表扫描
优化方法
- 尽量避免使用子查询（创建临时表），如：
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 - 高效性：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包含两个端点 -
limit优化
select id,name from t limit 866613, 20
改为
select id,name from table_name where id> 866612 limit 20 - 禁用不必要的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 - 确定数据不会重复时，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
↑↑↑不进行去重操作 - 随机函数，无法用到索引
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4; - 将多次插入换成批量Insert插入(减少IO开销)
- 只返回必要的列
减少cpu、io、内存、网络带宽等消耗，如果查询的字段都在索引中，还可以直接从索引中获取字段信息直接返回 - 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作为驱动表 - 优先选择使用数字类型
引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了 - join：保证驱动表是小结果集，被驱动表的join字段加上索引
- left join 前面的表是驱动表，后面的表是被驱动表
- right join 后面的表是驱动表，前面的表是被驱动表
- inner join / join 会自动选择表数据比较少的作为驱动表
- straight_join(≈join) 直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）
索引失效的情况（仅列举了容易让人忽略的部分情况）
- 字符串类型，必须带“”引号进行查询（即：不在索引列上做任何操作）
select * from user where phone = 13030303030；
相当于
select * from user where CAST(phone AS signed int) = 13030303030;
原因是：当遇到字符串和数字比较时，会将字符串转为数字 - or导致索引失效
select * from t_user where name = 'z' or age = 18;
其中name有索引，age没有，则无法使用索引
MySQL优化五大方面：
- 硬件及Server配置优化：连接池大小、缓存大小
- 表结构优化：分库分表、读写分离
- 加索引
- SQL优化
- 结合Redis、ES等其它中间件
