很多開發人員在使用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_timedate_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_timedate_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改寫及優化的能力。
以上就是MySQL 函數索引的優化方案的詳細內容，更多關于MySQL 函數索引及優化方案的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- Zend studio for eclipse中使php可以調用mysql相關函數的設置方法
- MySQL中聚合函數count的使用和性能優化技巧
- MySQL利用procedure analyse()函數優化表結構
- 詳解Mysql函數調用優化
