很多開(kāi)發(fā)人員在使用MySQL時(shí)經(jīng)常會(huì)在部分列上進(jìn)行函數(shù)計(jì)算等，導(dǎo)致無(wú)法走索引，在數(shù)據(jù)量大的時(shí)候，查詢效率低下。針對(duì)此種情況本文從MySQL5.7 及MySQL8.0中分別進(jìn)行不同方式的優(yōu)化。

1、 MySQL5.7

MySQL5.7版本中不支持函數(shù)索引，因此 遇到函數(shù)索引的時(shí)候需要進(jìn)行修改，否則即使查詢的字段上有索引，執(zhí)行時(shí)也無(wú)法使用索引而進(jìn)行全表掃描，數(shù)據(jù)量大的表查詢時(shí)間會(huì)比較長(zhǎng)。具體案例如下：

1.1 創(chuàng)建測(cè)試表及數(shù)據(jù)

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 創(chuàng)建索引

在create_time字段上創(chuàng)建索引

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 按時(shí)間查詢

查詢創(chuàng)建時(shí)間是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)

執(zhí)行計(jì)劃如下：

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)

執(zhí)行計(jì)劃中可以看出是進(jìn)行了全面掃描

1.4 優(yōu)化

因MySQL5.7不支持函數(shù)索引，所以需要修改SQL寫(xiě)法來(lái)實(shí)現(xiàn)走索引（或者使用虛擬列的方式），上述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)

執(zhí)行計(jì)劃如下：

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)

可見(jiàn)，修改后，使用了索引。

2、 MySQL8.0

MySQL8.0的索引特性增加了函數(shù)索引。其實(shí)MySQL5.7中推出了虛擬列的功能，而MySQL8.0的函數(shù)索引也是依據(jù)虛擬列來(lái)實(shí)現(xiàn)的。將上述的案例在MySQL8.0中實(shí)現(xiàn)情況如下文所述。

2.1 創(chuàng)建函數(shù)索引

在將上述的表及數(shù)據(jù)在MySQL8.0的實(shí)例上創(chuàng)建，然后創(chuàng)建create_time的函數(shù)索引，SQL如下

mysql> alter  table tb_function add key idx_create_time((date(create_time))); --   注意里面字段的括號(hào)
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.2 按時(shí)間查詢

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)

執(zhí)行計(jì)劃如下

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)

可見(jiàn)，在MySQL8.0 創(chuàng)建對(duì)應(yīng)的函數(shù)索引后，不改變SQL寫(xiě)法的前提下，查詢的列上進(jìn)行對(duì)應(yīng)的函數(shù)計(jì)算后也可以走索引。

關(guān)于MySQL函數(shù)索引的優(yōu)化及MySQL8.0函數(shù)索引還可以有更多的場(chǎng)景進(jìn)行測(cè)試，建議大家多動(dòng)手試試，提高SQL改寫(xiě)及優(yōu)化的能力。

想了解更多內(nèi)容或參與技術(shù)交流可以關(guān)注微信公眾號(hào)【數(shù)據(jù)庫(kù)干貨鋪】或進(jìn)技術(shù)交流群溝通。