MySQL反经验定律创建索引

说明

相信大家在做数据库优化的时候肯定都会听到，一定不能这样做，必须要这样做。这也是为什么DBA会和开发"打架"原因，毕竟有些经验的开发多多少少也会一点优化啦。

大家有听到过在为表建立索引的时候一定不能在选择性(selective)低的字段上建立索引吧，比如(性别、类型、状态等等)。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

我们的应用就有类似以下一张表:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

CREATE TABLE t1(
  id INT unsigned NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  status TINYINT unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY(id)
);

其中status是有0,1这两个值:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

• 0:代表数据是从别地同步过来的数据，但是我们未经过处理。
• 1:说明我们已经处理过的数据。

在几次无意中的show processlist总是能看到有这样的语句在Sending data:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

SELECT * FROM t1 WHERE status = 0;

好奇的小看了一下发现这个表的数据还挺多。自己在开发库执行了一下，执行的好久。最后和开发了解了一下，他们说这是一个排程，每5分钟一次，经过更深入的了解，知道他们要操作的数据其实只是status=0的，而在在5分钟之内接收的数据和总数据相比简直是小巫见老巫了。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

于是就在开发库添加一下索引。发现效果真是杠杠的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

演示

下面是一系列演示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

-- 创建测试表
DROP TABLE t1;
CREATE TABLE t1(
  id INT unsigned NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  status TINYINT unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY(id)
);

-- 添加数据
DROP PROCEDURE insert_t1;
DELIMITER //
CREATE PROCEDURE insert_t1()
BEGIN
  DECLARE num INT;
  SET num=1;
  SET autocommit=0;
  WHILE num < 10000000 DO
    IF (num%10000=0) THEN
      COMMIT;
    END IF;
    INSERT INTO t1 VALUES(NULL, 'XXXXXXXX', 1);
    SET num=num+1;
  END WHILE;
  COMMIT;
  SET autocommit=1;
END //

DELIMITER ;

CALL insert_t1();

-- 生成几条status=0的数据
UPDATE t1 SET status = 0 WHERE id >= 9999990;

-- 没有键索引时的速度
SELECT * FROM t1 WHERE status=0;
+---------+----------+--------+
| id      | name     | status |
+---------+----------+--------+
| 9999990 | XXXXXXXX |      0 |
| 9999991 | XXXXXXXX |      0 |
| 9999992 | XXXXXXXX |      0 |
| 9999993 | XXXXXXXX |      0 |
| 9999994 | XXXXXXXX |      0 |
| 9999995 | XXXXXXXX |      0 |
| 9999996 | XXXXXXXX |      0 |
| 9999997 | XXXXXXXX |      0 |
| 9999998 | XXXXXXXX |      0 |
| 9999999 | XXXXXXXX |      0 |
+---------+----------+--------+
10 rows in set (6.90 sec)

-- 创建索引
ALTER TABLE t1 ADD INDEX idx$t1$status(status);
-- 查看创建索引后的速度
SELECT * FROM t1 WHERE status=0;
+---------+----------+--------+
| id      | name     | status |
+---------+----------+--------+
| 9999990 | XXXXXXXX |      0 |
| 9999991 | XXXXXXXX |      0 |
| 9999992 | XXXXXXXX |      0 |
| 9999993 | XXXXXXXX |      0 |
| 9999994 | XXXXXXXX |      0 |
| 9999995 | XXXXXXXX |      0 |
| 9999996 | XXXXXXXX |      0 |
| 9999997 | XXXXXXXX |      0 |
| 9999998 | XXXXXXXX |      0 |
| 9999999 | XXXXXXXX |      0 |
+---------+----------+--------+
10 rows in set (0.00 sec)

 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

昵称：HH
QQ：275258836
ttlsa群交流沟通(QQ群②：6690706 QQ群③：168085569 QQ群④：415230207（新） 微信公众号：ttlsacom)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

感觉本文内容不错，读后有收获？文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/

逛逛衣服店，鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-reverse-experience-law-create-index/