1. 查询优化
   1. SQL查询执行顺序
   2. Where条件相关性能问题
   3. 子查询(in/exist)相关
   4. 分组排序相关
2. 索引优化
3. 事务

0x01 SQL查询执行顺序

第一次启动时，yearning是无法访问的，因为第一次mysql需要初始化yearning的数据，可能yearning先于mysql启动了，导致读取数据出现异常

解决方法

• 先启动mysql，通过查看日志，确认mysql初始化完成后在启动yearning

docker-compose.yml

version: '2'

services:
  db:
    image: mysql:5.7
    volumes:
      - ./docker/etc/mysql/:/etc/mysql/conf.d/
      - ./db_data/:/var/lib/mysql/
      - ./init-sql/:/docker-entrypoint-initdb.d/
    restart: always
    ports:
      - "3406:3306"
    environment:
      MYSQL_ROOT_PASSWORD: yearning
      MYSQL_DATABASE: Yearning
      MYSQL_USER: yearning
      MYSQL_PASSWORD: yearning
  yearning:
    image: registry.cn-hangzhou.aliyuncs.com/cookie/yearning:latest
    depends_on:
      - db
    ports:
      - "9180:8000"
    environment:
      HOST: localhost
      MYSQL_PASSWORD: yearning
      MYSQL_USER: root
      MYSQL_ADDR: db
  inception:
    image: hhyo/inception:latest
    depends_on:
      - db
    ports:
      - "6669:6669"
    volumes:
      - ./inc.cnf:/etc/inc.cnf

# 默认账号：admin，默认密码：Yearning_admin
# 感谢 eacdy 张功震 贡献

注意的点

1. 必须先给用户赋予ddl权限，用户才可以提交ddl的工单

perocona toolkit
重复锁查询
杀慢查询

pt-query-digest 分析慢查询日志

use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "supply_chain2" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql  
FROM `TABLES` where table_schema like "supply_chain2" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "supply_chain2" and data_type in ('varchar');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "supply_chain2"
-- and data_type in ('text','tinytext','mediumtext','longtext');

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@`%` IDENTIFIED BY 'repl';

flush privileges;

# for master-slave architecture
log_bin=mysql-bin
server_id=10

SHOW MASTER STATUS\G

log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1

CHANGE MASTER TO MASTER_HOST='mysql',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

SHOW SLAVE STATUS\G

START SLAVE;

replicate_wild_do_table = test.%

docker run --name=cpx_prod20180430023756 -v $PWD:/root/tools -e MYSQL_ROOT_PASSWORD=root -d registry.cn-hangzhou.aliyuncs.com/xiayu/mysql-5.6-xtrabackup:latest

innobackupex --apply-log /root/tools/mysql --user=root --password=root

innobackupex --defaults-file=/etc/mysql/my.cnf --copy-back /root/tools/mysql

mysqld_safe --defaults-file=/root/tools/mysql/backup-my.cnf --user=mysql --datadir=/root/tools/mysql/ &

innobackupex --defaults-file=/root/tools/mysql/backup-my.cnf --apply-log /root/tools/mysql

mysqld_safe --defaults-file=/etc/mysql/my.cnf --user=root --datadir=/var/lib/mysql &

set names utf8
source

select语句 into outfile '外部文件路径' fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
load data infile '外部文件路径' into table 表名 fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

只导出表结构
mysqldump -u 用户名 -p -d --add-drop-table 数据库名 > 导出的文件名

mysqldump  -uroot -p --databases test mysql #空格分隔
mysqldump  -uroot -p -all-databases
mysql -uroot -proot test < test.sql

查看锁等待

SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM
    information_schema.innodb_lock_waits w
        INNER JOIN
    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
        INNER JOIN
    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;