MYSQL 索引选择性陷阱

场景：

用户表有1000个用户，其中有一个sex（性别）字段。sex字段上有一个sex索引。

但用户表有超过60%的用户sex为1（男性）

此时，执行语句

 explain select * from user where sex =1;
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--
|  1 | SIMPLE      | user  | NULL       | ALL  | sex           | NULL | NULL    | NULL |   14 |    71.43 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--



explain select * from user where sex = 0;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-
|  1 | SIMPLE      | user  | NULL       | ref  | sex           | sex  | 4       | const |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-

可以看到检索男性用户时，索引失效了。但检索女性时，索引依然可以用。

先看InnoDB的存储结构：

以主键为索引时，速度很快，主键为聚集索引，因为每一条数据对应的主键都是有序的。

以其他列为索引时，索引按照字母表（abc…）的顺序排列的，每一条数据都有一个编号，对应着主键索引。

所以会出现LIKE头匹配命中索引（不一定），而尾匹配与任意匹配全表扫描的问题了。

为什么会出现头匹配仍然不命中索引？前面的性别索引又为什么失效？

索引选择性问题

不严谨的解释
命中的索引值超过总量的25%就可能产生索引选择性问题导致全表扫描（以EXPLAIN为准）

解决方法：

1.组合索引

 select * from user where name="姚" sex =1;

2.引入搜索引擎ES或Solr（更换数据源）

将用户表导入到ES，ES基于分片多线程检索，解决查询慢的问题

3.强制使用索引（以实际运行效果为准）

select * from user force index(sex)  where sex =1;

4.增加缓存，提高全表检索速度（钞能力）

innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=2