首页  思维导图  详情

MySQL调优

2020-10-22 16:10:01   2  举报





AI智能生成

mysql调优、优化

mysql查询优化

mysql索引优化

mysql解析sql语句

mysql主从复制读写分离分库分表

作者其他创作

大纲/内容

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集群

主从复制

当对主库进行增删改操作，从库异步获取

原理

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%2==0/uid%2==0)将数据添加到不同库的不同表中。

比如：两个库中都有一组相同的表结构。

操作公共表

多个库中都有一张或多张相同的表名和表结构，增删改数据在不同数据库中的表都是同步的。

拆分问题

跨节点的连接查询问题（分页、排序）

多数据源管理问题

跨库事务

ShardingSphere-JDBC

轻量级的Java框架，增强版的JDBC驱动

使用JDBC实现水平切分

使用JDBC实现垂直切分

使用JDBC操作公共表

使用JDBC读写分离

通过对SQL语句的语义解析，实现读写分离过程，不是实现主从复制。

ShardingSphere-Proxy

透明化的数据库代理端，可以直接当MySQL使用

使用 Proxy 读写分离

使用 Proxy 分库分表

Mycat（前身阿里cobar）

查询性能优化

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_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) -- 命中三个字段

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'; -- 索引失效<>

不使用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 索引失效

字符串一定要加单引号

Explain select sql_no_cache * from emp where emp.name =123; create index idx_name on emp(name); -- 可以查询,MySQL自动类型转换 -- 索引失效 (name是varchar类型)

一般性建议（3）

单键索引：尽量选择过滤性好的字段，比如手机号、身份证号

组合索引：尽量选择过滤性好的字段放在组合索引的最前面：尽量包含where条件后的所有字段，一般不超过5个；尽量把范围查找的字段放在组合索引最后面

书写sql，尽量避免造成索引失效

关联查询

尽量给被驱动表建索引，LEFT JOIN

Explain select * from class LEFT JOIN book ON class.card=book.card; -- 不建索引两个表进行全表扫描，扫描行数为两个表做笛卡尔积数 (20*20) alter table book add index Y(card); alter table class add index X(card); -- Y索引有效，因为只能给被驱动表创建索引 (物理扫描行数：20*1) -- 即使class表建索引，也不能再次优化

优化器会优化选择表顺序，一般把小表放前面，可减少物理扫描行数。INNER JOIN

Explain select * from class INNER JOIN book ON class.card=book.card; alter table class add index Y(card); -- 索引优化，MySQL自主选择驱动表和被驱动表 -- 一张大表，一张小表，大表放到被驱动表位置可减少物理扫描行数

一般性建议（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;

排序分组

如果出现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无过滤，不索引

顺序错，必排序

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，基本就可用了。

覆盖索引

索引并不是越多越好

索引监控

show status like 'Handler_read%'

参数解释

Handler_read_first：读取索引第一条目的次数

Handler_read_key：通过index获取数据的次数

Handler_read_last：读取索引最后一条目的次数