MySQL-扩展

存储引擎

体系结构

image

  • 连接层
    最上层是一些客户端和链接服务，主要是完成一些类似于链接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层
    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
  • 引擎层
    存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需求，来选取合适的存储引擎。
  • 存储层
    主要是将数据存储在文件系统之上，并完成存储引擎的交互。

存储引擎简介

存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于数据库，所以存储引擎也可以被成为表类型。

  • 通过查看表的创建语句查看表的存储引擎
show create table learn_user;

image

  • 查看当前数据库支持的存储引擎
show engines;

image

存储引擎特点

InnoDB

InnoDB是一种兼顾高可靠性和高新能的通用存储引擎，在Mysql 5.5之后，InnoDB是默认的MySQL存储引擎。

  • 特点
    DML操作遵循ACID模型，支持事务；
    行级锁，提高并发访问性能；
    支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
  • 文件
    xxx.ibd: xxx代表的是表名，innoDB引擎的每张表都会对用这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
    参数：innodb_file_per_table 用于控制每一张表对应一各表空间文件。
    如图，在test数据库中存储的表：
    image
  • 逻辑存储结构
    从上到下分为：Tablespace(表空间)-》Segment(段)-》Extent(区)-》Page(页)-》Row(行)。
    Row存储的是表结构中的数据行；Row中包含Trx_id、Roll pointer
    image

MyISAM

MyISAM是MySQL早期的默认存储引擎。

  • 特点
    不支持事务，不支持外键
    支持表锁，不支持行锁
    访问速度快
  • 文件
    xxx.sdi: 存储表结构信息
    xxx.MYD: 存储数据
    xxx.MYI: 存储索引

Memory

Memory引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能将这些表作为临时表或缓存使用。

  • 特点
    内存存放
    hash索引（默认）
  • 文件
    xxx.sdi: 存储表结构信息

存储引擎选择

image
在选额存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB
    时Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎时比较合适的选择。
  • MyISAM
    如果应用是以读操作和插入操作为主，只是很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
  • MEMORY
    将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的权限就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

概念

索引（index）是帮助Mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

  • 优点
    提高数据的检索效率，降低数据库的IO成本；
    通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
  • 缺点
    降低了数据的更新、插入效率，数据更新、插入时需要消耗额为的CPU资源调整构造索引结构。

索引结构

image

  • 不同存储引擎对索引的支持情况

image

基础结构

二叉树

image
优点
结构简单，能支持大部分无序数据的快速检索。
缺点
当数据是顺序存储时，二叉树会变成全链表结构，导致检索数据变成了类似全表扫描的方式。
时间复杂读
乱序存储构成平衡二叉树：O(log n)
顺序存储构成单链：O(n)
时间复杂度为：O(log n) ~ O(n)
image

红黑树（自平衡二叉树）

针对二叉树中有序存储导致数据结构变成单链的问题，红黑树在二叉树的基础上提供了
image
简介
需要满足如下五个特征：
* 节点颜色：每个节点要么是红色，要么是黑色。
* 根节点颜色：根节点是黑色。
* 叶子节点颜色：所有叶子节点都是NIL节点，NIL为黑色。
* 红色节点子节点颜色：每个红色节点的两个子节点都是黑色。
* 路径黑色节点数目：从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点。

  • 插入过程
  1. 插入节点均默认为红色节点（因为红色节点不会改变书中的黑色节点数，因此能减少树结构的调整过程）
  2. 插入节点为根节点，直接插入后调整颜色为黑色
  3. 插入节点的父节点为黑色，直接插入不做调整
  4. 插入节点的父节点为红色且叔节点也为红色，直接插入后叔、父节点与爷节点颜色互换
  5. 插入节点的父节点为红色，且叔节点为黑色，直接插入后父节点与爷节点颜色互换，此时爷节点变红导致叔节点分支的黑色节点数变少，因此需要往叔节点方向旋转，保持各分支的黑色节点数一致。

注意：4、5操作结束后，可能导致爷节点与爷节点的父节点颜色都为红色，需要根据实际情况往上递归做4、5的操作。
红黑树具体原理和详细操作可参考：红黑树是怎么来的
优点
基于二叉树，提供了自旋方式保证了各分支节点的平衡，
缺点
由于加入了平衡控制，所以减少了数据插入和删除时重构索引的效率；
数据结构基于二叉树结构形成，索引的基本结构仍未二叉树，所以还是存在较大的层级
时间复杂度
O(log n)

B-Tree(B树，多路平衡树)

B-Tree中，一个节点可以有多个子节点，减少了树的层级，增大了查询效率。
通过树的最大度数（max-degree）来控制b-tree的key(可存储的数据数量)和指针（可指向的子节点数）。
如，max-degree为5，则key为4，指针数为5；当节点中的数据已经为4个时，当再次向节点中添加数据，会导致节点以五个数中的中位数为父节点进行分裂。

  • 结构图
    image
    特点
    每个节点会存储数据。
    优点
    增加了节点的子节点数，减少了树的层级
    缺点
    数据分散在各个节点上，范围查询时，降低了IO检索的效率。
    时间复杂读
    O(log m~n) m为树的最大节点数

B+Tree(B+树)-InnoDB默认

B+Tree是基于B-Tree结构的改造，区别在于数据只会存储在叶子中，非数据节点中的key元素都会落到叶子节点中。

  • 结构图
    image
  • 特点
    B+Tree索引中，非叶子节点不需要存储数据，可以留出更多的空间存储指针，因此相较于B-Tree树，B+Tree树的层级会更小，能更快的提高查询效率。
    优点
    提供了链式结构，提高了区间访问的性能，加快了排序操作
    缺点
    结构复杂，对插入、删除、修改不友好
    时间复杂读
    O(log m~n) m为树的最大节点数

hash（哈希索引）

哈希索引就是采用一定的hash算法，将索引值通过hash函数换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
针对hash冲突时，会将hash冲突的数据进行链式存储。

  • 结构图
    image
    特点
  1. Hash索引只能用于对等比较（=，in),不支持范围查询（between ,>, <, ...）
  2. 无法利用索引完成排序操作
  3. 查询效率高，同时只需要一次检索就可以了，效率通常要高于B+tree索引

索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为一下两种：

  • 聚集索引（Clustered Index）
    将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。数据表中必须有而且只能有一个（主键）
  • 二级索引（Secondary Index）-非聚集索引
    将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个；实际操作中，在表创建之后会已经存在聚集索引（一般是主键），之后再创建的索引都是二级索引。通过二级索引检索时，会先根据索引值检索的主键，让后使用主键通过聚集索引再次检索查询最终数据；再次检索的过程称为回表，仅当二级索引的索引列中不包含要查询的列时发生。

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果不存在主键且没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,...);

为某一列创建索引为单列索引，为多列创建索引为联合索引。
查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次;

SHOW GLOBAL STATUS LIKE 'Com_______';

image

慢查询日志

慢查询日志记录了所有执行时间超过制定参数（long_query_time，单位：秒，默认10秒）的多有SQL语句的日志。Mysql的慢查询日志默认没有开启，需要在Mysql的配置文件(/etc/my.cnf)中配置如下信息：

# 开启慢查询日志开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，需要重启服务器，可在/var/lib/mysql/localhost-slow.log文件中查看慢查询日志。

show profiles

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;

默认profiling是关闭的，可以通过sql语句在session/global级别开启profiling;

SET profiling = 1;

执行如下操作，可以通过执行查看sql具体的耗时情况:

# 查看每一条SQL的耗时基本情况
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

#查看指定query_id的SQL语句CPU的使用情况
show profile_cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令获取Mysql如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法：

# 直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
  • 查询结果示例

image

  • EXPLAIN执行计划各字段含义：
    • id:
      select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
    • select_type:
      表示select的类型，常见的取值有SIMPLE（简单表，即不使用表连接或子查询）、PRIMARY(主查询，即外层的查询)、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含子查询）等。
    • type
      表示连接类型，性能由好导差的连接类型为NULL、system、const eq_ref、ref、range、index、all。
    • possible_keys：
      查询过程中可能用到的索引
    • key:
      实际使用的索引，如果为NULL，则没有使用索引。
    • key_len:
      表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度准确性的前提下，长度越短越好。
    • rows
      MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
    • filtered
      表示返回结果的行数占需要读取行数的百分比，filterred的值越大越好。

索引使用规则

索引使用

  • 最左前缀法则
    如果索引使用了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
    如果跳跃某一列，索引将会部分失效（从跳跃的列以及这一列之后的索引列会失效）。
# 假如交易表cust_trade_info存在联合索引idx_cti_fund_code_trade_acct_type(fund_code, trade_acct, `type`)，则仅当查询过程中fund_code存在时索引会生效，当fund_code存在且trade_acct不存在时索引部分失效(type列失效)，查询条件中fund_code, trade_acct, `type`三个字段的顺序不固定。

# 一下查询结构索引生效
select * from cust_trade_info where fund_code = 'ZC001' and trade_acct = '12345678' and type= '1';
select * from cust_trade_info where fund_code = 'ZC001' and trade_acct = '12345678';
select * from cust_trade_info where fund_code = 'ZC001';

# 索引部分失效(type列失效)
select * from cust_trade_info where fund_code = 'ZC001' and type = '1';

上述示例中索引生效的结果如下：
image
image
image
索引失效结果如下：

  • 范围查询
    联合索引中，出现范围查询(>,<)，范围查询右侧的索引失效。
    image
# 假如职员表user_info存在联合索引index_name_emp_age(name,emp,age)。
#如下查询中使用了范围查询右侧>范围，则age列索引失效
select * from user_info where name = 'zwj' and emp = '研发部' and age > 19;
# 将>调整为>=即可，如下索引生效；因此，查询设计时，右侧判断最好带上=符合
select * from user_info where name = 'zwj' and emp = '研发部' and age >= 19;

索引失效

  • 索引列运算
    不要在索引列上进行运算操作，索引将失效
EXPLAIN select * from cust_trade_info where version + 1 = 1;
  • 字符串字段不加引号
    字符串字段使用时，不加引号，索引将失效，但是数据会正常查询。
select * from cust_trade_info where fund_code = 'ZC001' and trade_acct = '12345678' and type= '1';
  • 模糊查询
    如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失败。
## 索引生效
EXPLAIN select * from cust_trade_info where fund_code like 'ZC0%';

## 索引失效
EXPLAIN select * from cust_trade_info where fund_code like '%ZC0';
EXPLAIN select * from cust_trade_info where fund_code like '%ZC0%';
  • or 连接条件
    用or分割开的条件，如果or的条件中存在没有索引的列，那么涉及的索引都不会被用到。
## 由于vote_id字段不存在索引，导致id字段的主键索引失效
EXPLAIN select * from vote_record vr where vr.id = 10 or vr.vote_id = 10;
## user_id字段存在索引，所以两个字段的索引都生效
EXPLAIN select * from vote_record vr where vr.id = 10 or vr.user_id = 'vtEIu6T1icX02YD6rJcE'
  • 数据分布影响
    如果Mysql评估使用索引比全表更慢，则不使用索引。所有导致大范围数据能被匹配导的查询语句都有可能导致索引失效，如：
    <>、IS NULL、IS NOT NULL、NOT IN、<、>、BETWEEN AND
## group_id > 0 会查出表的所有数据，所以使用索引反而降低查询效率，因此mysql会放弃使用索引
EXPLAIN SELECT * from vote_record vr where group_id >= 0
如下索引生效
EXPLAIN SELECT * from vote_record vr where group_id >= 99

SQL提示

SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为提示来达到优化操作的目的。
当一个字段同时涉及稻多个索引时（既是单列索引，又是聚集索引的最左列），此时mysql会自动选择一个索引进行查询；如果需要强制指定使用具体的某一个索引，可以通过SQL提示进行操作。

  • use index:
    建议mysql使用指定索引，最终使用情况由mysql判断
EXPLAIN SELECT * from vote_record vr use index(idx_vote_record_group_id) where group_id = 99;
  • ignore index:
    忽略某个索引
EXPLAIN SELECT * from vote_record vr ignore index(idx_vote_record_group_id) where group_id = 99;
  • force index:
    强制使用某个索引
EXPLAIN SELECT * from vote_record vr force index(idx_vote_record_group_id) where group_id = 99;

复盖索引&回表查询

尽量使用复盖索引（查询使用了索引，并且需要返回的列在该索引中已经全部能够找到），减少select *。

当索引没法复盖所有结果字段时，二级索引只能先检索出主键，然后回表-再次使用主键在聚集索引中进行检索，影响查询效率。

前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大的节约索引空间，从而提高索引效率。

  • 语法：
create index idx_xxxx on table_name(column(n));
  • 前缀长度
    可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;

单列索引与联合索引的选择

实际应用中，建议针对需要频繁查询的多个字段建立联合索引，避免回表查询的情况；
如果对多个字段建立多个单列索引，当查询条件中涉及到所有索引字段，实际检索时并非所有索引都生效，mysql会默认使用检索效率高的索引。

SQL优化

插入数据

  • 批量插入
    存在大量数据同时插入时使用批量插入
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Zhangsan');
  • 分批次批量插入
    当数据量特别大，无法使用一条sql进行批量插入时，可以分批次进行批量插入。
    手动控制事务，避免每次插入时需要重新开启-提交事务。
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Zhangsan');
insert into tb_test values(4,'Tom2'),(5,'Cat2'),(6,'Zhangsan2');
insert into tb_test values(7,'Tom3'),(8,'Cat3'),(9,'Zhangsan3');
commit;
  • 主键顺序插入
    顺序插入能够提高聚集索引的构建效率。
  • 大批量插入数据
    对于大量数据的备份转移，可以使用load命令将指定格式的文件的数据插入到表结构中。

主键优化

数据组织方式

在InnoDB存储引擎中，表数据都是根据顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。已知主键聚集索引叶子结点是按顺序排列的链表结构。

主键设计原则

  • 满足业务需求的情况下，尽量降低主键的长度（主键需要存储到二级索引的叶子节点中，主键长度越低，可以留出更多的空间存放指针）。
  • 插入数据时，尽量选择顺序插入（避免页分裂），选择使用AUTO_INCREMENT自增长主键。
  • 尽量不要使用UUID做主键或者是其它自然主键，如身份证号。
  • 业务操作是，避免对主键的修改。

order by优化

  • Using filesort:
    通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
  • Using index:
    通过有序索引顺序操作直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
## 当存在age和phone的联合索引时，如下排序生效(需要使用复盖索引)：
explain select id,age,phone from tn_user order by age asc|desc, phone asc|desc;
explain select id,age,phone from tn_user order by age desc|asc;

group by优化

  • 针对分组字段建立联合索引
  • 分组使用字段需要满足联合索引的最左前缀法则，可在where和group by中按最左前缀法则分布索引列
## 当存在age和phone的联合索引时，如下分组查询生效
explain select id,age,phone from tn_user group by age ,phone;
explain select id,age,phone from tn_user group by age;
explain select id,age,phone from tn_user where age =  18 group by phone;

limit优化

如limit 2000000,10中，MySQL需要先排序找到前2000000记录后仅获取后10条记录，排序代价非常大。
优化思路：一般分页查询时，通过创建复盖索引能够比较好地提高性能，可以通过复盖索引加子查询形式进行优化。

explain select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

  • count(id)
    InnoDB引擎会便利整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接计算id数量（主键不可能为NULL）。
  • count(字段)
    没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
    有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接计算字段值数量。
  • count(1)
    InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接进行计算1的数量。
  • count(*)
    InnoDB引擎不会把全部字段取出来，而是专门做了优化，不取值，服务处直接按行进行累加。

update优化

InnoDB引擎执行update预计是会加行级锁，若where语句中的判断条件不是索引字段时，会升级为表锁；因此执行update语句时，尽量使用索引字段作为判断条件。

视图/存储过程/触发器

视图

  • 介绍
    视图（View）是一种虚拟存在的表。视图中的数据在数据库中并不存在，行和列数据来自于自定义视图查询使用的表，并且是在使用视图时动态生成的。
    视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建试图的时候，主要的工作就落在创建这条SQL查询语句上。
  • 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL ] CHECK OPTION]
  • 使用
select * from view_name;

存储过程

  • 介绍
    存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用中开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
    存储过程就是数据库SQL语言层面的代码封装与重用，将比较通用的业务数据处理逻辑通过SQL实现，并编译在数据库中。
  • 特点
    封装、复用业务数据处理逻辑。
    可以接受参数，也可以返回数据
    减少网络交互，效率提升

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型。
语法示例：

CREATE FUNCTION get_user_count ()
RETURNS INT

BEGIN

  RETURN (SELECT COUNT(*) FROM users);

END

触发器

  • 介绍
    触发器是与表有关的数据对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库确保数据的完整性，日志记录，数据校验等操作。
    使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级处罚，不支持语句级触发。
  • 语法
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_body
END;

锁

概述

  • 分类
    按照锁的颗粒度分类：
  1. 全局锁：锁定数据库中的所有表
  2. 表级锁：每次操作锁住整张表
  3. 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，已经更新操作的事物提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为以下三类：

  1. 表锁
  • 表共享写锁（read lock）
    只会阻塞其它事物的写，不会阻塞读
  • 表独占写锁 (write lock)
    其它事物的读、写操作都会阻塞
  1. 元数据锁（meta data lock，MDL）
    MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
    当对一张表进行更、删、改、查时，对一张表加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。
  2. 意向锁
    当通过id更改表中的数据时会给对于数据行加行级锁，如果此时有其它客户端要对表添加表锁，就不得不遍历全表的每一行数据判断是否有行级锁，通过意向锁可以解决这个问题。
    当事务开启时，首先会给表加意向锁，执行更新语句时会给数据行加行级锁，此时其它事务添加表锁时会先判断意向锁的状态。
  • 分类
    • 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
    • 意向排他锁（IX）：与表锁共享锁（read）及排他锁（write）都互斥，意向锁之间不会互斥。

行级锁

  • 介绍
    每次加锁锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
    InnoDB的数据时基于索引组织的，行锁是通过对索引上的索引加锁来实现的，而不实对记录加的锁。
    对于行级锁，主要分为以下三类：
    • 行锁（Record Lock）:锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
    • 间隙锁（Gap Lock）:锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其它事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。
    • 临建锁（Next-key Lock）:行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
  • 行锁分类
    • 共享锁（S）：其它事务对数据添加共享锁，组织排他锁（可以读，不能写）
    • 排他锁（X）：阻止其它事务获得数据的共享锁和排他锁（不可以读和写）
      insert、update、delete语句添加排他锁，select ... lock in share mode语句添加共享锁，select ... for update添加排他锁。
  • 行锁添加场景
    默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。
    1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
    2. InnoDB的行锁是针对与索引加的锁。不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。
    3. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
    4. 索引上的等值查询（普通索引），给记录后边的数据页间隙加间隙锁。
    5. 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止，添加临键锁。
      间隙锁唯一的目的是防止幻读，间隙锁可以共享。

InnoDB引擎

逻辑存储结构

包含表空间（Table Space）、段（Segment）、区（Extent）、页（Page）、行（Row）
image

  • 表空间（Table Space）:一个表空间对应一个ibd文件，用于存储记录、索引和表结构。
  • 段（Segment）:分为数据段、索引段、回滚段，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。
  • 区（Extent）：表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
  • 页（Page）：是InnoDB存储引擎磁盘管理的最小单位，每个页的默认大小为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘生气4-5个区。
  • 行（Row）：InnoDB存储引起数据是按行进行存放的。
    • Trx_id: 记录最后一个对记录进行修改的事务id
    • Roll pointer: 修改记录前，会将就的记录备份到undo日志中， Roll pointer用于存储指向旧数据的指针。

架构

包含内存部分（In-Memory）和磁盘部分(On-Disk)
image

内存架构

  • 缓冲池（Buffer Pool）:
    是主内存中的一个区域，用于缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有，则从磁盘加载），然后再以一定频率刷新到磁盘，减少磁盘IO。
  • Cange Buffer:更改缓冲区(针对于二级索引页)，在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存储在变更缓冲区中，在未来数据被读取时，再将数据合并回复到Buffer Pool中，再将合并后的数据刷洗到磁盘中。
  • Log Buffer: 日志缓冲区，用来保存要写入到磁盘中的log日志数据。

后台线程

  • Master Thread
    核心后台线程，负责调度其它线程，还负责将缓冲池的数据异步刷新到磁盘，保持数据库的一致性，还包括脏页的刷新，合并插入缓存、undo页的回收。

  • IO Thread
    在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大地提高数据库性能，而IO Thread主要负责这些IO请求的回调。

  • Purage Thread
    主要用于回收事务已经提交了的undo log，在事务提交之后，undo log不可能用了，就用它来回收。

  • Page Cleaner Thread
    协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。

事务原理

  • 简介
    事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
  • 特性
    • 原子性： 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
    • 一致性： 事务完成时，必须使所有的数据保持一致状态。
    • 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
    • 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
  • 特性实现原理
    原子性、一致性、持久性由redo log和undo log两张日志表实现；
    隔离性由锁和MVCC多版本并发控制实现。
  • redo log-持久性
    重做日志，记录的是事务提交时数据页的物理修改，时用来实现事务的持久性。
    该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改的信息都存到该日志文件中，用于在刷新脏页到磁盘时发生错误时，进行数据恢复使用。
  • undo log-原子性
    回滚日志，用于记录数据被修改前的信息，作用包含两个：提交回滚和MVCC(多版本并发控制)。
    undo log和redo log记录物理日志不一样，它是逻辑日志。可以人为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应的相反的update记录；当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
    undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
    undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段，内部包含1024个undo log segment。

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，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、read view。

实现原理

  • 记录中的隐藏字段
    每张表会默认添加DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

    • DB_TRX_ID：记录最近一次修改记录的事务ID。
    • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log。
    • DB_ROW_ID：隐藏主键，仅当表中没有指定主键时会自动生成。

  • undo log日志
    回滚日志，在insert、update、delete的时候产生的，便于数据回滚日志。
    当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除；而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即删除。

  • undo log版本链
    image
    不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

  • Readview
    ReadView(读试图)是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交）id。
    ReadView包含了四个核心字段：

    • m_ids: 当前活跃的事务ID集合；
    • min_trx_id: 最小活跃事务ID；
    • max_trx_id: 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）；
    • creator_trx_id: ReadView创建者的事务ID。

    版本链数据访问规则：(trx_id:代表是当前事务ID，取数据行的db_trx_id)。

    1. trx_id == creat_trx_id: 可以访问该版本-》说明数据是当前这个事务更改的
    2. trx_id < min_trx_id: 可以访问该版本-》说明当前事务查询的是已经提交的数据
    3. trx_id > max_trx_id: 不可以访问该版本-》说明该事务是在ReadView生成后才开启
    4. min_trx_id <= trx_id <= max_trx_id: 如果trx_id不在m_ids中是可以访问该版本的=》说明事务已经提交。

    不同的隔离级别，生成ReadView的时机不同：

    • READ COMMITED：在事务中每一次执行快照读时生成ReadView;
    • REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

多版本并发控制的过程分析

  1. 每一次快照读时，根据当前数据行的db_trx_id与ReadView中的属性进行比较，比较规则见版本链数据访问规则；
  2. 当符合规则时，直接返回数据行数据;
  3. 不符合规则，则根据db_trx_id在undo log日志中查找上一次的更新记录中的db_trx_id，再此与ReadView比较，以此方式迭代，知道找到能匹配规则的版本数据，并返回。

下图为READ COMMITED事务级别的MVCC过程
image
图中事务5两次读取都为快照读，在RC事务级别下会分别生成ReadView，按图分析，第一次会读取到事务2提交的数据，第二次会读取到事务3提交的数据，因此RC事务级别不能支持可重复读，但可以支持脏读。
针对RR事务级别，事务中只有第一次快照读时会产生ReadView，后面的快照读会共用第一次产生的ReadView，因此每次读获取的数据是同一个版本。

二进制日志（BINLOG）

二进制日志（BINLOG）记录了所有的DDL(数据定义语言)语句和DML（数据操纵语言）语句，但不包括数据查询(SELECT、SHOW)语句。
作用：

  1. 灾难时的数据恢复；(误删时可根据删除语句还原数据)
  2. MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：
show variables like '%log_bin%'

主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制传到从库服务器中，然后在从库上对这些日志重新执行，从而使从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从库的主库，实现链状复制。

  • 特点
    • 主库出现问题，可以快速切换到从库提供服务
    • 实现读写分离，降低主库的访问压力
    • 可以在从库中执行备份，以避免备份期间影响主库服务
  • 搭建配置
    • 主库配置
      1. 修改配置文件 /etc/my.cnf
      # mysql服务器ID，保证整个集群环境中唯一，取值分为：1~2^32 - 1，默认为1
server-id=1
#是否只读，1 代表只读， 0代表读写
read-only=0
#忽略的数据，指定不需要同步的数据库
binlog-ingore-db=mysql
#指定同步的数据库
binlog-do-db=db01
      1. 重启MySQL服务器
      systemct restart mysqld
      1. 登录mysql，创建远程连接的账号，并授予主从复制权限
      #创建itcast用户，并这是密码，该用户在可在任意主机连接该MySQL服务
CREATE USER ‘itcast’@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为'itcast'@'%'用户分配主从复制权限
GRANT REPLICATION SLAE ON *.* TO 'itcast'@'%';
    • 主库配置
      1. 修改配置文件 /etc/my.cnf
      # mysql服务器ID，保证整个集群环境中唯一，取值分为：1~2^32 - 1，默认为1
server-id=2
#是否只读，1 代表只读， 0代表读写
read-only=1
      1. 登录从库mysql，设置主库配置
      CHANGE REPLICATION SOURCE_HOST='XXX.XXX',SOURCE_USER='XXX',SOURCE_PASSWORD='XXX',SOURCE_LOG_FILE='XXX',SOURCE_LOG_POS=XXX;

分库分表

概述

随着数据量的增长，采用单台数据库进行数据存储，存在以下性能瓶颈：

  • IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带款不够，网络IO瓶颈。
  • CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会损费大量的CPU资源，请求数太多，CPU出现瓶颈。

拆分策略

垂直拆分

  • 垂直分库
    以表为单位进行拆分，将不同的表拆分到不同的数据库；如user表存储到db1，cust_trade表存储到db2。
  • 垂直分表
    以字段为依据，根据字段属性将不同字段拆分到不同表中。（根据业务情况拆分成1:1的多张表）

水平拆分

  • 水平分库
    将一张表的数据分散存储在不同的数据库，每个数据库中的表结构一样，类似于集群。
  • 水平分表
    将一张表的数据分散存储在不同的表中，每张表的结构一样。

实现技术

shardingJDBC

基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能高。
参考文献：shardingsphere官方手册

逻辑表

针对水平拆分的真实表给出的虚拟表，如数据库将客户交易数据分别存储在cust_trade_0、cust_trade_1中，为了方便项目中sql的编写，给出逻辑表统一代替执行对cust_trade_0、cust_trade_1的增、删、查、改操作。

真实表

数据库中真实存储数据的表，如上述示例中的cust_trade_0、cust_trade_1。

绑定表

如存在交易表主子表cust_trade和cust_trade_item，根据表中交易编号req_no作为分区关键字对数据分区存储在cust_trade_0、cust_trade_item_0、cust_trade_1、cust_trade_item_1中，如需要根据req_no查询交易主子表信息，则实际查询会对主子表做笛卡尔积，如下四条查询语句：

select * from cust_trade_0 ct left join cust_trade_item_0 cti on cti.req_no = ct.req_no;
select * from cust_trade_0 ct left join cust_trade_item_1 cti on cti.req_no = ct.req_no;
select * from cust_trade_1 ct left join cust_trade_item_0 cti on cti.req_no = ct.req_no;
select * from cust_trade_1 ct left join cust_trade_item_1 cti on cti.req_no = ct.req_no;

可以通过关联表将表cust_trade和cust_trade_item进行关联，采用同样的分区策略，则在实际查询时只会出现两种情况，如下：

select * from cust_trade_0 ct left join cust_trade_item_0 cti on cti.req_no = ct.req_no;
select * from cust_trade_1 ct left join cust_trade_item_1 cti on cti.req_no = ct.req_no;

在yaml文件中配置绑定表：

shardingRule:
	bindingTables:
		- cust_trade,cust_trade_item

数据库分片

当在多个数据分散存储同一逻辑表数据时，为了提高查询效率，需要配置合适的数据库分片策略，保证关联数据存储在同各一个数据库服务中;如交易表cust_trade和交易账号表trade_info，业务上绝大部分场景会对两张表做关联查询，因此当同一个用户的交易账号和交易单数据分区在同一台数据库服务器上时，能提高查询效率。

数据库分片关键字

数据库分片需要依赖于分片关键字和分片算法实施，通过对关键字执行分片算法的散列过程映射到具体的数据节点上，关键字可以是多个表字段。针对上述示例，可以用使用用户id(user_id)做关键字，将同一用户的数据存储在同一数据库服务器上；也可以根据产品编号（prd_code）将同一产品的数据，存储在数据库服务器上。

数据库分片算法

提供了分片表达式，用于自定义制定根据关键字计算数据节点的计算过程。

表分片关键字

作用与数据库分片关键字类似，但是表分片关键字一般使用了数据库主键，mysql中为了提交数据查询效率大部分采用自增主键，因此逻辑表水平拆分后需要保证主键的唯一性和自增性。可通过keyGenerator.type配置主键生成规则，shardingJDBC提供了UUID和雪花算法两种方式。

  • UUID：
    根据UUID生成特性，任意时刻生成的UUID都能保证唯一性，产生冲突的几率非常小，可作为主键生成工具。但是UUID无法保证自增性，可在不需要提供排序、分页查询的表中使用。
  • 雪花算法（SNOWFLAKE）：
    雪花算法基于时间戳+自定义字段（官方推荐服务器标识）+序列号（同一时刻同一服务器生成多个主键时，通过序列号从小到大排列）,雪花算法同时保证了自增性和唯一性。

配置

  • 数据分片
配置示例
数据分片
dataSources:
  ds0: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds0
    username: root
    password: 
  ds1: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds1
    username: root
    password: 

shardingRule:  
  tables:
    t_order: 
      actualDataNodes: ds${0..1}.t_order${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy: 
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: ds${0..1}.t_order_item${0..1}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: ds${user_id % 2}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item${order_id % 2}  
	        cust_hold:
#          actual-data-nodes: ds1.cust_hold_$->{2020..2030}_$->{1..12}
	  actual-data-nodes: ds1.cust_hold_2024_$->{1..4}
      table-strategy:
          standard: # 制定自定义分片算法
            sharding-column: hold_date
#              提供自定义分片实现
            precise-algorithm-class-name: com.zwj.learn.springcloud.tradeserver.common.sharding.DatePreciseShardingAlgorithm
  bindingTables:
    - t_order,t_order_item
  broadcastTables:
    - t_config
  
  defaultDataSourceName: ds0
  defaultTableStrategy:
    none:
  defaultKeyGenerator:
    type: SNOWFLAKE
    column: order_id
  
props:
  sql.show: true
  • 读写分离
dataSources:
  ds_master: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_master
    username: root
    password: 
  ds_slave0: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_slave0
    username: root
    password: 
  ds_slave1: !!org.apache.commons.dbcp.BasicDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_slave1
    username: root
    password: 

masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_master
  slaveDataSourceNames: 
    - ds_slave0
    - ds_slave1

props:
    sql.show: true
posted @ 2024-03-10 22:21  周仙僧  阅读(15)  评论(0)  编辑  收藏  举报