31.进阶-索引-小结

31.进阶-索引-小结

（1）总结

1、索引概述

索引是高效获取数据的数据结构；

2、索引结构

B+Tree

解释：在B+Tree中所有的数据都会出现在叶子节点，而且叶子节点形成了双向链表

Hash索引

解释：Memory存储引擎中支持的索引结构，他就是一个哈希表。对于哈希索引来说，最大 的特点就是他的检索效率很高，只需要计算出字段值的哈希值，然后直接定位到对应的数据。如果存在对应的哈希碰撞，这个时候就需要沿着链表再去找到对应的数据。它的性能相对来说比较高，对于Hash索引来说他还有一个缺点，这个缺点就是他只能支持精确匹配，不支持范围查询及索引的排序。

3、索引的分类

主键索引：我们在建表的时候，只要这张表你指定了主键，那么主键默认会建立一个主键索引

唯一索引：如果有一列它的字段值是不会重复的，比如身份证号，比如用户名或者用户手机号，此时可以针对这个字段建立唯一索引

常规索引：正常建立的索引

全文索引：不做介绍

根据索引的存储结构分为两类：

聚集索引：在InnoDB这种引擎的表结构中，是必须存在的，而且只能有一个聚集索引。聚集索引最大的特点就是这个B+Tree叶子结点下面挂的是这一行的数据，而二级索引叶子结点下面挂的是对应的主键。默认主键索引是聚集索引，如果没有主见，第一个唯一索引为聚集索引，如果唯一索引也没有，此时Mysql会自动生成一个隐藏的rowid来作为聚集索引。

二级索引

4、索引语法

create [unique] index xxx on xxx(xxx);
show index from xxxx;
drop index xxx on xxxx;

5、SQL性能分析

执行频次：可以判断当前数据库是以插入为主，还是查询为主…

慢查询日志：通过慢查询日志可以定位出哪些sql执行比较耗时。只会记录超过了预设时间的sql。

profile：监控每一条sql语句的耗时，以及具体的时间耗费在哪个阶段了

explain：用的最多。通过explain查看sql语句的执行计划，来评判sql语句的执行性能

6、索引使用

联合索引：最左前缀法则。最左边的列必须存在，如果最左边的列不存在，整个联合索引全都失效。如果最左边的列存在，但是中间跳过了某一列，那么后面的列索引会失效。而且联合索引中我们在进行范围查询的时候，范围查询右侧的列会失效，我们尽量使用大于等于和小雨等于这样的操作符，避免使用大于和小于。

索引失效：不要在索引列上进行函数运算，字符串不加引号，会造成隐式类型转换，like模糊匹配%不在最后，or连接的条件如果一侧有索引一侧没有索引，mysql评估走全表扫描比走索引快（数据分布的影响）

SQL提示：执行sql语句的时候，如果有很多的索引，mysql会根据它内部的策略来选择使用哪个索引，我们可以给mysql一些提示，告诉他我要用哪个索引

覆盖索引：查询返回的列在索引结构中都包含了，不需要回表查询。回表查询是指我们在查询的的时候先走二级索引检索到这一行数据的id，再根据id到聚集索引中查找到这一行的数据。

前缀索引：遇到一些字符串长度较长，可以建立前缀索引，缩小索引的体积。

单列/联合索引：推荐使用联合索引。因为联合索引性能高。如果使用得当，可以避免回表查询。

7、索引设计原则

针对哪些表设计索引：数据量大，而且查询频次高。

针对这些表的哪些字段建立索引：经常在where之后。order by、group by之后出现的字段来建立对应的索引。

建立什么样的索引：①如果这一列是唯一的，建立唯一索引，区分度高。②如果能建立联合索引，尽量使用联合索引。③如果涉及到的字符串较长，尽量使用前缀索引。