MySQL高级篇

SQL执行经过的组件

1. 连接器：mysql服务器之外的客户端连接

2. 连接池：提供多个用于客户端交互的线程

3. SQL接口：接收 SQL指令，返回查询结果

4. 查询缓存：以key-value方式查询结果（MySQL8中删除了查询缓存）

5. 解析式：语法和语义解析，生成语法树

6. 优化器：核心组件，对SQL进行优化

7. 插件式存储引擎：与底层文件系统进行交互

8. 文件系统和日志

9. 查询缓存：以key-value方式存储结果（MySQL8中删除了查询缓存）

InnoDB

• MySQL5.5之后的默认引擎

• 支持外键、事务、行锁、自适应哈希、B树索引、集群索引、数据索引

• 空间和内存使用率都比较高

• 适用于大并发、事务、更大资源

• InnoDB索引缓存要存储数据和索引，对内存要求较高；MyISAM索引缓存只存储索引，不存储数据，效率较高

• .frm存储表结构，.ibd存储数据和索引

• 除非特别原因，优先选择InnoDB

MyISAM

• MySQL5.5之前的默认索引

• 支持表锁、B树索引；不支持事务、行级锁、外键、集群索引、数据索引，崩溃后无法恢复

• 空间和内存使用率都比较低，节省资源，消耗少

• 对于事务要求不高、以查询和插入为主、没有大并发的简单业务，MyISAM执行速度比InnoDB快

• .frm存储表结构，.MYD存储数据，.MYI存储索引

Memory

• 采用内存存储数据，比MyISAM快一倍

• 数据易丢失，生命周期短

• 适用于对于数据小且经常访问的数据的表

Archive

• 用于数据存档

Blackhole

• 丢弃写操作、读操作，会返回空内容

CSV

• 存储数据时，以逗号分隔各个数据项

Merge

• 管理多个MyISAM表构成表的集合

NDB

• mysql集群专用的存储引擎

查询缓存

• 提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果

• MySQL 中的查询缓存，不是缓存查询计划，而是对应的查询结果。因为命中条件苛刻（查询语句多一个空格都不行），而且只要数据表发生变化，查询缓存就会失效，因此命中率极低

缓冲池

• InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中,也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，如果有请求再次访问该页面时，就可省去磁盘IO的开销

缓冲池读取数据

• 缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取

InnoDB存储方式

• 索引和数据都是保存在页结构中，一个页的大小为16KB，因此一次最少从磁盘中读取16KB的数据到内存中

• 一个页中保存多条记录（行），不论读取一行还是多行，都是将这些行所在的页进行加载

• 数据库管理存储空间的基本单位是页，数据库I/O操作的最小单位也是页

• 页和页之间通过双向链表连接，每个页中的记录按照主键值从小到大的顺序组成一个单向链表

• 通过主键查找记录时，在页目录中使用二分法快速定位到对应的页，再遍历对应页中的记录即可快速找到指定的记录

• 区是比页大一级的存储结构，一个区会分配64个连续的页，因此一个区大小为 16KB * 64 = 1MB

• 段由一个区或多个区组成，段中不要求区和区之间是连续的；段是数据库中的分配单位

• 表空间由一个或多个段组成

页的内部结构

• 文件头（38字节）：描述页的信息

• 页头（56字节）：页的状态信息

• 最大最小记录（26字节）：最大和最小记录，虚拟的行记录

• 用户记录（可变）：存储行记录内容

• 空闲空间（可变）：页中还没有被使用的空间

• 页目录（可变）：存储用户记录的相对位置

• 文件尾（8字节）：校验页是否完整

索引

• 高效获取数据的数据结构或排好序的快速查找的数据结构。有普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引

• 每针对某个或某几个字段创建索引，就创建了对这个字段或几个字段进行排序的B+树

• 索引分类：

  1. 按功能逻辑分：普通索引、唯一性索引、主键索引、全文索引

     • 普通索引：没有任何限制条件，只用于提高查询效率

     • 唯一性索引：用unique设置索引为唯一性索引，一张表可有多个

     • 主键索引：非空和唯一，一张表只有一个

  2. 按物理实现方式分：聚簇索引、非聚簇索引

  3. 按作用字段分：单列索引、联合索引

     • 单列索引：作用在一个字段上的索引，一张表可有多个

     • 联合索引：作用在多个字段上的索引

聚簇索引

• InnoDB中，聚簇索引默认由主键创建；

• 若没有主键，自动选择表中字段为unique的字段创建聚簇索引；

• 若表中没有unique的字段，会自动提供一个隐藏的主键（row_id）来创建聚簇索引

全文索引

• 使用full text设置索引为全文索引，用于数据量较大的字符串类型查找

空间索引

• 使用spatial设置索引为空间索引，空间索引只能建立在空间数据类型上

覆盖索引

• 非聚簇索引中包含了索引列和主键，此索引包含了满足查询结果的数据，就不需要回表

• 索引列和主键包含了查询语句中所有的列

• 查询语句中出现的列都在联合索引中，语句可以比联合索引列少，不能多

• 优点：

  • 避免InnoDB的表进行索引二次查询(回表)

  • 把随机IO变成顺序IO，因为回表后的顺序是随机的

• 缺点：

  • 写查询语句时，不清楚需要使用到的列，可能经常对联合索引进行创建，消耗资源

索引条件下推

• ICP（index condition pushdown），默认开启

• 在联合索引的回表情况下，优化器发现位于where靠后的条件先判断比靠前的条件效率更高，则优化器先进行靠后的条件判断

• 有回表，所以ICP用于二级索引

• 使用到覆盖索引时，就用不到ICP，因为覆盖索引没有回表

索引的优点

• 降低数据库的IO成本

• 创建唯一索引，可保证数据库表中每行数据的唯一性

• 加速表和表之间的连接

• 减少查询中分组和排序的时间

索引的缺点

• 创建、维护索引需要耗费时间

• 占据磁盘空间

• 对表中的数据进行增删改时，索引也要动态维护，因此降低数据的维护速度

索引的创建

1. 隐式创建索引：在声明有主键约束、唯一性约束、外键约束的字段上，会自动添加相关索引；如果都没有，会隐式创建一个字段row_id为主键索引

2. 显式创建索引：mysql8.0在字段后可加上asc或desc，不加默认为asc；mysql5.7只支持asc

   • 创建表时创建索引：

     • 普通索引：最后一行加上 index 索引名(字段)

     • 唯一性索引：最后一行加上 unique index 索引名(字段)

     • 主键索引：在字段后加上primary key；删除主键索引：alter table 表名 drop primary key

     • 单列索引：上述都是单列索引，作用在一个字段上

     • 联合索引：最后一行加上index 索引名(字段1,字段2)

   • 在表创建好的情况下创建索引：

     • alter table：

       1. alter table 表名 add index 索引名(字段)：创建普通索引

       2. alter table 表名 add unique 索引名 (字段)：创建唯一索引

       3. alter table 表名 add index 索引名(字段1,字段2)：创建联合索引

     • create index：

       1. create index 索引名 on 表名(字段)：创建普通索引

       2. create unique index 索引名 on 表名(字段)：创建唯一索引

       3. create index 索引名 on 表名(字段1,字段2)：创建联合索引

索引的查看

• show create table 表名

• show index from 表名

索引的删除

• alter table 表名 drop index 索引名

• drop index 索引名 on 表名 注意：添加自增的约束字段的唯一性索引不能被删除

单表索引数量不要超过6个，原因：

• 索引需要占用磁盘空间

• 增删改会影响增删改的性能，增删改的同时，索引也需要维护

• 优化器在选择如何优化查询时，会根据统一信息，对每个可用到的索引进行评估，以生成一个最好的执行计划，如果有多个索引都可用于查询，会增加优化器生成执行计划时间

适合创建索引的情况

• 字段数值有唯一性限制，即使是组合字段也必须建成唯一性索引

• 频繁作为where查询条件的字段

• 经常group by和order by的列，如果查询语句中用到了多个字段，那么查询前应该创建这多个字段的联合索引，要按照联合索引中的顺序进行使用

• update、delete的where条件列

• distinct字段

• 多表join连接操作时：

  • 连接表的数量不要超过3张

  • 对where条件创建索引

  • 对用于连接的字段创建索引

• 使用数据范围类型小的列创建索引，比如tinyint、mediumint、int、bigint，能用tinyint就用tinyint，尽量让索引列使用较小的类型

• 使用字符串前缀创建索引，在varchar字段上建立索引时，必须指定索引长度，没必要对全文字段建立索引，根据实际文本决定索引长度

• 散列性高的列创建索引，就相当于对字段去重后数据很多的时候

• 使用最频繁的列放在联合索引的左侧

• 在多个字段都要创建索引情况下，联合索引优于单值索引

不适合创建索引的情况

• 在where中使用不到的字段，不设置索引

• 数据量小的表，不设置索引

• 有大量重复数据的列，不设置索引

• 经常更新的表或字段，不创建过多的索引

• 不使用无序的值作为索引

• 删除不再使用或使用很少的索引

• 不要定义冗余或重复的索引

SQL查询效率

• 缓冲池＞内存＞磁盘

慢查询日志

• 用来记录响应时间超过阈值的语句，指运行时间超过long_query_time值的SQL，会被记录到慢查询日志中

• long_query_time默认值为10s，超过10s以上的SQL就超出阈值

• 慢查询日志默认关闭，若不是调优需要，不开启慢查询日志

• 关闭慢查询日志：set global slow_query_log=off

查询优化器explain

1. id：在查询语句中每个select语句都对应一个唯一的id。

   • 如果id相同，则是一组，从上往下执行；

   • id值越大，越先执行；

   • 一个id表示执行一次

2. select_type：select对应的查询的类型

   • simple：不包含union、子查询、连接查询

  - primary：包含union、union all、子查询的最左边的查询是primary

  - union：包含union、union all的查询除了最左边的查询，其他的为union

  - union result：对于union的查询，会产生临时表，为union result

  - subquery：包含子查询的语句不能转为多表连接的形式，并且是不相关子查询，而且优化器采用子查询物化的方案执行该子查询时，该子查询第一个select为subquery

1. table：表名

2. partition：分区表中的命中情况，非分区表为null。一般情况下都为null

3. type：对单表的访问方法（从上到下，从好到坏）

   • system：表中只有一条记录，并且使用的引擎的统计数是精确的，比如MyISAM、Memory

  - const：根据主键或唯一的二级索引列与常数进行等值匹配

  - eq_ref：连接查询时，被驱动表通过主键或唯一的二级索引列等值匹配的方式进行访问，则被驱动表的访问方法为eq_ref

  - ref：通过普通的二级索引列与常量进行等值匹配查询

  - ref_or_null：对普通二级索引列进行等值匹配查询，该索引列的值可以为null

  - index_merge：用or连接的两个单列索引列进行等值匹配

  - unique_subquery：对于包含"in"的子查询语句，优化器决定将"in"子查询转换为"exists"子查询，而且子查询可使用到主键进行等值匹配

  - range：使用索引获取某些范围区间的记录

  - index：可使用索引覆盖，到需要扫描全部的索引记录

  - all：使用全表扫描select * from 表

1. possible_keys：可能用到的索引

2. key：实际用到的索引

3. key_len：实际使用索引列的长度，同一个索引中，数值越大越好

4. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

5. rows：预估读取的记录条数，越小越好

6. filtered：某个表经过搜索条件过滤后剩余记录数的百分比，越大越好

7. Extra：额外信息

   • using index：查询使用到索引，效果很好

   - using temporary：查询使用到了临时表，建立和维护临时表需要很大成本，最好用索引替代临时表

   - using filesort：查询使用到了文件排序，尽量避免

• explain的不足：

  • 不会考虑各种cache

  • 不显示执行查询时的优化工作

  • 不显示触发器、存储过程的信息或用户自定义函数对查询的影响情况

  • 部分统计是估算的，并非精确值

分析优化器执行计划：trace

监控分析视图：sys schema

索引失效

• 全值匹配，select * from …

• 最佳左前缀法则。对于联合索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个索引列，索引列后面的字段会失效

• 主键插入顺序。如果主键不是依次递增，当新增数据时需要在原有的表的中间插入，会导致页分裂

• 运算、使用函数会使索引失效

• 类型转换（自动或手动）导致索引失效

• 使用联合索引时，某个索引列若用到了范围条件，则它右边的索引列会失效。创建联合索引时，应该把需要用到范围条件的列写在联合索引的最后一个

• 不等于（!=或<>）会使索引失效

• is null可使用索引，is not null不能使用索引

• like以通配符%开头的索引失效

• or前后出现非索引的列，索引失效

• 不同的字符集进行比较前会自动转换，导致索引失效。因此需要设置数据库和表的字符集的统一

SQL优化

关联查询优化

• 对于内连接，查询优化器可以决定谁作为驱动表，谁作为被驱动表

• 对于内连接，如果表的连接条件只有一个字段有索引，则有索引的字段所在的表会作为被驱动表

• 对于内连接，在连接两个表的连接条件都存在索引的情况下，会选择小表作为去驱动表（小表驱动大表，更精确地说是小结果集驱动大结果集，其实是减少外层循环次数）

join连接原理

• 对于外连接，select * from A left join B on...，A一般是驱动表，但有时候也未必，其实是优化器把外连接转换成内连接，再优化

• 对于内连接，select * from A join B on...，A不一定是驱动表，优化器自动将小结果集的表作为驱动表

• INLJ ＞ BNLJ ＞ SNLJ

  • index nexted loop join索引嵌套循环连接

  • block nexted loop join块嵌套循环连接

  • simple nexted loop join普通嵌套循环连接

• 小结果集驱动大结果集，其实就是减少外层循环的数据量

• 为被驱动表匹配的条件加上索引，减少内层表循环匹配次数

• 增大join buffer size大小，一次缓存的数据越多，内层包扫描次数越少

• 减少驱动表不必要的字段查询

子查询优化

• 执行子查询时，会建立临时表，而且临时表不能建立索引，查询完毕后再撤销临时表，会消耗更多资源，产生慢查询

• 子查询会创建中间表，消耗更多资源，因此能用多表连接的就不用子查询

排序优化

• 两种排序方式

  1. index，索引保证数据有序性，不需要再排序，效率高

  2. filesort，在内存中排序，耗费资源多，若排序结果大会产生临时文件I/O到磁盘进行排序，效率低

• 在where子句中使用索引避免全表扫描，在order by子句使用索引避免filesort排序

• where和order by后是相同列就使用单列索引，不同就使用联合索引

• 无法使用index时，需要对filesort方式调优

• 数据量很大进行order by时，不加limit会使索引失效。使用索引时不加limit会回表，此时不用索引反而比使用索引好，因此优化器不会使用索引；加上limit使数据量不大，使用索引回表不会消耗很多资源

• order by时使用的顺序和索引列顺序不一致，导致索引失效

• 无法使用索引列时，增大max_length_for sort_data和sort_buffrt_size的参数值

分组优化

• 先排序后分组

• 能用where的就不用having

• 减少使用order by

分页查询优化

• select * from 表 limit 1000000 10，可优化成创建主键id索引，再select * from 表 where id ＞ 1000000 limit 10

select *

• 尽量使用明确字段，解析的时候会将 * 转换成表中所有字段，耗费资源和时间

• 无法使用覆盖索引

limit

• 针对全表扫描的SQL，若确定结果集只有一条，加上limit 1 可提高查询效率

• 若对字段建立唯一索引，可通过索引进行查询，不需要全表扫描，就不需要加limit 1

多使用commit

• commit会释放资源

  1. 回滚段上用于恢复数据的信息

  2. 被程序语句获得的锁

  3. redo/undo log buffer中的空间

  4. 管理上述三种资源中的内容花费

exists和in的区别

• 如果表A小，使用exists：select * from A where exists (select c from B where B.c=A.c)

• 如果表A大，使用in：select * from A where c in (select c from B)

count(*)、count(1)和count(字段)

• count(*)和count(1)一样

  • InnoDB中时间复杂度都为O(n)

  • MyISAM中时间复杂度都为O(1)，因为MyISAM中有字段专门存储了数据的个数

• InnoDB中

  • 若用count(字段)进行计数，尽量使用二级索引，因为聚簇索引包含的信息多

  • 若用count(*)或count(1)，系统会自动采用占用空间更小的二级索引进行计数，若有多个二级索引，会使用key_len小的二级索引，若没有二级索引，才会使用聚簇索引

淘宝数据库主键的设计

• 不使用自增id：①可靠性不高；②安全性不高；③性能差；④交互多；⑤局部唯一性

• 不使用业务字段做主键，比如会员卡号；电话和身份证涉及隐私，也不能作为主键

• 淘宝订单主键ID=时间+去重字段+用户ID后6位尾号

• 非核心业务，可设置表的ID主键自增ID；核心业务，主键设计至少是全局唯一且单增

• UUID不适合用于主键：

  • 全局唯一、占用36字节、无序

  • 改造UUID：MySQL8支持时间高低位互换以及存储空间变为16字节，此时UUID全局唯一、单增、占用空间小，可用于主键

范式(Normal form)

• 关系型数据库中，关于数据表设计的基本原则、规则称为范式

• 高级别的范式一定符合低级别的范式要求

• 范式级别越高，冗余度越低，一般情况下满足第三范式即可

• 范式中的术语：

  1. 超键：能唯一标识的属性集

  2. 候选键/码：若超键不包括多余属性，则此超键是候选键

  3. 主键/主码：候选键中的一个可作为主键

  4. 外键：该表中的非主键属性集是另一个表的主键，此属性集是外键

  5. 主属性：包含在候选键中的属性

  6. 非主属性：不包含在候选键中的属性

• 范式的优点：消除数据的冗余

• 范式的缺点：降低查询效率，关联多张表，可能还会使索引失效

第一范式

• 确保数据表中每个字段的值必须具有原子性，也就是每个字段的值不可拆分

第二范式

• 满足第一范式基础上，表里每条记录都是可唯一标识的，并且所有非主键字段都必须完全依赖主键，不能只依赖主键的一部分

• 一张表就是一个独立的对象

第三范式

• 满足第二范式基础上，表中所有非主键字段不能依赖其他非主键字段，只能依赖主键字段

• 第三范式在性能、扩展性、数据性达到最好的平衡

巴斯范式

• 在第三范式基础上，只有一个候选键，或每个候选键都是单属性

第四范式

• 在巴斯范式基础上，消除非平凡非函数依赖的多值依赖，即把同一表内的多对多关系删除

  • 多值依赖：属性间一对多关系

  • 函数依赖：单值依赖，不能表达属性间一对多关系

  • 平凡多值依赖：全集U＝A+B，一个A可对应多个B

  • 非平凡多值依赖：全集U＝A+B+C，一个A可对应多个B，也可对应多个C，B与C相互独立

第五范式/完美范式

• 满足第四范式基础上，消除不是由候选键包含的连接依赖

域键范式

• 终极范式，理论研究中…

反范式化

• 业务优先原则，再减少冗余

• 数据量比较大，利用反范式化：通过在数据表中增加冗余字段提高数据库的读性能

• 缺点：

  • 存储空间表大

  • 一个表中字段修改，另一个表中冗余的字段也需要修改

  • 数据量小情况下使用反范式会使数据库设计更复杂

ER(实体关系)模型

• Entity Relationship

• 在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮我们理清业务逻辑，从而设计优秀的数据库

• 三个主要概念：

  • 实体：数据对象（表），矩形表示

    • 强实体：不依赖其他实体的实体

    • 弱实体：依赖其他实体的实体

  • 属性：实体的属性（字段），椭圆形表示

  • 关系：实体和实体的关系（表和表的关系），菱形表示

表设计原则

• 表个数越少越好

• 表中字段越少越少

• 表中联合主键字段越少越好

• 使用主键和外键（应用层实现）越多越好，即表和表之间的关系越多越好，可减少数据冗余，保证表的独立性，提升表之间的关联使用率

数据库/表编写建议

• 英文小写，单词间用_隔开

• 创建数据库必须指定字符集为utf8

• 创建表时必须加上主键，且主键为id，类型为int或bigint，且为auto_increment，建议使用unsigned无符号型

• 创建表时必须指定存储引擎

• 创建表的字段时必须加上comment

• 表中字段尽量都是not null：①如果为null值，还有一个专门空间存储null值；②进行比较和计算时，省去对null值的字段判断是否为空的开销

• 禁止在数据库中存储图片或文件大的二进制数据

• 不同表存储相同数据的列名和列类型必须一致

• 主键值禁止更新

• 单个表索引个数不超过6个

• 多建立联合索引

• 被驱动表应该有索引

• 禁止使用select *

• insert into语句要指定具体字段

• 减少使用order by、group by和distinct

• 对单表的alter操作合并成一次

• 事务里的SQL不超过5个

数据库服务器调优

• 加上redis作为缓存

• 配置较大的内存，以此增加缓冲区容量，使数据在内存中停留时间更长，减少磁盘IO

• 配置高速磁盘系统

• 合理分配磁盘IO

• 配置多处理器，MySQL是多线程数据库，多处理器可同时执行多个线程

数据库结构调优

• 拆分表，冷热数据分离

• 增加中间表

• 增加冗余字段来提高查询效率

• 优先选择符合存储需要最小的数据类型，对于非负类型的数据，加上unsigned可多出一倍的存储空间

• 即可使用文本类型又可使用整数类型的字段，使用整数类型，占用空间小

• 避免使用text、blob这样大的数据类型

• 避免使用enum类型

• 使用timestamp存储时间

• 用decimal代替float和double，float和double会丢失精度

优化插入记录的速度

• MyISAM：禁用索引，禁用唯一性检查，使用一个insert into插入多条记录

• InnoDB：禁用唯一性检查，禁用外键检查，禁止自动提交

事务

• 事务指一组逻辑单元，使数据从一种状态变换为另一种状态。事务是针对DML语言

• 事务的状态

  • 活动的：事务对应的数据库操作正在执行

  • 部分提交的：由于操作在内存中执行，事务执行完后只是对内存数据进行修改，还没有把数据刷新到磁盘

  • 提交的：在部分提交状态的事务将修改的数据同步到磁盘

  • 失败的：当事务处在活动或部分提交的状态下，遇到某些错误而无法继续执行

  • 中止的：当事务执行一部分后变为失败状态，回滚执行完毕时就处于中止状态

• 事务过程

  1. 开启事务

  2. DML操作

  3. 结束的状态：提交commit或中止rollback

• 显式事务

  • 开启事务：start transaction或者begin

  • 最后使用commit或rollback结束事务，如果此时事务自动提交开启的，事务自动提交不会生效

• 隐式事务

  • set autocommit=true/false，默认为true，因此每执行一条增删改语句都是一个事务，执行每条语句都会自动提交

  • 最后使用commit或rollback结束事务

• 保存点

  • 事务执行过程中可设置一个保存点：savepoint s1

  • 回滚到保存点：rollback to s1

事务的四个特性

1. 原子性atomicity：事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚

2. 一致性consistency：事务执行前后，数据从一个合法状态变换为另一个合法状态

3. 隔离性isolation：一个事务的执行不能被其他事务干扰

4. 持久性durability：事务一旦被提交，则对数据库中的数据的改变是永久的

数据并发出现的问题

• 脏写：事务A修改并提交了事务B修改但未提交的数据

• 脏读：事务A读取了事务B修改但没有提交的数据。若事务B回滚，则事务A读取的数据是临时且无效的

• 不可重复读：事务A读取一个字段，事务B修改并提交了该字段，之后事务A又读取这个字段，此时事务A读取的数据和开始读取的数据不一致

• 幻读：事务A从一个表读取一个字段，事务B在该表中新增了几行，之后事务A再读取这个表，就多出了几行（多出的记录称为幻影记录）

四种隔离级别

• 数据量大，并发可以提高效率，但会出现上述并发出现的四种问题，因此需要平衡上述问题

• 由于脏写的问题太严重，四种隔离级别都解决了脏写

• read uncommitted读未提交：可能会出现脏读、不可重复读、幻读

• read committed读已提交：可能会出现不可重复读、幻读(Oracle默认)

• repeatable read可重复读：可能会出现幻读(MySQL默认)

• serializable串行化：都不会出现，但是条件要求的越多，并发性越低

事务日志和锁

• 持久性由事务的redo日志保证

• 原子性、一致性由undo日志保证

• 隔离性由锁机制实现

日志

redo log(重做日志)

• 提供再写入操作，恢复提交事务修改的页操作，保证事务持久性

• 记录的是物理级别上的页操作

• 对内存中的数据进行修改后，先保存在日志中，再把日志中的数据保存到磁盘。这样可以保证服务器宕机后通过redo日志进行数据恢复，保持事物持久性

• redo日志降低刷盘频率，redo日志占用空间非常小

• redo日志是顺序写入磁盘，效率比随机IO快

• 事务执行过程中，redo日志一直在记录

• redo log结构

  • 重做日志缓冲(redo log buffer)，保存在内存中，易丢失

  • 重做日志文件(redo log file)，保存在硬盘中，持久性的

• redo log执行流程

  1. 将原始数据从磁盘中读到内存中，修改数据的内存拷贝

  2. 生成redo log并写入redo log buffer，记录的是数据被修改后的值

  3. 事务commit时，将redo log buffer中的内容刷新到redo log file，对redo log file采用追加写的方式

  4. 定期将内存中修改的数据刷新到磁盘

undo log(回滚日志)

• 回滚记录到某个特定版本，保证事务的原子性和一致性。记录的是逻辑上的页操作

• 回滚数据，并非将数据库物理地恢复到执行语句或事务之前，而是将数据库逻辑地恢复到之前。执行增删改之前，把数据记录在undo log中

• MVCC，InnoDB存储引擎中MVCC是通过undo来完成。当读取一天记录时，若该记录被其他事务占用，当前事务可通过undo读取之前的行版本信息，实现非锁定读取

• 每个事务只会使用一个回滚段，一个回滚段在同一时刻会服务于多个事务；回滚段存在于undo表空间，数据库中可存在多个undo表空间，同一时刻只能使用一个undo表空间

• 一个事务开始时，会制定一个回滚段，事务进行中数据被修改时，原始数据会被复制到回滚段

其他日志

• bin log（二进制日志）

  • 记录所有更改数据的语句，用于服务器遇到故障时数据的无损恢复，还可用于主从服务器间的数据同步

  • 默认开启，文件为bin log（一个二进制文件），每次服务器重启都会创建一个bin log文件

• 慢查询日志：记录所有执行时间超过long_query_time的查询

• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令。默认关闭，文件为 库名.log

• 错误日志：记录MySQL服务启动、运行、停止时出现的问题。默认开启，文件为mysqld.log

• 中继日志：用于主从服务器架构的从服务器中，用来存放主服务器二进制日志内容的中间文件。从服务器通过读取中继日志内容，来同步主服务器上的操作

• 数据定义语句日志：记录数据定义语句执行的元数据操作

日志的缺点

• 记录日志耗费时间，降低MySQL数据库性能

• 占用大量磁盘空间

bin log和redo log对比

• redo log是物理日志，记录内容是在某个数据页上做了什么修改；bin log是逻辑日志，记录内容是语句的原始逻辑

• redo log让InnoDB拥有了崩溃恢复能力；bin log保证了MySQL集群架构的数据一致性

• redo log在事务执行过程中可以不断写入；bin log只有在事务提交时才写入

锁

• 协调多个进程或线程并发访问某一资源的机制。当多个并发访问某个数据时，需要保证此数据在任何时刻最多只能有一个线程访问，保持数据的完整性和一致性

• 并发事务访问相同记录分为三种情况

  1. 读-读，读取不会对记录产生任何影响，不会引起问题，不考虑锁

  2. 写-写：会出现脏写，需要排队执行，加锁，一个事务对应一个锁结构

  3. 读-写：会出现脏读、不可重复读、幻读

     • 方案一：读利用MVCC，写加锁，性能好

     • 方案二：读和写都加锁，性能差

锁的分类

• 对数据操作类型划分

  • 读锁/共享锁（可加在表上也可加在行上）

  • 写锁/排他锁（可加在表上也可加在行上）

• 锁的粒度划分

  • 表锁：表级别的S锁(读锁)、X锁(写锁)；意向锁；自增锁；元数据锁（MDL）

  • 行锁：记录锁；间隙锁；临键锁；插入意向锁

  • 页锁

• 锁的态度划分：悲观锁，乐观锁

• 加锁方式：隐式锁，显式锁

• 其他：全局锁，死锁

读锁

• 共享锁（Shared Lock，S Lock）

• 对于同一份数据，多个事务读操作可以同时进行，不会互相影响，相互不阻塞

写锁

• 排他锁（Exclusive Lock，X Lock）

• 写操作完成前，阻断其他写锁和读锁，只有一个事务执行写，防止其他用户读取正在写入的同一资源

表锁

S锁和X锁

• S锁：表级别的读锁；X锁：表级别的写锁

• 锁住整个表，开销小，可避免死锁，但是并发率很低。表锁分为表级别的共享锁和表级别的独占锁

• 查看锁：show open tables

• 加表锁：lock tables 表 read/write

• 释放锁：unlock tables

意向锁

• InnoDB允许行锁与表锁共存，意向锁就是其中的一种表锁

• 当为行级别增加共享锁/排他锁时，会自动向表级别添加意向共享锁/意向排他锁

• 意向锁是为了协调行锁和表锁的关系，意向锁是行级锁和表级锁不冲突的，表名某个事务正在某些行持有了锁或该事务准备持有锁

自增锁

• 当向有自增列的表中插入数据时需要获取的一种特殊的表级锁

• 一个事务在有自增锁过程中，其他事务插入语句都要被阻塞

元数据锁（MDL）

• 对表做增删改查时，加MDL读锁

• 对表结构变更时，加MDL写锁

行锁

记录锁

• 行锁也称记录锁，锁定某一行记录

• 优点：锁定力度小，锁冲突概率低，并发度高

• 缺点：锁的开销大，加锁慢，容易出现死锁

间隙锁

• 仅仅是防止插入幻影记录，锁住该记录前边的间隙插入新数据

• 可能会产生死锁

临键锁

• 即锁住某条记录，又锁住该记录前边的间隙插入新数据，同时满足记录锁和间隙锁

插入意向锁

• 事务在等待的时间也需要在内存中生成一个锁结构，表名有事务意图在某个间隙插入数据，但是此时在等待

• 不会阻止别的事务继续获取该记录上任何类型的锁

页锁

• 页锁的开销介于表锁和行锁之间，会出现死锁

• 锁定粒度介于表锁和行锁之间，并发度一般

• 每个层级锁数量有限，锁会占用空间，锁空间大小有限

• 当某个层级锁数量超过阈值时，自动进行锁升级，行锁升级为表锁，可降低锁占用空间，但数据并发度下降

悲观锁

• 共享资源每次只给一个线程使用，其他线程阻塞，用完后再把资源转让给其他线程

• select...for update就是悲观锁，使用悲观锁必须确定使用索引，而不是全表扫描，否则会将整个表锁住

• 悲观锁适用于写操作的情况，不适用于长事务，开销非常大

乐观锁

• 数据更新的时候会判断在此期间别人有没有去更新这个数据，不采用数据库自身锁机制，而是通过程序实现，多用于读的情况下

• 乐观锁版本号机制：在表中设计一个版本字段version，第一次读取version字段的取值，再对数据进行修改，会执行update...set version=version+1 where version=#{version}。此时如果有事务对这条数据修改了，修改就不成功

• 乐观锁时间戳机制：当前数据的时间戳和更新之前取得的时间戳进行比较，若两者一致则更新成功，否则是版本冲突

• 乐观锁在主从复制的时候，由于版本号的原因，只用主机进行读取

• 乐观锁适用于读操作的情况

隐式锁

• 一个事务对新插入的记录可以不显式加锁，但由于事务id的存在，相当于加了一个隐式锁

显式锁

• 显式添加共享锁：select...lock in share mode

• 显式添加排他锁：select...for update

全局锁

• 对整个数据库加锁：flush tables with read lock

• 其他线程不能对此数据库进行任何操作，一直处于阻塞状态

• 全局锁用于做全库逻辑备份

死锁

• 两个事务/线程都持有对方需要的锁，并且都在等待对方释放，且双方都不会释放自己的锁

• 产生死锁的必要条件

  1. 两个或以上的事务

  2. 每个事务都已持有锁且申请新的锁

  3. 锁资源同时只能被同一个事务持有或不兼容

  4. 事务间因为持有锁和申请锁导致彼此循环等待

• 处理死锁

  • 等待，超过设置的阈值时自动回滚

  • 死锁检测机制：通过事务等待链表和锁的信息链表，构建等待图，一旦检测到回路，就是死锁，此时回滚undo量最小的事务，让其他事务继续执行。缺点：判断死锁需要消耗很多时间

• 避免死锁

  • 合理设计索引

  • 调整SQL执行顺序

  • 避免大事务，拆分为小事务

  • 并发高的情况下不显式加锁

  • 降低事务隔离级别

锁的结构

• 锁所在的事务信息：是一个指针，表锁和行锁都在事务执行中生成的，哪个事务生成这个锁结构

• 索引信息：对于行锁，记录加锁的记录是属于哪个索引的，也是一个指针

• 表锁/行锁：表锁记录对哪个表加的锁；行锁记录：①Space ID：记录所在表空间；②Page Number：记录所在页号；③n_bits：代表使用了多少比特位

• type_mode：行锁的具体类型、锁的类型、锁的模式

• 其他信息：为更好管理系统运行过程中生成的各种锁结构而设计的哈希表和链表

• 一堆比特位：对应页面中的记录，一个比特位映射到页内的一条记录

锁监控

• 查看锁的时间：show status like 'innodb_row_lock%'

• 查看事务和锁：select * from information_schema.innodb_trx

MVCC

• 多版本并发控制（Mutiversion Concurrency Control）

• MVCC是针对于读已提交和可重复读的隔离级别的事务

• 为了查询正在被其他事务更新的行，并且可以看到被更新前的值，这样在做查询的时候就不用等待其他事务释放锁

• MVCC主要是为了提高并发性能，更好的处理读-写冲突，做到不加锁也能阻塞并发读，这个读就是快照读，不是当前读

• 快照读：不加锁的简单select都是快照读，采用乐观锁的方式；当前读：select后加锁的就是当前读，是悲观锁的实现

• 可重复读解决了脏写、脏读、不可重复读。在MySQL中，由于MVCC的存在，可重复读还解决了幻读

MVCC实现原理

• 隐藏字段

  1. row_id

  2. trx_id：事务对某条聚簇索引记录改动时，会把该事务的事务id赋值给trx_id隐藏列

  3. roll_pointer：事务对某条聚簇索引记录改动时，会把旧的版本写入到undo log，此隐藏列相当于指针，可通过它找到该记录修改前的信息

• undo log版本链：每次对记录改动，会记录一条undo log，每条undo log也有roll_pointer列(insert语句没有，因为它就是最开始的版本)，将这些undo log连起来，形成链表

• ReadView：一个事务使用MVCC进行快照读时产生的读视图

  • 四个主要内容

    1. creator_trx_id：创建这个ReadView的事务id

    2. trx_ids：生成ReadView时系统中开启但未提交的事务的事务id列表

    3. up_limit_id：开启但未提交的事务中最小的事务id

    4. low_limit_id：生成ReadView时系统应分配给下一个事务的事务id

ReadView的规则

• 隔离级别为读已提交时，一个事务的每次select对应一次ReadView；隔离级别为可重复读时，一个事务只在第一次select获取一次ReadView。在访问某条记录时，按照以下步骤判断记录的某个版本是否可见

• 若被访问版本的trx_id属性值与ReadView的creator_trx_id相同，则当前事务在访问自己修改过的记录，所以该版本可被当前事务访问

• 若被访问版本的trx_id属性值小于ReadView的up_limit_id值，则生成该版本的事务在当前事务生成ReadView前已提交，该版本可被当前事务访问

• 若被访问版本的trx_id属性值大于或等于ReadView的low_limit_id值，则生成该版本的事务在当前事务生成ReadView后开启，该版本不可被当前事务访问

• 若被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id间，需要判断trx_id属性值是否在trx_ids中：若在，说明创建ReadView时生成该版本的事务是开启但未提交的，该版本不可被访问；若不在，说明创建ReadView生成该版本的事务已被提交，该版本可被访问

MVCC整体操作流程

1. 首先获取事务自己版本号，即事务id

2. 获取ReadView

3. 查询得到的数据，然后与ReadView中的事务版本号进行比较

4. 若不符合规则，从undo log中获取历史快照

5. 最后返回符合规则的数据

MVCC在可重复读的隔离级别下解决幻读

• 查询之前，为事务产生一个ReadView，根据trx_ids的范围判断能否被访问

主从复制

• 作用：读写分离、数据备份、高可用性

• 原理：Slave从Master读取bin log进行数据同步

• 步骤

  1. Master将写操作记录到二进制日志，这个记录叫做二进制日志事件

  2. Slave将Master的二进制日志事件拷贝到它的中继日志

  3. Slave重做中继日志中的事件，将改变应用到自己的数据库中。

     复制是异步的且串行化的，重启后从接入点开始复制

• 主从复制原则：

  • 每个Slave只有一个Master

  • 每个Slave只有有一个唯一的服务器id

  • 每个Master可以有多个Slave

• 主从复制数据同步一致性问题(从上到下数据一致性越好，但是并发性越差)

  • 异步复制：客户端commit后不需要等从库返回任何结果，而是直接将结果返回给客户端。不会影响主库写的效率，可能会宕机

  • 半同步复制：客户端commit后不直接将结果返回客户端，等待至少有一个库接受到了bin log，并写入中继日志，再返回客户端。提高数据一致性，降低主库写的效率

  • 组复制：将多个节点共同组成一个复制组，在执行读写事务的时候，需要通过一致性协议层的同意，也就是读写事务想要进行提交，必须经过组里大部分节点同意（＞n/2+1），才可以提交。而针对只读的事务，不需要经过组里的同意，直接commit

数据库备份和恢复

• 逻辑备份：mysqldump -u root -p 需要备份的数据库 ＞ 备份文件名.sql

• 逻辑恢复：MySQL -u root -p ＜ all.sql

• 表的导出：select * from 表 into outfile 目录

• 表的导入：load data infile 目录 into table 表

MySQL高级操作命令

• 查看数据库连接数：

  show status like 'threads%';

• 查看数据库字符集：

  show variables like '%char%';

• 查看引擎： show variables like '%storage_engine%'; select @@default_storeage_engine;

• 修改引擎： set default_storage_engine = MyISAM;（临时） 在my.cnf中设置default-storage-engine = MyISAM，重启服务

• 查看缓冲池大小： show variables like ‘innodb_buffer_pool_size’; 128MB 设置缓冲池大小： set global innodb_buffer_pool_size = 268435456; 256MB

• 克隆表： create table 新表 like 旧表; insert into 新表select * from 旧表;

• 创建新用户： create user '用户名'@'localhost' identified by '密码';

• 查看用户： use MySQL; select user,host,authentication_string from user;

• 重命名用户： use MySQL; rename user '旧用户名'@'localhost' to '新用户名'@'localhost';

• 删除用户： drop user '用户名'@'localhost';

• 修改当前用户密码： set password='新密码'

• 忘记root密码： 在my.cnf中加上skip-grant-tables 重启服务：systemctl restart mysql 直接输入mysql登录 修改root密码：update MySQL.user set authentication_string=password('新密码'); 刷新权限：flush privileges; 登录mysql：MySQL -uroot -p 最后把my.cnf中加的注释掉

• 授予权限： 权限列表：select,update,insert,delete,all grant 权限列表 on 库名.表名 to '用户名'@'localhost' identified by '密码'; flush privileges;

• 查看权限： show grants for '用户名'@'localhost';

• 撤销权限： revoke 权限列表 on 库名.表名 from '用户名'@'localhost';

• 查看隔离级别： show variables like 'transaction_isolation';

• 设置隔离级别： set global/session transaction_isolation='read-committed'; global在每个会话中生效，session只在当前会话生效

• 查看锁： show open tables

• 加表锁： lock tables 表 read/write

• 释放锁： unlock tables