MYSQL高性能优化规范建议
- 没有特殊要求所有表必须使用 Innodb 存储引擎
- 禁止使用存储过程,视图,触发器,Event
- 优先选择符合存储需要的最小的数据类型
- 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
- 尽可能把所有列定义为 NOT NULL
常见索引列建议
- 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
- 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
- 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
- 多表 join 的关联列
索引列的顺序
- 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
- 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
- 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
对于频繁的查询优先考虑使用覆盖索引
覆盖索引：就是包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引
覆盖索引的好处：
- 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
避免数据类型的隐式转换
隐式转换会导致索引失效如:
select name,phone from customer where id = '111';
充分利用表上已经存在的索引
避免使用双%号的查询条件。如：a like '%123%'，（如果无前置%,只有后置%，是可以用到列上的索引的）
一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。
在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。
几种情况导致索引失效
- 索引列上做计算,函数,类型转换
- 使用不等于(
!=或<>) ,is null ,is not null 会导致全表扫描(新版本的MySQL中这些已经可以使用索引了) - 使用like以通配符开头会导致全表扫描
- 当于表中字段类型为varchar，且值为数字，如果没有带引号，则不会命中索引
- 使用or或in ,MySQL内部优化器可能不走索引
order by优化
- 如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句
- 如果文件排序没办法避免，那么尽量想办法使用覆盖索引
group by优化
group by的原理是先排序后分组，因此对于group by的优化参考order by
分页优化
-- 通过先进行覆盖索引的查找，然后再使用join做连接查询获取所有数据。这样比全表扫描要快
select * from dept a inner join (select id from dept order by name limit 10000,10) b on a.id = b.id;
join优化
在jion中会涉及到大表（数据量大）和小表（数据量小）的概念。MySQL内部优化器会根据关联字段是否创建了索引来使用不同的算法
- NLJ(嵌套循环算法)：如果关联字段使用了索引，MySQL会对小表做全表扫描，用小表数据去和大表的数据去做索引字段的关联查询
- BNLJ(块嵌套循环算法)：如果关联字段没有使用索引，MySQL会提供一个join buffer缓冲区，先把小表放到缓冲区中，然后全表扫描大表，把大表的数据和缓冲区中的小表数据在内存中进行匹配
结论：使用join查询时，一定要建立关联字段的索引，且两张表的关联字段在设计之初就要做到字段类型、长度是一致的，否则索引失效