Mysql添加索引后的效果
时间:2014-03-06 07:03:48 作者:beebol 标签: 索引 rbo 分类: Mysql
最近线上数据库更新，就是删除一些记录，901条删除语句，每一条删除语句都关联了三个表，act_id_membership，apollo_erp_workerinfo ，apollo_erp_post。删除的语句如下：
DELETE a from act_id_membership a,apollo_erp_workerinfo b,apollo_erp_post c where a.GROUP_ID_=c.post_No and a.USER_ID_=b.workerinfo_No and c.post_type='job' and b.out_email='www@www.com';
问题来了，检查更新语句没有问题后，备份完毕后，直接执行了。回车一敲，就发现悲剧了。等了好几分钟还没有执行完毕。
由于执行了，没有办法只好等，最后完成花了30多分钟。（当时是在线删除数据的，库的数据量不是很大，在线备份在线操作）。
今天有空了，把更新之前的备份导入到了虚拟机，然后进行测试，检查索引。如下都是在虚拟机上操作：
重新恢复数据库到虚拟机，然后执行一条delete语句分析，结果如下：
Mysqladmin pr查看：
[root@Server3 update]# mysqladmin pr +----+------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | 12 | root | localhost | apollodb | Query | 42 | Sending data | DELETE a from act_id_membership a,apollo_erp_workerinfo b,apollo_erp_post c where a.GROUP_ID_=c.post | | 30 | root | localhost | | Query | 0 | | show processlist |+----+------+-----------+----------+---------+------+--------------+----------------- +----+------+-----------+----------+---------+------+--------------+-----------------
这么简单的一条delete执行还这么慢，time都到42了，然后再看看slow日志：
Time Id Command Argument # Time: 140129 4:44:25 # User@Host: root[root] @ localhost [] # Query_time: 48.716238 Lock_time: 0.010976 Rows_sent: 0 Rows_examined: 48244969 use apollodb; SET timestamp=1390941865;DELETE a from act_id_membership a,apollo_erp_workerinfo b,apollo_erp_post c where a.GROUP_ID_=c.post_No and a.USER_ID_=b.workerinfo_No and c.post_type='job' and b.out_email=' www@www.com '; DELETE a from act_id_membership a,apollo_erp_workerinfo b,apollo_erp_post c where a.GROUP_ID_=c.post_No and a.USER_ID_=b.workerinfo_No and c.post_type='job' and b.out_email=' www@www.com ';
正常情况下，这个删除应该很快，为什么这么慢呢？因为out_email=' www@www.com '查询出来就一条记录，再到三个表中查询那不是很快吗？是吧。开始检查调用的各表中的字段是否都有了索引。
[root@localhost:Wed Jan 29 05:04:12 2014 {apollodb}]>show indexes from act_id_membership;
+-------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| act_id_membership | 0 | PRIMARY | 1 | USER_ID_ | A | 7420 | NULL | NULL | | BTREE | | |
| act_id_membership | 0 | PRIMARY | 2 | GROUP_ID_ | A | 7420 | NULL | NULL | | BTREE | | |
| act_id_membership | 1 | ACT_FK_MEMB_GROUP | 1 | GROUP_ID_ | A | 7420 | NULL | NULL | | BTREE | | |
+-------------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)
[root@localhost:Wed Jan 29 05:14:13 2014 {apollodb}]>show indexes from apollo_erp_workerinfo;
+-----------------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| apollo_erp_workerinfo | 0 | PRIMARY | 1 | workerinfo_id | A | 7397 | NULL | NULL | | BTREE | | |
| apollo_erp_workerinfo | 0 | workerinfo_No_unique | 1 | workerinfo_No | A | 7397 | NULL | NULL | | BTREE | | |
+-----------------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
[root@localhost:Wed Jan 29 05:14:23 2014 {apollodb}]>show indexes from apollo_erp_post;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| apollo_erp_post | 0 | PRIMARY | 1 | post_id | A | 2809 | NULL | NULL | | BTREE | | |
| apollo_erp_post | 1 | post_no | 1 | post_No | A | 187 | NULL | NULL | YES | BTREE | | |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)
2 rows in set (0.00 sec)
刚开始还没有注意到，后面仔细一看问题出来了。其它的些字段有了索引，如GROUPID、post_No、USERID、workerinfo_No。唯有最重新的一个字段没有添加索引apollo_erp_workerinfo 的out_email。
再看看这个表的数据行数：
[root@localhost:Wed Jan 29 05:14:32 2014 {apollodb}]>select count(*) from apollo_erp_workerinfo;
+----------+
| count(*) |
+----------+
| 7344 |
+----------+1 row in set (0.00 sec)
1 row in set (0.00 sec)
虽然只有7344，但由于没有索引，每次查询都进行了全表扫描：
[root@localhost:Wed Jan 29 05:19:43 2014 {apollodb}]>explain select workerinfo_No from apollo_erp_workerinfo where out_email=' www@www.com '\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: apollo_erp_workerinfo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7397
Extra: Using where1 row in set (0.00 sec)
1 row in set (0.00 sec)
为out_email添加个索引。
[root@localhost:Wed Jan 29 04:46:23 2014 {apollodb}]>create index out_email_index on apollo_erp_workerinfo(`out_email`);
Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
然后再分析这条语句，走索引了，直接拿取数据，速度不是吹的。
[root@localhost:Wed Jan 29 04:47:00 2014 {apollodb}]>explain select workerinfo_No from apollo_erp_workerinfo where out_email=' www@www.com '\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: apollo_erp_workerinfo
type: ref
possible_keys: out_email_index
key: out_email_index
key_len: 97
ref: const
rows: 1
Extra: Using where1 row in set (0.00 sec)
1 row in set (0.00 sec)
从rows: 7397变成rows: 1，然后再试试删除语句：
[root@localhost:Wed Jan 29 04:47:07 2014 {apollodb}]>DELETE a from act_id_membership a,apollo_erp_workerinfo b,apollo_erp_post c where a.GROUP_ID_=c.post_No and a.USER_ID_=b.workerinfo_No and c.post_type='job' and b.out_email=' www22@www.com ';Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
最后以不到4秒的速度把901条数据删除：
[root@Server3 update]# time mysql -f apollodb <lxy-2.sql real 0m3.945s user 0m0.006ssys 0m0.084s sys 0m0.084s
果断瞬间秒杀。
总结：
把out_email放到最后面是对的，没有加索引就是悲剧的，mysql的sql的where条件解析顺序是从右到左开始的，最好把小的结果集发到最右边。注意mysql只有rbo，没有cbo哦。