索引的类型

Mysql 索引概念与使用

普通索引、唯一性索引、主键索引，也可以分为 主索引、辅助索引

  1. 普通索引的作用：
  • 条件查询： where id=1
  • 范围查询： where id<200， between and， in
  • 模糊后缀匹配查询： like ‘hello%’
  • 排序： order by 重点注意：非主键的普通索引，如果查询的内容里面还有其他字段则不走索引，只查询索引字段的的话走索引
  1. //对info创建索引，如果查询其他字段的话，就不走索引而是大表扫描
  2. select * from class a  order BY  info  ;
  4. //对info创建索引，如果只查询索引字段的话，就走索引，不需要查询主表了
  5. select info from class a  order BY  info ;
  • 分组查询
  • 表和表关联查询： join 和 left join、right join的逻辑不通
  1. 唯一性索引：基于普通索引的结构，但是value值只能对应一个数据。唯一性索引查询的时候，也会很快。
  2. 主键索引：是唯一索引的特殊类型，主键不可为空，唯一索引可以为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个数据返回。
所以优化的方案：

  1. 根据业务场景，可以尝试用范围查找替代 limit查找
    比如换成 where ID>10000 limit 20，这样如果ID是主键的话，或者是索引，那么只一共扫描20行的数据。
    但是下面2个SQL的查询结果可能并不一样，因为ID可能是不连续的
  2. 如果不能用范围查找来替代的话，可以利用 limit+覆盖索引 来降低IO的开销以及 扫描的行数
  1. //性能最差
  2. select * from test   limit 90000,5;
  4. //性能稍微改善，因为只需要扫描 900005行的id，再根据获取到的id 去匹配5行的大表里面的全数据
  5. select a.* from test a join (select id from test limit 90000,5) b on a.id=b.id;
  7. //性能最佳，但是要求id必须连续才行
  8. select * from test where id>90000 limit 5;

覆盖索引

ID为主键，name为辅助索引，所以 SQL2 SQL3可以使用覆盖索引，而SQL1 查询条件里面有 sex字段，所以只能走全表扫描
InnoDB的主键索引是聚簇索引。
直接在辅助索引上面获取（索引字段、主键字段）是不需要去主索引中读取数据，直接从 辅助索引里面获取就可以了

  1. EXPLAIN select id,name,sex  from user order by name limit 1000,10;    //全表扫描 0.015秒
  2. EXPLAIN select id           from user order by name limit 1000,10;   //索引1010行 0.001秒
  3. EXPLAIN select name         from user order by name limit 1000,10;  //索引1010行 0.001秒

注意的坑

  1. 如果统计行数的话，count(*)比count（Column) 更好。第二个会统计所有该column不为null的总行数，会耗费更多的搜索资源。但是如果统计某列的值不为Null的总行数，必须要用第二种统计方法。
  2. 如果字段有表达式，那么就不会走索引,例如下面的 id+1=500，就会走全表扫描 ，但是值是支持表达式的，仍然会走索引
  1. EXPLAIN select * from wx_ib where id+1=500; //不走索引
  2. EXPLAIN select * from wx_ib where id=500-1; //使用索引
  1. 索引的选择
  • 小表不要建索引，全表扫描更快
  • 删除掉不常用的索引，因为每次DML都要维护索引
  • 避免创建重复的索引，比如表中已经有索引 Index(name)，如果要添加联合索引的话，修改成Index（name,address)，而不是再创建一个新的联合索引。
  • 超大型表的话，分区、分库可能是更好的选择，维护索引的成本太高了

  1. 海量数据的业务场景下情况下，反范式的冗余字段，来避免表和表之间的关联查询，可能更有效。比如学生表里面，包含了学生成绩，这样就不需要关联成绩表，但是更新成绩的时候，要同时更新 成绩表和学生表。

  2. 两张表做关联查询的时候：关联字段需要创建索引，提升效率
    join 和 left join、 right join不一样

join的时候: 在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表

  • 普通查询：只要在一张表上创建索引既可以，先后顺序无所谓(例如SQL1 和SQL2）
  • 关联查询+条件查询：两张表都创建关联字段的索引，这样查询的时候会走2个索引（例如SQL3)
  1. -- SQL1
  2. select * from class a join student_test b on a.info=b.info ;
  3. -- SQL2
  4. select * from student_test a join class b on a.info=b.info ;
  6. -- SQL3
  7. 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字段上创建相应的索引

  1. select * from class a  left join student_test b on a.info=b.info ;