导图社区 MySQL
该思维导图内容丰富,结构清晰,适合用于学习和复习MySQL数据库的相关知识,帮助用户系统地掌握MySQL的安装、使用、优化等方面的内容。
编辑于2025-07-02 15:30:21MySQL
备份
物理备份
备份什么
数据
配置文件
二进制日志
事务日志
热备
xtrabackup(物理备份)
InnoDB
mysqldump
温备
可读不可写
冷备(离线)
不可读写
备份工具
mysqldump(逻辑)
导出所有数据库
mysqldump -uroot -p -S /tmp/mysql.sock --single-transaction -A >/tmp/all.sql
导出某几个库
mysqldump -uroot -p -S /tmp/mysql.sock --single-transaction -B test1 test2 >/tmp/all.sql
导出某数据库的几个表
mysqldump -uroot -p -S /tmp/mysql.sock --single-transaction test t1 >/tmp/t1.sql
导出表结构
mysqldump -uroot -p -S /tmp/mysql.sock --single-transaction --all-databases -d >/tmp/all.sql
恢复过程
mysql -uroot -p -S /tmp/mysql.sock < /tmp/all.sql
mysqlhotcopy(物理)
xtrabackup(开源工具)
相关命令
FLUSH TABLES;
刷新表
LOCK TABLES;
锁表
FLUSH TABLES WITH READ LOCK;
以读的方式锁定表
UNLOCK TABLES;
解锁
FLUSH LOGS;
刷新日志
SHOW BINARY LOGS;
显示二进制日志位置信息
--master-data={0|1|2}
0:不记录二进制日志文件及位置
1:以CHANGE MASTER TO的方式记录位置,可用于恢复后直接启动从服务器;
2:以CHANGE MASTER TO的方式记录位置,但默认为被注释;
--lock-all-tables
锁定所有表
--flush-logs
执行日志flush
如果指定库中的表类型均为InnoDB,可使用--single-transaction启动热备
备份多个库
--all-databases:备份所有库
--databases db_name,db_name...:备份指定库
完整备份语句
mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /path/to/somefile.sql
purge binary logs to "binlog_name";
删除指定二进制日志前的日志
mysqlbinlog binlog_name;
mysqlbinlog解析二进制日志
mysqlbinlog bin_name | mysql -uroot -p
安装
-DENABLE_PROFILING=1
性能分析(至关重要)
-DCMAKE_INSTALL_PREFIX=
安装路径
-DMYSQL_DATADIR=
数据存储路径
-DSYSCONFDIR=
配置文件路径
-DWITH_READLINE=1
-DWITH_SSL=system
ssl加密连接
-DWITH_ZLIB=system
压缩
-DWITH_LIBWRAP=0
-DDEFAULT_CHARSET=utf8
默认字符集
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
默认套接字
cmake
cmake编译
cmake .
清理此前编译
make clean && rm CMakeCache.txt
cmake帮助
cmake . -LH or ccmake .
三种方式
专用软件包管理器
rpm
deb
通用二进制格式
gcc
x86,x64
icc
intel c编译平台
源代码包
编译安装
cmake编译
初始生成5个用户
3个root
2个匿名用户
客户端工具
mysql
mysqldump
mysqladmin
mysqlcheck
mysqlimport
导出为csv文件
select * from test.test_csv into outfile '/tmp/test_csv.csv' fields terminated by ',' optionally enclosed by '' lines terminated by '\n';
导入数据
load data infile '/tmp/test_csv.csv' into table test.test_csv fields terminated by ',' optionally enclosed by '' lines terminated by ' ';
引擎
MyISAM
.frm:表结构
.MYD:表数据
.MYI:表索引
InnoDB
建议每个表一个表空间文件
打开参数:innodb_file_per_table=1
.frm:表结构
.ibd:表空间(数据和索引)
db.opt:默认字符集和排序规则
初始化数据库
mysql5.6以前
scripts/mysql_install_db --user=mysql --datadir=/path/to/dir
mysql5.7以后
mysqld --initialize-insecure --user=mysql --datadir=/path/to/dir
配置文件优先级
/etc/my.cnf -> /etc/mysql/my.cnf -> $MYSQL_HOME/my.cnf -> --default-extra-file=/path/to/somefile -> ~/.my.cnf
命令
select 函数();
执行内置函数
create
create index index_name on tb_name(col,....) using BTREE;
创建B树索引,可随意创建B树索引,但hash索引不行;
alter
alter table table_name ENGINE=InnoDB;
更改表的搜索引擎。实质是创建一张新表,把数据导入再删除老表
alter table tb1 add foreign key (cid) references tb2(cid)
创建外键,要确保外键列数据类型一致
alter database|table database_name|table_name default character set utf8;
修改库或表的字符集
alter table table_name drop primary key;
删除主键,如有自增长需先删除自增长
alter table tb_name modify cid tinyint unsigned not null;
更改数据类型
show
show character set;
显示服务器支持的字符集
show collation;
支持的排序规则
show global variables like 'sql_mode';
查看sql 模型
show indexs from tb_name;
查看表索引信息
show binlog events in 'mysql-bin.000005';
查看二进制日志事件
delete
drop
drop index index_name on tb_name;
删除索引
help
help create databse|table|index;
命令帮助
desc
desc table_name;
显示表结构信息
mysqlbinlog
mysqlbinlog mysql-bin.000005;
查看二进制日志内容
--start-datetime=;--stop-datetime=
--start-position=;--stop-position=
flush
flush logs;
flush hosts;
purge
purge binary logs to '某二进制日志文件名';
清除日志
DML
select
where
逻辑操作符
and、or、not
比较操作符
IS NULL
IS NOT NULL
BETWEEN ... AND ...
like '%...%'
REGEXP, RLIKE
IN
ORDER BY field_name {ASC|DESC}
LIMIT num
mysql服务器变量
全局变量
show global variables
会话变量
show [session variables]
调用全局变量
select @@global.sql_mode;
调用会话变量
select @@session.sql_mode;
注意
InnoDB才支持外键
外键约束能一定程度保证数据安全,但消耗系统资源,不建议使用
索引有两种类型
BTREE
支持全部,为默认
HASH
DBA
开发DBA
数据库设计、SQL语句、存储过程、存储函数、触发器
管理DBA
安装、升级、备份、恢复、用户管理、权限管理、监控、性能分析、基准测试
数据类型
数值型
精确数值
int、decimal
近似数值
float、dubble
字符型
定长型
char(n)
变长型
varchar(n)
日期时间型
date、time、datetime、timestamp
事务
锁
共享锁
允许读取一个数据,同时允许其他事务来读取数据
排它锁
允许当前事务修改数据,但是不允许其他的锁类型
锁粒度
最小是行级别,采用行锁,可以最大限度保证数据库的并发能力
如何去查询mysql的锁情况
show full processlist;
show engine innodb status;
selec * from information_schema.innodb_locks\G
查看当前锁信息
select * from information_schema.innodb_lock_waits\G
查看锁等待信息
innodb三种锁算法
Record Lock
单个行记录上的锁算法
加在指定行上的锁
Gap Lock
间隙锁,锁定一个范围
锁定的是行之间的间隙,一般在rr模式下,避免幻读的产生
Next-Key Lock
上两个的结合
MyISAM锁
简而言之,就是读锁会阻塞写,但不会堵塞读。而写锁则会把读和写都堵塞
show open tables;
查看哪些表被加锁了
show status like 'table%';
通过检查table_locks_waited和table_locks_immediate状态来分析系统上的表锁定;
table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1;
table_locks_waited:出现表级锁定急用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重的表级锁争用情况;
innodb引擎
并发事务处理带来的问题
更新丢失(Lost Update)
当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其它事务的存在,就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新。
脏读(Dirty Reads)
事务A读到了事务B已修改但尚未提交的数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取的数据无效,不符合一致性要求。
不可重复读(Non-Repeatable Reads)
事务A读取到了事务B已提交的修改数据,不符合事务的隔离性
一个事务在读取某些数据后的某个时间,再次读取以前的读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了,这种现象就叫做“不可重复读”。
幻读(Phantom Reads)
事务A读到了事务B已提交的新增数据,不符合事务的隔离性
事务隔离级别
read uncommited
未提交读
造成脏读
read commited
已提交读
造成幻读
repeateable read
可重复读
解决幻读问题
SERIALIZABLE
可序列化
完全串行化执行对应的事务
查看事务隔离级别
show variables like '%iso%'
mysql默认事务隔离级别为可重复读
行锁
索引失效导致行锁变表锁
示例:vachar类型不写引号
间隙锁的危害
间隙锁:当我们用范围条件反射而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引 加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是间隙锁(Next-Key锁)
危害:因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。
间隙锁有一个比较致使的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定, 而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
如何锁定一行数据
1、打个起点>begin;
2、select * from test where a=8 for update;
手动为某条数据上锁
3、commit;解除锁定
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show status like 'innodb_row_lock%';
innodb_row_lock_current_waits:当前正在等待锁定的数量;
innodb_row_lock_time:从系统启动到现在锁定总时间长度;
innodb_row_lock_time_avg:每次等待所花平均时间;
innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
innodb_row_lock_waits:系统启动后到现在总共等待的次数;
优化建议
尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
合理设计索引,尽量缩小锁的范围
尽可能较少索引条件,避免间隙锁
尽量控制事务大小,减少锁定资源量和时间长度
尽可能低级别事务隔离
页锁
开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
binlog
三种格式
statment格式
特别:记录每一条数据的SQL,将执行的每一条SQL记录在binlog中,减少日志量,节省IO,提高性能
缺点:某些SQL中的函数无法使用,比如SYSDATE(),在同步过程中会出现无法同步的问题。
mixed格式
特点:一般的语句使用SQL语句来记录,遇到特殊的语句使用ROW格式来记录,保证数据的一致性和复制的准确性。
row格式
特点:binlog中仅仅记录哪一条记录被修改,不记录dml的SQL语句,会详细记录每一row的更改细节,不会出现无法复制的问题
缺点:因为要记录每一条修改记录的日志,所以大量占用磁盘IO和大量使用硬盘空间。
清理binlog日志
手工
reset master;
删除master的binlog,主从结构慎操作,容易造成主从不同步
reset slave;
删除slave的binlog,主从结构慎操作,容易造成主从不同步
purge master logs before '2022-05-18 17:00:55';
删除指定日期以前的日志
purge master logs to 'binlog.000002';
删除指定日志文件的日志索引中binlog日志文件
自动
show binlog logs; show variables like 'expire_logs_days'; set global expire_logs_days=3;
通过binlog参数(expire_logs_days)来实现mysql自动删除binlog
原理
sql执行
用户传入sql>查询缓存>解析器(生成sql解析树)>预处理器(进一步优化)>查询优化器>sql执行计划>查询执行引擎(innodb等)>返回数据给用户
优化器
RBO
基于规则的优化器
CBO
基于成本的优化器
成本值是根据索引、表、行的统计信息计算出来的
统计信息
没有直方图也无法手工删除统计信息
统计信息由存储引擎实现
统计信息分为索引的统计信息,表的统计信息
查看统计信息
索引统计信息
show index from table 或information_schema.statistics表
表统计信息
show table status like 或information_schema.tables表
Analyze table收集表和索引的统计信息,适用于MyISAM和InnoDB
Innodb在下列情况会重新收集统计信息
表第一次打开的时候
表修改的行超过1/16或新插入20亿行的时候计算索引的统计信息
执行show index或查询information schema下的表
执行计划
查看执行计划
explain select/delete ****
mysql高级
mysql介绍
单表瓶径500~1000万条数据
修改字符集问题(中文乱码)
show variables like '%char%';
查看字符集
修改配置文件参数为utf8
character_set_database
character_set_server
已生成的库表字符集变更
alter database dataname character set 'utf8';
修改数据库的字符集
alter table table_name convert to character set 'utf8';
修改数据表的字符集
原有的数据如果是用非'utf8'编码,数据不会改变
索引
索引简介
索引是排好序的快速查找数据结构
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数 据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引
一般说来索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上
类型
B+树索引 多路搜索树
聚集索引
次要索引
复合索引
前缀索引
唯一索引
哈希牵引
性能分析
MySql Query Optimizer
MySQL常见瓶颈
Expain
优劣势
优势
提高数据检索的效率,降低数据库的IO成本
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
劣势
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
虽然索引大大提高了查询速度,同时却会降低更新表的速度,中对表进行insert,update,delete。因为更新表时,Mysql不仅 要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的Mysql有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询。
哪些适合建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段,外键关系建立索引
频繁更新的字段不适合创建索引
where条件里用不到的字段不创建索引
高并发下倾向创建组合索引
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段应该创建索引
哪些不适合建索引
表记录太少,一般300万以上应该建索引
3层B树可以存储上百万数据
经常增删改的表
数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。 注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果
7种join理论
内连接,A、B公有
select <select_list> from TableA A inner join TableB B on A.key=B.key
左连接,A的全部
select <select_list> from TableA A left join TableB B on A.key=B.key
右连接,B的全部
select <select_list> from TableA A right join TableB B on A.key=B.key
A的独有
select <select_list> from TableA A left join TableB B on A.key=B.key where B.key is NULL
B的独有
select <select_list> from TableA A right join TableB B on A.key=B.key where A.key is NULL
全连接,A、B的全部
select <select_list> from TableA A left join TableB B on A.key=B.key union select <select_list> from TableA A left join TableB B on A.key=B.key
A、B独有
select <select_list> from TableA A left join TableB B on A.key=B.key where B.key is null union select <select_list> from TableA A left join TableB B on A.key=B.key where A.key is null
性能分析
mysql常见瓶颈
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态
MySql Query Optimizer
Explain
查看执行计划
使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理你的sql语句的,分析你的查询语句或是表结构的性能瓶颈。
能干嘛
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
怎么玩
执行计划包含的信息
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
Explain + SQL语句
执行计划字段解释
id
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
三种情况
id相同,执行顺序由上到下
id不同,如果是子查询,id的序号会递增,id值越大优先级超高,越先被执行
id相同不同,同时存在,id越大越先执行,相同的从上往下顺序执行
select_type
6个类型|6个值
SIMPLE
PRIMARY
SUBQUERY
DERIVED
UNION
UNION RESULT
查询的类型,主要是用于区别 普通查询、联合查询、子查询等的复杂查询
SIMPLE
简单的select查询,查询中不包含子查询或者union
PRIMARY
查询中若包含任何复杂的子部分,最外层查询则被标记为
SUBQUERY
在select或where列表中包含了子查询
DERIVED
在from列表中包含的子查询被标记为DERIVED(衍生) Mysql会递归执行这些子查询,把结果放在临时表里
UNION
若第二个select出现在union之后,则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层select将被标记为:DERIVED
UNION RESULT
从UNION表获取结果的SELECT
type
8种值
system>const>eq_ref>ref>range>index>ALL以上7种及NULL
system
表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
const(常量)
表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。
如将主键置于where列表中,mysql就能将该查询转换为一个常量
eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref
非唯一性索引扫描,返回匹配某个单独值的所有行。
本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
range
只检索给定范围的行,使用一个索引来选择来选择行。
一般就是在where中出现between,<,>,in等
index
Full Index Scan, index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。 也就是说虽然all和index都是读全表,但index是从索引(内存)中读取的,而all是从硬盘中读的。
all
全表扫描:从硬盘中读取数据,一般情况下数据达上百万需做索引
一般来说,得保证查询至少达到range级别,最好能达到ref
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
possible_keys
理论上可能用到的索引:显示可能应用在这张表中的索引,一个或多个。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
key
实际使用的索引,如果为null,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
如果索引字段可以为Null,则会使用1个字节进行标识 2个字节标识为可变长度字符类型
在utf8中,一个字符占3个字节 gbk中一个字符占2个字节 litain中一个字符占1个字节
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,越小越好
Extra
十分重要的额外信息
using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取
Mysql中无法利用索引完成的排序操作称为“文件排序”
出现不好,需要优化索引
using temporary
使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by 和 分组查询 group by
group by 尽量与索引一致
更不好,十死无生
using index
表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错!
不需要回表查询
如果同时出现using where, 表明索引被用来执行索引键值的查找。
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
覆盖索引
覆盖索引:查询列要为所建索引的所有列,select的数据列只用从索引中就能够取得,不必读取数据行。
如果要使用覆盖索引,一定要注意select列表中只取需要的列,不可select *, 因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
using where
使用了where过滤
需要回表查询
using join buffer
使用了连接缓存
impossible where
where 子句的值总是false,不能用来获取任何元组
select tables optimized away
在没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操 作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作,在找到第一匹配的元组后即停止同样值的查找动作
索引优化
索失效
索引口绝
全值匹配我最爱,最左前缀要遵守; 带头大哥不能死,中间兄弟不能断; 索引列上少计算,范围之后全失效; Like百分写最右,覆盖索引不写星; 不等空值还有or,索引失效要少用; VAR引号不可丢,SQL高级也不难;
全值匹配我最爱
最佳左前缀法则
如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不路过索引中的列。
不在索引上做任何操作(计算、函数、(自动or手动)类型转换,会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描
is null,is not null|也无法使用索引
like以通配符开头(%name...),mysql索引失效会变成全表扫描的操作
可以用覆盖索引解决两端都是%索引失效的问题
字符串不加单引号索引失效
少用or,用它来连接时会索引失效
join优化
尽可能减少join语句中的NestedLoop的循环总次数; 永远用小结果集驱动大的结果集;
优先优化嵌套循环(NestedLoop)的内层循环
保证Join语句中被驱动表上Join条件字段已经被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置
一般性建议
对于单健索引,尽量选择针对当前query过滤性更好的索引
在选择组合索引时,当前query中过滤性最好的字段在索引顺序中,位置越靠前越好
在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查询截取分析
查询优化
优化原则:小的数据集驱动大的数据集;
order by 关键字优化
满足最左前列原则,会使用index方式排序
尽量使用index方式排序,避免使用filesort方式排序
如果不在牵引上,filesort有两种算法
双路排序
mysql4.1之前
单路排序
占用较多空间
优化策略
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
在取得数据量小时sort_buffer容量时,单路优于双路,如大于sort_buffer容量时,多次IO,性能不一定优化双路
提高order by的速度
禁用select *
当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法——单路排序,否则使用多路排序
两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
尝试提高sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,国为这个参数是针对每个进程的
尝试提高max_length_for_sort_data
提高这个参数,会增加改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率。
group by 关键字优化
同order by
group by实质是先排序后进行分组,遵照索引建的最佳左前缀
当无法使用索引列,增大max_length_for_sort_data参数的设备+增大sort_buffer_size参数的设备
where高于having,能写在where限定的条件就不要去having限定了
慢查询日志
show variables like '%slow_query_log%';
开启:set global show_query_log=1
阀值:show variables like '%long_query_time%';
大于阀值的记录,而非大于等于
数据库的慢sql条数:show global status like '%Slow_queries%';
mysqldumpslow
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" slow.log
另外建议在使用这些命令时结合|和more使用,否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 slow.log|more
帮助信息
s:表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式,大小写不敏感;
批量数据脚本
show variables like 'log_bin_trust_function_creators';
如开慢日志时需开启如下参数才能构建函数 set global log_bin_trust_function_creators=1;
创建函数,保证每条数据都不同
随机产生字符串
随机产生部门编号
创建函数据
创建存储过程
调用
DELIMITER ; call fun();
Show Profile
mysql提供的可以用来分析当前会话中语句执行的资源消耗情况,用于SQL的调优的测量
show variables like 'profiling';
set profiling=on;
show profiles;
show profile cpu,block_io for query $query_id;
查看执行生命周期
帮助信息
ALL --显示所有的开销信息
BLOCK_IO --显示块IO相关开销
CONTEXT_SWITCHES --上下文切换相关开销
CPU --显示CPU相关开销信息
IPC --显示发送和接收相关开销信息
MEMORY --显示内存相关开销信息
PAGE_FAULTS --显示页面错误相关开销信息
SOURCE --显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS --显示交换次数相关开销的信息
判断依据(4大杀手)
converting HEAP to MyISAM 查询结果太大,内存不够用了往磁盘上搬运
create tmp table创建临时表
拷贝数据到临时表
用完再删除
copying to tmp table on disk 把内存中临时表复制到磁盘,危险!!!
locked
全局查询日志
永远不要在生产启用
开启:set global general_log=1; set global log_output='TABLE';
此后,你所编写的SQL语句,将会记录到MYSQL库里的general_log表,可以用下面的命令查看
select * from mysql.general_log;
分支主题
日常分析步骤
1、观察,至少跑1天,看看生产的慢SQL情况; 2、开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来; 3、explain+慢SQL分析; 4、show Profile 5、运维经理 or DBA,进行SQL数据库服务器的参数调优;
4部分析法
1、慢查询的开启并捕获; 2、explain+慢SQL分析; 3、show profile查询SQL在mysql服务器里面的执行细节和生命周期情况; 4、SQL数据库服务器的参数调优;
B树
B+树
3层Btree可存储上百万数据
Btree一般都是B+,数据全部存放在叶节点
B+树中查询任意的数据次数为B+树的高度
一些其它优化方法
exist和in
如果主查询数据集大,使用in效率高
如果子查询的数据集大,使用exist效率高
order by 优化
分支主题