MySQL入门之索引基础

MySQL入门之索引基础

索引数据结构为b+树

优点：

1. 大大减少了扫描量
2. 避免排序和临时表
3. 将随机io变为顺序io

主键索引：

主键索引的叶子节点保存着主键即对应行的全部数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

二级索引（非主键索引）： 二级索引树中的叶子结点保存着索引值和主键值，当使用二级索引进行查询时，需要进行回表操作。在InnoDB里，非主键索引也被称为二级索引（secondary index）

联合索引

最左匹配原则,mysql会从左向右进行匹配。
最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

例如我们定义了(name,password)两个联合索引字段，我们使用

where name='张三' and password='2'

索引可以生效的，当我们是颠倒了他们的顺序使用

where password='1' and name='王五'

，索引同样也是可以生效的，在mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划

覆盖索引

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中（联合索引），可以直接使用索引查询而不需要回表。这就是覆盖索引。

索引下推

如果现在有一个需求：检索出表中“名字第一个字是张，而且没有删除的信息(is_del = 1)。

SQL语句如下：

mysql> select * from tuser where username like '张%' and isdel=1

在MySQL 5.6之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值

在MySQL 5.6中 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

根据（username,is_del）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。

索引失效情况

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
2. like查询是以%开头
3. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
4. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
5. 对于组合索引，不是使用的第一部分，则不会使用索引