MYSQL数据库 索引、事务
索引
定义：
索引是对数据库表中一列或多列的值进行排序的一种结构（类似书的目录）。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
索引的优点：
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点：
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
- 如果给不合适创建索引的列创建了索引，不会提高性能。
哪些列应该创建索引？
- 列的选择性：对于选择性高的列创建索引，可以大大提高查询效率。
- 查询效率：对于经常被用于查询的列，特别是大表的查询，应该创建索引。
- 数据量大小：对于数据量大的表，可以考虑创建索引。
- 经常被用于连接的列：对于经常与其他表进行连接的列，可以考虑创建索引。
- 经常出现在WHERE子句中的列：对于经常出现在WHERE子句中的列，特别是大表的列，应该创建索引。
- 对于主键或外键，必须创建索引。
- 对于经常被用于排序、分组、去重等操作的列，可以考虑创建索引。
- 对于经常被用于子查询、联合查询等操作的列，可以考虑创建索引。
哪些列不应该创建索引？
- 列的选择性：对于选择性低的列，创建索引可能不会提高查询效率。
- 数据量大小：对于数据量小的表，可以考虑不创建索引。
- 修改频率：对于经常被修改的列，可以考虑不创建索引，因为索引的维护成本较高。
- 查询效率：对于查询效率已经很高的列，可以考虑不创建索引。
- 对于定义为text、image、bit等数据类型的列，应该避免创建索引。
- 对于经常被用于查询，但是数据量很少的列，可以考虑不创建索引。
索引的分类：
| 索引类型 | 描述 |
|---|---|
| 普通索引 | 默认索引，无限制，可用于提高查询效率 |
| 唯一索引 | 限制索引的值必须唯一，但允许有空值 |
| 主键索引 | 一种特殊的唯一索引，用于标识数据库记录的唯一性，不允许重复且不允许为空 |
| 组合索引 |
一个组合索引包含两个或两个以上的列，组合索引“最左原则” |
普通索引
语法：
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。