数据库索引、事务

一、索引

1. 索引的概念

• 索引是一个用于快速查找数据库表中记录的数据结构，通过存储索引值和对应的数据行物理地址来加快查询速度。
• 索引可以减少全表扫描的次数，使查询更加高效。

2. 索引的作用

• 索引的主要作用是加快查询速度，尤其是在数据量较大或涉及多表连接的情况下。
• 索引可以降低数据库的 I/O 成本和排序成本。
• 唯一索引可以保证数据的唯一性，并加快表之间的连接操作。

3. 索引的副作用

• 索引会占用额外的磁盘空间，特别是在 InnoDB 引擎中，索引文件是表数据文件的一部分。
• 索引的创建和维护会增加插入和修改数据的时间成本。

4. 创建索引的原则

• 适合创建索引的场景包括：主键和外键字段、大于300行的表、经常连接的表字段、选择性高且查询频繁的字段等。
• 不适合创建索引的场景包括：唯一性太差、更新频繁、数据量过大或是大文本字段。

5. 索引的分类和创建方法

5.1 普通索引

普通索引是最基本的索引类型，没有唯一性限制，适用于需要加速查询但不需要唯一性检查的场景。

• 直接创建索引：

CREATE INDEX phone_index ON kyky (phone);

• 修改表方式创建索引：

ALTER TABLE kyky ADD INDEX id_index (id);

• 创建表时指定索引：

CREATE TABLE test(
    id INT(4) NOT NULL,
    name VARCHAR(10) NOT NULL,
    cardid VARCHAR(18) NOT NULL,
    INDEX id_index (id)
);

5.2 唯一索引

唯一索引确保索引列中的每个值都是唯一的，允许空值。唯一索引通常用于需要确保唯一性的场景，如身份证号、邮箱等。

• 直接创建唯一索引：

CREATE UNIQUE INDEX address_index ON kyky (address);

• 修改表方式创建唯一索引：

ALTER TABLE kyky ADD UNIQUE cardid_index (cardid);

• 创建表时指定唯一索引：

CREATE TABLE amd2(
    id INT,
    name VARCHAR(20),
    UNIQUE id_index (id)
);

5.3 主键索引

主键索引是一种特殊的唯一索引，不允许空值，一个表只能有一个主键。主键用于唯一标识表中的每一行记录。

• 创建表时指定主键索引：

CREATE TABLE test1(
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

• 修改表方式创建主键索引：

ALTER TABLE ky20 ADD PRIMARY KEY (id);

5.4 组合索引

组合索引可以包含多个列，用于优化涉及多列的查询。遵循最左前缀原则，索引的使用顺序必须与查询条件一致。

• 创建表时指定组合索引：

CREATE TABLE amd1(
    id INT NOT NULL,
    name VARCHAR(20),
    cardid VARCHAR(20),
    INDEX index_amd (id, name)
);

• 组合索引的使用示例：

SELECT id, name FROM test3 WHERE id = 1 AND name = 'zhangsan'; # 触发组合索引
SELECT name, id FROM test3 WHERE name = 'zhangsan' AND id = 1; # 不触发组合索引

5.5 全文索引

全文索引（FULLTEXT）用于在文本中进行模糊查询，适合于搜索文章内容。MySQL 5.6 及以后版本的 InnoDB 引擎支持全文索引。

• 直接创建全文索引：

CREATE FULLTEXT INDEX remark_index ON kyky (remark);

• 修改表方式创建全文索引：

ALTER TABLE kyky ADD FULLTEXT remark_index (remark);

• 创建表时指定全文索引：

CREATE TABLE kyky (
    id INT,
    remark TEXT,
    FULLTEXT remark_index (remark)
);

• 使用全文索引查询：

SELECT * FROM kyky WHERE MATCH(remark) AGAINST('this is vip');

6. 查看索引

在 MySQL 中，可以通过以下命令来查看表的索引信息：

-- 水平显示表索引信息
SHOW INDEX FROM 表名;

-- 垂直显示表索引信息
SHOW INDEX FROM 表名\G;

各字段含义如下：

• Table: 表的名称
• Non_unique: 如果索引内容唯一，则为 0；如果可以不唯一，则为 1。
• Key_name: 索引的名称。
• Seq_in_index: 索引中的列序号，从 1 开始。
• Column_name: 列名称。
• Collation: 列以什么方式存储在索引中。
• Cardinality: 索引中唯一值数目的估计值。
• Sub_part: 如果列只是被部分地编入索引，则为被编入索引的字符的数目；如果整列被编入索引，则为 NULL。
• Packed: 指示关键字如何被压缩。
• Null: 如果列含有 NULL，则含有 YES；如果没有，则该列含有 NO。
• Index_type: 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
• Comment: 备注。

7. 删除索引

删除索引可以通过以下方式进行：

直接删除索引

-- 删除指定索引
DROP INDEX 索引名 ON 表名;

例：DROP INDEX name_index ON member;

修改表方式删除索引

-- 修改表结构删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

例：ALTER TABLE member DROP INDEX id_index;

删除主键索引

-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

总结

在数据库设计和优化中，合理使用索引是提升查询效率的关键。不同类型的索引适用于不同的需求，如普通索引、唯一索引、组合索引和全文索引等。删除索引时，需谨慎操作，确保不会影响到数据库的正常运行和数据完整性。

二、MySQL事务

MySQL事务主要用于处理操作量大、复杂度高的数据，确保一组数据库操作要么全部执行成功，要么全部失败。

1. MySQL事物的概念

事务是一个包含多个数据库操作命令的机制，这些命令作为一个整体提交或撤销操作。事务的主要特点包括：

• 不可分割性：事务作为一个不可分割的工作单元，必须要么完全执行，要么完全撤销。
• 一致性：事务在执行前和执行后必须使数据库处于一致的状态。
• 隔离性：事务在并发执行时，每个事务的操作不会影响其他事务。
• 持久性：一旦事务提交，所有的更改都将持久化，即使系统发生故障也不会丢失。

实例：在一个人员管理系统中，删除一个人员记录涉及多个操作，如删除基本资料、信箱和文章等。如果这些操作不作为一个整体处理，可能会导致数据不一致。例如，如果在删除人员信息的过程中发生系统故障，可能会留下不完整的数据。

2. 事务的 ACID 特性

ACID 是事务应具备的四个特性：

1. 原子性 (Atomicity)：事务中的所有操作要么都执行，要么都不执行。即使事务中的某个操作失败，也不会影响其他操作。

   案例：如果 A 给 B 转账 100 元，事务中包含从 A 账户扣款和向 B 账户加款。如果系统突然崩溃，事务保证要么两个操作都完成，要么两个操作都不会完成，以避免 A 的账户被扣款而 B 的账户没有收到钱的情况。

2. 一致性 (Consistency)：事务在开始之前和结束之后，数据库的完整性约束必须得到满足。

   案例：在银行转账过程中，无论事务是否成功，事务结束后 A 和 B 的账户总金额必须保持一致。

3. 隔离性 (Isolation)：并发事务之间互不干扰。每个事务在执行时，应该看不到其他事务的中间结果。

   案例：如果事务 A 正在修改某条记录，事务 B 在事务 A 完成之前不会看到事务 A 的修改结果。

4. 持久性 (Durability)：事务一旦提交，对数据库的更改是永久性的，不会因为系统崩溃或其他故障而丢失。

   案例：无论系统发生何种故障，事务提交后的所有更改都将被保留在数据库中。

3. 事务隔离级别

事务隔离级别定义了不同事务之间的可见性和操作的隔离程度。MySQL 提供了四种隔离级别：

1. READ UNCOMMITTED：允许读取未提交的数据，可能导致脏读。性能最佳，但安全性最差。

   案例：事务 A 修改了数据 X，但尚未提交。事务 B 可以读取到未提交的 X 数据。如果事务 A 回滚，事务 B 读取到的数据可能是无效的。

2. READ COMMITTED：只能读取已提交的数据，可以避免脏读，但可能会遇到不可重复读。

   案例：事务 A 提交数据后，事务 B 只能看到事务 A 已提交的数据，但事务 A 中的两次相同查询可能得到不同结果。

3. REPEATABLE READ：可以避免脏读和不可重复读，但可能出现幻读。MySQL 的默认隔离级别。

   案例：事务 A 读取数据后，即使其他事务提交了修改，事务 A 的查询结果仍然保持不变。但如果另一个事务插入了新数据，事务 A 可能会看到数据总量的变化。

4. SERIALIZABLE：最高级别的隔离性，解决了脏读、不可重复读和幻读，但性能较差。

   案例：事务 A 和事务 B 都需要对同一表进行操作。为了避免并发问题，系统会对表加锁，确保每次操作都按照顺序进行。

4. 事务控制语句

事务控制语句用于显式地管理事务的生命周期：

• BEGIN 或 START TRANSACTION：开始一个事务。
• COMMIT 或 COMMIT WORK：提交事务，永久保存对数据库的更改。
• ROLLBACK 或 ROLLBACK WORK：回滚事务，撤销对数据库的所有未提交的更改。
• SAVEPOINT：设置一个回滚点，可以在事务中创建多个回滚点。
• ROLLBACK TO SAVEPOINT：回滚到指定的保存点。

示例：

• 提交事务：

BEGIN;
UPDATE info SET money = money - 100 WHERE name = 'A';
COMMIT;

• 回滚事务：

BEGIN;
UPDATE info SET money = money + 100 WHERE name = 'A';
ROLLBACK;

• 多点回滚：

BEGIN;
UPDATE info SET money = money + 100 WHERE name = 'A';
SAVEPOINT S1;
UPDATE info SET money = money + 100 WHERE name = 'B';
SAVEPOINT S2;
INSERT INTO info VALUES (3, 'C', 1000);
ROLLBACK TO S1;

• 设置事务自动提交：

SET AUTOCOMMIT = 0;  # 禁用自动提交
SET AUTOCOMMIT = 1;  # 启用自动提交
SHOW VARIABLES LIKE 'AUTOCOMMIT';  # 查看当前 AUTOCOMMIT 值

如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
当然无论开启与否，begin; commit|rollback; 都是独立的事务。

三、存储引擎概念

1. 存储引擎介绍

存储引擎是 MySQL 用于管理数据存储的技术。不同的存储引擎具有不同的特性，如事务支持、索引机制和锁定级别等。以下是 MySQL 中两个常用的存储引擎：

1.1 MyISAM

• 特性：
  • 不支持事务和外键约束。
  • 支持全文索引。
  • 数据文件和索引文件分开保存。
• 文件结构：
  • .frm 文件：存储表结构定义。
  • .MYD 文件：存储数据。
  • .MYI 文件：存储索引。
• 锁定机制：表级锁定。读写操作时会锁定整个表，导致读写操作串行化，适合读多写少的场景。
• 存储格式：
  • 静态表：字段长度固定，存储迅速，容易缓存，恢复简单，但占用空间较多。
  • 动态表：字段长度可变，存储节省空间，但可能产生碎片，需要定期优化。
  • 压缩表：通过 myisamchk 工具创建，占用空间小，访问开销也小。
• 适用场景：适合于单向读取或写入较多的业务场景，对事务一致性要求不高的业务。

 

1.2 InnoDB

• 特性：
  • 支持事务处理和四种事务隔离级别。
  • 从 MySQL 5.5.5 版本开始，默认存储引擎为 InnoDB。
  • 支持外键约束和全文索引（5.5 版本后）。
  • 行级锁定，支持分区和表空间。
• 文件结构：
  • 不直接保存表的行数，计数操作需要扫描整个表。
  • 自增字段需要索引。
• 锁定机制：行级锁定，但全表扫描操作仍会表现为表级锁定。对于事务处理，行级锁定避免了大部分的锁冲突，但可能出现死锁。
• 适用场景：适合需要高并发、事务处理的应用场景，对硬件资源要求较高。

2. 查看系统支持的存储引擎

要查看 MySQL 系统支持的存储引擎，可以使用以下 SQL 命令：

SHOW ENGINES;

该命令将列出所有支持的存储引擎及其状态。

3. 查看表使用的存储引擎

方法一：使用 SHOW TABLE STATUS 命令

SHOW TABLE STATUS FROM 库名 WHERE NAME='表名'\G;

例如：
SHOW TABLE STATUS FROM SCHOOL WHERE NAME='info'\G;

方法二：使用 SHOW CREATE TABLE 命令

USE 库名;
SHOW CREATE TABLE 表名;

例如：
USE SCHOOL;
SHOW CREATE TABLE info;

4. 修改存储引擎

方法一：通过 ALTER TABLE 修改

USE 库名;
ALTER TABLE 表名 ENGINE=MyISAM;

例如：
USE SCHOOL;
ALTER TABLE info ENGINE=MyISAM;
SHOW CREATE TABLE info;

方法二：修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务

sudo vim /etc/my.cnf

在 [mysqld] 部分添加或修改：

default-storage-engine=INNODB

然后重启 MySQL 服务：

sudo systemctl restart mysqld.service

此方法只对新创建的表有效，已存在的表不会受影响。

方法三：通过 CREATE TABLE 创建表时指定存储引擎

USE 库名;
CREATE TABLE 表名 (字段1 数据类型, ...) ENGINE=MyISAM;

例如：
USE SCHOOL;
CREATE TABLE hellolic (name VARCHAR(10), age CHAR(4)) ENGINE=MyISAM;

总结

MyISAM和InnoDB之间的主要区别：

1. 事务支持

• MyISAM：不支持事务，也不支持回滚和崩溃恢复。MyISAM适合一些不需要事务处理的应用场景，如只需要简单的读写操作的系统。
• InnoDB：支持事务，提供ACID（原子性、一致性、隔离性、持久性）属性，并支持回滚、崩溃恢复等机制。InnoDB更适合需要事务处理和数据一致性的场景。

2. 锁机制

• MyISAM：使用表级锁，即在对一个表进行读或写操作时，整个表会被锁定，导致并发性能较低，适合读多写少的应用场景。
• InnoDB：使用行级锁，即在对某一行数据进行操作时，只锁定该行，不会影响其他行的操作，并发性能更高，更适合读写频繁的场景。

3. 外键支持

• MyISAM：不支持外键约束，因此无法在表与表之间建立关系约束。
• InnoDB：支持外键约束，可以维护表之间的引用完整性，有助于防止数据的不一致性。

4. 崩溃恢复

• MyISAM：在系统崩溃后，可能会导致数据损坏，恢复数据需要手动修复。
• InnoDB：拥有崩溃恢复功能，通过日志文件（redo log）可以自动恢复未完成的事务，保证数据的完整性。

5. 数据存储

• MyISAM：将表结构、数据、索引分别存储在不同的文件中。表结构保存在.frm文件中，数据保存在.MYD文件中，索引保存在.MYI文件中。
• InnoDB：表数据和索引一起存储在一个.ibd文件中，同时支持将多个表的数据存储在共享表空间中。

6. 性能

• MyISAM：在读多写少的场景下表现较好，访问速度快，适合查询、插入为主的应用，如日志系统、报表系统等。
• InnoDB：在高并发场景下表现优异，尤其在事务处理、数据一致性要求高的应用场景中，如金融系统、电子商务系统等。

7. 存储格式

• MyISAM：支持静态表、动态表和压缩表三种不同的存储格式。
• InnoDB：使用聚簇索引，将数据按主键顺序存储，支持分区和表空间的管理。

8. 全表扫描

• MyISAM：对于SELECT COUNT(*)操作，MyISAM能快速返回结果，因为它存储了表的行数。
• InnoDB：对于SELECT COUNT(*)操作，InnoDB需要扫描全表，性能会较慢。

9. 数据行数的存储

• MyISAM：存储表的总行数，因此SELECT COUNT(*)操作非常快。
• InnoDB：不存储表的行数，需要扫描全表来计算行数，因此在大表中执行SELECT COUNT(*)时会比较慢。

10. 死锁

• MyISAM：由于采用表级锁定机制，不会产生死锁。
• InnoDB：由于采用行级锁定机制，在高并发的环境下可能会出现死锁，需要谨慎处理。

11. 适用场景

• MyISAM：适合读多写少、不需要事务的场景，如简单的内容管理系统、数据仓库等。
• InnoDB：适合需要高并发、支持事务和外键约束的应用场景，如在线交易系统、企业级应用等。