更好的创建和使用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就可以将更多的记录加载到内存中），读写效率也就越高。

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