优化mysql数据导入速度

1.数据刷盘策略调整：
sync_binlog,innodb_flush_log_at_trx_commit,slow_query_log

set global sync_binlog = 0;
set global innodb_flush_log_at_trx_commit = 0;
set global slow_query_log = off;

2.修改脚本为并行加载
a)insert into table xxx values(xx,xx,xx),(xx,xx,xx)...
b)begin;...;commit;

awk '{if(NR%5000!=0)ORS=" ";else ORS="\n"}1' data.sql > data1.sql
sed 's/^/begin;/' data1.sql > data2.sql
sed 's/$/commit;/' data2.sql > data3.sql
source data3.sql

3.参数还原

set global sync_binlog = 1;
set global innodb_flush_log_at_trx_commit = 1;
set global slow_query_log = on;

mysql 远程执行脚本

nohup mysql -uroot -hxxx.com -p -P3306 -Dschema_name -e "source /tmp/load.sql" >> /tmp/nohup.txt

MySQL字符集乱码情况

MySQL字符集编码.png

授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;

显示用户正在运行的线程

SELECT concat('kill ', id ,';')
FROM `information_schema`.`PROCESSLIST`
WHERE state='statistics';

死锁、慢查询相关查询

显示慢查询状态及日志目录

SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';

开启慢查询(说明: 1开启;0关闭;)

SET GLOBAL slow_query_log = 1;

显示慢查询阈值(单位秒)，默认执行时间超过10s才会被记录到日志

SHOW VARIABLES LIKE '%long_query%';

设置慢查询阈值 (注意:设置后需要重新打开mysql客户端才能到最新的值)

SET GLOBAL long_query_time = 2;

查看死锁的日志是否开启

SHOW GLOBAL VARIABLES LIKE "%innodb_print_all_deadlocks%";

开启记录死锁

SET GLOBAL innodb_print_all_deadlocks=1

InnoDB 行锁等待超时时间(默认为50秒)

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 查看当前会话  
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 查看全局
设置

获取死锁日志信息

SHOW ENGINE INNODB STATUS

查询sql mode

select @@sql_mode;

查询 MyISAM 引擎的表

SELECT table_catalog
      ,table_schema
      ,table_name
      ,ENGINE
FROM information_schema.tables
WHERE table_schema='xxx'
AND ENGINE='MyISAM';

查询一个库或者表的大小

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M') 
FROM information_schema.tables 
WHERE table_schema='ss'
AND table_name='tt';

查询各表的总记录行数

SELECT table_name,table_rows 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'xxx' 
ORDER BY table_rows DESC

查询表的分区情况

SELECT 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
FROM information_schema.partitions  WHERE 
  table_schema = 'sss'
  AND table_name='ttt';

创建、使用全文索引（MySQL 5.7+内置ngram插件支持中文）

需要先设置分词大小

[mysqld] 
ngram_token_size=2

ALTER TABLE `posts` ADD FULLTEXT INDEX `idx_fulltext` (`markdown`) WITH PARSER ngram;
# 按自然语言搜索模式查询 
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('关键词' IN NATURAL LANGUAGE MODE);
# 按布尔全文搜索模式查询 
## 1 匹配既有管理又有数据库的记录 
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('+数据库 +管理' IN BOOLEAN MODE); 
## 2 匹配有数据库，但是没有管理的记录 
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('+数据库 -管理' IN BOOLEAN MODE); 
## 3 匹配MySQL，但是把数据库的相关性降低 
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('>数据库 +MySQL' INBOOLEAN MODE);

随机查询记录

SELECT file_id 
FROM `dc_resource_file` t1 
JOIN (SELECT RAND() * (SELECT MAX(file_id) FROM `dc_resource_file`) AS nid
) t2 
ON t1.file_id > t2.nid
LIMIT 1000;

分析性能

#SET profiling = 1;
## 列出最近的会话
SHOW PROFILES;

## 你可以给SHOW PROFILES指定一个Query_ID来查看指定的语句
SHOW PROFILE FOR QUERY xx;
## 还可以给输出添加新的列。如，查看用户和CPU使用
SHOW PROFILE CPU FOR QUERY 1;

查看索引散列程度 Cardinality

SHOW INDEX FROM user;

处理数据临时备份表，支持mysql 5.6、5.7

CREATE TABLE table_user_copy LIKE table_user;

INSERT IGNORE INTO table_user_copy 
SELECT *
FROM table_user;

查询名为xxx的字段

SELECT column_name, table_name FROM information_schema.columns WHERE table_schema = 'xxx_db' AND column_name LIKE  '%xxx%';

查询某一表的字段

SELECT CONCAT(COLUMN_NAME, ' AS ', COLUMN_NAME, ',') 
FROM information_schema.COLUMNS 
WHERE table_name = 'table_user' 
AND table_schema = 'xxx_db'
ORDER BY ORDINAL_POSITION

查询外键依赖

SELECT 
  CONCAT(table_name, '.', column_name) AS 'foreign key',
  CONCAT(
    referenced_table_name,
    '.',
    referenced_column_name
  ) AS 'references' 
FROM
  information_schema.key_column_usage 
WHERE table_schema = 'xxx_db' 
  AND referenced_table_name='table_user';

查询unix时间戳

SELECT UNIX_TIMESTAMP()  
Select UNIX_TIMESTAMP('2006-11-04 12:23:00');

select FROM_UNIXTIME(1496200744);

查询表记录行数

SELECT table_name,data_length 
FROM information_schema.tables
WHERE table_schema='xxx_db'
AND data_length>'9977856'
ORDER BY table_name ASC
#order by data_length desc

数据库设计结构导出

SELECT 
	c.TABLE_COMMENT `表中文名称`, c.table_name `表英文名称`, 
	IF(c.column_key='PRI', 1, 'NULL') `主键定义`, 
	c.`column_comment` `字段中文名`, c.column_name `字段英文名`, 
	c.data_type `字段类型`, IFNULL(c.CHARACTER_MAXIMUM_LENGTH, 'NULL')`字段长度`,
	IF(s.`TABLE_CATALOG` IS NULL, 'N', 'Y') `是否索引`, c.`column_comment` `注解`
FROM 
(
SELECT c.*, t.TABLE_COMMENT
FROM information_schema.COLUMNS c
INNER JOIN information_schema.tables t
ON t.TABLE_SCHEMA='db_xxx'
AND t.table_name IN ('table_a', 'table_b')
WHERE c.`TABLE_SCHEMA`='db_xxx'
AND c.`TABLE_NAME` IN ('table_a', 'table_b')
) c
LEFT JOIN 
(SELECT *
FROM information_schema.statistics s
WHERE s.TABLE_SCHEMA='db_xxx'
AND s.table_name IN ('table_a', 'table_b')
) s
ON c.column_name=s.column_name
GROUP BY c.table_name, c.column_name
ORDER BY c.table_name, c.ORDINAL_POSITION

ghost blog 更新博文中图片、附件链接为 https

UPDATE posts SET markdown=REPLACE(markdown, 'http://ghost.oss.sherlocky.com', 'https://ghost.oss.sherlocky.com'), 
	html=REPLACE(html, 'http://ghost.oss.sherlocky.com', 'https://ghost.oss.sherlocky.com')
#select * from posts
WHERE LOCATE('http://ghost.oss.sherlocky.com', markdown)>0

mysql 数据一致性比较工具（可分析表数据内容一致性）

工具下载地址：
http://www.ttlsa.com/mysql/mysqldbcompare-compare-two-databases/

常见用法：

mysqldbcompare --server1=root:@192.168.1.121 --server2=root:@192.168.1.121 --changes-for=server1 --skip-row-count --difftype=sql  db_xxx:db_yyy > C:/different_xxx.sql

binlog 恢复日志

可通过 mysqlbinlog 过滤某一时段的日志到一个临时文件，以方便分析。

/usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v --database=db_xxx --start-datetime='2019-01-16 08:00:00' --stop-datetime='2019-01-16 12:00:00' mysql-bin.000001 > /data/tmp/mysql-bin.000001_05.sql

日志文件过大可通过 linux grep、sed 等命令快速查看，参考grep & sed 搜索超大文本文件。

在线修改大表结构

可使用 pt-online-schema-change

归档历史数据

可使用 pt-archiver

Q.E.D.

Think Big, Act Small