MySQL索引 及事务

索引介绍

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现

索引的概念

• 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。
• 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
• 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
• 索引是表中一列或者若干列值排序的方法。
• 建立索引的目的是加快对表中记录的查找或排序。

索引的作用优点

• 加快查询速度，提高数据库性能

• 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。

• 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。避免排序和使用临时表

• 可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本。将随机I/O转为顺序I/O

• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

• 可以加快表与表之间的连接。

• 在使用分组和排序时，可大大减少分组和排序的时间。

• 建立索引在搜索和恢复数据库中的数据时能显著提高性能

缺点

• 占用额外的磁盘空间，影响插入速度 占用磁盘空间
• 索引提高了查询的效率，但降低了更新表（Insert, Update, Delete）的速度，因为增删改表也需要同时维护索引

索引类型

1. B-树索引（B-Tree Index）

   • 最常见的索引类型，大部分MySQL引擎都支持B-树索引。
   • B-树索引通过树形结构存储数据，支持全键值、键值范围和键值前缀查询，也可以对查询结果进行ORDER BY排序。
   • MySQL中B-树索引的变种是B+树索引，它在B-树的基础上进行了优化，所有数据都存储在叶子节点，并且叶子节点之间通过指针相连，提高了区间访问的性能。

B Tree

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree 缺点 节点存储效率问题：

B树的每个节点都存储了关键字（key）和数据（data） 由于磁盘页的存储空间有限，如果数据（data）部分较大，会导致每个节点（磁盘块）能存储的关键字数量减少。这意味着在数据量相同的情况下，B树可能需要更多的节点和更深的树结构来存储这些数据，从而增加了IO操作的次数，影响性能。

连续范围查找都要从头开始，效率不稳定

B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+Tree相对于B-Tree有不同：

• 非叶子节点只存储键值信息。
• 所有叶子节点之间都有一个链指针。
• 数据记录都存放在叶子节点中。

1. 节点大小：每个节点（页）的大小为16KB。
2. 主键类型：这里考虑两种常见的主键类型，int（占用4字节）和bigint（占用8字节）。
3. 指针大小：假设每个指针（用于连接节点）占用6字节。
4. 数据行大小：为了方便计算，我们假设每条数据行的大小为1KB（实际中可能因字段数量和类型而异）。

深度为三的B+树存储能力

• 根节点（第一层） ：

  • 根节点是非叶子节点，它指向第二层的节点。
  • 对于int类型的主键，根节点可以指向约1600个第二层节点。
  • 对于bigint类型的主键，根节点可以指向约1170个第二层节点。

• 第二层节点：

  • 每个第二层节点也是非叶子节点，指向第三层（叶子层）的节点。
  • 因此，第二层节点的总数（基于第一层节点的指针数）将决定第三层节点的数量。

• 第三层节点（叶子节点） ：

  • 每个叶子节点存储16条数据。
  • 所以，第三层节点的总数乘以每个节点的存储能力（16条数据）就是B+树总共能存储的数据量。

具体计算

• 对于int类型的主键：

  • 第三层节点总数 = 1600（第一层节点数） * 1600（第二层节点数，假设与第一层相同，实际可能因数据分布而异）
  • 总数据量 = 第三层节点总数 * 16（每个叶子节点的数据量）
  • 约等于 40960000条数据。

• 对于bigint类型的主键：

  • 第三层节点总数 = 1170（第一层节点数） * 1170（第二层节点数，同样假设）
  • 总数据量 = 第三层节点总数 * 16（每个叶子节点的数据量）
  • 约等于 21902400条数据。

所以在 InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储

索引的优缺点

• 优点：

  • 提高数据检索效率，降低数据库的I/O成本。
  • 通过对索引列进行排序，降低数据排序的成本，减少CPU消耗。

• 缺点：

  • 索引提高了查询的效率，但降低了更新表（Insert, Update, Delete）的速度，因为增删改表也需要同时维护索引。
  • 索引需要占用额外的存储空间。

索引管理

查看索引

show index from 表名;
show keys from 表名;

例子：

show index from hellodb.students;

建立索引

格式：

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

在 MySQL 中，你可以直接在创建索引的语句中指定前缀长度：

	CREATE INDEX idx_name ON students(name(5));

这个语句将创建一个名为 idx_name 的索引，它只索引 students 表中 name 列的前5个字符。

create index index_name on students(name);  #给students 表加 name表
explain select * from students where name like 's%';  #找s开头的人看是否会用索引
explain select * from students where name like '%s';  #找s结尾的人是否会用
explain select * from students where name like '%s%'; #包含s的是否会调用索引
explain select * from students where name like 'x%';  #是否使用索引

删除索引

●直接删除索引

DROP INDEX 索引名 ON 表名;

EXPLAIN 工具

以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

参考资料： dev.mysql.com/doc/refman/…

语法：

EXPLAIN SELECT clause

例子：

 explain select * from stundets where stuid=20;

说明：

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询：SIMPLE ，复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

使用 profile 工具 监控

#打开后，会显示语句执行详细的过程
set profiling = ON;
#查看语句,注意结果中的query_id值
show profiles ;
#显示语句的详细执行步骤和时长
Show profile for query #  
show profile for query 1;

存储引擎

MyISAM 引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 崩溃恢复性较差
• MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

• 只读（或者写较少）
• 表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据库引擎

管理存储引擎

查看mysql支持的存储引擎

show engines;
​
show table status like 'user'\G   #这张表用的是 myisam
show table status from mysql\G ;
​

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like  'tb_name';
show create table tb_name;

设置表的存储引擎：

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

中的系统数据库

• mysql 数据库

是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

• information_schema 数据库

MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

• performance_schema 数据库

MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

• sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

存储过程

存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中

存储过程优势

存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量

事务

事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元

事务日志：记录事务信息，实现undo,redo等故障恢复功能

9.2.1ACID特性：

• A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，只要有一个失败后回滚
• C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)
• I：Isolation隔离性；一个事务所做出的操作在提交之前，能不能为其它事务所见；和隔离级别有关系 4个隔离级别
• D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

Transaction生命周期

9.2.1.1 管理事务

显示启动事务

BEGIN
BEGIN WORK
START TRANSACTION
​
begin       #开启事务
begin  work
start  transaction

结束事务

#提交,相当于vi中的wq保存退出
COMMIT
commit
​
#回滚,相当于vi中的q!不保存退出
ROLLBACK
rollback

注意：只有事务型存储引擎中的DML语句方能支持此类操作

自动提交：

set autocommit={1|0}

默认为1，为0时设为非自动提交

建议：显式请求和提交事务，而不要使用"自动提交"功能

例子： 修改变量开启事务

mysql> select @@autocommit;      #查看变量的值
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
​
mysql> set autocommit=0;       #设置为 不主动提交事务
Query OK, 0 rows affected (0.00 sec)
​
​
insert teachers values(5,"wuyazi",70,'M');   #插入数据 测试
select *  from   hellodb.teachers;   #本客户端可以看到
​
​
​
#去别的终端查看，  看不到  wuyazi  这个选项
select *  from   hellodb.teachers;   #
​
​
​
rollback  #回退  就看不到  第5条数据了， 可以反悔
commit

例子： 手动开启事务

begin    #开启事务
insert teachers values(6,"yuebuqun",50,'M');   #插入数据 测试
commit   #提交

并不是所有的操作都可以撤回

drop   tables  students;

dml: 增删改查 可以撤销 insert

ddl： 删库等严重操作 drop alter create

9.2.1.2 事务的效率对比

mysql  -uroot -p'123123' hellodb < testlog.sql    # 导入存储过程
truncate table   testlog    #删除testlog 表中的内容
mysql> call sp_testlog;      #执行存储过程
Query OK, 1 row affected (1.01 sec)
​
​
​
​
​
mysql> begin;                 #开启事务
​
mysql> call sp_testlog;commit;
Query OK, 1 row affected (0.99 sec)
​

因为事务减少了io 把事务当成了一个整体， 插入100000条数据只有1次io，而 外面一个insert就是一次io

input

output

9.2.1.3 死锁

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态，

mysql数据库会自动发现死锁，自动回滚。

范例：找到未完成的导致阻塞的事务

死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
范例：找到未完成的导致阻塞的事务
BEGIN;
#开启事务
update students set classid=10;
#终端1
​
update students set classid=20;
#终端2
​
show engine innodb status;
#在第三个会话中执行
​
​
show processlist;
kill 13;    #13是进程列表
show global variables like 'innodb_lock_wait_timeout';   #查看死锁的等待时间

acid

a 原子性

c 一致性

i 隔离性

d 持久性

9.2.2 事务隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

select @@tx_isolation;    #系统隔离级别，是系统自带变量
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
​
vim  /etc/my.cnf
transaction-isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
​
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'

隔离级别	脏读	可重复读	幻读	加读锁
读未提交	可以出现	可以出现	可以出现	否
读提交	不允许出现	可以出现	可以出现	否
可重复读	不允许出现	不允许出现	可以出现	否
序列化	不允许出现	不允许出现	不允许出现	是

• READ UNCOMMITTED（未提交可读 脏读） 可读取到未提交数据，产生脏读

• READ COMMITTED（提交可读，每次读取数据不一致） 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致

• REPEATABLE READ 可重复读 幻读 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置

• SERIALIZABLE 串读 可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

可重复读： A B 两个事务， A 事务的所有操作包括提交和未提交的， B 事务都看不到， B事务只能看到A事务的初始值， 只有B 事务，提交结束，B事务才能看A事务做的修改

未提交可读： A B 两个事务， 不需要提交， 都可以看到对方的所有操作

串行化 ： 会互相影响， 加锁

MVCC和事务的隔离级别：

MVCC（多版本并发控制机制）只在READ COMMITTED（提交可读）和REPEATABLE READ（可重复读）两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

演示实例：

可重复读 默认是此隔离级别

#1.打开两个终端的 数据库，都开启事务
use hellodb;
begin;
​
​
#在其中一个终端上插入数据在未提交前，另一个终端的事务无法看到修改的结果，但是自己可以看见
insert teachers values(null,'wuyazi',79,'M');
select * from teachers;
​
#终端1 提交后  终端2只要不结束事务还是看不到，需要提交事务后才可以看到。
commit

演示第一隔离级别 可看见脏读 READ UNCOMMITTED

vim /etc/my.cnf
[mysqld]
transaction-isolation=READ-UNCOMMITTED
​
systemctl restart mysqld
​
select @@tx_isolation;   #查看隔离级别 
​
begin;
#在其中一个终端上插入数据在未提交前,另一终端也可以看见
insert teachers values(6,'c',80,'M');
select * from teachers;

演示串行化 最严格的隔离级别 串行影响并发性

#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'
​
​
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
systemctl restart mysqld
​
​
select @@tx_isolation;   #查看隔离级别 
​
begin;
#开启事务两边可以同时读表，会互相锁
select * from teachers;
delete from teachers where tid=5;
#无法删除加锁  并发性较差
​
​