木道寻08

这个屌丝很懒，什么也没留下！

热门标签

【MySQL索引和事务】优化数据库性能的关键_mysql事务对性能的影响

作者：木道寻08 | 2024-07-02 21:40:28

踩

mysql事务对性能的影响

6.1 二叉搜索树（Binary Search Tree）

6.2 B树（Balance Tree）

6.3 B+树（Balance Plus Tree）

一、索引

1. 如何理解索引

索引是一种数据结构，用于帮助数据库系统快速检索和定位数据。各类索引有各自的数据结构实现。

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所起的作用类似书籍、文章目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助。

2. 索引类型

在MySQL中，常见的索引类型包括：

普通索引（Normal Index）：最基本的索引，没有任何限制。
唯一索引（Unique Index）：要求索引列的值是唯一的，不允许重复值。
主键索引（Primary Key Index）：主键索引是一种特殊的唯一索引，用于唯一标识每条记录。
组合索引（Composite Index）：将多个列组合起来创建索引，可以提高多列查询的效率。
全文索引（Full-text Index）：用于全文搜索，适合于对文本内容进行搜索的场景。

3. 索引的优点

加快数据检索速度：通过使用索引，数据库可以快速定位到符合条件的记录，减少全表扫描的开销。
提高查询性能：对经常被查询的列创建索引，可以大幅提高查询性能。
加速排序：某些情况下，使用索引可以加速排序操作。

4. 索引的注意事项

索引会占用存储空间：索引会占用额外的存储空间，对于大型表格需要谨慎考虑。
索引会影响写操作性能：每次对记录进行插入、更新、删除操作时，索引也需要更新，可能会影响写操作性能。
不宜过多创建索引：过多的索引会增加维护成本，同时也会增加查询优化器的选择难度。

5. 如何创建和查看索引

在MySQL中，可以使用CREATE INDEX语句来创建索引。例如：

CREATE INDEX idx_name ON table_name(column_name);

可以通过

SHOW INDEX FROM table_name;

语句来查看表的索引信息，包括索引名称、字段、索引类型等。

6.常见的索引数据结构

6.1 二叉搜索树（Binary Search Tree）

二叉搜索树是一种经典的数据结构，每个节点最多有两个子节点，且左子节点的值小于父节点，右子节点的值大于父节点。在MySQL中，使用二叉搜索树实现的索引称为B树（平衡树）或B+树。

6.2 B树（Balance Tree）

B树是一种多路搜索树，能够保持所有叶子节点位于相同高度，且具有良好的平衡性能。在数据库中，B树的节点通常存储多个键和对应的指针，因此可以减少磁盘I/O次数，提高查询效率。

6.3 B+树（Balance Plus Tree）

B+树是在B树基础上进行了优化，将非叶子节点只存储索引字段而不存储数据记录，数据记录只存储在叶子节点上，且通过链表连接起来。B+树的叶子节点形成一个有序链表，便于范围查找和遍历。

6.4 Hash索引

Hash索引基于哈希表实现，通过计算索引列的哈希值来直接定位数据存储位置。Hash索引适合于等值查找，但不支持范围查询和排序操作。

6.5 Full-text索引

Full-text索引用于全文搜索，通常基于倒排索引实现。倒排索引将文档中的关键词映射到文档ID，便于快速定位包含特定关键词的文档。

6.6 R树（R-tree）

R树是一种用于空间索引的数据结构，广泛应用于地理信息系统（GIS）等领域。R树可以高效地存储和查询空间对象的范围查询。

以上是一些常见的索引底层数据结构，在实际应用中，根据不同的需求和场景选择合适的索引类型和数据结构是非常重要的，可以有效提升数据库的查询性能和效率。

二、事务

1. 为什么使用事务

生活中，我们经常要进行微信转账等操作，假设一个微信号对应这样一个表：


create table wechat
(
    id int, 
    account_balance double(10,2)
);

例如，1向2转了500，此时对应的sql语句应该是


update wechat set account_balance = account_balance - 500 where id = 1;
 
update wechat set account_balance = account_balance + 500 where id = 2;

假如在执行以上第一句 SQL 时，出现网络错误，或是数据库挂掉了，1的账户会减少 500 ，但是2的账户上就没有了增加的金额。

解决方案：使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

2. 事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

3. 事务的使用

（ 1 ）开启事务： start transaction;

（ 2 ）执行多条 SQL 语句

（ 3 ）回滚或提交： rollback/commit;

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


start transaction;
//
update wechat set account_balance = account_balance - 500 where id = 1;
//
update wechat set account_balance = account_balance + 500 where id = 2;
commit;

4. 事务的四种特性（ACID）

4.1 原子性（Atomicity）

原子性指事务是一个不可分割的工作单位，要么所有操作都执行成功，要么全部回滚到事务开始前的状态（在事务中，MySQL会记录），即要么全部提交，要么全部撤销。

原子性的实现主要依赖于数据库管理系统（DBMS）的日志和回滚机制。

日志（Log）：当事务开始执行时，DBMS会在日志中记录事务执行所做的所有修改操作，包括数据的更新、插入、删除等。这些日志记录称为事务日志（Transaction Log）。
写前日志（Write-Ahead Logging，WAL）：在事务对数据库进行任何修改之前，DBMS首先将这些修改操作记录到日志中（写前日志），然后再将其应用到数据库中。这样即使在事务执行过程中发生故障，可以通过事务日志回滚数据到事务开始前的状态。
事务提交：当事务执行完所有操作并达到一致性状态时，事务会被提交（Commit）。在提交事务之前，DBMS会将事务的所有操作持久化到磁盘中，同时记录事务提交的标识。
事务回滚：如果事务执行过程中发生错误或者事务被取消，DBMS可以根据事务日志中的信息，将事务的修改操作逆向执行，回滚事务，使数据恢复到事务开始前的状态。

4.2 一致性（Consistency）

一致性指事务在执行前后，数据库从一个一致性状态转换到另一个一致性状态。即事务的执行不能破坏数据库数据的完整性和约束，保证数据的正确性。

4.3 隔离性（Isolation）

隔离性指多个事务并发执行时，每个事务之间是相互隔离的，一个事务的执行不应影响其他事务的执行。隔离性可以防止并发执行的事务相互干扰，避免数据不一致性。

数据库管理系统（DBMS）定义了一些隔离级别来控制事务之间的交互和影响，下面是四个常见的隔离级别：

读未提交（Read Uncommitted）：最低级别的隔离级别。在该级别下，一个事务可以读取另一个事务尚未提交的数据。这可能导致脏读（Dirty Read），即读取到未经验证的不一致的数据。
读已提交（Read Committed）：在该级别下，一个事务只能读取已经提交的数据。这样可以避免脏读，但可能会出现不可重复读（Non-repeatable Read）问题。不可重复读指的是在同一个事务中，多次读取同一数据，在事务执行过程中，数据值可能发生变化。
可重复读（Repeatable Read）：在该级别下，一个事务在执行期间多次读取同一数据时，保证结果一致。其他事务在并发执行时，不允许对当前事务正在读取的数据进行修改。这样可以避免脏读和不可重复读问题，但可能会出现幻读（Phantom Read）。幻读指的是在同一个事务中，多次执行同样的查询，但返回的结果集却发生了变化。
可串行化（Serializable）：最高级别的隔离级别。在该级别下，事务串行执行，相当于每个事务依次执行。保证了最高的隔离性，避免了脏读、不可重复读和幻读的问题。但是，由于串行执行的特性，可能会导致并发性能下降。

需要注意的是，隔离级别越高，数据一致性和安全性越好，但并发性能也会降低。因此，在选择隔离级别时需要根据具体应用场景和需求进行权衡和选择，以平衡数据的一致性和并发性能。

4.4 持久性（Durability）

持久性指一旦事务提交，则其所做的修改将永久保存在数据库中，并不会因系统故障或者其他异常导致数据丢失。即使系统发生故障，数据也能够被恢复。

这四大特性确保了数据库事务的可靠性、稳定性和一致性，保证了数据的完整性和安全性。在进行数据库操作时，合理利用事务，可以有效地管理和维护数据库中的数据。

三、索引和事务对数据库效率提升的不同点

索引和事务都可以提高数据库的效率，但它们的作用和方式略有不同。

1. 索引的作用和效率提升

加快数据检索速度：索引可以大大减少数据库系统需要扫描的数据量，加快数据检索速度。
优化排序和分组：索引可以加速排序和分组操作，提高查询效率。
提高连接性能：对连接操作中的连接字段建立索引可以提高连接的效率。

2. 事务的作用和效率提升

数据一致性：事务可以确保数据库中的数据始终保持一致性，避免了数据的部分更新或错误修改。
并发控制：事务可以通过隔离级别来控制多个并发事务的执行顺序和数据访问，避免数据丢失和不一致问题。
故障恢复：事务保证了在系统故障或异常情况下，数据的持久性和恢复性。

因此，索引主要针对数据的检索、排序和连接等操作进行优化，而事务主要保证数据的一致性、并发控制和故障恢复。两者结合使用可以有效提高数据库的整体性能和可靠性。然而，需要注意的是，索引的过多或者错误使用可能会导致性能下降，而不恰当的事务设计也可能影响并发性能。因此，在实际应用中，需要根据具体场景和需求合理地设计和使用索引和事务。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/木道寻08/article/detail/781013