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。