mysql通配符查询 性能_使用mysql5.7新特性解决前通配符查询性能问题
眾所周知，在mysql里的后通配符可以使用索引查找，前通配查詢卻無法使用到索引，即使是使用到了索引，也是使用了索引全掃描，效率依然不高，再MySQL5.7之前，一直都沒有好的辦法解決，但是到了MySQL5.7，自從有了虛擬列，這個問題就好辦多了，能夠已空間換時間。
創建測試表
root@localhost [zeno]>show create tabletest_user\G ;*************************** 1. row ***************************
Table: test_userCreate Table: CREATE TABLE`test_user` (
`uid`int(11) NOT NULLAUTO_INCREMENT,
`name`varchar(32) DEFAULT NULL,
`add_time`datetime DEFAULT NULL,PRIMARY KEY(`uid`),KEY`ix_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6037060 DEFAULT CHARSET=utf81 row in set (0.00sec)
ERROR:
No query specified
使用python插入測試數據
#!/usr/bin/python
importstringimportrandomimportMySQLdbimporttime
conn= MySQLdb.connect(host='IPAddr',
port=3306,
user='zeno',
passwd='zeno',
db='zeno')definsert(para):
i= 11
whileTrue:
r_name= ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(random.randint(10, 30)))printr_name
cursor=conn.cursor()
cursor.execute("INSERT INTO test_user (name,add_time) VALUES ('%s', now())" %str(r_name))
i= i + 1conn.commit()#time.sleep(0.1)
printi
insert(conn)
查看插入的數據量
root@localhost [zeno]>show table status like 'test_user'\G ;*************************** 1. row ***************************Name: test_user
Engine: InnoDB
Version:10Row_format: Dynamic
Rows:6002441Avg_row_length:51Data_length:310165504Max_data_length:0Index_length:0Data_free:5242880Auto_increment:6037060Create_time:2017-11-23 16:25:15Update_time:2017-11-23 16:23:29Check_time:NULLCollation: utf8_general_ci
Checksum:NULLCreate_options:
Comment:1 row in set (0.00sec)
ERROR:
No query specified
root@localhost [zeno]>select * from test_user limit 10;+-----+-------------------------------+---------------------+
| uid | name | add_time |
+-----+-------------------------------+---------------------+
| 1 | U0WUJ3JJ81IRP27BSA4471 | 2017-11-23 15:37:49 |
| 2 | SOLYNM9Q9A5Y94YG | 2017-11-23 15:37:49 |
| 3 | ONNU5PPKXC3GBR | 2017-11-23 15:37:49 |
| 4 | WVC6GOJ29C | 2017-11-23 15:37:49 |
| 5 | Z653X99ZZI | 2017-11-23 15:37:49 |
| 6 | YP92P02DIKQ8O66K | 2017-11-23 15:37:49 |
| 7 | 2X3G6H8849SDP | 2017-11-23 15:37:49 |
| 8 | 9N9F668XQMTRQSCNE0FWJBMMJEFC0 | 2017-11-23 15:37:50 |
| 9 | 15XAHWZ1IJBP6P4EKCH | 2017-11-23 15:37:50 |
| 10 | VHQJQGQC7U | 2017-11-23 15:37:50 |
+-----+-------------------------------+---------------------+
10 rows in set (0.00 sec)
開始測試
一、驗證查詢條件中使用后通配符的情況
root@localhost [zeno]>select * from test_user where name like '9N9F668XQ%';+-----+-------------------------------+---------------------+
| uid | name | add_time |
+-----+-------------------------------+---------------------+
| 8 | 9N9F668XQMTRQSCNE0FWJBMMJEFC0 | 2017-11-23 15:37:50 |
+-----+-------------------------------+---------------------+
1 row in set (0.00sec)
root@localhost [zeno]>explain select * from test_user where name like '9N9F668XQ%';+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_user | NULL | range | ix_name | ix_name | 99 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
600W的數據，執行時間0.00sec，已經是毫秒級查詢了
從執行計劃中可以看出，type=range， key = 'ix_name'，證明是對索引ix_name進行了范圍查找，所以，能很快地得到結果
二、驗證查詢條件中使用前通配符的情況
root@localhost [zeno]>select * from test_user where name like '%WJBMMJEFC0';+-----+-------------------------------+---------------------+
| uid | name | add_time |
+-----+-------------------------------+---------------------+
| 8 | 9N9F668XQMTRQSCNE0FWJBMMJEFC0 | 2017-11-23 15:37:50 |
+-----+-------------------------------+---------------------+
1 row in set (3.84sec)
root@localhost [zeno]>explain select * from test_user where name like '%WJBMMJEFC0';+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 6002441 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
600萬的數據，運行了3.84sec，速度非常慢
從執行計劃中type=‘ALL’可以看出是進行了全表掃描，掃描完之后，再根據where條件找出合適的數據
在MySQL5.7之前，對于這種條件中使用了前通配符的查詢，幾乎就是束手無策，但是，MySQL5.7中增加了一項新功能，可以用較小的代價實現快速查詢
創建虛擬列
root@localhost [zeno]>alter table test_user add r_name varchar(32) generated always as (reverse(`name`));
Query OK,0 rows affected (0.44sec)
Records:0 Duplicates: 0 Warnings: 0
在虛擬列上創建索引(跟一般創建索引無異)
root@localhost [zeno]>create index ix_r_name ontest_user(r_name) ;
Query OK,0 rows affected (41.90sec)
Records:0 Duplicates: 0 Warnings: 0
問題來了，已經創建了虛擬列，也創建了所以，怎么實現對前通配符的快速查詢呢？
先用一個簡短的數字來說明一下思路：假設要查詢的列的最終值為‘0123456789’，前通配查詢的時候，條件是 name like '%6789'，但是已經創建了虛擬列，虛擬列的效果是把原來的數據反轉，也就是變成了‘9876543210’，那么，查詢的條件變成了name like '9876%'，但是，不可能是每次都要自己計算一下，把'6789'換成‘9876’
因此，在查詢的時候，還要取巧的一步，條件中再次把輸入的值反轉，結果如下
root@localhost [zeno]>select * from test_user where r_name like concat(reverse('WJBMMJEFC0'),'%');+-----+-------------------------------+---------------------+-------------------------------+
| uid | name | add_time | r_name |
+-----+-------------------------------+---------------------+-------------------------------+
| 8 | 9N9F668XQMTRQSCNE0FWJBMMJEFC0 | 2017-11-23 15:37:50 | 0CFEJMMBJWF0ENCSQRTMQX866F9N9 |
+-----+-------------------------------+---------------------+-------------------------------+
1 row in set (0.00sec)
root@localhost [zeno]>explain select * from test_user where r_name like concat(reverse('WJBMMJEFC0'),'%');+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | range | ix_r_name | ix_r_name | 99 | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
從執行結果來看，效果已經達到了，600W的數據也只是執行了0.00sec
三、在條件中同時使用了前通配符和后通配符的情況，暫時沒有好的解決辦法
參考文檔：
MySQL官方介紹虛擬列：https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
以上，如有錯謬，請不吝指正。
原創作品，如需轉載，請標明出處，謝謝~
總結
以上是生活随笔為你收集整理的mysql通配符查询 性能_使用mysql5.7新特性解决前通配符查询性能问题的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 太原有哪些工艺品 山西太原特产
- 下一篇： mysql中非主键不能自增吗_mysql