MySQL高级

MySQL进阶

 

文章目录

• • MySql进阶
    • 1、存储引擎
    • 2、索引
    • 3、SQL性能分析
    • 4、索引的使用
    • 5、覆盖索引
    • 6、前缀索引
    • 7、单列索引和联合索引
    • 8、索引设计原则
    • 9、SQL优化
      • 9.1 insert 优化
      • 9.2 主键优化
      • 9.3 order by 优化
      • 9.4 group by优化
      • 9.5 limit 优化
      • 9.6 count 优化
      • 9.7 update 优化
    • 10、锁
      • 10.1 全局锁
      • 10.2 表级锁
      • 10.3 行级锁
    • 11、InnoDB 引擎
      • 11.1 逻辑存储结构
      • 11.2 架构
      • 11.3 事务原理
      • 11.4 MVCC
    • 12、系统数据库
    • 13、常用的工具
  • MySQL运维
    • 1、日志
      • 1.1 错误日志
      • 1.2 二进制日志
      • 1.3 查询日志
      • 1.4 慢查询日志
    • 2、主从复制
    • 3、分库分表
    • 4、MyCat
      • 4.1 入门配置
      • 4.2 垂直分库
      • 4.3 水平分表
      • 4.4 分片规则
    • 5、Mycat的管理和监控
    • 6、读写分离

 

1、存储引擎

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

MySql的体系结构

• 连接层： 最上层是一些客户端的链接服务，主要完成连接处理、授权认证、及相关的安全方案。
• 服务层： 第二层架构主要完成大多数的核心业务，如SQL接口，并完成缓存查询，SQL的分析和优化。
• 引擎层： 负责mysql数据的存储与提取。不同的引擎有不同的功能，根据需要选择合适的引擎，mysql默认使用innodb引擎。
• 存储层： 数据的存储位置。

各类存储引擎的对比

特点	innodb	myisam	memory
存储权限	64TB	有	有
事务安全	支持	不支持	不支持
B+tree索引	支持	支持	支持
hash索引	自适应hash索引	不支持	支持
全文索引	支持（5.6版本后）	支持	不支持
支持外键	支持	不支持	不支持
文件	xxx.ibd	xx.sdi、xx.myd、xx.myi	xx.sdi
锁	支持行锁和表锁	支持锁	-

• InnoDB： 是mysql的默认存储引擎，支持事务、外键、行级锁。可以保持数据的一致性（事务）
• MyiSAM： 以读和插入为主的，对事务的完整性不高。
• Memory： 数据存储在内存，采用的为hash索引。

InnoDB的逻辑存储结构

• Tablespace：表空间
• Segment：区
• Extent：段 【默认大小为1M】
• page：页 【默认大小为16k】

2、索引

索引是mysql进阶部分的核心，是优化sql语句的主要渠道

• 优点
  • 提高数据检索的效率，降低数据库io成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
• 缺点
  • 索引列占用空间
  • 索引提高了查询效率，但同时也降低了表单更新、插入、删除效率，因为要维护索引

索引结构	描述
B+Tree索引	最常见的索引类型，解决了层高问题
Hash索引	底层结构是由hash表实现的，只能精确匹配，不能范围查询
B-Tree索引	空间索引是MySAM引擎的一个特殊索引类型，主要用于地理空间数据类型
full-text全文索引	快速匹配文档的方式

B-Tree（多路平衡查找树）

• 以一颗最大度数为5阶的B-数为例（每个节点最多存储4个key、5个指针）
• 每个key下都挂载数据

B+Tree

• mysql索引数据结构对B+Tree优化了，增加了一个指针，提高了区间的访问性能。
• 每一个key都会出现在叶子节点上，所有的数据都挂载在叶子节点上。
• 叶子节点形成了一个双向链表。

B-Tree和B+Tree比较

• 相对于B-Tree，B+Tree的层级更少，搜索效率更高，解决了层高问题。
• B-Tree无论是叶子节点还是非叶子节点都保存数据，导致一页存储的健和指针变少，高度也会随之增加。

索引的分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	只能有一个	primary
唯一索引	避免某数据列的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	查找本关键字	可以有多个	fulltest

InnoDB中根据存储形式分为：

• 聚集索引：将数据存储与索引放在一块，索引结构的叶子节点保存了行数据，必须有且只能有一个，有主键时主键索引就是聚集索引，没有主键时就使用第一个唯一索引，都没有时会自动生成一个rowid作为隐藏的聚集索引。
• 二级索引：将数据与索引分离，叶子节点关联的是对应的主键，可能会用于回表查询，可以存在多个。

索引语法

• 创建索引

create [unique|filltest] index index_name on table_name(index_col_name);

• 查看索引

show index from table_name;

• 删除索引

drop index index_name on table_name;

索引命名规范：idx _ 表明 _ 字段名

3、SQL性能分析

Mysql客户端连接成功后，通过show 【session 会话|global 全局】status 命令可以提高服务器状态信息

• 查看当前数据库的 增、删、改、查的访问频次

show global status like 'Com_ _ _ _ _ _ _'；  

知道了各种SQL语句的访问频次可以为SQL的优化提供参考

慢查询日志，涮选出查询较慢的SQL语句，后续进行优化

• 修改mysql的配置文件 my.cnf

#开启MySQL的慢日志查询开关

slow_query_log=1

#设置慢日志的时间，即超过了这个时间就会视为慢查询，就会记录在慢查询日志中 localhost-slow.log

long_queue_time=2

profile 详情

show profiles 能够在做SQL优化时帮助了解时间都耗费在哪里，即SQL执行的时间在各个阶段花费的时间

• 查看当前是否支持profile

select @@have_profile;

• 开启profile

set profile=1;

• 操作

--查看所有SQL的耗时
show profile

--查看指定SQL的耗时
show profile for query query id;

explain 执行计划

最常用的查看SQL执行情况的方式，只要在SQL语句前面加上 explain就可以查看

• id：select的执行顺序，都相同时从上往下执行，当不相同的时，值越大越先执行。
• type：表示连接类型，由好到差 null >system >const >eq_ref >ref >range >index >all 。
• possible_key：可能用到的索引。
• key：实际使用的索引。
• key_len：索引长度。
• filtered：返回数据的行数占总记录数的百分比，越大越好。

4、索引的使用

最左前缀法则

如果使用的是联合索引 ，要遵守最左前缀法则。最左前缀法则是指在创建联合索引时写在最左端的那个字段必须要出现在查询语句的条件部分。从最左侧列开始，并且不跳过中间的列，如果跳过了中间某列，索引就会部分失效【跳过列的后面字段索引失效】

create index idx_user_age_name_status on user(age,name,status) --创建联合索引

select * from user where age=18 and name='haha' and status='kkk' --由于age这歌最左列存在，索引不会失效

select * from user where age=18 and name='haha'  --虽然字段status没有，但它是最后一个即中间没有跳过某些字段，索引不会失效
 
select * from user where name='haha' and status='kkk' --没有最左列age，索引失效

select * from user where age=18 and status='kkk'  --跳过了name索引status索引部分失效，age存在

范围查找

联合索引 中出现了范围查询 （>,<），范围查询右侧的索引失效

create index idx_user_age_name_status on user(age,name,status) --创建联合索引

select * from user where age>18 and name='haha' and status='kkk' --age>8范围查找'>'后面的列索引失效

注意：>=、<= 不会引起索引失效

索引列运算索引失效

在带有索引的字段上进行运算操作会导致索引失效

create index idx_user_age_name_status on user(age,name,status) --创建联合索引

select * from user where age>18 and substring(name,2,6) and status='kkk' --substring()运算该列索引失效

字符串不加引号

字符串类型字段使用时不加引号，索引会失效

create index idx_user_age_name_status on user(age,name,status) --创建联合索引

select * from user where age>18 and substring(name,2,6) and status=kkk --kkk不加引号，该列索引失效

模糊查询

模糊查询时仅仅是尾部模糊匹配，索引不会失效，如果**头部模糊匹配索引就会失效**

select * from tb_user where profession like '软件%'； --没有头部模糊匹配，索引不失效

select * from tb_user where profession like '%软件'；  --有头部模糊匹配，索引失效

select * from tb_user where profession like '%软件%'； --有头部模糊匹配，索引失效

or 连接条件

用or分割开的条件，只要or前后的字段中有一个字段没有索引，那么涉及到的索引都失效，即使有的字段有索引也会失效

select * from tb_user where id=2 or age=21  --由于age没有索引，即使id有主键索引也会都失效

数据分布影响

mysql会评估，如果使用索引比全表更慢就不会使用索引，具体而定

sql提示

• user index：建议mysql使用说明索引，但接不接受由mysql决定。

select * from tb_user user index(idx_user_age) where id=2 and age=21

• ignore index：忽略特定索引。

select * from tb_user ignore  index(idx_user_age) where id=2 and age=21

• force index：强制mysql使用特定索引，这时mysql就没有决定权，必须使用指定索引。

select * from tb_user force  index(idx_user_age) where id=2 and age=21

5、覆盖索引

覆盖索引是指当使用的不是聚集索引时查询时可以避免回表查询，提高效率，要尽量使用覆盖索引

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

create index idx_user_age_name_status on user(age,name,status) --创建联合索引

select id,age,name,status from user where  age=21 and name='haha' and status='kkk';

由于所查询的字段为id,age,name,status 在索引中可以找到，而id也是挂载在二级索引的叶子结点上，这样就不会进行回表查询，这就是覆盖索引。如果没有覆盖索引就会进行回表查询。

6、前缀索引

当字段类型为字符串类型（varchar，test等）时，可能会存储很长的字符串，这会让索引变得很大，查询就会比对的很慢，即使创建了索引。所以要使用前缀索引，只比对部分字符串，这时查询的效率就会相对较高

语法

create index idx_user_name on user(name(2));  --创建前缀索引，这里是比对前面两个字符

前缀选择的依据

• 根据选择性来选择，选择性是指不重复的索引值和数据表中的记录总数的比值，越高查询效率就越高，唯一索引的选择性是1所以选择性最强，性能也是最好的

select count(distinct substring（email，1,5）/count(*) from user; 

• 截取email的5个字符的数据与表中的所有数据的比值，值越大选择性越好

7、单列索引和联合索引

• 单列索引：一个索引只包含单个列
• 联合索引：一个索引包含多个字段
• 尽量使用联合索引，效率较高
• 多条件联合查询时，mysql优化器会评估使用哪个字段的索引效率更高，会选择该索引来完成查询

8、索引设计原则

1. 针对数据量较大且查询比较频繁的表建立索引。
2. 针对常作为查询条件、排序、分组操作的字段建立索引。
3. 选择区分度较高的列作为索引。
4. 字段长度较长的使用前缀索引。
5. 尽量使用联合索引，联合索引很多时候可以覆盖索引，提高查询的效率。
6. 要控制索引的数量，索引越多维护索引的代价也就越大，会影响增、删、改的效率。
7. 如果索引列不能存储null时创建表时使用not null来约束，当优化器知道每列是否包含空值时，它可以更好的确定哪个索引最有效的用于查询。

9、SQL优化

9.1 insert 优化

批量插入

insert into tb_user values(1,'haha'),(2,'heihei'),(3,'xixi');

手动提交事务

start transacyion; --开启事务手动提交
insert into tb_user values(1,'haha'),(2,'heihei'),(3,'xixi');
insert into tb_user values(1,'haha'),(2,'heihei'),(3,'xixi');
insert into tb_user values(1,'haha'),(2,'heihei'),(3,'xixi');
commit;  --提交

主键顺序插入

• 主键顺序插入 【效率较高】
• 主键乱序插入
• 大批数据插入：如果需要大批数据的插入，使用insert语句插入的性能就会很低，此时可以使用mysql的load指令来进行插入。

#连接数据库服务时加上参数   --local-infile
mysql --local-infile -u root -p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1

#执行load指令将准备好的数据加载到表结构中，'表名字段以 ， 分隔'   '以换行分隔行数据 \n '
load data local infile '文件的地址' into table '表名' fields teminated by ',' lines teminated  by '\n' ；

9.2 主键优化

对主键进行优化

列分裂

• 页可以为空，也可以填充一半，填充100%，每个页包含了2-n行数据（一行数据过大时就会行溢出），根据主键排序的，如果是乱序插入的话，就可能会发生列分裂。

列合并

• 当删除一行数据时，其实不会立即删除，而是进行标记该空间已经被删除了可以被申请，当该页数据占比小于或等于50%时，innodb就会寻找最近的页看是否可以将其进行合并。

主键的设计原则

1. 满足业务要求的情况下，主键的长度尽量小一些。
2. 插入时尽量顺数插入，是用Auto_increment自增。
3. 尽量避免对主键的操作。

---

9.3 order by 优化

排序的优化

• using filesort：通过表的索引或者全文扫描，读取满足条件的行，然后在排序缓存区进行排序操作，所有不是通过索引直接返回排序结果的排序都叫 filesort排序。
• using index：通过有序索引顺序扫描直接返回有序数据，就是using index ，不需要额外的排序，效率高。

#通过没有创建索引的字段进行排序
select id,age,phone from user order by age,phone;  #using filesort·

#带索引排序
select id,age,phone from user order by age,phone;  #using index，效率高，同为升序或同为降序，没有指定默认为升序

图示：

说明：

• 根据排序字段建立连个索引，遵循最左前缀法则
• 尽量使用覆盖索引
• 要想一个升序一个降序，在创建索引时声明，默认为升序，如果没有声明一个升序一个降序还是会有额外的排序即using filesort

9.4 group by优化

分组优化

#创建索引
create index idx_user_sex_name on user(sex,name);

#分组
select id name sex from user group by sex;  #使用到了索引，效率高

• 遵循最左前缀法则，可以覆盖索引。

9.5 limit 优化

分页优化

当数据量大时就需要用到分页，而分页越到后面就会越慢。如将100000到100010分为一页。这时需要用覆盖索引 加子查询 的办法来优化.

9.6 count 优化

聚合函数的优化

count（）是一个聚合函数，一行一行的判断，如果count函数的参数不是null，累计就加1，最后统计累计值

count的几种用法

• count（主键）： innodb引擎会遍历整张表 ，把每一行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为null，所以不用判断）。
• count（字段）： 没有not null约束，会遍历整张表将该字段都取出返回服务层，服务层进行判断不为null就加1；有notnull约束时，遍历整张表取出该字段返回服务层，服务不需判断，直接按行累加。
• count（1）： 遍历整张表，但不取值，服务层对于返回的每一行放一个数字1进去，直接按行进行累加。
• count（*）： innodb做了优化，不取值，服务层直接按行累加。

按查询效率排序：count（字段）< count(主键) < count(1) 、count( * ) 【尽量使用count（*）】

9.7 update 优化

更新语句优化

update操作时，条件尽量为带有索引的字段，防止行锁升级为表锁

updata user set name='hha' where id=1  
#由于主键有索引，所以此时是行锁，锁的是id=1这一行记录，其他线程可以操作id！！=1的记录
   
update user set sex='女'  where  name='haha'
#由于name字段没有索引，所以此时锁的是整个表，其他线程此时都不能操作该表，并发性极低

innodb的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则行锁也会升级为表锁

10、锁

概述

锁是计算机协调多个进程或线程并发访问某一个资源的机制，为了保证数据并发访问的一致性和安全性。锁对数据而言显得重要，也更加复杂了。

分类 【按锁的粒度分】

• 全局锁： 锁定数据库中的所有表。
• 表级锁： 每次操作锁住整张表。
  • 表锁
  • 元数据锁
  • 意向锁
• 行级锁： 每次操作锁住对应的行数据。
  • 行锁
  • 间隙锁
  • 临建锁

10.1 全局锁

全局锁的粒度是最大的，锁住的是整个数据库实例，加锁后整个实例处于只读状态【即查询】，其他语句都处于阻塞状态

典型的使用场景：全库备份时，阻塞所有的DML、DDL语句，保证数据的完整性

• 加锁全局锁： flush tables with read lock、
• 释放全局锁： unlock tables；

特点

数据库中加全局锁，是一个比较重的操作，存在一下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新操作。
2. 如果在从库上备份，那么在备份期间从库不能执行主库的同步过来的二进制文件。会导致主从延迟。

在innodb引擎中，可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

mysqldump --single-transaction -uroot -p123456 itcast>itcast.sql  #备份到itcast.sql文件

10.2 表级锁

锁整张表

（一）表锁

对于表锁分为：

• 表级共享锁（read lock）

• 表级占写锁（write lock）

语法：

• 加锁： lock tables 表名 read/write 【加都锁、加写锁】
• 释放锁： unlock tables

读锁不会阻塞其他客户端的读操作，但会阻塞写操作。写锁不但阻塞其他客户端的写操作也会阻塞读操作，自己的客户端不阻塞

（二）元数据锁

MDL加锁过程是系统自动控制，无需显示声明，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL语句的冲突，保证读写的正确性。

mysql5.5中引入了MDL，当对一张表数据进行增删改查的时候加MDL共享读锁或共享写锁；当对表结构进行变更时加MDL非共享写锁（排他锁）

对应的SQL	锁类型	说明
select	share_read	与share_read、share_write兼容，与exclusive互斥
insert、update、delete	share_write	与share_read、share_write兼容，与exclusive互斥
alter	exclusive	与以上都互斥

查看元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from perfromance_schema.metadata_locks;

（三）意向锁

意向锁主要解决表锁和行锁的冲突问题，这样表锁不需逐行检查是否加了行锁。

1. 意向共享锁（IS）： 由语句 select ...lock inshare mode 添加，与读锁兼容与写锁互斥
2. 意向排它锁（IX）： 由 insert、update、delete、select ... for update 添加，与读锁与写锁都互斥

查看意向锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from perfromance_schema.data_locks;

10.3 行级锁

行级锁，每次操作时是锁住行数据，锁粒度最小，发生冲突的概率最低，并发度最高，应用在innodb存储引擎中

行锁是针对索引加锁的而不是对记录加锁

• 行锁（Record lock）： 在RC、RR隔离级别下都支持
• 间隙锁（Gap lock）： 锁定索引记录间隙（不含该记录），确保索引记录的间隙不变，防止其他事务在这个间隙插入值，产生幻读。在RR隔离级别下支持。

• 临建锁（Next-Key lock）： 行锁和间隙锁的组合，同时锁住数据，以及数据前面的间隙，在RR隔离级别下支持。

（一）行锁

innodb实现类一下两种类型的行锁

1. 共享锁（S)： 允许事务读，阻止其他事务加排他锁。
2. 排他锁（X)： 允许获得排他锁的事务操作，其他事务阻塞排他锁和共享锁都不能加。

	共享锁	排他锁
共享锁（S)	兼容	冲突
排他锁（X)	冲突	冲突

• insert、update、delete、select … for update 自动加排它锁。
• select … lock in share mode加共享锁。
• select … 不加任何锁

注意：需针对索引加锁，否则会升级为表锁

查看行锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from perfromance_schema.data_locks;

（二）间隙锁、临建锁

默认情况下，innodb在RR事务隔离级别运行，innodb使用nest-key锁进行搜索和索引扫描，以防止幻读

• 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
• 索引上的等值查询（普通索引），向右遍历时最后一个值不满足需求时，临建锁退化为间隙锁。
• 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止。

注意：间隙锁的唯一目的就是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上加间隙锁。

11、InnoDB 引擎

innodb默认的mysql的引擎

11.1 逻辑存储结构

• Tablespace：表空间
• Segment：区
• Extent：段 【默认大小为1M】
• page：页 【默认大小为16k】
• Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
• Roll_pointer：每次对某条记录进行改动时，都会把旧的版本写入到undo日志中，便于数据的恢复，这个隐藏列就相当于一个指针，可以通过它来找到记录修改前的信息。

11.2 架构

innodb的架构

内存区

• buffer pool：缓冲池是主内存中的一个区域，里面缓存的是经常操作的真实数据，所有的SQL语句操作后到数据都会到该缓冲池，然后会一定的频率刷新到磁盘中。缓冲池中以page为单位存储，page的类型分为三种：
  • free page 空闲，未被使用 、clean page 被使用了但为修改数据 、diry page使用了且数据修改过
• change buffer：更改缓冲区，数据的修改先在该区完成，在同步到buffer pool缓冲池，减少磁盘IO。
• Adaptive Hash Index：自定义hash索引，自动完成 【参数：adaptive_hash_index】
• log buffer：日志缓冲区，定期刷新到磁盘。

磁盘区

• 系统表空间，更新缓冲区的存在区域。
• 单个表空间、通用表空间、全局临时表空间。
• Doublewrite buffer file：双写缓冲区，InnoDB引擎将数据页从buffer pool 刷新到磁盘前，先写入该双写缓冲区文件中，便于系统异常时恢复数据。
• Redo log：重做日志，用来实现事务的持久性。redo log buffer 内存中，redo log 磁盘中。

后台线程

• master thread： 核心的后台线程，负责调度其他线程。还负责减缓冲区的数据异步刷新到磁盘，负责脏页的刷新、合并、undo页的回收。
• IO thread： 负责IO请求，在主从复制时，就需要io线程读取binglog日志到从机。

线程类型	职责	默认个数
read thread	4	读操作
write thread	4	写操作
log thread	1	日志到磁盘
insert buffer thread	1	数据到磁盘

• purge thread： 用于回收事务已经提交了的undo log。
• page cleaner thread： 协作master thread脏页的刷新

11.3 事务原理

事务时一组操作的集合，要么都做要么都不做

ACID：原子性、一致性、隔离性、持久性

• redo log + undolog 解决原子性、一致性、持久性
• 锁 + MVCC 解决隔离性

redo log 如何保证持久性 【物理日志】

重做日志，记录事务提交时数据页的物理修改，用来实现事务的持久性

redo log buffer 在内存中 通过后台线程 同步到磁盘中的 redo log file 中用于持久化，即使缓存池数据同步到磁盘时失效，磁盘也会根据redo log file 文件来恢复数据进行持久化

undo log 如何保证原子性 【逻辑日志】

回滚日志，用于记录数据被修改前的信息，用于后续的 回滚事务、MVCC

undo记录的是补偿SQL，即如果执行的是delete语句，其就会记录对应的insert语句

• undo log 销毁： undo log 在事务提交后不会立刻删除，因为undo log可能会用于MVCC

• undo log 存储： 存储在rollback segment 回滚段中。

11.4 MVCC

mvcc 概念

多版本并发控制。维护一个数据的多个版本，使读写操作没有冲突，快照读为mysql实现mvcc提供了一个非阻塞读功能

实现mvcc依赖于数据库中的三个隐式字段 、 undo log ，readView

• 当前读： 读取的是记录的最新版本，加锁都是当前读。

• 快照读： 简单的select不加锁，读取的是可见版本，有可能是历史版本，非阻塞读

  • RC隔离级别：每次select都生成一个快照读。
  • RR隔离级别：开启事务后第一次读时才生成一个快照读，后面都共用这一个快照读。
  • Serializable隔离级别：快照读都退化为当前读，因为该隔离级别是串行化，阻塞。

（一）隐藏字段

隐藏字段	含义
DB_TRX_ID	最近修改事务的id
DB_ROLL_PTR	回滚指针
DB_ROW_ID	没有主键生成的隐藏主键

（二）undo log 版本链

（三）readView

readView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护正在活动的事务（未提交的事务）

四个核心字段

字段	含义
m_ids	当前活跃事务的id
min_trx_id	活跃事务中最小的id
max_trx_id	预分配事务id，最大事务id+1
creator_trx_id	readView创建者事务的id

版本链事务提取的规则

演示

MVCC实现总结

12、系统数据库

数据库	含义
mysql	存储mysql运行的基本信息
information_schema	提供各种元数据的各种表和视图，包括类型及访问权限
performmance_schema	底层监控功能，性能参数
sys	性能调优和诊断的视图

13、常用的工具

1. mysql：mysql客户端工具

1. mysqladmin：mysql管理工具

1. mysqlbinlog：二进制文件查看工具

1. mysqlshow：查看信息工具

1. mysqldump：数据备份工具

1. mysqlimport / source：数据导入工具

qiumin

MySQL运维

1、日志

Mysql日志包括 错误日志、二进制日志、查询日志、慢查询日志

1.1 错误日志

概述

• 错误日志是mysql中最重要的日志之一，记录了当mysql启动和停止时，以及服务器在运行中出现的任何严重错误的相关信息，当服务器不能正常启动时，可以查看该日志。
• 默认为开启的，日志文件为 mysqld.log

show  variables like '%log_error%'  #查看该日志

1.2 二进制日志

概念

二进制日志（binlog）记录了所有的DDL（数据定义语言）语句和DML（数据操纵）语言，但不包括数据查询语句。

作用：

• 灾难时的数据恢复。
• MySQL的主从复制。
• 在MySQL8版本中，默认二进制日志是开启的。binlog.000001该格式

show variables like '%log_bin%';

日志格式： 【my.cnf配置文件中修改】

日志格式	含义
statement	基于SQL语句的日志记录，记录的SQL语句，对数据进行修改的SQL都会记录在日志文件中
row	基于行的日志记录，记录的是每一行的数据变更（默认)
mixed	混合statement、mixed

查看日志格式

show variables like 'binlog_format';

日志查看

mysqlbinlog [参数选项] logname；

• -d：指定数据库名称，只列出指定数据库相关操作。
• -o：忽略日志中的前n行。
• -v：将行事件重构为SQL语句。
• -w：将行事件重构为SQL语句，并输出注释。

日志删除

指令	含义
reset	删除整个binlog日志
purge master logs to 'binlog.* * * * ’	删除编号为binlog.* * * *之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss ’	删除时间’yyyy-mm-dd hh24:mi:ss ’ 之前的日志

可以在配置二进制文件的过期时间，设置到时间自动删除。

show  vartables like '%binlog_expire_logs_seconds%';

1.3 查询日志

记录客户端所有的SQL语句，默认为关闭状态，需在配置文件【my.cnf】中手动开启

1.4 慢查询日志

概述

记录的是超过规定时间的SQL语句，【long_query_time设置，默认为10 s】

#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2

2、主从复制

概念

• 主从复制是指将主数据库【master】的DDL和DML操作通过二进制文件传到从数据库服务器【slave】上，然后从库服务器上对这些日志重新执行（也叫重做），从而保证从数据库与主数据库的数据一致。
• mysql支持一台主库同时向多个从库进行复制，从库同时也可以作为其他从库的主库，构成链状复制，主库可写可查询，从库只能查询

作用：

1. 主库出现故障时可以快速的切换到从数据库上提供服务，但不能进行写操作。
2. 可以实现读写分离，降低主数据库的访问压力。
3. 可以在从库上进行数据备份，避免备份期间影响主库的服务。

主从复制原理

• master事务提交时，会把数据的变更记录在binlog二进制文件中。
• 从库的io后台线程从主库读取binlog日志 文件写入从库的中继日志relay log 中。
• 从库的sql线程读取中继日志并执行其中的SQL，将数据的变更同步到自己的数据库。

搭建主从复制 【两台服务器】

（一）主库搭建配置

1. 修改配置文件 my.cnf

#mysql的服务id，只要保证在一个集群中id唯一即可
server-id = 1

# 是否只读，1代表只读，0代表读写
read-only = 0

#忽略是数据库，指不需要同步的数据库
binlog-ignore-db = haha

#指定同步的数据库
binlog-do-db = qiumin

#有写入操作时更新二进制日志文件
log-slave-updates

1. 重启mysql服务让配置生效 【systemctl restart mysqld】
2. 登录mysql，创建远程连接的账号，并授予主从复制的权限，从库需要通过该账号连接到主库

#创建用户、密码
create user 'qiumin'@'%' identified with mysql_native_password BY 'Root@123456';

#给该账号赋予主从复制的权限
grant replication slave on *.* to 'qiumin'@'%';

1. 查看二进制日志坐标

show master status

#字段解释：
#file:从哪个日志文件开始推送日志文件
#position：从文件中哪个位置开始推送日志
#binlog-ignore-db：指不需要同步的数据库

1. 启动主库服务 【systemctl start mysqld】

（二）从库搭建配置

1. 修改配置文件

#mysql的服务id，只要保证在一个集群中id唯一即可
server-id = 1

# 是否只读，1代表只读，0代表读写
read-only = 0

1. 重启mysql服务让配置生效 【systemctl restart mysqld】
2. 登录mysql，设置主库配置即连接的主库

#mysql版本8.0.23的语法
change replication source to source_host='主库ip' source_user='用户名' source_password='密码' 
source_log_file='二进制文件名' source_log_pos='位置 position'

#mysql版本8.0.23之前版本的语法
change master to master_host='主库ip' master_user='用户名' master_password='密码' 
master_log_file='二进制文件名' master_log_pos='位置 position'

参数名	含义	8.0.23版本之前
source_host	主库的ip	master_host
source_user	连接主库的用户名	master_user
source_password	用户名密码	master_password
source_log_file	binlog日志文件名	master_log_file
source_log_pos	binlog日志文件位置	master_log_pos

1. 开启同步操作

start replica;  #8.0.22之后
start slave；   #8.0.22之前

1. 查看主从同步状态，是否配置成功

show replica status;   #8.0.22之后
sjow slave status;     #8.0.22之前

（三）测试

• 当向主库中插入数据提交事务后，从库中也可以查到该插入的数据，即数据已经同步到从库。
• 主库可写可读，从库只能都。

3、分库分表

概念

随着互联网的快速发展，数据量越来越大，采用单数据库进行数据存储会存在一下瓶颈：

• IO瓶颈：热点数据太多，数据库的缓存不足，效率低
• CPU瓶颈：排序、分组、连接查询、聚合统计会消费大量的cpu资源

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能，从而达到提升数据库的性能

垂直

（一）垂直分库

• 垂直分库： 以表为依据，根据不同的表拆分到不同的数据库
  • 每个库的表结构不一样
  • 每个库的数据不一样
  • 所有库的并集是全量数据

（二）垂直分表

• 垂直分表： 以字段为依据，根据字段属性将不同的字段拆分到不同的表中
  • 每个表的表结构不同
  • 每个表的数据不一样，通过一列（主键外键）关联
  • 所有表的并集为全量数据

水平

（一）水平分库

• 水平分库 ：以表为依据按照一定的策略，将一个库中数据拆分到多个库中
  • 每个库的表结构一样
  • 每个库中的数据都不一样
  • 所有库的表中数据的并集为全量数据

（二）水平分表

• 水平分表 ：以字段为依据，按照一定的策略，将表的数据分到多个表中
  • 每个表的表结构一样
  • 表中数据都不一样
  • 所有表中数据的并集为全量数据

4、MyCat

概述

mycat是开源的、活跃的、基于java语言编写的MySQL 数据库中间件。可以像使用mysql一样来使用mycat

优势： 性能可靠稳定

mycat下载官网： http://dl.mycat.org.cn/

• mycat： 数据库分库分表的中间件，不用调整代码即可实现分库分表，支持多种语言。
• shardingJDBC： 数据库分库分表的中间件，基于AOP编程，只支持java语言，性能高。

4.1 入门配置

分片配置 【schema.xml】

配置分片，逻辑表逻辑库

schema.xml作为mycat最重要的配置文件之一，涵盖了mycat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置

• schema标签：逻辑库

  <!--逻辑库的配置-->
	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
			 <!--逻辑表的配置-->
			<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>

• datanode标签：分片节点

<!--分片节点-->
	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />

• datahost标签：节点数据库

<!--分片节点的数据库-->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
					  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">			  
			<heartbeat>select user()</heartbeat>
			<writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
					<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
			</writeHost>
	</dataHost>

balance： 负载均衡 writeType： 写操作分发方式 dbDriver： 数据库驱动 支持native、jdbc

服务配置 【server.xml】

配置，登录的用户可以操作的逻辑库，即授权

启动测试

mycat start    #启动  占用端口号：8066
mycat stop     #停止

查看日志看mycat是否启动成功

tail -f logs/wrapper.log

4.2 垂直分库

字典表中，在多个业务模块中都可能会用到，可以将其设置为全局表，利于业务操作

< table > 标签中加上 type="global" 属性 < table type="global" >

4.3 水平分表

4.4 分片规则

在水平分表时，会存在数据库的节点分配问题，这时就需要一定的分片的规则，根据指定字段及其配置的范围与数据节点的对应情况，来决定该数据属于哪一个分片 【rule.xml】

一般情况一个rule 对应一个**< tableRule >,一个< tableRule >** 对应一个**< function >** ,一个**< function >** 对应一个txt文件

• **分片规则-范围 ** 【rule="auto-sharding-long" 】

根据字段的值在哪个节点的范围

• 分片规则-取模 【rule="mod-long"】

字段值与节点数的取模运算

• 分片规则-一致性hash 【rule="sharding-by-murmur"】

相同的哈希因子计算值被划分到相同的分区表中，不会因为分区节点的增加而改变分区的位置

注意：字段值必须为数字类型

• 分片规则-枚举 【rule="sharding-by-intfile"】

指定数据放在指定节点

• 分片规则-应用指定 【rule="sharding-by-substring"】

运行阶段应用自主决定路由到哪个分片，直接根据字符串截取，但截取的必须为数字

• 分片规则-固定分片hash算法 【rule="sharding-by-long-hash"】

id的低10位与10个1作&运算，得到结果转为十进制，在哪个范围就在哪个分片中 【可实现均匀分配也可非均匀】

注意：字段值必须为数字类型

• 分片规则-字符串hash解析 【rule="sharding-by-shardinghash"】

截取字符串中指定位置上的子字符串，进行hash算法，算出分片

• 分片规则-按日期分片 【rule="sharding-by-date"】

指定日期分片

• 分片规则-按月份分片 【rule="sharding-by-month"】

按月份分片

5、Mycat的管理和监控

mycat的运行原理

mycat的管理

mycat默认开通2个端口，可以在 server.xml 中进行配置：

• 8066 数据访问端口，进行DML和DDL操作。
• 9066 数据管理端口，即mycat服务管理控制功能，用于管理mycat的整个集群状态。

mysql -h ip地址 -P 9066 -uroot -p123456

查看mycat提供的管理命令：show @@help

mycat监控 【mycat-eye】

• mycat-web（mycat-eye） 是对mycat-server提供监控服务，功能不局限于对mycat-server使用。通过JDBC连接对mycat、mysql监控，监控远程服务器。
• mycat-web运行过程中需要依赖zookeeper注册中心，所以需要先安装zookeeper。
• zookeeper官网：https://zookeeper.apache.org
• mycat-web官网：http://dl.mycat.org.cn/mycat-web-1.0/
• 启动zookeeper，在端口2181运行，再启动mycat-web 【mycat-web的配置文件已经关联了zookeeper-------> WEB-INF/classes/mycat.properties 】
• 访问：地址+端口+mycat 就会进入监控页面

6、读写分离

概念 【主从复制上面有】

读写分离，将数据库的读和写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，就可以减轻·数据库的压力，mycat可以实现该功能。

（一）一主一从

balance参数说明：

参数值	含义
0	不开启读取分离
1	全部readhost与备用writehost都参与select语句的负载均衡
2	随机分发
3	writehost不承担读压力

（二）双主双从

有备用的主机，当某个主机宕机为了防止写服务停滞，加一个备用主机提高高可用性

双主双从搭建过程：先搭建两组一组一从，在将两个主库相互复制配置

注意：备用主机开始时也就相当于从机，也要登录到刚开始主库。

配置：

参数说明：

• balance=“1” ： 负载均衡策略
• writetype=“0”：指定刚开始的时的主库节点
• switchtype=“1”：开启自动切换主库，当主机宕机时

qiumin