MySQL 数据库 索引和事务

MySQL 数据库 索引和事务完整攻略

索引

索引的作用和原理

索引可以帮助MySQL快速的定位符合特定条件的数据，常用的索引类型包括B-Tree索引、Hash索引等。其中B-Tree索引是MySQL最常用的索引类型，其原理是对于B-Tree索引的每一层，左边的值小于等于中间值，右边的值大于中间值，每个节点都包含了指向下一级节点的指针，根据B-Tree索引的这种排列方式，可以快速查找到符合条件的数据。

创建索引

对于MySQL中的表，可以通过使用CREATE INDEX语句来创建索引，例如：

CREATE INDEX idx_name ON user(name);

上述语句创建了一个名为idx_name的索引，作用于名为user的表的name字段上。

使用索引

在查询MySQL中的数据时，使用了索引可以大幅提高查询速度。使用索引需要注意以下几个问题：

• 尽可能的使用索引覆盖查询，避免使用 SELECT * 这种全字段查询语句，以提高查询性能；
• 对于多列的索引，需要注意将查询条件中需要使用的列放在索引的前端，这样才能发挥多列索引的作用；
• 避免对索引字段进行函数操作，例如：select * from user where year(age)=2017; 应该改写为 year=2017，否则索引将失去作用；
• 对于LIKE查询，如果匹配规则类似于 abc% 或者 %abc 的前缀查询，可以使用索引进行优化，如下：

select * from user where name like 'abc%';   --可以使用索引
select * from user where name like '%abc';   --不能使用索引
select * from user where name like '%abc%';  --不能使用索引

事务

事务的概念

事务是指一组操作在执行的过程中，能保证数据的完整性、一致性并且是可靠的，对于MySQL的事务，可以通过以下语句来开启一个事务：

start transaction;

执行完需要事务控制的操作后，如果需要将增删改操作提交，则可以使用以下语句来提交事务：

commit;

如果需要回滚事务到操作之前的状态，则可以使用以下语句进行回滚：

rollback;

事务的ACID特性

• ATOMICITY：(原子性)事务必须是原子工作单位，整个事务中的所有操作必须全部完成，要么全部不完成。如果事务在执行的过程中发生任何错误，则会回滚到操作之前的状态。
• CONSISTENCY：(一致性)事务执行的过程中，必须保证数据的完整性和一致性。无论任何情况，事务的执行都必须遵循数据库的规则和约束。
• ISOLATION：(隔离性)事务的执行状态相互独立，不会受到其他执行事务的影响，保证数据的准确性和完整性。
• DURABILITY：(持久性)经过事务提交完成的数据，必须长久保存，不会因为系统故障而丢失。

示例

假设有一个用户表user，其中包括name和age两个字段，现在需要将其中年龄大于30岁的用户名称全部修改为"老年人"。

start transaction;
update user set name='老年人' where age>30;
commit;

以上代码使用了事务控制，可以确保该操作全部执行成功后才提交，以保证数据的一致性和完整性。

Reference

• MySQL 中文手册

• MySQL公开课-阿里云官网