MySQL 多列索引优化小记_mysql索引类型多列

由于爬虫抓取的数据不断增多,这两天在不断对数据库以及查询语句进行优化,其中一个表结构如下：

CREATE TABLE +newspaper_article+ (
  +id+ varchar(50) NOT NULL COMMENT '编号',
  +title+ varchar(190) NOT NULL COMMENT '标题',
  +author+ varchar(255) DEFAULT NULL COMMENT '作者',
  +date+ date NULL DEFAULT NULL COMMENT '发表时间',
  +content+ longtext COMMENT '正文',
  +status+ tinyint(4) DEFAULT '0',
  PRIMARY KEY (+id+),
  KEY +idx_status_date+ (+status+,+date+)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

根据业务需要,添加了 idx_status_date 索引,在执行下面这个 SQL 时特别耗时：

SELECT id, title, status, date FROM article WHERE status > -2 AND date = '2016-01-07';

这是为什么呢?

解决方案

将 idx_status_date (status, date) 改为 idx_status (status) 后,查看 MySQL 执行计划：

可以看到将多列索引改为单列索引后,执行计划要扫描的数据总量没有任何变化.结合多列索引遵循最左前缀原则,推测上面的查询语句只使用了 idx_status_date 最左边的 status 的索引.

翻了下<<高性能MySQL>>找到了下面这段话,证实了我的想法：

所以呢,这里解决思路有两种：

可以通过使用多个等于条件来代替范围条件

修改 idx_status_date (status, date) 为索引 idx_date_status (date, status) ,并新建一个 idx_status 索引,即可达到同样的效果.

优化后的执行计划：

实际执行结果：

总结

当人们谈论索引的时候,如果没有特别指明类型,那么多半说的是 B-Tree 索引,它使用 B-Tree 数据结构来存储数据.我们使用术语"B-Tree",是因为 MySQL 在 CREATE TABLE 和其他语句中也使用该关键字.不过,底层的存储引擎也可能使用不同的存储结构.InnoDB使用的是B+Tree.假如有如下数据表：

CREATE TABLE People (
  last_name  varchar(50)    not null,
  first_name varchar(50)    not null,
  dob        date           not null,
  gender     enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

B-Tree 索引对如下类型的查询有效

匹配最左前缀上表中的索引可用于查找所有姓为 Allen 的人,即只使用索引的第一列.

匹配列前缀只匹配某一列的值的开头部分.例如上表的索引可用于查找所有以 J 开头的姓的人.这里也只使用了索引的第一列.

匹配范围值例如上表中的索引可用于查找姓在 Allen 和 Barrymore 之间的人.这里也只使用了索引的第一列.

精确匹配某一列并范围匹配另外一列上表的索引也可用于查找所有姓为 Allen ,并且名字是字母 K 开头（比如 Kim 、 Karl 等）的人.即第一列 last_name 全匹配,第二列 first_name 范围匹配.

只访问索引的查询B-Tree 通常可以支持"只访问索引的查询",即查询只需要访问索引,而无须访问数据行.

B-Tree 索引的一些限制

如果不是按照索引的最左列开始查找,则无法使用索引.例如上表的索引无法用于查找名字为 Bill 的人,也无法查找某个特定生日的人,因为这两列都不是最左数据列.类似地,也无法查找姓氏以某个字母结尾的人.

不能跳过索引中列.也就是说,上表的索引无法用于查找姓氏为 Smith 并且在某个特定日期出生的人.如果不指定名（first_name）,则 MySQL 只能使用索引的第一列.

以上就是土嘎嘎小编为大家整理的MySQL 多列索引优化小记相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！