MySQL索引和事务

索引的作用与概念

MySQL有哪几种索引类型

如何提高查找效率

聚簇索引与非聚簇索引

覆盖索引

索引的优点和缺点

索引的一些基本操作

索引优化

B树、B+树、Hash、红黑树的区别

B树与B+树的区别

MySQL为什么使用B+树作为索引

联合索引中的顺序

MySQL的最左前缀原则

查看表的索引信息

怎么判断要不要加索引

所有的字段都适合建索引吗

如何评估一个索引创建的是否合理？索引在哪些情况下会失效？如何避免索引失效？

如何判断数据库的索引有没有生效？

索引是用来干什么的

索引的使用的场景

事务的概念

事务原子性如何保证

MySQL 事务的 ACID 特性

事务的使用

MySQL中事务的隔离级别

脏读、不可重复读、幻读

索引的作用与概念

索引就相当于书的目录，主要作用就是提高查找效率。

MySQL有哪几种索引类型

1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引。这里所描述的是索引存储时保存的形式。

2、从应用层次来分：普通索引，唯一索引，复合索引。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
非聚簇索引：不是聚簇索引，就是非聚簇索引。

如何提高查找效率

当从数据库中进行查找操作时，比如根据条件 id=3 查找：可以遍历表进行查询，但是这种方式效率比较低。如何提高效率呢？

这就需要想办法尽量避免遍历，可以通过一些特殊的数据结构来表示一些记录的特征，通过这些特征来减少比较次数，加快比较的速率。

当查找效率提高时，也将会付出一些代价：

就相当于与给书加上目录会费一些纸，即加上索引就会消耗一定的存储空间，数据量越大，消耗的空间就越大

书的目录确定了，后续每次对书的内容进行调整时，都可能会影响到目录的准确性，就需要重新调整目录，同理，数据库的索引也是一样的，当进行增删查改时，往往也需要同步的调整索引的结构

聚簇索引与非聚簇索引

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描。
通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可。
注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

覆盖索引

覆盖索引是指一个索引包含了查询所需的所有列数据，因此在查询时可以直接使用索引返回结果，而不需要回到数据表中查找数据行，从而大幅提高查询性能。

举个例子，假设有一张订单表，包含了订单编号、订单金额、订单日期等列，如果我们需要查询某个日期范围内的所有订单金额，我们可以在订单日期列上创建一个索引，如果该索引还包含了订单金额列，那么查询时就可以直接使用这个索引返回查询结果，而不需要再回到订单表中查找对应的订单金额，这就是覆盖索引。

可以看出，覆盖索引可以大幅提高查询性能，尤其是在大数据量的情况下。但是，在创建覆盖索引时需要注意，索引需要包含查询所需的所有列数据，因此索引的大小可能会比较大，从而增加读取磁盘的成本，也需要权衡创建索引的成本和查询性能的提高。

索引的优点和缺点

索引带来的好处：提高了查找得速度
索引带来得坏处：占用了更多的空间，拖慢了增删查改的速度

从表面来上看，似乎索引的坏处比索引带来的好处要多。但！这不必意味着弊大于利！！
因为在实际需求的场景中，查询操作往往是最高频率的操作。
相对于“增删改” 的使用频率则低的可怜。
因此，查询作为一个高频操作，索引对其来说是不可缺少的，

另外，有了索引之后对于查询的效率的提升使非常巨大的！！！
当MySQL里面的数据量级达到千万级别的时候（一个表里就有几千万，甚至破亿的数据）再去遍历表，就会非常非常的低效！！！

在另一方面：MySQL在进行数据比较的时候，不是像我们编程那样，一个for循环（这样的想法是错误的）。
编程上的查询是在内存中的比较；MySQL 中的比较是在硬盘上比较。
也就是说：在MySQL中的每一次比较都会涉及到硬盘的 IO 操作。

索引的一些基本操作

1.查看索引：show index from 表名
有些约束是自动带索引的，比如：primary ，unique
2.给指定列创建索引create index 索引名字 on 表名(列名);
例如：create index class_index on student(class);-- 给class这一列加上索引
注意：创建索引是一个非常低效的事情，尤其是当前表里面已经有很多数据的时候
所以，当你针对线上的数据库的时候，如果这个表没有索引的时候，不要贸然去加上索引…
3.删除索引drop index 索引名字 on student 表名
例如：drop index class_index on student;
注意：删除索引和创建索引一样，都是效率比较低的操作,也容易把数据库搞挂
因此，在创建表的时候，就应该把索引规划好。

索引优化

确定需要创建的索引的列：

可以通过查询SQL语句的执行计划，找出哪些SQL语句的查询效率比较低，然后确定需要创建索引的列。

确定索引类型：

不同的索引类型在不同的场景下具有不同的优势，需要根据具体的业务需求和场景来选择合适的索引类型。

避免在索引列上使用函数或者表达式：

在索引列上使用函数或者表达式会导致索引失效，从而降低查询性能。

避免使用 OR 连接条件：

在查询语句中使用 OR 连接条件会导致索引失效，从而降低查询性能。

避免在表达式左侧使用运算符：

在查询语句中，将运算符放在表达式的左侧会导致索引失效，从而降低查询性能。

B树、B+树、Hash、红黑树的区别

BTree：

B树是一棵多路平衡查找树

B树的每个节点都存储索引和数据（key和data）
每个节点中的关键字都按照从小到大的顺序排列
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同

B+Tree：

B+树也是一棵多路平衡查找树

B+树中非叶子节点不存储数据，只存储索引。对于非叶子节点中key都按照从小到大的顺序排列，非叶子节点中的每一个key，都会出现在子节点中，是子节点中最大或最小元素。所有的非叶子节点起到了索引作用。
只有叶子节点存储data，叶子节点包含了这棵树的所有数据。
叶子节点依据关键字的大小从小到大顺序链接，形成一个有序链表。便于区间查找和遍历。
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。

Hash索引：
虽然可以快速定位，但是没有顺序，IO复杂度高；
适合等值查询，如=、in()，不支持范围查询；
因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序；
如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
红黑树：
树的高度随着数据量增加而增加，IO代价高。

B树与B+树的区别

B+树的优势：

IO次数更少
查询性能很稳定
范围查询更简便

1.B树的每个节点都存储key和data，B+树只有叶子节点存储data，叶子节点包含了这棵树的所有数据，这样一个叶子节点可以存储更多的key,可以使树更矮，所以 IO操作的次数更少。

2.B+树中所有的叶子节点构成一个有序链表，可以按照关键子码排序的次序遍历全部记录，由于数据顺序排列并相连，所以编译区间查找和搜索。而B树则需要进行每一层的遍历，相邻的元素可能在内存中并不相邻。

3.在B树中，当要查找的值恰好在一个非叶子节点时，查找到该节点就会成功并结束查询，而B+树由于非叶子节点只是索引部分，这些节点中只含有其子树中最大或最小关键字，当非终端节点的关键字等于给定值时，查找并不终止，而是继续向下查找直到叶子节点。因此，在B+树中，无论是否查找成功，都是走了一条从根节点到叶子节点的路径。

MySQL为什么使用B+树作为索引

（1）B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

（2）B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

（3）B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

联合索引中的顺序

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为: MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

MySQL的最左前缀原则

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

在设计索引时要考虑使用最左前缀原则，将最常用的列放在最左边。这样可以使索引更加高效，查询速度更快。