Mysql索引索引有什么用( 三 ) _小知识

例如在a和b字段上建立联合索引，索引结构将如下图所示：

文章插图

一目了然，当我们再执行SELECT score FROM student WHERE name='叶良辰';时，可以直接通过扫描非聚集索引直接获取score的值，而不再需要到聚集索引上二次扫描了。
最左前缀匹配
联合索引中有一个重要的课题，就是最左前缀匹配。
最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
这是为什么呢？我们再仔细观察索引结构，可以看到索引key在排序上，首先按a排序，a相等的节点中，再按b排序。因此，如果查询条件是a或a和b联查时，是可以应用到索引的。如果查询条件是单独使用b，因为无法确定a的值，因此无法使用索引。
假如在table表的a,b,c三个列上建立联合索引，简要分类分析下联合索引的最左前缀匹配。
首先看等值查询：
1、全值匹配查询时（where子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序），可以用到联合索引
SELECT * FROM table WHERE a=1 AND b=3 AND c=2SELECT * FROM table WHERE b=3 AND c=4 AND a=2
2、匹配左边的列时，可以用到联合索引
SELECT * FROM table WHERE a=1SELECT * FROM table WHERE a=1 AND b=3
3、未从最左列开始时，无法用到联合索引
SELECT * FROM table WHERE b=1 AND b=3
4、查询列不连续时，无法使用联合索引（会用到a列索引，但c排序依赖于b，所以会先通过a列的索引筛选出a=1的记录，再在这些记录中遍历筛选c=3的值，是一种不完全使用索引的情况）
SELECT * FROM table WHERE a=1 AND c=3
再看范围查询：
1、范围查询最左列，可以使用联合索引
SELECT * FROM table WHERE a>1 AND a<5
2、精确匹配最左列并范围匹配其右一列（a值确定时，b是有序的，因此可以使用联合索引）
SELECT * FROM table WHERE a=1 AND b>3;
3、精确匹配最左列并范围匹配非右一列（a值确定时，c排序依赖b，因此无法使用联合索引，但会使用a列索引筛选出a>2的记录行，再在这些行中条件 c >3逐条过滤）
SELECT * FROM table WHERE a>2 AND c>5;
索引的原理探究到此结束，这部分内容堪称最难啃的骨头。不过，能坚持读下来的朋友，你的收获也一定良多。接下来的内容就轻松愉悦多了。

文章插图
2 索引的正确使用姿势索引的优点如下：

通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这是使用索引最主要的原因。
在实现数据的参考完整性方面可以加速表与表之间的连接。
在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

既然索引这么好，那么我们是不是尽情使用索引呢？非也，索引优点明显，但相对应，也有缺点：

创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。
当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

因此，使用索引时要兼顾索引的优缺点，寻找一个最有利的平衡点。
2.1 索引的类型区分以InnoDB引擎为例，Mysql索引可以做如下区分。
首先，索引可以分为聚集索引和非聚集索引，它们的区别和含义在前文有大幅介绍，此处不再赘述。
其次，从逻辑上，索引可以区分为：

普通索引：普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
唯一索引：唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。例如在student 表中的 id 字段上建立名为 index_id 的索引CREATE UNIQUE INDEX index_id ON tb_student(id);
主键索引：主键索引就是专门为主键字段创建的索引，也属于索引的一种。主键索引是一种特殊的唯一索引，不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
- 上一页
- 1
- 2
- 3
- 4
- 5
- 下一页

Mysql索引 索引有什么用( 三 )

Mysql索引索引有什么用( 三 )