索引的代价
- 空间上的代价
显而易见的，每建立一个索引，都要为它建立一棵B+树。这是存储空间的消耗。 - 时间上的代价
每当对表中的数据进行增删查改操作时，都需要修改各个B+树索引，这里面可能会造成页分裂和页面回收的动作，这些都是性能耗时的关键。
另外还有一点就是在执行查询语句前，首先要生成的一个执行计划，一般情况下，一条查询语句在执行的过程中最多使用一个二级索引，在生成执行计划时需要计算使用不同索引执行查询
先建立一个表：
CREATE TABLE single_table (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY uk_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part (key_part1,key_part2,key_part3),
) Engine=InnoDB CHARSET=utf8;
回表
SELECT * FROM single_table WHERE key1 > 'a' AND key1 < 'C'
我们可以选择下面2种方式来执行。
- 全表扫描
直接扫描全部的聚簇索引记录，判断每一条记录是否符合搜索条件。 - 使用
id_key1执行该查询
搜索条件得到的对应的扫描区间为('a', 'c')，然后扫描该区间的二级索引记录，但是由于二级索引记录的只有 key1，id 这2个列。所以这时候我们还需要获取每条二级索引记录对应的聚簇索引记录，这就是回表。
那么在执行查询的时候，什么时候使用全表扫描，什么时候使用二级索引+回表的方式呢？
这是查询优化器所做的事情，查询优化器会事先针对表中的一些记录计算一些统计数据，然后再利用这些统计数据或者访问表中的少量记录来计算需要执行回表操作的记录数。如果需要执行回表的记录数越多，就越倾向于使用全表扫描，反之则倾向于使用二级索引+回表的方式。
应用 B+ 树索引
- 扫面区间和边界条件
对于查询来说，最简单的方案就是 扫描表中所有的记录，判断每一条记录是否符合搜索条件，这种方案也成为全表扫描。
索引用于排序 ORDER
需要使用ORDER BY子句对查询出来的记录按照某种规则进行排序，一般情况下，我们只能把记录加载到内存中，然后再利用一些排序算法在内存中对这些记录进行排序，如果查询的结果集太大无法在内存中进行排序，此时还需要借助磁盘空间。
但是，如果ORDER BY 子句使用了索引列，就有可能省去在内存中或磁盘排序的步骤。
- 不可以使用索引进行排序的情况
- ASC DESC混用
- 排序列包含非同一个索引的列
- 排序列是某个联合索引的索引列，但是这些排序列并不连续
- 用来形成的扫描区间的索引列与排序列不同
- 排序列不是以单独列名的形式出现在ORDER BY 子句中
索引用于分组 GROUP
更好的创建索引
-
只为用于搜索，排序或者分组的列创建索引
只为出现在 WHERE 子句中的列，连接子句中的连接列，或者出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。 -
考虑索引列中不重复值的个数
-
索引列的类型尽量小
如果想对某个整数类型建立索引，在表示整数的范围允许的情况下，尽量让使用索引列使用较小的数据类型，比如能使用INT就不要使用BIGINT，因为数据类型越小，索引占用的空间就越少，在一个数据页内就可以存放更多的记录，磁盘IO带来的性能损耗也就越小。 -
为列前缀建立索引
-
覆盖索引
为了告别回表操作带来的性能损耗，最好在查询列表中只包含索引列。SELECT * key1, id FROM single_table WHERE key1 > 'a' AND key1 < 'c'idx_key1索引已经包含了我们想要的列，这时我们可以从二级索引中直接获取到想要的信息。 -
让索引列以列名的形式在搜索条件中单独出现
SELECT * FROM single_table WHERE key * 2 < 4key2 列并不是以单独列名的形式出现的，而是以key * 2 这样的表达式出现的。MySQL 并不会化简表达式，而是认为这个搜索条件并不能形成合适的扫描区间来减少需要扫描的记录数量，所以该查询只能以全表扫描的方式来执行。
但是，这个查询语句我们稍微改以下就可以了SELECT * FROM single_table WHERE key < 4 / 2 -
新插入记录时主键大小对效率的影响
B+ 树的每一层数据页以及页面中的记录都是按照主键值从小到大的顺序排列的。如果新插入记录的主键值是依次 增大的话，则每插满一个数据页就能换导下一个数据页继续插入，如果新插入的主键值忽大忽小，就比较麻烦了。
image.png
这会造成页分裂。页分裂就意味着性能损耗，所以为了避免这种无谓的性能损耗，最好让插入的记录的主键值依次递增，所以，一般来说，会用自增ID做主键。
- 冗余和重复索引
网友评论