MySQL tips

1、pt-query-digest

–limit参数：格式为pt:num，输出log中各个query的占比和占比数量最高的前num个数

2、mysqlslap

个人常用指令：

time /usr/local/mysql/bin/mysqlslap -uuser -ppasswd -S/path/mysql.sock --create=create.sql --create-schema=schema_name --query=query.sql --iterations=NUM1 --concurrency=NUM2 --detach=NUM3 --delimiter='str'

参数解释：
–iterations=NUM1, -i NUM1 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次；
–detach=N 执行N条语句后断开重连；
–concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定的值做为分隔符；
–create-schema 代表自定义的测试库名称；
–query=name,-q 使用自定义脚本执行测试；
–delimiter=’str’ str各个查询语句的分割符号；

3、sysdate()

查询语句中，where条件中使用当前时间时，尽量避免使用sysdate(),此函数可能导致对应的索引列索引失效；

4、半连接去重执行策略之firstmatch

FirstMatch：为了对记录进行合并而在扫描内表，并且对于给定值群组有多个实例时，选择其一而不是将它们全部返回。这为表扫描提供了一个早期退出机制而且还消除了不必要记录的产生。例子后续补充

5、MySQL5.7改进的进一步认识

• InnoDB buffer pool功能增强
  Online buffer pool resize：由小改大几乎无影响；由大改小需要释放部分内存，不过影响也不大，秒级完成。
  Buffer pool dump增强：设置innodb_buffer_pool_dump_pct，只导出最热那部分数据；系统负载高的时候，会根据innodb_io_capacity设置自动限制buffer pool dump的速度。

• VARCHAR in-place enlarge
  255字节长度是个门槛；不跨越255长度门槛即可在线调整；这使得增加VARCHAR列长度毫无压力；也就没必要再为VARCHAR列预留太大长度了；不过，不支持VARCHAR列长度in-place缩减。

• 其他innodb增强

  临时表使用独立表空间、不记录redo、没有change buffer、锁更少；
  执行innodb_undo_log_truncate=1选项，当undo log超过innodb_max_undo_log_size时自动truncate；
  支持多个page cleaner线程，提高dirty page flush效率；

6、BKA和BNL

从顺序随机I/O原理来讨论MYSQL MRR NLJ BNL BKA

7、联合唯一索引

联合唯一字段中，如果字段中出现null，则引擎有如下判断：不同记录的null值是不确定的，即为不相等的。

create table tmp1(id int,name varchar(20),unique key idx_id_name(id,name));mysql> insert into tmp1 values(1,null);Query OK, 1 row affected (0.05 sec)mysql> insert into tmp1 values(1,null);Query OK, 1 row affected (0.02 sec)mysql> select * from tmp1;+------+------+| id   | name |+------+------+|    1 | NULL ||    1 | NULL |+------+------+2 rows in set (0.00 sec)

8、analyze table和optimize table

Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新
如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。
删除了表的部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

9、innodb_data_file_path格式

提示如下：syntax error in innodb_data_file_path or size specified is less than 1 megabyte
innodb_data_file_path=ibdata1:76M:ibdata2:128M:autoextend
改为(格式错误，冒号改分号)：
innodb_data_file_path=ibdata1:76M;ibdata2:128M:autoextend

10、innodb_autoinc_lock_mode

• 设置为0时
  也就是官方说的traditional级别，该自增锁是表锁级别，且必须等待当前SQL执行完成后或者回滚掉才会释放，这样在高并发的情况下可想而知自增锁竞争是比较大的；
• 设置为1时
  也就是官方说的consecutive级别，这时如果是单一的insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其他事务中已经有session获取了自增锁）。另外当SQL是一些批量insert sql时，比如insert into …select …,load data,replace ..select..时，这时还是表级锁，可以理解成退化为必须等待当前SQL执行完才释放。可以认为，该值为1时是相对比较轻量的锁，也不会对复制产生影响，唯一的缺陷是产生的自增值不一定是完全连续的；
• 设置为2时
  所有insert种类的SQL都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当binlog_format为statement时，这时的复制没法保证安全，因为批量的insert，比如insert ..select..语句在这个情况下，也可以立马获取到一大批的自增id值，不必锁整个表，slave在回放这个sql时必然会产生错乱。
  参考：innodb_autoinc_lock_mode的表现形式和选值参考

11、通过status中的Handler_read_%查看索引使用情况

• Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引（这个值越低越好）。
• Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
• Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
• Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
• Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
• Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

12、mysqldump备份blob类型的表

用mysqldump备份出数据库内容到SQL文件。
数据库表有blob类型字段的时候，再导入的时候就会因为blob字段内容乱码。
解决办法：mysqldump命令，加 –hex-blob参数，这样备份出来的sql文件即可。

13、innodb_fast_shutdown参数：InnoDB关机模式

• 如果值为0，InnoDB在关闭之前执行缓慢关闭，完全清除和插入缓冲区合并。
• 如果值为1（默认值），InnoDB将在关机时跳过这些操作，这个过程称为快速关闭。
• 如果值为2，InnoDB会刷新其日志并关闭冷却，就好像MySQL已经崩溃; 没有提交的事务丢失，但崩溃恢复操作使下一次启动需要更长时间。

在极端情况下，缓慢关闭可能需要几分钟或甚至几个小时，而大量数据仍然被缓存。 在MySQL主要版本升级或降级之前，请使用慢速关机技术，以便在升级过程更新文件格式的情况下，所有数据文件都经过充分准备。

在紧急情况或故障排除情况下使用innodb_fast_shutdown = 2，如果数据有损坏的风险，可以绝对最快关闭。

14、xtrabackup版本部分区别

version-2.2.6 在指定–no-timestamp参数下，不需要手工创建备份子目录，而version-2.4.1需要创建

15、实时远程备份MySQL的binlog

从MySQL5.6开始，mysqlbinlog支持将远程服务器上的binlog实时复制到本地服务器上。备份命令：

mysqlbinlog --read-from-remote-server --raw --host=remote_IP --port=3306 --user=repl --password=repl --stop-never  mysql-bin.xxxx

解释如下：

--read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。--raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。--user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。--stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。mysql-bin.xxxxx：代表从哪个binlog开始复制。除了以上选项外，还有以下几个选项需要注意：--stop-never-slave-server-id：在备份远程服务器的binlog时，mysqlbinlog本质上就相当于一个从服务器，该选项就是用来指定从服务器的server-id的。默认为-1。--to-last-log：代表mysqlbinlog不仅能够获取指定的binlog，还能获取其后生成的binlog，获取完了，才终止。如果指定了--stop-never选项则会隐式打开--to-last-log选项。--result-file：用于设置远程服务器的binlog，保存到本地的前缀。譬如对于mysql-bin.000001，如果指定--result-file=/test/backup-，则保存到本地后的文件名为/test/backup-mysql-bin.000001。注意：如果将--result-file设置为目录，则一定要带上目录分隔符“/”。譬如--result-file=/test/，而不是--result-file=/test，不然保存到本地的文件名为/testmysql-bin.000001。

远程备份脚本：

#!/bin/shBACKUP_BIN=/usr/bin/mysqlbinlogLOCAL_BACKUP_DIR=/backup/binlog/BACKUP_LOG=/backup/binlog/backuplogREMOTE_HOST=192.168.244.145REMOTE_PORT=3306REMOTE_USER=replREMOTE_PASS=replFIRST_BINLOG=mysql-bin.000001#time to wait before reconnecting after failureSLEEP_SECONDS=10##create local_backup_dir if necessarymkdir -p ${LOCAL_BACKUP_DIR}cd ${LOCAL_BACKUP_DIR}## 运行while循环，连接断开后等待指定时间，重新连接while :do  if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 0 ];then     LAST_FILE=${FIRST_BINLOG}  else     LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} | grep -v backuplog |tail -n 1 |awk '{print $9}'`  fi  ${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE}  echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止，返回代码：$?" | tee -a ${BACKUP_LOG}  echo "${SLEEP_SECONDS}秒后再次连接并继续备份" | tee -a ${BACKUP_LOG}    sleep ${SLEEP_SECONDS}done

转自：https://www.cnblogs.com/ivictor/p/5502240.html

16、通过gdb修改mysql参数

可以通过gdb修改mysql参数，这种修改会立即生效，即使是已经建立的连接，类似在所有连接都执行了修改参数操作一样。

gdb -p $(cat /data/mysql/mysql-xxx.pid) -ex "set variables_name=xxx" -batch

或者

gdb -p pid_number -ex "set variables_name=xxx" -batch

例如：由于连接过多无法登陆mysql，则可以使用上述命令先修改max_connections，然后登陆数据库分析连接过多的原因；

[root@mycat-1 mysql]# gdb -p 13609 -ex "set max_connections=10" -batch[New LWP 14203][New LWP 14161][New LWP 13699][New LWP 13698][New LWP 13697][New LWP 13696][New LWP 13674][New LWP 13673][New LWP 13672][New LWP 13671][New LWP 13670][New LWP 13669][New LWP 13668][New LWP 13667][New LWP 13666][New LWP 13665][New LWP 13664][New LWP 13663][New LWP 13657][New LWP 13655][New LWP 13654][New LWP 13653][New LWP 13652][New LWP 13651][New LWP 13650][New LWP 13649][New LWP 13648][New LWP 13647][New LWP 13646][New LWP 13645][New LWP 13644][New LWP 13643][New LWP 13642][New LWP 13641][New LWP 13640][New LWP 13639][New LWP 13638][New LWP 13637][New LWP 13636][New LWP 13635][New LWP 13634][New LWP 13633][New LWP 13632][New LWP 13631][New LWP 13630][New LWP 13629][New LWP 13628][New LWP 13627][New LWP 13626][New LWP 13625][New LWP 13624][New LWP 13623][New LWP 13622][New LWP 13614][Thread debugging using libthread_db enabled]Using host libthread_db library "/lib64/libthread_db.so.1".0x00007f9b38362dfd in poll () from /lib64/libc.so.6

17、建表时设置auto_increment之后重启mysql服务遇到的问题

由于项目需要，需要设置一个表的自增初始值为5000000，但是创建表之后，没有插入数据，重启mysql服务，anto_increment自动消失；

mysql> create table test_5(id int primary key auto_increment)engine=innodb auto_increment=200;Query OK, 0 rows affected (0.28 sec)mysql> show create table test_5\G*************************** 1. row ***************************       Table: test_5Create Table: CREATE TABLE `test_5` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> quitBye

重启之后：

mysql> show create table test_5\G*************************** 1. row ***************************       Table: test_5Create Table: CREATE TABLE `test_5` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

插入之后，自增初始值自动按照auto_increment_offset定义值进行插入；