MySQL索引、事务与存储引擎

索引介绍

MySQL索引是数据库优化性能的关键部分，它们允许数据库系统更快地检索数据。以下是关于MySQL索引的一些基本介绍：

1. 索引是什么？

• 索引是一种数据结构，它可以帮助数据库系统更快地访问数据表中的特定数据。
• 没有索引，数据库必须从头开始扫描整个表以找到相关的行，这被称为全表扫描，通常是非常耗时的。
• 有了索引，数据库可以直接定位到数据的存储位置，而无需扫描整个表。

2. 索引的类型

MySQL支持多种类型的索引：

• B-Tree索引：最常见的索引类型，InnoDB和MyISAM存储引擎都使用它。
• 哈希索引：基于哈希表的索引，只支持等值查询，不支持范围查询。MEMORY存储引擎支持它。
• 空间索引 (R-Tree)：用于地理空间数据类型。
• 全文索引：用于全文搜索。
• 聚簇索引：InnoDB特有的索引类型，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。

3. 索引的创建和删除

• 使用CREATE INDEX语句创建索引。
• 使用ALTER TABLE语句添加或删除索引。
• 使用DROP INDEX语句删除索引。

4. 索引的优缺点

优点：

• 提高查询速度：通过索引，数据库可以快速定位到需要的数据。
• 加速表与表之间的连接：在执行连接操作时，如果连接字段被索引，则连接速度会加快。
• 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

• 占用磁盘空间：索引本身需要存储空间。
• 降低写入速度：当向表中插入、更新或删除记录时，索引也需要被相应地维护，这可能会降低写入速度。
• 维护成本：索引需要定期维护，例如重建或优化，以确保其性能。

5. 索引的设计原则

• 选择最经常用于搜索、排序和连接的列作为索引。
• 不要过度索引：每个额外的索引都会占用额外的磁盘空间，并可能降低写入速度。
• 考虑使用复合索引：如果一个查询经常需要基于多个列进行搜索，那么可以考虑创建一个复合索引来覆盖这些列。
• 避免在经常更新的列上创建索引：这可能会降低写入性能。
• 定期分析和优化索引：使用MySQL的内置工具（如ANALYZE TABLE和OPTIMIZE TABLE）来保持索引的最佳状态。

6. 使用EXPLAIN语句查看查询执行计划

EXPLAIN语句可以帮助你了解MySQL如何执行一个查询，包括是否使用了索引、使用了哪些索引以及查询的性能如何。这对于优化查询和索引设计非常有用。

索引的分类

MySQL 中的索引有多种分类方式，以下是基于其不同特性和用途的一些主要分类：

1. 按存储结构分类

• B-Tree 索引：最常见的索引类型，InnoDB 和 MyISAM 存储引擎都支持。其特点是查询效率高，且能够很好地支持范围查询。
• Hash 索引：基于哈希表的索引，只支持等值查询，不支持范围查询。MEMORY 存储引擎支持它。
• R-Tree 索引（空间索引）：用于地理空间数据类型，如点、线和多边形。
• FULLTEXT 索引（全文索引）：用于全文搜索，如 MyISAM 和 InnoDB 存储引擎（从 MySQL 5.6.4 开始）。

2. 按数据列分类

• 单列索引：基于单个数据列的索引。
• 复合索引（联合索引、多列索引）：基于两个或更多个数据列的索引。例如，在 CREATE INDEX idx_name_age ON users(name, age); 中，name 和 age 列共同构成了一个复合索引。

3. 按功能和用途分类

• 普通索引：最基本的索引，没有任何限制。
• 唯一索引：与普通索引类似，但不同的记录值必须具有唯一的索引列值。
• 主键索引：它是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。在 InnoDB 存储引擎中，表数据文件本身就是按 B+Tree 组织的一个主键索引结构，这被称为聚簇索引（Clustered Index）。
• 外键索引：用于保证数据引用完整性的索引，它指向另一个表的主键。

4. 按物理存储方式分类

• 聚簇索引（Clustered Index）：数据行的物理顺序与列值（通常是主键）的逻辑（索引）顺序相同。在 InnoDB 中，表数据文件本身就是按主键聚簇索引组织的。
• 非聚簇索引（Non-Clustered Index）：数据行的物理顺序与索引顺序无关。MyISAM 存储引擎使用的是非聚簇索引。

5. 其他

• 前缀索引：基于字符串列的前几个字符创建的索引。这可以节省空间并提高查询效率，但可能会降低索引的选择性。
• 覆盖索引（Covering Index）：如果一个索引包含了查询所需要的所有字段的值，则称为覆盖索引。这可以大大提高查询效率，因为不需要回表查询数据。

在设计数据库和索引时，需要根据实际的应用场景和需求来选择合适的索引类型，并进行合理的优化和调整。

创建索引的原则依据

MySQL创建索引的原则依据主要包括以下几点：

1. 查询频率：
   • 对查询频率高的字段创建索引，因为索引的主要目的是提升查询速度。例如，在用户表中，手机号这种经常需要查询的字段就应该创建索引。
2. 排序、分组、联合查询：
   • 对经常需要排序、分组或进行联合查询的字段创建索引。这样可以显著提高这些操作的性能。
3. 索引数量：
   • 索引的数目不宜过多，因为每个索引都会占用物理空间，过多的索引会导致INSERT、UPDATE、DELETE语句的执行效率降低。需要根据实际情况权衡索引带来的查询性能提升和写操作的性能损失。
4. 多列索引：
   • 当需要将多个列设置索引时，可以考虑使用多列索引（复合索引）。但是需要注意，多列索引的创建和使用需要遵循BTree类型，即只有使用到索引的最左侧列时，索引才会被启用。
5. 索引选择性：
   • 索引应该建在选择性高的字段上，即字段中的唯一值占比较高。这样的索引能够提供更多的信息，从而提高查询性能。
6. 字段大小：
   • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。因为索引本身也会占用存储空间，大字段的索引会导致索引文件过大，影响性能。
7. 更新频率：
   • 更新太频繁的字段不适合创建索引，因为每次数据更新都需要更新索引，这会降低写操作的性能。
8. 主键和外键：
   • 表的主键和外键默认就有索引，因为它们需要快速定位数据。
9. 表的大小：
   • 数据量大的表应该考虑创建索引，以提高查询性能。但是，对于小表（如记录数少于300行的表），全表扫描可能更快，因此不需要创建索引。
10. 连接字段：
    • 经常与其他表进行连接的表，在连接字段上应该建立索引。这可以加快表与表之间的连接速度。
11. 避免冗余索引：
    • 删除无用的索引，避免对执行计划造成负面影响。同时，要注意不要创建冗余的索引，即多个索引覆盖了相同的字段组合。

在创建索引时，需要综合考虑以上因素，并根据实际的应用场景和需求来确定是否创建索引以及创建何种类型的索引。同时，也需要定期分析和优化索引，以确保其处于最佳状态。

查看索引

在MySQL中，你可以使用多种方法来查看表上的索引。以下是一些常用的方法：

1. 使用SHOW INDEX命令

SHOW INDEX命令可以显示指定表上的所有索引信息。

例如，如果你有一个名为users的表，并想查看其上的索引，你可以执行：

这将返回一个结果集，其中包含关于索引的详细信息，如索引名、索引类型、列名、唯一性（是否唯一索引或主键索引）等。

2. 使用INFORMATION_SCHEMA.STATISTICS表

INFORMATION_SCHEMA.STATISTICS表包含了关于数据库中所有表的索引的元数据。你可以查询这个表来获取索引信息。

在这个查询中，你需要将'你的数据库名'和'你的表名'替换为你实际的数据库名和表名。这个查询将返回与SHOW INDEX命令相似的结果集。

3. 使用DESC或DESCRIBE命令（有限信息）

虽然DESC或DESCRIBE命令主要用于显示表的结构，但它也可以显示主键和唯一索引的信息（如果有的话）。这些索引会在Key列中显示为PRI（主键）或UNI（唯一）。

或者

但是，请注意，这种方法不会显示非唯一索引的信息。

4. 使用第三方工具

你还可以使用各种MySQL管理工具（如phpMyAdmin、MySQL Workbench、Adminer等）来查看和管理索引。这些工具通常提供了一个图形用户界面，使你可以更直观地查看和管理数据库、表和索引。

mysql事务

MySQL中的事务（Transaction）是一系列数据库操作的逻辑单元，这些操作要么全部成功执行，要么全部不执行。事务的主要目的是确保数据的完整性和一致性，即使在并发操作或系统崩溃的情况下也能保持数据的准确性。

事务的特性（ACID）

1. 原子性（Atomicity）：事务被视为一个不可分割的最小工作单位，其中的操作要么全部完成，要么全部不完成。
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。
3. 隔离性（Isolation）：在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的隔离空间，一个事务的内部操作对其他事务都是不可见的。
4. 持久性（Durability）：一旦事务提交，则其结果就是永久性的，即使发生系统崩溃，数据库也能通过日志等手段恢复数据。

事务控制语句

在MySQL中，你可以使用以下语句来控制事务：

• START TRANSACTION 或 BEGIN：开始一个新的事务。
• COMMIT：提交当前事务，使其所做的更改永久化。
• ROLLBACK：撤销当前事务中的更改，将其回滚到事务开始前的状态。
• SAVEPOINT：在事务中设置一个保存点，允许你回滚到该保存点，而不是事务的起点。
• ROLLBACK TO SAVEPOINT：回滚到指定保存点，不撤销保存点之后的事务更改。
• RELEASE SAVEPOINT：删除一个保存点。
• SET TRANSACTION：用于改变事务的隔离级别。

隔离级别

MySQL支持四种事务隔离级别，这些级别定义了事务如何与其他并发事务进行交互：

1. READ UNCOMMITTED（未提交读）：最低的隔离级别，一个事务可以读取另一个未提交事务的数据。
2. READ COMMITTED（提交读）：一个事务只能读取已经提交事务所做的更改。这是大多数数据库系统的默认隔离级别（但不是MySQL的）。
3. REPEATABLE READ（可重复读）：这是MySQL的默认事务隔离级别。对同一字段的多次读取结果都是一致的。但是，对于不同的字段或行，读取结果可能会不一致。
4. SERIALIZABLE（可串行化）：最高的隔离级别，所有的事务依次逐个执行，这样事务之间就不可能产生干扰。

你可以使用以下SQL语句来设置事务的隔离级别：

其中，[隔离级别] 可以是上述四种隔离级别中的任意一种。