深入理解MySQL索引知识

索引在MySQL中也叫做键(key)是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能十分关键。尤其是当表中的数据量越来越大时&＃xff0c;索引对性能的影响愈发重要。在数据量小且负载较低时&＃xff0c;不恰当的索引对性能的影响可能还不明显&＃xff0c;但是当数据量逐渐增大时&＃xff0c;查询性能则会急剧下降。索引优化应该是对查询性能优化最有效的手段。索引能够轻而易举地将查询性能提高几个数量级。索引是在存储引擎层实现的&＃xff0c;而mysql有多个存储引擎索引的结构有多种innodb和myisam使用B树B&＃43;树memory存储引擎采hash索引。

1.1 索引简介(本质是一种数据结构)

1.1.1 使用索引能够快速查找出某个或者是多个列中有特定值的行&＃xff0c;如果没有索引例如表有2万条记录&＃xff0c;执行select name from student where id &＃61; 9000&＃xff1b;mysql就必须整表扫描直到找到id &＃61; 9000的这条记录。如果在id列上创建索引mysql就不需要整表扫描&＃xff0c;直接在索引里找9000这个值就可以对应数据库表的记录。提高查询效率。也可以将索引简单的类比字典目录&＃xff0c;当我们去查找某个汉字时可以使用字母或者偏旁部首去查找达到查询到这个汉字的汉字的页码的目的。
1.1.2 mysql数据库表的记录是存储在磁盘上的&＃xff0c;查询慢的主要原因的磁盘io操作&＃xff0c;很多应用的瓶颈也是io所导致的。而对于这种现象我们有两种方案解决&＃xff1a;
1.减少io次数&＃xff1a;次数减少。
2.减少io的量&＃xff1a;满足需求的情况下&＃xff0c;从磁盘加载到内存的数据量尽可能的少。
局部性原理&＃xff1a;数据和程序都有集群的倾向&＃xff0c;之前被访问过的数据很可能再次被访问&＃xff0c;空间局部性和时间局部性
磁盘预读&＃xff1a;内存跟磁盘交互的时候一般是有一个最小的逻辑单元我们称之为页。页的大小一般是由操作系统决定的。一般是4k或8k或者是4的整数倍B&＃43;树上的每个节点大小为16K通过 (show variables like ‘innodb_page_size’;)16438

1.2 索引优缺点

优点(排好序的快速查找数据结构)&＃xff1a;
1.通过创建唯一索引&＃xff0c;可以保证数据库表中每一行数据的唯一性
2.可以大大加快数据的查询速度&＃xff0c;这也是创建索引的主要原因&＃xff0c;降低数据库io成本(随机io转变为顺序io)
3.在使用分组和排序子句进行数据查询时&＃xff0c;可以明显减少查询中分组和排序的时间&＃xff0c;降 低cpu的消耗
4.在实现数据的参考完整性方面&＃xff0c;可以加速表和表之间的连接。

缺点&＃xff1a;
1.创建索引和维护索引都是需要耗费时间的&＃xff0c;并且随着数据量的增大所耗费的时间也会随之增加
2.索引是存储在磁盘上的数据结构&＃xff0c;其不仅占用表空间而且也占用一定的物理空间&＃xff0c;并且随着数据量的增大它所占用的物理空间也会随之增大
3.当对表中的数据进行增加&＃xff0c;删除&＃xff0c;修改时索引需要动态维护这样就降低了增删改的性能。
4.不恰当的索引不仅不能优化查询性能&＃xff0c;还会降低系统性能。

1.3 索引的设计原则?

索引设计不合理或者缺少索引在数据量少的时候&＃xff0c;它的性能影响不大。但是当数据量达到几百万上千万时&＃xff0c;高效的索引能提供良好的查询性能&＃xff0c;反之索引设计不合理或者缺少索引的情况查询性能就非常差。索引我们得考虑索引的设计原则&＃xff1a;
1.索引并非越多越好&＃xff0c;一个表中如果有大量的索引&＃xff0c;不仅占用磁盘空间&＃xff0c;还会影响增删改的语句性能&＃xff0c;因为在表中更新数据时&＃xff0c;索引也会进行调整和更新。
2.避免对经常更新的表进行过多的索引&＃xff0c;并且索引的列尽可能地少&＃xff0c;经常用于查询的字段进行创建索引&＃xff0c;避免添加不必要的字段
3.数据量少的表尽量不要使用索引&＃xff0c;由于数据量少查询所花费的时间可能比遍历索引时间还要短&＃xff0c;索引发挥不出优化的的功能。
4.在条件表达式中经常用到的不同值较多的列上创建索引&＃xff0c;在不同值很少的列上不要建立索引。比如学生表中的性别字段&＃xff0c;只有男女两个值一次不需要建立索引&＃xff0c;如果建立索引不但不能提高查询效率反而会严重降低数据的更新性能
5.在频繁的分组或排序的列上建立索引&＃xff0c;若待排序的列有多个&＃xff0c;可以考虑建立组合索引。

1.4 索引的分类&＃xff1f;

1.单列索引&＃xff1a;即一个索引只包含单个列&＃xff0c;一个表可以有多个单列索引
2.组合索引&＃xff1a;是指表的多个字段组合上创建的索引&＃xff0c;列的值组合必须唯一。只有在查询条件中使用了这些字段的左边字段时&＃xff0c;索引才会被使用&＃xff0c; 使用组合索引时遵循最左前缀集合
3.普通索引&＃xff1a;是mysql中最基本索引类型&＃xff0c;允许在定义索引列中插入重复值和空值
4.唯一索引&＃xff1a;要求索引列的值必须唯一&＃xff0c;但允许有空值。
5.主键索引&＃xff1a;是一种特殊的唯一索引&＃xff0c;不允许有空值
6.全文索引&＃xff1a;全文索引的类型为FULLTEXT&＃xff0c;在定义的列上支持值的全文查找&＃xff0c;允许这些索引列插入重复值和空值。全文索引可以在char varchar text类型的列上创建&＃xff0c;mysql只有MyISAM存储引擎支持全文索引

1.5 创建索引的几种方式&＃xff1f;

1.CREATE INDEX indexName ON tableName (columnName(length));
例如我们对ip_address这一列创建一个长度为16的索引&＃xff1a;
CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));

2.ALTER TABLE tableName ADD INDEX indexName(columnName);
ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

3.建表的时候创建索引&＃xff1a;
CREATE TABLE tableName(
id INT NOT NULL,
columnName columnType,
INDEX [indexName] (columnName(length))
);
查看索引信息&＃xff1a;
SHOW INDEX FROM t_user_action_log;
删除索引&＃xff1a;
ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;

1.6 什么是B树&＃xff1f;

B-树就是B树&＃xff0c;多路搜索树&＃xff0c;树高一层意味着多一次的磁盘I/O&＃xff0c;下图是3阶B树

B树的特征&＃xff1a;
树中每个节点最多包含m个孩子。
除根节点与叶子节点外&＃xff0c;每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点&＃xff0c;则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n&＃43;1个指针组成&＃xff0c;其中[ceil(m/2)-1] <&＃61; n <&＃61; m-1
以5叉BTree为例&＃xff0c;key的数量&＃xff1a;公式推导[ceil(m/2)-1] <&＃61; n <&＃61; m-1。所以 2 <&＃61; n <&＃61;4 。当n>4时&＃xff0c;中间节点 分裂到父节点&＃xff0c;两边节点分裂。
优势&＃xff1f;
与传统的二叉树&＃xff0c;红黑树相比B树B&＃43;树的一个节点可以存放更多的数据。以及节点的孩子数量也更多&＃xff0c;所以树的高度可控减少比对次数提高查询效率
根节点是已加载到内存&＃xff0c;查询的数据与根节点比对&＃xff0c;比对后匹配的数据加载到内存&＃xff0c;减少io操作避免全表扫描&＃xff0c;提高查询性能
非叶子节点也可以全部加载到内存&＃xff0c;其数据量不大&＃xff0c;索引数据全部存储在叶子节点。减少io操作。

1.7 什么是B&＃43;树&＃xff1f;

1.所有关键字都出现在叶子结点的链表中&＃xff08;稠密索引&＃xff09;&＃xff0c;且链表中的关键字恰好是有序的&＃xff1b;
2.不可能在非叶子结点命中&＃xff1b;
3.非叶子结点相当于是叶子结点的索引&＃xff08;稀疏索引&＃xff09;&＃xff0c;叶子结点相当于是存储&＃xff08;关键字&＃xff09;数据的数据层&＃xff1b;
4.每一个叶子节点都包含指向下一个叶子节点的指针&＃xff0c;从而方便叶子节点的范围遍历。
5.更适合文件索引系统&＃xff1b;

哈希索引就是采用一定的哈希算法&＃xff0c;把键值换算成新的哈希值&＃xff0c;检索时不需要类似B&＃43;树那样从根节点到叶子节点逐级查找&＃xff0c;只需一次哈希算法即可立刻定位到相应的位置&＃xff0c;速度非常快。

1.7 什么是哈希索引&＃xff1f;

哈希索引就是采用一定的哈希算法&＃xff0c;把键值换算成新的哈希值&＃xff0c;检索时不需要类似B&＃43;树那样从根节点到叶子节点逐级查找&＃xff0c;只需一次哈希算法即可立刻定位到相应的位置&＃xff0c;速度非常快。

优点&＃xff1a;哈希表这种结构适用于只有等值查询的场景&＃xff0c;比如Memcached及其他一些NoSQL引擎
一般不用hash索引&＃xff0c;主要原因是它不支持范围查找(缺点)

1.8 为什么要使用B&＃43;树&＃xff1f;B树和B&＃43;树的区别是什么&＃xff1f;

虽然我们可以通过查看mysql索引结构是B树&＃xff0c;但是实际中mysql使用的是B&＃43;树是B树的变种。b树和b&＃43;树的区别&＃xff1a;
1.B树所有节点都可以存放索引数据&＃xff0c;而B&＃43;树索引数据只存在叶子节点&＃xff0c;做样做的好处是减少非叶子节点的内存开销&＃xff0c;可以存放更多的索引字段&＃xff0c;意味着可以形成更多的叉&＃xff0c;减低树的高度&＃xff0c;提高优化查询性能。
2.B&＃43;叶子节点用指针相连&＃xff0c;提高了区间访问的性能 而B树叶子节点没有。
3.一般B&＃43;树3-4层足以支撑千万条数据量的存储。
4.由于B&＃43;Tree只有叶子节点保存key信息&＃xff0c;查询任何key都要从root走到叶子。所以B&＃43;Tree的查询效率更加稳定。

1.9 Mysql的索引结构为什么要使用BTREE和B&＃43;TREE&＃xff1f;

二叉排序树&＃xff1a; 虽然能做到节点数据的排序功能,数据量小的时候确实是个不错的选择。但是我们知道一般来说对表中的每个列进行添加索引那么这个列的数据量是非常大的使用二叉排序树在大数据量的是时候&＃xff0c;它的高度是不可控的也就是说如1000w条数据会导致二叉树的高度非常高&＃xff0c;查找效率低io操作频繁。而且不保证平衡最坏的情况下会退化成链表。
红黑树&＃xff1a; 虽然能做到相对平衡&＃xff0c;但是高度也不可控&＃xff0c;大数据量下查找效率低。
树上的每个节点大小为16K通过(show variables like ‘innodb_page_size’;)16438&＃61;16k
所以索引的字段尽可能地少占用空间&＃xff0c;节点数据就可以存放更多的key
BTree 又叫多路平衡搜索树&＃xff0c;一颗m叉的BTree特性如下&＃xff1a;
树中每个节点最多包含m个孩子。
除根节点与叶子节点外&＃xff0c;每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点&＃xff0c;则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n&＃43;1个指针组成&＃xff0c;其中[ceil(m/2)-1] <&＃61; n <&＃61; m-1
以5叉BTree为例&＃xff0c;key的数量&＃xff1a;公式推导[ceil(m/2)-1] <&＃61; n <&＃61; m-1。所以 2 <&＃61; n <&＃61;4 。当n>4时&＃xff0c;中间节点 分裂到父节点&＃xff0c;两边节点分裂
B树画图
B&＃43;树画图

2.1 InnoDB 的索引模型

在InnoDB中&＃xff0c;表都是根据主键顺序以索引的形式存放的&＃xff0c;这种存储方式的表称为索引组织表。又因为前面我们提到的&＃xff0c;InnoDB使用了B&＃43;树索引模型&＃xff0c;所以数据都是存储在B&＃43;树中的。每一个索引在InnoDB里面对应一棵B&＃43;树。假设&＃xff0c;我们有一个主键列为ID的表&＃xff0c;表中有字段k&＃xff0c;并且在k上有索引。这个表的建表语句是&＃xff1a;

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine&＃61;InnoDB;


表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)&＃xff0c;两棵树的示例示意图如下。

从图中不难看出&＃xff0c;根据叶子节点的内容&＃xff0c;索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里&＃xff0c;主键索引也被称为聚簇索引&＃xff08;clustered index&＃xff09;。
非主键索引的叶子节点内容是主键的值。在InnoDB里&＃xff0c;非主键索引也被称为二级索引&＃xff08;secondary index&＃xff09;。
根据上面的索引结构说明&＃xff0c;我们来讨论一个问题&＃xff1a;基于主键索引和普通索引的查询有什么区别&＃xff1f;
如果语句是select * from T where ID&＃61;500&＃xff0c;即主键查询方式&＃xff0c;则只需要搜索ID这棵B&＃43;树&＃xff1b;
如果语句是select * from T where k&＃61;5&＃xff0c;即普通索引查询方式&＃xff0c;则需要先搜索k索引树&＃xff0c;得到ID的值为500&＃xff0c;再到ID索引树搜索一次。这个过程称为回表。
也就是说&＃xff0c;基于非主键索引的查询需要多扫描一棵索引树。因此&＃xff0c;我们在应用中应该尽量使用主键查询。

2.2 索引维护

B&＃43;树为了维护索引有序性&＃xff0c;在插入新值的时候需要做必要的维护。以上面这个图为例&＃xff0c;如果插入新的行ID值为700&＃xff0c;则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400&＃xff0c;就相对麻烦了&＃xff0c;需要逻辑上挪动后面的数据&＃xff0c;空出位置。

而更糟的情况是&＃xff0c;如果R5所在的数据页已经满了&＃xff0c;根据B&＃43;树的算法&＃xff0c;这时候需要申请一个新的数据页&＃xff0c;然后挪动部分数据过去。这个过程称为页分裂。在这种情况下&＃xff0c;性能自然会受影响。

除了性能外&＃xff0c;页分裂操作还影响数据页的利用率。原本放在一个页的数据&＃xff0c;现在分到两个页中&＃xff0c;整体空间利用率降低大约50%。

当然有分裂就有合并。当相邻两个页由于删除了数据&＃xff0c;利用率很低之后&＃xff0c;会将数据页做合并。合并的过程&＃xff0c;可以认为是分裂过程的逆过程。

基于上面的索引维护过程说明&＃xff0c;我们来讨论一个案例&＃xff1a;
你可能在一些建表规范里面见到过类似的描述&＃xff0c;要求建表语句里一定要有自增主键。当然事无绝对&＃xff0c;我们来分析一下哪些场景下应该使用自增主键&＃xff0c;而哪些场景下不应该
自增主键是指自增列上定义的主键&＃xff0c;在建表语句中一般是这么定义的&＃xff1a; NOT NULL PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定ID的值&＃xff0c;系统会获取当前ID最大值加1作为下一条记录的ID值。

也就是说&＃xff0c;自增主键的插入数据模式&＃xff0c;正符合了我们前面提到的递增插入的场景。每次插入一条新记录&＃xff0c;都是追加操作&＃xff0c;都不涉及到挪动其他记录&＃xff0c;也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键&＃xff0c;则往往不容易保证有序插入&＃xff0c;这样写数据成本相对较高。

除了考虑性能外&＃xff0c;我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段&＃xff0c;比如字符串类型的身份证号&＃xff0c;那应该用身份证号做主键&＃xff0c;还是用自增字段做主键呢&＃xff1f;

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键&＃xff0c;那么每个二级索引的叶子节点占用约20个字节&＃xff0c;而如果用整型做主键&＃xff0c;则只要4个字节&＃xff0c;如果是长整型&＃xff08;bigint&＃xff09;则是8个字节。
显然&＃xff0c;主键长度越小&＃xff0c;普通索引的叶子节点就越小&＃xff0c;普通索引占用的空间也就越小。

所以&＃xff0c;从性能和存储空间方面考量&＃xff0c;自增主键往往是更合理的选择。

有没有什么场景适合用业务字段直接做主键的呢&＃xff1f;还是有的。比如&＃xff0c;有些业务的场景需求是这样的&＃xff1a;
1.只有一个索引&＃xff1b;
2.该索引必须是唯一索引。
你一定看出来了&＃xff0c;这就是典型的KV场景。
由于没有其他索引&＃xff0c;所以也就不用考虑其他索引的叶子节点大小的问题。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则&＃xff0c;直接将这个索引设置为主键&＃xff0c;可以避免每次查询需要搜索两棵树。

2.3 覆盖索引

在下面这个表T中&＃xff0c;如果我执行 select * from T where k between 3 and 5&＃xff0c;需要执行几次树的搜索操作&＃xff0c;会扫描多少行&＃xff1f;
下面是这个表的初始化语句。

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT &＃39;&＃39;,
index k(k))
engine&＃61;InnoDB;insert into T values(100,1, &＃39;aa&＃39;),(200,2,&＃39;bb&＃39;),(300,3,&＃39;cc&＃39;),(500,5,&＃39;ee&＃39;),(600,6,&＃39;ff&＃39;),(700,7,&＃39;gg&＃39;);


现在&＃xff0c;我们一起来看看这条SQL查询语句的执行流程&＃xff1a;

1.在k索引树上找到k&＃61;3的记录&＃xff0c;取得 ID &＃61; 300&＃xff1b;

2.再到ID索引树查到ID&＃61;300对应的R3&＃xff1b;

3.在k索引树取下一个值k&＃61;5&＃xff0c;取得ID&＃61;500&＃xff1b;

4.再回到ID索引树查到ID&＃61;500对应的R4&＃xff1b;

5.在k索引树取下一个值k&＃61;6&＃xff0c;不满足条件&＃xff0c;循环结束。

在这个过程中&＃xff0c;回到主键索引树搜索的过程&＃xff0c;我们称为回表。可以看到&＃xff0c;这个查询过程读了k索引树的3条记录&＃xff08;步骤1、3和5&＃xff09;&＃xff0c;回表了两次&＃xff08;步骤2和4&＃xff09;。

在这个例子中&＃xff0c;由于查询结果所需要的数据只在主键索引上有&＃xff0c;所以不得不回表。那么&＃xff0c;有没有可能经过索引优化&＃xff0c;避免回表过程呢&＃xff1f;

覆盖索引
如果执行的语句是select ID from T where k between 3 and 5&＃xff0c;这时只需要查ID的值&＃xff0c;而ID的值已经在k索引树上了&＃xff0c;因此可以直接提供查询结果&＃xff0c;不需要回表。也就是说&＃xff0c;在这个查询里面&＃xff0c;索引k已经“覆盖了”我们的查询需求&＃xff0c;我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数&＃xff0c;显著提升查询性能&＃xff0c;所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是&＃xff0c;在引擎内部使用覆盖索引在索引k上其实读了三个记录&＃xff0c;R3~R5&＃xff08;对应的索引k上的记录项&＃xff09;&＃xff0c;但是对于MySQL的Server层来说&＃xff0c;它就是找引擎拿到了两条记录&＃xff0c;因此MySQL认为扫描行数是2
备注&＃xff1a;关于如何查看扫描行数的问题&＃xff0c;我将会在第16文章《如何正确地显示随机消息&＃xff1f;》中&＃xff0c;和你详细讨论。

基于上面覆盖索引的说明&＃xff0c;我们来讨论一个问题&＃xff1a;在一个市民信息表上&＃xff0c;是否有必要将身份证号和名字建立联合索引&＃xff1f;
假设这个市民表的定义是这样的&＃xff1a;

CREATE TABLE &＃96;tuser&＃96; (&＃96;id&＃96; int(11) NOT NULL,&＃96;id_card&＃96; varchar(32) DEFAULT NULL,&＃96;name&＃96; varchar(32) DEFAULT NULL,&＃96;age&＃96; int(11) DEFAULT NULL,&＃96;ismale&＃96; tinyint(1) DEFAULT NULL,PRIMARY KEY (&＃96;id&＃96;),KEY &＃96;id_card&＃96; (&＃96;id_card&＃96;),KEY &＃96;name_age&＃96; (&＃96;name&＃96;,&＃96;age&＃96;)
) ENGINE&＃61;InnoDB


我们知道&＃xff0c;身份证号是市民的唯一标识。也就是说&＃xff0c;如果有根据身份证号查询市民信息的需求&＃xff0c;我们只要在身份证号字段上建立索引就够了。而再建立一个&＃xff08;身份证号、姓名&＃xff09;的联合索引&＃xff0c;是不是浪费空间&＃xff1f;

如果现在有一个高频请求&＃xff0c;要根据市民的身份证号查询他的姓名&＃xff0c;这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引&＃xff0c;不再需要回表查整行记录&＃xff0c;减少语句的执行时间。

当然&＃xff0c;索引字段的维护总是有代价的。因此&＃xff0c;在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务DBA&＃xff0c;或者称为业务数据架构师的工作。

2.4 最左前缀原则

看到这里你一定有一个疑问&＃xff0c;如果为每一种查询都设计一个索引&＃xff0c;索引是不是太多了。如果我现在要按照市民的身份证号去查他的家庭地址呢&＃xff1f;虽然这个查询需求在业务中出现的概率不高&＃xff0c;但总不能让它走全表扫描吧&＃xff1f;反过来说&＃xff0c;单独为一个不频繁的请求创建一个&＃xff08;身份证号&＃xff0c;地址&＃xff09;的索引又感觉有点浪费。应该怎么做呢&＃xff1f;

这里&＃xff0c;我先和你说结论吧。B&＃43;树这种索引结构&＃xff0c;可以利用索引的“最左前缀”&＃xff0c;来定位记录。

为了直观地说明这个概念&＃xff0c;我们用&＃xff08;name&＃xff0c;age&＃xff09;这个联合索引来分析。

可以看到&＃xff0c;索引项是按照索引定义里面出现的字段顺序排序的。

当你的逻辑需求是查到所有名字是“张三”的人时&＃xff0c;可以快速定位到ID4&＃xff0c;然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人&＃xff0c;你的SQL语句的条件是"where name like ‘张%’"。这时&＃xff0c;你也能够用上这个索引&＃xff0c;查找到第一个符合条件的记录是ID3&＃xff0c;然后向后遍历&＃xff0c;直到不满足条件为止。

可以看到&＃xff0c;不只是索引的全部定义&＃xff0c;只要满足最左前缀&＃xff0c;就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段&＃xff0c;也可以是字符串索引的最左M个字符。

基于上面对最左前缀索引的说明&＃xff0c;我们来讨论一个问题&＃xff1a;在建立联合索引的时候&＃xff0c;如何安排索引内的字段顺序。

这里我们的评估标准是&＃xff0c;索引的复用能力。因为可以支持最左前缀&＃xff0c;所以当已经有了(a,b)这个联合索引后&＃xff0c;一般就不需要单独在a上建立索引了。因此&＃xff0c;第一原则是&＃xff0c;如果通过调整顺序&＃xff0c;可以少维护一个索引&＃xff0c;那么这个顺序往往就是需要优先考虑采用的。

所以现在你知道了&＃xff0c;这段开头的问题里&＃xff0c;我们要为高频请求创建(身份证号&＃xff0c;姓名&＃xff09;这个联合索引&＃xff0c;并用这个索引支持“根据身份证号查询地址”的需求。

那么&＃xff0c;如果既有联合查询&＃xff0c;又有基于a、b各自的查询呢&＃xff1f;查询条件里面只有b的语句&＃xff0c;是无法使用(a,b)这个联合索引的&＃xff0c;这时候你不得不维护另外一个索引&＃xff0c;也就是说你需要同时维护(a,b)、(b) 这两个索引。

这时候&＃xff0c;我们要考虑的原则就是空间了。比如上面这个市民表的情况&＃xff0c;name字段是比age字段大的 &＃xff0c;那我就建议你创建一个&＃xff08;name,age)的联合索引和一个(age)的单字段索引。

2.5 索引下推

上一段我们说到满足最左前缀原则的时候&＃xff0c;最左前缀可以用于在索引中定位记录。这时&＃xff0c;你可能要问&＃xff0c;那些不符合最左前缀的部分&＃xff0c;会怎么样呢&＃xff1f;

我们还是以市民表的联合索引&＃xff08;name, age&＃xff09;为例。如果现在有一个需求&＃xff1a;检索出表中“名字第一个字是张&＃xff0c;而且年龄是10岁的所有男孩”。那么&＃xff0c;SQL语句是这么写的&＃xff1a;

mysql> select * from tuser where name like &＃39;张%&＃39; and age&＃61;10 and ismale&＃61;1;


你已经知道了前缀索引规则&＃xff0c;所以这个语句在搜索索引树的时候&＃xff0c;只能用 “张”&＃xff0c;找到第一个满足条件的记录ID3。当然&＃xff0c;这还不错&＃xff0c;总比全表扫描要好。然后呢&＃xff1f;当然是判断其他条件是否满足。在MySQL 5.6之前&＃xff0c;只能从ID3开始一个个回表。到主键索引上找出数据行&＃xff0c;再对比字段值。而MySQL 5.6 引入的索引下推优化&＃xff08;index condition pushdown)&＃xff0c; 可以在索引遍历过程中&＃xff0c;对索引中包含的字段先做判断&＃xff0c;直接过滤掉不满足条件的记录&＃xff0c;减少回表次数。图3和图4&＃xff0c;是这两个过程的执行流程图。


在图3和4这两个图里面&＃xff0c;每一个虚线箭头表示回表一次。

图3中&＃xff0c;在(name,age)索引里面我特意去掉了age的值&＃xff0c;这个过程InnoDB并不会去看age的值&＃xff0c;只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此&＃xff0c;需要回表4次。

图4跟图3的区别是&＃xff0c;InnoDB在(name,age)索引内部就判断了age是否等于10&＃xff0c;对于不等于10的记录&＃xff0c;直接判断并跳过。在我们的这个例子中&＃xff0c;只需要对ID4、ID5这两条记录回表取数据判断&＃xff0c;就只需要回表2次。

2.6 聚簇索引和非聚簇索引

聚簇是为了提高某个属性(或属性组)的查询速度&＃xff0c;把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。
聚簇索引&＃xff08;clustered index&＃xff09;不是单独的一种索引类型&＃xff0c;而是一种数据存储方式。这种存储方式是依靠B&＃43;树来实现的&＃xff0c;根据表的主键构造一棵B&＃43;树且B&＃43;树叶子节点存放的都是表的行记录数据时&＃xff0c;方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块&＃xff0c;找到索引也就找到了数据。

非聚簇索引&＃xff1a;数据和索引是分开的&＃xff0c;B&＃43;树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM存储引擎都默认使用B&＃43;树结构存储索引&＃xff0c;但是只有InnoDB的主键索引才是聚簇索引&＃xff0c;InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

拓展&＃xff1a;聚簇索引优缺点&＃xff1f;
优点&＃xff1a;
1.数据访问更快&＃xff0c;因为聚簇索引将索引和数据保存在同一个B&＃43;树中&＃xff0c;因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快

缺点&＃xff1a;
1.插入速度严重依赖于插入顺序&＃xff0c;按照主键的顺序插入是最快的方式&＃xff0c;否则将会出现页分裂&＃xff0c;严重影响性能。因此&＃xff0c;对于InnoDB表&＃xff0c;我们一般都会定义一个自增的ID列为主键&＃xff08;主键列不要选没有意义的自增列&＃xff0c;选经常查询的条件列才好&＃xff0c;不然无法体现其主键索引性能&＃xff09;
2.更新主键的代价很高&＃xff0c;因为将会导致被更新的行移动。因此&＃xff0c;对于InnoDB表&＃xff0c;我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找&＃xff0c;第一次找到主键值&＃xff0c;第二次根据主键值找到行数据。

主键索引和普通索引的区别&＃xff1a;主键索引只要搜索ID这个B&＃43;Tree即可拿到数据。普通索引先搜索索引拿到主键值&＃xff0c;再到主键索引树搜索一次(回表)

2.7 为什么建议Innodb表必须建主键,并且推荐使用整形的自增主键&＃xff1f;

如果不建立主键mysql会帮助我们的表的每一列的每一行排查看是否存在不重复的数据并将其设置成主键。如果没有找到mysql会帮助我自己添加一列自增数据并将其设置成主键&＃xff0c;这种做法无疑增加了mysql的压力&＃xff0c;降低系统性能。整型比较的速度快&＃xff0c;占用内存小自增?以B&＃43;树为例&＃xff0c;索引数据都存放在叶子节点&＃xff0c;而且叶子节点都是有序的&＃xff0c;自增这就意味着每次插入的索引数据是从最后面的一个叶子节点插入&＃xff0c;即使是分裂也分裂的比较规整。不自增分裂要调整平衡&＃xff0c;性能下降。

索引是占据物理空间的&＃xff0c;在不同的存储引擎中&＃xff0c;索引存在的文件也不同。存储引擎是基于表的&＃xff0c;以下分别使用MyISAM和InnoDB存储引擎建立两张表。innodb: .frm(表结构) .idb(数据文件 &＃43; 索引文件) myisam: .frm(表结构) .myi(索引文件) .myd(数据文件)
存储引擎为MyISAM&＃xff1a;
*.frm&＃xff1a;与表相关的元数据信息都存放在frm文件&＃xff0c;包括表结构的定义信息等
*.MYD&＃xff1a;MyISAM DATA&＃xff0c;用于存储MyISAM表的数据
*.MYI&＃xff1a;MyISAM INDEX&＃xff0c;用于存储MyISAM表的索引相关信息
存储引擎为InnoDB&＃xff1a;
*.frm&＃xff1a;与表相关的元数据信息都存放在frm文件&＃xff0c;包括表结构的定义信息等
*.ibd&＃xff1a;InnoDB DATA&＃xff0c;表数据和索引的文件。该表的索引(B&＃43;树)的每个非叶子节点存储索引&＃xff0c;叶子节点存储索引和索引对应的数据

主键索引的叶子节点存的是整行数据。在InnoDB里&＃xff0c;主键索引也被称为聚簇索引&＃xff08;clustered index&＃xff09;。
非主键索引的叶子节点内容是主键的值。在InnoDB里&＃xff0c;非主键索引也被称为二级索引&＃xff08;secondary index&＃xff09;。
根据上面的索引结构说明&＃xff0c;我们来讨论一个问题&＃xff1a;基于主键索引和普通索引的查询有什么区别&＃xff1f;

如果语句是select * from T where ID&＃61;500&＃xff0c;即主键查询方式&＃xff0c;则只需要搜索ID这棵B&＃43;树&＃xff1b;
如果语句是select * from T where k&＃61;5&＃xff0c;即普通索引查询方式&＃xff0c;则需要先搜索k索引树&＃xff0c;得到ID的值为500&＃xff0c;再到ID索引树搜索一次。
这个过程称为回表。
也就是说&＃xff0c;基于非主键索引的查询需要多扫描一棵索引树。因此&＃xff0c;我们在应用中应该尽量使用主键查询

回表&＃xff1a;当根据普通索引查询到聚簇索引的key值之后&＃xff0c;再根据key值在聚簇索引中获取所有记录。回到主键索引树搜索的过程&＃xff0c;我们称为回表
覆盖索引&＃xff1a;ID主键 k普通索引
如果执行的语句是select ID from T where k between 3 and 5&＃xff0c;这时只需要查ID的值&＃xff0c;而ID的值已经在k索引树上了&＃xff0c;因此可以直接提供查询结果
不需要回表。也就是说&＃xff0c;在这个查询里面&＃xff0c;索引k已经“覆盖了”我们的查询需求&＃xff0c;我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数&＃xff0c;显著提升查询性能&＃xff0c;所以使用覆盖索引是一个常用的性能优化手段。这种方式效率是更高的

主键长度越小&＃xff0c;普通索引的叶子节点就越小&＃xff0c;普通索引占用的空间也就越小。
所以&＃xff0c;从性能和存储空间方面考量&＃xff0c;自增主键往往是更合理的选择。