MySQL调优

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_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;
      – 不建索引两个表进行全表扫描，扫描行数为两个表做笛卡尔积数 (2020)
      alter table book add index Y(card);
      alter table class add index X(card);
      – Y索引有效，因为只能给被驱动表创建索引 (物理扫描行数：201)
      – 即使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：读取索引最后一条目的次数
  • 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