关于MySql索引，你需要知道！！！

实战一 聚集索引

聚集索引(索引和数据放在一起的)聚集索引一旦创建就不会改变，只能自己删除

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

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

创建了一张表是innodb,那么这个表一定有一个聚集索引

（1）如果表定义了PK(主键)，则PK就是聚集索引；，在stu表中id是主键，所以这里id是默认的索引

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；(查询的时候会显示)当去除掉not null unique的时候索引还是存在的，因为索引一旦创建就不会改变，除非自己手动删除

当我取消了id为主键，设置name不为空且是唯一约束,可以发现此时的索引默认是name

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；(这个查询的时候不显示)

当啥也没有的时候，没有主键，没有字段不为空没有唯一约束的时候，会创建一个隐藏的row_id作为聚集索引，这里有一个隐藏的索引，不显示

实战二 散列度 意思就是什么字段适合建立索引

问题：为什么有的字段建立索引，查询速度反而变慢了呢？

列的散列度越低，不推荐建立索引。值就几个来回重复比如说sex散列度低，只有男和女

散列度公式：count(distinct(column_name)) / count(*) 去重之后的数据/所有的数据

现在有一列字段，假如列的名称是 sex性别，存储的值是 男或者女，表中有500w条数据，现在执行sql：select * from t_user where sex = '男'  查询花费的时间2秒多，现在对sex 列建立了索引，再执行之前的sql语句，但是花费的时间变成了 20多秒，建立索引后没有变快，反而变慢了，这是为啥呢？？？

实战三 联合索引 就是两个索引键创建的索引

联合索引，最左匹配原则。索引如何创建的

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

两个字段一般是同时出现的，推荐建立联合索引，例如高考成绩查询：身份证号+考号

-- 创建联合索引 这个联合索引受用于123，4是不受用的

CREATE INDEX index_name_sex on t_user (name,sex)

EXPLAIN SELECT * from t_user where name = 'name4999008' and sex = '男'

EXPLAIN SELECT * from t_user where sex = '男' and name = 'name4999008'  优化最优计划

EXPLAIN SELECT * from t_user where name = 'name4999008'

EXPLAIN(查看执行计划) SELECT * from t_user where sex = '男' 这个是不能查询到的

基于成本的计算 优化器  Cost Based Optimizer

例如：建立 A B C联合索引，就相当于建立了3个索引。

第一个：A

第二个：A B

第三个：A B C

实战四 回表及其覆盖索引

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

覆盖索引的概念：select字段已经包含在用到的索引中的时候称为覆盖索引。覆盖这里不写*，查到的字段是我索引的字段，那么直接从辅助树中查索引的字段就可以了，不需要再回表了，只是查一列字段即可，回表是*有所有的东西，完整数据需要回表的操作

下列操作是否会使用覆盖索引？执行计划中出现Using index 字样，表示用到了覆盖索引，没有产生回表的操作。

EXPLAIN SELECT name,sex from t_user where name = 'name4999008'

EXPLAIN SELECT name from t_user where name = 'name4999008' and sex = '男'

EXPLAIN SELECT * from t_user where name = 'name4999008'

索引实战总结

在什么字段上创建索引？where、join、 order by 去建立索引。

索引个数不要过度。

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

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

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

索引失效总结：在该条件上进行了计算会失效

索引列上使用函数、表达式、运算符。

出现类型隐式转换，例如：字符串忘记编写引号。

EXPLAIN select * from t_user where name = 122

like条件字符前面带% (最左前缀)(不一定)

负向查询<> != NOT IN (不一定)

EXPLAIN select * from t_user where id not in(5000007,5000008,5000009)

面试题：什么会造成索引失效？

1.索引列上使用函数、表达式、运算符

2.索引列出现类型隐式转换，例如：字符串忘记编写引号

3.like条件字符前面带%(最左前缀)(不一定)