MySQL数据库索引、事务和存储引擎管理

一、索引管理

1.索引的概念

2.索引的作用

3.索引的副作用

4.创建索引的原则依据

5.索引的分类和创建

5.1普通索引

5.2唯一索引

5.3主键索引

5.4组合索引

5.5全文索引

6.查看和删除索引

6.1查看索引

6.2删除索引

二、事务管理

1.事务的概念

2.ACID特点

2.1原子性（Atomicity）

2.2一致性（Consistency）

2.3隔离性（Isolation）

2.4持久性（Durability）

3.事务隔离级别

3.1读未提交（Read Uncommitted）

3.2读已提交（Read Committed）

3.3可重复读（Repeatable Read）

3.4串行化（Serializable）

3.5默认事务隔离级别

3.6事务隔离级别的作用范围

3.7查询和设置事务隔离级别

3.7.1查询全局事务隔离级别

3.7.2查询会话事务隔离级别

3.7.3设置全局事务隔离级别

3.7.4设置会话事务隔离级别

4.事务控制语句

4.1BEGIN或START TRANSACTION

4.2COMMIT或COMMIT WORK

4.3ROLLBACK或ROLLBACK WORK

4.4SAVEPOINT

4.5ROLLBACK TO SAVEPOINT

4.6RELEASE SAVEPOINT

4.7SET AUTOCOMMIT

4.8测试

4.8.1测试提交事务

4.8.2测试回滚事务

4.8.3测试多点回滚

4.8.4设置控制事务

三、MySQL存储引擎

1.存储引擎概念

2.存储引擎的作用

3.常见的存储引擎

3.1.MylSAM引擎

3.1.1特点

3.1.2支持的存储格式

3.1.3优缺点

3.1.4应用场景

3.2.InnoDB引擎

3.2.1特点

3.2.2性能

3.2.3优缺点

3.2.4应用场景

4.常用存储引擎操作

4.1查看支持的存储引擎

4.2查看表使用的存储引擎

4.3修改表的存储引擎

4.4创建表时指定存储引擎

4.5设置默认存储引擎

4.6优化表

一、索引管理

1.索引的概念

索引是用于提高数据库查询效率的技术。它相当于一本书的目录，通过存储列值及其对应的物理地址，帮助快速定位数据

结构：

索引是一个排序的列表，其中存储了索引值和包含这些值的行所在的物理地址。通过索引，MySQL不需要全表扫描，而是可以直接通过索引查找到对应的数据
加速查询：

索引的主要作用是加速查询，通过先查询索引来快速定位数据行的位置，大大减少了数据的检索时间

物理地址：

类似于C语言的指针，索引保存的是数据记录在物理存储中的位置

排序功能：

索引不仅可以加速查找，还可以提高数据库的排序能力。由于索引是有序的，排序操作也会变得更为高效

2.索引的作用

索引在数据库和信息检索中起着至关重要的作用，主要包括以下几个方面：

1. 提高查询速度：

索引可以加速数据的检索过程，减少查询时需要扫描的数据量，从而提高性能

2. 优化排序和筛选：

通过索引，可以快速获取有序的数据，便于排序和条件筛选

3. 减少I/O操作：

索引通常会显著降低磁盘I/O操作的次数，特别是在处理大规模数据时

4. 支持唯一性约束：

某些类型的索引（如唯一索引）可以确保数据的唯一性，避免重复数据的插入

5. 提高数据完整性：

通过索引，可以方便地实现外键约束，维护数据之间的关系

总之，索引是提升数据库性能和数据管理的重要工具

3.索引的副作用

虽然索引在提高数据库性能方面非常有用，但也存在一些副作用，主要包括：

1. 占用存储空间：

索引需要额外的存储空间，尤其是对于大型表，可能会显著增加数据库的总体大小

2. 影响写入性能：

每当对表进行插入、更新或删除操作时，相关索引也需要进行更新，这可能会导致写入性能下降

3. 维护成本：

随着数据的增加或变更，索引可能需要定期重建或优化，增加了数据库的维护复杂性和成本

4. 选择不当的索引：

不合理的索引选择可能会导致查询性能下降，例如过多的索引可能会使查询优化器做出错误决策

5. 降低查询的灵活性：

在某些情况下，强制使用索引可能会导致不够灵活的查询计划，从而影响性能

6. 锁争用：

在高并发环境下，索引的维护可能引发锁争用，影响系统的并发性能

总之，虽然索引在许多情况下是必要的，但在使用时需要权衡其利弊，以确保整体性能的优化

4.创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担

适合创建索引的情况：
1. 主键和外键：

主键自带唯一性，外键用于关联表，索引可以加速这些操作
2. 表记录较多：

一般来说，当表记录超过300行时，索引的效果会更明显
3. 频繁连接的字段：

在连接查询中使用的字段应创建索引，以提高连接性能
4. WHERE子句中的字段：

经常出现在WHERE子句中的字段，特别是在大表上，应该建立索引
5. 选择性高的字段：

选择性高的字段能显著提高查询效率
6. 小字段：

小字段的索引创建和维护成本较低，且能提高查询速度
7. 不经常更新的字段：

更新频繁的字段不适合建立索引，因为维护索引会带来额外的性能负担

不适合创建索引的情况：
1. 唯一性差的字段：

如果字段的唯一性差，索引的效果可能不明显，反而增加开销
2. 更新频繁的字段：

这些字段的索引维护成本较高，会影响写入性能
3. 大文本字段：

对于大型文本（如BLOB、CLOB），索引的效果有限，并且开销较大
4. 低使用频率的字段：

查询率低的字段，即使建立索引，也无法带来性能提升

5.索引的分类和创建

5.1普通索引

最基本的索引类型，没有唯一性之类的限制

特点：

非唯一性：允许索引列中有重复的值
查询加速：加速对表中数据的检索
不影响数据完整性：与唯一索引不同，普通索引不强制列值的唯一性

直接创建索引：

CREATE INDEX index_name ON table_name (column_name[(length)]);
length：是可选项，用于限制索引使用列前的字符长度示例
CREATE INDEX phone_index ON ky37 (phone);

修改表方式创建

ALTER TABLE table_name ADD INDEX index_name (column_name);示例
ALTER TABLE ky37 ADD INDEX id_index (id);

创建表时指定索引

CREATE TABLE table_name (field1 data_type,field2 data_type,INDEX index_name (column_name));示例
CREATE TABLE test (id INT(4) NOT NULL,name VARCHAR(10) NOT NULL,cardid VARCHAR(18) NOT NULL,INDEX id_index (id));

5.2唯一索引

唯一索引类似于普通索引，但每个值必须唯一。允许空值

特点

唯一性：确保索引列中的每个值唯一，不能有重复值
支持空值：允许列中有空值(NULL)
加速查询：提升对数据的检索速度
自动创建：添加唯一约束时，MySQL会自动创建唯一索引

直接创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);示例
CREATE UNIQUE INDEX address_index ON ky37 (address);

修改表方式创建

ALTER TABLE table_name ADD UNIQUE index_name (column_name);示例
ALTER TABLE ky37 ADD UNIQUE cardid_index (cardid);

创建表时指定唯一索引

CREATE TABLE table_name (field1 data_type,UNIQUE index_name (column_name));示例
CREATE TABLE kgc (id INT,name VARCHAR(20),UNIQUE id_index (id));

5.3主键索引

主键索引是一种特殊的唯一索引，必须指定为“PRIMARY KEY”，一个表只能有一个主键，不允许有空值

特点

唯一性：每个主键值必须唯一，不能重复
非空性：主键列不能包含空值(NULL)
加速检索：主键索引可以显著提高数据检索的速度
自动创建：定义主键时，MySQL会自动为该列创建主键索引

创建表时指定主键

CREATE TABLE table_name (...,PRIMARY KEY (column_name));示例
CREATE TABLE test1 (id INT PRIMARY KEY,name VARCHAR(20));

修改表方式创建主键

ALTER TABLE table_name ADD PRIMARY KEY (column_name);示例
ALTER TABLE users ADD PRIMARY KEY (id);

5.4组合索引

组合索引可以是单列或多列上创建的索引，需遵循最左前缀原则

特点

提高查询性能：能加速涉及多个列的查询
最左前缀原则：查询条件的列顺序必须与索引的顺序一致，以便有效使用索引
支持范围查询：支持等值和范围查询，但如果使用范围条件，后面的列将无法被索引

创建表时指定组合索引

CREATE TABLE table_name (column1 data_type,column2 data_type,INDEX index_name (column1, column2));示例
CREATE TABLE ky37 (id INT NOT NULL,name VARCHAR(20),cardid VARCHAR(20),INDEX index_amd (id, name));

查询示例

SELECT * FROM ky37 WHERE id = '...' AND name = '...';

5.5全文索引

全文索引是用于在文本字段中进行高效的模糊查询的一种特殊索引类型。它可以在较大文本数据中快速检索出包含特定关键词的记录
特点
适合长文本：适用于CHAR、VARCHAR或TEXT类型的字段，适合于存储较长的文本数据
高效的搜索：支持自然语言查询，可以检索文本中包含特定单词或短语的记录
支持布尔模式：可以通过布尔运算符(如 AND、OR、NOT)进行更复杂的查询
限制：在 MySQL 5.6 版本之前，全文索引仅适用于 MyISAM 存储引擎，从5.6 版本开始，InnoDB 也支持全文索引

创建表时指定全文索引

CREATE TABLE table_name (column1 data_type,column2 data_type,FULLTEXT index_name(column_name));示例
CREATE TABLE ky37 (id INT NOT NULL,title VARCHAR(100),content TEXT,FULLTEXT index_content (content));

修改表方式添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_name (column_name);示例
ALTER TABLE ky37 ADD FULLTEXT index_content (content);

直接创建全文索引

CREATE FULLTEXT INDEX index_name ON table_name (column_name);示例
CREATE FULLTEXT INDEX index_content ON ky37 (content);

6.查看和删除索引

6.1查看索引

SHOW INDEX FROM table_name;示例
SHOW INDEX FROM member;这将返回表中所有索引的详细信息，包括索引名、类型、唯一性等

6.2删除索引

直接删除索引（使用DROP INDEX语句）

DROP INDEX index_name ON table_name;示例
DROP INDEX name_index ON member;

通过修改表删除索引（使用ALTER TABLE语句）

ALTER TABLE table_name DROP INDEX index_name;示例
ALTER TABLE member DROP INDEX id_index;

删除主键索引

ALTER TABLE table_name DROP PRIMARY KEY;示例
ALTER TABLE member DROP PRIMARY KEY;

二、事务管理

1.事务的概念

MySQL事务主要用于处理操作量大，复杂度高的数据

(1)事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行

(2)事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元

(3)事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等

(4)事务是通过事务的整体性以保证数据的一致性

2.ACID特点

2.1原子性（Atomicity）

定义：原子性保证事务中的所有操作要么全部成功执行，要么全部不执行
作用：如果在事务执行过程中发生错误，事务会回滚到起始状态，之前的所有操作将被撤销
示例：如果银行转账中，一方账户扣款成功，而另一方账户加款失败，系统将回滚，撤销扣款操作，保持账户数据一致性

2.2一致性（Consistency）

定义：一致性保证事务执行前后，数据库始终处于一致的状态，遵循所有的业务规则、约束和数据完整性要求
作用：事务执行的结果必须从一种合法的数据库状态转换为另一种合法的状态，不能违反数据库的完整性约束（如主键、外键约束）
示例：在转账操作中，无论事务成功与否，转账金额总数应保持不变，数据库的完整性规则得到维护

2.3隔离性（Isolation）

定义：隔离性保证多个事务并发执行时，每个事务的中间状态对其他事务不可见，事务的操作互不干扰
作用：确保事务之间的并发执行不会相互影响，防止出现“脏读”、“不可重复读”、“幻读”等问题。不同隔离级别会影响事务间的可见性
示例：一个用户在事务中更新订单数据时，其他用户在该事务提交之前，无法看到这个修改

2.4持久性（Durability）

定义：持久性保证事务一旦提交，其对数据库的更改将被永久保存，即使系统发生故障，数据也不会丢失
作用：事务提交后的数据是可靠的，写入数据库的更改必须持久保留，即使遇到宕机等情况，事务的结果依然可以恢复
示例：在一个电商系统中，用户提交订单后，即使系统突然断电，订单数据仍然会被保存在数据库中，不会丢失

总结

原子性：确保事务要么完全执行，要么完全不执行
一致性：确保事务前后数据库处于一致的合法状态
隔离性：确保并发事务之间互不干扰
持久性：确保事务提交后其结果永久生效

3.事务隔离级别

3.1读未提交（Read Uncommitted）

描述：允许读取尚未提交的数据
特点：
脏读：可能读取到其他事务未提交的修改
安全性最差，但性能最好，通常不建议使用
示例：一个事务正在修改数据，但尚未提交，其他事务仍然可以读取到这些未提交的修改

3.2读已提交（Read Committed）

描述：只能读取已提交的数据
特点：
解决脏读：确保只读取到已提交的事务数据
可能发生不可重复读（同一事务内多次读取相同数据时，结果可能不同）
安全性较差，性能较好；Oracle 等多数数据库默认采用此级别
示例：一个事务提交后，其他事务才能读取到其修改的数据

3.3可重复读（Repeatable Read）

描述：在一个事务中，多次读取同一数据时，总是能得到相同的结果
特点：
解决脏读和不可重复读：事务在执行时，看到的数据不受其他事务影响
MySQL 默认的隔离级别，安全性较高，但性能较差
示例：即使其他事务提交了对同一数据的更改，当前事务仍然可以获得第一次读取时的数据值

3.4串行化（Serializable）

描述：完全串行化的读，确保每次读操作都能获得表级共享锁
特点：
解决脏读、不可重复读和幻读：最高的安全性
性能差，可能导致较高的锁竞争，通常不建议使用
示例：在一个事务进行数据读取时，其他事务无法进行任何读写操作，直到该事务完成

3.5默认事务隔离级别

MySQL的默认事务处理级别是可重复读（repeatable read）

Oracle和SQL Server的默认级别是读已提交（read committed）

3.6事务隔离级别的作用范围

全局级：对所有的会话有效
会话级：只对当前的会话有效

3.7查询和设置事务隔离级别

3.7.1查询全局事务隔离级别

SHOW GLOBAL VARIABLES LIKE '%isolation%';
SELECT @@global.tx_isolation;

3.7.2查询会话事务隔离级别

SHOW SESSION VARIABLES LIKE '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

3.7.3设置全局事务隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.7.4设置会话事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4.事务控制语句

MySQL中的事务控制语句用于管理事务的开始、提交、回滚和保存点操作。通过这些语句，可以确保多条数据库操作作为一个整体执行，从而保证数据的一致性

4.1BEGIN或START TRANSACTION

功能：开始一个事务
说明：使用这条语句标识事务的起点。在事务结束之前，所有操作都不会被永久保存，直到使用 COMMIT 提交事务，或者 ROLLBACK 取消事务

START TRANSACTION;
-- 或者
BEGIN;

4.2COMMIT或COMMIT WORK

功能：提交事务，将所有在事务期间执行的更改保存到数据库
说明：当事务中的所有操作都成功执行后，使用 COMMIT 确保这些更改永久写入数据库

COMMIT;

4.3ROLLBACK或ROLLBACK WORK

功能：回滚事务，撤销自事务开始以来所做的所有更改
说明：如果事务中的某些操作出现错误，或者你希望取消事务中的更改，可以使用 ROLLBACK 将数据恢复到事务开始前的状态

ROLLBACK;

4.4SAVEPOINT

功能：在事务中创建一个保存点
说明：通过保存点，事务可以回滚到该保存点，而不是回滚整个事务。SAVEPOINT 是部分回滚的一个控制机制，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称

SAVEPOINT savepoint_name;

4.5ROLLBACK TO SAVEPOINT

功能：回滚到指定的保存点
说明：使用 ROLLBACK TO SAVEPOINT 可以撤销到某个保存点之后的更改，而不影响该保存点之前的操作

ROLLBACK TO SAVEPOINT savepoint_name;

4.6RELEASE SAVEPOINT

功能：删除一个保存点
说明：释放指定的保存点，保存点一旦被释放后将无法再进行回滚操作。可以通过删除不再需要的保存点来节省系统资源

RELEASE SAVEPOINT savepoint_name;

4.7SET AUTOCOMMIT

功能：设置自动提交模式
说明：在 MySQL 中，默认情况下 AUTOCOMMIT 是启用的（即 `1`），意味着每条 SQL 语句执行后都会立即提交。如果关闭自动提交（SET AUTOCOMMIT = 0），你可以手动控制事务的开始和结束

-- 关闭自动提交模式
SET AUTOCOMMIT = 0;-- 开启自动提交模式
SET AUTOCOMMIT = 1;

4.8测试

4.8.1测试提交事务

-- 创建数据库和表
CREATE DATABASE SCHOOL;
USE SCHOOL;CREATE TABLE info(  id INT(10) PRIMARY KEY NOT NULL,  name VARCHAR(40),  money DOUBLE  
);-- 插入初始数据
INSERT INTO info VALUES(1, 'A', 1000);  
INSERT INTO info VALUES(2, 'B', 1000); 
SELECT * FROM info;-- 开启事务
BEGIN;
UPDATE info SET money = money - 100 WHERE name = 'A';  -- 更新 A 的金额
SELECT * FROM info;  -- 查看更新后的结果-- 提交事务
COMMIT;-- 退出并重新连接查看数据
QUIT;
mysql -u root -p
USE SCHOOL;
SELECT * FROM info;  -- 查看事务提交后的结果

结果：A的money变为900，事务提交后修改永久生效

4.8.2测试回滚事务

-- 开启事务
BEGIN;
UPDATE info SET money = money + 100 WHERE name = 'A';  -- 更新 A 的金额
SELECT * FROM info;  -- 查看更新后的结果-- 回滚事务
ROLLBACK;-- 退出并重新连接查看数据
QUIT;
mysql -u root -p
USE SCHOOL;
SELECT * FROM info;  -- 查看事务回滚后的结果

结果：A的money恢复为900，因为事务回滚撤销了更改

4.8.3测试多点回滚

-- 开启事务
BEGIN;
UPDATE info SET money = money + 100 WHERE name = 'A';  -- 更新 A 的金额
SELECT * FROM info;-- 创建第一个保存点 S1
SAVEPOINT S1;UPDATE info SET money = money + 100 WHERE name = 'B';  -- 更新 B 的金额
SELECT * FROM info;-- 创建第二个保存点 S2
SAVEPOINT S2;-- 插入新数据
INSERT INTO info VALUES(3, 'C', 1000);
SELECT * FROM info;-- 回滚到保存点 S1
ROLLBACK TO SAVEPOINT S1;
SELECT * FROM info;  -- 检查回滚后的数据

结果：事务回滚到保存点S1后，B的金额恢复到回滚点前的状态，并且C的插入操作被撤销

4.8.4设置控制事务

USE SCHOOL;-- 查看当前数据
SELECT * FROM info;-- 禁止自动提交
SET AUTOCOMMIT=0;
SHOW VARIABLES LIKE 'AUTOCOMMIT';-- 更新数据，但由于禁用了自动提交，不会立即生效
UPDATE info SET money = money + 100 WHERE name = 'B';
SELECT * FROM info;  -- 当前会话中看到更改-- 退出并重新连接查看数据
QUIT;
mysql -u root -p
USE SCHOOL;
SELECT * FROM info;  -- 由于未提交，其他会话中看不到更改

结果：由于AUTOCOMMIT被禁用，数据更新不会立即生效，直到事务被显示提交

三、MySQL存储引擎

1.存储引擎概念

在MySQL中，存储引擎是用于定义数据在数据库文件系统中存储和管理的方式。不同的存储引擎提供了不同的存储机制、索引策略、锁定级别以及其他数据处理特性。MySQL的灵活性体现在它可以根据具体的应用场景使用不同的存储引警，以满足不同的数据处理需求

2.存储引擎的作用

存储引擎决定了 MySQL 数据如何存储在文件系统中，包括以下几个方面：
数据存储格式：每个存储引擎将数据存储为不同的格式，如表文件的结构和数据的组织形式
索引机制：不同引擎的索引方式不同，影响查询性能
事务支持：有些存储引擎支持事务处理（如 InnoDB），而有些则不支持（如 MyISAM）
锁机制：存储引擎对并发操作的锁定策略不同，影响并发访问的效率
故障恢复：有的存储引擎提供崩溃恢复机制，有的则不提供

3.常见的存储引擎

3.1.MylSAM引擎

MyISAM 是 MySQL 中的一种存储引擎，主要用于处理以读操作为主的场景

3.1.1特点

不支持事务：
MyISAM 不支持 ACID 事务特性，这意味着无法保证操作的原子性和一致性

不支持外键约束：
这使得 MyISAM 在数据完整性管理上比较弱，开发者需自行确保数据一致性

分开存储文件：
数据文件（.MYD）、索引文件（.MYI）和表结构文件（.frm）分别存储
.frm：表的结构定义
.MYD：表的数据内容
.MYI：表的索引信息

表级锁定：
MyISAM 使用表级锁，这意味着在进行写操作时，整个表会被锁定，导致并发性能降低。
读操作和写操作无法同时进行，但多次读操作可以并发

访问速度快：
对于只读或读取为主的应用，MyISAM 提供了较高的访问速度

3.1.2支持的存储格式

静态表：
字段为固定长度，每条记录占用相同的存储空间，存取速度快，适合频繁的读操作

动态表：
字段为可变长度，可以节省存储空间，但频繁更新会产生碎片，需要定期优化

压缩表：
数据被单独压缩，适合存储大量归档数据，访问开销小

3.1.3优缺点

优点：
对只读和读取为主的操作优化良好
简单易用，管理和维护相对方便
文件存储结构简单，备份和恢复速度快

缺点：
不支持事务和外键，限制了数据完整性
表级锁限制了并发性能，读写操作无法同时进行

3.1.4应用场景

适用的生产环境：
适合对事务支持要求不高的应用
查询操作频繁的场景，如日志查询、内容管理系统等
服务器硬件资源相对较差的环境，因为 MyISAM 对系统资源的占用相对较少

不适用的场景：
写操作频繁的场景
对数据一致性和完整性要求高的应用（如金融系统）

3.2.InnoDB引擎

InnoDB 是 MySQL 的默认存储引擎，从 MySQL 5.5 版本开始，它被广泛用于需要高并发和事务处理的应用

3.2.1特点

支持事务：
InnoDB 完全支持 ACID（原子性、一致性、隔离性、持久性）事务特性，确保数据的完整性

行级锁定：
支持行级锁定，允许高并发的读写操作，减少了锁冲突的机会

支持外键约束：
InnoDB 允许创建外键，可以保证表之间的 referential integrity（参照完整性）

崩溃恢复：
内置的崩溃恢复机制能够在系统故障后自动恢复数据

表与主键的簇存储：
数据和索引以簇的方式存储，优化了数据访问的性能

支持全文索引（从 MySQL 5.6 版本开始）：
能够进行高效的文本搜索

3.2.2性能

读写性能：
InnoDB 的行级锁定机制允许多个事务并发进行读写，适合高并发的在线事务处理（OLTP）系统

自增长字段：
InnoDB 支持自增长字段，且要求该字段是索引的一部分

数据缓存：
提供了高效的缓存机制，能有效提升数据访问的速度

3.2.3优缺点

优点：
提供事务支持，确保数据一致性
行级锁定提高了并发性能
支持外键约束，增强了数据完整性
崩溃恢复机制保障数据安全

缺点：
对系统资源的要求较高，尤其是内存
在某些情况下，清空表（TRUNCATE）的性能较慢，因为是逐行删除