Columns
Index_Type
PRIMARY
ext_id
Unique
i_mobile
mobile
i_msource_type
msource_type
i_msource_id
msource_id
而msource_type列的可选择性是非常低了，基本上到这里我大概知道是怎么回事了，剩下的事情就是验证我的猜测，explain:
mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_vip_ext where (msource_id=‘xx1391434680574433‘ and msource_type=1 ) or ( mobile=‘1391434680574433‘ and msource_type=1);
+----+-------------+-----------+------+--------------------------------------+----------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+--------------------------------------+----------------+---------+-------+-------+-------------+
| 1 | SIMPLE | m_vip_ext | ref | i_mobile,i_msource_type,i_msource_id | i_msource_type | 4 | const | 58383 | Using where |
+----+-------------+-----------+------+--------------------------------------+----------------+---------+-------+-------+-------------+
1 row in set (0.17 sec)
果不其然，Mysql的优化器并没有选择之前的2个索引，而是选择了可选择性很低的i_msource_type，这无疑是比全表扫描效率更低的一种方式。
果断drop掉索引：
mysql> alter table m_vip_ext drop index i_msource_type;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次explain:
mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_vip_ext where (msource_id=‘xx1391434680574433‘ and msource_type=1 ) or ( mobile=‘1391434680574433‘ and msource_type=1);
+----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------+
| 1 | SIMPLE | m_vip_ext | index_merge | i_mobile,i_msource_id | i_msource_id,i_mobile | 98,99 | NULL | 2 | Using sort_union(i_msource_id,i_mobile); Using where |
+----+-------------+-----------+-------------+-----------------------+-----------------------+---------+------+------+------------------------------------------------------+
1 row in set (0.17 sec)
mysql> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_vip_ext where (msource_id=‘xx1391434680574433‘ and msource_type=1 ) or ( mobile=‘1391434680574433‘ and msource_type=1) \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: m_vip_ext
type: index_merge
possible_keys: i_mobile,i_msource_id
key: i_msource_id,i_mobile
key_len: 98,99
ref: NULL
rows: 2
Extra: Using sort_union(i_msource_id,i_mobile); Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show status like ‘%cost%‘;
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 7.328006 |
+-----------------+----------+
1 row in set (0.00 sec)
OK了，开发人员用测试库2做的压测，反映并发也能上1000了。
Mysql的一条SQL优化（二）
标签：mysql 优化
