《深入浅出MySQL 数据库开发、优化与管理维护》第3版 阅读笔记

第9章 索引的设计和使用

9.6 索引在MySQL8.0 中的改进

不可见索引

索引默认是可见的，可以在创建表时增加invisible关键字来创建不可见索引

create table t1(
i int,
j int,
index i_idx(i) invisiable
)engine=InnoDB;

create index i_idx on t1(i) invisiable;
alter table t1 add index i_idx(i) invisiable;
alter table t1 add index i_idx(i) visiable;

当数据库中数据量达到一定程度之后，删除或重建索引会造成巨大开销，当发现某个索引不需要时，可以将其设置为invisiable，当没有sql用到时，再将其删除。当发现新增的索引对系统带来了负面影响时，可以将其设为invisiable。

倒叙索引

desc

第14章 MySQL分区

注意：

无论哪种MySQL分区类型，要么分区表上没有主键/唯一索引，要么分区表的主键/唯一索引都必须包含分区间，也就是说不能使用主键/唯一键字段（当存在时）之外的其他字段分区。

分区类型：

• range

create table emp(...store_id int not null)
partition by range (store_id)(
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p0 values less than (30)
);

• List

create table table_name(...category int)
partition by list(category)(
    partition p0 values in (3,5),
    partition p1 values in (1,10),
    partition p2 values in (2),
    partition p3 values in (6),
);

• Columns

-- 只支持整型类型
-- 支持date和datetime
-- 支持char，varchar，binary和varbinary
create table (a int, b int)
partition by range column(a,b)(
    partition p0 values less than (0,10),
    partition p1 values less than (10,10),
)
-- range columns 分区键的比较是多列排序，根据排序结果分区存放数据

• hash分区

-- 常规hash分区，取模运算
-- 线性hash分区，线性的2的幂的运算法则
create table emp(...store_id)
partition by [linear] hash (store_id) partitions 4;

• key分区（需使用MySQL中的hash函数）

create table table_name(job varchar(30)...)
partition by key(job) partitions 4;

-- key可以为空，为空时会选取主键id或非null唯一索引作为分区键。

14.2.7 MySQL分区处理NULL值的方式

注意：

• range分区中，null值会被当做最小值来处理。

• list分区中，null值必须出现在枚举列表中，否则不被接受。

• Hash/key分区中，null值会被当做零值处理。

14.3 分区管理

14.3.1 range和list分区管理

• 删除分区

alter table table_name frop partition p1;

• 重定义分区

alter table expenses reorganize partition p4,p5,p6 into (
    partition p4 values in(6,11),
    partition p5 values in(7,8)
);

14.3.2 hash和key分区管理

• 减少分区数量

alter table table_name coalesce partition 2;

• 增加分区数量

alter table table_name add partition partitions 8; -- 增加8个分区

分区表达式支持的函数

分区表达式中，并不是能使用所有的函数，只支持以下函数：

ABS()
CEILING()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP(),(使用TIMESTAMP类型的列时)
WEEKDAY()
YEAR()
YEARWEEK()

第15章 SQL优化

优化sql的一般步骤：

• 通过show status命令了解各种sql的执行频率

show global status like 'Com_%';

• 定位执行效率较低的sql语句（慢日志，show processlist）

• 通过explain分析低效的sql执行计划

15.1.4 (已弃用)通过show profile分析SQL

• 检查是否支持show profile

 show variables like 'have_profiling';

• 默认profiling是关闭的，在session级别开启profiling

set profiling=1;

• 使用profiling

-- 1.执行sql

show profiles;
show profile for query n;

-- 查看各个state状态的耗费时间
select 
    state, sum(duration) as total_r, 
    round(100 * sum(duration)/(select sum(duration) from information_schema.profiling where query_id=24),2) as percent,
    sum(1) as call_time,
    sum(duration)/count(*) as "R/Call"
from 
    information_schema.profiling 
where query_id =24 
group by 
    state 
order by   
    total_r desc;
-- 根据各个状态的消耗时间，进一步选择all,cpu,block io, context switch, page faults等明细类型查看MySQL在什么资源上耗费了过高的时间
show profile block io for query 24;
show profile all for query 24;
show profile cpu for query 24;
show profile content switch for query 24;
show profile page faults for query 24;

15.1.5 通过trace 分析优化器如何选择执行计划

执行完SQL之后执行：

select * from information_schema.optimizer_trace \G

存在索引而不能使用索引的典型场景

• 以%开头的like查询

• 数据类型出现隐式转换（如果是匹配值向条件谓词字段转换，则还可以走索引。如果是条件谓词字段向匹配值转换，则不走索引）

• 复合索引情况下，不满足最左匹配原则

• 优化器认为全表扫描更快，则不使用索引

• 条件谓词用or连接，且有的字段有索引，有的没有。（本质是为or的每个字段生成扫描区间，如果评估出来的扫描区间是负无穷到正无穷就不会走索引）

15.2.3 查看索引使用情况

show status like 'Handler_read%';

• Handler_read_key:该值很高则表明被索引值读的次数高，索引利用率高。如果低，则表明增肌索引得到的改善性能不高，因为索引不经常使用；

• Handler_read_rnd_next:该值越高意味着查询运行低效。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，该值高，则通常说明表索引不正确或写人的查询没有利用素引。

15.3 简单使用的优化方法

15.3.1 定期分析表和检查表

• analyze table table_name;分析表，本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。

• check table table_name;检查表的作用是检查一个或多个表（视图）是否有错误。

15.3.2 定期优化表

• optimize table table_name;这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。InnoDB会将该命令转换成重建表和分析表两个操作，加锁时间仅仅在整个工作的prepare和commit阶段做短暂的加锁工作，对于表的读写几乎没有影响。

注意

analyze,check,optimize,alter table执行期间对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关操作。

15.4 常用SQL的优化

15.4.1 大批量导入数据

1. 对于InnoDB引擎，保证导入的数据按照主键有序。

2. 导入数据前执行set unique_checks=0，关闭唯一性校验；在导入后执行set unique_checks=1恢复唯一性校验。

3. 如果应用使用自动提交的方式，建议在导入前执行set autocommit=0关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交。

15.4.2 优化insert语句

• 同时从同一客户端插入，应尽量使用多个值的insert语句。能减少客户端与数据库之间的连接、关闭等消耗。

• 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

• 当从一个文本文件装载一个表时，使用load data infile。这通常比使用很多insert语句快20倍。

15.4.3 优化order by语句

Filesort的优化

MySQL中有两种排序算法：

• 两次扫描算法：将排序字段和主键id放到内存中排序，之后再根据主键回表。

• 一次扫描算法：将所有字段放入内存排序，不需要回表。需要内存足够大。

15.4.4 优化 group by语句

默认情况下，MySQL对所有group by的字段进行排序，如等同于 group by ... order by ...。如果查询包括group by，但用户想避免排序结果的消耗，则可以指定order by null，禁止排序。

15.4.5 优化join操作

• 嵌套循环

• 索引嵌套循环（匹配内层表索引）

• 块嵌套循环

• Hash Join (MySQL8.0.22之后支持）

15.4.8 优化分页查询

原查询

select film_id, description  from film order by title limit 50,5 \G

1. 第一种优化思路

在索引上完成排序分页的操作，然后根据主键关联回原表查询所需要的其他列内容。

select 
    a.filmm, a.description
from 
    film a 
inner join 
    (select film_id from film order by title limit 50,5) b
on
    a.film_id = b.film_id

2. 第二种优化思路

记录上一页的id值，然后现根据根据这个id值过滤，再limit n。

15.4.9 使用SQL提示

1. use index

通过该参数提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。(不一定会走use index的所以)

2. ignore index

   忽略一个或多个索引。

3. force index

强制

15.5 直方图

并不是所有大表的字段都需要创建直方图。通常在一些唯一值较少、数据分布不均衡、查询较为频繁、没有创建素引的字段上考虑创建直方图。虽然在创建素引有时也可以达到优化效果，但由于这类字段素引使用率低、 索引维护成本高，因此通常不会 在这些字段上单独创建索引。而直方图只需要创建一次，对数据的变更不需要实时进行维护，代价较小，更适合于此类条件的查询。

第16章 锁问题

16.3 InnoDB锁问题

16.3.2 获取InnoDB行锁争用情况

show status like 'innodb_row_lock%';
-- InnoDB_row_waits 和 InnoDB_row_lock_time_avg的值比较高则表明锁争用严重

设置监视器

set global innodb_status_output=on;
set global innodb_status_output_locks=on;
-- 长时间打开监视器会导致日志文件过大，用完及时关闭
set global innodb_status_output=on;
set global innodb_status_output_locks=on;

查看最新的状态信息

show engine innodb status \G

16.3.4 InnoDB行锁实现方式

加锁优化

两个原则、两个优化、一个bug

访问到数据才会加锁

加锁基本单位是临键锁，前开后闭(,]

索引等值查询，唯一索引，临键锁会退化成行锁

索引等值查询，向右遍历最后一个不满足条件的值时，临键锁会退化成间隙锁

InnoDB行锁特性

• 不通过索引的条件查询会锁住表中所有记录

• MySQL的行锁是针对索引加的锁，不是针对记录加的锁。（而对二级索引加锁时，是根据二级索引获取到主键值，然后对主键加锁）

• 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

• 即便在条件中使用了索引字段，是否使用索引来检索数据是由执行计划决定的。分析锁冲突时，需要检查SQL的执行计划。

16.3.6 恢复和复制的需要，对InnoDB锁机制的影响

四种复制模式

• 基于SQL语句的复制：SBL

• 基于行数据的复制：RBR

• 混合复制模式：对安全的SQL语句采用SQL语句的复制模式，对于非安全的SQL语句采用基于行的复制模式。

• 使用全局事务ID的复制（GTIDS）：主要是解决主从自动同步一致问题。

对于基于SQL语句的复制的BinLog日志

如果是CTAS这种SQL语句：insert into target_tab select * from source_tab where ...和create table new_tab ... Select ... From source_tab where...MySQL会对source_tab中的记录加锁。因为只有在事务提交的时候才会记录到binlog中，如果在执行这类语句时有其他更新的语句事务提交了，则在binlog中记录的顺序会是update语句，insert语句。（根据binlog只在事务提交时记录，这样先提交的事务在恢复的时候先执行，不存在实际执行的过程中的快照读了，因此会造成binlog恢复出来的数据和数据库中实际存储的不一样）

使用表锁需要注意的点

• 使用lock tables可以给InnoDB加表锁。但是表锁不是由InnoDB管理的，而是由MySQL Server管理的，autocommit=0且innodb_table_locks=1，InnoDB才能知道MySQL加的表锁，才能自动识别涉及表锁的死锁。

• 事务结束前不要使用unlock tables释放锁，因为其会隐含地提交事务。另外，用commit和rollback不会释放表锁，必须unlock tables；

避免死锁的常用方法

• 以相同的顺序访问表

• 批量处理数据时，事先对数据排序，保证每个线程固定的顺序来处理记录，能降低出现死锁的可能。

• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不是先申请共享锁再申请排他锁。

• repeatable-read隔离级别下，如果两个线程同时对相同记录用select ... For update加排他锁，在没有记录的条件下两个线程都会加锁成功。如果此时都尝试插入一条新记录，就会出现死锁。（查询不存在的记录时，加的是间隙锁，而间隙锁是兼容的。执行insert语句时，需要insert意向锁，和gap锁是冲突的，所以产生了死锁）

第17章 MySQL体系结构概况

缓冲

https://cloud.tencent.com/developer/article/1967962

• 缓冲池(Buffer pool)：用于优化读请求，减少读的磁盘IO

• 写缓冲(change buffer):用于优化写请求，减少写入磁盘的IO（****AWS Aurora MySQL不支持）

17.2.3 InnoDB内存优化

用一块内存做IO缓存（缓存索引页和数据页）：free list、flush list、LRU list。

LRU list分为young list（热点数据）和old list（使用频率不高的数据）。因为MySQL“预读”的存在，如果只用一个LRU list，则会导致预读时把一些热点数据频繁挤出LRU list到flush list中。

select
    round(sum(data_length + index_length) / 1024 /1024, 2) "Total Size (MB)",
    round(sum(data_free) /1024 /1024, 2) "Free Space (MB)"
from
    information_schema.tables;
    
-- 查看当前所有表占用的空间和空闲空间

调整old sublist大小

查看：show variables like '%innodb_old_blocks_pct%';

调整Innodb_old_blocks_time的大小

表示将数据从old list中移到young list 的时间间隔。只有在old list中待够innodb_old_blocks_time（ms）后才会移动到young list中。

调整用户服务线程排序缓存区

sort_merge_passes:进行归并排序的次数。归并排序是多个文件排序时的操作。

当该值过大时，可以增大sort_buffer_size的值来增大排序缓冲区。

InnoDB doublewrite

因为InnoDB的页大小（16KB）和操作系统页大小（4KB）不一样，所以极端情况会导致InnoDB一页未完全刷到磁盘上。为此，InnoDB用系统表空间的一块缓存作为doublewrite buffer。当刷脏页时，先将缓存中的数据副本写入doublewrite buffer中，然后立即刷新到磁盘（顺序写）。之后再将doublewrite buffer的数据写入各个表空间中。

Redo log是物理逻辑型日志，不是纯物理的，因此不完全具有幂等性，恢复时需要根据原始数据页+redo恢复。https://cloud.tencent.com/developer/article/1783817

17.3.3 设置log file size，控制检查点

innodb_log_file_size用于设置redo log 的大小。一般来说，平均每半个小时写满一个日志文件比较合适。

计算InnoDB每分钟的日志量：

pager grep -i "Log sequence number"

show engine innodb status \G 
select sleep(60); 
show engine innodb status \G

nopager

select round((661616830-661616800)/1024/1024) as MB; -- 用后一个值减去前一个值

然后计算半个小时的日志量。

17.3.4 调整innodb_log_buffer_size

用于调整redo log缓冲池的大小，默认是16MB。

17.4 MySQL并发相关的参数

max_connections

连接到MySQL数据库的最大数量，默认值是151。（如果状态变量connection_errors_max_connections不为0，并且一直在增长，就说明不断有连接请求因数据库连接数已达到最大允许的值而失败，因考虑提升max_connections）。

back_log

控制监听TCP端口时设置的积压请求栈大小。默认是50+(max_connections/5)，最大值不超过900。增大该值用于较短时间内处理大量连接请求。

table_open_cache

每一个SQL执行线程至少都要打开1 个表缓存，参数table_open_cache 控制所有SQL 执 行线程可打开表缓存的数量。

thread_cache_size

innodb_lock_wait_timeout

第19章 优化数据表的设计

19.1.2 通过拆分提高表的访问效率

• 垂直拆分

• 水平拆分

  • 分区

  • 分表

19.2 数据库应用优化

第20章 PS/SYS数据库

20.2 SYS库

• SYS表的统计维度

-- 使用上述关键词查询相关的表
select table_name from information_schema.tables where table_schema ='sys' table_name like 'host%' ;

主机相关

select * from host_summary \G

• host:连接到MySQL实例的主机

• statements:主机IP执行总的SQL语句数量

• statement_latency:主机IP执行SQL语句消耗的总时间

• file_io_latency:主机IP发生文件等待事件消耗的总时间

• current_connections:主机IP当前总的连接数

• current_memory:实例为主机IP当前分配的内存

Innodb相关

• innodb_buffer_stats_by_%:分析哪些对象占用缓冲池的内存较多

• innodb_lock_waits:用于行锁等待分析

连接数和会话相关

sys.processlist和sys.session的信息比information_schema.processlist全面。

db相关

查看冗余索引表：schema_redundant_indexes

select * from schema_redundant_indexes \G

SQL语句相关

表：statement_analysis

select * from statement_analysis where  ... order by ... limit n \G

• query:替换SQL语句变量并美化

• db:应用执行SQL时连接的db名

• exec_count:SQL执行总次数

• rows_examined:SQL执行查询的总记录数

• rows_affected:SQL执行影响的总记录数

第24章 MySQL日志

日志分类：错误日志、二进制日志、查询日志、慢查询日志、中继日志、元数据日志。前四个比较常用。

24.2.3 日志的删除

• reset master

MySQL客户端执行reset master，删除所有binlog，新生成的日志从编号000001开始。
8.0中支持reset master to+编号，自定义新日志的开始编号。

• purge master logs to 'mysql-bin.***'

删除mysql-bin.***之前编号的所有日志，不包括mysql-bin.***

• purge master logs before 'yyyy-mm-dd hh24:mi:ss'

删除日期为yyyy-mm-dd hh24:mi:ss之前的所有日志

• 设置过期天数，使其自动删除

--expire_logs_day=# 修改my.cnf配置文件
set global expore_logs_day=3

• binlog_expire_logs_secondsMySQL 8.0版本的参数，binlog过期时间可以精确到秒

24.4 慢查询日志

默认管理语句和不使用索引的查询不记录到慢查询日志。

--log-queries-not-using-indexes 开启监控查询语句
--log-slow-admin-statements 开启监控管理语句
--long_query_time 设置记录慢查询日志的时间阈值，源码中超过该值才会记录，等于不会记录

pt-query-digest工具汇总查看慢日志

25 章 备份与恢复

逻辑备份和恢复

mydump备份文件一共有三类：db.table.sql文件，存放表数据；db.table-schema.sql文件，存放表结构；metadata文件，记录备份的开始时间和结束时间，以及binlog记录文件名、位置和GTID信息。可以根据metadata中的信息进行完全恢复或基于位置的恢复。

完全恢复例子

/*
-- 备份数据库
mysqldump -uroot -p --single-transaction -F employees > employees.dmp

-- 恢复备份
mysql -uroot -p employees < employees.dmp

-- 使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysqlbinlog ..-bin.0... | mysql -u root -p employees
*/



-- 备份
sudo mydumper -u root -p root -P 3340 -h 127.0.0.1 --database yanhao  -G -R -E -c -t 8 -o /data/dba/yanhao/application/backup/test_table_back
-- 恢复
myloader -u root -p root -P 3340 -h 127.0.0.1 -o -d /data/dba/yanhao/application/backup/test_table_back
-- 中间插几条数据

-- 查看metadata，备份过程中binlog处于那个文件中
-- binlog恢复，2024-07-24 03:24:30之前的数据有点问题
sudo /data/dba/yanhao/application/mysql/mysql8/bin/mysqlbinlog --start-datetime="2024-07-24 03:24:30" /data/dba/yanhao/application/mysql/dataMysql57/binlog.000016  | /data/dba/yanhao/application/mysql/mysql8/bin/mysql -uroot -p -h127.0.0.1 -P3340

基于时间点恢复

-- 跳过出问题sql语句的那个时间点
mysqlbinlog  --stop-datetime="2018-10-20 9:59:59" binlogPath | mysql -uroot -p

mysqlbinlog  --start-datetime="2018-10-20 10:01:00" binlogPath | mysql -uroot -p

基于位置的恢复

-- 现将小范围时间的数据导出来
mysqlbinlog  --start-datetime="2018-10-20 9:55:00"  --stop-datetime="2018-10-20 10:05:00" binlogPath > tmp.sql

-- 找出有问题语句的前后位置编号
mysqlbinlog  --stop-position="xxx" binlogPath | mysql -uroot -p

mysqlbinlog  --start-position="xxx" binlogPath | mysql -uroot -p

26 MySQL权限与安全

26.1 MySQL权限管理

账号管理

1. 创建账号

   1. Create user + grant方式

   2. 直接操作授权表

create user testuser1@localhost identified by 'passwd';

grant all privileges on *.* to testuser1@localhost;

-- with grant option表示可以将自己的权限授予其他用户
grant all privileges on *.* to testuser1@localhost with grant option;

-- 改密码，将密码设置为testuser1
alter user testuser1@localhost identified by 'testuser1';

-- 
create user testuser2@'%' identified by 'testuser2';
grant select, updatetime, delete, insert on employees.*  to testuser2@'%';

-- 只授予登录权限给testuser3@localhost
create user testuser3@localhost identified by 'testuser3';
-- 创建用户后进行grant，默认具有usage权限

查看权限

show grants for testuser2@'%';

+--------------------------------------------------------------------------+
| Grants for testuser2@%                                                   |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser2`@`%`                                    |
| GRANT SELECT, INSERT, UPdatetime, DELETE ON `employees`.* TO `testuser2`@`%` |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

更改账号权限

• 赋予权限

grant select on *.* to testuser3@'localhost';

• 收回权限

revoke select on *.* from testuser3@'localhost';

权限表中host和user的匹配规则

如果权限表中的Host 既 有 “ thomas.loc.gov ”，又有〝%” ，而此时，连接从主机thomas.loc.gov过来。显然，user表里面这两条记录都符合匹配条件，那系统会选择哪一个？

1. 优先匹配具体的host
2. host相同时优先匹配具体的user
3. user表会按照上述两个规则进行排序
-- 主机名和ip地址时最具体的，%是最不特定的。

修改账号密码

1. mysqladmin命令修改

mysqladmin -u username -h hostname password "newpwd"

2. alter user语句修改

alter user testuser1@localhost identified by 'testuser1';

3. Grant usage语句修改（MySQL8.0 没成功）

grant usage on *.* to 'testuser3'@'localhost' identified by 'testuserpwd'；

删除账号

drop user testuser3@localhost;

设置资源限制

/*
MAX_QUERIES_PER_HOUR count:每小时最大查询次数。
MAX_UPdatetimeS_PER_HOUR count:每小时最大更新次数。
MAX_CONNECTIONS_PER_HOUR count:每小时最大连接次数。
MAX_USER_CONNECTIONS count:最大用户连接数。
*/
-- 创建用户emp，要求具有employees库上的select权限，并且每小时查询次数小于等于6次，最多同时只能有了个此用户进行并发连接。
create user testuser3@localhost identified by 'testuser4';
grant select on employees.* to testuser4@localhost;
alter user testuser4@localhost with max_queries_per_hour 6;

-- MySQL8.0不支持如下设置资源限制方式
grant select on employees.* to testuser4@localhost with max_queries_per_hour 6;

清除资源限制

使用root执行alter语句，将指定的参数置为0即可。

用户密码管理

/**
password_expired:密码是否过期，默认值为N
password_last_changed:最后修改密码的时间
password_lifetime:密码有效期，单位为天
*/

-- 将账号密码设置为过期
alter user testuser4@localhost password expire;

-- 重置用户密码
alter user testuser4@localhost identified by 'testuser4';

锁定用户

select account_locked from user where user='testuser4';
-- 锁定
alter user testuser4@localhost account lock;
-- 解锁
alter user testuser4@localhost account unlock;

第28章 MySQL常见问题和应用技巧

28.4 从mysqldump文件抽取需要恢复的表

• 查看已备份的表和对应的开始行号

grep -n 'Table structure' employees.sql

• 用sed命令抽取sql语句

sed -n 'n,m p' employees.sql > salaries.sql

• 执行sql语句，恢复表

-- 在mysql中执行
source salaries.sql

28.5 使用innobackupex备份恢复单表

• 全量备份到fullback目录

innobackupex --defaults-file=  --no-timestamp --user=root --password= --host=  fullback

• 根据全备导出所需的.exp、.frm、.ibd文件

innobackupex --defaults-file=  --user=root --password= --use-memory=256m --redo-only --apply-log funllback --export  fullback

• 重建表结构

create table table_name ...

• 删除表空间

alter table table_name discard tablespace;

• 将导出的相关文件拷贝到需要恢复的表目录下

• 加载表

alter table table_name import tablespace;

28.6 分析binlog，找出写的热点表

• 安装perl

yum install perl

• 下载解压pasrebinlog脚本

wget https://codeload.github.com/wubx/mysql-binlog-statistic/zip/master

unzip master

mysql-binlog-statistic-master/bin/pasrebinlog

• 修改pasrebinlog脚本参数

-- 修改datadir目录，存放binlog文件的目录
-- 修改mysqlbinlog工具的位置

• 解析sql

perl pasrebinlog脚本路径 binlog文件路径

9.6 索引在MySQL8.0 中的改进

不可见索引

索引默认是可见的，可以在创建表时增加invisible关键字来创建不可见索引

create table t1(
i int,
j int,
index i_idx(i) invisiable
)engine=InnoDB;

create index i_idx on t1(i) invisiable;
alter table t1 add index i_idx(i) invisiable;
alter table t1 add index i_idx(i) visiable;

当数据库中数据量达到一定程度之后，删除或重建索引会造成巨大开销，当发现某个索引不需要时，可以将其设置为invisiable，当没有sql用到时，再将其删除。当发现新增的索引对系统带来了负面影响时，可以将其设为invisiable。

倒叙索引

desc

第14章 MySQL分区

注意：

无论哪种MySQL分区类型，要么分区表上没有主键/唯一索引，要么分区表的主键/唯一索引都必须包含分区间，也就是说不能使用主键/唯一键字段（当存在时）之外的其他字段分区。

分区类型：

• range

create table emp(...store_id int not null)
partition by range (store_id)(
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition p0 values less than (30)
);

• List

create table table_name(...category int)
partition by list(category)(
    partition p0 values in (3,5),
    partition p1 values in (1,10),
    partition p2 values in (2),
    partition p3 values in (6),
);

• Columns

-- 只支持整型类型
-- 支持date和datetime
-- 支持char，varchar，binary和varbinary
create table (a int, b int)
partition by range column(a,b)(
    partition p0 values less than (0,10),
    partition p1 values less than (10,10),
)
-- range columns 分区键的比较是多列排序，根据排序结果分区存放数据

• hash分区

-- 常规hash分区，取模运算
-- 线性hash分区，线性的2的幂的运算法则
create table emp(...store_id)
partition by [linear] hash (store_id) partitions 4;

• key分区（需使用MySQL中的hash函数）

create table table_name(job varchar(30)...)
partition by key(job) partitions 4;

-- key可以为空，为空时会选取主键id或非null唯一索引作为分区键。

14.2.7 MySQL分区处理NULL值的方式

注意：

• range分区中，null值会被当做最小值来处理。

• list分区中，null值必须出现在枚举列表中，否则不被接受。

• Hash/key分区中，null值会被当做零值处理。

14.3 分区管理

14.3.1 range和list分区管理

• 删除分区

alter table table_name frop partition p1;

• 重定义分区

alter table expenses reorganize partition p4,p5,p6 into (
    partition p4 values in(6,11),
    partition p5 values in(7,8)
);

14.3.2 hash和key分区管理

• 减少分区数量

alter table table_name coalesce partition 2;

• 增加分区数量

alter table table_name add partition partitions 8; -- 增加8个分区

分区表达式支持的函数

分区表达式中，并不是能使用所有的函数，只支持以下函数：

ABS()
CEILING()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP(),(使用TIMESTAMP类型的列时)
WEEKDAY()
YEAR()
YEARWEEK()

第15章 SQL优化

优化sql的一般步骤：

• 通过show status命令了解各种sql的执行频率

show global status like 'Com_%';

• 定位执行效率较低的sql语句（慢日志，show processlist）

• 通过explain分析低效的sql执行计划

15.1.4 (已弃用)通过show profile分析SQL

• 检查是否支持show profile

 show variables like 'have_profiling';

• 默认profiling是关闭的，在session级别开启profiling

set profiling=1;

• 使用profiling

-- 1.执行sql

show profiles;
show profile for query n;

-- 查看各个state状态的耗费时间
select 
    state, sum(duration) as total_r, 
    round(100 * sum(duration)/(select sum(duration) from information_schema.profiling where query_id=24),2) as percent,
    sum(1) as call_time,
    sum(duration)/count(*) as "R/Call"
from 
    information_schema.profiling 
where query_id =24 
group by 
    state 
order by   
    total_r desc;
-- 根据各个状态的消耗时间，进一步选择all,cpu,block io, context switch, page faults等明细类型查看MySQL在什么资源上耗费了过高的时间
show profile block io for query 24;
show profile all for query 24;
show profile cpu for query 24;
show profile content switch for query 24;
show profile page faults for query 24;

15.1.5 通过trace 分析优化器如何选择执行计划

执行完SQL之后执行：

select * from information_schema.optimizer_trace \G

存在索引而不能使用索引的典型场景

• 以%开头的like查询

• 数据类型出现隐式转换（如果是匹配值向条件谓词字段转换，则还可以走索引。如果是条件谓词字段向匹配值转换，则不走索引）

• 复合索引情况下，不满足最左匹配原则

• 优化器认为全表扫描更快，则不使用索引

• 条件谓词用or连接，且有的字段有索引，有的没有。（本质是为or的每个字段生成扫描区间，如果评估出来的扫描区间是负无穷到正无穷就不会走索引）

15.2.3 查看索引使用情况

show status like 'Handler_read%';

• Handler_read_key:该值很高则表明被索引值读的次数高，索引利用率高。如果低，则表明增肌索引得到的改善性能不高，因为索引不经常使用；

• Handler_read_rnd_next:该值越高意味着查询运行低效。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，该值高，则通常说明表索引不正确或写人的查询没有利用素引。

15.3 简单使用的优化方法

15.3.1 定期分析表和检查表

• analyze table table_name;分析表，本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。

• check table table_name;检查表的作用是检查一个或多个表（视图）是否有错误。

15.3.2 定期优化表

• optimize table table_name;这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。InnoDB会将该命令转换成重建表和分析表两个操作，加锁时间仅仅在整个工作的prepare和commit阶段做短暂的加锁工作，对于表的读写几乎没有影响。

注意

analyze,check,optimize,alter table执行期间对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关操作。

15.4 常用SQL的优化

15.4.1 大批量导入数据

1. 对于InnoDB引擎，保证导入的数据按照主键有序。

2. 导入数据前执行set unique_checks=0，关闭唯一性校验；在导入后执行set unique_checks=1恢复唯一性校验。

3. 如果应用使用自动提交的方式，建议在导入前执行set autocommit=0关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交。

15.4.2 优化insert语句

• 同时从同一客户端插入，应尽量使用多个值的insert语句。能减少客户端与数据库之间的连接、关闭等消耗。

• 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

• 当从一个文本文件装载一个表时，使用load data infile。这通常比使用很多insert语句快20倍。

15.4.3 优化order by语句

Filesort的优化

MySQL中有两种排序算法：

• 两次扫描算法：将排序字段和主键id放到内存中排序，之后再根据主键回表。

• 一次扫描算法：将所有字段放入内存排序，不需要回表。需要内存足够大。

15.4.4 优化 group by语句

默认情况下，MySQL对所有group by的字段进行排序，如等同于 group by ... order by ...。如果查询包括group by，但用户想避免排序结果的消耗，则可以指定order by null，禁止排序。

15.4.5 优化join操作

• 嵌套循环

• 索引嵌套循环（匹配内层表索引）

• 块嵌套循环

• Hash Join (MySQL8.0.22之后支持）

15.4.8 优化分页查询

原查询

select film_id, description  from film order by title limit 50,5 \G

1. 第一种优化思路

在索引上完成排序分页的操作，然后根据主键关联回原表查询所需要的其他列内容。

select 
    a.filmm, a.description
from 
    film a 
inner join 
    (select film_id from film order by title limit 50,5) b
on
    a.film_id = b.film_id

2. 第二种优化思路

记录上一页的id值，然后现根据根据这个id值过滤，再limit n。

15.4.9 使用SQL提示

1. use index

通过该参数提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。(不一定会走use index的所以)

2. ignore index

   忽略一个或多个索引。

3. force index

强制

15.5 直方图

并不是所有大表的字段都需要创建直方图。通常在一些唯一值较少、数据分布不均衡、查询较为频繁、没有创建素引的字段上考虑创建直方图。虽然在创建素引有时也可以达到优化效果，但由于这类字段素引使用率低、 索引维护成本高，因此通常不会 在这些字段上单独创建索引。而直方图只需要创建一次，对数据的变更不需要实时进行维护，代价较小，更适合于此类条件的查询。

第16章 锁问题

16.3 InnoDB锁问题

16.3.2 获取InnoDB行锁争用情况

show status like 'innodb_row_lock%';
-- InnoDB_row_waits 和 InnoDB_row_lock_time_avg的值比较高则表明锁争用严重

设置监视器

set global innodb_status_output=on;
set global innodb_status_output_locks=on;
-- 长时间打开监视器会导致日志文件过大，用完及时关闭
set global innodb_status_output=on;
set global innodb_status_output_locks=on;

查看最新的状态信息

show engine innodb status \G

16.3.4 InnoDB行锁实现方式

加锁优化

两个原则、两个优化、一个bug

访问到数据才会加锁

加锁基本单位是临键锁，前开后闭(,]

索引等值查询，唯一索引，临键锁会退化成行锁

索引等值查询，向右遍历最后一个不满足条件的值时，临键锁会退化成间隙锁

InnoDB行锁特性

• 不通过索引的条件查询会锁住表中所有记录

• MySQL的行锁是针对索引加的锁，不是针对记录加的锁。（而对二级索引加锁时，是根据二级索引获取到主键值，然后对主键加锁）

• 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

• 即便在条件中使用了索引字段，是否使用索引来检索数据是由执行计划决定的。分析锁冲突时，需要检查SQL的执行计划。

16.3.6 恢复和复制的需要，对InnoDB锁机制的影响

四种复制模式

• 基于SQL语句的复制：SBL

• 基于行数据的复制：RBR

• 混合复制模式：对安全的SQL语句采用SQL语句的复制模式，对于非安全的SQL语句采用基于行的复制模式。

• 使用全局事务ID的复制（GTIDS）：主要是解决主从自动同步一致问题。

对于基于SQL语句的复制的BinLog日志

如果是CTAS这种SQL语句：insert into target_tab select * from source_tab where ...和create table new_tab ... Select ... From source_tab where...MySQL会对source_tab中的记录加锁。因为只有在事务提交的时候才会记录到binlog中，如果在执行这类语句时有其他更新的语句事务提交了，则在binlog中记录的顺序会是update语句，insert语句。（根据binlog只在事务提交时记录，这样先提交的事务在恢复的时候先执行，不存在实际执行的过程中的快照读了，因此会造成binlog恢复出来的数据和数据库中实际存储的不一样）

使用表锁需要注意的点

• 使用lock tables可以给InnoDB加表锁。但是表锁不是由InnoDB管理的，而是由MySQL Server管理的，autocommit=0且innodb_table_locks=1，InnoDB才能知道MySQL加的表锁，才能自动识别涉及表锁的死锁。

• 事务结束前不要使用unlock tables释放锁，因为其会隐含地提交事务。另外，用commit和rollback不会释放表锁，必须unlock tables；

避免死锁的常用方法

• 以相同的顺序访问表

• 批量处理数据时，事先对数据排序，保证每个线程固定的顺序来处理记录，能降低出现死锁的可能。

• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不是先申请共享锁再申请排他锁。

• repeatable-read隔离级别下，如果两个线程同时对相同记录用select ... For update加排他锁，在没有记录的条件下两个线程都会加锁成功。如果此时都尝试插入一条新记录，就会出现死锁。（查询不存在的记录时，加的是间隙锁，而间隙锁是兼容的。执行insert语句时，需要insert意向锁，和gap锁是冲突的，所以产生了死锁）

第17章 MySQL体系结构概况

缓冲

https://cloud.tencent.com/developer/article/1967962

• 缓冲池(Buffer pool)：用于优化读请求，减少读的磁盘IO

• 写缓冲(change buffer):用于优化写请求，减少写入磁盘的IO（****AWS Aurora MySQL不支持）

17.2.3 InnoDB内存优化

用一块内存做IO缓存（缓存索引页和数据页）：free list、flush list、LRU list。

LRU list分为young list（热点数据）和old list（使用频率不高的数据）。因为MySQL“预读”的存在，如果只用一个LRU list，则会导致预读时把一些热点数据频繁挤出LRU list到flush list中。

select
    round(sum(data_length + index_length) / 1024 /1024, 2) "Total Size (MB)",
    round(sum(data_free) /1024 /1024, 2) "Free Space (MB)"
from
    information_schema.tables;
    
-- 查看当前所有表占用的空间和空闲空间

调整old sublist大小

查看：show variables like '%innodb_old_blocks_pct%';

调整Innodb_old_blocks_time的大小

表示将数据从old list中移到young list 的时间间隔。只有在old list中待够innodb_old_blocks_time（ms）后才会移动到young list中。

调整用户服务线程排序缓存区

sort_merge_passes:进行归并排序的次数。归并排序是多个文件排序时的操作。

当该值过大时，可以增大sort_buffer_size的值来增大排序缓冲区。

InnoDB doublewrite

因为InnoDB的页大小（16KB）和操作系统页大小（4KB）不一样，所以极端情况会导致InnoDB一页未完全刷到磁盘上。为此，InnoDB用系统表空间的一块缓存作为doublewrite buffer。当刷脏页时，先将缓存中的数据副本写入doublewrite buffer中，然后立即刷新到磁盘（顺序写）。之后再将doublewrite buffer的数据写入各个表空间中。

Redo log是物理逻辑型日志，不是纯物理的，因此不完全具有幂等性，恢复时需要根据原始数据页+redo恢复。https://cloud.tencent.com/developer/article/1783817

17.3.3 设置log file size，控制检查点

innodb_log_file_size用于设置redo log 的大小。一般来说，平均每半个小时写满一个日志文件比较合适。

计算InnoDB每分钟的日志量：

pager grep -i "Log sequence number"

show engine innodb status \G 
select sleep(60); 
show engine innodb status \G

nopager

select round((661616830-661616800)/1024/1024) as MB; -- 用后一个值减去前一个值

然后计算半个小时的日志量。

17.3.4 调整innodb_log_buffer_size

用于调整redo log缓冲池的大小，默认是16MB。

17.4 MySQL并发相关的参数

max_connections

连接到MySQL数据库的最大数量，默认值是151。（如果状态变量connection_errors_max_connections不为0，并且一直在增长，就说明不断有连接请求因数据库连接数已达到最大允许的值而失败，因考虑提升max_connections）。

back_log

控制监听TCP端口时设置的积压请求栈大小。默认是50+(max_connections/5)，最大值不超过900。增大该值用于较短时间内处理大量连接请求。

table_open_cache

每一个SQL执行线程至少都要打开1 个表缓存，参数table_open_cache 控制所有SQL 执 行线程可打开表缓存的数量。

thread_cache_size

innodb_lock_wait_timeout

第19章 优化数据表的设计

19.1.2 通过拆分提高表的访问效率

• 垂直拆分

• 水平拆分

  • 分区

  • 分表

19.2 数据库应用优化

第20章 PS/SYS数据库

20.2 SYS库

• SYS表的统计维度

-- 使用上述关键词查询相关的表
select table_name from information_schema.tables where table_schema ='sys' table_name like 'host%' ;

主机相关

select * from host_summary \G

• host:连接到MySQL实例的主机

• statements:主机IP执行总的SQL语句数量

• statement_latency:主机IP执行SQL语句消耗的总时间

• file_io_latency:主机IP发生文件等待事件消耗的总时间

• current_connections:主机IP当前总的连接数

• current_memory:实例为主机IP当前分配的内存

Innodb相关

• innodb_buffer_stats_by_%:分析哪些对象占用缓冲池的内存较多

• innodb_lock_waits:用于行锁等待分析

连接数和会话相关

sys.processlist和sys.session的信息比information_schema.processlist全面。

db相关

查看冗余索引表：schema_redundant_indexes

select * from schema_redundant_indexes \G

SQL语句相关

表：statement_analysis

select * from statement_analysis where  ... order by ... limit n \G

• query:替换SQL语句变量并美化

• db:应用执行SQL时连接的db名

• exec_count:SQL执行总次数

• rows_examined:SQL执行查询的总记录数

• rows_affected:SQL执行影响的总记录数

第24章 MySQL日志

日志分类：错误日志、二进制日志、查询日志、慢查询日志、中继日志、元数据日志。前四个比较常用。

24.2.3 日志的删除

• reset master

MySQL客户端执行reset master，删除所有binlog，新生成的日志从编号000001开始。
8.0中支持reset master to+编号，自定义新日志的开始编号。

• purge master logs to 'mysql-bin.***'

删除mysql-bin.***之前编号的所有日志，不包括mysql-bin.***

• purge master logs before 'yyyy-mm-dd hh24:mi:ss'

删除日期为yyyy-mm-dd hh24:mi:ss之前的所有日志

• 设置过期天数，使其自动删除

--expire_logs_day=# 修改my.cnf配置文件
set global expore_logs_day=3

• binlog_expire_logs_secondsMySQL 8.0版本的参数，binlog过期时间可以精确到秒

24.4 慢查询日志

默认管理语句和不使用索引的查询不记录到慢查询日志。

--log-queries-not-using-indexes 开启监控查询语句
--log-slow-admin-statements 开启监控管理语句
--long_query_time 设置记录慢查询日志的时间阈值，源码中超过该值才会记录，等于不会记录

pt-query-digest工具汇总查看慢日志

25 章 备份与恢复

逻辑备份和恢复

mydump备份文件一共有三类：db.table.sql文件，存放表数据；db.table-schema.sql文件，存放表结构；metadata文件，记录备份的开始时间和结束时间，以及binlog记录文件名、位置和GTID信息。可以根据metadata中的信息进行完全恢复或基于位置的恢复。

完全恢复例子

/*
-- 备份数据库
mysqldump -uroot -p --single-transaction -F employees > employees.dmp

-- 恢复备份
mysql -uroot -p employees < employees.dmp

-- 使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysqlbinlog ..-bin.0... | mysql -u root -p employees
*/



-- 备份
sudo mydumper -u root -p root -P 3340 -h 127.0.0.1 --database yanhao  -G -R -E -c -t 8 -o /data/dba/yanhao/application/backup/test_table_back
-- 恢复
myloader -u root -p root -P 3340 -h 127.0.0.1 -o -d /data/dba/yanhao/application/backup/test_table_back
-- 中间插几条数据

-- 查看metadata，备份过程中binlog处于那个文件中
-- binlog恢复，2024-07-24 03:24:30之前的数据有点问题
sudo /data/dba/yanhao/application/mysql/mysql8/bin/mysqlbinlog --start-datetime="2024-07-24 03:24:30" /data/dba/yanhao/application/mysql/dataMysql57/binlog.000016  | /data/dba/yanhao/application/mysql/mysql8/bin/mysql -uroot -p -h127.0.0.1 -P3340

基于时间点恢复

-- 跳过出问题sql语句的那个时间点
mysqlbinlog  --stop-datetime="2018-10-20 9:59:59" binlogPath | mysql -uroot -p

mysqlbinlog  --start-datetime="2018-10-20 10:01:00" binlogPath | mysql -uroot -p

基于位置的恢复

-- 现将小范围时间的数据导出来
mysqlbinlog  --start-datetime="2018-10-20 9:55:00"  --stop-datetime="2018-10-20 10:05:00" binlogPath > tmp.sql

-- 找出有问题语句的前后位置编号
mysqlbinlog  --stop-position="xxx" binlogPath | mysql -uroot -p

mysqlbinlog  --start-position="xxx" binlogPath | mysql -uroot -p

26 MySQL权限与安全

26.1 MySQL权限管理

账号管理

1. 创建账号

   1. Create user + grant方式

   2. 直接操作授权表

create user testuser1@localhost identified by 'passwd';

grant all privileges on *.* to testuser1@localhost;

-- with grant option表示可以将自己的权限授予其他用户
grant all privileges on *.* to testuser1@localhost with grant option;

-- 改密码，将密码设置为testuser1
alter user testuser1@localhost identified by 'testuser1';

-- 
create user testuser2@'%' identified by 'testuser2';
grant select, updatetime, delete, insert on employees.*  to testuser2@'%';

-- 只授予登录权限给testuser3@localhost
create user testuser3@localhost identified by 'testuser3';
-- 创建用户后进行grant，默认具有usage权限

查看权限

show grants for testuser2@'%';

+--------------------------------------------------------------------------+
| Grants for testuser2@%                                                   |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser2`@`%`                                    |
| GRANT SELECT, INSERT, UPdatetime, DELETE ON `employees`.* TO `testuser2`@`%` |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

更改账号权限

• 赋予权限

grant select on *.* to testuser3@'localhost';

• 收回权限

revoke select on *.* from testuser3@'localhost';

权限表中host和user的匹配规则

如果权限表中的Host 既 有 “ thomas.loc.gov ”，又有〝%” ，而此时，连接从主机thomas.loc.gov过来。显然，user表里面这两条记录都符合匹配条件，那系统会选择哪一个？

1. 优先匹配具体的host
2. host相同时优先匹配具体的user
3. user表会按照上述两个规则进行排序
-- 主机名和ip地址时最具体的，%是最不特定的。

修改账号密码

1. mysqladmin命令修改

mysqladmin -u username -h hostname password "newpwd"

2. alter user语句修改

alter user testuser1@localhost identified by 'testuser1';

3. Grant usage语句修改（MySQL8.0 没成功）

grant usage on *.* to 'testuser3'@'localhost' identified by 'testuserpwd'；

删除账号

drop user testuser3@localhost;

设置资源限制

/*
MAX_QUERIES_PER_HOUR count:每小时最大查询次数。
MAX_UPdatetimeS_PER_HOUR count:每小时最大更新次数。
MAX_CONNECTIONS_PER_HOUR count:每小时最大连接次数。
MAX_USER_CONNECTIONS count:最大用户连接数。
*/
-- 创建用户emp，要求具有employees库上的select权限，并且每小时查询次数小于等于6次，最多同时只能有了个此用户进行并发连接。
create user testuser3@localhost identified by 'testuser4';
grant select on employees.* to testuser4@localhost;
alter user testuser4@localhost with max_queries_per_hour 6;

-- MySQL8.0不支持如下设置资源限制方式
grant select on employees.* to testuser4@localhost with max_queries_per_hour 6;

清除资源限制

使用root执行alter语句，将指定的参数置为0即可。

用户密码管理

/**
password_expired:密码是否过期，默认值为N
password_last_changed:最后修改密码的时间
password_lifetime:密码有效期，单位为天
*/

-- 将账号密码设置为过期
alter user testuser4@localhost password expire;

-- 重置用户密码
alter user testuser4@localhost identified by 'testuser4';

锁定用户

select account_locked from user where user='testuser4';
-- 锁定
alter user testuser4@localhost account lock;
-- 解锁
alter user testuser4@localhost account unlock;

第28章 MySQL常见问题和应用技巧

28.4 从mysqldump文件抽取需要恢复的表

• 查看已备份的表和对应的开始行号

grep -n 'Table structure' employees.sql

• 用sed命令抽取sql语句

sed -n 'n,m p' employees.sql > salaries.sql

• 执行sql语句，恢复表

-- 在mysql中执行
source salaries.sql

28.5 使用innobackupex备份恢复单表

• 全量备份到fullback目录

innobackupex --defaults-file=  --no-timestamp --user=root --password= --host=  fullback

• 根据全备导出所需的.exp、.frm、.ibd文件

innobackupex --defaults-file=  --user=root --password= --use-memory=256m --redo-only --apply-log funllback --export  fullback

• 重建表结构

create table table_name ...

• 删除表空间

alter table table_name discard tablespace;

• 将导出的相关文件拷贝到需要恢复的表目录下

• 加载表

alter table table_name import tablespace;

28.6 分析binlog，找出写的热点表

• 安装perl

yum install perl

• 下载解压pasrebinlog脚本

wget https://codeload.github.com/wubx/mysql-binlog-statistic/zip/master

unzip master

mysql-binlog-statistic-master/bin/pasrebinlog

• 修改pasrebinlog脚本参数

-- 修改datadir目录，存放binlog文件的目录
-- 修改mysqlbinlog工具的位置

• 解析sql

perl pasrebinlog脚本路径 binlog文件路径