Mysql索引会失效的几种情况分析

Mysql索引会失效的几种情况分析

CREATE TABLE `members` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
  `qq` int(10) DEFAULT NULL COMMENT '密码',
  `register_ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册IP',
  `wechat` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `password` (`password`(191)) USING BTREE,
  KEY `qq` (`qq`)
) ENGINE=MyISAM AUTO_INCREMENT=286 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1.explain 查看索引使用

• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
• possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引奖杯列出，但不一定被查询实际使用。
• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
• key_len列，索引长度。
• rows列，扫描行数。该值是个预估值。
• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

各列表的值详情：https://www.cnblogs.com/miskis/p/9081187.html

1.1type类型

type	解释
null	–
system	const的特例，仅返回一条数据的时候。
const	查找主键索引，返回的数据至多一条（0或者1条）。 属于精确查找
eq_ref	查找唯一性索引，返回的数据至多一条。属于精确查找
ref	查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条
range	查找某个索引的部分索引，一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行，属于范围查找
index	查找所有的索引树，比ALL要快的多，因为索引文件要比数据文件小的多。
ALL	不使用任何索引，进行全表扫描，性能最差。

从下到上逐渐变好，使用的索引至少要达到range 级别。

2.使用or / !=(<>) / not null / is null

or影响

mysql> explain select * from members where name = '1' or wechat = '1';
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | members | ALL  | name,name_password | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+

!=

mysql> explain select * from members where name != '1';
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | members | ALL  | name,name_password | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+

is null

mysql> explain select * from members where name is not null;
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | members | ALL  | name,name_password | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+

is not null

mysql> explain select * from members where name is not null;
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | members | ALL  | name,name_password | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+

3. 字符串类型，where的时候带引号

mysql> explain select * from members where name = '1';
+----+-------------+---------+-------+--------------------+------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys      | key  | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+--------------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | members | const | name,name_password | name | 402     | const |    1 |       |
+----+-------------+---------+-------+--------------------+------+---------+-------+------+-------+
1 row in set

mysql> explain select * from members where name = 1;
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | members | ALL  | name,name_password | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+---------+------+--------------------+------+---------+------+------+-------------+

4. 不建议使用%前缀模糊查询

例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。
但是可以使用LIKE “name%”。

mysql> explain select name from members where name like '%1';
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | members | index | NULL          | name | 402     | NULL |    2 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set

mysql> explain select name from members where name like '1%';
+----+-------------+---------+-------+--------------------+------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys      | key  | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+--------------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | members | index | name,name_password | name | 402     | NULL |    2 | Using where; Using index |
+----+-------------+---------+-------+--------------------+------+---------+------+------+--------------------------+

那如何查询%name%？答案：使用全文索引。

# 更新全文索引
ALTER TABLE `members` ADD FULLTEXT INDEX `name` (`name`);
mysql> explain select name from members where match(name) against ('1' in boolean mode);
+----+-------------+---------+----------+---------------+------+---------+-----+------+-------------+
| id | select_type | table   | type     | possible_keys | key  | key_len | ref | rows | Extra       |
+----+-------------+---------+----------+---------------+------+---------+-----+------+-------------+
|  1 | SIMPLE      | members | fulltext | name          | name | 0       |     |    1 | Using where |
+----+-------------+---------+----------+---------------+------+---------+-----+------+-------------+

5.索引列上少计算

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
其中后面那句varchar引号不能丢也是如此，如果是丢了，则会进行自动类型转换，就相当于计算。

待续。。。。。