mysql查询优化相关技术

公司dba团队分享了mysql查询优化相关技术,感觉干货比较多,脱敏重新排版分享下

分享整体内容如下,共计3节21点

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

0x01 SQL查询执行顺序

执行顺序 SQL关键字 执行内容 说明
8 SELECT 根据选择的字段,结果写入虚拟表T8
9 DISTINCT 字段 对SELECT的结果T8执行去重后, 写入虚拟表T9
1 FROM 左表 as a 对FROM中的左表与右表执行笛卡尔积,生成虚拟表T1 每步操作结果都会生成一个虚拟表, 这里用T加执行顺序号来命名
3 LEFT JOIN 右表 as b 如指定了左外连接,会将左表中存在,但右表不存在的行,添加到T2表,生成虚拟表T3; 如果FROM中包含多个表, 则会将T3与下一表重复执行步骤1~3 Join类型: Inner: 内联接, 等值连接 Outer: 外连接, 常用的Left join(以左表记录为基准), Right join(以右表记录为基准)
2 ON a.id = b.id 对T1表应用on条件筛选, 符合条件的行写入虚拟表T2 Inner jojn中on 与where条件效果相同; left join中on与where条件效果不同;
4 WHERE 条件 对T3表应用where条件过滤, 符合条件的记录写入T4表
5 GROUP BY 字段 根据group by中字段 , 对T4执行分组,生成虚拟表T5 注意:不在GROUP BY中的字段,如果出现在SELECT中,都要使用聚合类函数,不推荐下面的写法: Select a,b, sum(c) from t Group by a
6 WITH ROLLUP [CUBE] 对T5结果,按不同维度执行统计,生成虚拟表T6 GROUP BY ROLLUP(A,B,C) 结果 (A,B,C),(A,B), (A) GROUP BY CUBE(A,B,C)结果 (A,B,C),(A,B)(A,C),(B,C)(A),(B),(C)
7 HAVING 条件 对T6结果执行过滤,结果写入虚拟表T7
10 ORDER BY 字段 对DISTINCT后的结果T9, 执行排序后写入虚拟表T10 这部分可以使用字段别名, 字段运算或按字段顺序号执行排序操作
11 LIMIT 对T10取出指定行数据记录后,返回最终结果
Read more   2020/4/17 posted in  规范 SQL

三分钟搭建大数据sql开发平台

Read more   2019/12/31 posted in  JAVA 三分钟系列 商业智能 SQL

yearning docker部署注意事项

第一次启动时,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的工单
2018/5/20 posted in  SQL

inception安装

docker 安装, docker hub地址

https://hub.docker.com/r/hhyo/inception

github仓库地址

https://github.com/hhyo/inception

inception 配置文件

[inception]
general_log=1
general_log_file=inception.log
port=6669
socket=/tmp/inc.socket
character-set-client-handshake=0
character-set-server=utf8
inception_remote_system_password=root
inception_remote_system_user=wzf1
inception_remote_backup_port=3306
inception_remote_backup_host=127.0.0.1
inception_support_charset=utf8,utf8mb4
inception_enable_nullable=0
inception_check_primary_key=1
inception_check_column_comment=1
inception_check_table_comment=1
inception_osc_on=OFF
inception_osc_bin_dir=/usr/bin
inception_osc_min_table_size=1
inception_osc_chunk_time=0.1
inception_enable_blob_type=1
inception_check_column_default_value=1

中文文档:https://inception-document.readthedocs.io/zh_CN/latest/

2018/5/20 posted in  SQL

perocona

perocona toolkit
重复锁查询
杀慢查询

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

image-20180610100427468

2018/5/20 posted in  SQL

把所有字段转成utf8mb4

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');
2018/5/20 posted in  SQL

死锁

查看当前的数据库隔离级别

​ select @@global.tx_isolation

http://www.ywnds.com/?p=4949

​ 两个事务开始修改多个表时,如果访问表的顺序不同,会出现互相等待对方释放锁,然后才能继续处理的情况。MySQL会立刻发现这种情况并且终止较小的事务,允许其他的事务执行。

2018/5/20 posted in  SQL

mysql配置主从复制小记

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.%
2018/5/20 posted in  SQL

使用xtrabackup进行mysql备份恢复

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 &
2018/5/20 posted in  SQL

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;
2017/3/10 posted in  SQL

三分钟实现程序分库分表

2014/7/6 posted in  PHP SQL