MySQL中B+树索引的应用场景大全

一、本文所用到的数据表

本篇是讲B+树的应用场景，也就是我们平时在写sql语句时需要思考的问题，这里重点总结一下

首先列出建表语句，后面例子均在此表基础上举例说明。

CREATE TABLE demo_info(
    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=utf8mb4;

在这里你需要观察到的是哪些列加了索引就可以。

二、全值匹配

如果我们的搜索条件中的列和索引列对应的话（列的字段和个数要相同），这种情况就称为全值匹配。

SELECT * FROM demo_info WHERE key_part_1 = 'a' AND key_part_2 = 'b' AND key_part_3 = 'c';

可以想象一下查询过程

1. 因为B+树的数据页和记录先是按照key_part_1列的值进行排序的，所以可以很快key_part_1列的值时'a'的记录位置。
2. 在key_part_1列相同的记录里又是按照key_part_2 列的值进行排序的，所以在key_part_1列的值是'a'的记录里又可以快速定位key_part_2列的值是'b'的记录。
3. 如果key_part_1 列和key_part_2列的值都是相同的情况下，那记录是按照key_part_3列的值进行排序的，所以联合索引中的三个列都可能被用到。

有的同学可能有个疑问？WHERE子句中几个搜索条件的顺序对查询结果有啥影响嘛？也就是说我们调换key_part_1、key_part_2、key_part_3这几个搜索列的顺序对查询的执行过程有啥影响嘛？比方说写成下边这样

SELECT * FROM demo_info WHERE key_part_2 = 'b' AND key_part_3 = 'c' AND key_part_1 = 'a';

答案是没有影响，只要你把联合索引的每个索引都用到了，随意更换顺序是没有影响的，查询优化器会自动调整。但是如果没有全部用到，那么就必须按照最左前缀原则使用。

三、最左前缀原则

在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行，比如说下边的查询语句。

SELECT * FROM demo_info WHERE key_part_1 = 'a';

或者包含多个左边的列也行

SELECT * FROM demo_info WHERE key_part_1 = 'a' AND key_part_2 = 'b';

为什么搜索条件中必须出现左边的列才可以使用到这个B+树索引呢？

因为B+树的数据页和记录先是按照key_part_1 列的值进行排序的，在key_part_1 列的值相同的情况下才使用key_part_2 列进行排序，也就是说key_part_1 列的值不同的记录中，key_part_2的值可能是无序的，根据key_part_2直接无序查找全表，innodb肯定不会那么笨啊。

来张图，举个例

四、匹配列的前缀(比如like ‘a%’)

like操作符比较特殊，只有在匹配完整的字符串或者字符前缀时才产生合适的扫描区间。

比较字符串的大小其实就是相当于依次比较每个字符的大小，字符串的比较过程如下：

1. 先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。
2. 如果两个字符串的第一个字符相同，再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推。

如果这个列是索引列，那么字符串前缀相同的记录在单链表中肯定是相邻的，比如搜索条件为key1 LIKE 'a%'，对于非聚集索引来说，所有字符串前缀为 'a'的记录肯定是相邻的，所有我们只要沿着单链表往后扫描即可，直到字符串前缀不为 'a'为止。所以，key1 LIKE 'a%'的扫描区间相当于['a', 'b')

五、匹配列的中间字符或者后缀(比如like ‘%a%’，like ‘%com’)

如果查询中间包含的某个字符串，比如

SELECT * FROM demo_info WHERE name LIKE '%a%';

MySQL就无法快速定位记录位置了，因为字符串中间有’a’的字符串并没有排好序，所以只能全表扫描了。

如果查询后缀包含某个字符串，你会怎么做呢？

假设有个索引列url，想查询以com为后缀的网址的话可以这样写查询条件，WHERE url LIKE '%com'，但是这样的话无法使用该url列的索引。

我们可以把后缀查询改为前缀查询，不过我们就得把表中的数据全部逆序存储一下，这样再查找以com为后缀的网址时搜素条件就可以这么写：WHERE url LIKE 'moc%'，这样就可以用到索引了。这样即使是千万上亿级别的数据量，也可以快速查找而不是全表扫描。如果要查看正确的url，只需要将此逆序一下就可以了。

六、匹配范围查找，确定扫描区间和边界

为了避免往上翻，这里再次列出建表语句

CREATE TABLE demo_info(
    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=utf8mb4;

并不是所有的搜索条件都可以成为边界条件的，如下

select * from demo_info where key1 < 'a' and key3 > 'z' and common_field = 'abc'

key1 和key3 是单独的索引列，不是联合索引。

如果使用idx_key1 执行查询，那么相应的扫描区间是(-∞, 'a')，非聚集索引的叶子节点只有key1和id，所以key3 > 'z' and common_field = 'abc'就是普通的搜索条件，这些普通的搜索条件是回表获得完整的用户记录后才判断它们是否成立。

如果使用idx_key3 执行查询，那么相应的扫描区间是(z，+∞)，非聚集索引的叶子节点只有key3和id，所以 key1 < 'a' and common_field = 'abc'就是普通的搜索条件，这些普通的搜索条件是回表获得完整的用户记录后才判断它们是否成立。

注意：
  对于B+树索引来说，只有索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS　NOT　NULL、＞、＜、＞＝、＜＝、BETWEEN、!=（也就是<>）或LIKE操作符连接起来，就可以产生所谓的扫描区间。

七、IS NULL、IS NOT NULL、!=、BETWEEN、IN等等不能用索引吗？必须辟谣！

IS NULL的例子

explain select * from demo_info where key1 is null

虽然没有key1为null的记录，但还是走了索引。

IS NOT NULL的例子

explain select * from demo_info where key1 is not null limit 5;

因为这里所有记录的key1都不为null，为了避免全表扫描，我这里限制一下返回结果集数量。因为所有的结果都满足is not null，所有记录都会回表，那么优化器会选择全表扫描，而不是多此一举走非聚集索引+回表的方式。

!= 的例子

explain select * from demo_info where key1 != 'a' limit 5;

这里也走了索引，限制结果集的理由同上一个例子。

between的例子

explain select * from demo_info where key1 between 'a' and 'd' limit 5;

走索引的理由同上。

多的例子就不举了，否则比较冗余，直接上结论。

结论：对于B+树索引来说，只要索引列使用了=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（也就是<>）或者LIKE（只能是’a%'前缀字符形式）操作符连接起来，就可以使用到索引，如果你发现没走索引，请检查自己的结果集是否过多，限制一下结果集数量。

八、隐式转换导致全表扫描不走索引(这个坑容易忽视)

这也是开发中经常可能遇到的坑

比如，你明明想利用索引查询key1='1'的记录

explain select * from demo_info where key1 = '1';

结果！你却写成了key1 = 1；这个1可是number类型，不是varchar了，当字段类型和查询条件数据类型不一致时，如果没有函数转换，则会隐式转换，如果不能隐式转换则报错，这里varchar的'1'可以顺利转换为number类型的1，结果转换了类型，索引用不到索引。

explain select * from demo_info where key1 = 1;

注意：
  Server层包含连接器，查询缓存（MqSQL8.0剔除），分析器，优化器，执行器等。涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期，时间，数学和加密函数等）。所有跨存储引擎的功能都在这一层实现。
   而非聚集索引的查询和回表是在存储引擎层，如果要用函数判断，必须等到把完整记录返回给Server层，这里隐式转换用到函数就在Server层，在Server层就用不到索引了，所以是全表扫描。

九、使用联合索引的场景

我们前面说了，有联合索引KEY idx_key_part(key_part1, key_part2, key_part3)
eg1：

select * from demo_info where key_part1 = 'a' and key_part2 = 'b';

对于这个联合索引，先按照key_part1排序，在key_part1列的值相等的情况下再按照key_part2列进行排序，所以符合key_part1 = 'a' and key_part2 = 'b'条件的非聚集索引记录一定是相邻的。

我们可以定位到符合key_part1 = 'a' and key_part2 = 'b'条件的第一条记录，然后回表，接着定位满足key_part1 = 'a' and key_part2 = 'b'的第二条记录，然后回表，就这样沿着记录所在的单链表往后扫描，直到不满足key_part1 = 'a' 或者key_part2 = 'b'条件为止。（每找到一条满足条件的记录都会进行回表操作）

如果你了解MRR，并对这里产生了疑问，可以见这里MRR的说明，我们讨论问题一般都会忽略MRR

eg2：

select * from demo_info where key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c';

与上面类似，先按照key_part1排序，在key_part1 的值相等的情况下再按照key_part2排序，在key_part1和key_part2的值都相等的情况下，再按照key_part3排序。所以符合key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'的非聚集索引的记录一定是相邻的。（如果有满足的多条记录）

定位到满足key_part1 = 'a' and key_part2 = 'b' and key_part3 = 'c'的第一条记录后，然后进行回表，接着沿着单链表往后扫描，直到找到不满足key_part1 = 'a' 或者key_part2 = 'b' 或者key_part3 = 'c'的记录为止。（每找到一条满足条件的记录都会进行回表操作）

eg3:

select * from demo_info where key_part2 = 'a';

非聚集索引不是按照key_part2的值进行排序的。无法通过key_part2 = 'a'这个条件来减少扫描的记录数量，只能全表扫描，不会使用 idx_key_part这个联合索引。

十、索引条件下推（Index Condition Pushdown，简称ICP）

1.索引下推表象理解

上面说到，有联合索引KEY idx_key_part(key_part1, key_part2, key_part3)

select * from demo_info where key_part1 = 'a' and key_part3 = 'c'

页中的记录如下

先来简单说明一下，不在Server层和存储引擎层分析，后面会分析。

由于非聚集索引idx_key_part是先按照key_part1来排序的，但是仅仅在key_part1相同的情况下，key_part3 却可能是无序的，如上图，这样的话，这个联合索引就只能使用到key_part1这个索引列了。在['a'，'a']区间内的所有非聚集索引的记录进行依次扫描。

这里例子需要注意！
有人说是先找到key_part1 = 'a'的第一条记录，然后回表获得完整的用户记录后，接着判断key_part3 = 'c'条件是否成立。每找到一条满足key_part1 = 'a'条件的记录都会进行回表操作，回表后在判断key_part3 = 'c'条件是否成立。其实是不对的。

在使用idx_key_part联合索引执行查询时，虽然不能直接使用到key_part3 ，但是还是包含key_part3 列的。因此每当从idx_key_part索引的扫描区间['a'，'a']中获取到一条非聚集索引记录时，我们可以先判断这条二级索引记录是否符合key_part3 = 'c'条件，如果符合该条件再执行回表操作，不符合就不回表。然后跳转到下一条非聚集索引记录继续上述判断，这样可能会减少因回表操作而带来的性能损耗，这种优化方式称为索引条件下推（Index Condition Pushdown，简称ICP）。

虽然只能用到联合索引的一部分，利用后面的条件可以判断是否继续回表，从而加快查询速度，索引下推的特性是在MySQL 5.6中引入的，该特性是默认开启的。

2.索引下推在存储引擎层和server层深入分析

前面说到，有联合索引KEY idx_key_part(key_part1, key_part2, key_part3)和普通索引KEY idx_key1 (key1)

explain select * from demo_info where key_part1 = 'a' and key_part3 = 'c' and key1 < 'b';

执行计划如下

MySQL分为Server层和存储引擎层，Server层和存储引擎层的交互是以记录为单位的。

1. Server层第一次开始执行查询，把条件key_part1 = 'a'交给存储引擎，让存储引擎定位符合条件的第一条记录。
2. 存储引擎在非聚集索引idx_key_part中定位到符合key_part1 = 'a'条件的第一条记录，我们看到explain语句的输出结果的Extra列有一个Using index condition的提示，这表明会将有关idx_key_part非聚集索引的查询条件放在存储引擎层判断，这个特性就是所谓的索引条件下推，很显然这里的ICP条件就是key_part3 = 'c'。ICP条件筛选后得到一条非聚集索引记录，根据这条记录的主键id去回表，把回表得到的这条完整的用户记录返回给Server层。

注意：筛选到一条非聚集索引记录后就去回表，而不是把所有满足条件的非聚集索引记录都拿到后去回表。

3. 我们执行计划输出的Extra列有一个Using Where的提示，意味着Server层在接收到存储引擎层返回的一条记录后，接着就要判断其他where条件是否成立（就是再判断一下key1 < 'b'是否成立）。如果成立就直接发送给客户端，否则就跳过该条记录。

发现一条记录符合条件就发送给客户端，客户端在接受完全部的记录之后再展示。

4. 接着server层向存储引擎层要求继续读取刚才那条记录的下一条记录。
5. 每条记录的头信息中都有next_record的这个属性，所以可以快速定位到下一条记录的位置，然后继续判断ICP条件，接着回表，存储引擎把下一条完整的记录取出后将其返回给server层。
6. 然后重复第3步的过程，直到存储引擎层遇到了不符合key_part1 = 'a'的记录，然后向server层返回了读取完毕的信息。这时server层将结束查询。

3.范围查找也会使用到Using index condition，需要注意！

explain select * from demo_info where key_part1 <= 'a' limit 1;

我们可以看到这里的Extra有Using index condition。

但是将范围查询改为等值查询后，结果就变了

explain select * from demo_info where key_part1 = 'a' limit 1

因为在InnoDB存储引擎层有特殊的处理方案，是不作为ICP条件进行处理的，所以不会用到Using index condition。

十一、索引用于排序(explain分析案例，精华！！)

1.在使用联合索引时需要注意，order by子句后面的列的顺序也必须按照索引的顺序来。

对于联合索引KEY idx_key_part(key_part1, key_part2, key_part3)，如下查询是用不到索引的

# 用不到联合索引的例子
explain select * from demo_info where key_part3 <= 'b' order by key_part1, key_part2;

不管是where子句还是order by子句，想要用到索引，就得按照规则来，如下

explain select * from demo_info where key_part1 = 'a' and key_part2 = 'b' order by key_part3 limit 10;

2.当记录数很多却不用limit限制查询返回的记录数时，极有可能不走索引

当我使用联合索引的条件去进行order by排序，结果却没有走索引，而是全表扫描

explain select * from demo_info order by key_part1, key_part2;

当我限制只返回一条记录的时候，一下就走到了联合索引

explain select * from demo_info order by key_part1, key_part2 limit 1;

其实这是查询优化器搞的鬼，查询优化器会事先针对表中的记录计算一些统计数据，然后利用这些统计数据，或者访问表中少量记录来计算需要执行回表的记录数，如果需要回表的记录越多，查询优化器就越倾向于全表扫描，反之则使用非聚集索引+回表的方式。

当你使用limit子句限制查询返回的记录数时，会让查询优化器更倾向于选择使用非聚集索引+回表的方式进行查询，因为回表的记录越少，性能提升越高。

注意：当你order by 主键id的时候，一定是走索引的，而且是走的PRIMARY索引，因为在聚集索引上就不存在回表了，不会使用全表扫描。

3.asc，desc混用无法完全利用索引，除非你的数据库是MySQL 8.0+

首先说下asc和desc排序

• asc是升序排序大家都知道，沿着页中单链表遍历即可。
• desc降序排序就要注意了，你需要直到页中有Infimum和Supremum记录，可以先简单理解为头指针和尾指针。首先从头指针处遍历到最后一条用户记录，接着从头指针处遍历到倒数第二条记录，从头指针遍历到倒数第三条记录…

这样就能获得倒序排列的结果集了，很显然，asc升序排序获取结果集要快于desc降序排序。

实际上，一个页中的记录分了很多组，页中有一块Page Directory的空间存放了叫"槽"的东西。槽中存放着每个分组内最后一条记录在页面中的地址偏移量，只要找到了最后一条记录，然后找到分组的第一条记录（上一个槽的下一条记录），就可以在这个分组内小范围的遍历获取倒序的结果，这可比从页中第一条记录开始遍历获取倒序结果好的多，大大减少遍历时间。

如果非要asc和desc混合排序，我们来分析一下

explain select * from demo_info where key_part1 = 'a' order by key_part2, key_part3 desc;

按照key_part2升序排列，key_part2相同的情况下就按key_part3降序排列。

MySQL 8.0以前无法直接利用索引进行asc和desc混合排序，所以过程如下

1. 先筛选到满足key_part1 = 'a'条件的记录。
2. 再按照单链表取出key_part2最小的记录(假设为'b'，有多条满足key_part2 = 'b'的记录)。
3. key_part3 无法直接利用索引完成，将key_part2 = 'b'的记录取出进行一次文件排序，即无需全部回表获得完整记录后再排序，直接在非聚集索引进行文件排序（也成为外部排序，一般是归并排序）。
4. 依次循环1-3过程，直到找到不满足key_part1 = 'a'条件的记录为止，最后回表取得结果集就是有序的完整的用户记录。

MySQL 8.0 引入了Descending index的特性，允许利用索引直接asc和desc混合排序。

但是这里用到的联合索引却是升序的KEY idx_key_part(key_part1, key_part2, key_part3)，Using filesort是因为这里要取出第二次按照key_part3降序排列，索引声明是升序的，降序排列一定提示Using filesort。当拿到key_part2相等的记录时，还要按照key_part3降序排一次（在满足条件的记录分组内小范围遍历获取倒序结果）

如果在MySQL 8.0+将key_part3为desc，这里索引改为KEY idx_key_part(key_part1, key_part2, key_part3 desc)，再次执行

explain select * from demo_info where key_part1 = 'a' order by key_part2, key_part3 desc;

结果是asc和desc完美的利用索引进行混合排序。而在MySQL8.0以下，索引声明desc是直接被忽略的。

4. 排序中不能包含非同一索引的列，否则不走索引

排序中用来排序的多个列不是同一索引是不能使用索引排序的。

前面说过，key1和key2不是联合索引

• KEY idx_key1 (key1)
• UNIQUE KEY uk_key2 (key2)

explain select * from demo_info order by key1, key2 LIMIT 1;

5. 排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续，则不走索引

explain select * from demo_info order by key_part1, key_part3 LIMIT 1;

如果你的排序条件换成连续的，马上就能用到索引了

explain select * from demo_info order by key_part1, key_part2 LIMIT 1;

6.排序列是索引列，但是使用了函数，则不走索引

explain select * from demo_info order by UPPER(key1) limit 1;

因为key1列是以 UPPER(key1) 函数调用的形式出现在order by子句中，所以不能使用idx_key1执行上述查询。

索引的查询和回表是在存储引擎层，如果要用函数判断，必须等到把完整记录返回给server层，在server层就用不到索引了，所以是全表扫描。

十二、索引用于分组

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组，比如下边这个分组查询：

explain select key_part1, key_part2, key_part3, count(*) from demo_info group by key_part1, key_part2, key_part3;

这个查询语句相当于做了3次分组操作。

1. 先把记录按照key_part1值进行分组，所以key_part1值相同的所有记录划分为一组。
2. 将每个key_part1值相同的分组里的记录再按照key_part2值进行分组，将key_part2值相同的记录放到一个小分组里，看起来就像在一个大分组里又细分了好多小分组。
3. 再将上一步中产生的小分组按照key_part3的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，最后把若干个小分组再细分成更多的小小分组。
4. 针对那些小小分组进行统计，上面这个查询语句就是统计每个小小分组包含的记录条数。

如果没有idx_key_part索引，就得建立一个用于统计的临时表，在扫描聚集索引的记录时将统计的中间结果填入这个临时表，当记录扫描完毕后，再把临时表中的结果作为结果集发送给客户端。

如果有了idx_key_part索引，恰巧这个分组顺序又与idx_key_part的索引列的顺序是一致的，而idx_key_part的非聚集索引记录又是按照索引列的值排好序的，所以可以直接使用索引进行分组，不用再建立临时表了。

十三、如何更好的创建和使用索引

索引设计的原则

查询更快，占用空间最小。

1.只为用于搜索，排序或分组的列创建索引

只为出现在where 子句中的列，连接子句中的连接列，或者出现在order by或group by子句中的列创建索引，而出现在select查询列表中的列就没必要建立索引了：

select common_field, key_part3 from demo_info where key1 = 'a';

像查询列表中的common_field、key_part3这两个列就没必要建立索引，我们只需要为出现在where子句中的key1 列创建索引就可以了。

2. 考虑索引列中不重复值的个数

比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有9条记录，但不重复值的个数是3个。也就是说，在记录行数一定的情况下，不重复值的个数越大，该列中的值越分散，不重复值的个数越小，该列中的值就越集中，这个不重复值的个数指标非常重要，不重复值的个数越多，可以称为区分度高或者筛选性好，区分度和筛选性就是不重复值的个数和总个数的比值，这直接影响我们是否能有效的利用索引。

假设某个列不重复值的个数为1，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，不管查什么都需要回表。 而且如果某个建立了非聚集索引的列的重复值特别多，那么使用这个非聚集索引查出的记录回表的次数越多，性能损耗越大，查询优化器可能就不会走这个非聚集索引了，改变为全表扫描。

所以结论就是：最好为不重复值的个数多的列建立索引，区分度低说明该列包含过多重复值，那么在非聚集索引+回表的方式执行查询时，就有可能执行太多回表操作，导致优化器选择全表扫描。

3.索引列的类型尽量小

在定义表结构的时候，要显示的指定列的类型，以整数类为例，有TINYINT、MEDIUMINT、INT、BIGINT这么几种，他们占用的存储空间依次递增，能表示的整数范围依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘I/O带来的性能损耗就越小（一次页面I/O能将更多记录加载到内存中），读写效率就越高。

这个建议对于表的主键来说更加适用，因为不仅是聚集索引中会存储主键值，其他所有的非聚集索引的节点处都会存储一份记录的主键值，如果主键使用较小的数据类型，也就意味着能节省更多的存储空间。

4. 为索引字符串值的前缀建立索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间，在需要为这个字符串列建立索引时，那就意味着在对应的B+树中的记录中，需要把这列的完整字符串存储起来，字符串越长，在索引中占用的存储空间就越大。

索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了一个方案—只对字符串的前几个字符放到索引中，也就是说在非聚集索引的记录中只保留字符串的前几个字符，如下

 #先删除原有索引
 alter table demo_info drop index idx_key1;
 #创建新的索引,保留前10个字符
 alter table demo_info add index idx_key1(key1(10));

然后再执行下面查询语句

select * from demo_info where key1 = 'abcdefghijklmnop';

由于在idx_key1的非聚集索引中只保留字符串的前10个字符，所以我们只能定位到前缀为‘abcdefghij’的非聚集索引记录。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值进行回表查询完整的字符串值，再比对就好了。

当列中存储的字符串包含的字符数比较多时，这种为列前缀建立索引的方法可以明显减少索引大小。

不过，在只对列前缀建立索引的情况下，就不能使用索引来完成排序需求了。

select * from demo_info order by key1 LIMIT 10;

因为非聚集索引中不包含完整的key1 列信息，所以无法对key1列前10个字符相同但其余字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能全表扫描+文件排序的方式来执行了。

5.让索引在where子句的一侧单独出现

上面demo_info表给出了key2的信息

....
key2 INT,
...
UNIQUE KEY uk_key2 (key2),
...

表中有一个整数列key2，这个列建立了唯一索引。下边的两个where子句虽然语义是一致的，但是在效率上却有很大差别：

explain select * from demo_info where key2 < 4 - 1;

但是表达式调整之后

explain select * from demo_info where key2 + 1 < 4;

后者key2 列并不是以单独列出现的，而是以key2 + 1这样的表达式形式出现的，存储引擎会依次遍历所有的记录，所以这种情况下是使用不到key2列建立的B+树索引的。

所以结论就是：如果where子句中表达式的一侧的索引不是单独的形式出现，而是经过计算或者函数调用的形式出现的话，是用不到索引的。

注意：索引树有key2的值，而不是key2 + 1的值，像这种计算不会在存储引擎层完成，而是在server层。

6.保证主键的插入顺序

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

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

可这个数据页已经满了啊，新纪录该插入到哪里呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的页中。页面分裂和记录移位有一定的性能损耗。如果想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，让主键具有AUTO_INCREMENT属性，MySQL会自动为新插入的记录生成递增的主键，这样能够避免因数据插入导致的记录不必要的移动损耗。

7.如有必要，尽量考虑使用索引覆盖

我个人在业务场景中，需要根据会话id(session_id)和场景id(scene_id)去查询有没有这条记录，从而继续下一步插入或者更新操作，但是session_id对应多个scene_id，所以session_id无法设置唯一索引。就不能使用存在则更新，不存在则插入的语法insert into 表名(字段1,字段2,...) values(值1, 值2,...) on duplicate key update 字段1=values(字段1), 字段2=values(字段2)

所以，我只查一查这条记录有没有，再去判断插入还是更新。建立联合索引idx_sessionid_sceneid(session_id, scene_id)

只需要如下

select id from 表名 where session_id = '1212213' and scene_id = 'scene123';

这里只查询id，因为这样可以利用到索引覆盖。联合索引idx_sessionid_sceneid生成的非聚集索引不仅有session_id和scene_id， 还有主键id，这样就省去了回表的性能损耗。

前面demo_info表中有KEY idx_key1 (key1)

select key1 from demo_info order by key1;

前面说过，需要回表的记录越多，非聚集索引查询的性能越低，从而导致查询优化器选择全表扫描，前面采用的是指定limit子句来限制查询返回的记录数，让查询优化器倾向于选择非聚集索引+回表的方式查询，而不是全表扫描。

这里虽然没有limit子句，但是由于可以采用索引覆盖，查询优化器会直接在非聚集索引进行排序，不需要回表操作。

在实际业务中，如果也无需要查询索引列以外的列，那还是以业务为重，不要为了炫耀自己的索引玩的溜而乱用。最好仅把业务需要的索引列放在查询列表，而不是select *。

8.避免创建冗余重复的索引

前面说过，我们有联合索引KEY idx_key_part(key_part1, key_part2, key_part3)。

如果你还单独建立一个key_part1索引，那就是多此一举，因为根据联合索引的最左前缀原则就已经可以用到key_part1索引了，索引的建立和维护也是需要时间和空间的，这种不必要的开销应该避免。

9.定义有外键的数据列一定要添加索引。

10.更新频繁字段不适合创建索引

11.对于定义为text，image和bit的数据类型列不要建立索引。