本文深入探讨了MySQL的存储引擎，包括InnoDB和MyISAM的特点，聚焦于存储引擎的区别，如事务支持、锁机制。此外，详细讲解了索引的概念、类型，如B树和B+树，以及聚簇和非聚簇索引。还讨论了事务的ACID特性、事务隔离级别和锁的分类。最后，分享了索引优化技巧和SQL查询优化策略，旨在提升数据库性能。
文章目录
- - -DataBase- - -
1 知识拓扑
如下图，DataBase知识拓扑。
如下图，MySQL知识拓扑。
2 文件与日志
你好
3 关系型数据库
你好
4 NoSQL
你好
5 非关系型数据库
你好
6 MySQL
6.1 存储引擎
6.1.1 数据库连接
1. 连接
① 通信类型：同步、异步
② 连接方式：长连接、短连接
③ 通信协议：TCP/IP、UDP
④ 通信方式：单工、半双工、双工
⑤ MySQL使用同步通信、长连接、TCP/IP协议和半双工通信方式。
2. JDBC
JDBC（Java DataBase Connectivity）是Java和数据库之间的桥梁，是一组用Java语言编写的类和接口。
6.1.2 MySQL结构
如下图，MySQL可分为两层架构，第一层SQLLayer，功能包括权限判断，sql解析，执行计划优化，querycache处理等；第二层存储引擎层，是底层数据存取操作实现部分。
MySQL两层架构中，服务层、存储引擎层中涉及的知识点如下图。其中，CRUD过程中，MySQL服务层中的步骤可分为连接、解析、优化、执行。
6.1.3 存储引擎概念
存储引擎是数据存储、数据索引、数据CRUD、是否支持事务等技术的实现方式，不同存储引擎的特性有一定差别。
6.1.4 存储引擎区别
| 特点 | InnoDB | MyISAM | MEMORY | FEDERATED |
|---|---|---|---|---|
| 支持事务 | 是 | 否 | 否 | 否 |
| 锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | - |
| 支持外键 | 是 | 否 | 否 | - |
| 支持索引 | B树索引 全文索引 集群索引 数据索引 | B树索引 全文索引 | B树索引 哈希索引 数据索引 | B树索引 |
| 其他 | 索引和数据文件不分离 | 索引和数据文件分离 | - | 适用于分布式场景，连接多个MySQL服务器 |
6.2 索引
6.2.1 索引
1. 定义
索引是对数据库中数据排序的一种数据结构，存储在内存或磁盘中，使用索引可快速访问数据库中的指定数据。
2. 优点
① 减少数据查询行数，提高效率；
② 建立唯一索引或者主键索引，保证数据字段的唯一性；
③ 检索时有分组和排序需求时，减少服务器排序的时间。
3. 缺点
① 创建和维护索引存在时间及内存消耗；
② 索引字段过多，数据量过大时，索引占据空间可能比表更大；
③ 更新数据时，也需要维护索引，增加数据维护复杂度。
4. 建立索引的原则
① 离散度高；
② 有序性好；
③ 索引数目不要多。
6.2.2 索引分类及使用
1. 单列索引
一个单列索引只包含单个列，但一个表中可以有多个单列索引。
普通索引：MySQL中基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列中的值是唯一的，但是允许为空值，
主键索引：是一种特殊的唯一索引，不允许有空值。
2. 组合索引
基于多个字段组合创建的索引，使用组合索引时遵循最左匹配原则。
3. 全文索引
在MyISAM引擎上才能使用，只能在CHAR、VARCHAR、TEXT类型字段上使用。
4. 空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。使用SPATIAL关键字，引擎为MyISAM。
6.2.3 B树
1. B树定义
① 树中每个结点最多含有m个孩子( m >= 2 );
② 除根结点和叶子结点外，其他每个结点至少 m/2 个孩子。
③ 若根结点不是叶子，至少2个孩子。
④ 有j个孩子的非叶节点恰好有 j-1 个关键码，关键码按递增次序排序。
6.2.4 B+树
1. B+树定义
① 非叶子节点的值会以最大或最小值出现在其子节点中，即叶子节点包含所有元素；
② 非叶子节点带有索引数据和指向叶子节点的指针，不包含实际数据信息，叶子节点有所有元素信息；
③ 所有叶子节点形成一个有序链表。
2. B+树优势
① B+树磁盘读写代价低，B树存储元素数据，B+只存储索引，可以存储更多节点；
② B+树查询效率稳定，非终结点只是关键字的索引，查找数据必须走到叶子节点；
③ B+树中叶子结点是一个链表，所以B+树在面对范围查询时比B树更加高效。
6.2.5 聚簇索引
1. 定义
聚簇索引，叶子节点存储的是数据。索引类型依赖存储引擎，Innodb使用的是聚簇索引。
2. 优点
① 减少磁盘IO次数，查询数据时，索引节点和数据被同时载入内存；
② 无需维护辅助索引，当出现数据页分裂时，无需更新索引中的数据块指针。
3. Innodb的主键索引和辅助索引
如图为Innodb存储引擎生成的主键索引结构，非叶子节点存储主键，叶子节点存储主键和行数据。
如下图为Innodb存储引擎生成的辅助索引结构。叶子节点存储索引字段和对应的主键值，索引到主键值后，根据主键值再去主键索引中查找对应的数据。 
6.2.6 非聚簇索引
非聚簇索引，叶子节点中存储的是指向数据块指针，MyISAM使用非聚簇索引。
6.2.7 密集索引和稀疏索引
InnoDB中，主键索引(或首个唯一非空索引)是唯一的密集索引，非主键索引为稀疏索引，MyISAM中，索引均为稀疏索引。
6.2.8 索引优化
(1) like语句前导模糊查询不使用索引
select address from t where name like ‘%xx’; - 不使用索引
select address from t where name like ‘xx%’; - 可使用索引
(2) 负向条件查询不使用索引
select name from t where status != 1 and status != 2; - 不使用索引
select name from t where status in (0,3,4); - 优化为in可使用索引
负向条件有!=,<>,not in,not exists,not like等(设status为0 1 2 3 4)。
(3) 范围条件右边的列不使用索引
select name from t where no < 10010 and title=‘xxx’ and date between ‘1986-01-01’ and ‘1986-12-31’;
范围条件有<,<=,>,>=,between等，索引最多用于一个范围列，如上联合索引 (no,title,date)，SQL中no使用索引，title date不适用索引。
(4) 在索引列做任何操作(计算、函数、表达式)不使用索引
select name from t where YEAR(create_time) <= ‘2016’; - 不使用索引
select name from t where create_time<= ‘2016-01-01’; - 可使用索引
select name from order where date < = CURDATE(); - 不使用索引
select name from order where date < = ‘2018-01-2412:00:00’; - 可使用索引
select id from t where substring(name,1,3)=’abc’; - 不使用索引
select id from t where name like ‘abc%’ ; - 可使用索引
select id from t where num/2=100; - 不使用索引
select id from t where num=100*2; - 可使用索引
(5) where中索引列使用参数会导致索引失效
select id from t where num=@num; - 不使用索引
select id from t with(index) where num=@num; - 可以改为强制查询使用索引
SQL在运行时解析局部变量，优化程序是在编译时选择访问计划，但在编译时，变量值是未知的。
(6) 强制类型转换会导致全表扫描
select name from t where phone=13800001234; - 不使用索引
select name from t where phone=‘13800001234’; - 可使用索引
字符串类型不加单引号时索引失效，因为mysql会做类型转换。
(7) is null, is not null无法使用索引，mysql的高版本允许使用索引
select id from t where num is null; - mysql低版本不使用索引
select id from t where num=0; - 可在num设置默认值0，确保num列没有null值
(8) 使用联合索引时，要符合最左原则
建立联合索引，字段数一般少于5个，如果在a b c上建立联合索引，会自动建立a、(a,b)、(a,b,c) 三组索引。
① 建立联合索引时，区分度最高的字段在最左边。
② 存在等号和非等号混合条件时，建立索引应把等号条件的列前置，如where a > ? and b= ?，即使a区分度更高，也把b放在索引最前列。
③ 最左前缀查询时，不是指where条件顺序必须和联合索引一致，但建议保持一致。
④ 假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4，a能用，b能用，c不能用。
6.3 事务
6.3.1 事务概念
数据库的事务是指一组sql语句组成的逻辑处理单元，在这组sql操作中，要么全部执行成功，要么全部执行失败。
6.3.2 事务特性
原子性(Atomic)：指事务的原子性操作，要么同时成功，要么同时失败；
一致性(Consistent)：事务操作前后，状态要一致；
隔离性(Isolated)：多个事务之间，相互隔离；
持久性(Durable)：当事务提交或回滚后，数据的新增、修改会持久化到数据库中。
6.3.3 事务隔离级别
1. 事务隔离级别
| 问题 | 问题描述 |
|---|---|
| 脏读 | 事务a，读取到事务b中没有提交的数据 |
| 不可重复读(虚读) | 事务a中，针对某条数据，两次读到的结果不同 |
| 幻读 | 事务a按相同条件检索数据，事务b添加满足该条件的数据，则事务a两次检索到的数据记录数不同 |
| 隔离级别 | 备注 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| Read uncommitted/0 | 未提交读 | 可能 | 可能 | 可能 |
| Read committed/1 | 已提交读（Oracle默认） | 否 | 可能 | 可能 |
| Repeatable read/2 | 可重复读（MySQL默认） | 否 | 否 | 可能 |
| Serializable/3 | 串行化 | 否 | 否 | 否 |
2. 未提交读执行过程图
3. 已提交读执行过程图
6.4 锁
6.4.1 锁分类
1. 按粒度划分
| 锁 | 优缺点 | 支持引擎 |
|---|---|---|
| 表锁 | 开销小，加锁快，不出现死锁，粒度大，发生锁冲突概率高，并发度低 | MyISAM、MEMORY、InnoDB |
| 行锁 | 开销大，加锁慢，会出现死锁，粒度小，发生锁冲突概率低，并发度高 | InnoDB |
2. 按类型划分
| 锁 | 优缺点 |
|---|---|
| 共享锁/读锁 | 多个事务对同一数据可共享一把锁，均可访问数据，只能读不能修改。 |
| 排他锁/写锁 | 事务A获取某数据行的排他锁，则其他事务不能获取该行的其他锁，包括共享锁和排他锁，获取排他锁的事务是可以对数据就行读取和修改 |
3. MySQL调度策略
① 写入请求应按其到达的次序进行处理；
② 写入具有比读取更高的优先权。
6.4.2 MyISAM的锁
1. 支持表锁
① Select操作加共享锁，Update、Delete、Insert操作加排它锁；
② 读写之间、写写之间是串行的，读读之间是并行的；
③ 由于表锁粒度大，读写是串行的，若更新操作较多，可能出现严重的锁等待。
2. 并发锁
MyISAM的系统变量concurrent_insert（默认设置为1）。
6.4.3 InnoDB的锁
1. 支持行锁
| 行锁分类 | 操作说明 |
|---|---|
| 记录锁/Record lock | 对索引项加锁，即锁定一条记录 |
| 间隙锁/Gap lock | 对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身 |
| 范围锁/Next-key Lock | 锁定一个范围的记录并包含记录本身（上面两者的结合） |
2. 行锁导致的死锁
(1) 死锁原理
MySQL中，行锁不是锁数据，而是锁索引；索引分为主键索引和非主键索引，若sql语句操作了主键索引，MySQL会锁定该主键索引；若语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在update、delete操作时，MySQL不仅锁定where条件扫描的索引记录，同时会锁定相邻的键值，即next-key locking。
(2) 死锁原因
当两个事务同时执行，事务a锁住了主键索引，在等待其他相关索引；事务b锁定了非主键索引，在等待主键索引，则发生死锁。
(3) 降低死锁
① 不同程序并发存取多个表，尽量以相同的顺序访问表；
② 一个事务，尽可能一次锁定需要的所有资源；
③ 对于容易产生死锁的业务，可使用粒度较大的锁，如表锁；
④ 若程序以批量方式处理数据，可事先对数据排序，保证每个线程按固定的顺序处理记录。
3. 行锁的间隙锁
用法：select * from 表名 where 字段名>** for update；使用范围条件(所示方法)检索数据时，InnoDB除了给索引记录加锁，还会给不存在的记录（间隙）加锁。
目的：防止幻读，避免其他事务插入数据。
6.4.4 悲观锁&乐观锁
1. 悲观锁
(1) 悲观锁流程及使用场景
① 修改记录前，先加排他锁，加锁失败则等待或者抛出异常，加锁成功则进行修改，事务完成后解锁；
② 行锁、表锁、读锁、写锁都属于悲观锁。
③ 悲观并发控制主要用于数据争用激烈的环境；
(2) 优缺点
① 优点 悲观并发控制使用“先取锁再访问”的策略，可保证数据处理的安全性；
② 缺点 (a)效率低，处理加锁有额外开销，增加死锁概率；
③ 缺点 (b)只读事务不涉及数据修改，无需加锁。
2. 乐观锁
如果系统并发量非常大，悲观锁会带来非常大的性能问题，可选择使用乐观锁，乐观锁的实现方法有版本控制机制和时间戳。
(1) 版本控制机制
标志：每行数据增加version字段，每次更新数据对应版本号+1，
原理：读出数据，将版本号一同读出，之后更新，版本号+1，提交数据版本号大于数据库当前版本号，则予以更新，否则认为是过期数据，重新读取数据。
(2) 使用时间戳实现
标志：每行数据增加time字段；
原理：读出数据，将时间戳一同读出，之后更新，提交数据时间戳等于数据库当前时间戳，则予以更新，否则认为是过期数据，重新读取数据。
6.5 MVCC
6.5.1 定义及工作原理
1. 定义
Multi-Version Concurrency Control ，一种多版本并发控制协议，只在数据库引擎为InnoDB、隔离级别为RC、RR的情况下存在。MVCC是通过版本号和快照/一致性视图，实现了事务隔离性，但只在事务级别为已提交读和可重复读时有效。MVCC最大的好处是：读不加锁，读写不冲突。
2. 工作原理
InnoDB引擎中，每行数据都有三个隐藏字段，唯一行号(DB_ROW_ID字段)、事务ID(DB_TRX_ID字段)和回滚指针(DB_ROLL_PTR字段)。开启事务时，会生成一个事务版本号，被操作的数据会生成新的数据行，但在提交前对其他事务不可见。数据更新之后，事务提交成功后，将该事务版本号赋值给数据行的创建版本号，图解如下。
6.5.2 快照
1. 快照创建策略
| 隔离级别 | 创建快照策略 |
|---|---|
| read committed级别 | 事务开启，每个select操作，都会创建快照(Read View)，保持最新快照 |
| repeatable read级别 | 事务开启，首个select操作之后，创建快照(Read View)，之后均使用该快照 |
2. 快照工作原理
如下图，版本1、版本2并非实际物理存在的，而图中的U1和U2实际就是undo log，这v1和v2版本是根据当前v3和undo log计算出来的。
快照遵循原则如下：自己更新的数据总是可见，其他事务更新的数据有三种情况：版本未提交的，都不可见；版本已经提交，但是是在创建视图之后提交的也不可见；版本已经提交，但是是在创建视图之前提交的是可见的。
6.6 执行计划explain
6.6.1 执行计划explain
explain执行计划包含信息如下图，其中，比较重要的字段为包含id、type、key、rows。
6.6.2 参数详解
1. id
select查询的序列号，表示执行select子句或操作表的顺序，包括三种情况。
① id相同，执行顺序由上至下；
② id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行；
③ id相同又不同(两种情况同时存在)，id如果相同，则为一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行 。
2. select_type
查询类型，用于区分普通查询、联合查询、子查询等复杂的查询。其值包含SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。
3. type
访问类型，sql查询优化的重要指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，sql查询至少达到range级别，最好能达到ref。
| type值类型 | 意义 |
|---|---|
| system | 表中只有一行记录匹配，是const类型特例 |
| const | 通过索引一次即可查询到数据，const用于比较primary key或者unique索引 |
| eq_ref | 唯一性索引扫描，表中只有一条记录匹配，常见于主键或唯一索引扫描 |
| ref | 非唯一性索引扫描，返回匹配某个单独值的所有行 |
| range | 检索给定范围的行，使用一个索引来选择行，一般是在where语句中出现bettween、<、>、in等的查询。 |
| index | Full Index Scan，遍历索引树(Index与ALL都是读全表，但index是从索引中读取，而ALL是从硬盘读取) |
| ALL | Full Table Scan，遍历全表以找到匹配的行 |
4. possible_keys
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用>
5. key
实际使用的索引，如果为NULL，则没有使用索引；查询中如果使用了覆盖索引，则该索引仅出现在key列表中 。
6. key_len
表示索引中使用的字节数，查询中使用的索引的长度(最大可能长度)，并非实际使用长度，理论上长度越短越好，key_len是根据表定义计算而得的，不是通过表内检索出的。
7. ref
显示索引的那一列被使用，如果可能，是一个常量const。
8. rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
6.7 优化技术
6.7.1 优化技术总结
① 表设计合理化(符合“三范式”，兼顾“反范式”)；
② 适当添加索引(包括普通索引、主键索引、唯一索引、全文索引)；
③ SQL语句优化(包括避免全表扫描、避免嵌套子查询等）；
④ 分表技术(水平分割、垂直分割)；
⑤ 读写分离（其中写包括update/delete/add）；
⑥ 存储过程（模块化编程，可提高速度，但迁移性差，服务器压力也会逐渐增大）；
⑦ mysql参数调优(修改my.ini配置文件的参数)；
⑧ mysql服务器硬件升级；
⑨ 定时清除不需要的数据，定时进行碎片整理(特别是使用MyISAM)。
6.7.2 表设计合理化
1. 第一范式
每列属性都是不可再分，确保每列原子性；
两列属性相同或相似，尽量合并属性一样的列，确保不产生冗余数据。
2. 第二范式
表中每列都只和主键相关，即一张表中只保存一种数据。
3. 第三范式
表中每列只能依赖于主键，非主键依赖数据用外键做关联。
4. 反范式
若业务所涉及的表非常多，经常会有多表联查，其查询效率就会大打折扣，可考虑使用“反范式”。增加必要的，有效的冗余字段，用空间来换取时间,在查询时减少或者是避免过多表之间的联查。
6.7.3 Sql语句优化
1. Sql执行效率排查方法
① SHOW [ SESSION|GLOBAL ] STATUS指令；
② 慢查询定位与记录。
2. 常用Sql优化方法
① group by 分组查询涉及到排序，使用order by null禁止排序；
② 使用左/右连接替代多表联查，因为MySQL使用JOIN不需要在内存中创建临时表；
③ 含or的查询语句，每个条件列都尽量使用索引，没有索引可考虑增加索引；
④ 选择合适的存储引擎，MyISAM引擎不支持事务，其查询和添加效率高，INNODB引擎支持事务，其数据一致性较好。
6.7.4 适当添加索引
索引知识，具体见6.2章节，索引技术可大大提高查询速度，其代价是降低插入、更新、删除的速度。
6.7.5 分库分表技术
1. 垂直分表
将一张表的若干列，提取成子表。如下图，商品描述信息访问频次低，占用存储空间大，商品基本信息访问频次高，占用存储空间小。采用垂直分表规则，将商品基本信息存放在表1中，访问商品描述信息放在表2中。
2. 水平分表
将一张表分割成多个相同的子表，在访问时，根据事先定义好的规则操作对应的子表。如下图，商品信息及商品描述被分成了两套表，如果商品ID为双数，将此操作映射至表1，如果商品ID为单数，将操作映射至表2。
3. 垂直分库
如下图，将SELLER_DB(卖家库)，分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)，并把这两个库分散到不同服务器。
4. 水平分库
如下图，将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中。
5. 其他分表分库技术
① mysql集群，其作用和分表相同，集群可将任务分担到多台数据库，可进行读写分离，减少读写压力。
② 自定义规则分表/库，按照业务规则来分解为多个表/库，常用规则包括Range(范围)、Hash(哈希)等，也可自定义规则。
6.7.6 读写分离
1. 读写分离原理
读写分离，即在主服务器上增删改，在从服务器上读，同时将数据从主服务器同步到从服务器。实现了数据备份、优化了数据库性能。
2. 常用分离技术
① 基于程序代码内部实现；
② 基于中间代理层实现，其结构如下图所示，流行的代理中间件有mysql_proxy、Atlas、Amoeba。
7 集群
你好
扫一扫
592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
