更好的创建和使用Mysql索引

表结构

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=utf8mb4

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

我们只为出现在where子句中的列、连接子句中的连接列,或者出现在ORDER BY或者GROUP BY子句中的列创建索引。仅出现在查询列表中的列就没必要建立索引。比如这样一个查询语句:

SELECT common_field,key_part3 FROM single_table WHERE key1 = 'a';

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

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

在通过二级索引进+回表的方式执行查询时,某个扫描区间中包含的二级索引记录数量越多,就会导致回表操作的代价越大。我们在为某个列创建索引时,需要考虑列中不重复值的个数占全部记录条数的比例。如果比例太低,则说明该列包含过多的重复值,那么在通过二级索引+回表的方式执行查询时,就有可能执行太多次回表操作。

索引的类型尽量小

能使用INT就不要使用BIGINT。因为数据类型越小,索引占用的存储空间就越少,在一个数据页就可以存放更多的记录,磁盘I/O带来的性能损耗也就越小(一次页面I/O就可以将更多的记录加载到内存中),读写效率也就越高。

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

作者:Zeran原文地址:https://segmentfault.com/a/1190000043825851

%s 个评论

要回复文章请先登录注册