MySQL调优
查询性能优化
Explain:用来分析SELECT查询语句,查看sql
语句的执行过程。
- id:SELECT查询的序列号
- table:正在访问的表名
- type:访问的类型。ALL全表扫描、Index全索引扫描
- key:使用的索引,NULL表示没有索引
- rows:扫描的物理行数
- Extra:额外的信息。using index、using filesort手工排序,没有利用索引。
查询优化
-
重构查询
-
切分大查询
- 一个大查询如果一次性执行的话,可能会锁住很多的数据,耗费系统资源。阻塞很多很小但很重要的查询
-
分解关联查询
- 分解成多个单表查询
- 减少锁竞争
- 让缓存更高效
-
-
优化数据访问
-
减少请求的数据量
- 返回必要的列。最好不适用SELECT(*)
- 返回必要的行。建议使用LIMIT
- 缓存重复查询的数据。
-
减少服务器端的扫描行数
- 最有效的方式是建索引
- 使用索引覆盖扫描来返回记录
-
大表优化
-
查询数据限定范围
- 比如查询购物订单,可以先查询近一周的信息
-
读写分离
- 主库负责写,从库负责读
-
重写sql语句
-
重构索引
-
垂直拆分
-
水平拆分
一条sql语句的执行过程
-
客户端发送一条sql语句给服务器
- 客户端和服务器端之间的通信协议是“半双工”的,只有一端完全接收整个消息才能响应
-
服务器先查询缓存,如果命中则返回
- 如果命中查询缓存,那么返回结果之前MySQL会做一次“用户权限检测”,如果权限没有问题,那么就会返回数据
-
服务器进行sql语句的解析、预处理,再由优化器生成执行计划
-
语法解析器:根据关键字将sql语句进行解析,生成一颗“解析树”。MySQL会根据语法规则验证和解析查询。
- 是否使用错误的关键字
- 关键字的书写顺序
- 括号前后是否匹配
-
预处理器:根据MySQL的规则检查解析树是否合法
- 检查数据表和列是否存在
- 解析名字和别名
-
-
根据优化器生成相应的执行计划,调用存储引擎的API来执行查询
-
查询优化器:一条语句有很多不同的执行计划,都会返回相同结果。优化器的作用是找到最优的执行计划。
- 重新定义关联表的顺序
- 将外连接转化为内连接
- 使用等价变换规则
- 优化count()、min()和max()
- 覆盖索引扫描
- 子查询优化
- 选择合适的索引
-
查询执行引擎
- 调用存储引擎的API接口
-
-
返回结果给客户端
- 如果查询结果可以被缓存,那么把数据放到查询缓存中
索引失效与优化
索引结构选择
-
索引是帮助MySQL高效获取数据的数据结构。索引的目的在于提高查询效率。索引存储在系统文件中。局部性原理(页)
-
HASH索引
- 哈希索引可以在O(1)时间查找,但失去了有序性
- 哈希索引不是按哈希值的顺序存储的,无法用于排序和分组
- 只支持精确查找,无法用于部分查找和范围查找
- hash冲突会便利对应的链表,而链表的维护成本较高
-
自适应哈希索引
- 适用InnoDB存储引擎。当某个索引值被使用的非常频繁时,会在B+Tree索引上再创建一个哈希索引。B+Tree就有了哈希索引的一些优点。
- 原理:使用一个字段的hash值作为索引,然后再使用B+TREE来进行查询。
-
红黑树和二叉树
- 不论是红黑树还是二叉树,都会因为树的深度过深,而造成IO次数增加。影响读取效率。
-
B树
- 所有的键值都分布在整棵树中
- 搜索可能在非叶子节点结束
- 每个节点最多有m棵子树(m表示阶)
- 所有叶子节点在同一层,每个节点最多有m-1个Key
- 根节点最少有2棵子树
- 缺点:每个节点都有key,同时也包含data,而每个页的存储空间是有限的,如果data比较大的话那么会导致每个结点的key数量减少,导致树加深,IO次数增加。进而影响查询性能。
-
B+树
- 非叶子节点存储key,叶子节点存储key和数据
- 叶子节点两两指针互相连接,构成链式循环结构,顺序查询性能更高
- 每个节点最多有m棵子树(m表示阶)
- 所有叶子节点在同一层,每个节点最多有m-1个Key
- 根节点最少有2棵子树
- 优点:B+TREE的有序性,所以可以用于排序和分组。
- InnoDB的B+TREE索引有主键索引和辅助索引
索引分类
-
聚簇索引
-
不同的存储引擎在B+TREE中存放的数据是不一样的
-
InnoDB通过B+TREE结构对主键创建索引,叶子结点存储数据。
- 如果没有主键、唯一键,那么会生成一个6位的row_id。
-
回表:遍历两次B+TREE
- 如果创建索引的字段是普通字段,先在以普通字段创建B+TREE中的叶子结点找到主键值,再在以主键值创建的B+TREE中找到对应的数据。
-
覆盖索引:遍历一次B+TREE
- 如果一个索引包含要查询的字段值,这样查询只需要扫描索引而无需回表。
-
-
非聚簇索引
- MyISAM叶子结点存储的数据的地址,根据地址把表中的数据读取出来。
-
主键索引
- 唯一性非空索引
-
唯一索引
- 索引列唯一,可以为空
-
普通索引
- 没有唯一性限制,可以为空
-
全文索引
- 查找文本中的关键词,而不是直接比较索引中的值
-
组合索引
-
最左前缀法则
- 查询从索引的最左前列开始并且不跳过中间的列
-
全值匹配
-
索引的优点
- 大大减少了服务器的扫描行数
- 帮助服务器避免了排序和分组,也避免了创建临时表
- 将随机IO变为顺序IO
应该使用索引
- 频繁作为查询条件的字段
- 经常使用表连接的字段
- 经常需要范围查找的字段
- 经常需要排序的字段
不应该使用索引
-
表的记录太少
- 索引表和数据表访问两次反而会变慢
-
经常增删改的表或字段
- 字段和索引都需要维护
-
过滤性不好的字段
- 字段唯一性较弱,比如 性别1、0
-
不在where条件里的字段
索引失效和优化
-
单表
-
全值匹配
- 索引尽量包含where条件后的所有字段
-
最左匹配原则
- create index idx_age_deptid_name on emp(age,deptid,name);
Explain select sql_no_cache * from emp where deptid=4 and emp.age=30 and emp.name=‘abcd’;
– 命中索引三个字段 (顺序由优化器优化)
Explain select sql_no_cache * from emp where emp.age=30 and emp.name=‘abcd’;
– 命中第一个字段,不会命中第二个字段
Explain select sql_no_cache * from emp where deptid=4 and emp.name=‘abcd’;
– 不命中索引字段
- create index idx_age_deptid_name on emp(age,deptid,name);
-
独立的列
- 不在索引上做任何操作 (计算、函数和类型转换),始终将索引列放在比较符号的一侧。
- create index idx_name on emp(name);
Explain select sql_no_cache * from emp where left(emp.name,3) ‘abc’;
– 不命中索引字段
-
不能使用索引中范围条件右边的索引列
- create index idx_age_deptid_name on emp(age,deptid,name)
Explain select sql_no_cache * from emp where emp.age=30
and emp.name=‘abc’ and emp.deptId>20;
– 命中索引age和deptid字段,范围查询字段右边的索引失效
create index idx_age_deptid_name on emp(age,name,deptid)
– 命中三个字段
- create index idx_age_deptid_name on emp(age,deptid,name)
-
Like不以通配符开头
- 左前缀原则
- create index idx_name on emp(name);
Explain select sql_no_cache * from emp where emp.name like =‘abc%’;
– 命中索引字段,因为它可以做简单的比较操作
Explain select sql_no_cache * from emp where emp.name like ‘%abc%’;
– 索引失效
-
不使用 ! =、< >
- create index idx_name on emp(name);
Explain select sql_no_cache * from emp where emp.name <> ‘abc’;
– 索引失效<>
- create index idx_name on emp(name);
-
不使用IS NOT NULL,但IS NULL可以使用索引
- create index idx_age on emp(age);
Explain select sql_no_cache * from emp where age is null;
Explain select sql_no_cache * from emp where age is not null;
– is not 索引失效
- create index idx_age on emp(age);
-
字符串一定要加单引号
- Explain select sql_no_cache * from emp where emp.name =123;
create index idx_name on emp(name);
– 可以查询,MySQL自动类型转换
– 索引失效 (name是varchar类型)
- Explain select sql_no_cache * from emp where emp.name =123;
-
一般性建议(3)
- 单键索引:尽量选择过滤性好的字段,比如手机号、身份证号
- 组合索引:尽量选择过滤性好的字段放在组合索引的最前面:尽量包含where条件后的所有字段,一般不超过5个;尽量把范围查找的字段放在组合索引最后面
- 书写sql,尽量避免造成索引失效
-
-
关联查询
-
尽量给被驱动表建索引,LEFT JOIN
- Explain select * from class LEFT JOIN book ON class.card=book.card;
– 不建索引两个表进行全表扫描,扫描行数为两个表做笛卡尔积数 (2020)
alter table book add index Y(card);
alter table class add index X(card);
– Y索引有效,因为只能给被驱动表创建索引 (物理扫描行数:201)
– 即使class表建索引,也不能再次优化
- Explain select * from class LEFT JOIN book ON class.card=book.card;
-
优化器会优化选择表顺序,一般把小表放前面,可减少物理扫描行数。INNER JOIN
- Explain select * from class INNER JOIN book ON class.card=book.card;
alter table class add index Y(card);
– 索引优化,MySQL自主选择驱动表和被驱动表
– 一张大表,一张小表,大表放到被驱动表位置可减少物理扫描行数
- Explain select * from class INNER JOIN book ON class.card=book.card;
-
一般性建议(3)
- 尽量保证给关联表的连接字段建索引
- LEFT JOIN:手动选择小表作为驱动表,大表作为被驱动表
- INNER JOIN:MySQL优化器会自动把小表放在驱动表位置
-
-
子查询
-
尽量不使用not in或not exists,使用left join on xxx is null替代
- select * from emp e where e.eid not in (
select d.eid from dept d where d.eid is not null);
– 优化子查询:确保索引不失效
select * from emp e left join dept d on e.eid=d.eid where d.eid is null;
- select * from emp e where e.eid not in (
-
-
排序分组
-
如果出现using filesort(手工排序),说明ORDER BY没有使用索引
-
无过滤,不索引
- create index idx_age_deptid_name on emp(age,deptid,name)
Explain select sql_no_cache * from emp order by age,deptid;
– 没有用到索引,using filesort依旧存在
Explain select sql_no_cache * from emp order by age,deptid limit 10;
– 使用到索引三个字段,ORDER BY无过滤,不索引
- create index idx_age_deptid_name on emp(age,deptid,name)
-
顺序错,必排序
- ORDER BY的字段顺序必须和组合索引字段的顺序一致。优化器不会改变ORDER BY的字段顺序,因为优化器是在不改变结果的情况下优化的。
- create index idx_age_deptid_name on emp(age,deptid,name);
Explain select * from emp where age=45 order by deptid;
Explain select * from emp where age=45 order by deptid,name;
– 使用到了索引age字段,order by使用到了索引字段
Explain select * from emp where age=45 order by deptid,eid;
– ORDER BY没有使用到索引,如果要使用到索引,索引字段必须为(age,deptid,eid);
Explain select * from emp where age=45 order by name,deptid;
– ORDER BY没有使用到索引, 因为:
– 优化器是在不改变查询结果的前提下,才会调整顺序
Explain select * from emp where deptid=45 order by age;
– where和ORDER BY都没有用到索引
-
方向反,必排序
- ORDER BY字段的排序方向必须一致
- create index idx_age_deptid_name on emp(age,deptid,name);
Explain select * from emp where age=45 order by deptid desc,name desc;
– ORDER BY使用到了索引,都是desc,只是换了取索引的方向
Explain select * from emp where age=45 order by deptid asc,name desc;
– 一个desc,一个asc 索引失效
-
-
前缀索引
-
varchar(11)类型
-
索引的选择性是指:不重复的索引值和数据表的记录总数的比值。
- SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;
//0.0312 如果前缀的选择性接近0.031,基本就可用了。
- SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;
-
-
覆盖索引
-
索引并不是越多越好
索引监控
-
show status like ‘Handler_read%’
-
参数解释
- Handler_read_first:读取索引第一条目的次数
- Handler_read_key:通过index获取数据的次数
- Handler_read_last:读取索引最后一条目的次数
- Handler_read_next:通过索引读取下一条数据的次数
- Handler_read_prev:通过索引读取上一条数据的次数
- Handler_read_rnd:从固定位置读取数据的次数
- Handler_read_rnd_next:从数据节点读取下一条数据的次数
MySQL集群
主从复制
-
当对主库进行增删改操作,从库异步获取
-
原理
- 1、写操作:主库数据改变会写入到本地bin-log。实时监控:从库会生成两个线程,一个I/O线程,一个SQL线程;
- 2、写从日志:I/O线程会去请求主库的bin-log,并将得到的bin-log 写入到本地的relay-log(中继日志)和bin-log日志中(如果还有从库)
- 3、主库会生成一个log-dump线程,用来给从库I/0线程传送bin-log;
- 4、读取日志并解析执行:从库的SQL线程读取relay-log,解析sql语句并逐一执行。
-
MySQL 本身就可以实现主从复制,只需配置即可。配置方式有一主一从、一主多从、多主一从
-
主从复制最好能保证主库和从库的版本一致,如果不能,那么尽量保证主库的版本低于从库的版本。
读写分离
-
增删改操作主库,查询操作从库
-
提高系统性能原因
- 主从库只负责各自的读或写,极大地缓解了X锁和S锁的争用
- slave库可以配置MyISAM引擎,提升查询性能以及节约系统开销
- 主库直接写是并发的,从库通过读取主库的bin-log日志是异步读取的
-
负载均衡策略:可以通过负载均衡策略将查询疏散到不同的从服务器
-
读写分离常采用代理方式来实现,代理层接收应用层传送过来的读写请求,然后决定转发到哪个服务器。
分库分表
-
拆分方式
-
垂直拆分
-
拆分表:将一张表的部分字段分配到一张表,另一些字段分配到一张表。
- 比如:课程信息表和课程描述表
-
拆分库:专库专表。根据业务拆分成多个不同的数据库。
- 比如:一个库包括订单表和用户表,现在分为两个库,一个订单的数据库和一个用户的数据库。
-
垂直拆分可以缓解数据量和访问量的压力,但无法根治
-
-
水平拆分
-
拆分表:根据分片策略(id%2==0)将添加数据到不同的表。多个表的结构完全相同。
- 比如:一个库中两张相同的订单表,使用ShardingSphere-JDBC划分数据到不同表。
-
拆分库:根据分片策略(id%20/uid%20)将数据添加到不同库的不同表中。
- 比如:两个库中都有一组相同的表结构。
-
-
操作公共表
- 多个库中都有一张或多张相同的表名和表结构,增删改数据在不同数据库中的表都是同步的。
-
拆分问题
- 跨节点的连接查询问题(分页、排序)
- 多数据源管理问题
- 跨库事务
-
-
ShardingSphere-JDBC
- 轻量级的Java框架,增强版的JDBC驱动
- 使用JDBC实现水平切分
- 使用JDBC实现垂直切分
- 使用JDBC操作公共表
- 使用JDBC读写分离
- 通过对SQL语句的语义解析,实现读写分离过程,不是实现主从复制。
-
ShardingSphere-Proxy
- 透明化的数据库代理端,可以直接当MySQL使用
- 使用 Proxy 读写分离
- 使用 Proxy 分库分表
-
Mycat(前身阿里cobar)
schema与数据类型优化
数据库组织和结构优化
-
表达是否概念的字段,必须使用is_xxx命名,数据类型unsigned tinyint类型。1表示是,0表示否。
- 任何非负整数的字段,必须是unsigned类型
- POJO类中任何表达是否的字段,都不加is前缀
- 表达逻辑删除的字段是is_deleted,1表示删除,0表示未删除
-
表名、字段名必须使用小写字母或数字,禁止出现以数字开头、两个下划线中间只有数字的命名。
- 正例:aliyun_admin、rdc_config、level3_name
- 反例:AliyunAdmin、RdcConfig、level_3_name
-
表必备三字段:id、create_time、update_time
-
超过三个表禁止join。需要 join 的字段,数据类型必须保持一致;多表关联时,保证被关联的字段必须有索引。
- 即使是双表也需要建索引,以提高SQL性能
-
推荐
-
表名最好是:业务名称_表的作用
-
库名与应用名称尽量一致
-
如果修改字段的含义,需要更新字段注释
-
单表超过500万行数据或表容量超过2GB,才推荐分库分表
- 如果预计三年后达不到这个数据量,就不推荐分库分表
-
合适的字符存储长度,节省空间,也提高了查询效率,无符号数避免了误存负数
- unsigned tinyint:0 ~ 255
- smallint unsigned:0 ~ 65535
- int unsigned:0 到 约43亿
- bigint unsigned:0 到 约10的19次方
-
数据类型优化
-
小数类型使用decimal,禁止使用float和double类型
- 在存储的时候,float和double会存在精度损失的问题,很可能在比较值得时候,得到不正确的结果
- 如果存储的数据超过decimal范围,将整数和小数分开存储。
- float 是浮点数,不能指定小数位。decimal 是精确数,可以指定精度,在内存中以字符串形式保存。
- decimal(5,2)小数点默认存储两位,不足补0,如果位数超过5位,保存报错。
-
如果存储的字符串长度几乎相等,使用char定长字符串类型
- 定长字符串 char:0 ~ 255
-
varchar是可变长字符串字段,不预先分配存储空间,长度不要超过5000。如果超过此长度,定义字段类型text,独立出一张表,用主键对应,避免影响其他字段的索引效率
- 可变字符串 varchar:0 ~ 65535
- 长文本数据 text:0 ~ 65535
执行计划
Explain 用来分析SELECT查询语句,查看sql语句的具体执行过程。
Explain 解析sql
-
id:SELECT 查询的序列号,表示查询语句中执行SELECT子句或操作表的执行顺序。
- id相同,执行顺序从上至下
- 如果id不同,如果是子查询,id的序列号会递增,id值越大,优先级越高,越先被执行
-
select_type:用来分辨查询类型。判断是普通查询/联合查询/子查询。
- sample:简单的查询。不包含子查询和union
- primary:查询中若包含任何复杂的子查询,最外层的查询被标记为Primary
- union:若第二个select出现在union后,标记为union
- dependent union:和union类似,此处dependent表示union和union all联合而成的结果会受到外部表的影响
- union result:从union表获取结果的select
- subquery:在select或者where列表中包含子查询
- dependent subquery:subquery的子查询要受到外部表查询的结果
- DERIVER:from子句中出现的子查询
- uncacheable subquery:表示使用子查询的结果不能被缓存
- uncacheable union:表示union的查询结果不能被缓存:sql语句未验证
-
table:正在访问的表名或别名。可能是临时表或union合并结果集。
-
partitions:匹配的分区。
-
type:访问类型
- system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 效率依次降低。
- 一般情况下要达到range级别,最好到达ref级别
- all:全表扫描。一般出现这样的情况且数据量比较大的时候,就需要进行优化。
- index:全索引扫描。一是覆盖索引;二是索引排序。
- range:表示利用索引查询的时候进行了限制,在指定范围内查询,这样就避免了index全索引扫描
- index_subquery:利用索引来关联子查询,不再全表扫描
- union_subquery:使用的是唯一索引
- index_merge:在查询的时候需要多个索引组合使用
- ref_or_null:对于某个字段需要关联查询也需要null值的情况下,优化器会使用这种访问方式
- ref:使用了非唯一性索引进行数据的查找
- eq_ref:使用唯一性索引进行数据的查找
- const:这个表至多有一个匹配行
- system:表只有一行记录(系统表),这是const的特例,平时不会出现
-
possible_keys:显示可能应用在这张表中的索引,一个或多个,查询到的字段上若存在索引,该索引的类型将被类型,但不一定被查询实际使用
-
key:实际使用的索引,如果使用了覆盖索引,那么索引和实际的查询字段重叠。
-
key_len:索引中使用的字节数,计算索引中的索引查询,长度越短越好
-
ref:表示索引的哪一列被使用了,可能是一个常数
-
rows:根据表的统计信息和索引的使用,大致估算出扫描所需要的行数,此参数很重要,越小越好。
-
filtered:按表条件过滤行的百分比
-
Extra:包含额外的信息
- using filesort:mysql没有利用索引排序,使用了排序算法进行排序(手工排序),会消耗额外的位置。
- using temporary:创建临时表存储中间结果,查询完成删除临时表。
- using index:表示当前查询是覆盖索引的,直接从索引中读取数据,而不用访问数据表。
- using where:使用where条件查询
- using join buffer:使用连接缓存
- impossible where:where子句的结果总是false
mysql执行计划.md
XMind - Trial Version