主要内容
原文地址：https://www.bilibili.com/video/BV1Vh411y7s7?p=8
- mysql优化
- mysql框架
- 存储引擎
- sql优化
- mysql日志支持
- 执行计划
- mysql中的索引
测试代码
####缓存的设置#########################################
-- 查询缓存相关的系统参数
SHOW VARIABLES LIKE "%query_cache%"
-- 设置缓存大小为128M 此处的单位为Byte
SET GLOBAL query_cache_size=134217728;
-- query_cache_type 这个参数无法设置临时，只能在my.ini 中去设置
-- 显示缓存情况，看看缓存有没有生效
SHOW STATUS like "%Qcache%";
-- ** Qcache_queries_in_cache 当前缓存中数量 重点看这个
-- -- Qcache_inserts 插入到缓存中的总数量
-- ** Qcache_hits 缓存命中数，查询缓存被访问的次数 重点看这个
-- -- Qcache_lowmem_prunes 由于缓存太小，从缓存中删除的查询数量
-- -- Qcache_not_cached 没有被缓存的数量
-- -- Qcache_free_memory 剩余的缓存容量
-- 测试
SELECT SQL_CACHE user_id from vote_record_memory WHERE id=1;
SELECT SQL_NO_CACHE user_id from vote_record_memory WHERE id=2;
-- 清空缓存
RESET QUERY CACHE;
####存储引擎的设置#########################################
-- 查看当前mysql支持的存储引擎,并查看当前用的哪一个
show engines;
-- 查看数据库默认使用的存储引擎
show VARIABLES like '%storage_engine%';
-- 查询建表语句
show CREATE TABLE vote_record;
-- 修改表的存储引擎
ALTER TABLE vote_record ENGINE = INNODB;
SELECT * from vote_record WHERE id=1000;
####通用日志设置#########################################
SHOW VARIABLES LIKE '%general%';
-- 通用日志，默认是关闭的，设置为开启
SET GLOBAL general_log=ON;
-- general_log OFF 是否开启了通用日志
-- general_log_file DESKTOP-UH4CF0U.log 保存日志的文件名
-- 设置保存日志的文件名
SET GLOBAL general_log_file='DESKTOP-UH4CF0U.log'
SET GLOBAL general_log_file='SMALLMING.log'
-- 设置日志输出的目的地
show VARIABLES LIKE '%log_output%';
-- 日志的输出类型 此处设置为在文件和表中都输出
-- 输出的目标表为mysql.general_log 中
set GLOBAL log_output='FILE,TABLE';
-- 测试语句
SELECT * from vote_record LIMIT 0,1;
-- 由于日志中的时间是UFC（美国标准时间），所以需要设置系统参数
show VARIABLES LIKE '%log_timestamps%';
-- 设置日志的打印时间为系统时间
SET GLOBAL log_timestamps='SYSTEM';
-- 测试语句
SELECT * from vote_record LIMIT 0,1;
####二进制日志设置#######################################
-- 查看二进制日志的配置情况 默认是没有开启状态
show VARIABLES LIKE '%log_bin%';
-- 开启二进制的参数是只读的，无法通过命令修改，只能在my.ini 中添加或修改
-- 注意，这个参数的值如果是关闭的话就my.ini 中注释掉，要生效的话，它的值代表的是生成文件的名字
set GLOBAL log_bin=ON;
-- 生成二进制文件
FLUSH LOGS;
-- 二进制文件包含两个文件.000001(某一个二进制文件) 和 .index(目录，用于记录哪些是二进制记录文件)
-- 测试命令
UPDATE vote_record SET vote_id=100 WHERE id=1;
-- 方法一：查看二进制文件，但是这种形式并不理想，无法看到真实的数据
show BINLOG EVENTS in 'mylogbin.000002';
-- 方法二：需要在dos命令中，利用mysqlbinlog 工具实现
C:\ProgramData\MySQL\MySQL Server 5.7\Data>mysqlbinlog --base64-output=decode-rows -v mylogbin.000002>D:/aaa.sql
-- --base64-output=decode-rows -v 这段话表示把二进制解密后输出到D:/aaa.sql 中了
####错误日志设置#########################################
-- 查看错误日志参数，错误日志默认是开启的。
show VARIABLES LIKE '%log_error%';
-- 参数说明
-- binlog_error_action 错误处理方式
-- ABORT_SERVER 出现问题终止服务
-- IGNORE_ERROR 忽略错误
-- log_error 日志保存位置
-- log_error_verbosity 记录级别
-- 设置记录级别 1 表示只记录警告（Warning）信息； >1 表示记录所有信息（Warning：警告、Note：在这是错误信息的意思）
SET GLOBAL log_error_verbosity=1;
####慢查询志设置#########################################
-- 查看满查询参数
show VARIABLES like '%quer%';
-- 参数说明
-- slow_query_log 是否开启慢查询，哦认识开启的
-- slow_query_log_file 慢查询sql保存的位置
-- long_query_time 认定为慢查询的阀值，默认为10秒，此值需要在my.ini 文件中设置才有效
-- log_queries_not_using_indexes 是否记录不和用于索引的查询（前提slow_query_log是开启的）
-- 设置记录不适用与索引的查询
SET GLOBAL log_queries_not_using_indexes=ON;
-- 设置保存日志的方式为文件和表中都有，表中数据保存在：mysql.slow_log
SET GLOBAL log_output='FILE,TABLE';
-- 测试代码
SELECT * from vote_record;
####执行计划#########################################
-- 测试代码
EXPLAIN SELECT * FROM (SELECT id,vote_id,user_id FROM vote_record where id<100 LIMIT 0,1) t;
-- 如果EXPLAIN对sql进行了优化，通过下面命令可以查看优化后的结果
SHOW WARNINGS;
####创建索引#########################################
-- 普通索引
-- 给vote_record表的user_id字段新建一个名称为：user_index 的索引
CREATE index user_index ON vote_record(user_id);
-- 测试代码
SELECT * from vote_record WHERE user_id='ceqeLYnNK9nfVMWc3u6U';
-- 查看表中的索引列表
show INDEX FROM vote_record;
-- 删除一个索引
DROP INDEX user_index ON vote_record;
####分区#########################################
-- Range分区
-- 注意：此处的 less 是小于的意思不包含10
create TABLE t1(_id int,name varchar(20),age int)
PARTITION by RANGE(age)
(
PARTITION p01 VALUES less than (10),
PARTITION p02 VALUES less than (20),
PARTITION p03 VALUES less than (30),
PARTITION p04 values less than (MAXVALUE)
);
-- 按照createTime时间的year部分尽心分区 5.5之后的版本才可以使用
CREATE table t3(_id int,_name varchar(20),age int,createTime date)
PARTITION BY RANGE(YEAR(createTime))
(
PARTITION p01_year VALUES less than (1970),
PARTITION p02_year VALUES less than (1980),
PARTITION p03_year VALUES less than (1990),
PARTITION p04_year VALUES less than (MAXVALUE)
);
-- list分区
create TABLE t4(_id int, cid int,_name VARCHAR(20),pos_time datetime)
partition BY list(cid)
(
PARTITION p01 VALUES in(1,2,3),
PARTITION p02 values in(4),
PARTITION p03 values in(5,6,7)
);
-- HASH 分区
CREATE TABLE t5(
_id int NOT NULL AUTO_INCREMENT,,
cid int,
_name VARCHAR(20),
post_time datetime,
PRIMARY KEY(_id,_name),
UNIQUE KEY `uk_username`(_id,_name)
)
PARTITION BY HASH(_name)
PARTITIONS 4;
-- HASH 分区
CREATE TABLE t5(
_id int,
cid int,
_name VARCHAR(20),
post_time datetime,
PRIMARY KEY(_id,_name),
UNIQUE KEY `uk_username` (_id,_name)
)
PARTITION BY HASH(_id)
PARTITIONS 4;
-- KEY 分区
CREATE TABLE t6(
_id int,
cid int,
_name VARCHAR(20),
post_time datetime,
PRIMARY KEY(_id,_name),
UNIQUE KEY `uk_username` (_id,_name)
)
PARTITION BY KEY(_id)
PARTITIONS 2;
-- 子分区
CREATE TABLE tb_sub_dir (id INT, purchased DATE)
ENGINE='MYISAM'
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
(
PARTITION p0 VALUES LESS THAN (1990)
(
SUBPARTITION s0
DATA DIRECTORY = '\\data_sub1'
INDEX DIRECTORY = '\\idx_sub1',
SUBPARTITION s1
DATA DIRECTORY = '\\data_sub1'
INDEX DIRECTORY = '\\idx_sub1'
),
PARTITION p1 VALUES LESS THAN (2000)
(
SUBPARTITION s2
DATA DIRECTORY = '\\data_sub2'
INDEX DIRECTORY = '\\idx_sub2',
SUBPARTITION s3
DATA DIRECTORY = '\\data_sub2'
INDEX DIRECTORY = '\\idx_sub2'
),
PARTITION p2 VALUES LESS THAN MAXVALUE
(
SUBPARTITION s4
DATA DIRECTORY = '\\data_sub3'
INDEX DIRECTORY = '\\idx_sub3',
SUBPARTITION s5
DATA DIRECTORY = '\\data_sub3'
INDEX DIRECTORY = '\\idx_sub3'
)
);
-- 增加分区
-- 如果这个表之前没有分区，需要通过修改分区配置添加分区
ALTER TABLE t6 PARTITION by RANGE(_id)(
PARTITION `pxiaoyu10` VALUES less than (10)
);
-- 如果之前有分区 只能添加统一类型的分区
ALTER TABLE t6 add partition (
partition `pxiaoyu40` values less than (40)
);
-- 移除分区：移除分区仅仅修改表分区定义，数据不会被删除；
ALTER TABLE t6 REMOVE PARTITIONING ;
-- 删除分区：删除分区会删除分区定义同时删除分区上的数据。
ALTER TABLE t6 DROP PARTITIONING ;
-- 可以查看创建分区表的create语句
show create table t6;
-- 可以查看表是不是分区表
show table status ;
-- 查看information_schema.partitions表
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='t6';
-- 通过此语句来显示扫描哪些分区，及他们是如何使用的.
explain partitions select * from t6;
一、mysql优化
1.1 硬件级别优化
a、磁盘寻找：换硬盘，换固态
b、硬盘读写：换硬盘，换固态
c、CPU和缓存：提高cpu的缓存
1.2 数据库级别优化
导致数据库应用最高效的手段是：基本设计
a、表结构是否正确，字段类型，默认值，能not null的就not null，优先使用数值类型。
b、正确设置索引达到查询高效，需要考虑的是什么sql会使索引失效。
c、对于不同情况选择不同的存储引擎。
d、每张表是否具有适当的行格式。
e、使用适当的锁策略，防止出现脏读（锁行），不可重复读（锁行），幻读的发生（锁表），在具有高并发、分布式的应用中，选择适当的锁策略以保证数据的共享性和特定环境下独占数据。锁策略锁太狠会影响性能。
f、所有缓存区使用的大小是否都正确。
2.优化的哲学
注意：优化有风险，入足需谨慎。
结论：保持现状或出现更差的情况都是失败。
3.优化的顺序
优化成本：硬件》系统配置》数据库表结构》SQL及索引
优化效果：硬件《 系统配置《 数据库表结构 《 SQL及索引
4.优化工具
explain 获取查询语句的执行计划
slow-log 记录慢查询语句
5.mysql框架
最常用的mysql链接协议：TCP协议
写一条sql后的执行流程：
a、通过tccp协议链接到mysql服务器
b、查询缓存：如果在查询缓存中有结果，直接返回，如何查询，sql生成时有一个hash算法生成的key看这个key是否在缓存中，缓存中除了存储了sql的hash值外，还存储了 表的缓存，记录的缓存、权限的缓存（索引等）。
c、解析器：如果没有，解析器会解析sql，主要分析关键字。
d、解析数：解析完成后生成解析结果（解析数）
e、预处理器：交给预处理器处理，主要处理：列是否存在，名字和别名是否有歧义，防止sql注入等
f、新解析数：预处理完成成生成新的解析结果
e、优化器：解析结果交给优化器进行sql的优化，生成结果计划
g、执行计划：生成执行计划后，查询当前的表用的什么引擎，通过引擎的api查询数据
6.查询缓存
6.1查询缓存参数
SHOW VARIABLES 表示查看系统参数，与my.ini 中设置的参数是对应的
SHOW VARIABLES LIKE "%query_cache%"
have_query_cache YES 当前的mysql是否支持查询缓存
query_cache_limit 1048576 查询出的记过最多存放条数
query_cache_min_res_unit 4096 最少结果限制
query_cache_size 0 查询缓存的大小
query_cache_type OFF 设置是否开启缓存 0 off，1 on 恒开启（但是sql中包含了：SQL_NO_CACHE可以不进行缓存），2 选择性开启（sql中必须含有：（SQL_CACHE）
query_cache_wlock_invalidate OFF
6.2设置缓存参数
设置缓存参数有两种：
1.通过sql命令设置，这种是临时的，重启mysql后设置会失效
2.在my.ini 中设置，设置成功后需要重新mysql，因为这个文件只在启动是加载依此。
这里我们先通过临时的方式去设置：
这里我们只需要设置2个参数：query_cache_size ； query_cache_type
-- 查询缓存相关的系统参数
SHOW VARIABLES LIKE "%query_cache%"
-- 设置缓存大小为128M 此处的单位为Byte
SET GLOBAL query_cache_size=134217728;
-- 显示缓存情况
SHOW STATUS like "%Qcache%";
-- -- Qcache_queries_in_cache 当前缓存中数量
-- -- Qcache_inserts 插入到缓存中的总数量
-- -- Qcache_hits 缓存命中数
-- -- Qcache_lowmem_prunes 由于缓存太小，从缓存中删除的查询数量
-- -- Qcache_not_cached 没有被缓存的数量
-- 测试
SELECT SQL_CACHE user_id from vote_record_memory WHERE id=1;
SELECT SQL_NO_CACHE user_id from vote_record_memory WHERE id=2;
-- 清空缓存
RESET QUERY CACHE;
注意：query_cache_type这个参数无法设置临时，只能在my.ini 中去设置
SET GLOBAL query_cache_type=1;
返回错误信息如下
[SQL]SET GLOBAL query_cache_type=1;
[Err] 1651 - Query cache is disabled; restart the server with query_cache_type=1 to enable it
6.3查询缓存的应用场景
大量查询，少量修改删除的场景
6.4查询缓存的优缺点
优点：提升查询速度
缺点：影响性能
7.存储引擎
mysql的存储引擎一共8个，我们重点研究2个：INNODB（默认），MYISAM
-- 查看当前mysql支持的存储引擎,并查看当前用的哪一个
show engines;
-- 查看数据库默认使用的存储引擎
show VARIABLES like '%storage_engine%';
-- 查询建表语句
show CREATE TABLE vote_record;
-- 修改表的存储引擎
ALTER TABLE vote_record ENGINE = INNODB;
InnoDB
MRG_MYISAM
MEMORY
BLACKHOLE
MyISAM
CSV
ARCHIVE
PERFORMANCE_SCHEMA
7.1 INNODB
默认最全的引擎，可以实现其他引擎的所有功能，生成的文件在
Data 文件夹下
.frm 结构文件
.idb 索引和数据文件
特点：
1.支持事务，提供了具有，提交、回滚、和崩溃亏负能力的事务安全（ACID兼容，满足一致性，牺牲可用性，与之对应的是BASE原则，牺牲一致性，满足可用性）存储引擎。
2.InnoDB锁定在行级
3.支持外键强制
4.数据多版本读取 ，可以和mysql其他的类型混合使用，甚至在同一个查询中也可以混合使用
5.适合执行大量更新、插入操作，适合高并发和高QPS
InnoDB是处理距大数据量是的最大性能设计，他的CPU效率可能是任何其他基于磁盘的关系型数据引擎所不能匹敌的。
7.2 MYISAM
Data 文件夹下
.frm 结构文件
.MYD 数据文件
.MYI 索引文件
特点：
1.不是事务安全的
2.不支持外键
3.只支持表级锁
4.适合执行大量查询操作
7.3 其他
可能会用的：1.memory ，2.NDBCluster(5.0之后主要用于mysql集群)，CSV（导出数据报表）
memory
所有数据都保存在内存中
Memory 表的结构在重启以后还会保留，但数据会丢失
只有.frm 文件，数存在内存中
archive
针对高写入压缩做了优化的简单引擎
适合日志和数据采集应用
blackhole
没有实现任何存储机制，它会舍弃所有写入数据，但是服务器会记录
Blackhole 表的日志。（不推荐）
federated
访问其他 MySQL 服务器的一个代理，默认禁用。（开发出来属于商业竞争行为）
merge （已被放弃）
7.4Mysql 中 MyISAM 和 InnoDB 的区别有哪些？
简单介绍区别
MyISAM是非事务安全的，而InnoDB是事务安全的
MyISAM锁的粒度是表级的，而InnoDB支持行级锁
MyISAM支持全文类型索引，而InnoDB不支持全文索引（5.7以上支持）
MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM5、MyISAM表保存成文件形式，跨平台使用更加方便
数据结构
两种引擎所使用的索引的数据结构都是B+树!
关于索引详情：MyISAM 和 InnoDB 索引的区别
MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。（但是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。）
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。
簇集索引（InnoDB）：索引的叶子节点存的是整个单条记录的所有字段值。
非簇集索引（MyISAM）：索引的叶子节点存的是被索引字段的值。
8.sql优化
8.1 sql优化的简介
对于特定的要求，使用更优的策略和索引策略，已达到让结果呈现的时间更短的目的。
我们常见的优化指的是：sql语句的优化和索引优化。
sql优化是伴随着业务而进行优化的，并不是下面的所有操作就必须都达到才是好的优化。
8.2 常规的调优思路（众多的解决方案之一）
- 查看 slow-log，分析show-log，分析出查询慢的语句。
- 按照一定优先级，进行一个一个的排查所有慢语句。
- 分析sql ，进行explain（分析它为什么慢）调试，查看语句执行时间。
- 调整索引或语句本身。
8.2.1 mysql5.7 中的日志分类
- 查询通用日志记录（啥都记录）
- 二进制日志（实现主从备份，保存的是修改，删除和新增操作）
- 错误日志（回滚等系统级的日志）
- 慢查询日志（记录出查询速度慢的日志）
查询通用日志记录
如何设置
####通用日志设置#########################################
SHOW VARIABLES LIKE '%general%';
-- 通用日志，默认是关闭的，设置为开启
SET GLOBAL general_log=ON;
-- general_log OFF 是否开启了通用日志
-- general_log_file DESKTOP-UH4CF0U.log 保存日志的文件名
-- 设置保存日志的文件名
SET GLOBAL general_log_file='DESKTOP-UH4CF0U.log'
SET GLOBAL general_log_file='SMALLMING.log'
-- 设置日志输出的目的地
show VARIABLES LIKE '%log_output%';
-- 日志的输出类型 此处设置为在文件和表中都输出
-- 输出的目标表为mysql.general_log 中
set GLOBAL log_output='FILE,TABLE';
-- 测试语句
SELECT * from vote_record LIMIT 0,1;
-- 由于日志中的时间是UFC（美国标准时间），所以需要设置系统参数
show VARIABLES LIKE '%log_timestamps%';
-- 设置日志的打印时间为系统时间
SET GLOBAL log_timestamps='SYSTEM';
-- 测试语句
SELECT * from vote_record LIMIT 0,1;
输出的日志
- table mysql.general_log
查看BLOB数据
2.文件中：C:\ProgramData\MySQL\MySQL Server 5.7\Data
二进制查询日志记录
主要用于：实现主从备份，保存的是修改，删除和新增操作
只有更新操作才会记录日志，查询操作不记录日志
####二进制日志设置#######################################
-- 查看二进制日志的配置情况 默认是没有开启状态
show VARIABLES LIKE '%log_bin%';
-- 开启二进制的参数是只读的，无法通过命令修改，只能在my.ini 中添加或修改
-- 注意，这个参数的值如果是关闭的话就my.ini 中注释掉，要生效的话，它的值代表的是生成文件的名字
set GLOBAL log_bin=ON;
-- 生成二进制文件
FLUSH LOGS;
-- 二进制文件包含两个文件.000001(某一个二进制文件) 和 .index(目录，用于记录哪些是二进制记录文件)
-- 测试命令
UPDATE vote_record SET vote_id=100 WHERE id=1;
-- 方法一：查看二进制文件，但是这种形式并不理想，无法看到真实的数据
show BINLOG EVENTS in 'mylogbin.000002';
-- 方法二：需要在dos命令中，利用mysqlbinlog 工具实现
C:\ProgramData\MySQL\MySQL Server 5.7\Data>mysqlbinlog --base64-output=decode-rows -v mylogbin.000002>D:/aaa.sql
-- --base64-output=decode-rows -v 这段话表示把二进制解密后输出到D:/aaa.sql 中了
如何设置
在my.ini 中添加 log-bin=mylogbin.log
设置完成后，查看二进制日志的配置情况 默认是没有开启状态
执行命令：show VARIABLES LIKE '%log_bin%';
设置完成后在data文件价中会升华成那个两个文件
.000001(某一个二进制文件)
.index(目录，用于记录哪些是二进制记录文件)
多生成一个二进制文件
在navicat中输入命令：FLUSH LOGS;
如何查看二进制文件
由于生成后的文件是二进制文件，我们需要通过mysql的mysqlbinlog 工具把二进制文件解密并存储到另一个文件汇总进行查看
mysql的命令和工具的区别
命令：select * 这种叫命令
工具：mysql - hroot 这种mysql.exe的可执行文件叫工具
C:\ProgramData\MySQL\MySQL Server 5.7\Data>mysqlbinlog --base64-output=decode-rows -v mylogbin.000002>D:/aaa.sql
生成后的文件
错误日志记录
mysql 错误日志记录mysql运行过程中较为严重的警告和错误信息，以及mysql 启动和关闭的详细信息
如何设置
####错误日志设置#########################################
-- 查看错误日志参数，错误日志默认是开启的。
show VARIABLES LIKE '%log_error%';
-- binlog_error_action 错误处理方式
-- ABORT_SERVER 出现问题终止服务
-- IGNORE_ERROR 忽略错误
-- log_error 日志保存位置
-- log_error_verbosity 记录级别
-- 设置记录级别 1 表示只记录警告（Warning）信息； >1 表示记录所有信息（Warning：警告、Note：在这是错误信息的意思）
SET GLOBAL log_error_verbosity=1;
生成后的文件在data下.error 文件
慢查询日志记录
1.记录所有执行时间超过 long_query_time 秒的所有查询或不服用与索引的查询。
2.long_query_time 默认为10秒，即超过10秒的查询都认识慢查询。
如何设置
查看mysql.slow_log 表
Time：执行查询的日期时间
User@Host：执行查询的用户和客户端IP
Id：是执行查询的线程Id
Query_time：SQL执行所消耗的时间
Lock_time：执行查询对记录锁定的时间
Rows_sent：查询返回的行数
Rows_examined：为了返回查询的数据所读取的行数---------可以借助分析慢查询是否跟磁盘io有关
8.2.1 explain工具分析满查询语句
通过explain去模拟执行计划
各个参数的意义
概况
id:选择标识符，这是SELECT的查询序列号，ID越大优先级越高，id相同表明是统一级别的查询，按顺序执行。
select_type:表示查询中每个select子句的类型，主要用于区分普通查询、联合查询、子查询等。
table:输出结果集的表
partitions:匹配的分区
★type:表示表的连接类型，最低级别需要优化到range或者ref
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
详细
-- 测试代码
EXPLAIN SELECT * FROM (SELECT id,vote_id,user_id FROM vote_record where id<100 LIMIT 0,1) t;
**1.id **
选择标识符，这是SELECT的查询序列号，ID越大优先级越高，id相同表明是统一级别的查询，按顺序执行
1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
2.select_type
表示查询中每个select子句的类型，主要用于区分普通查询、联合查询、子查询等
(1) SIMPLE(简单SELECT，不使用UNION或子查询的都是简单查询)
(2) PRIMARY(子查询中最外层查询，union查询中的第一个查询）
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM后面的select查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
3.table:输出结果集的表
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
4.partitions:匹配的分区
匹配的分区
5.type:表示表的连接类型 ----重点掌握
type 为sql优化汇总非常重要的置标，丛好到坏排列，一般至少是range级别，最好能达到ref级别。
System>const>eq_ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All
5.1 system
system是const的特例，「表示表中只有一行记录」，这个几乎不会出现，也作为了解。
EXPLAIN SELECT * FROM (SELECT id,vote_id,user_id FROM vote_record where id<100 LIMIT 0,1) t;
5.2 const
const表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。例子：
explain select * from user where id =2;
5.3 eq_ref
eq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中只有一条记录与之匹配
explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id
5.4 ref
ref性能比eq_ref差，也表示表的链接匹配条件，也就是使用哪些表字段作为查询索引列上的值，ref与eq_ref的区别就是eq_ref使用的是唯一索引或者主键索引。
ref扫描后的结果可能会找到多条符合条件的行数据，本质上是一种索引访问，返回匹配的行
explain select * from user where name = '张三';
5.5 range
「range使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range」
explain select * from user where id > 2;
5.6 index
index表示会遍历索引树，index回避ALL速度快一些，但是出现index说明需要检查自己的索引是否使用正确
explain select id from user;
5.7 All
「ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询：
explain select * from user;
6.possible_keys:表示查询时，可能使用的索引
6.7.8,9 一起看，主要查看key值
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
7.key:表示实际使用的索引
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
8.key_len:索引字段的长度
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好
9.ref:列与索引的比较
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
10.rows:扫描出的行数(估算的行数)
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
11.iltered:按表条件过滤的行百分比
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
12.Extra:执行情况的描述和说明
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
-- 测试Extra的filesort
explain select * from emp order by name;
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
8.3 索引优化
8.3.1 索引的分类
- 普通索引：最基本的索引，它没有任何限制
- 唯一索引：索引列的值必须唯一，且不能为空，如果是组合索引，则列值的组合必须唯一。
- 主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
- 联合索引：在多个字段上建立索引，能够加速查询到速度
####创建索引#########################################
-- 普通索引
-- 给vote_record表的user_id字段新建一个名称为：user_index 的索引
CREATE index user_index ON vote_record(user_id);
-- 测试代码
SELECT * from vote_record WHERE user_id='ceqeLYnNK9nfVMWc3u6U';
-- 查看表中的索引列表
show INDEX FROM vote_record;
-- 删除一个索引
DROP INDEX user_index ON vote_record;
8.3.2 索引的优点
- 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
- 可以加快数据的检索速度
- 可以加速表与表之间的连接
- 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间
8.3.3 索引的缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 索引需要占用物理空间，数据量越大，占用空间越大
- 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护
8.3.4 怎样的列适合创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引（where后面的字段）
- 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）
- 查询中统计或者分组的字段；
8.3.5 怎样的列不适合创建索引
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
- where条件里用不到的字段，不创建索引；
- 表记录太少，不需要创建索引；
- 经常增删改的表；
- 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。
- 列的类型太大或太小的列，对于那些定义为text，image和bit数据类型的列不太适合创建索引。
8.3.6 索引失效的情况
- 对于联合索引，
没有遵循左前缀原则 - 索引的
字段区分度不大，可能引起索引近乎全表扫描 - 对于join操作，索引字段的
编码不一致，导致使用索引失效 - 对于
hash索引，范围查询失效，hash索引只适合精确匹配 - 有索引，但操作
索引项字段“·不干净”加了函数或者各种骚操作，对于sql执行耗时问题 最好使用explain和 profilings 查看执行计划详细信息 - 对于innodb 或myisam，is null 走索引的情况 不走索引的情况？
is not null对于索引字段设计不管允许为空或不许为null都不走索引
is null对于索引字段设计为允许为null时 走索引；如果字段设计不可null 则不走索引
8.3.7 常见的索引优化策略
1、前导模糊查询不能使用索引，
如name like ‘%静’
2、Union、in、or可以命中索引，建议使用in
3、负条件查询不能使用索引，可以优化为in查询，
其中负条件有！=、<>、not in、not exists、not like等
4、联合索引最左前缀原则，又叫最左侧查询，
如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。
5、建立联合查询时，区分度最高的字段在最左边
6、如果建立了（a，b）联合索引，就不必再单独建立a索引。
同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引
7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置
8、范围列可以用到索引，但是范围列后面的列无法用到索引。
索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、<=、>、>=、between等。
9、把计算放到业务层而不是数据库层。
在字段上计算不能命中索引，
10、强制类型转换会全表扫描，
如果phone字段是varcher类型，则下面的SQL不能命中索引。Select * fromuser where phone=13800001234
11、更新十分频繁、数据区分度不高的字段上不宜建立索引。
更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（*）来计算。
12、利用覆盖索引来进行查询操作，避免回表。
被查询的列，数据能从索引中取得，而不是通过定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。
13、建立索引的列不能为null，使用not null约束及默认值
14、利用延迟关联或者子查询优化超多分页场景，
MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页进行SQL改写。
15、业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
16、超过三个表最好不要用join，
需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。
17、如果明确知道查询结果只要一条，limit 1能够提高效率，比如验证登录的时候。
18、Select语句务必指明字段名称
19、如果排序字段没有用到索引，就尽量少排序
20、尽量用union all 代替 union。
Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟，当然，使用union all的前提条件是两个结果集没有重复数据。
21、使用合理的分页提高效率。
select id,name from product limit 866613, 20
8.4 mysql 分区优化
8.4.1 mysql分区的好处
- 与单个磁盘或文件系统分区相比，可以存储更多的数据。
- 通过删除与增加哪些数据有关的分区，很容易地删除或增加哪些数据。
- 一些查询可以得到极大的优化。
- 通过跨多个磁盘甚至服务器来分散数据查询，来获得更大的查询吞吐量。
- mysql5.5 之后支持所有函数的分区优化。
8.4.1 分区的分类
分区分为：水平分区模式（按照行） 和 垂直分区模式（按照列分区）
我们经常使用的是按照行的分区。
8.4.1 水平分区模式（按照行）
- ange（范围） –
这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。 - List（预定义列表） –
这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 - Hash（哈希） – 这种模式允许DBA通过对表的一个或多个列的Hash
Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。 - key（键值） – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
- Composite（复合模式） -
很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。
分区的使用
范围 ：PARTITIONED BY RANGE COLUMNS
列表 ：PARTITION BY LIST COLUMNS
HASH：PARTITION BY HASH
KEY ：PARTITION BY KEY
子分区：SUBPARTITION BY XXX
分区的优势
1、冷热分离：表非常大且只在表的最后部分有热点数据，冷数据根据分区规则自动归档。
2、定期淘汰历史数据：按时间写入，历史数据可淘汰，可快速删除，空间可快速回收。
3、优化查询：在where字句中包含分区列时，分区可以大大提高查询效率，减少缓存开销、减少IO开销。
4、统计性能提升：在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。
RANGE partitioning
create TABLE t1(_id int,name varchar(20),age int)
PARTITION by RANGE(age)
(
PARTITION p01 VALUES less than (10),
PARTITION p02 VALUES less than (20),
PARTITION p03 VALUES less than (30),
PARTITION p04 values less than (MAXVALUE)
);
CREATE table t3(_id int,_name varchar(20),age int,createTime date)
PARTITION BY RANGE(YEAR(createTime))
(
PARTITION p01_year VALUES less than (1970),
PARTITION p02_year VALUES less than (1980),
PARTITION p03_year VALUES less than (1990),
PARTITION p04_year VALUES less than (MAXVALUE)
);
这种是最常见的，也是我们MDB平台提供自动按天见分区的格式。一般也比较适合按天分区，或者固定范围的分区，比如时间范围，只能按照数字大小（年龄／编号）进行区间划分。
优势：
1、按分区快速淘汰历史数据
2、按分区字段的范围查询
LIST partitioning
create TABLE t4(_id int, cid int,_name VARCHAR(20),pos_time datetime)
partition BY list(cid)
(
PARTITION p01 VALUES in(1,2,3),
PARTITION p02 values in(4),
PARTITION p03 values in(5,6,7)
);
表面上看，咦？好像使用list分区的都可以使用rang分区实现呢，其实大部分场景两种分区方式都是可以实现的，线上实际只能使用list分区的场景也比较少。
连续数据更趋向于使用range分区， list分区一般比较适合离散数据的分区，同时可以将多个离散的属性归类存储，比如我需要把20170801、20170803、20170809三个时间的数据放一个分区，20170802、20170805、20170808放个分区，这种就适合使用list分区，针对自己业务特性进行离散的分区，可以非常灵活的将数据打散到不同的分区。可以看出这种分区策略就不适合where条件的范围查询，适合固定值的in条件查询。
优势：
1、灵活的离散数据分区，可自定义分区list规则。
2、 离散分区不适合where条件date>20170801 and date >20170809，适合固定分区的等值查询或in条件查询
HASH partitioning
CREATE TABLE t5(
_id int,
cid int,
_name VARCHAR(20),
post_time datetime,
PRIMARY KEY(_id,_name),
UNIQUE KEY `uk_username` (_id,_name)
)
PARTITION BY HASH(_id)
PARTITIONS 4;
注意：mysql分区的字段必须是要包含在主键字段之内的。不然是会报错的。
hash分区很好理解，就是对指定列做hash，均匀的存到指定的分区，比如按用户名hash分区，那么按用户名进行查找的速度就会快很多，这种针对分区列数据不固定，想把数据根据分区列离散的存储到固定分区数的表中，不需要做数据淘汰的场景比较适合。
优势：
1、维护简单，分区数固定，根据hash自动分区。
2、适合固定条件的等值查询。
3、对于分区列数据不固定，分区列值不固定（不适合list），可根据hash值均匀打散数据到不同分区。
KEY partitioning
CREATE TABLE t6(
_id int,
cid int,
_name VARCHAR(20),
post_time datetime,
PRIMARY KEY(_id,_name),
UNIQUE KEY `uk_username` (_id,_name)
)
PARTITION BY KEY(_id)
PARTITIONS 2;
同样，使用key分区跟hash分区有着神奇的相似，不同的是，如果表有主键或者唯一键的时候无需指定key的列名，key分区自动根据键值进行分区。
优势：
对于有主键的表，可无需关心分区列，MySQL自行根据主键／唯一键分区。如果主键设置不合理，查询条件都不带主键，查询性能会很差。
SUBPARTITION partitioning 子分区
CREATE TABLE tb_sub_dir (id INT, purchased DATE)
ENGINE='MYISAM'
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
(
PARTITION p0 VALUES LESS THAN (1990)
(
SUBPARTITION s0
DATA DIRECTORY = '\\data_sub1'
INDEX DIRECTORY = '\\idx_sub1',
SUBPARTITION s1
DATA DIRECTORY = '\\data_sub1'
INDEX DIRECTORY = '\\idx_sub1'
),
PARTITION p1 VALUES LESS THAN (2000)
(
SUBPARTITION s2
DATA DIRECTORY = '\\data_sub2'
INDEX DIRECTORY = '\\idx_sub2',
SUBPARTITION s3
DATA DIRECTORY = '\\data_sub2'
INDEX DIRECTORY = '\\idx_sub2'
),
PARTITION p2 VALUES LESS THAN MAXVALUE
(
SUBPARTITION s4
DATA DIRECTORY = '\\data_sub3'
INDEX DIRECTORY = '\\idx_sub3',
SUBPARTITION s5
DATA DIRECTORY = '\\data_sub3'
INDEX DIRECTORY = '\\idx_sub3'
)
);
注意：子分区在windos64 位系统下设置文件路径是不起作用，有人说在32位细起作用。
优势：
子分区可以把文件存放在不同的硬盘上，这样可以加快存取速度
增加删除查看分区
-- 增加分区
-- 如果这个表之前没有分区，需要通过修改分区配置添加分区
ALTER TABLE t6 PARTITION by RANGE(_id)(
PARTITION `pxiaoyu10` VALUES less than (10)
);
-- 如果之前有分区 只能添加统一类型的分区
ALTER TABLE t6 add partition (
partition `pxiaoyu40` values less than (40)
);
-- 移除分区：移除分区仅仅修改表分区定义，数据不会被删除；
ALTER TABLE t6 REMOVE PARTITIONING ;
-- 删除分区：删除分区会删除分区定义同时删除分区上的数据。
ALTER TABLE t6 DROP PARTITIONING ;
-- 可以查看创建分区表的create语句
show create table t6;
-- 可以查看表是不是分区表
show table status ;
-- 查看information_schema.partitions表
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='t6';
-- 通过此语句来显示扫描哪些分区，及他们是如何使用的.
explain partitions select语句
扫一扫
615
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
