【Java 面试八股学习 自用版】MYSQL优化-------索引

MYSQL 索引

什么是索引

①index 是帮助MySql高效获取数据的数据结构（有序）。
②提高数据检索的效率，降低数据库的I0成本(不需要全表扫描)
③通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

为什么不对表中所有的列都建索引呢？？？
————①当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引的底层数据结构

InnoDB引擎使用B+树存储索引

B+树 只有叶子阶段存储数据。
与B树相比 ①磁盘读写代价更低（比b树更加矮胖）只有叶子阶段存储数据 ②查询效率更稳定，阶数更多，路径更短 ③ 便于扫库和区间查询 叶子节点是一个双向链表

聚簇索引（聚集索引），非聚簇索引（二级索引），回表查询

概念：
聚簇索引 ：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（必须有，且唯一）

 选取原则： 
 有主键，主键索引就是； 
 无主键，使用第一个**唯一（UNIQUE）索引**作为聚簇索引；
 都没有innoDB**自动生成**一个rowid作为隐藏的聚簇索引。					

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以有多个）

两者区别在于，前者叶子节点存储了一整行数据，而后者叶子节点只有主键

回表查询： 先通过二级索引，得到对应的主键，然后使用聚簇索引查询整行的数据。

覆盖索引

概念：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
通俗来说，就是一次查询就能查询到所有数据，不需要回表查询

主键索引一定是覆盖索引
二级索引中，若查询的是i主键以及二级索引本身

索引创建的原则

数据量大，查询频繁的表
常作为查询条件（where）、排序order 、分组（group by）的字段
选择区分度高的列作为索引，尽量建立唯一索引
字符串类型 ，字段过长可以建立前缀索引
尽量使用联合索引，减少单列索引 联合索引很多时候可以覆盖索引，效率高避免回表，节省时间
控制索引的数量

为什么不对表中所有的列都建索引呢？？？
————①当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引列不能存null值，创建表时使用NOT NULL约束。优化器知道每列是否包含NULL时可以确定那个索引最有效用于查询

索引失效

使用 explain 判断是否失效

• 违反最左前缀法则 。索引多列时 ，查询从索引最左端前列开始且不跳过索引中的列
• 范围查询最右的列，不能使用索引
• 再索引列上运算操作
• 字符串不加单引号
• 以%开头的Like模糊查询。尾部模糊不会失效，头部模糊会！！！！！