- A+
所属分类：MySQL
说明
相信大家在做数据库优化的时候肯定都会听到，一定不能这样做，必须要这样做。这也是为什么DBA会和开发"打架"原因，毕竟有些经验的开发多多少少也会一点优化啦。
大家有听到过在为表建立索引的时候一定不能在选择性(selective)低的字段上建立索引吧，比如(性别、类型、状态等等)。
我们的应用就有类似以下一张表:
|
1 2 3 4 5 6 |
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这两个值:
- 0:代表数据是从别地同步过来的数据，但是我们未经过处理。
- 1:说明我们已经处理过的数据。
在几次无意中的show processlist总是能看到有这样的语句在Sending data:
|
1 |
SELECT * FROM t1 WHERE status = 0; |
好奇的小看了一下发现这个表的数据还挺多。自己在开发库执行了一下，执行的好久。最后和开发了解了一下，他们说这是一个排程，每5分钟一次，经过更深入的了解，知道他们要操作的数据其实只是status=0的，而在在5分钟之内接收的数据和总数据相比简直是小巫见老巫了。
于是就在开发库添加一下索引。发现效果真是杠杠的。
演示
下面是一系列演示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
-- 创建测试表 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) |
昵称：HH
QQ：275258836
ttlsa群交流沟通(QQ群②：6690706 QQ群③：168085569 QQ群④：415230207（新） 微信公众号：ttlsacom)
感觉本文内容不错，读后有收获？
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
22/02/2017 上午 10:36 沙发
我觉得这个原因没有写清；而且测试数据的 status 字段 0 的数据量要比 1 的小得多
29/09/2016 下午 5:20 板凳
写得挺好的
15/05/2016 下午 9:33 地板
这个也要写篇文章。。。