MySQL函式索引及優化

懂點IT的耿小廚發表於2020-07-04

很多開發人員在使用MySQL時經常會在部分列上進行函式計算等,導致無法走索引,在資料量大的時候,查詢效率低下。針對此種情況本文從MySQL5.7 及MySQL8.0中分別進行不同方式的優化。

1、 MySQL5.7

MySQL5.7版本中不支援函式索引,因此 遇到函式索引的時候需要進行修改,否則即使查詢的欄位上有索引,執行時也無法使用索引而進行全表掃描,資料量大的表查詢時間會比較長。具體案例如下:

1.1 建立測試表及資料

mysql> use testdb;
Database changed
mysql> create table   tb_function(id int primary key auto_increment,name  varchar(100),create_time datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into  tb_function(name,creatE_time) values('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into  tb_function(name,creatE_time) values('CWQSsar3qcssg','2020-07-01 15:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into  tb_function(name,creatE_time) values('vxfqrt2adafz','2020-07-01 21:30:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into  tb_function(name,creatE_time) values('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into  tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  tb_function(name,creatE_time) values('tuilklmdadq','2020-07-02 15:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  tb_function(name,creatE_time) values('wesv2wqdshehq','2020-07-02 20:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  tb_function(name,creatE_time) values('89yoijnlkwr1','2020-07-03 02:56:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into  tb_function(name,creatE_time) values('olj;nsaaq','2020-07-03 08:41:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into  tb_function(name,creatE_time) values('ygo;jkdsaq','2020-07-03 16:20:00'); 
Query OK, 1 row affected (0.01 sec)

mysql> select  * from tb_function;
+----+-----------------------+---------------------+
| id | name                  | create_time         |
+----+-----------------------+---------------------+
|  1 | anniuadaOAIFAPUHIA    | 2020-07-01 12:00:00 |
|  2 | CWQSsar3qcssg         | 2020-07-01 15:00:00 |
|  3 | vxfqrt2adafz          | 2020-07-01 21:30:00 |
|  4 | etxzwrwbdhegqgaheqhag | 2020-07-02 01:30:00 |
|  5 | awrs433fsgvsfwtwg     | 2020-07-02 03:30:00 |
|  6 | awrs433fsgvsfwtwg     | 2020-07-02 07:32:00 |
|  7 | awrs433fsgvsfwtwg     | 2020-07-02 10:32:00 |
|  8 | tuilklmdadq           | 2020-07-02 15:32:00 |
|  9 | wesv2wqdshehq         | 2020-07-02 20:32:00 |
| 10 | 89yoijnlkwr1          | 2020-07-03 02:56:00 |
| 11 | olj;nsaaq             | 2020-07-03 08:41:00 |
| 12 | ygo;jkdsaq            | 2020-07-03 16:20:00 |
+----+-----------------------+---------------------+
12 rows in set (0.00 sec)

1.2  建立索引

在create_time欄位上建立索引

mysql> alter  table tb_function add key idx_create_time(create_time);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

1.3  按時間查詢

查詢建立時間是2020-07-01那天的所有記錄

mysql> select  *  from  tb_function  where   date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| id | name               | create_time         |
+----+--------------------+---------------------+
|  1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
|  2 | CWQSsar3qcssg      | 2020-07-01 15:00:00 |
|  3 | vxfqrt2adafz       | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
3 rows in set (0.00 sec)

執行計劃如下

mysql> explain select  *  from  tb_function  where   date(create_time)='2020-07-01';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_function | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

執行計劃中可以看出是進行了全面掃描

1.4  優化

因MySQL5.7不支援函式索引,所以需要修改SQL寫法來實現走索引(或者使用虛擬列的方式),上述SQL可以修改為

mysql> select  *  from  tb_function  where   create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+--------------------+---------------------+
| id | name               | create_time         |
+----+--------------------+---------------------+
|  1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
|  2 | CWQSsar3qcssg      | 2020-07-01 15:00:00 |
|  3 | vxfqrt2adafz       | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
3 rows in set (0.00 sec)

執行計劃如下:

mysql> explain select  *  from  tb_function  where   create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table       | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_function | NULL       | range | idx_create_time | idx_create_time | 6       | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

可見,修改後,使用了索引。

2、 MySQL8.0

MySQL8.0的索引特性增加了函式索引。其實MySQL5.7中推出了虛擬列的功能,而MySQL8.0的函式索引也是依據虛擬列來實現的。將上述的案例在MySQL8.0中實現情況如下文所述。

2.1  建立函式索引

在將上述的表及資料在MySQL8.0的例項上建立,然後建立create_time的函式索引,SQL如下

mysql> alter  table tb_function add key idx_create_time((date(create_time))); --   注意裡面欄位的括號
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.2  按時間查詢

mysql> select  *  from  tb_function  where   date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| id | name               | create_time         |
+----+--------------------+---------------------+
|  1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
|  2 | CWQSsar3qcssg      | 2020-07-01 15:00:00 |
|  3 | vxfqrt2adafz       | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
3 rows in set (0.00 sec)

執行計劃如下

mysql> explain select  *  from  tb_function  where   date(create_time)='2020-07-01';
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_function | NULL       | ref  | idx_create_time | idx_create_time | 4       | const |    3 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可見,在MySQL8.0 建立對應的函式索引後,不改變SQL寫法的前提下,查詢的列上進行對應的函式計算後也可以走索引。

關於MySQL函式索引的優化及MySQL8.0函式索引還可以有更多的場景進行測試,建議大家多動手試試,提高SQL改寫及優化的能力。

想了解更多內容或參與技術交流可以關注微信公眾號【資料庫乾貨鋪】或進技術交流群溝通。

 

相關文章