参考
陈川大佬的博客
https://www.jianshu.com/p/d7665192aaaf
索引的类型
普通索引、唯一性索引、主键索引，也可以分为 主索引、辅助索引
- 普通索引的作用：
- 条件查询： where id=1
- 范围查询： where id<200， between and， in
- 模糊后缀匹配查询： like 'hello%'
- 排序： order by 重点注意：非主键的普通索引，如果查询的内容里面还有其他字段则不走索引，只查询索引字段的的话走索引
//对info创建索引，如果查询其他字段的话，就不走索引而是大表扫描
select * from class a order BY info ;
//对info创建索引，如果只查询索引字段的话，就走索引，不需要查询主表了
select info from class a order BY info ;
- 分组查询
- 表和表关联查询： join 和 left join、right join的逻辑不通
- 唯一性索引：基于普通索引的结构，但是value值只能对应一个数据。唯一性索引查询的时候，也会很快。
- 主键索引：是唯一索引的特殊类型，主键不可为空，唯一索引可以为null，每张表只能有1个主键，而且InnoDB的主键索引是聚簇索引，范围查询特别快。
索引的使用规范
- 禁止在频繁变更的表上添加索引
- 删除不再使用的索引，同时要优化索引，避免重复功能的索引(单列索引和符合索引重复）
- 联合索引应该把区分度更大的字段放前面，效果更好
- Innodb 不建议使用过长的字段作为主键，因为是聚集式索引，其他索引会存储不压缩的主键
- 最左匹配原则有2个含义：like匹配通配符不能在前面、联合索引顺序也是按照从左到右的顺序
- 索引字段 查询条件中含有函数或表达式，索引无效：比如 emp_no -1=3， left(title,6）=‘huangz'
- 少基数的字段不建议使用索引，性能提升不明显，同时要DML的时候要维护索引，空间和性能上浪费。比如男女字段，比例大约1:1，提升不明显，如果男女比率 1000:1，那么查询女的用户的时候，性能提升明显。
- mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
- 字符串使用短索引，可以提高查询速度和节省磁盘空间和I/O操作
limit的优化
其实limit的性能并不好，特别是在limit的初始值较大的时候，比如 limit10000，20， 默认会顺序扫描到10000下标的数据，然后再继续扫描20个数据返回。
所以优化的方案：
- 根据业务场景，可以尝试用范围查找替代 limit查找
比如换成 where ID>10000 limit 20，这样如果ID是主键的话，或者是索引，那么只一共扫描20行的数据。
但是下面2个SQL的查询结果可能并不一样，因为ID可能是不连续的
- 如果不能用范围查找来替代的话，可以利用 limit+覆盖索引 来降低IO的开销以及 扫描的行数
//性能最差
select * from test limit 90000,5;
//性能稍微改善，因为只需要扫描 900005行的id，再根据获取到的id 去匹配5行的大表里面的全数据
select a.* from test a join (select id from test limit 90000,5) b on a.id=b.id;
//性能最佳，但是要求id必须连续才行
select * from test where id>90000 limit 5;
覆盖索引
ID为主键，name为辅助索引，所以 SQL2 SQL3可以使用覆盖索引，而SQL1 查询条件里面有 sex字段，所以只能走全表扫描
InnoDB的主键索引是聚簇索引。
直接在辅助索引上面获取（索引字段、主键字段）是不需要去主索引中读取数据，直接从 辅助索引里面获取就可以了
EXPLAIN select id,name,sex from user order by name limit 1000,10; //全表扫描 0.015秒
EXPLAIN select id from user order by name limit 1000,10; //索引1010行 0.001秒
EXPLAIN select name from user order by name limit 1000,10; //索引1010行 0.001秒
注意的坑
-
如果统计行数的话，count(*)比count（Column) 更好。第二个会统计所有该column不为null的总行数，会耗费更多的搜索资源。但是如果统计某列的值不为Null的总行数，必须要用第二种统计方法。
-
如果字段有表达式，那么就不会走索引,例如下面的 id+1=500，就会走全表扫描 ，但是值是支持表达式的，仍然会走索引
EXPLAIN select * from wx_ib where id+1=500; //不走索引
EXPLAIN select * from wx_ib where id=500-1; //使用索引
- 索引的选择
- 小表不要建索引，全表扫描更快
- 删除掉不常用的索引，因为每次DML都要维护索引
- 避免创建重复的索引，比如表中已经有索引 Index(name)，如果要添加联合索引的话，修改成Index（name,address)，而不是再创建一个新的联合索引。
- 超大型表的话，分区、分库可能是更好的选择，维护索引的成本太高了
-
海量数据的业务场景下情况下，反范式的冗余字段，来避免表和表之间的关联查询，可能更有效。比如学生表里面，包含了学生成绩，这样就不需要关联成绩表，但是更新成绩的时候，要同时更新 成绩表和学生表。
-
两张表做关联查询的时候：关联字段需要创建索引，提升效率
join 和 left join、 right join不一样
join的时候: 在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表
- 普通查询：只要在一张表上创建索引既可以，先后顺序无所谓(例如SQL1 和SQL2）
- 关联查询+条件查询：两张表都创建关联字段的索引，这样查询的时候会走2个索引（例如SQL3)
-- SQL1
select * from class a join student_test b on a.info=b.info ;
-- SQL2
select * from student_test a join class b on a.info=b.info ;
-- SQL3
select * from class a join student_test b on a.info=b.info where a.info like '100_';
left join 或者 right join的时候，需要在从表上创建索引
例如下面的主表是 class表，那么需要在从表 student_test表的 info字段上创建相应的索引
select * from class a left join student_test b on a.info=b.info ;
网友评论