这是我参与「第五届青训营 」笔记创作活动的第11天
今天面试被问到了mysql索引，所以正好来整理一下mysql索引的原理及使用
-
MySQL怎么加索引，好处，坏处，分类
-
索引：对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息
-
如何判断一个sql查询是否用到了索引呢？如何确认？
- 使用解释函数explain，只需添加在sql语句之前即可
-
优点：
- 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性
- 可以加快数据的检索速度
- 可以加速表与表之间的连接
- 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间
-
缺点：
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 索引需要占用物理空间，数据量越大，占用空间越大
- 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护
-
分类：
-
普通索引：最基本的索引，它没有任何限制
-
alter table 表名 add index 索引名(index_name) (列名); -
唯一索引：索引列的值必须唯一，且不能为空，如果是组合索引，则列值的组合必须唯一
-
alter table 表名 add unique (列名); -
主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束
-
alter table 表名 add primary key (列名); -
联合索引：在多个字段上建立索引，能够加速查询到速度
- 最左前缀原则（最左侧查询）：如果在(a，b，c)三个字段上建立联合索引，那么它能够加快a|(a，b)|(a，b，c)三组的查询速度
- 建立联合查询时，区分度最高的字段在最左边
-
alter table 表名 add index 索引名(index_name) (列名1，列名2.......); 删除：： DROP INDEX <索引名> ON <表名>
-
-
-
什么时候需要创建索引+什么时候不需要创建索引
-
什么时候需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找
- 查询中统计或者分组的字段
-
什么时候不需要创建索引
-
频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
-
where条件里用不到的字段，不创建索引
-
表记录太少，不需要创建索引
-
经常增删改的表
-
数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引
-
-
为什么需要使用联合索引？
-
减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大减少开销。
-
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的io操作，能提升性能。
-
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，大大提升效率。
-
-
索引失败情况
-
查询条件中有or，除非所有的查询条件都建有索引，否则索引失效
-
like查询是以%开头
-
如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引
-
索引列上参与计算会导致索引失效
-
违背最左匹配原则
-
mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效
-
不在索引列上做任何操作，会导致索引失效而导致全表扫描
-
主键索引也是聚簇索引，非主键索引都是非聚簇索引。除格式信息外，两种索引的非叶子节点都是只存索引数据的，比如索引为id，那非叶子节点就是存的id数据。
叶子节点的区别如下：
-
聚簇索引的叶子节点一般情况下存的是这条数据的所有字段信息。所以我们
select * from table where id = 1的时候，都是要去叶子节点拿数据的。 -
非聚簇索引的叶子节点存的是这条数据所对应的主键和索引列信息。比如这条非聚簇索引是username，然后表的主键是id，那该非聚簇索引的叶子节点存的就是 username 和 id，而不存其他字段。
-
先从非聚簇索引查到主键的值，再根据主键索引去查数据内容，一般情况下要查两次（除非索引覆盖），这也称之为 回表 ，就有点类似于存了个指针，指向了数据存放的真实地址。
一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，每个索引都会有一颗单独的树。
-
B- Tree：n叉查找树，所有节点上都有数据和区间。‘
- 缺点：B树支持按区间查找，但并不高效。因为当B树做范围查询时需要使用中序遍历，那么父节点和子节点也就需要不断的来回切换涉及了多个节点会给磁盘I/O带来很多负担。
-
B+ Tree：n叉查找树
-
只有叶子节点上才有数据，非叶子节点都不存储数据只作为索引。
-
叶子节点之间构成一个从小到大有序的链表互相指向相邻的叶子节点，也就是叶子节点之间形成了有序的双向链表。叶子节点上增加了顺序访问的指针，便于区间查找。
-
这样非叶子可以存储更多的key和指针，n叉树的n更大，I/O次数更少。
-
建索引本身的首要目的：要能尽快的按照区间高效地范围查找
尽量减少磁盘io次数->树状结构->多叉树
mysql的B+树：
好处：
-
B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
-
B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
-
B+树的区间查找更加高效： 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引
B+树的查询是从上往下一层层查询的，一般情况下我们认为B+树的高度保持在3层以内是比较好的，也就是上两层是索引，最后一层存数据，这样查表的时候只需要进行3次磁盘IO就可以了(实际上会少一次，因为根节点会常驻内存)，且能够存放的数据量也比较可观。
如果数据量过大，导致B+数变成4层了，则每次查询就需要进行4次磁盘IO了，从而使性能下降。所以我们才会去计算InnoDB的3层B+树最多可以存多少条数据。
-
存储结构（主索引／辅助索引）
- InnoDB的数据文件本身就是主索引文件。而MyISAM的主索引和数据是分开的。
- InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。
- innoDB是聚簇索引，数据挂在逐渐索引之下
-
锁
- MyISAM使用的是表锁 InnoDB使用行锁
-
事务
- MyISAM没有事务支持和MVCC
- InnoDB支持事务和MVCC
-
主键
- MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址
- InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值
-
外键
- MyISAM不支持，InnoDB支持
