Mysql的一條SQL最佳化（一），mysql一條sql最佳化

Mysql的一條SQL最佳化（一），mysql一條sql最佳化
開發反應做壓測時並發上不去，有條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以上。