MySql索引、锁、事务知识点小结

MySql作为一种快速、安全、可靠的数据库，在开发中广泛使用。了解MYSQL索引、锁、事务知识点，可以帮助我们更好的管理、优化和提高Mysql的性能。

索引

索引是数据库中数据的快速查找结构。一个主键只能有一个索引，如果你经常使用where子句，order by子句，join子句进行查询，建立索引可以大大缩短查询的时间。

MySQL支持多种索引类型，如B-tree索引、哈希索引、全文索引等。

B-tree索引

B-tree索引最常使用的索引类型，用于对值进行分类和排序。在B-tree索引中，所有的节点数据都是排好序的，且中间节点保存了指向下一级节点的指针。B-tree索引可以加快查询速度，且适用于范围查询（如BETWEEN、>等）。

例如，为表users建立一个名为index_userId的B-tree索引：

CREATE INDEX index_userId ON users(userId);

哈希索引

哈希索引利用哈希算法将索引值映射到一个哈希表中。哈希索引不能进行范围查询，只能进行等于查询和IN查询，适用于使用哈希算法分布数据的场景。

例如，为表users建立一个名为hash_userId的哈希索引：

CREATE INDEX hash_userId ON users(userId) USING HASH;

锁

锁是用于限制多个客户之间访问相同信息的并发控制方法。MySQL中常用的锁类型有共享锁和排他锁。

共享锁

共享锁是指多个客户可以同时读取相同的资料，但是他们之间是阻塞的，不能同时进行写操作。在共享锁的情况下，其他客户可以获取共享锁和读取相同的数据，但是无法进行写操作。共享锁使用LOCK SHARED语句进行获取。

LOCK TABLE users SHARED;

排他锁

排他锁是指当一个客户正在修改资料时，其他的客户不能读取或修改相同资料。排他锁称为写锁，可以使用LOCK WRITE语句进行获取。

LOCK TABLE users WRITE;

事务

事务是指作为一个单独单元执行的一系列相关的操作，事务的ACID原则包括原子性、一致性、隔离性和持久性。

事务的四种隔离级别

• 未提交读（read uncommitted）：事务不会加锁，也可以读取未提交的事务，可能出现脏读、不可重复读和幻读的问题。
• 提交读（read committed）：事务会在查询结束后立即释放锁，不能读取未提交的事务，可以避免脏读的问题，但可能会引起不可重复读和幻读。
• 可重复读（repeatable read）：保证在同一事务中所有读取的数据结果都是一致的，但可能出现幻读。
• 可串行化（serializable）：最高的隔离级别，会对读取和写入的数据加强锁机制，可以避免脏读、不可重复读和幻读，但会降低并发性能。

MySQL默认的隔离级别为可重复读（repeatable read）。可以使用SET TRANSACTION语句来修改隔离级别。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
//操作
COMMIT;

示例

索引示例

例如，我们创建一个表，随机生成1000万不重复的整数值，并建立索引：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询number=1000，未建立索引时查询时间大约为0.22秒，建立索引后查询时间只需要0.0005秒，效果显著。

SELECT * FROM `user` WHERE `number` = 1000; -- 未建立索引
SELECT * FROM `user` USE INDEX(`uniq_number`) WHERE `number` = 1000; -- 建立索引

事务示例

例如，我们有一个银行数据库，两个客户同时修改余额：

SELECT * FROM `account` WHERE `id` = 1; -- 客户A查询自己的余额
UPDATE `account` SET `balance` = `balance` - 100 WHERE `id` = 1; -- 客户A进行100元转账
SELECT * FROM `account` WHERE `id` = 2; -- 客户B查询自己的余额
UPDATE `account` SET `balance` = `balance` + 100 WHERE `id` = 2; -- 客户B进行100元接收转账

如果客户A和客户B同时执行转账操作，可能会导致余额错误，但通过使用事务，可以避免并发问题：

START TRANSACTION;
SELECT * FROM `account` WHERE `id` = 1 FOR UPDATE; -- 客户A查询自己的余额并上锁
UPDATE `account` SET `balance` = `balance` - 100 WHERE `id` = 1; -- 客户A进行100元转账
SELECT * FROM `account` WHERE `id` = 2 FOR UPDATE; -- 客户B查询自己的余额并上锁
UPDATE `account` SET `balance` = `balance` + 100 WHERE `id` = 2; -- 客户B进行100元接收转账
COMMIT;

以上是MySQL索引、锁、事务知识点的小结和示例，可以帮助我们更好地学习和使用MySQL。