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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2112954/，如需转载，请注明出处，否则将追究法律责任。