Mysql的聚集索引、散列度、联合索引以及回表和覆盖索引

1 Mysql索引实战

1.1 实战一 聚集索引

InnoDB聚集索引和普通索引有什么差异？

InnoDB聚集索引的叶子节点存储行记录（也就是一行数据的全部信息），因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了主键，则主键就是聚集索引

（2）如果没有定义主键，则第一个不为null且唯一的 列就是聚集索引

（3）否则，InnDB会创建一个隐藏的row-id作为聚集索引

1.2 实战二 散列度

问题：建立索引反而变慢了？

select * from t_user where sex = ‘男’

列的散列度越低，不推荐建立索引。

散列度公式：count(distinct(column_name)) / count(*)

散列度变得太低了；散列度=一列中数据不同数据的数量/表中数据总量；

可能将不唯一的字段建立索引了，所以会非常非常慢。比如将字段为性别 男女 的字段建立成为了索引。

根据性别建立的索引树会加载出很多的值为男的节点，每找一个男节点，就回去主键的所引树查找一次，会非常的耗时。

1.3 实战三 联合索引

联合索引，最左匹配原则。

必须从联合索引的第一个字段开始，不能跳过，不能中断。

-- 一个索引树的节点会存两个值：name sex 
create INDEX index_name_sex on t_user (name,sex)

前面的字段如果相同，就比较后面的字段。

-- 创建联合索引
CREATE INDEXindex_name_sex on t_user (name,sex)
EXPLAIN SELECT * fromt_user where name = 'name4999008' and sex = '男'
EXPLAIN SELECT * fromt_user where sex = '男' and name = 'name4999008'
EXPLAIN SELECT * fromt_user where name = 'name4999008' 
EXPLAIN SELECT * fromt_user where sex = '男'

1）我们在写语句的条件的时候需要按照定义索引的顺序进行条件的组装，会执行索引。

2）如果没有按照顺序来，优化器就会调整为定义索引时的顺序，会执行索引。

3）sql条件只使用前面的索引条件name，会执行索引

4）sql条件只使用后面的索引条件sex，不会执行索引，因为他是无序的

例如：建立ABC联合索引，就相当于建立了三个索引：

1、A

2、A,B

3、A,B,C

1.4 实战四 回表及其覆盖索引

回表的概念：通过二级索引（辅助索引）树查询索引数据，然后再通过聚集索引树查询完整数据的过程称为回表。

覆盖索引的概念：select字段已经包含在用到的索引中的时候称为覆盖索引。查找的不再是 * ，而是建立索引条件的一部分name或者sex 或者name，sex。

-- 下列操作是否会使用覆盖索引？执行计划中出现Using index 字样，表示用到了覆盖索引，没有产生回表的操作。
EXPLAIN SELECT name,sexfrom t_user where name = 'name4999008'
EXPLAIN SELECT name fromt_user where name = 'name4999008' and sex = '男'
EXPLAIN SELECT * fromt_user where name = 'name4999008'

1.5 索引实战总结

在什么字段上创建索引？

        where、join、 order by 去建立索引。

索引个数不要过度。散列度低的字段，不要建立索引。

随机无序或频繁更新的值，不适合作为主键，推荐使用递增的ID作为主键索引，而不推荐使用UUID或者身份证号等作为索引。递增的ID作为主键索引，数据会按着ID的顺序追加写入，如果使用随机的UUID作为为主键，那么写入数据的顺序是不固定的，可能第一条数据写入到第一个数据中，第二条数据写入到其他的数据页中，会出现数据页分裂和合并的效果。

创建联合索引时避免冗余索引。