未建索引
mysql> alter table modulestatus drop index imei;
Query OK, 457922 rows affected (4.29 sec)
Records: 457922 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) from modulestatus where imei like '1%';
+----------+
| count(*) |
+----------+
| 270 |
+----------+
1 row in set (0.53 sec)
mysql> SELECT count(*) from modulestatus where imei like '%1';
+----------+
| count(*) |
+----------+
| 35499 |
+----------+
1 row in set (0.66 sec)
mysql> SELECT count(*) from modulestatus where imei like '1%' and '9%';
+----------+
| count(*) |
+----------+
| 270 |
+----------+
1 row in set, 1 warning (0.49 sec)
建立索引
mysql> alter table modulestatus add index (imei);
Query OK, 457922 rows affected (31.67 sec)
Records: 457922 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) from modulestatus where imei like '1%';
+----------+
| count(*) |
+----------+
| 270 |
+----------+
1 row in set (0.04 sec)
mysql> SELECT count(*) from modulestatus where imei like '%1';
+----------+
| count(*) |
+----------+
| 35499 |
+----------+
1 row in set (0.80 sec)
mysql> SELECT count(*) from modulestatus where imei like '1%' and '9%';
+----------+
| count(*) |
+----------+
| 270 |
+----------+
1 row in set, 1 warning (0.00 sec)
结论
1、like %keyword：索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
2、like keyword%：索引有效。
3、like %keyword% 索引失效，也无法使用反向索引。
未建索引
mysql> SELECT count(*) from modulestatus where imei like '%1%';
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.73 sec)
mysql> SELECT count(*) from modulestatus where locate('1',imei);
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.76 sec)
建立索引
mysql> alter table modulestatus add index (imei);
Query OK, 457922 rows affected (11.06 sec)
Records: 457922 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) from modulestatus where imei like '%1%';
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.93 sec)
mysql> SELECT count(*) from modulestatus where locate('1',imei);
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.95 sec)
结论
LOCATE(str,colum)函数，可以代替column like '%str%'，但效率并没有明显的差别。
建立索引后，都会减慢like '%str%' 与locate(str,colum)函数查询的速度。
未建立索引
mysql> SELECT count(*) from modulestatus where imei like '%1%';
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.73 sec)
mysql> SELECT count(*) from modulestatus where instr(imei,'1');
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.79 sec)
建立索引
mysql> SELECT count(*) from modulestatus where imei like '%1%';
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (1.00 sec)
mysql> SELECT count(*) from modulestatus where instr(imei,'1');
+----------+
| count(*) |
+----------+
| 326019 |
+----------+
1 row in set (0.84 sec)
结论
instr(colum,str)函数，可以代替column like '%str%'，但效率并没有明显的差别。
建立索引后，都会减慢like '%str%' 与instr(colum,str)函数查询的速度
未建立索引
mysql> SELECT count(*) from modulestatus where imei like '%1';
+----------+
| count(*) |
+----------+
| 35499 |
+----------+
1 row in set (0.90 sec)
mysql> SELECT count(*) from modulestatus where reverse(imei) like reverse('%1');
+----------+
| count(*) |
+----------+
| 35499 |
+----------+
1 row in set (0.70 sec)
建立索引
mysql> alter table modulestatus add index (imei);
Query OK, 457922 rows affected (10.04 sec)
Records: 457922 Duplicates: 0 Warnings: 0
mysql> SELECT count(*) from modulestatus where imei like '%1';
+----------+
| count(*) |
+----------+
| 35499 |
+----------+
1 row in set (1.06 sec)
mysql> SELECT count(*) from modulestatus where reverse(imei) like reverse('%1')
+----------+
| count(*) |
+----------+
| 35499 |
+----------+
1 row in set (0.84 sec)
结论
1、建立索引，会降低like '%str'和reverse(column) like reverse('%str′)的查询速度。
2、不管是建立索引，还是没有建立索引，reverse(column) like reverse('%str′)都要比column like '%str'要快。
注：在执行column like '%str'的时候，执行计划显示，消耗值，io值，cpu值均非常大，原因是like后面前模糊查询导致索引失效，进行全表扫描。
使用翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全扫描。有效降低消耗值，io值，cpu值这三个指标，尤其是io值的降低。
