MySQL索引、事务

一：MySQL 索引介绍

1.1：索引概述

索引是一种特殊的数据结构，它存储了表中某些列的值以及指向这些值所在的行的指针。索引的主要目的是加快数据检索的速度，类似于图书中的目录页，可以帮助快速定位到所需信息。

1.2：索引作用

• 提高查询速度：索引可以显著减少数据库查找数据所需的 I/O 操作次数。
• 提供数据唯一性：某些索引类型（例如唯一索引和主键索引）可以确保数据的唯一性。
• 辅助排序和分组：索引可以帮助优化排序和分组操作。

1.3：索引的分类

        1：普通索引

                （1）直接创建索引语法

CREATE INDEX index_name ON table_name (column_name);

                （2）修改表结构的方式添加索引语法

ALTER TABLE table_name ADD INDEX index_name (column_name);

                （3）创建表结构时，同时创建索引

CREATE TABLE table_name (
  column_name datatype,
  INDEX index_name (column_name)
);

        2：唯一索引

                （1）创建唯一索引语法

CREATE UNIQUE INDEX unique_index_name ON table_name (column_name);

                （2）修改表结构的时候添加唯一索引语法

ALTER TABLE table_name ADD UNIQUE INDEX unique_index_name (column_name);

                （3）创建表的时候同时创建唯一索引

CREATE TABLE table_name (
  column_name datatype,
  UNIQUE INDEX unique_index_name (column_name)
);

        3：主键索引

                （1）创建主键索引语法

1ALTER TABLE table_name ADD PRIMARY KEY (column_name);

                （2）创建表时指定主键索引

CREATE TABLE table_name (
  column_name datatype,
  PRIMARY KEY (column_name)
);

        4：组合索引

CREATE INDEX combo_index_name ON table_name (column1, column2, column3);

       说明：组合索引是在多个列上创建的索引，可以用于优化涉及多个列的查询。

        5：全文索引

                （1）创建表的全文索引语法

CREATE TABLE table_name (
  column_name datatype,
  FULLTEXT INDEX fulltext_index_name (column_name)
) ENGINE = MyISAM;

                注意：默认情况下，全文索引仅在 MyISAM 表中可用。InnoDB 引擎从 MySQL 5.6 版本开始支持全文索引。

                （2）修改表结构添加全文索引语法

ALTER TABLE table_name ADD FULLTEXT fulltext_index_name (column_name);

                （3）直接创建索引语法

CREATE FULLTEXT INDEX fulltext_index_name ON table_name (column_name);

1.4：创建索引的原则依据

• 选择性：索引应该基于查询中最常使用的列创建。
• 查询频率：如果某个查询经常执行，则考虑为该查询创建索引。
• 更新频率：如果一个列经常被修改，则不应该为其创建索引。
• 索引大小：过大的索引会影响插入、更新和删除操作的性能。
• 覆盖索引：尽可能创建包含所有查询所需字段的索引，这样可以避免额外的表访问。
• 多列索引：当一个查询涉及多个列时，创建一个包含这些列的组合索引可能比为每个列单独创建索引更有效。

1.5：查看索引

                （1）查看表的所有索引:

SHOW INDEXES FROM table_name;

                （2）查看表的索引信息:

SHOW INDEX FROM table_name;

输出列说明：Key_name 显示索引名称；Non_unique 表示索引是否允许重复值；Seq_in_index 显示索引列的顺序；Column_name 显示索引中的列名；Cardinality 显示索引的基数（即不同值的数量）等。

1.6：删除索引

                （1）删除一个索引:

DROP INDEX index_name ON table_name;

                （2）通过 ALTER TABLE 删除索引:

ALTER TABLE table_name DROP INDEX index_name;

二：MySQL 事务

事务的概念

事务是一系列不可分割的操作序列，这些操作作为一个整体被提交或回滚。事务必须满足 ACID 属性：

• 原子性：事务中的所有操作要么全部完成，要么全部不完成。
• 一致性：事务完成后，系统必须处于一致的状态。
• 隔离性：并发执行的事务之间不能互相干扰。
• 持久性：一旦事务被提交，其结果就会永久保存下来。

事务的基本操作

• 开始事务:

  START TRANSACTION;

• 提交事务:

  COMMIT;

• 回滚事务:

  ROLLBACK;

事务的隔离级别

MySQL 支持四种不同的隔离级别：

• READ UNCOMMITTED：最低的隔离级别，允许读取未提交的数据。
• READ COMMITTED：只允许读取已经提交的数据。
• REPEATABLE READ：这是 MySQL 默认的隔离级别，重复读取相同的行将返回相同的结果集。
• SERIALIZABLE：最高的隔离级别，完全串行化读取，确保数据的一致性。

要设置事务的隔离级别，可以使用以下命令：

SET SESSION TRANSACTION ISOLATION LEVEL isolation_level;

其中 isolation_level 可以是上述四种之一。

事务示例

下面是一个简单的事务示例：

START TRANSACTION;
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
INSERT INTO accounts (account_id, balance) VALUES (2, 1000);

-- 转账操作
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

在这个例子中，我们首先开启了一个事务，然后执行了一系列的操作，最后提交了事务。如果在这期间出现了任何错误，我们可以使用 ROLLBACK 来撤销所有的更改。