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的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

1. 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集 索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。
2. 通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可 。
3. 注意：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的查询优化器会帮 你优化成索引可以识别的形式。

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

查看表的索引信息

SHOW INDEX FROM 表名;

怎么判断要不要加索引

• 当唯一性是某种数据特征的时候加唯一索引。需要判断定义列的完整性，以此提高查询速度。
• 在频繁的进行分组或者排序时（即建立group by / order by ）的列上建立索引，如果待排序的列有多个，可以进行组合索引。

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

1. 数据较少的表
2. 频繁更新的字段
3. 数据比较重复的字段，比如性别、真假值
4. where条件中用不到的字段不需要建立索引
5. 参与列计算的列

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

（1）对列进行计算或者是使用函数，则该列的索引会失效

（2）不匹配数据类型，会造成索引失效

（3）where语句中使用了IS NULL或者IS NOT NULL，会造成索引失效

（4）使用了反向操作，该索引将不起作用

（5）使用了link操作，索引就将不起作用

（6）在WHERE中使用OR时，有一个列没有索引，那么其它列的索引将不起作用

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

通过explain

索引是用来干什么的

给信息分配一个id，方便能够快速找寻到该数据

索引的使用的场景

通常情况下，应该为需要经常进行查询的列创建索引，特别是那些数据量较大的列。使用索引可以显著提高查询效率。但是，在创建索引的时候，需要注意索引会增加数据表的存储空间和数据修改的成本，因此不能为所有列都创建索引。

以下是创建索引的一些建议：

• 主键应该是唯一的，且自动递增的，因此默认会创建主键索引。
• 对经常用于搜索条件的列进行索引，如where和join语句中使用的列。
• 列值不重复或者重复很少的列上创建索引，如性别、状态等列。
• 对经常需要排序的列进行索引。

需要注意的是，索引并不是万能的，也并不是越多越好。在数据量比较小的情况下，无索引查询的效率可能比使用索引还要高；在数据修改和写入比较频繁的表中，创建过多的索引会影响数据的修改和写入性能，因此需要权衡索引的使用和维护成本，选择合适的索引策略。

事务的概念

MySQL 事务是指一组操作，是一个不可分割的工作单位，可以确保一组数据库操作要么全部执行，要么全部不执行。换句话说，事务是 MySQL 中保证数据一致性和完整性的机制。

在 MySQL 中，事务可以用来保证数据库中数据的一致性和完整性，例如在向数据库中插入或更新一组数据时，要么所有数据插入或更新成功，要么所有操作全部回滚，保持数据的原样。

事务原子性如何保证

在执行到第二个SQL之前，是无法预料这次执行会失败（假设执行到第二个会失败）
因此，当出现执行失败后，有数据库执行一些“还原”操作，来消除前面SQL带来的影响。这个还原性的操作，叫做“回滚”。
因而外面看起来：当执行失败时，一个都没有执行。

MySQL 事务的 ACID 特性

ACID 是事务处理中的关键概念，它指的是：

• 原子性：指事务中的操作要么全部执行成功、要么全部失败回滚，不会出现部分执行的情况。
• 一致性：指在事务开始和结束后，数据库的完整性约束没有被破坏，也就是说事务执行前后都需要满足一些预定义的约束条件。
• 隔离性：指一个事务中的执行不受其他并发事务的影响，它们之间是相互隔离的。
• 持久性：指在事务提交之后，对数据的更新就被永久写入数据库，即使数据库出现故障也能够恢复。

 事务的使用

1. 开启事务：start transaction;
2. 执行多条SQL
3. 回滚或提交：rollback ，commit

说明：rollback即是全部失败，commit即是全部成功

MySQL中事务的隔离级别

隔离级别是指在并发情况下，不同事务之间对数据库操作数据的可见性和影响范围的规定。 MySQL 支持以下四种隔离级别：

• 读未提交：一个事务所做的修改，即使没有提交，对其他事务也是可见的。在这种隔离级别下可能会出现脏读和不可重复读问题。
• 读已提交：一个事务所做的修改，在提交后才会对其他事务可见，同样可能会出现不可重复读问题。
• 可重复读：保证了在同一个事务中对同一数据的读取是一致的，不受其他事务的影响。但是，可能会出现幻读问题。
• 序列化：最严格的隔离级别，它通过对所有事务进行串行化执行来保证事务的隔离性。这种隔离级别能够避免所有并发问题，但是会对数据库性能产生较大影响。

在选择隔离级别时，需要根据应用的实际情况和数据安全性要求来选择，即需要权衡数据安全性和数据库性能。通常情况下，可重复读已经能够满足大部分应用需求。

脏读、不可重复读、幻读

处理脏读：在写的过程中，别人就不能读了（加锁的状态）等修改完了之后，别人才能读（解除加锁） 

处理不可重复读：给读加锁，读的时候不能写，就解决了不可重复读的问题。 

 处理幻读：串行化完成事务，即：写——>读——>写——>读

一个事务在执行中，读到另一个执行中事务的更新 ( 或其他操作 ) 但是未 commit 的数据，这种现象叫做脏读

同一个事务内，同样的读取，在不同的时间段(依旧还在事务操作中！ ) ，读取到了不同的值，这种现象叫做不可重复读

数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读