MySQL性能优化
2020-05-11 14:05:25 2 举报AI智能生成
MySQL性能优化思维导图
作者其他创作
大纲/内容
MySQL性能优化
MySQL优化概述
MySQL性能的影响因素
1. 数据库结构设计
2. 数据库存储引擎
3. SQL语句的书写
4. 数据库参数配置
5. 服务器操作系统与硬件
MySQL 性能的衡量指标
TPS（Transactions Per Second）
即每秒事务处理量
是一台数据库服务器在单位时间内处理的事务的个数，是支持事务的存储引擎（如InnoDB）等特有的一个性能指标
TPS = （COM_COMMIT + COM_ROLLBACK） / UPTIME
QPS（Queries Per Second）
即每秒查询处理量
是一台服务器每秒能够响应的查询次数，是对一个特定的查询服务器在规定时间内所处理查询量多少的衡量标准
QPS = QUESTIONS / UPTIME
响应时间
从接收请求开始到发送响应之间的时间跨度
MySQL的压测工具
mysqlslap
目标
测试MySQL服务器瓶颈
作用
可创建schema、table、test data
运行负载测试，可以使用多个并发客户端连接
测试环境清理（删除创建的数据、表等，关闭连接）
参数
--create-schema
指定测试的数据库名称，默认是mysqlslap
--engine
创建测试表所使用的存储引擎，可指定多个，用“，”隔开
--concurrency=N
模拟N个客户端并发执行，可以分成多次，用“，”隔开
--number-of-queries=N
总的测试查询次数（并发客户端*每客户查询次数），比如并发是10，总次数是100，那么10个客户端各执行10个
--iterations=N
迭代执行的次数，即重复的次数（相同的测试执行N次，求一个平均值），指的是整个步骤的重复次数，包括准备数据，测试load、清理
--commit=N
执行N条DML后提交一次
自动生成测试表和数据，表示用mysqlslap工具自己生成的sql脚本来测试并发压力
--auto-generate-sql-load-type
测试语句的类型，代表要测试的环境是读操作还是写操作还是两者混合的
取值包括read（scan tables），write（insert into tables），key（read primary keys），update（update primary keys）或者是mixed（half inserts、half scanning selects），默认值是mixed
auto-generate-sql-add-auto-increment
对生成的表自动添加到auto_increment列
--number-char-cols
自动生成的测试表中包含N个字符类型的列，默认1
--number-int-cols
自动生成的测试表中包含N个数字类型的列，默认1
--dubug-info
打印内存和CPU信息
system time
在内核态下花费的时间
user time
在用户态下花费的时间
max resident size
占用的最大物理内存（驻留内存）
Non-physical pagefaults
直接通过回收脏页来解决IO读写而产生的缺页中断的次数
involuntary context switch
线程因为时间片到了或更高优先级的线程抢占导致的切换的次数
voluntary context switch
线程主动让出处理器导致的切换的次数，很可能是等待IO
MySQL架构与存储引擎
逻辑架构
整体逻辑架构
MySQL整体逻辑架构图
分层逻辑结构
MySQL分层逻辑结构图
连接层
过程
1. 当MySQL启动（MySQL服务器就是一个进程），等待客户端连接时，每一个客户端连接请求，服务器都会新建一个线程处理（如果是线程池，则分配一个空闲的线程），每个线程独立，拥有各自的内存空间，但是，如果该请求只是查询，不会引发数据同步问题，但是若是修改数据，当两个线程修改同一块内存时会引发数据同步问题
2. 连接到服务器，服务器会对其进行验证，即用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如是否允许客户端对某个数据库某个表的某个操作）
SQL处理层
主要功能
SQL语句的解析、优化
缓存的查询
MySQL内置函数的实现
跨存储引擎功能（引擎需对外提供接口），例如存储过程、触发器、视图等
1. 如果是查询语句（select语句），则首先查询缓存（缓存sql语句以及sql语句的执行计划）是否已有对应结果，有则返回结果，无则进行下一步（如果不是查询语句同样跳转到下一步）
2. 解析查询，创建一个内部数据结构（解析树），这个解析树要用来对sql语句的语义与语法进行解析
3. 优化sql语句，例如重写查询、决定表的读取顺序、以及选择需要的索引等。这一阶段用户是可以查询的，可以查询服务器优化器是如何优化的，便于用户重构查询和修改相关配置，以达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等
数据缓存
数据缓存默认关闭
查看数据缓存是否开启
show variables like '%query_cache_type%';
查看数据缓存大小
show variables like '%query_cache_size%';
SQL查询解析
SQL查询模版
SELECT DISTINCT < select_list >FROM < left_table > < join_type >JOIN < right_table > ON < join_condition >WHERE < where_condition >GROUP BY < group_by_list >HAVING < having_condition >ORDER BY < order_by_condition >LIMIT < limit_number >
SQL查询解析顺序
MySQL查询解析顺序图
存储引擎
MySQL 存储引擎相关参数
查看MySQL支持的存储引擎
show engines;
查看MySQL当前默认的存储引擎
show variables like '%storage_engine%';
查看创建表的语句
show create table 表名;
查看数据文件目录地址
show variables like '%datadir%';
MyISAM存储引擎
MySQL5.5及以前版本默认的存储引擎
MyISAM 存储引擎采用非聚集索引
MyISAM存储引擎中数据和索引分别存放在MYD（数据文件）和MYI（索引文件）中
MyISAM 存储引擎的特性
并发性与锁级别：表级锁
支持全文检索
支持数据压缩
压缩数据
myisampack -b -f testmyisam.MYI
修复数据
check table testmyisam;repair table testmyisam;
适用场景
非事务型应用（数据仓库、报表、日志数据）
只读类应用
空间类应用（空间函数、坐标）
InnoDB存储引擎
MySQL5.5及以后版本默认的存储引擎
InnoDB 采用“表空间”保存文件
MySQL5.6以前默认为系统表空间
查看是否开启独立表空间
show variables like 'innodb_file_per_table';
系统表空间的优势
系统表空间 all in one 不利于管理
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
独立表空间可以通过optimize table回收存储空间
建议：如果使用InnoDB存储引擎，则推荐使用独立表空间
InnoDB 存储引擎的特性
InnoDB是一种事务型存储引擎
完全支持事务的ACID特性
Redo Log和Undo Log
InnoDB支持行级锁（并发程度更高）
InnoDB存储引擎适合于大多数OLTP（联机事务处理过程）应用
MyISAM存储引擎与InnoDB存储引擎对比
不支持主外键
不支持事务
仅支持表级锁（即使操作一条记录也会锁住整个表），不适合高并发操作
只缓存索引，不缓存真实数据
表空间小
重点关注性能
支持主外键
支持事务
支持行级锁（操作时只锁住某一行，不对其他行有影响），适合高并发操作
不仅缓存索引，还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间大
重点关注事务
其他存储引擎
CSV存储引擎
数据以文本方式存储在文件
数据文件组成
.CSV文件存储内容
.CSM文件存储表的元数据，如表状态和数据量
特点
以csv格式进行数据存储
所有列都不能为null
不支持索引（不适合大表，不适合在线处理）
可以对数据文件直接编辑（保存文本文件内容）
Archive存储引擎
以zlib对表数据进行压缩，磁盘IO更少
数据存储在ARZ为后缀的文件中
只支持insert和select操作
只允许在自增ID列上加索引
Memory存储引擎
也称HEAP存储引擎，数据保存在内存中
支持Hash索引和BTree索引
所有字段都是固定长度，即varchar(10)=char(10)
不支持Blog和Text等大字段
Memory存储引擎使用表级锁
关键参数
设置max_heap_table_size控制内存表大小（字节）
设置tmp_table_size设置内存临时表最大值（字节）
Federated存储引擎
提供了访问远程MySQL服务器上表的方法
本地不存储数据，数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息
使用场景
偶尔的统计分析及手工查询
开启Federated存储引擎
MySQL 数据库默认不开启Federated存储引擎
在配置文件中添加federated=1
MySQL锁
锁的简介
锁的概念
锁是计算机协调多个进程或线程并发访问某一资源的机制
锁保证数据并发访问的一致性、有效性
锁冲突也是影响数据库并发访问性能的一个重要因素
锁是MySQL在服务器层和存储引擎层的的并发控制
MySQL中的锁
MySQL的锁机制
共享锁与排他锁
共享锁（读锁）
其他事务可以读，但不能写
排他锁（写锁）
其他事务不能读取，也不能写
粒度锁
MySQL不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）
BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁
默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令
不同粒度锁的比较
表级锁
开销小，加锁快
不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低
这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁
表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
行级锁
开销大，加锁慢
会出现死锁
锁定粒度小，发生锁冲突的概率最低，并发度也最高
最大程度的支持并发，同时也带来了最大的锁开销
在InnoDB存储引擎中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB存储引擎中发生死锁是可能的
行级锁只在存储引擎层实现，而MySQL服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
页面锁
开销和加锁时间介于表级锁和行级锁之间
锁定粒度介于表级锁和行级锁之间，并发度一般
MyISAM存储引擎中的锁
模式
表共享读锁（Table Read Lock）
不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
表独占写锁（Table Write Lock）
会阻塞其他用户对同一表的读和写操作
MyISAM存储引擎中表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止
默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求
改变读锁和写锁的优先级
通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级
给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会
共享读锁
加锁和释放锁
显示加锁
lock table 表名 read;
lock table 表名 as 别名 read;
显示释放锁
unlock tables;
注意：读锁不支持别名
总结
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表对写请求
对MyISAM表的读操作，不会阻塞当前Session对锁定表的读操作，但对锁定表进行修改会报错
一个Session使用lock table命令给表加了读锁，这个Session可以查询锁定表中的记录，但更新或访问其他表都会提示错误
另外一个表中可以查询表中的记录，但更新就会出现锁等待
独占写锁
给表加锁（语法）
lock table 表名 write;
lock table 表名 as 别名 write;
释放锁（语法）
注意：写锁不支持别名
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作
对MyISAM表的写操作，当前Session可以对锁定表做CRUD，但对其他表进行操作会报错
InnoDB存储引擎中的锁
InnoDB行级锁
在MySQL的InnoDB存储引擎支持行级锁
共享锁（S）
允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
给行加锁（语法）
select * from 表名 where 条件 lock in share mode;
排他锁（X）
允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
select * from 表名 where 条件 for update;
InnoDB表级锁
意向共享锁（IS）
事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁
意向排他锁（IX）
事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁
行级锁和表级锁的兼容情况
InnoDB存储引擎中锁模式的兼容情况
如果一个事务请求的锁模式与当前的锁兼容， InnoDB就将请求的锁授予该事务； 反之， 如果两者不兼容，该事务就要等待锁释放
行级锁升级为表级锁
索引失效时，行级锁会升级为表级锁
物理结构修改
面试题
系统运行一段时间，数据量已经很大，这时候系统升级，有张表需要增加个字段，并发量白天晚上都很大，请问如何修改表结构
面试考点：修改表结构会导致表级锁，数据量大，修改数据很长，导致大量用户阻塞，无法访问
答案
1. 首先创建一个和要执行alter操作的表一样的空的表结构
2. 执行赋予表结构的修改，然后copy原表中的数据到新表里面
3. 在原表上创建一个触发器，在原表数据copy的过程中，将原表更新数据的操作全部更新到新表中来
4. copy完成之后，用rename table新表代替原表，默认删除原表
结构修改工具
pt-online-schema-change
安装
1. 下载安装perl环境
2. 下载percona-toolkit工具集合
3. ppm install DBI依赖
4. ppm install DBD::mysql 安装MySQL驱动依赖
使用
MySQL事务
事务的简介
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行
事务用来管理insert、update、delete语句
事务的特性
原子性（Atomicity）
一个事务是一个不可分割的工作单位，事务中包括的诸操作，要么都做，要么都不做
一致性（Consistency）
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性是与原子性密切相关的
隔离性（Isolation）
一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性（Durability）
持久性也称永久性（Permanence），指一个事务 一旦提交，它对数据库中数据的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响
隔离性级别
事务的隔离性级别
读未提交（Read Uncommitted）
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
读已提交（Read Committed）
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
可重复读（Repeatable Read）
set SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
MySQL默认的事务隔离性级别
可串行化（Serializable）
set SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
写数据时会锁住整个表
查看MySQL事务的隔离性级别
show variables like '%tx_isolation%';
事务并发问题
脏读
指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据
不可重复读
指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的
幻读
例如系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B在这时插入了一条具体分数的记录，系统管理员A修改结束后发现还有一条记录没有修改过来，就好像发生了幻觉一样，就叫做幻读
关联
会产生脏读
会产生不可重复读
会产生幻读
不会产生脏读
不会产生不可重复读
不会产生幻读
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于大多数应用程序，可以优先考虑把数据库系统的事务隔离级别设为读已提交，它能够避免脏读，而且有较好的并发性能
事务语法
开启事务
begin
start transaction（推荐）
begin work
事务回滚
rollback
事务提交
commit
还原点
savepoint
业务设计
逻辑设计
范式设计
第一范式
1. 数据库所有字段都只有单一属性
2. 单一属性是由基本数据类型构成的
3. 数据库的表都是二维的（行与列）
第二范式
1. 要求符合第一范式
2. 表必须有一个主键（一列或多列）
3. 其他字段可由主键确定
4. 二范式目的是通过拆表减少数据冗余
第三范式
1. 要求符合第二范式
2. 字段要求直接依赖于主键，不允许间接依赖
3. 第三范式目的在于拆分实体（完善主从表）
反范式设计
反范式设计的优缺点
1. 单表查询易于优化，易于管理
2. SQL语句简单，有利于程序开发，团队协作
3. 存在数据冗余，写操作时需要额外更新从表数据
4. 不合理的反范式设计会让表变得臃肿不堪
实体关系设计
实体关系分析
1. 实体关系分析是指系统事物之间的联系
2. 实体关系需要双向分析
3. 实体关系决定表关系
实体关系的种类
1. 一对一
2. 一对多
3. 多对多
表设计原则
1. 一对一，通过主键关联
2. 一对多，在多的一方设置外键
3. 多对多，增加中间表，持有双方外键
物理设计
定义数据库、表及字段的命名规范
数据库、表、字段的命名要遵守可读性原则
使用大写和小写来格式化的库对象名字以获得良好的可读性
数据库、表、字段的命名要遵守表意性原则
对象的名字应该能描述它所标识的对象
数据库、表、字段的命名要遵守长名原则
尽可能少使用或者不使用缩写
选择合适的存储引擎
为表中的字段选择合适的数据类型
优先级考虑
1. 优先考虑数字类型
2. 其次考虑日期、时间类型
3. 最后考虑字符类型
4. 对于相同级别的数据类型，应该优先选择占用空间小的数据类型
整数类型
tinyint
存储空间：1字节
signed：-128~127
unsigned：0-255
smallint
存储空间：2字节
signed：-32768-32767
unsigned：0-65535
mediumint
存储空间：3字节
signed：-8388608-8388607
unsigned：0-16777215
int
存储空间：4字节
signed：-2147483648-2147483647
unsigned：0-4294967295
bigint
存储空间：8字节
signed：-9223372036854775808-9223372036854775807
unsigned：0-18446744073709551615
实数类型
Float
是否精确类型：否
Double
Decimal
存储空间：每4个字节存9个数字，小数点占一个字节
是否精确类型：是
VARCHAR类型
VARCHAR变体长度，根据实际内容保存数据
1. 使用最小的符合需求的长度
2. VARCHAR(255)以下使用额外一个字节保存长度
3. VARCHAR(255)以上使用额外两个字节保存长度
4. VARCHAR(5)与VARCHAR(200)内存占用不同
5. VARCHAR变更长度时会出现锁表
VARCHAR的使用场景
1. VARCHAR适合存储长度波动大的数据，（如博客文章）
2. 字符串很少被更新的场景
3. VARCHAR适合保存多字节字符
CHAR类型
CHAR属于定长数据
1. CHAR最大长度255
2. CHAR类型会自动删除末尾的空格
3. CHAR检索效率比VARCHAR高
CHAR的使用场景
1. CHAR适合存储长度波动不大的数据，如MD5摘要
2. CHAR适合存储短字符串
3. CHAR类型适合存储经常更新的字符串
DATETIME日期类型
DATETIME日期时间类型，占用8个字节（MySQL5.5及以前版本，以后版本占用5个字节）
与时区无关
可保存到毫秒
可保存的时间范围大
不要使用字符串存储日期类型
TIMESTAMP时间戳
TIMESTAMP时间戳，占用4字节
时间范围：1970-01-01到2038-01-19
TIMESTAMP精确到秒
采用整型存储
依赖于时区
自动更新timestamp列的值
建立数据库结构
慢查询
慢查询配置
慢查询日志
定义：查询慢的日志，是指MySQL记录所有执行超过long_query_time参设设定的时间阈值的sql语句的日志
该日志能为MySQL语句的优化带来很好的帮助
默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志
慢查询相关参数
slow_query_log
启用或停止慢查询日志
slow_query_log_file
指定慢查询日志的存储路径及文件（默认与数据文件放在一起）
long_query_time
指定记录慢查询日志sql执行时间的阈值（单位：秒，默认：10秒）
log_queries_not_using_indexes
是否记录未使用索引的sql
log_output
日志存放的地方（可选FILE、TABLE，默认是FILE）
慢查询日志内容
User@Host
用户名@用户的IP信息
Id
线程ID号
Query_time
执行sql花费的时间（单位：毫秒）
Lock_time
执行sql获得锁的时间
Rows_sent
获得的结果行数
Rows_examined
扫描的数据行数
SET timestamp
sql执行的具体时间
具体的sql语句
慢查询分析
MySQL的慢查询分析工具
mysqldumpslow
汇总除查询条件外其他完全相同的sql，并将分析结果按照参数中所指定的顺序输出
语法
mysqldumpslow -s r -t 10 slow-mysql.log
-s
排序方式，可选参数有c（访问计数）、t（总时间）、l（锁定时间）、r（返回记录）、al（平均锁定时间）、ar（平均访问记录数）、at（平均查询时间）
-t
是top n的意思，返回多少条数据
-g
可以跟上正则匹配模式，对大小写不敏感
Percona的慢查询分析工具
pt_query_digest
--create-review-table
当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
--create-history-table
当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
--filter
对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit
限制输出结果百分比或数量，默认值是20，即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止
--host
MySQL服务器地址
--user
MySQL用户名
--password
MySQL用户密码
--history
将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中
--output
分析结果输出类型，值可以是report（标准分析报告）、slowlog（MySQL slow log）、json、json-anon，一般使用report，以便于阅读
--since
从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]“格式的时间点，也可以是简单的一个时间值：s（秒）、h（小时）、m（分钟）、d（天），如12h就表示从12小时前开始统计
--until
截止时间，配合—since可以分析一段时间内的慢查询
--explain
输出执行计划
索引与执行计划
索引
定义：索引是帮助MySQL高效获取数据的数据结构
MySQL中的索引
MySQL中默认的存储引擎InnoDB只显示支持B-Tree（从技术上来说是B+Tree）索引
索引分类
从应用层次来分
普通索引
即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引
索引列的值必须唯一，但允许有空值
复合索引
即一个索引包含多个列
根据数据中的物理顺序与键值的逻辑顺序关系来分
聚集索引
数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同，通俗来说，就是数据与索引存储在一起
非聚集索引
数据库表行中数据的物理顺序与键值的逻辑（索引）顺序不同，通俗来说，就是数据与索引分开存储
基础语法
查看索引
show index from 表名;
创建索引
create [UNIQUE] index 索引名 on 表名(字段名列表);
alter table 表名 add [UNIQUE] index 索引名 (字段名列表);
删除索引
drop index 索引名 on 表名;
执行计划
定义：使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句的
分析查询语句或是表结构的性能瓶颈
语法：explain + 查询sql
输出详解
id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况
id相同
执行顺序由上至下
id不同
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同又不同（两种情况同时存在）
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type
查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
查询类型
SIMPLE
简单的select查询，查询中不包含子查询或者union
PRIMARY
查询中包含任何复杂的子部分，最外层查询则被标记为primary
SUBQUERY
在select或where列表中包含了子查询
DERIVED
在from列表中包含的子查询被标记为derived（衍生），MySQL或递归执行这些子查询，把结果放在临时表里
UNION
若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
UNION RESULT
从union表获取结果的select
table
查询涉及的表或衍生表
type
访问类型，sql查询优化中一个很重要的指标
结果值从好到坏依次是
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
索引类型
system
表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
const
表示通过索引一次就找到了，const用于比较主键或者唯一键索引，因为只需匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个const
eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一键索引扫描
ref
非唯一键索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
range
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好，只需要开始于某个点，结束于另一个点，不用扫描全部索引
index
index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（减少一次回表操作）
ALL
遍历全表以找到匹配的行
possible_keys
查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引，如果为NULL，则没有使用索引
查询中如果使用了覆盖索引，则该索引仅出现在key列表中
key_len
表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的
计算公式
变长字段需要额外2字节（varchar值保存时只保存需要的字符数，另加1个字节来记录长度，如果列声明的长度超过255，则使用2个字节，所以varchar索引长度计算时要加2），固定长度字段不需要额外的字节
NULL需要1个字节的额外空间，所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间
符合索引有最左前缀（最左匹配）的特性，如果符合索引能全部使用上，则key_len大小为符合字段的索引长度之和，这也可以用来判定符合索引是否部分使用，还是全部使用
显示索引的那一列被使用了，如果可能，是一个常量const
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra
不适合在其他字段中显示，但是十分重要的额外信息
额外信息
Using filesort
MySQL对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说MySQL无法利用索引完成的排序操作成为“文件排序”
Using temporary
使用临时表保存中间结果，也就是说MySQL在对查询结果排序时使用了临时表，常见于order by和group by
Using index
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现Using where，表明索引被用来执行索引键值的查找
如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作
Using where
使用了where过滤
Using join buffer
使用了链接缓存
Impossible WHERE
where子句的值总是false，不能用来获取任何元祖
select tables optimized away
在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化
distinct
优化distinct操作，在找到第一个匹配的元祖后即停止找同样值的动作
MySQL优化
尽量避免会导致索引失效的情况
索引选择性太差
<> / not in 无法使用索引
is null 会使用索引，is not null 不会使用索引
or条件有未建立索引的列
or尽量替换成union
复合索引情况下不遵循最左匹配原则
对索引列进行计算或使用函数
隐式类型转换
insert语句批量提交优化
使用Java代码
提交前关闭自动提交
尽量使用批量insert语句
可以使用MyISAM存储引擎
使用sql语句
使用load data INFILE into table 表名
0 条评论
回复 删除
下一页
