mysql 备忘录

AntHub

已于 2022-06-08 17:34:01 修改

阅读量625

点赞数

分类专栏： database 文章标签： mysql 数据库 sql

于 2020-09-05 16:55:54 首次发布

本文链接：https://blog.csdn.net/wwq921220/article/details/108421707

版权

database 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

rownum

select a.* from (
select @rownum := @rownum +1 AS rownum, l.id from tb_limit_test l ,(select @rownum :=0) t ) a  where (a.rownum%2)=0;

版本

select version(); ： 5.7.31-log；
以下内容，大多基于此版本；
select connection_id();：查看当前 connection id；

DDL

建库：create database test_db charset utf8mb4;
建表：create table t( id bigint unsigned primary key auto_increment, a varchar(10),index(a));
加字段(设置超时时间，防止 mdl 阻塞)：ALTER TABLE tbl_name NOWAIT add column ...; ALTER TABLE tbl_name WAIT N add column ...
加索引：alter table t add index index1(name);
加前缀索引： alter table t add index index2(name(4));
查看表索引：select index from tb_device;

Cardinality 列，为索引基数，代表索引的区分度，是通过采样统计得到，一般并不精确，但数量级和实际差不多
InnoDB 采样统计：默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数；而数据表的数据是会不断变化的，索引统计信息也需要持续更新，以正确的帮助优化器做出判断决策，索引统计信息更新触发的节点，是当变更的数据行数超过 1/M 时，会自动触发重新做一次索引统计；
analyze table table_name; ：重新统计索引信息
innodb_stats_persistent：存储索引统计信息的方式，on-统计信息会持久化存储；默认的 N 是 20，M 是 10。off-统计信息只存储在内存中；默认的 N 是 8，M 是 16；此时每次重启需要重新统计索引信息。
创建分区表：

CREATE TABLE `tab_name` (
  `col_time` datetime NOT NULL,
  `id` int(11) DEFAULT NULL,
  KEY (`col_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(col_time))
(
 PARTITION p_2020_bef VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

DML

新增：insert into t values (null,'a'),(null,'b');
共享锁：select k from t where id=1 lock in share mode;

insert

insert into tab_name select * from tab_name_other; 加锁是为了 binlog 数据一致性;保持语义正确性;
insert 唯一键（主键;唯一索引）冲突会加 next-key lock;
insert into tab_name values(...); : 会给新增的记录加行锁；

insert into x with recursive (mysql8.0)

INSERT INTO t1
WITH RECURSIVE t AS (
  SELECT 1 AS c1, 1 AS c2
  UNION ALL
  SELECT t.c1 + 1, t.c1 * 2
    FROM t
   WHERE t.c1 < 1000000
)SELECT *  FROM t;

临时表

以下语句会使用临时表：
- insert into t (select * from t);
- order by rand();

insert into … on duplicate key update; : 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束冲突，就执行后面的更新语句;如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行

table_def_key：内存中用于区别不同的表；
- 普通表为：库名 + 表名
- 临时表为：库名 + 表名+server_id+thread_id
临时表磁盘文件命名规则：表结构文件：#sql{进程 id}_{线程 id}_ 序列号.frm ，例如：#sql1228_5_0.frm；表数据文件：5.6及以前版本：#sql{进程 id}_{线程 id}_ 序列号.ibd；5.7 版本开始，引入了一个临时文件表空间，专门用来存放临时文件的数据，无需单独 ibd 文件；
在实现上，每个线程都维护了自己的临时表链表。这样每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE + 表名”操作；
执行 rename table 语句的时候，要求按照“库名 / 表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的 frm 文件是放在 tmpdir 目录下的，并且文件名的规则是“#sql{进程 id}{线程 id} 序列号.frm”，因此会报“找不到文件名”的错误
当 binlog_format=row 时，和临时表相关语句就不会记录到 binlog，而 binlog_format=statment/mixed 时，binlog 会记录对临时表的操作，否则在备库执行操作临时表的sql 时，会报错找不到对象，表不存在等。
mysql 在记录 binlog 时，会将主库执行语句的线程id 记录到binlog，这样当主库分别在两个线程创建了名字相同的临时表，而这两条建表语句被发给同一个 worker 执行时，备库会根据 binlog 中记录的主库的线程id 构造临时表的 table_def_key（库名 +t1+“M 的 serverid”+“session A 的 thread_id”） ,和磁盘文件，因此备库可正常应用 binlog。

常用

查看建表语句：show create table t\G
执行计划：explain select a from t where t.a='a';
全局权限：grant all privileges on *.* to 'user_name'@'%' with grant option; ：磁盘中更新 mysql.user; 内存中更新 acl_users;
- revoke all privileges on *.* from 'user_name'@'%';
库级权限：grant all privileges on db_name.* to 'user_name'@'%' with grant option; ：磁盘中更新 mysql.db; 内存中更新 acl_dbs;
表级权限：grant all privileges on db_name.table_name to 'user_name'@'%' with grant option;：磁盘中更新 mysql.tables_priv; 内存中更新 column_priv_hash;
列权限：grant select(col1_name), insert(col1_name,col2_name) on db_name.table_name to'user_name'@'%' with grant option; : 磁盘中更新 mysql.solumns_priv; 内存中更新 column_priv_hash;
flush privileges; ：以库中的权限值为准，更新内存中对应的变量值；
- 对于全局权限：内存更新 acl_users，对于已经存在的连接（使用自己线程中的权限位）的全局权限无效；
- 对于库级权限：内存更新 acl_dbs（所有线程公用的全局变量），对于存在的连接有效；对于使用权限变动之前已经使用 use db_name（库级权限在会话变量中）；的连接无效

存储过程

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

数据导出/导入

mysqldump

导出：mysqldump -h$host -P$port -u$user --single-transaction --add-locks=0 --no-create-info --set-gtid-purged=OFF db_name tab_name --where="a>900" --result-file=$data_dir/t.sql
- –single-transaction : 使用一致性视图保证数据的一致性，不需要对表 db_name.tab_name 加表锁；不影响其他事务的执行
- –add-locks: 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；
- –no-create-info ：不需要导出表结构；
- –set-gtid-purged=off ：不输出跟 GTID 相关的信息；
- –result-file: 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的
- –where : where 条件
- –skip-extended-insert：一条insert 语句只插入一条数据；像上面的命令一条 insert 语句会有多个value，这样的语句执行起来更快
- –tab ：同时导出表结构定义文件和 csv 数据文件
导入：mysql -h$host -P$port -u$user db_name_one -e "source $dir/t.sql"
这个方法以前在生产用过

load data（csv，比 insert 快好多）

导出数据(手动替换$secure_file_priv)：

select * from db_name.tab_name  into outfile '$secure_file_priv/db_name_tab_name.csv';

select * from db_name.tab_name  into outfile '$secure_file_priv/db_name_tab_name.csv' FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

导入数据：

LOAD DATA INFILE '$secure_file_priv/wd.csv'  INTO TABLE db_name.tab_name CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';
-- IGNORE 1 LINES;

导出的 csv 文件会放在服务端的 $secure_file_priv 目录；

复制表空间（faster；InnoDB）

mysql 5.6 引入可传输表空间(transportable tablespace）；
自己直接 copy .frm .ibd 文件是不行的，数据字典中未注册；
例如现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：
- 执行 create table r like t，创建一个相同表结构的空表；
- 执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
- 执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
- 在（客户端shell） db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限）；
- 执行 unlock tables，这时候 t.cfg 文件会被删除；
- 执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

information_schema

information_schema.innodb_trx：查看长事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>30; ：查找持续时间超过 30s 的事务

performance_schema

file_summary_by_event_name : 这个表统计了 io 相关信息；
- wait/io/file/innodb/innodb_log_file: redo_log 的相关时间，单位：皮秒 (10^-12 s)
- COUNT_STAR ：所有 io 总次数；
- SUM_TIMER_WAIT ,MIN_TIMER_WAIT ,AVG_TIMER_WAIT ,MAX_TIMER_WAIT ：所有IO类型的统计，分别为：总和/最小值/平均值/最大值
- COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ： io 读操作统计；
- SUM_NUMBER_OF_BYTES_READ：从 redo_log 里读的字节数；
- COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE ：写操作统计；
- COUNT_MISC, SUM_TIMER_MISC, MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC：其他类型的io统计，例如 fsync 操作系统封装了很多的系统调用，供程序开发者使用；
- wait/io/file/sql/binlog ：binlog 的相关统计；
update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%'; : 打开 redo_log 的时间监控；
select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>1000 000 000 000;: 使用 MAX_TIMER_WAIT 判断数据库是否异常；

mysql> select event_name from file_summary_by_event_name;
±--------------------------------+
| event_name |
±--------------------------------+
| wait/io/file/sql/map |
| wait/io/file/sql/binlog |
| wait/io/file/sql/binlog_index |
| wait/io/file/sql/relaylog |
| wait/io/file/sql/relaylog_index |
| wait/io/file/sql/casetest |
| wait/io/file/sql/dbopt |
| wait/io/file/sql/des_key_file |
| wait/io/file/sql/ERRMSG |
| wait/io/file/sql/select_to_file |
| wait/io/file/sql/file_parser |
| wait/io/file/sql/FRM |
| wait/io/file/sql/global_ddl_log |
| wait/io/file/sql/load |
| wait/io/file/sql/LOAD_FILE |
| wait/io/file/sql/log_event_data |
| wait/io/file/sql/log_event_info |
| wait/io/file/sql/master_info |
| wait/io/file/sql/misc |
| wait/io/file/sql/partition |
| wait/io/file/sql/pid |
| wait/io/file/sql/query_log |
| wait/io/file/sql/relay_log_info |
| wait/io/file/sql/send_file |
| wait/io/file/sql/slow_log |
| wait/io/file/sql/tclog |
| wait/io/file/sql/trigger_name |
| wait/io/file/sql/trigger |
| wait/io/file/sql/init |
| wait/io/file/mysys/charset |
| wait/io/file/mysys/cnf |
| wait/io/file/csv/metadata |
| wait/io/file/csv/data |
| wait/io/file/csv/update |
| wait/io/file/myisam/data_tmp |
| wait/io/file/myisam/dfile |
| wait/io/file/myisam/kfile |
| wait/io/file/myisam/log |
| wait/io/file/myisammrg/MRG |
| wait/io/file/archive/metadata |
| wait/io/file/archive/data |
| wait/io/file/archive/FRM |
±--------------------------------+
42 rows in set (0.00 sec)

事务

commit work and chain;
锁等待超时时间：innodb_lock_wait_timeout，InnoDB 默认50s
主动死锁检测：innodb_deadlock_detect 默认 on
开启事务并创建一致性视图：START TRANSACTION WITH CONSISTENT SNAPSHOT;
一致性视图中数据版本：row trx_id
GTID :
- Global Transaction Identifier；
- 全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识；
- 格式：GTID=server_uuid:transaction_id
- server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
- transaction_id 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。
GTID 有两种生成方式，使用哪种方式取决于 session 变量 gtid_next 的值
- 如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:transaction_id 分配给这个事务。
  - a. 记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:transaction_id ’;
  - b. 把这个 GTID 加入本实例的 GTID 集合。
- 指定一个 GTID 的值，比如通过 set gtid_next='current_gtid’指定为 current_gtid，那么就有两种可能：
  - a. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；这在主备切换时非常有用；
  - b. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 transaction_id 也不用加 1。但一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行 set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic。
每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。
GTID 模式的启动：在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on；
gtid 解决主键冲突：set gtid_next='主库重复事务的 gtid '; begin; commit; set gtid_next=automatic; start slave;

慢查询日志

查看慢查询日志文件: show variables like 'slow_query_log_file';
查看慢查询日志配置开关: show variables like 'slow_query_log'; （5.7）
long_query_time : 慢查询阈值，当查询时间多于(等于是不记录的)设定的阈值时，记录日志。
log_output : 日志存储方式：FILE-日志文件；TABLE-存入数据库表 mysql.slow_log ；可以同时设置 log_output=‘FILE,TABLE’
log_queries_not_using_indexes : 未使用索引的查询也记录到慢查询日志中；
log_slow_admin_statements : 将ddl 慢语句也记录到慢查询日志中，如 alter table ； analyze table 等；默认 off
log_slow_slave_statements : 从库是否记录从主库复制过来的慢查询；默认off；修改不会立即生效
show status like '%Slow_queries%'; ：慢查询条数；
--log-short-format : 命令行参数：使用短格式，默认 false；
mysqldumpslow : 慢查询日志分析工具，这是个 perl 脚本；需要安装语言环境；

hash join

mysql8.0.18GA，引入 hash_join 连接方式；
默认配置时，MySQL 所有可能的情况下都会使用 hash join。同时提供了两种控制是否使用 hash join 的方法：
- 在全局或者会话级别设置服务器系统变量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 选项。默认为 hash_join=on。
- 在语句级别为特定的连接指定优化器提示 HASH_JOIN 或者 NO_HASH_JOIN。
可以通过系统变量 join_buffer_size 控制 hash join 允许使用的内存数量；
hash join 不会使用超过该变量设置的内存数量。
如果 hash join 所需的内存超过该阈值，MySQL 将会在磁盘中执行操作。
需要注意的是，如果 hash join 无法在内存中完成，并且打开的文件数量超过系统变量 open_files_limit 的值，连接操作可能会失败。为了解决这个问题，可以使用以下方法之一：
- 增加 join_buffer_size 的值，确保 hash join 可以在内存中完成。
- 增加 open_files_limit 的值。

kill

kill query + 线程 id：终止这个线程中正在执行的语句；
kill connection + 线程 id (connection 可以省略)：断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的；
Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL。

binlog

set sql_log_bin=on; /set sql_log_bin=off;：打开/关闭 binlog

主从复制

show slave status：查看备库状态；
- seconds_behind_master ：主备延迟时间
主备切换：
- 使用位点：CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password MASTER_LOG_FILE=$master_log_name MASTER_LOG_POS=$master_log_pos ;
- 使用 gtid ： CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password master_auto_position=1 ; ：master_auto_position=1 就表示这个主备关系使用的是 GTID 协议；
跳过事务： set global sql_slave_skip_counter=1; start slave;
跳过错误：slave_skip_errors;

延迟

select master_pos_wait(file, pos[, timeout]);
- 它是在从库执行的；
- 参数 file 和 pos 指的是主库上的文件名和位置；
- timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。
- 这个命令正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。
- 如果执行期间，备库同步线程发生异常，则返回 NULL；
- 如果等待超过 N 秒，就返回 -1；
- 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回 0。
select wait_for_executed_gtid_set(gtid_set, 1); version >= 5.7.5
- 等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；.
- 超时返回 1。
select @@global.gtid_executed;;

并行复制

原则：
- 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
- 同一个事务不能被拆开，必须放到同一个 worker 中。
slave_parallel_workers ：worker 线程数；
mysql 5.6 : 按库分发
MariaDB：组提交机制，模拟主库的并行事务，但是不能真正实现主库的并行度，备库中一组事务需要全部提交完成，才能开始运行下一组事务
mysql 5.7：
- slave-parallel-type：
- DATABASE ：使用5.6 的按库分发机制；
- LOGICAL_CLOCK：使用类似于 MariaDB 的组提交机制，之所有说是类似是因为，进行了优化：同时处于 prepare、处于prepare 和 commit 之间的事务可以并行；
- 有两个参数是用于故意拉长 binlog 从 write 到 fsync 的时间，以此减少 binlog 的写盘次数：
- binlog_group_commit_sync_delay: 参数，表示延迟多少微秒后才调用 fsync;
- binlog_group_commit_sync_no_delay_count：参数，表示累积多少次以后才调用 fsync。
mysql 5.7.22：增加新的并行策略由参数 binlog-transaction-dependency-tracking 控制：
- COMMIT_ORDER：根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
- WRITESET：表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。
  - hash 值是通过“库名 + 表名 + 索引名 + 值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个 hash 值；
  - writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；
  - 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；
  - 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的
- WRITESET_SESSION：在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序；

mysqlbinlog

–stop-position / –start-position ：binlog停止位置/ binlog开始位置；
备库执行 show binlogs; 查看binlog 文件；
--start-datetime=datetime：从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。
--stop-datetime=datetime：从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。
--start-position=N：从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=N：从二进制日志中第1个位置等于和大于N参量时的事件起停止读。
--base64-output=DECODE-ROWS：会显示出row模式带来的sql变更
-d /--database ：效果相同，指定一个数据库名称。
--offset=N，-o N：跳过前N个条目。

注意项

1.不要查看当前正在写入的binlog文件
2.不要加–force参数强制访问
3.如果binlog格式是行模式的,请加 -vv参数

示例

基于开始/结束时间：mysqlbinlog --start-datetime='2020-11-20 00:00:00' --stop-datetime='2020-11-20 01:01:01' -d 库名二进制文件;
基于pos值：mysqlbinlog --start-position=107 --stop-position=1000 -d 库名二进制文件;
转换为可读文本：mysqlbinlog --base64-output=DECODE-ROWS -vv -d 库名二进制文件;

参数

innodb_buffer_pool_size：InnoDB Buffer Pool 的大小；一般建议设置成可用物理内存的 60%~80%；show engine innodb status; 查看命中率；
innodb_thread_concurrency：InnoDB并发线程数，默认值0表示不限制并发线程数；建议64~128之间；注意与最大连接数区分；
innodb_change_buffer_max_size : 20 代表 change buffer 的大小最多只能占用 buffer pool 的 20%
open_files_limit ：打开文件个数限制，默认值 1024；
innodb_open_files：InnoDB 能打开的表（.ibd文件）数量，默认值 300;
secure_file_priv：数据导入、导出的目录限制，默认 $data_dir/Uploads/；SHOW VARIABLES LIKE "secure_file_priv";
- 设置为 NULL ：禁止执行 select … into outfile 操作；
- 设置为 empty：不限制位置；
- 设置为具体路径：生成的文件只能在指定的路径下或路径的子路径下；
show status like '%Innodb_rows_read%';：InnoDB 扫描行数;
auto_increment_offset ：自增的初始值；
auto_increment_increment ：自增步长；
innodb_autoinc_lock_mode 5.1.22 引入，默认值是1
- 0：语句执行结束后释放锁
- 1：普通 insert 语句，自增锁在申请之后就马上释放；insert … select ；要等语句执行结束后释放锁；
- 2：所有申请主键都是在申请后就释放锁，此时 binlog_format=row;
join_buffer : Block Nested-Loop Join (BNL) 暂存数据；大小是由参数 join_buffer_size 设定的，默认值是 256k；
read_rnd_buffer_size : 控制 Multi-Range Read （MRR）优化，read_rnd_buffer 的大小；
set optimizer_switch="mrr_cost_based=off" ：使用 MRR 优化
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';：使用 BKA 优化算法；
net_buffer_length：控制 net_buffer 大小，默认 16k；sql 结果返回时，先存到mysql 服务端的 net_buffer，net_buffer 写满，就调用网络接口往外放，发送完成后, 清空 net_buffer，然后继续读取数据写 net_buffer ；如果网络栈（socket send buffer）满，会阻塞
socket send buffer：操作系统参数，linux 默认定义 /proc/sys/net/core/wmem_default
mysql_use_result：读一行处理一行
mysql_store_result：直接把查询结果保存到本地内存；一个查询的返回结果不会很多时，都应该使用这个方法
innodb_old_blocks_time：默认值 1000 毫秒；LRU 链表中数据在old 区域超过这个时间，在被再次访问时，会移动到链表头部的young 区域，防止冷数据使 buffer_pool 失效，导致缓存命中率降低
sql_safe_updates：默认 off ；
- 在update操作中：当where条件中列（column）没有索引可用且无limit限制时会拒绝更新。where条件为常量且无limit限制时会拒绝更新。
- 在delete操作中：当①where条件为常量，②或where条件为空，③或where条件中列（column）没有索引可用且无limit限制时拒绝删除。

5.7 安装后优化

4 个重要选项，my.conf 文件中

innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT

MySQL 监控/图形工具：Percona 监控语句管理平台（PMMM）

错误

1062 错误是插入数据时唯一键冲突；
1032 错误是删除数据时找不到行。

AntHub

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 备忘录

文章目录DDLDMLDDL建库：create database test_db charset utf8mb4;建表：create table t( id bigint unsigned primary key auto_increment, a varchar(10),index(a));DML新增：insert into t values (null,'a'),(null,'b');show create table t\G执行计划：explain select a from
复制链接

扫一扫