Linux系统——Mysql索引补充知识

目录

一、索引介绍

1.索引的优点

2.索引的分类

3.索引的技术名词

3.1回表

3.2覆盖索引

3.3最左匹配

3.4索引下推

4.索引匹配方式

4.1全值匹配

4.2最左前缀匹配

4.3匹配列前缀

4.4匹配一个范围值

4.5精确匹配某一列并范围匹配另一列

4.6只访问索引的查询

---

一、索引介绍

1.索引的优点

• 大大减少服务器需要扫描的数据量，也就是IO量
• 帮助服务器避免排序和临时表（尽量避免文件排序，而是使用索引排序）
• 将随机IO变成顺序IO

2.索引的分类

数据库默认建立的索引是给唯一键建立的

• 主键索引(唯一且非空)
• 唯一索引(唯一可为空)
• 普通索引(普通字段的索引)
• 全文索引(一般是varchar,char,text类型建立的，但很少用)
• 组合索引(多个字的建立的索引)

3.索引的技术名词

3.1回表

name字段是普通索引，从name列的B+树找到主键，再从主键的B+树找到最终的数据，这就是回表。(主键索引的叶子节点保存的是列的所有数据，但是普通所有的叶子结点保存的是对应的主键ID）

如图：一个use表中name建立的索引结构sql是select * from use where name='sun'首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据，并返回，这个就是回表。

3.2覆盖索引

在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。

如上图name索引，sql是 select id,name from user where name ="1" ，id的值在第一步非主键索引就已经有了，就不需要根据ID到主键索引中查询行数据了。

3.3最左匹配

组合索引中 先匹配左边，再继续向后匹配；比如user表中有name+age组成的联合索引，select * from user where name="郭先生" and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。

如果索引是name+age的组合索引，sql是select * from user where age = 18 and name="郭先生"很多人认为这种是不能走索引，实际上可以的。mysql的优化器会优化调整顺序的，调整成 name="郭先生" and age = 18

3.4索引下推

组合索引中尽量利用索引信息，来尽可能的减少回表的次数

还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

4.索引匹配方式

4.1全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

mysql> alter table staff add index index_n1(first_name,last_name,username);

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'

其中的ref是三个const， 用到三个字段，能全匹配一条数据

4.2最左前缀匹配

只匹配组合索引中前面几个字段

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

4.3匹配列前缀

可以匹配某一列的的开头部分，像like属性

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用

4.4匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

4.5精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

4.6只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。

总的来说，只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对应很小的表，大部分情况下没有索引，全表扫描更高效；对应中大型表，索引时非常有效的；但是对于超大的表，索引的建立和使用代价也就非常高，一般需要单独处理特大型的表，例如分区，分库，分表等。