如何更好地使用mysql索引

这篇笔记主要是记录一下mysql索引在实际应用中的一些注意点。

1.建议使用自增id作为表的主键

在之前关于innodb行格式的学习中,已经知道了数据页里面的数据是按主键递增的顺序串起来的。

如果我们建表的时候,没有使用自增列作为主键,而是使用了业务相关的列作为主键,那么在业务上会有很多插入、删除操作。如果一个数据页已经满了,此时再在中间插入一条记录的话,就需要分页。相对应的就是,如果删除了大量数据,一个页里面的数据太少的话,也会进行页的合并。页分裂页合并都是开销很大的操作,我们需要避免。

所以,我们在实际应用中,建议使用自增id作为主键,而不是业务上的某个属性作为主键。


2.在业务中使用查询语句的时候不要select *,建议写明需要的列名

在上一篇笔记为什么innodb索引采用B+树结构里面,已经学习过索引的结构。已经知道叶子节点存放的是具体的数据,非叶子节点是索引。

那么innodb索引的结构可以简化成如下图所示:

此处插入索引树的图片

在innodb里面,主键索引又叫聚簇索引

我们在实际应用中,除了主键索引,还会在某个列上面建立索引,例如

CREATE TABLE `testsecondaryindex` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` varchar(255) DEFAULT NULL,
  `c3` char(11) DEFAULT NULL,
  `c4` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT

c2上的索引叫二级索引二级索引树的叶子节点存储的不是行内容,而是主键的值。

如果你业务里的查询语句是select * from xxx where c2='abc',要查询的是所有列,但是在二级索引只能查到c2c1(二级索引列和主键列)的值,没有其他列的值。为了获取其他列的数据,就需要根据主键值去查主键索引,最终找到叶子节点上完整的行记录,这个过程叫回表

而且在回表的时候,并不是在二级索引里面把所有主键id全部都找出来之后,再统一去回表。而是,每在二级索引里面找到一条符合的数据,就去回表。所以,如果在二级索引里面查找的数据太多的话,需要回表的次数也就越多。更有甚者,有时候譬如在二级索引里面查的数据差不多占整体数据的90%,而mysql的查询分析器经过分析之后发现代价太高,有可能最后选择的是全表扫描。

所以,业务中不要select *,而要指定具体的列名。


3.尽量使用覆盖索引

上面提到了回表是有代价的,那么我们在实际业务中需要有意识的避免回表。

比如说上面提到的这张表,它在c2列上建了索引

CREATE TABLE `testsecondaryindex` (
  `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c2` varchar(255) NOT NULL DEFAULT '',
  `c3` char(11) NOT NULL DEFAULT '',
  `c4` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`c1`),
  KEY `idx_c2` (`c2`),
  KEY `idx_c3_c4` (`c3`,`c4`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT 

如果在业务里面只需要获取c2列的值,譬如select c2 from testsecondaryindex where c2 in ('abc', 'qwe'),那么只需要读取c2索引树就行了。这种就叫覆盖索引

再譬如,我在上面那张表的c3和c4列上建立了一个索引idx_c3_c4,如果我在类似这种查询语句select c4 from testsecondaryindex where c3='abc'里面,就可以直接在idx_c3_c4里面获取想要的c4的值,而不需要回表。但是如果是select c2 from testsecondaryindex where c3='abc',因为idx_c3_c4里面不包含c2的值,那么就需要回表了。


4.排序的时候尽量考虑使用索引

我们以上方的testsecondaryindex表为例,它的字符集是ascii,它有idx_c2和idx_c3_c4两个二级索引,其中idx_c3_c4也叫联合索引

往这个表里面插入一些数据:

mysql> select * from testsecondaryindex;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | a    | bbbb | c    |
|  2 | dd   | e    | aaaa |
|  3 | cc   | aa   | dd   |
|  4 | cc   | e    | c    |
+----+------+------+------+
4 rows in set (0.00 sec)

那么以上述数据为例,idx_c2是如下这样的:

此处插入idx_c2

每一行的内容是:c2的值,主键的值。行与行之间的顺序是:先按照c2的值递增,如果c2的值一样那就按照主键值递增。

那么我们可以写出idx_c3_c4的样子: 即每一行分别是 c3的值,c4的值,主键的值。行与行之间的顺序是:先按照c3的值递增,如果c3的值一样那就按照c4值递增,如果c4的值一样那就按照主键值递增。

此处插入idx_c3_c4

所以如果我们业务里面有类似这种SQL:select c3, c4 from testsecondaryindex where c3='abc' order by c4,那么就可以直接用到idx_c3_c4来排序,也就是首先在idx_c3_c4定位到第一个c3='abc'的数据,然后按顺序依次往后,直到遇到c3 !='abc'的数据,期间的数据就是我们所需要的且已经按c4递增排好了的数据。

再举一个例子,select c3, c4 from testsecondaryindex where c3 > 'abc' order by c4, c3; 这个SQL语句的需求是先按照c4递增再按照c3递增,像这种查询语句就不能很好地用利用索引了,因为在idx_c3_c4索引里面,是先按照c3递增,然后再按照c4递增的。

上面举的是增序的例子,下面举一个倒序的例子select c3, c4 from testsecondaryindex where c3='abc' order by c4 desc,可以看到这条语句是将结果按c4倒序排列。其实这样也可以用到索引,如同上面那样,首先在idx_c3_c4定位到第一个c3='abc'的数据,然后按顺序依次往后,直到遇到c3 !='abc'的数据,期间的数据就是按c4递增排好了的数据,那我们添加到结果集里面的时候,最先获取的放到最后,最后获取的放到最前面那,就是我们需要的按c4倒序排列的数据。

再举一个倒序排列的例子,select c3, c4 from testsecondaryindex where c3 > 'abc' order by c3 desc, c4 desc,这个语句的排序也是使用到了索引。就如同上面说的,按照增序的顺序取出来之后,倒一下,就是我们想要的数据了。

再举一个增序与倒序混用的例子,select c3, c4 from testsecondaryindex where c3 > 'abc' order by c3 desc, c4 asc。这个排序就没办法使用索引了。

如果在排序的时候对要排序的列使用了函数,例如select c3, c4 from testsecondaryindex where c3 > 'abc' order by UPPER(c3), c4;,那么索引也就用不到了。

对于那种SQL里面有order by但是又不能直接使用索引的语句,explain查看的时候,会在extra里面显示Using filesort,即mysql表示没有使用索引排序,而是自己在外部进行了排序。在实际业务的类似场景中,有时候会发现mysql的CPU使用率会有所上升,可以考虑一下是否是因为mysql使用了外部排序。

所以一般的做法是,如果不能直接使用索引来排序,那么会将数据从mysql取出之后,在业务代码中实现排序的逻辑。


5.建议使用bigint类型存储时间

mysql有DATEDATETIMETIMESTAMPTIMEYEAR好几种类型可以用来存储时间。

鉴于我们的业务需求是存储时间(年月日时分秒),例如创建时间、更新时间等。首先我们要把DATE这个类型pass掉,它的时间粒度是到,不能存时分秒。然后我们要把TIME这个类型pass掉,它存的是时分秒,不能存我们需要的年月日。然后也要把YEAR类型pass掉,它存的是年份,默认是4位格式的年。

然后我们可以在DATETIMETIMESTAMP里面选。TIMESTAMP占4个字节,DATETIME占8个字节。

TIMESTAMP的范围是1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999,快到期了。当插入日期时,会先转换为本地时区后再存放;当查询日期时,会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。如果存进去是NULL,TIMESTAMP会自动存储当前时间。

DATETIME的范围是1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999。如果存进去是NULL,DATETIME会存储NULL。

那么我们可以选用DATETIME来存储时间。但是在我们实际业务中,还是建议是使用bigint来存储时间,有利于在时间字段上建立索引,有利于范围查找。

注意,如果使用int来存储时间,那么int的最大能表示的时间范围有限2038-01-19 11:14:07,当然也可以使用usigned int来存,最大时间是2106-02-07 14:28:15,但还是建议bigint来存时间。


6.尽量选用占用字节数少的数据类型作为主键

上文已经说过,建议使用自增id来作为主键。如果业务中有明确需求需要使用业务字段来作为主键,那么尽量选用占用字节数少的字段作为主键。

举例,有的业务使用uuid作为主键,去掉uuid里面的-字符之后,需要使用32个字节来存储它。或者说有的业务直接使用身份证号来作为主键,占18个字节。

如果选用int类型的字段作为主键,该字段只占用4个字节,如果是bigint,那么占8个字节。

被读入内存的时候,那么如果里的数据越多,那么就有更大的可能找到你需要的数据。而里的数据越多,意味着每个数据占据的空间越小。

也就是说,如果主键占的字节数越少,那么里的数据越多。

如果业务上实在是不能使用int作为主键或者二级索引,而要用字符串,那么可以考虑使用前缀索引。即取前X个字符串作为索引。例如alter table xxx add index idx_uid(uuid(8)),取前8个字节作为索引。


7.建立二级索引的时候选择的列要有区分度

很多时候业务上有需求比如说根据性别来查询,或者根据状态来查询,比如说gender或status字段,只有0和1两个值。如果只在gender或者status上建立二级索引,那么意义其实不大。建立索引的目的是为了加快查询速度,而像gender或者status这种只有两个值的列,只能筛掉一般的数据。

我们在为表建立二级索引的时候,最好是选择有区分度的列。


8.不要建立过多的索引

既然索引可以加快查询的速度,那是不是可以在每个列上或者可能的组合字段上都加上索引?

答案是不可以。假如你的表上有5个索引,那么每次插入和删除的是否,这5棵索引树都需要更新及维护。而且索引树是需要占据空间的,树的个数越多,占据的空间越大。

所以我们不要在表上建立太多的索引。


9.对于长度差不多的字符串列,建议使用char类型

mysql里面有charvarchar都可以用来存储字符串。char是定长的,建表的时候需要指定长度,varchar是变长的

innodb行格式这篇笔记里面,我们已经学习过,对于char(M)类型的列,如果实际存储的不满M个字符,那么在存储的时候会默认进行填充,以达到真正M个字符的效果。对于varchar(M)类型的列,实际是几个字符就占据几个字符的位置。

业务里面有时候会存储MD5,像这种情况,肯定是使用char(32)来存储。

如果业务里面,某个字段的字符串长度其实差不多,那么建议使用char类型来存储。譬如说某个字段在10 ~ 15个字符之间,那么建议可以直接使用char(15)来存储。虽然有浪费一点点空间,但是对于更新操作比较友好。

举个具体的例子来说明,假设用char(15)来存储了某个列mycolumn,某条记录的这个列原先是10个字符,后来又被update成了12个字符,那么就可以在原来的位置上直接更新该列。那么假如说是用varchar(15)来存储的该列,那么当某条记录里面该列的值从10个字符变成12个字符的时候,原先的存储位置不够了,需要把整行的其他列的内容搬过去,在另外的位置存储。具体可以在另一篇笔记里面查看。

注意:

  • char(M)和varchar(M)里面的M,表示的是字符个数,而不是字节数。例如是utf8编码格式的,那么一个字符占3个字节。
  • char(M)和varchar(M)字段,不能存储超过M个字符。
Show Comments