Home >Database >Mysql Tutorial >Mysql的一条SQL优化(一)_MySQL

Mysql的一条SQL优化(一)_MySQL

WBOY
WBOYOriginal
2016-06-01 13:00:061027browse

开发反应做压测时并发上不去,有条SQL执行非常慢,于是explain下:

MariaDB [db_vip]>
MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_db_vip 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_db_vip | ALL | NULL | NULL | NULL | NULL | 86987 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
MariaDB [db_vip]> show status like '%cost%';
+-----------------+--------------+
| Variable_name | Value |
+-----------------+--------------+
| Last_query_cost | 18006.399000 |
+-----------------+--------------+
1 row in set (0.00 sec)
从执行计划中可以看出,这个SQL对表这个表m_db_vip进行了全表扫描,而表m_db_vip共有约10万行记录,Mysql优化器评估出的成本为18006,
where条件的三列中msource_id与mobile都是选择性很强的列,msource_type的选择性弱,


首先对列msource_id创建一个索引:
MariaDB [db_vip]> create index i_m_db_vip_1 on m_db_vip(msource_id);
Query OK, 0 rows affected (1.18 sec)
Records: 0 Duplicates: 0 Warnings: 0


再次执行SQL,发现Mysql并没有用上msource_id的索引(还是Oracle的CBO智能呀!)
MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_db_vip 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_db_vip | ALL | i_m_db_vip_1 | NULL | NULL | NULL | 86987 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)


MariaDB [db_vip]> show status like '%cost%';
+-----------------+--------------+
| Variable_name | Value |
+-----------------+--------------+
| Last_query_cost | 18006.399000 |
+-----------------+--------------+
1 row in set (0.00 sec)
再创建mobile上的索引:
MariaDB [db_vip]> create index i_m_db_vip_2 on m_db_vip(mobile);
Query OK, 0 rows affected (0.96 sec)
Records: 0 Duplicates: 0 Warnings: 0


MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_db_vip 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_db_vip | index_merge | i_m_db_vip_1,i_m_db_vip_2 | i_m_db_vip_1,i_m_db_vip_2 | 98,99 | NULL | 2 | Using union(i_m_db_vip_1,i_m_db_vip_2); Using where |
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+---------+------+------+-------------------------------------------------------+
1 row in set (0.02 sec)


MariaDB [db_vip]> show status like '%cost%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 6.826060 |
+-----------------+----------+
1 row in set (0.00 sec)


MariaDB [db_vip]>

SQL的执行成本已经大幅降低了,经开发已测试,效果相当不错,并发从10几个冲到2000以上。

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn