LMLPHP后院

联合索引查询特别慢怎么办?技术

maybe yes 发表于 2021-05-30 08:01

联合索引涉及到业务逻辑,其实已经超出数据库范畴,所以不用为妙,MySQLMariaDB 是做不好的。

联合索引你知道多少呢?说实话,我找这个坑就花了很多时间,各种打日志,各种发布代码,线上测试,找到元凶不容易啊!

遇到了一个特别慢的查询,耗时 300 多秒,其实是可以用上索引的,explain 显示可能可以用上,但是实际情况比较复杂,在不同的机器,不同的数据上测试效果完全不一样,看来 MySQL 对于索引的优化足够复杂,以至于优化的不一定对,其实对于联合索引以左边为基准的使用原则不一定是对的,我这次遇到的问题,就是完完全全的以左边的字段为条件查询,结果就是特别的慢,这个跟数据是有关系的,不同的数据效果不一样,可能左边字段的值数据太多,导致 MariaDB 没有使用上索引。总之,骚年,不要再研究技术了。

MariaDB [ ]> show create table \G
*************************** 1. row ***************************
       Table: 
Create Table: CREATE TABLE `` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `status` tinyint(3) NOT NULL DEFAULT 1 COMMENT '0 not post, 1 posted'
  PRIMARY KEY (`id`),
  KEY `userid_status` (`userid`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1700000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.003 sec)
MariaDB []> explain SELECT id FROM lmlphp WHERE status=1 and userid=1 order by id desc limit 10;
+------+-------------+---------------------+------+---------------+---------------+---------+-------------+-------+-------------+
| id   | select_type | table               | type | possible_keys | key           | key_len | ref         | rows  | Extra       |
+------+-------------+---------------------+------+---------------+---------------+---------+-------------+-------+-------------+
|    1 | SIMPLE      |                     | ref  | userid_status | userid_status | 5       | const,const | 23564 | Using where |
+------+-------------+---------------------+------+---------------+---------------+---------+-------------+-------+-------------+
1 row in set (0.005 sec)

MariaDB []> explain SELECT id FROM lmlphp WHERE userid=1 order by id desc limit 10
    -> ;
+------+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table               | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      |                     | index | userid_status | PRIMARY | 4       | NULL |  436 | Using where |
+------+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.000 sec)

耗时太久,直接不等了。

MariaDB [ ]> SELECT id FROM   WHERE userid=56 order by id desc limit 10;
^CCtrl-C -- query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted
MariaDB [ ]> exit
Bye

总结:

不要使用联合索引,是个大坑。

2021-10-24 07:24:58 1635031498 0.018116