第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 大批量导入数据
-
对于InnoDB引擎，保证导入的数据按照主键有序。
-
导入数据前执行
set unique_checks=0，关闭唯一性校验；在导入后执行set unique_checks=1恢复唯一性校验。 -
如果应用使用自动提交的方式，建议在导入前执行
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
- 第一种优化思路
在索引上完成排序分页的操作，然后根据主键关联回原表查询所需要的其他列内容。
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
- 第二种优化思路
记录上一页的id值，然后现根据根据这个id值过滤，再limit n。
15.4.9 使用SQL提示
- use index
通过该参数提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。(不一定会走use index的所以)
-
ignore index
忽略一个或多个索引。
-
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权限管理
账号管理
-
创建账号
-
Create user + grant方式
-
直接操作授权表
-
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地址时最具体的，%是最不特定的。
修改账号密码
- mysqladmin命令修改
mysqladmin -u username -h hostname password "newpwd"
- alter user语句修改
alter user testuser1@localhost identified by 'testuser1';
- 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 大批量导入数据
-
对于InnoDB引擎，保证导入的数据按照主键有序。
-
导入数据前执行
set unique_checks=0，关闭唯一性校验；在导入后执行set unique_checks=1恢复唯一性校验。 -
如果应用使用自动提交的方式，建议在导入前执行
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
- 第一种优化思路
在索引上完成排序分页的操作，然后根据主键关联回原表查询所需要的其他列内容。
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
- 第二种优化思路
记录上一页的id值，然后现根据根据这个id值过滤，再limit n。
15.4.9 使用SQL提示
- use index
通过该参数提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。(不一定会走use index的所以)
-
ignore index
忽略一个或多个索引。
-
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权限管理
账号管理
-
创建账号
-
Create user + grant方式
-
直接操作授权表
-
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地址时最具体的，%是最不特定的。
修改账号密码
- mysqladmin命令修改
mysqladmin -u username -h hostname password "newpwd"
- alter user语句修改
alter user testuser1@localhost identified by 'testuser1';
- 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文件路径