mysql> select * from t_test;
+--------+-------------+---------+
| deptno | dname | loc |
+--------+-------------+---------+
| 10 | Research | Beijing |
| 20 | Maintenance | Huludao |
| 30 | Market | Tianjin |
| 40 | Leader | Qingdao |
+--------+-------------+---------+
4 rows in set (0.00 sec)

mysql> create index idx_test_dname on t_test(dname);
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show keys from t_test\G
*************************** 1. row ***************************
Table: t_test
Non_unique: 1
Key_name: idx_test_dname
Seq_in_index: 1
Column_name: dname
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)

%Lea%沒有使用索引掃描

mysql> explain select * from t_test where dname like '%Lea%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Lea%使用索引掃描

mysql> explain select * from t_test where dname like 'Lea%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | range | idx_test_dname | idx_test_dname | 47 | NULL | 1 | Using where |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
1 row in set (0.12 sec)

mysql> explain select dname from t_test where dname like 'Lea%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | t_test | index | idx_test_dname | idx_test_dname | 47 | NULL | 4 | Using where; Using index |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

MySQL 5.5 模式匹配LIKE

相關文章