B+树索引（13）之索引挑选（下）

B+树索引（12）之索引挑选（下）

前言回顾

上篇文章简单聊了索引选择的几点如

• 只为搜索、排序、分组相关列建立索引，即使是显示列（select）也不考虑。

• 尽量避免为基数太小的列建立索引，区分度太低索引可能不生效（如性别字段最多只有男、女、其它三种值，区分度太低）。

• 索引列尽可能的少占用空间，特别是主键索引因为占用空间太多将影响聚簇索引、二级索引的存储和搜索效率。

文章链接参考

B+树索引（11）之索引挑选（上）

这篇文章是对索引挑选进行一个补充。

适量的使用前缀索引

如果我们的数据表字符集选择uft8，这就意味着一个字符需要采用1~3个字节编码，如果字符串够长那么将占用大量的内存空间，而数据页固定大小一般为16k那么索引列越长一个数据页中所能存储的记录就越少，那么搜索时可能需要消耗更多的性能在磁盘IO以及查找数据页上。

所以我们可以考虑存储部分字符串，这就是前缀索引，前缀索引虽然可以减少索引列的长度，但一定需要注意的一个问题就是前缀索引的选择性，只有前缀索引的选择性趋近完整列的选择性才是最佳的。

前缀索引使用

alter table table_name add index index_name(column_name(prefix_length));

1
2

前缀索引的缺点

前缀索引的优点肯定就是减少索引的存储长度，节省空间，但同样不能忽略的是它的缺点。

• 使用前缀索引在匹配值后会去回表到聚簇索引中再次查询，所以索引覆盖对前缀索引无效。

• 前缀索引只是对指定列的部分字符串排序，那么对于order by排序和group by分组语句都不生效。

具体前缀索引的介绍可以参考

B+树索引（12）之索引前缀

索引列需要单独出现

在使用索引时不应该给索引加任何修饰（函数也是类似），不然索引会失效，如下

select * from test_index where column_name * 2 < 4;

select * from test_index where column_name < 4 / 2;

1
2
3
4

两条语句的效果是一样，但是执行效率却是不同，第二条SQL的效率要高于第一条，因为第一条SQL是存储引擎遍历所有记录将每个column_name值都乘2再来比较结果值是否大于4，走了全表扫描，而第二条语句是单纯判断索引。

隐式类型转换

这里还需要注意一种常见情况，索引列隐式类型转换！

例如存在如下表

CREATE TABLE student_int (
  id tinyint(4) NOT NULL AUTO_INCREMENT COMMENT 'id',
  sno varchar(20) DEFAULT NULL COMMENT '学号',
  sname varchar(10) DEFAULT NULL COMMENT '学生姓名',
  PRIMARY KEY (`id`),
  KEY `index_sno` (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8

1
2
3
4
5
6
7
8

同时存在如下测试数据

mysql> select * from student_int;
+-----+------+-------+
| id  | sno  | sname |
+-----+------+-------+
|   1 | 1001 | test  |
| 127 | 1001 | test  |
+-----+------+-------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

如果存在如下查询语句

 select * from student_int where sno='1001';
 
 select * from student_int where sno=1001;

1
2
3
4

那么查询结果是多少呢？显然可以查出两条数据

但是为什么sno为varchar类型却能用int类型去比较呢？这就涉及到了索引列的隐式类型转换，当索引列name搜索的条件为sno=1001那么会将name列进行类型转换后再比较，所以不会走索引，如下所示。

主键插入顺序

我们知道聚簇索引会根据主键进行排序（记录与记录之间会按主键顺序排序，数据页与数据页间也会按主键顺序排序），但如果插入数据库的主键大小忽大忽小，就可能造成页的分裂和记录移位（当插入的记录主键值在一个已满数据页之间时，Mysql会将本数据页裂分为两个数据页，将本页的数据一部分移动到新数据页中，当插入记录主键值在一个未满的数据页之间时，那么会发生记录移位才能将新纪录插入到数据页单向链表中），这样显然会造成性能损耗，所以正确的做法是给主键设置自增长值（也就是AUTO_INCREMENT），让主键索引的记录都是往后面追加尽量减少数据页的裂分和记录位移带来的性能消耗。