MYSQL数据库进阶 索引、事务

索引

定义：

索引是对数据库表中一列或多列的值进行排序的一种结构（类似书的目录）。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

索引的优点：

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
4. 如果给不合适创建索引的列创建了索引，不会提高性能。

哪些列应该创建索引？

1. 列的选择性：对于选择性高的列创建索引，可以大大提高查询效率。
2. 查询效率：对于经常被用于查询的列，特别是大表的查询，应该创建索引。
3. 数据量大小：对于数据量大的表，可以考虑创建索引。
4. 经常被用于连接的列：对于经常与其他表进行连接的列，可以考虑创建索引。
5. 经常出现在WHERE子句中的列：对于经常出现在WHERE子句中的列，特别是大表的列，应该创建索引。
6. 对于主键或外键，必须创建索引。
7. 对于经常被用于排序、分组、去重等操作的列，可以考虑创建索引。
8. 对于经常被用于子查询、联合查询等操作的列，可以考虑创建索引。

哪些列不应该创建索引？

1. 列的选择性：对于选择性低的列，创建索引可能不会提高查询效率。
2. 数据量大小：对于数据量小的表，可以考虑不创建索引。
3. 修改频率：对于经常被修改的列，可以考虑不创建索引，因为索引的维护成本较高。
4. 查询效率：对于查询效率已经很高的列，可以考虑不创建索引。
5. 对于定义为text、image、bit等数据类型的列，应该避免创建索引。
6. 对于经常被用于查询，但是数据量很少的列，可以考虑不创建索引。

索引的分类：

索引类型	描述
普通索引	默认索引，无限制，可用于提高查询效率
唯一索引	限制索引的值必须唯一，但允许有空值
主键索引	一种特殊的唯一索引，用于标识数据库记录的唯一性，不允许重复且不允许为空
组合索引	一个组合索引包含两个或两个以上的列，组合索引“最左原则”

普通索引

语法：

CREATE INDEX index_name
ON table_name (column1, column2, ...)

其中：

• index_name 是索引的名称，可以自定义，但最好具有含义性。
• table_name 是要创建索引的表名。
• (column1, column2, ...) 是要包含在索引中的列名，可以指定多个列。

普通索引是一种常见的索引类型，允许在数据表中的任意列中出现重复值，并且可以加快对数据的访问速度。

例如，假设有一个名为"users"的表，其中有一个名为"email"的列，我们想要提高根据email进行查询的速度，那么可以创建普通索引。

创建普通索引的SQL代码如下：

CREATE INDEX idx_users_email ON users (email);

上述代码将在"users"表的"email"列上创建一个名为"idx_users_email"的普通索引。

通过创建普通索引，数据库系统可以更快地查找到特定email的用户信息，从而提高查询效率。

唯一索引

语法：

CREATE UNIQUE INDEX indexName
ON tableName (columnName(length));

注意：

• 如果表中的某列不允许出现重复值，就应该对其创建唯一索引。
• 可以将唯一索引作用域一个或多个列上，这些列或列的组合必须唯一。
• 同一张表允许创建多个唯一索引。
• 唯一索引的列允许空值。
• 使用 INSERT 和 UPDATE 向唯一索引列生成重复值会报错。

举例：

在 "student" 表的 "phone" 列上创建唯一索引：

CREATE UNIQUE INDEX idx_student_phone
ON student (phone);

主键索引

语法：

ALTER TABLE table_name
ADD PRIMARY KEY (column);

其中，table_name 是要创建主键索引的表名，column 是要指定为主键索引的列名。

需要注意的是，每个表只能有一个主键索引，且主键索引的值必须唯一，不能为 NULL。如果表中的某列已经指定为 PRIMARY KEY，那么可以省略 PRIMARY KEY 关键字。

举例：

假设有一个名为 "users" 的表，其中有一个名为 "id" 的列，我们想将其设置为该表的主键索引，可以使用以下SQL语句：

ALTER TABLE users
ADD PRIMARY KEY (id);

上述代码将在 "users" 表的 "id" 列上创建一个名为 "PRIMARY" 的主键索引。

组合索引

语法：

CREATE INDEX indexName
ON tableName (column1, column2, ...);

在上述语法中，indexName 是索引的名称，tableName 是要创建索引的表名，column1、column2 等是要包含在索引中的列名。

组合索引是多列组合建立的索引，可以涵盖多个列，列的顺序按照查询频率进行排序。组合索引可以提高查询效率，特别是在查询语句中同时涉及到这些列时。

需要注意的是，虽然组合索引可以提高查询效率，但是它也有一些负面影响。例如，组合索引的维护成本比单列索引要高，并且可能会对插入和更新操作产生一定的影响。因此，在创建组合索引时需要权衡利弊。

举例：

假设有一个名为 "orders" 的表，其中包含 "order_date" 和 "customer_id" 两个列，我们想创建一个组合索引，按照日期排序，然后按照客户ID排序。可以使用以下SQL语句：

CREATE INDEX idx_orders_date_customer
ON orders (order_date, customer_id);

上述代码将在 "orders" 表上创建一个名为 "idx_orders_date_customer" 的组合索引，包含 "order_date" 和 "customer_id" 两个列。

这个组合索引可以用于以下查询：

• 查询某个日期的订单：

  SELECT * FROM orders WHERE order_date = '2023-07-01';

• 查询某个客户的所有订单：

  SELECT * FROM orders WHERE customer_id = 1234;

事务

定义：

一种对数据库进行一系列读取和写入操作的过程。这些操作必须全部成功才能提交，否则将会回滚到事务的起点，以确保数据的一致性和完整性。

一个事务就是一个完整的业务逻辑，这个业务是最小的逻辑单元，不可再分，要么同时成功，要么同时失败。事务主要用于处理操作量大，复杂度高的数据。

主要特点：

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性通常简称为ACID特性。

1. 原子性：事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部不执行。（最小的工作单元，不可分割）
2. 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的任何数据都将保存在数据库中，如果在事务过程中发生故障，则会有回滚操作来恢复数据库的完整性。（同时成功或同时失败）
3. 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务。隔离性也被称为独立性。（A事务和B事务之间有隔离）
4. 持久性：一旦事务提交，则其结果永久保存在数据库中。即使系统崩溃，重新启动后数据库还能恢复到提交事务后的状态，以确保数据的永久保存。（事务提交后，事务执行所产生的数据将会被持久化到数据库表中）

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。事务用来管理insert、update、delete语句。

语法：

1. 开始事务：使用BEGIN或START TRANSACTION语句来开始一个新的事务。
2. 提交事务：使用COMMIT语句来提交当前的事务，将所有的修改保存到数据库中。
3. 回滚事务：使用ROLLBACK语句来回滚当前的事务，撤销所有的修改并返回到事务的起点。

-- 开始事务  
START TRANSACTION;  
  
-- 执行一些数据库操作  
INSERT INTO table1 VALUES (1, 'A');  
INSERT INTO table2 VALUES (2, 'B');  
UPDATE table3 SET column = 'C' WHERE condition;  
  
-- 提交事务  
COMMIT;  
  
-- 回滚事务  
ROLLBACK;