索引失效情况：索引使用
使用explain 分析索引是否使用
explain select *from tbl_file where file_sha1='0c4b1036671ad0b2727a9d18349d02feae0e8161'; +----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tbl_file | NULL | const | idx_file_hash | idx_file_hash | 120 | const | 1 | 100.00 | NULL | +----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
possible_keys ：可能索引 key：索引键
- 索引列上进行位运算 失效
使用substring
explain select *from tbl_file where substring(file_sha1,1,2)='0c'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_file | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)key 为NULL 索引失效
- 字符串类型不加引号 失效
explain select *from tbl_file where file_sha1=123456; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_file | NULL | ALL | idx_file_hash | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec)
- 索引字段使用模糊匹配，头部匹配失效，尾部匹配不会
##头部匹配 失效 mysql> explain select *from tbl_file where file_sha1 like '%161'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_file | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ##尾部匹配 索引未失效 mysql> explain select *from tbl_file where file_sha1 like '0c4%'; +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | tbl_file | NULL | range | idx_file_hash | idx_file_hash | 120 | NULL | 1 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
- or连接的条件
用or分割开的条件，如果or前的条件中 的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
mysql> explain select *from tbl_file where file_sha1='123456' or ext1=0; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tbl_file | NULL | ALL | idx_file_hash | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
- 数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
