MySQL6_1其他主题

发表于 更新于 分类于

常用服务器配置

  • 启动选项和系统变量
    启动选项是运维启动 MySQL 时传入的一些参数，包括命令行启动选项和配置文件 my.cnf
    系统变量会影响 MySQL 进程的运行行为，大部分是由启动选项初始化的，有些是运行时自动生成的
  • 查看系统变量
    show [GLOBAL|SESSION] variables [like 匹配的模式];
  • 配置文件中配置组的概念
  • 配置作用范围
    1、GLOBAL 指配置文件或命令行启动选项设置的系统变量
    2、SESSION（LOCAL）刚连接时会被初始化为 GLOBAL 的变量，可以通过以下命令来设置
    SET [GLOBAL|SESSION] 系统变量名 = 值;
  • 状态变量
    指关于程序运行状态的变量，是只读的，不能手动修改
    比方说 Threads_connected 表示当前有多少客户端与服务器建立了连接，Handler_update 表示已经更新了多少行记录
    SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

InnoDB 统计数据

两种统计数据

InnoDB 中有两种统计数据：
1、永久性：服务器重启也不会消失，这些数据被存储到了innodb_table_stats和innodb_index_stats这两张表中；
2、非永久性：重启即消失。
可以通过服务器的innodb_stats_persistent变量来查看这个统计数据的方式。

innodb_table_stats 统计方式

1、n_rows(一个表中的记录行数)统计项的收集
按照一定算法选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的 n_rows 值
2、clustered_index_size 和 sum_of_other_index_sizes

  • 从数据字典里找到表的各个索引对应的根页面位置。
    系统表 SYS_INDEXES 里存储了各个索引对应的根页面信息。
  • 从根页面的 Page Header 里找到叶子节点段和非叶子节点段对应的 Segment Header。
    在每个索引的根页面的 Page Header 部分都有两个字段：
    PAGE_BTR_SEG_LEAF：表示 B+树叶子段的 Segment Header 信息。
    PAGE_BTR_SEG_TOP：表示 B+树非叶子段的 Segment Header 信息。
  • 从叶子节点段和非叶子节点段的 Segment Header 中找到这两个段对应的 INODE Entry 结构。
    这个是 Segment Header 结构：
  • 从对应的 INODE Entry 结构中可以找到该段对应所有零散的页面地址以及 FREE、NOT_FULL、FULL 链表的基节点。
    这个是 INODE Entry 结构：
  • 直接统计零散的页面有多少个，然后从那三个链表的 List Length 字段中读出该段占用的区的大小，每个区占用 64 个页，所以就可以统计出整个段占用的页面。
    这个是链表基节点的示意图：
  • 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数，它们的和就是 clustered_index_size 的值，按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是 sum_of_other_index_sizes 的值。

innodb_index_stats 统计方式

1
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'single_table';
  • n_leaf_pages：表示该索引的叶子节点占用多少页面。
  • size：表示该索引共占用多少页面。
  • n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的 NN 长得有点儿怪呀，啥意思呢？
    其实 NN 可以被替换为 01、02、03… 这样的数字。比如对于 idx_key_part 来说：
    n_diff_pfx01 表示的是统计 key_part1 这单单一个列不重复的值有多少。
    n_diff_pfx02 表示的是统计 key_part1、key_part2 这两个列组合起来不重复的值有多少。
    n_diff_pfx03 表示的是统计 key_part1、key_part2、key_part3 这三个列组合起来不重复的值有多少。
    n_diff_pfx04 表示的是统计 key_part1、key_part2、key_part3、id 这四个列组合起来不重复的值有多少。
  • 在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size 列就表明了采样的页面数量是多少。

基于内存的非永久性统计数据

开启非永久性统计数据的方法：
1、将innodb_stats_persistent的值设置为 OFF；
2、直接在创建表或修改表时设置STATS_PERSISTENT属性的值为 0；

MySQL Server 统计数据

Server 层而不是 InnoDB（存储引擎层）统计数据。
1、查看连接数配置

1
show variables like '%max_connections%'

2、查看当前连接数

1
show full processlist;

数据恢复

数据的误删基本分以下几种情况：

  1. 使用 delete 语句误删数据行；
  2. 使用 drop table 或 truncate table 误删表；
  3. 使用 drop database 误删数据库；
  4. 使用 rm 命令误删整个 MySQL 实例。

误删行

使用 Flashback 工具通过闪回把数据恢复。
Flashback 恢复数据的原理，是修改 binlog 的内容（事务里的语句顺序颠倒、语句的语义颠倒比如 insert 变成 delete），拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。

误删库 / 表

误删库表的情况不能使用 Flashback 恢复，因为即使配置 binlog_format=row，truncate/drop 语句在 binlog 中也只会记录一条对应的语句，而用这些信息是无法恢复数据的。
这种情况下，恢复需要使用全量备份，加增量日志。这个方案要求线上有定期的全量备份，并且实时备份 binlog。

rm 删除数据

仅仅删除某个节点的数据的情况，HA 系统可以选出新的主库，从而保证整个集群的正常工作。
之后，我们可以在这个被删节点上把数据恢复回来，再接入整个集群。

中断查询

有时候因为查询耗时过长，或出现死锁等待，我们不得不提早终止执行 SQL 的线程，可以通过information_schema.processlist 和 performance_schema.threads这两张表来查看正在执行的线程：

  • processlist 表中每一行对应一个客户端连接，也对应一个线程；
  • threads 每一行对应一个线程。

kill query pid可以杀死线程，但是客户端的连接还在，可以看到被 kill 后该连接进入了 Sleep 状态：

1
2
# Id, User, Host, db, Command, Time, State, Info
'494633', 'beta', '192.168.19.142:56193', 'ds_0', 'Sleep', '26', '', NULL

kill pid可以中断连接，执行后再用processlist就找不到那个 pid 了。

在客户端 Ctrl + C 并不能中断服务器线程，只能中断客户端进程，

大表查询

Server 层

MySQL 使用缓存来保证一次性查询大量数据的情况下不会把服务器内存打满，服务器并不需要保存一个完整的结果集。取数据和发数据的流程如下：
MySQL-查询结果发送流程

  1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
  2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
  3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
  4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

从上面的流程可知，MySQL 一次查询占用的内存是有限的，最大是**min(net_buffer_length, socket send buffer)**，即不能超过 net_buffer_length 和 socket send buffer；

存储引擎层（InnoDB）

InnoDB 使用 Buffer Pool 管理内存数据页，如果 Buffer Pool 命中率足够高，那么大部分时候是不需要读磁盘的，直接从内存拿结果，可以加快查询速度。
执行 show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率，一般一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要在 99% 以上。
Buffer Pool 的空间是有限的，新旧页面的更替是通过 LRU 算法控制的，但 InnoDB 中的 LRU 并不是单纯的新页面替换老页面（因为这样相当于每次大查询都会把整个 Buffer Pool 都刷新一遍），而是将 LRU 链表分成了 young 区和 old 区，页面第一次被访问时会被添加到 old 区，old 区的页面如果是短期内被多次访问，则其不会被移动到链表的头部（young 区），会很快被淘汰掉。

临时表

1
2
3
4
create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

临时表特性：

  1. 不同 session 的临时表是可以重名的，常被用在复杂查询的优化过程中，比如有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
  2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的使用场景

union 语句

表 t1 在执行前已初始化插入了 1~1000 的数据。

1
(select 1000 as f) union (select id from t1 order by id desc limit 2);

MySQL-union执行流程
上面语句将两个子查询的结果合并去重，union 合并时会生成临时表，这可以通过 explain 来验证。

group by

1
select id%10 as m, count(*) as c from t1 group by m;

MySQL-groupby执行流程
上面语句先创建内存临时表，表里有 m 和 c 两个字段，主键是 m，扫描 t1 索引 a，将id%10的结果插入临时表，如果出现主键冲突则计算 c 值+1。

  1. 加索引
    默认情况下id%10是无序的，所以需要先在临时表中统计排序后再返回，但是如果原表本身就是有序的，则不需要临时表、也不需要额外排序了，实际上只要引入索引就可以解决这个问题，因为索引是有序的。
  2. 如果不能加索引，也可以加一列 generated column
    MySQL5.7 支持 generated column 机制，并可以在该列上创建索引：
    1
    alter table t1 add column z int generated always as(id % 100), add index(z);
    上面的 group by 语句可以改成如下的形式：
    1
    select z, count(*) as c from t1 group by z;
  3. 如果不需要排序，可以显式声明忽略排序
    如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null
  4. 数据量小时使用内存临时表
    如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
  5. 数据量大时使用磁盘临时表
    如果数据量较大，因为内存临时表的空间是有限的，当达到上限后就会转到磁盘内存表，与其这样转一下，不如直接使用磁盘内存表。
    因此，如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

Memory 引擎

Memory 引擎与 InnoDB 引擎区别

  1. 数据组织方式
    InnoDB 引擎采用 B+树来组织数据，主键是有序存储的。InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
    Memory 引擎的数据和索引是分开的，数据以数组的方式单独存放，而主键索引是 hash 索引，存的是每个数据的位置，索引上的 key 并不是有序的：
    MySQL-Memory引擎数据组织
    Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。
  2. 存放顺序
    InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
  3. 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
  4. 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
  5. InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
  6. InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

hash 索引和 B-Tree 索引

内存表也支持 B-Tree 索引：

1
alter table t1 add index a_btree_index using btree (id);

MySQL-内存表B-Tree索引
可以查看以下两个语句的输出：

1
2
3
4
-- 命中索引a_btree_index，因此输出结果是有序的
select * from t1 where id < 5;
-- 强制使用主键id索引，因此是无序的
select * from t1 force index (primary) where id < 5;

不推荐在生产环境使用 Memory 引擎

  1. 锁粒度问题
    内存表不支持行锁，只支持表锁，只要这张表上有更新，就会堵住所有其他在这张表上的读写操作，因此在处理并发事务时性能也不会太好。
  2. 数据持久化问题
    因为数据被存放在内存中，数据库重启时所有的内存表都会被清空。

虽然一般情况下不适合使用内存表，但是还有一种情况可以考虑使用内存表：用户临时表，只是临时数据，如果数据可控，不会消耗过多内存的情况下，可以考虑使用内存表。
内存临时表（通过 create temporary table 语句创建）刚好可以无视内存表的两个不足，主要是下面的三个原因：

  1. 临时表不会被其他线程访问，没有并发性的问题；
  2. 临时表重启后也是需要删除的，清空数据这个问题不存在；
  3. 备库的临时表也不会影响主库的用户线程。

备份

  • 将数据导出成一组 insert 语句
    1
    mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
    恢复：
    1
    mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"
  • 导出 CSV 文件
    1
    select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
    恢复，将数据导入到目标表 db2.t 中：
    1
    load data infile '/server_tmp/t.csv' into table db2.t;
  • 物理拷贝
    不能通过直接拷贝表的.frm 文件和.ibd 文件来实现物理拷贝，因为一个 InnoDB 表除了包含这两个物理文件外，还需要在数据字典中注册，直接拷贝的情况下系统不会识别。
    在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。
    1. 执行 create table r like t，创建一个相同表结构的空表；
    2. 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
    3. 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
    4. 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；
    5. 这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
    6. 执行 unlock tables，这时候 t.cfg 文件会被删除；
    7. 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

这三种方法各有优劣：

  1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
    • 必须是全表拷贝，不能只拷贝部分数据；
    • 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；……
    • 由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。
  2. 用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。
  3. 用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

MySQL 中的自增 ID

表的自增 id

我们经常给表的主键加上自增属性，用于唯一标识一条记录，但是因为自增值达到上限后再申请得到的值不变，因此自增字段的范围应该略大一些，尽可能创建成bigint unsigned。

row_id

如果没有指定主键，InnoDB 会创建一个不可见的、长度为 6 字节的 row_id，超过上限后再申请时会得到 0，如果新写入的行的 row_id 在表中已存在，则会直接覆盖原有的行，因此，最好优先使用自增 ID 而不是 row_id。

Xid

Xid 用于唯一标识一个事务。Xid 的值由一个内存变量 global_query_id 给出，重启后清零，但是因为每次重启时 binlog 都会重新生成，所以 binlog 中的 Xid 也不会重复。global_query_id 的长度为 8 个字节，除非 MySQL 实例一直执行了2^64 - 1次查询且期间没有重启，不然不会出现 Xid 重复的情况。

max_trx_id

Xid 由 server 层维护。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。
InnoDB 内部维护了一个 max_trx_id 全局变量，每次需要申请一个新的 trx_id 时，就获得 max_trx_id 的当前值，然后并将 max_trx_id 加 1。

InnoDB 事务在读操作时不会申请 trx_id，trx_id 的值就是 0，只有在加锁或执行写操作时才会申请。
只读事务不申请 trx_id 的原因是只读事务不影响事务的可见性判断，且能减少 trx_id 的申请次数、减少并发事务申请 trx_id 的锁冲突。

MVCC 判断数据可见性的核心思想：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。

thread_id

系统保存一个全局变量 thread_id_counter，每新建一个连接就将 thread_id_counter 赋值给这个新连接的线程变量。