# 优化mysql数据导入速度
1.数据刷盘策略调整：
sync_binlog,innodb_flush_log_at_trx_commit,slow_query_log
```sql
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;
```bash
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.参数还原
```sql
set global sync_binlog = 1;
set global innodb_flush_log_at_trx_commit = 1;
set global slow_query_log = on;
```
# mysql 远程执行脚本
```bash
nohup mysql -uroot -hxxx.com -p -P3306 -Dschema_name -e "source /tmp/load.sql" >> /tmp/nohup.txt
```
# MySQL字符集乱码情况
# 授权
```sql
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
# 显示用户正在运行的线程
```sql
SELECT concat('kill ', id ,';')
FROM `information_schema`.`PROCESSLIST`
WHERE state='statistics';
```
# 死锁、慢查询相关查询
## 显示慢查询状态及日志目录
```sql
SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';
```
## 开启慢查询(说明: 1开启;0关闭;)
```sql
SET GLOBAL slow_query_log = 1;
```
## 显示慢查询阈值(单位秒)，默认执行时间超过10s才会被记录到日志
```sql
SHOW VARIABLES LIKE '%long_query%';
```
## 设置慢查询阈值 (注意:设置后需要重新打开mysql客户端才能到最新的值)
```sql
SET GLOBAL long_query_time = 2;
```
## 查看死锁的日志是否开启
```sql
SHOW GLOBAL VARIABLES LIKE "%innodb_print_all_deadlocks%";
```
## 开启记录死锁
```sql
SET GLOBAL innodb_print_all_deadlocks=1
```
## InnoDB 行锁等待超时时间(默认为50秒)
```sql
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 查看当前会话
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 查看全局
设置
```
## 获取死锁日志信息
```sql
SHOW ENGINE INNODB STATUS
```
# 查询sql mode
```sql
select @@sql_mode;
```
# 查询 MyISAM 引擎的表
```sql
SELECT table_catalog
,table_schema
,table_name
,ENGINE
FROM information_schema.tables
WHERE table_schema='xxx'
AND ENGINE='MyISAM';
```
# 查询一个库或者表的大小
```sql
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M')
FROM information_schema.tables
WHERE table_schema='ss'
AND table_name='tt';
```
# 查询各表的总记录行数
```sql
SELECT table_name,table_rows
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'xxx'
ORDER BY table_rows DESC
```
# 查询表的分区情况
```sql
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插件支持中文）
需要先设置分词大小
```bash
[mysqld]
ngram_token_size=2
```
```sql
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);
```
# 随机查询记录
```sql
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;
```
# 分析性能
```sql
#SET profiling = 1;
## 列出最近的会话
SHOW PROFILES;
## 你可以给SHOW PROFILES指定一个Query_ID来查看指定的语句
SHOW PROFILE FOR QUERY xx;
## 还可以给输出添加新的列。如，查看用户和CPU使用
SHOW PROFILE CPU FOR QUERY 1;
```
# 查看索引散列程度 `Cardinality`
```sql
SHOW INDEX FROM user;
```
# 处理数据临时备份表，支持mysql 5.6、5.7
```sql
CREATE TABLE table_user_copy LIKE table_user;
INSERT IGNORE INTO table_user_copy
SELECT *
FROM table_user;
```
# 查询名为xxx的字段
```sql
SELECT column_name, table_name FROM information_schema.columns WHERE table_schema = 'xxx_db' AND column_name LIKE '%xxx%';
```
# 查询某一表的字段
```sql
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
```
# 查询外键依赖
```sql
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时间戳
```sql
SELECT UNIX_TIMESTAMP()
Select UNIX_TIMESTAMP('2006-11-04 12:23:00');
select FROM_UNIXTIME(1496200744);
```
# 查询表记录行数
```sql
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/
常见用法：
```bash
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 过滤某一时段的日志到一个临时文件，以方便分析。
```bash
/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 搜索超大文本文件](/linuxchang-yong-ming-ling-za-ji/#autocjs-heading-15)。
# 在线修改大表结构
可使用 pt-online-schema-change
# 归档历史数据
可使用 pt-archiver
MySQL 杂记
Copyright: 采用 知识共享署名4.0 国际许可协议进行许可