导图社区 MySQL
- 19
- 0
- 0
- 举报
MySQL
该思维导图内容丰富，结构清晰，适合用于学习和复习MySQL数据库的相关知识，帮助用户系统地掌握MySQL的安装、使用、优化等方面的内容。
编辑于2025-07-02 15:30:21- mysql
- MySQL优化
- MySQL基础
- 相似推荐
- 大纲
MySQL
备份
物理备份
备份什么
数据
配置文件
二进制日志
事务日志
热备
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 优化
分支主题