瑞幸咖啡网站建设方案,酒店行业的网站建设,开源cms管理系统,淘宝官网网页版MySQL之性能分析和系统调优
性能分析
查看执行计划 EXPLAIN
EXPLAIN作为MySQL的性能分析神器#xff0c;可以用来分析SQL执行计划#xff0c;需要理解分析结果可以帮助我们优化SQL
explain select … from … [where ...]TABLE 表名 查询的每一行记录都对于着一张表 id 该…MySQL之性能分析和系统调优
性能分析
查看执行计划 EXPLAIN
EXPLAIN作为MySQL的性能分析神器可以用来分析SQL执行计划需要理解分析结果可以帮助我们优化SQL
explain select … from … [where ...]TABLE 表名 查询的每一行记录都对于着一张表 id 该语句的唯一标识。如果explain的结果包括多个id值则数字越大越先执行而对于相同id的行则表示从上往下依次执行。 select_type 每一个子查询在语句中扮演的角色 simple 不包含union或者子查询的语句primary 最外层的查询union union中第二个后随后的select被标记为union如果union被from子句中的子查询包含那么它的第一个select会被标记为deriveddependent union union中第二个或后面的的查询依赖了外面的查询subquery 子查询的第一个selectdependent subquery 子查询的第二个或后面的select 依赖了外面的查询derived 表示包含在from子句的子查询中的selectMySQL会将执行结果放在一个临时表中MySQL内部将其称为derived table因为该表是从临时查询中派生出来的materialized将子查询物化之后供外层连接使用 partitions 当前查询匹配记录的分区 type 执行查询时的访问方法 system表中只有一个数据并且使用的存储引擎的统计数据是精确的有一个变量记录比如myisam memory const根据主键或者唯一的二级索引列等值匹配时 eq_ref被驱动表是通过主键或者唯一的二级索引进行匹配的 ref普通的二级索引进行等值匹配 ref_or_null该类型类似于ref但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询 index_merge此类型表示使用了索引合并优化表示一个查询里面用到了多个索引 unique_subquery该类型和eq_ref类似但是使用了IN查询且子查询是主键或者唯一索引 index_subquery和unique_subquery类似只是子查询使用的是非唯一索引 range范围扫描表示检索了指定范围的行主要用于有限制的索引扫描 index全索引扫描和ALL类似只不过index是全盘扫描了索引的数据 查询仅使用索引中的一部分列时可使用此类型。有两种场景会触发 如果索引是查询的覆盖索引并且索引查询的数据就可以满足查询中所需的所有数据则只扫描索引树。此时explain的Extra 列的结果是Using index。index通常比ALL快因为索引的大小通常小于表数据。按索引的顺序来查找数据行执行了全表扫描。此时explain的Extra列的结果不会出现Uses index。 all全表扫描性能最差 possible_keys 展示当前查询可以使用哪些索引这一列的数据是在优化过程的早期创建的因此有些索引可能对于后续优化过程是没用的。 key 表示MySQL实际选择的索引 key_len 索引使用的字节数。由于存储格式当字段允许为NULL时key_len比不允许为空时大1字节 主要针对联合索引是否充分利用索引 ref 表示将哪个字段或常量和key列所使用的字段进行比较。 如果ref是一个函数则使用的值是函数的结果。要想查看是哪个函数可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。 rows MySQL估算会扫描的行数数值越小越好。 filtered 表示符合查询条件的数据百分比最大100。用rows × filtered可获得和下一张表连接的行数。例如rows 1000filtered 50%则和下一张表连接的行数是500。 extra no tables used没有from子句impossible wherewhere始终为falseusing where不使用索引使用索引但包含不使用索引的条件no match min/max使用聚合函数但没有匹配的where、using index查询到数据使用二级索引不需要进行回表操作如果进行回表则是nullusing index conditionIndex condition pushdown索引条件下推 EXPLAIN格式
传统格式缺少一个衡量执行计划好坏的重要信息–成本JSON格式在explain和语句之间加上formatJSON 多一些详细信息如成本TREE格式在8.0.16新增格式 主要由各部分之间的关系和执行顺序决定可视化输出workbench
show warmings
展示真实执行的sql语句
数据库设计规范
数据库主键的设计
自增ID 自增ID的问题缺点太多核心业务不推荐使用 1.可靠性不高在集群模式下存在自增ID回溯的问题 2.安全性不高容易被被爬虫获取数据 3.性能差需要占用数据库服务端的资源有AUTO-INC锁来完成 4.交互多 5.局部唯一性在一台机器下 业务字段 与业务有关的字段不能设计为主键对于数据库维护造成很大困难 UUID 优点性能高不依赖网络使用简单全局唯一 缺点占用36个字节长度过长没有含义无序 有序UUID 将时间高位和地位互换这样时间保证了单调递增去掉无用‘-’并使用二进制存储 雪花算法 时间戳数据单调递增数据中心ID机器编码全局唯一性序列号1ms内生成多个ID的问题 缺点 依赖了机器ID的分配集群的扩容和缩容需要重新分配可能会导致ID重复 解决可以使用zookeeper的临时顺序节点分配ID 依赖了系统时间如果集群中的机器时间被回拨也可能造成ID重复 解决一个客户端在取到新ID后与之前最大的ID比较如果ID变小则报错或者等待 集群美团的leaf每次有新节点接入zk时会将节点上报的时间和集群平均的时间对比如果新加入的节点的时间落后于平均时间则报错 范式
1NF 确保每一个字段的值都有原子性属性的原子性有主观性字段不可再分 2NF 得有主键且非主键字段必须完全依赖于主键不能只依赖主键的一部分 一个表只表达一个意思 如果不完全依赖候选键会导致 1.数据冗余重复数据记录多次 2.插入异常 3.删除异常 4.更新异常 应该将表拆分直到满足2NF 3NF 非主键字段不能依赖于其他非主键字段非主键字段之间相互独立不存在传递性 反范式化 遵循业务优先的原则为了减少关联查询可以适当增加冗余字段来增强读性能以空间换时间 一个表中的字段修改那么也应该同步修改另一张表保证数据一致性 适用场景 增加的冗余字段不需要经常进行修改查询的时候不可或缺 SQL语句调优
逻辑查询优化
关联查询优化
联接算法 联接算法是MySQL数据库用于处理联接的物理策略。目前MySQL数据库仅支持Nested-Loops Join算法。 两张表的Join的过程 上图的Fetch阶段是指当内表关联的列是辅助索引时但是需要访问表中的数据那么这时就需要再访问主键索引才能得到数据的过程不论表的存储引擎是InnoDB存储引擎还是MyISAM这都是无法避免的只是MyISAM的回表速度要快点因为其辅助索引存放的就是指向记录的指针而InnoDB存储引擎是索引组织表需要再次通过索引查找才能定位数据。 Fetch阶段也不是必须存在的如果是聚集索引联接那么直接就能得到数据无需回表也就没有Fetch这个阶段。另外上述给出了两张表之间的Join过程多张表的Join就是继续上述这个过程。 接着计算两张表Join的成本这里有下列几种概念 外表的扫描次数记为O。通常外表的扫描次数都是1即Join时扫描一次外表驱动表的数据即可 内表的扫描次数记为I。根据不同Join算法内表被驱动表的扫描次数不同 读取表的记录数记为R。根据不同Join算法读取记录的数量可能不同 Join的比较次数记为M。根据不同Join算法比较次数不同 回表的读取记录的数记为F。若Join的是辅助索引可能需要回表取得最终的数据 Simple Nested-Loops JoinSNLJ简单嵌套循环联接 没有索引小表驱动大表暴力循环查找 Index Nested-Loops JoinINLJ基于索引的嵌套循环联接 在内表建立索引以此降低Nested-Loop Join算法的开销减少内表扫描次数 外连接消除 我们前边说过内连接的驱动表和被驱动表的位置可以相互转换而左外连接和右外连接的驱动表和被驱动表是固定的因为有些不满足联接条件的记录会通过外部表行的方式再次添加到结果中。这就导致内连接可以通过优化表的连接顺序来降低整体的查询成本而外连接却无法优化表的连接顺序。 外连接和内连接的本质区别就是对于外连接的驱动表的记录来说如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记录的各个字段使用NULL值填充而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录那么该记录会被舍弃。 Block Nested-Loops JoinBNL基于块的嵌套循环联接 引入join buffer缓冲区不是一次一条取驱动表数据而是一块一块取减少IO次数在8.0.2版本被hash join替代 整体效率INLJBNLJSNLJ
优化策略 1.用小的结果集驱动大的结果集 2.为被驱动表的匹配条件字段增加索引减少内层循环次数 3.使用BNLJ时可以适当增大join buffer的大小 4.hash join大数据连接时常用方式优化器使用较小的表的join key在内存中建立散列表通过扫描较大的表并探测散列表找出匹配的行 子查询优化 子查询可以帮准我们通过一个SQL来实现较为复杂的的查询但子查询的效率一般都不高 缺点 1.执行子查询时内层查询会建立一个临时表撤销临时表都会消耗资源产生大量慢查询 2.产生的临时表没有索引效率低 建议使用连接查询JOIN来代替子查询 排序优化 可以通用filesort和index两种方式排序 index 通过有序索引顺序扫描直接返回有序数据不需要额外的排序操作效率较高。 filesort通过对返回数据进行排序filesort 并不代表通过磁盘文件排序而是说明进行了一个排序操作至于排序操作是否使用了磁盘文件或临时表等则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。 有两种排序算法 1.双路排序算法从磁盘取排序的字段在buffer中排序后再从磁盘中取排好序的数据 2.单路排序算法从磁盘读取所有需要排序字段的排序数在buffer中排好序输出内存空间占用大sort_buffer_size适当调大 sort_buffer_size设置的排序区是每个线程独占的所以同一时刻MySQL中存在多个sort buffer排序区。 GROUP BY优化 先排序再分组 遵循最佳左前缀原则 无法使用索引列时增加sort_buffer_size和max_length_for_sort_data where效率高于having 减少使用order by尽量在客户端进行数据排序数据库的CPU资源是宝贵的 使用覆盖索引和索引下推
在索引篇已经说明
物理查询优化
索引优化 sql语句是否使用索引与优化器的选择有关数据库版本数据量数据选择度不基于语义也不基于规则而是基于const开销 什么会导致索引失效
、范围查询(一般将范围查询条件放在最后) mysql 会一直向右匹配直到遇到索引搜索键使用、就停止匹配。一旦权重最高的索引搜索键使用、范围查询那么其它、搜索键都无法用作索引。即索引最多使用一个、的范围列因此如果查询条件中有两个、范围列则无法全用到索引。 like %xx 如搜索键值以通配符%开头如like %abc则索引失效直接全表扫描若只是以%结尾则不影响索引构建 类型转换导致索引失败 如果列是字符串类型传入条件是必须用引号引起来不然报错或索引失效 计算或者函数导致索引失效 如果查询条件中含有函数或表达式将导致索引失效而进行全表扫描 不等于索引失效情况 普通索引使用 !索引失效主键索引没影响。 where语句中索引列使用了负向查询可能会导致索引失效。 负向查询包括NOT、!、、NOT IN、NOT LIKE等。 or 条件索引问题 or 的条件列除了同时是主键的时候索引才会生效。其他情况下的无论条件列是什么索引都失效。 联合索引违背最左匹配原则 联合索引中where中索引列违背最左匹配原则一定会导致索引失效 其他优化策略
exists和in的区别 主要由表的大小决定in适合用于驱动表比被驱动表小的情况exists适合用于驱动表比被驱动表大的情况 count*和count具体字段的区别 如果是myisam存储引擎在每一张表都有一个row_count字段存储表数据行数那么查询数据行数的时间复杂度是o(1)的但由于InnoDB中支持行级锁和MVCC机制无法维护这样的变量所以是全表扫描时间复杂度是o(n)的 count(具体字段)一般推荐使用二级索引因为二级索引存储的字段更小count(*)和count(1)一般自动选择key_length小的索引 关于select* mysql中将*通过查询数据字段转变为对应的字段需要花费时间和资源 不能使用覆盖索引 limit 1 对于会进行全表扫描的sql如果我们确定结果集只有一个那么加上limit 1后查询到一条数据后就不会再进行查询加快查询速度 但是如果针对已经是唯一索引的字段就没有必要了 多使用commit commit会释放相应的资源 1.回滚段记录的用于恢复数据 2.被程序获得的锁 3.redo/undo log buffer中的空间 4.管理上述资源花费的资源 数据库其他调优
选择合适的DBMS 如果对数据安全性以及事务性处理要求高的话可以使用SQL Server或者Oracle 选择合适的存储引擎innodb支持事务行锁外键等适用于事务性要求高myisam处理大量的更新操作 优化表的设计 表要求遵循第三范式 如果查询应用较多进行的大量的多表查询操作可以将经常查询的字段增加来换取查询的效率也是反范式化 表的字段类型的选择 优化查询逻辑 逻辑查询优化改变SQL语句的内容让SQL执行更高效 物理查询优化索引优化 使用Redis作为缓存
库级优化 读写分离 数据分片对数据库分库分表 优化MySQL服务器 服务器硬件优化 优化数据库结构 拆分表冷热数据的分离一个页中可以存储更多数据减少磁盘IO 增加中间表 增加冗余字段反范式化 优化数据类型 1.比如对于非负型整数可以使用unsigned来存储 2.对于文本数据类型大整数占用空间更少 3.避免使用text blogMySQL内存临时表不支持必须使用磁盘临时表性能差 4.避免使用enum修改enum需要使用alter语句尽量用tinyint来替代 5.使用timestamp 6.使用decimal避免float double 优化插入记录的速度 myisam 1.禁用索引 2.禁用唯一性 3.检查使用批量插入 innodb 1.禁用唯一性检查 2.禁用外键检查 3.禁止自动提交 使用非空约束
分析表检查表优化表 分析表ANALYZE 分析表各个字段 检查表CHECK 优化表OPTIMIZE 大表优化 限定查询范围 读写分离 垂直拆分 分库分表 水平拆分 其他调优策略 服务器语句超时处理 创建全局通用表空间 隐藏索引
