One SQL Optimization for Mysql (1) and one SQL Optimization for mysql

One SQL Optimization for Mysql (1) and one SQL Optimization for mysql
When the development reaction is used for stress testing, the concurrency does not go up, and some SQL statements are executed very slowly, so 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 = 'authorization' and msource_type = 1) or (mobile = '000000' and msource_type = 1 );
+ ------ + ------------- + ----------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------ + ------------- + ----------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------- +
| 1 | SIMPLE | m_db_vip | ALL | NULL | 86987 | Using where |
+ ------ + ------------- + ----------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------- +
1 row in set (0.00 sec)
MariaDB [db_vip]> show status like '% cost % ';
+ ----------------- + -------------- +
| Variable_name | Value |
+ ----------------- + -------------- +
| Last_query_cost | 1, 18006.399000 |
+ ----------------- + -------------- +
1 row in set (0.00 sec)
From the execution plan, we can see that this SQL statement scans the entire table of the table m_db_vip, and the table m_db_vip has a total of about 0.1 million rows of records. The cost evaluated by the Mysql optimizer is 18006,
Among the three columns of the where condition, msource_id and mobile are both highly selective columns, and msource_type is less selective,


First, create an index for the column 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


Execute the SQL statement again and find that Mysql does not use the index of msource_id (or the CBO intelligence of Oracle !)
MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword, status from m_db_vip where (msource_id = 'authorization' and msource_type = 1) or (mobile = '000000' 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 | 86987 | Using where |
+ ------ + ------------- + ----------- + ------ + --------------- + ------ + --------- + ------ + ------- + ------------- +
1 row in set (0.01 sec)


MariaDB [db_vip]> show status like '% cost % ';
+ ----------------- + -------------- +
| Variable_name | Value |
+ ----------------- + -------------- +
| Last_query_cost | 1, 18006.399000 |
+ ----------------- + -------------- +
1 row in set (0.00 sec)
Create an index on 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 = 'authorization' and msource_type = 1) or (mobile = '000000' and msource_type = 1 );
+ ------ + ------------- + ----------- + ------------- + ----------------------------- + --------------------------- + --------- + ------ + Accept +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ------ + ------------- + ----------- + ------------- + ----------------------------- + --------------------------- + --------- + ------ + Accept +
| 1 | SIMPLE | m_db_vip | index_merge | indexing, 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, ing); Using where |
+ ------ + ------------- + ----------- + ------------- + ----------------------------- + --------------------------- + --------- + ------ + Accept +
1 row in set (0.02 sec)


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


MariaDB [db_vip]>
The SQL Execution cost has been greatly reduced. After development and testing, the effect is quite good, and the concurrency has increased from 10 to more than 2000.