0.索引结构
0.1 B+树
叶子节点：单向链表，包含了所有插入的元素
非叶子节点：主要起到索引的作用
mysql中，B+树的每个非叶子节点存的是索引和指针，叶子节点还是个双向链表，提高区间访问的性能，每个节点都是存放在Page（页）中
1.Mysql存储引擎
1.1.InnoDB
Mysql5.5后，InnoDB是默认的存储引擎
- 特点
DML（增删改）操作遵循ACID模型，支持事务（原子性、隔离性、一致性、持久性）
行级锁，提高并发访问性能
支持外键约束，保证数据的正确性 和完整性 - 文件
xxx.idb
索引结构：
B+Tree，相对于一般二叉树如果按顺序插入，可能造成单链表结构，并且如果数据量大时，层级会增高，查询效率低。
相对于BTree，B+Tree中每页中不存放数据，只存放节点和指针，那么存放的指针就会变多，指针多了，相同数据量下，层级将变少，效率更高。
1.2.MyISAM
- 特点
不支持事务，不支持外键
支持表级锁
访问速度快 - 文件
xxx.MYD、xxx.MYI、xxx.sdi
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+树索引 | 支持 | 支持 | 支持 |
| hash索引 | - | - | 支持 |
| 全文索引 | 支持（5.6以后） | 支持 | - |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
2.性能分析
2.1.慢查询日志
慢查询日志记录了所有执行时间超过指定参数（默认10秒）的所有SQL语句的日志
通过show variables like 'slow_query_log'命令查看是否开启慢查询日志。
Mysql的慢查询日志默认不开启，可以通过修改配置文件windows（my.ini），Linux（/etc/my.cnf）开启日志，
Windows下：5.6版本以后取消了参数log-slow-queries，更改为slow-query-log-file
开启了慢查询日志后，如果有慢查询操作，便会在日志文件下生成日志记录
# Time: 2023-02-17T14:11:04.279014Z
# User@Host: root[root] @ localhost [::1] Id: 8
# Query_time: 2.073090 Lock_time: 0.000059 Rows_sent: 1 Rows_examined: 2000000
SET timestamp=1676643064;
SELECT COUNT(*) FROM player WHERE MONEY = 1000;
2.2.profile详情
profile能够在SQL优化时帮助我们了解时间都消耗到哪里去了
show profile for query [Query_ID];查具体耗时
2.3.explain
explain可以获取Mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序
-
id字段：
如果ID相同，则表示sql语句是从上往下执行的：tp–>t–>p。
如果ID不同，则ID越大的越先执行。t–>m–>subquery2–>p
-
select_type：
表示select的类型，常见simple,primary等 -
type：
表示连接类型，性能由高及低依次为NULL、system、const、eq_ref、ref、range、index、all
const：主键或唯一性索引、ref：普通索引、index：用了索引，但遍历整个索引树、all：全表扫描 -
possible_keys：
显示可能在这张表上的索引，一个或多个 -
key：
实际用到的索引，如果没有则是NULL -
key_len:
使用到的索引的字节数 -
rows：
查询行数（预估值） -
filtered：
返回结果行数占需读取行数的百分比，值越大越好。
2.4.索引使用规则
- 最左前缀法则
select语句中使用联合索引查询时，会从联合索引的最左侧开始匹配，并且不跳过中间的列，如果where后面没有联合索引中的部分字段，那么索引将部分失效。 - 索引列运算
不要在索引上进行运算，索引不要使用运算函数，字符串索引要加引号，否则索引将失效。 - 模糊查询
如果是头部模糊匹配'%name'，则锁引失效 - OR连接
用or关键字时，只要or任意一侧的索引失效，那么另一侧的索引也将失效 - 数据分布
数据库判断走全表扫描可能会比匹配索引快时，索引也会失效
2.5.sql优化
2.5.1.插入优化
- 批量插入
批量插入最好用一条insert语句执行完所有数据的插入，如果数据量大于1000条，再分为多条insert语句插入，或者使用存储过程循环插入 - 手动提交事务
执行多条insert语句时，开启事务，最后统一提交事务 - 主键顺序插入
数据插入时，尽量按主键顺序插入，如果是乱序插入，会出现索引页分裂现象 - 大批量插入数据
如果一次性需要插入大批量数据，可以使用Mysql数据库提供的load指令进行插入
2.5.2.主键优化
数据插入时，尽量按主键顺序插入，如果是乱序插入，会出现索引页分裂现象
在满足业务需求的情况下，尽量降低主键的长度
尽量不要使用UUID做主键或者是其他自然主键
业务操作时，避免对主键进行修改
2.5.3.order by 优化
- using filesort：读取满足条件的数据后，在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的都叫filesort排序
- using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，效率更高。默认索引都是升序索引ASC
多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引
2.5.4.group by 优化
在分组查询时，通过索引可以提高效率。
索引也需要满足最左前缀法则。
2.5.5.limit优化
一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化
2.5.6.count优化
效率排序：count(字段)<count(主键id)<count(1)=count(*)
2.5.7.update优化
update时，where后面没有用到索引，这时加的锁是表锁
3.锁
3.1.全局锁
全局锁就是堆整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DDL语句，DML语句，以及更新操作的事务提交语句都将被阻塞。
通过FLUSH TABLES WITH READ lock;命令对数据库加锁，UNLOCK TABLES命令解锁。
3.2.表级锁
每次操作锁住整张表，锁定粒度大。发生锁冲突的概率最高，并发度最低。MyISAM 、InnoDB 、BDB存储引擎都支持表级锁
-
表锁
- 读锁（read lock）：
语法：加锁lock tables 表名 read；加了读锁后，客户端就只能进行读操作，而阻塞改操作。释放锁unlock tables - 写锁（write lock）：
语法：lock tables 表名 write；客户端加了写锁后，对自己客户端的读和写都不受影响，但其他客户端会阻塞读和写。释放锁unlock tables
- 读锁（read lock）：
-
元数据锁（mete data lock, MDL）
MDL加锁过程是系统自动控制的，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要保护表元数据的数据（表结构）一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性
查看元数据锁：SELECT object_type,object_schema,object_name,lock_type,lock_duration FROM performance_schema.metadata_locks; -
意向锁
为避免行锁和表锁冲突，当事务A申请行锁时，数据库会自动先开始申请表的意向锁，再申请行锁，这样B事务想要申请表锁时就只需判断是否存在意向锁，如果存在，则进入阻塞，直到A事务释放了锁
3.3.行级锁
每次操作所住对应的数据行，锁定的粒度最小，发生锁冲突的概率最低，并发度最高。只有InnoDB支持行级锁。
InnoDB的数据是基于索引组织的，行级锁是对索引进行加锁来实现的。
- 行锁（Record Lock）：锁定单行记录，阻止其他事务的update和delete。
- 间隙锁（Gap Lock）：锁定索引记录的间隙，确保索引的间隙不会发生改变，防止其他事务在这个间隙进行insert，产生幻读
- 临键锁（Next-Key Lock）：行锁和间隙锁的组合，会同时锁住行记录和索引的间隙。
3.3.1.行锁（Record Lock）
InnoDB的行锁是针对索引加的锁，并且该索引不能失效，否则会升级为表锁
两种类型的行锁：共享锁（S）和排他锁（X）
- 共享锁：允许事务读一行，阻止其他事务获得相同数据，共享锁之间是兼容的，但共享锁和互斥锁之间是互斥的
- 排他锁：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据的共享锁和排他锁
| sql | 行锁类型 | 说明 |
|---|---|---|
| INSERT | 排他锁 | 自动加锁 |
| UPDATE | 排他锁 | 自动加锁 |
| DELETE | 排他锁 | 自动加锁 |
| SELECT | 不加任何锁 | |
| SELECT … LOCK IN SHARE MODE | 共享锁 | |
| SELECT … FOR UPDATE | 排他锁 |
默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
行锁是针对索引加的锁，如果不通过索引条件检索数据，那么InnoDB将对整个表进行加锁，升级为表锁
3.3.2.间隙锁/临键锁
索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，Next-Key Lock退化为间隙锁
索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止
4.InnoDB引擎
4.1.逻辑存储结构
TableSpace（表空间）：一个mysql实例可以对应多个表空间，用于存储记录，索引等数据，即ibd文件
Segment（段）：InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个区
Extent（区）：表空间的单元结构，每个区的大小为1M，默认情况下，InnoDB一个页为16K，一个区有64个页
Page（页）：磁盘管理的最小单元，每页大小默认16K
Row（行）：包含Trx_id、Roll_pointer。
在一般情况下，InnoDB一次最少从磁盘中读取一页的内容到内存中，一次最少把内存中的一页内容刷新到磁盘中。
4.2.架构
4.2.1.内存结构
- Buffer Pool：缓冲池是主内存中的一个区域，当进行CURD操作时，会先操作缓冲池中的数据，并以一定的频率刷新到磁盘中。以Page页为单位，分别为
free page：空闲页；clean page：被使用过，但未修改；dirty page：修改过，但未刷新到磁盘中 - Change Buffer：更改缓冲区（针对非唯一二级索引页），DML语句执行时，如果Buffer Pool中没有，不会直接操作磁盘，而是读到Change Buffer，再以一定频率刷新到Buffer Pool，再一起刷新到磁盘中
- Adaptive Hash
Index：自适应Hash索引，InnoDB会自动判断，当Hash值比B+树更适合做索引时，会自动建立Hash索引。 - Log Buffer：日志缓冲区（日志都有缓冲区），为了提高效率，将log日志数据（redo log、undo log）定期刷新到磁盘空间中。
4.2.2.磁盘结构
4.2.3.后台线程
后台线程的作用是将内存空间的数据刷新到磁盘空间
4.3.事务原理
事务是一组不可分割工作单位，这组操作要么同时成功，要么同时失败。
4.3.1.四大特性：ACID
原子性：事务是一组不可分割工作单位，这组操作要么同时成功，要么同时失败。
一致性：事务完成时，必须使所有的数据都保持一致状态。
隔离性：保证事务不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或回滚，数据库中的数据的改变将时永久的。
4.3.2.事务隔离级别
事务并发时出现的问题：
- 脏读：事务T1读取了事务T2修改但未提交的数据之后，事务T2回滚，T1读取的数据就是无效的
- 不可重复度：事务T1多次读取一份数据，同时事务T2修改了这份份数据然后提交了，这时T1读取数据将不一致。
- 幻读：一个事务T1按照条件查询数据时，没有对应的数据行，这时事务T2插入了这条数据，于是T1在插入数据时，又发现这行数据已经存在，但再次查询，却还是查不到数据。
mysql中的四种事务隔离级别如下：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| read uncommitted | √ | √ | √ |
| read committed | × | √ | √ |
| repeatable read | × | × | √ |
| serializable | × | × | × |
查看当前的事务隔离级别通过tx_isolation变量或者transaction_isolation(版本8.0以上使用)；
select @@TRANSACTION_ISOLATION
#设置事务的隔离级别:
set session| global transaction isolation level read uncommitted;
4.3.3.redo log
保证事务的持久性。
重做日志，记录事务提交时，如果数据发生变化，会记录修改信息到redo log buffer中，直接刷新到磁盘中，如果后台线程在将缓冲区里的数据刷新到磁盘中时发生错误，将会通过redo log中的日志进行数据修复。从而保证事务的持久性
redo log是循环写的，并不会持久保留下来
4.3.4.undo log
回滚日志，用于记录数据被修改前的信息，undo log是逻辑日志，redo log是物理日志。
当执行rollback时，从读取undo log中的信息，进行回滚。用于保证事务原子性
4.3.5.MVCC
- 当前读
像select lock in share mode(共享锁),select for update;update; insert; delete(排他锁)这些操作都是一种当前读，读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。 - 快照读
简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，即不加锁的非阻塞读
read committed：每次select都生成一个快照读
repeatable read：开启事务后，第一个select语句才是快照读的地方
serializable：快照读会退化为当前读 - MVCC
即多版本并发控制（Multi-Version Concurrency Control）。主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
隐式字段
- DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
- DB_ROLL_PTR：回滚指针，记录上一个版本，用于配合undo log，指向上一个版本
- DB_ROW_ID：隐藏主键，如果表结构没有指定主键，生成该字段作为隐藏主键。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
