7.4.4 【MySQL】索引字符串值的前缀

我们知道一个字符串其实是由若干个字符组成，如果我们在 MySQL 中使用 utf8 字符集去存储字符串的话，编码一个字符需要占用 1~3 个字节。假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的 B+ 树中有这么两个问题：

• B+ 树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
• 如果 B+ 树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们前边儿说过索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了个方案 --- 只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。这样只在 B+ 树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题，何乐而不为，比方说我们在建表语句中只对 name 列的前10个字符进行索引可以这么写：

CREATE TABLE person_info( 
name VARCHAR(100) NOT NULL, 
birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL, 
 country varchar(100) NOT NULL, 
 KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)); 

name(10) 就表示在建立的 B+ 树索引中只保留记录的前 10 个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

7.4.4.1 索引列前缀对排序的影响

如果使用了索引列前缀，比方说前边只把 name 列的前10个字符放到了二级索引中，下边这个查询可能就有点儿尴尬了：

SELECT * FROM person_info ORDER BY name LIMIT 10;

因为二级索引中不包含完整的 name 列信息，所以无法对前十个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只好乖乖的用文件排序。

7.4.5 让索引列在比较表达式中单独出现

假设表中有一个整数列 my_col ，我们为这个列建立了索引。下边的两个 WHERE 子句虽然语义是一致的，但是在效率上却有差别：

1. WHERE my_col *

2 < 42. WHERE my_col < 4/2

第1个 WHERE 子句中 my_col 列并不是以单独列的形式出现的，而是以 my_col * 2 这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于 4 ，所以这种情况下是使用不到为 my_col 列建立的 B+ 树索引的。而第2个 WHERE 子句中 my_col 列并是以单独列的形式出现的，这样的情况可以直接使用B+ 树索引。

所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

7.4.6 主键插入顺序

我们知道，对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，这就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间：

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

可这个数据页已经满了啊，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ，比方说我们可以这样定义person_info 表：

CREATE TABLE person_info( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
name VARCHAR(100) NOT NULL, 
birthday DATE NOT NULL, 
phone_number CHAR(11) NOT NULL, 
country varchar(100) NOT NULL, 
PRIMARY KEY (id), 
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)); 

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。

7.4.7 冗余和重复索引

有时候有的同学有意或者无意的就对同一个列创建了多个索引，比方说这样写建表语句：

CREATE TABLE person_info( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
name VARCHAR(100) NOT NULL, 
birthday DATE NOT NULL, 
phone_number CHAR(11) NOT NULL, 
country varchar(100) NOT NULL, 
PRIMARY KEY (id), 
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), 
KEY idx_name (name(10))); 

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对name 列的索引就算是一个 冗余 索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

另一种情况，我们可能会对某个列重复建立索引，比方说这样：

CREATE TABLE repeat_index_demo ( 
c1 INT PRIMARY KEY, 
c2 INT, 
UNIQUE uidx_c1 (c1), 
INDEX idx_c1 (c1)); 

我们看到， c1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

7.5 总结

1. B+ 树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
2. B+ 树索引适用于下边这些情况：

• 全值匹配
• 匹配左边的列
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 用于排序
• 用于分组

1. 在使用索引时需要注意下边这些事项：

• 只为用于搜索、排序或分组的列创建索引
• 为列的基数大的列创建索引
• 索引列的类型尽量小
• 可以只对字符串值的前缀建立索引
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况，建议让主键拥有 AUTO_INCREMENT 属性。
• 定位并删除表中的重复和冗余索引
• 尽量使用 覆盖索引 进行查询，避免 回表 带来的性能损耗。