网站开发带后台,杭州市造价网价格查询,网站建设论文结尾,郴州网站设计导航#xff1a; 本文一些内容需要聚簇索引、非聚簇索引、B树、覆盖索引、索引下推等前置概念#xff0c;虽然本文有简单回顾#xff0c;但详细可以参考下文的【MySQL高级篇】 【Java笔记踩坑汇总】Java基础进阶JavaWebSSMSpringBoot瑞吉外卖SpringCloud黑马旅游谷粒商城学成… 导航 本文一些内容需要聚簇索引、非聚簇索引、B树、覆盖索引、索引下推等前置概念虽然本文有简单回顾但详细可以参考下文的【MySQL高级篇】 【Java笔记踩坑汇总】Java基础进阶JavaWebSSMSpringBoot瑞吉外卖SpringCloud黑马旅游谷粒商城学成在线MySQL高级篇设计模式常见面试题源码 目录
一、监控报警
二、排查慢SQL
2.1 开启慢查询日志
2.2 找出最慢的几条SQL
2.3 分析查询计划
2.3.1 EXPLAIN命令
2.3.2 EXPLAIN ANALYZE命令
三、MySQL调优
3.1 基础优化
3.1.1 缓存优化
3.1.1.0 简介
3.1.1.1 缓冲池优化
3.1.1.2 Redis优化
3.1.2 硬件优化
3.1.3 参数优化
3.1.4 定期清理垃圾
3.1.4.1.清理不再使用的表
3.1.4.2.清理过期数据
3.1.4.3.清理日志
3.1.4.4.清理缓存池
3.1.4.5.优化表OPTIMIZE TABLE
3.1.4.6.分析表ANALYZE TABLE
3.1.4.7.计划任务清理数据、日志、优化表
3.1.5 使用合适的存储引擎
3.1.5.1 各存储引擎使用场景
3.1.5.2 回顾各存储引擎和B树
3.1.6 读写分离
3.1.7 分库分表
3.2 表设计优化
3.2.1 混合业务分表、冷热数据分表
3.2.2 联合查询改为中间关系表
3.2.3 遵循三个范式
3.2.4 字段建议非空约束
3.2.5 反范式使用冗余字段
3.2.6 数据类型优化
3.3 索引优化
3.3.0 数据准备
3.3.0.1 创建学生表并生成50w条数据
3.3.0.2 创建删除所有索引的存储过程
3.3.1 考虑索引失效的11个场景
3.3.1.1.尽量全值匹配
3.3.1.2.考虑最左前缀
3.3.1.3.主键尽量有序
3.3.1.4.计算、函数导致索引失效
3.3.1.5.类型转换导致索引失效
3.3.1.6.没索引下推时范围条件右边的列索引失效
3.3.1.7.没覆盖索引时“不等于”导致索引失效
3.3.1.8.没覆盖索引时左模糊查询导致索引失效
3.3.1.9.没覆盖索引时is not null、not like无法使用索引
3.3.1.10.“OR”前后存在非索引列或不同索引列导致索引失效
3.3.1.11.不同字符集导致索引失败
3.3.2 遵循索引设计原则
3.3.3 连接查询优化
3.3.3.1 被驱动表连接字段加索引
3.3.3.2 小表驱动大表
3.3.3.3 两表连接字段类型必须一致
3.3.4 子查询优化
3.3.4.1.子查询优化成关联查询
3.3.4.2.多次查询代替子查询
3.3.4.3.临时表代替子查询
3.3.5 排序优化
3.3.5.1 合理选择索引排序和FileSort排序
3.3.5.2 排序字段符合最左前缀
3.3.5.3 全升序或者全降序
3.3.5.4 待排序数量大时尽管索引没失效索引效率不如filesort
3.3.5.5 范围查询使排序索引失效
3.3.5.6 优先范围字段加索引即使排序索引失效
3.3.5.7 调优FileSort
3.3.6 分组优化
3.3.7 深分页查询优化
3.3.8 尽量覆盖索引
3.3.9 字符串前缀索引
3.3.10 尽量使用MySQL5.6支持的索引下推
3.3.11 读少写多的场景尽量用普通索引
3.4 SQL优化
3.4.1 合理选用 EXISTS 和 IN
3.4.2 统计数量COUNT(1) 或 COUNT(*)
3.4.3 避免SELECT *
3.4.4 全表扫描时尽量用 LIMIT
3.4.5 使用 LIMIT N少用 LIMIT M, N
3.4.6 代码将长事务拆为多个小事务
3.4.7 删改前先查询
3.4.8 尽量UNION ALL而不是UNION MySQL调优主要分为三个步骤监控报警、 排查慢SQL、MySQL调优。
一、监控报警
在MySQL调优过程中首先第一步是发现问题而发现慢SQL的场景可以是用户访问时查询慢也可以是通过监控工具监控到慢SQL。
监控工具例如PrometheusGrafana监控MySQL发现查询性能变慢时可以报警提醒运维人员 其他监控工具
MySQL Enterprise Monitor由Oracle提供的商业工具提供实时和历史的MySQL性能监控包括查询性能、服务器状态、数据库复制等。Percona Monitoring and ManagementPercona提供的开源工具提供性能监控、查询分析、数据库配置等功能。MyTOP一个基于命令行的工具用于实时监控MySQL数据库的性能。MySQL Performance SchemaMySQL自带的性能监控工具可以通过查询Performance Schema表来获取有关数据库性能和资源利用情况的详细信息。Nagios一个通用的网络监控工具可以使用插件来监控MySQL数据库的各种指标。Zabbix一个通用的网络监控工具可以使用插件来监控MySQL数据库的各种指标。Datadog一个云端监控服务提供对MySQL数据库的性能和状态的实时监控。Prometheus Grafana一组流行的开源工具可以通过Prometheus监控MySQL数据库然后使用Grafana创建漂亮的仪表板进行可视化。
二、排查慢SQL
2.1 开启慢查询日志
查看慢查询次数
# 临时关闭慢查询日志如果想永久关闭需要修改my.ini或my.cnf配置文件
show status like slow_queries; 查询慢查询日志是否打开
SHOW VARIABLES LIKE slow_query_log;开启慢查询日志修改慢查询阈值
set slow_query_logON; #开启慢查询日志
set long_query_time 1; #设置慢查询阈值 2.2 找出最慢的几条SQL
可以通过mysqldumpslow命令分析慢查询日志找到最慢的几条语句
mysqldumpslow 命令的具体参数如下
-a: 不将数字抽象成N字符串抽象成S-s: 是表示按照何种方式排序c: 访问次数l: 锁定时间r: 返回记录t: 查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间 默认方式ac:平均查询次数-t: 即为返回前面多少条的数据-g: 后边搭配一个正则匹配模式大小写不敏感的 示例 按照查询时间排序查看前五条慢查询SQL 语句 #命令行按照查询时间排序查看前五条 慢查询SQL 语句
mysqldumpslow -s t -t 5 /var/lib/mysql/xxx-slow.log 2.3 分析查询计划
2.3.1 EXPLAIN命令
explan分析sql执行计划访问类型、记录条数、索引长度等主要关注字段
possible_keys查询可能用到的索引key实际使用的索引key_len实际使用的索引的字节数长度。type访问类型看有没有走索引。按性能从高到低 system一行记录时,快速查询。例如SELECT * FROM dual;const命中主键索引或唯一索引eq_ref对于前一张表的结果连接查询或子查询第二个表查找时命中主键索引或唯一索引ref命中非唯一索引range范围索引。阿里规约SQL 性能优化的目标至少要达到 range 级别要求是 ref 级别如果可以是const最好。index索引树上全表扫描index_merge查询条件多时使用多个索引合并结果。使用了多个索引来满足查询条件然后将结果合并。这通常发生在查询中有多个条件每个条件可以使用不同的索引来访问数据。all全表扫描。性能最差Extra额外信息。看有没有走索引。 using index覆盖索引不回表。using filesort需要额外的排序。排序分为索引排序和filesort排序索引排序一般更快深分页等查询数据量大时filesort更快。using index condition索引下推。MySQL5.6开始支持。联合索引某字段是模糊查询非左模糊或者范围查询、不等于时该字段进行条件判断后后面几个字段可以直接条件判断判断过滤后再回表对不包含在联合索引内的字段条件进行判断。using where没完全走索引需要回表。在MySQL中回表table lookup 或 table access是指在使用非聚簇索引secondary index查询时数据库先通过索引查找到满足条件的记录的主键或行ID然后再根据主键到聚簇索引或数据表中查找完整的记录。 执行计划各个列的作用 id每个SELECT子句或者join操作都会被分配一个唯一的编号编号越小优先级越高id相同的语句可以被认为是一组。id为NULL表示独立的子查询子查询优先级都比主查询高。select_type查询的类型。主查询(primary)、普通查询(simple)、联合查询、子查询(subquery)、derived(from表临时子查询)、union(union后查询)、union result()table表名。显示当前这行的数据是哪个表的。partitions匹配的分区信息。如果表未分区则为NULL。type访问类型根据索引、全表扫描等方法来执行查询的优化策略。all全表扫描ref命中非唯一索引const命中主键/唯一索引、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。possible_keys 可能用到的索引。列出MySQL能够使用哪些索引来查询。 如果该列只有一个possible_keys通常意味着这个查询是高效的。 如果这个列有多个possible_keys并且MySQL只使用了其中一个则需要考虑是否需要在该列上增加一个联合索引。 key实际上使用的索引。如果没有明确的指定KEYMySQL会根据查询条件自动选择最优的索引。key_len实际使用到索引的字节数长度。越短表示越快一般表示索引字段越小越好。ref当使用索引列等值查询时与索引列进行等值匹配的对象信息。常量等值查询const, 表达式/函数使用到时func,关联查询显示关联字段名rows预估的需要读取的记录条数。数值越小越好表示结果集越小查询越高效。filtered某个表经过搜索条件过滤后剩余记录条数的百分比。这个值越小越好说明可通过索引直接返回数据。Extra额外信息。看有没有走索引还是全表扫描了。一般搭配type字段看。Using index(使用到覆盖索引)、Using where(使用了回表在服务器层面即SQL层应用WHERE条件过滤记录而不是在存储引擎层面利用索引进行过滤。)、Using temporary(临时表存储结果集.排序/分组会使用)、Using filesort(排序操作未用索引)、Using join buffer(连接条件未用索引)、Impossible where(where约束语句可能有问题导致没有结果集) 2.3.2 EXPLAIN ANALYZE命令
MySQL 8.0引入了explain analyze命令相比explain它提供的是实际的查询计划而explain提供的是预估查询计划。
explain和explain analyze的区别
explain只生成执行计划不实际执行explain analyze生成执行计划并实际执行sql 示例 人员表联查部门表 EXPLAIN ANALYZE
SELECT *
FROM personnel p LEFT JOIN department d on p.departmentd.id 查询计划结果 - Nested loop left join (cost915.25 rows1980) (actual time0.333..14.500 rows2453 loops1)- Table scan on p (cost222.25 rows1980) (actual time0.283..8.625 rows2453 loops1)- Filter: (p.DEPARTMENT d.ID) (cost0.25 rows1) (actual time0.002..0.002 rows1 loops2453)- Single-row index lookup on d using PRIMARY (IDp.DEPARTMENT) (cost0.25 rows1) (actual time0.002..0.002 rows1 loops2453)结果分析 第一行 Nested loop left join (cost915.25 rows1980) (actual time0.333..14.500 rows2453 loops1) Nested loop left join: 执行的最外层操作表示使用嵌套循环的左连接。 成本估计: (cost915.25 rows1980)预计消耗915.25ms并返回1980行。 实际时间: (actual time0.333..14.500 rows2453 loops1)实际读取第一行平均花费0.333ms返回所有行平均花费14.500ms共循环调用该迭代器1次返回2453行。 第二行 Table scan on p (cost222.25 rows1980) (actual time0.283..8.625 rows2453 loops1) Table scan on p: 对人员表的全表扫描。 成本估计: (cost222.25 rows1980)预计消耗222.25ms并返回1980行。 实际时间: (actual time0.283..8.625 rows2453 loops1)实际读取第一行平均花费0.283ms返回所有行平均花费8.625ms共循环调用该迭代器1次返回2453行。 第三行 Filter: (p.DEPARTMENT d.ID) (cost0.25 rows1) (actual time0.002..0.002 rows1 loops2453) Filter: (p.DEPARTMENT d.ID): 执行对 md_gams_jc_department 表中 p.DEPARTMENT d.ID 条件的过滤操作。 成本估计: (cost0.25 rows1)预计消耗0.25ms并返回1行。 实际时间: (actual time0.002..0.002 rows1 loops2453)实际过滤操作平均花费0.002ms共循环调用该迭代器2453次返回1行。 第四行 Single-row index lookup on d using PRIMARY (IDp.DEPARTMENT) (cost0.25 rows1) (actual time0.002..0.002 rows1 loops2453) Single-row index lookup on d using PRIMARY (IDp.DEPARTMENT): 对部门表使用主键索引进行单行查找其中 IDp.DEPARTMENT。 成本估计: (cost0.25 rows1)预计消耗0.25ms并返回1行。 实际时间: (actual time0.002..0.002 rows1 loops2453)实际查找操作平均花费0.002ms共循环调用该迭代器2453次返回1行。 三、MySQL调优
3.1 基础优化
3.1.1 缓存优化
3.1.1.0 简介
缓冲池优化调整缓冲池大小innodb_buffer_pool_size。引入内存结构数据库例如Redis。
3.1.1.1 缓冲池优化
缓冲池MySQL的缓冲池被分为多个不同的缓存池其中包括
查询缓存用来缓存查询结果。InnoDB缓存池用来缓存热点表和索引数据页。MyISAM缓存池用来缓存表数据块。
缓冲池是主内存中的一部分空间用来缓存已使用的表和索引数据。缓冲池使得经常被使用的数据能够直接在内存中获得从而提高速度。
缓冲池的淘汰策略
LRU算法。MySQL的缓冲池默认使用的是LRU最近最少使用淘汰策略它会优先缓存最近使用的数据。当缓冲池的空间不足时MySQL会将最不常用的数据从缓冲池中替换出去以腾出空间缓存新的数据。 lru算法底层原理 底层是双向链表因为经常要移动元素链表首部是最常使用元素尾部是最少使用元素。 每次刚访问的数据会移动到链表首部刚添加的数据也会添加到链表首部。超出maxmemory会淘汰链表尾部元素它也最长时间没有被使用的数据。 缓冲池相关参数
MySQL的缓存设置包括多个参数其中比较常见的缓存参数包括以下几个 MyISAM缓冲池大小key_buffer_size该参数用来设置MyISAM索引的缓存大小。如果应用程序中涉及到大量的索引查询可以适当提高该值。一般来说key_buffer_size占用总内存的1/4到1/3比较合适。 InnoDB缓冲池大小innodb_buffer_pool_size该参数用来设置InnoDB缓冲池的大小。InnoDB存储引擎使用缓冲池来缓存数据和索引文件。如果InnoDB表的读写频次较高建议将该值设置为物理内存的70%到80%。 排序缓冲区大小sort_buffer_size该参数用来设置排序缓冲区大小。如果查询中涉及到ORDER BY或GROUP BY操作可以适当提高该值。一般来说sort_buffer_size占用总内存的1/4到1/3比较合适。 读取缓冲区大小read_buffer_size和read_rnd_buffer_size这两个参数是用来设置读取缓冲区大小的默认值为128 KB。如果应用程序中经常进行大文件的读取操作可以适当提高这两个参数。 binlog大小binlog_cache_size该参数是用来设置二进制日志的缓存大小。如果应用程序中需要持久化一些数据可以开启二进制日志并适当调整该参数。
参数配置方法
1.查看当前缓冲池参数
show VARIABLES like key_buffer_size; 2.修改缓冲池参数
方法一在运行中的MySQL实例中临时设置这个值这不会持久保存重启后会失效 SET GLOBAL key_buffer_size 67108864; -- 64MB方法二 在MySQL配置文件通常是 my.cnf 或 my.ini中进行更改然后重启MySQL服务使更改生效。例如 [mysqld]
key_buffer_size 64M
3.1.1.2 Redis优化
Redis是一个基于内存的NoSQL数据库MySQL是一个基于磁盘的关系型数据库。
我们知道内存的读写速度是远高于磁盘的所以对于一些多读少写的热点数据搭配Redis存储数据可以极大地提高数据的访问速度。 Redis特点 数据库Redis是一款基于键值对的、线程安全的NoSQL数据库内存读写性能它在内存中读写性能非常高每秒可以处理超过百万次的读写操作。服务端线程安全客户端线程不安全Redis服务端是线程安全的永远只有主线程一个线程进行读写不需要任何的同步机制。虽然Redis6.0增加了多线程的模型但多线程目的只是为了处理网络的IO事件读写指令的执行依然由主线程自己处理。Redis客户端层面线程不安全要引入原子指令例如INCR是给数值原子性加1、分布式锁、lua脚本保证Redis的原子操作。 Redis读写为什么不采用多线程 CPU不是瓶颈Redis在内存中读写性能非常高CPU不是Redis的瓶颈无需使用多线程。担心加锁影响性能多线程情况下想实现线程安全必须加锁加锁将极大地影响性能。 为什么单线程还读写性能这么高 基于内存Redis是基于内存的内存的读写速度非常快上下文切换单线程避免了不必要的上下文切换和竞争条件IO多路复用底层采用NIO非阻塞IONIO采用IO多路复用技术一个线程通过多路复用器处理多个连接。IO多路复用技术选用epoll调用模型红黑树存所有事件链表存就绪事件。epoll_wait函数链表通知应用程序读写操作。 Redis的瓶颈 内存因为读写在内存中进行内存大小会影响Redis性能。可以通过加内存、读写分离优化性能。网络带宽网络 IO是Redis最大瓶颈也就是客户端和服务端之间的网络传输延迟。Redis6.0引入了网络IO多线程模型提高了性能瓶颈。 功能键过期、事务、lua脚本基于C语言性能快、持久化机制。 事务 实现方式MULTI开启事务将命令都放进队列里EXEC执行事务DISCARD取消事务清空队列。不支持回滚在语法正确的情况下Redis事务一定会执行成功。只有语法错误时才会导致事务失败而语法问题应该在开发时就避免所以为了提高性能Redis事务不支持回滚。事务是一个原子操作要么全部执行要么全不执行。不完全满足ACID特性Redis只满足隔离性和持久性不满足原子性和一致性。 原子性事务的所有操作要么全部成功要么全部失败。Redis不满足原子性单个 Redis 命令的执行是原子性的但事务失败后无法回滚。一致性事务前后数据库的约束没有被破坏保持前后一致。Redis连约束这个概念都没有。隔离性操作同一资源的并发事务之间相互隔离不会互相干扰。Redis满足隔离性因为redis server是单线程的串行化执行事务肯定是满足隔离性的。持久性事务的结果最终一定会持久化到数据库宕机等故障也无法影响。Redis在开启aof并指定立刻持久化命令时满足持久性。rdb模式会丢失部分数据不满足持久性。 数据类型 string、hash、 list、set集合、zset有序集合 应用场景缓存热点且不经常修改的数据、计数器、限时业务、分布式锁set nx、队列等。 持久化机制 数据备份机制RDB默认数据每隔一段时间写进磁盘rdb文件故障后从文件读。可以在redis.conf配置多少秒内多少key修改时自动bgsave。占CPU和内存但恢复快不能恢复完整数据。save命令是主进程立即执行一次RDB其他所有命令进程阻塞。bgsave是子进程fork主进程阻塞并拷贝一份主进程的页表虚拟内存到物理内存的映射关系然后子进程写数据到rdb文件主进程继续处理用户请求追加文件机制AOF命令日志按指定频率默认立刻在redis.conf配置为缓存一秒写进磁盘aof文件可以按条件redis.conf配置比上次重写aof文件超过多少百分比时自动重写、aof文件超过多大自动重写自动重写aof文件中的命令多次更新同一数据只有最近一次更新有效故障后从文件读命令恢复数据。不占CPU和内存占IO能恢复完整或故障1s前的数据但恢复慢。 3.1.2 硬件优化
在很多情景下我们已经对数据库的索引、参数等数据进行了优化但当数据库并发量高、数据量大时就需要考虑优化服务器配置、分库分表等方案直观地提高数据库的性能。
优化方案
服务器加内存条升级SSD固态硬盘把磁盘I/O分散在多个设备配置多处理器。
3.1.3 参数优化
除了以上说的缓冲池参数外我们还可以通过其他参数对MySQL进行优化。
优化方案
关闭不必要的服务和日志调优结束后关闭慢查询日志 # 临时关闭慢查询日志如果想永久关闭需要修改my.ini或my.cnf配置文件
SET GLOBAL slow_query_log OFF;调整最大连接数max_connections 修改方法同上。MySQL5.5及之后版本默认最大连接数是151可以根据实际场景压测得出合适的最大连接数。 MySQL5.5 5.7默认的最大连接数都是 151上限为100000MySQL5.0 版本默认的最大连接数为 100上限为 16384MySQL8.0 版本: 默认的最大连接数是 151调整线程池缓存线程数thread_cache_size缓存空闲线程有连接时直接分配该线程处理连接调整缓冲池大小innodb_buffer_pool_size 。上面3.1.1已经详细说过了此处不再赘述。
3.1.4 定期清理垃圾
对于不再使用的表、数据、日志、缓存等应该及时清理避免占用过多的MySQL资源从而提高MySQL的性能。
3.1.4.1.清理不再使用的表
# 删除这些表
DROP TABLE table_name;
# 保留表结构但删除所有数据
delete from table_name;
3.1.4.2.清理过期数据
一些场景下某个时期之前的数据都不再需要可以清理这些数据
DELETE FROM table_name WHERE createTime NOW() - INTERVAL 30 DAY;
创建一个MySQL事件来定期清理过期数据
CREATE EVENT clean_up_event
ON SCHEDULE EVERY 1 DAY
DODELETE FROM table_name WHERE created_at NOW() - INTERVAL 30 DAY;3.1.4.3.清理日志
# 清理2023年之前的日志
PURGE BINARY LOGS BEFORE 2023-01-01 00:00:00;
3.1.4.4.清理缓存池
RESET QUERY CACHE; 或者修改配置文件禁用查询缓存以避免潜在的性能问题
[mysqld]
query_cache_type 0
query_cache_size 0
3.1.4.5.优化表OPTIMIZE TABLE
在 MySQL 数据库中OPTIMIZE TABLE 是一个重要的命令用于优化表的性能和空间利用。通过重新组织表的存储结构去除碎片、重建索引OPTIMIZE TABLE 可以帮助提高查询性能、减少存储空间占用以及减少数据碎片。
OPTIMIZE TABLE命令
OPTIMIZE TABLE table_name;
优化原理
删除delete语句留下来的垃圾碎片。使用delete语句删除数据时delete语句只会将记录的位置或者数据页标记为可复用但是数据库磁盘文件的大小不会改变即表空间不会被回收此时使用该命令可以释放空间压缩数据文件。
底层原理
执行OPTIMIZE TABLE命令后MySQL会进行以下几个步骤
创建临时表MySQL 首先会创建一个与原表结构相同的临时表。原表数据复制到临时表将原表中的数据复制到临时表中。临时表去碎片在数据复制的过程中MySQL 会对数据进行整理和重组去除碎片提高数据的连续性。删旧表留新表当数据复制完成并且表被优化后MySQL 会删除原表然后将临时表重命名为原表的名称。
3.1.4.6.分析表ANALYZE TABLE
MySQL 的Optimizer优化元件在优化SQL语句时首先需要收集一些相关信息其中就包括表的cardinality散列程度它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度那么索引就基本失效了。
ANALYZE TABLE命令
ANALYZE TABLE table_name;
对不同存储引擎的效果
InnoDB对 InnoDB 表执行 ANALYZE TABLE 会重新计算表和索引的统计信息并更新优化器统计信息。MyISAM对 MyISAM 表执行 ANALYZE TABLE 会分析表的关键字分布并更新索引统计信息。其他存储引擎对其他存储引擎如 MEMORY 或 ARCHIVE效果类似即更新表和索引的统计信息。 3.1.4.7.计划任务清理数据、日志、优化表
对于以上的清理垃圾方案可以写一个定时任务定期统一清理垃圾数据、优化表的存储空间和索引。
方案一创建cron作业
1.编辑crontab
使用crontab命令编辑当前用户的cron作业列表。对于系统级别的作业可以使用sudo运行crontab。
crontab -e或者为特定用户编辑cron作业 sudo crontab -u username -e2.编写cron作业
在打开的编辑器中添加新的cron作业每行代表一个作业执行指定路径下的脚本
0 2 * * * /path/to/cleanup_script.shcron表达式格式如下 * * * * * command-to-be-executed
- - - - -
| | | | |
| | | | ----- Day of the week (0 - 7) (Sunday0 or 7)
| | | ------- Month (1 - 12)
| | --------- Day of the month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59) 3.示例清理脚本
/path/to/cleanup_script.sh
#!/bin/bash# MySQL credentials
USERyour_username
PASSWORDyour_password
DATABASEyour_database# 清理过期数据
mysql -u $USER -p$PASSWORD -e DELETE FROM table_name WHERE created_at NOW() - INTERVAL 30 DAY; $DATABASE# 清理二进制日志
mysql -u $USER -p$PASSWORD -e PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;# 优化表
mysql -u $USER -p$PASSWORD -e OPTIMIZE TABLE table_name; $DATABASE方案二使用Spring定时任务 spring根据定时任务的特征将定时任务的开发简化到了极致。怎么说呢要做定时任务总要告诉容器有这功能吧然后定时执行什么任务直接告诉对应的bean什么时间执行就行了就这么简单一起来看怎么做
步骤①开启定时任务功能在引导类上开启定时任务功能的开关使用注解EnableScheduling
SpringBootApplication
//开启定时任务功能
EnableScheduling
public class Springboot22TaskApplication {public static void main(String[] args) {SpringApplication.run(Springboot22TaskApplication.class, args);}
}步骤②在task包下定义Bean在对应要定时执行的操作上方使用注解Scheduled定义执行的时间执行时间的描述方式还是cron表达式
Component
public class MyBean {Scheduled(cron 0/1 * * * * ?)public void print(){// 具体清理垃圾的逻辑}
}如何想对定时任务进行详细配置可以通过配置文件进行
spring:task:scheduling:pool:size: 1 #任务调度线程池大小 默认 1thread-name-prefix: ssm_ #调度线程名称前缀 默认 scheduling- shutdown:await-termination: false #线程池关闭时等待所有任务完成await-termination-period: 10s #调度线程关闭前最大等待时间确保最后一定关闭总结 spring task需要使用注解EnableScheduling开启定时任务功能 为定时执行的的任务设置执行周期描述方式cron表达式 3.1.5 使用合适的存储引擎
3.1.5.1 各存储引擎使用场景
InnoDB适合并发写入的场景因为行级锁、B树叶存记录。MyISAM适合读取频繁写入较少的场景因为表级锁、B树叶存地址
3.1.5.2 回顾各存储引擎和B树 详细参考 MySQL高级篇——存储引擎和索引_mysql存储索引的表-CSDN博客 InnoDB支持外键和事务行锁适合高并发缓存索引和数据内存要求高因为要缓存索引和记录适合存大数据量增删改性能更优行级锁高并发耗费磁盘因为有多个非聚簇索引索引可能比记录空间还大。
B每个元素的结构 InnoDB叶节点存数据各列的值 因为聚簇索引树数据页存的元素第一个值是主键而且聚簇索引树是默认创建的所以如果我没有另外创建索引时或者创建了其他索引但没发生覆盖索引时用主键查询是最快的例如select * from student where idxx快过select * from student where agexx。
当然聚簇索引并不一定是最快的例如给age字段创建了索引那么虽然select * from
student where idxx快过select * from student where agexx因为要回表聚簇索引树查所有字段但是select age from student where agexx快过select age from student where idxx因为发生了覆盖索引直接在非聚簇索引树就查到了age没必要再回表聚簇索引树查其他字段信息。
MyISAM不支持外键和事务表锁不适合高并发缓存索引和数据地址内存要求低因为不用缓存记录查询性能更优因为查询时InnoDB要维护MVCC一致而且多缓存了记录节省磁盘因为磁盘不存完整记录。 对比 InnoDB MyISAM 特点 支持外键和事务 不支持外键和事务 行表锁 行锁操作时只锁某一行不对其它行有影响 适合高并发的操作 表锁即使操作一条记录也会锁住整个表不适合高并发的操作 缓存 缓存索引和数据对内存要求较高而且内存大小对性能有决定性的影响 只缓存索引不缓存真实数据 关注点 事务并发写、事务、更大资源 性能节省资源、消耗少、简单业务、查询快 默认使用 5.5及其之后 5.5之前
3.1.6 读写分离
读写分离读写分离能有效提高查询性能。读写分离基于MySQL的主从同步一台主库负责写多台从库负责读每次主库发生写操作后通过binlog和relay log将修改操作同步到从库从而保持主库和从库的数据一致性。
主从同步一台或多台MySQL数据库(slave即从库)从另一台NySQL数据库master即主库进行日志的复制然后再解析日志并应用到自身最终实现从库的数据和主库的数据保持一致。MySQL主从复制是NySQL数据库自带功能无需借助第三方工具。
主从同步实现步骤
主服务器把数据更改记录到二进制日志binlog记录改不记录读用于数据复制和数据恢复中从服务器异步近似实时地把主服务器的二进制日志复制到自己的中继日志relay log中从服务器重做中继日志中的操作把更改应用到自己的数据库上以达到数据的最终一致性。
主从同步的延时问题
延时问题是主服务器压力大导致的复制延时问题。解决方案 网络带宽优化如果是网络延时问题可以通过增大服务器的带宽解决。硬件调优如果是因为硬件配置差导致同步延时可以通过提升服务器配置解决。参数调优 [mysqld]
# 多线程复制MySQL 5.7及以上版本设置并行线程数
slave_parallel_workers 4
# 开启半同步复制MySQL 5.5及以上版本
rpl_semi_sync_master_enabled 1
rpl_semi_sync_slave_enabled 1
# 同步延时时间1秒超时。如果频繁写则适当缩小如果频繁读少写则适当增大以降低服务器压力
rpl_semi_sync_master_timeout 1000 缩小事务粒度一些代码中直接在整个Controller或者整个Service方法上加个Transcational而整个业务中其实只有一小段需要保持事务这样是很影响性能的因为事务不是那个MySQL的连接会一直被占用对数据库的压力很大。解决方案减小代码中事务的粒度例如将大事务拆解成小事务将其中的一些查询操作脱离出去只在写操作的方法中加事务
复制原理
主库二进制日志转储线程负责将二进制日志发给从库。强制从主库读取数据时/*master*/ SELECT * FROM user会给二进制日志加锁 读完解锁。从库I/O 线程负责连接主库并向主库发送请求和复制二进制日志到中继日志。从库SQL 线程负责读取并执行中继日志中的更新语句实现主从同步。
主从库数量
每个 Master 可以有多个 Slave每个 Slave 只能有一个唯一的服务器ID只有一个 Master。 3.1.7 分库分表
概念
只分表单表数据量大读写出现瓶颈这个表所在的库还可以支撑未来几年的增长。只分库整个数据库读写出现性能瓶颈将整个库拆开。分库分表单表数据量大所在库也出现性能瓶颈就要既分库又分表。垂直拆分把字段分开。例如spu表的pic字段特别长建议把这个pic字段拆到另一个表同库或不同库。水平拆分把记录分开。例如表数据量到达百万我们拆成四张20万的表。 拆分原则
数据量增长情况数据表类型优化核心思想数据量为千万级是一个相对稳定的数据量状态表能不拆就不拆读需求水平扩展数据量为千万级可能达到亿级或者更高流水表业务拆分面向分布式存储设计数据量为千万级可能达到亿级或者更高流水表设计数据统计需求存储的分布式扩展数据量为千万级不应该有这么多的数据配置表小而简避免大一统
分库分表步骤 MySQL调优数据量能稳定在千万级近几年不会到达亿级其实是不用着急拆的先尝试MySQL调优优化读写性能。 目标评估评估拆几个库、表举例: 当前20亿5年后评估为100亿。分几个表? 分几个库?解答:一个合理的答案1024个表16个库按1024个表算拆分完单表200万5年后为1000万.1024个表*200w≈100亿 表拆分 业务层拆分混合业务拆分为独立业务、冷热分离 数据层拆分 按日期拆分这种使用方式比较普遍尤其是按照日期维度的拆分其实在程序层面的改动很小但是扩展性方面的收益很大。例如 日维度拆分如log_20191021。月维度拆分如log_201910。当需要查询某天的日志时就可以直接根据所在月份直接得出在哪个日志表查。年维度拆分如log_2019 按主键范围拆分例如【1,200w】主键在一个表【200w400w】主键在一个表。优点是单表数据量可控。缺点是流量无法分摊写操作集中在最后面的表。 中间表映射表随意拆分引入中间表记录查询的字段值以及它对应的数据在哪个表里。优点是灵活。确定是引入中间表让流程变复杂。 hash切分sharding_key%N。优点是数据分片均匀流量分摊。缺点是扩容需要迁移数据跨节点查询问题。例如日志表拆分成logs_0、logs_1、logs_2每次读写日志时将用户的id转成md5哈希值然后%3就可以得出具体要在哪个日志表查询。 按分区拆分hash,range等方式。不建议因为数据其实难以实现水平扩展。 sharding_key分表字段选择尽量选择查询频率最高的字段然后根据表拆分方式选择字段。 代码改造修改代码里的查询、更新语句以便让其适应分库分表后的情况。 数据迁移最简单的就是停机迁移复杂点的就是不停机迁移要考虑增量同步和全量同步的问题。 全量同步老库到新库的数据迁移要控制好迁移效率解决增量数据的一致性。 定时任务定时任务查老库写新库中间件使用中间件迁移数据 增量同步老库迁移到新库期间新增删改命令的落库不能出错 同步双写同步写新库和老库异步双写推荐 写老库监听binlog异步同步到新库中间件同步工具通过一定的规则将数据同步到目标库表 数据一致性校验和补偿假设采用异步双写方案在迁移完成后逐条对比新老库数据一致则跳过不一致则补偿 新库存在老库不存在新库删除数据新库不存在老库存在新库插入数据新库存在、老库存在比较所有字段不一致则将新库更新为老库数据 灰度切读灰度发布指黑旧版本与白新版本之间让一些用户继续用旧版本一些用户开始用新版本如果用户对新版本没什么意见就逐步把所有用户迁移到新版本实现平滑过渡发布。原则 有问题及时切回老库灰度放量先慢后快每次放量观察一段时间支持灵活的规则门店维度灰度、百 (万)分比灰度 停老用新下线老库用新库读写。
3.2 表设计优化
3.2.1 混合业务分表、冷热数据分表
混合业务分表
根据业务逻辑将不同的业务数据分开存储在不同的表中。每个业务模块的数据单独存储减少了单表的大小和查询的复杂度。
示例将一个大的日志表拆分成交易日志表、操作日志表、登录日志表等等。这些需要在项目设计期间就根据预估的数据量进行拆分。
冷热数据分表
将频繁访问的“热数据”和不常访问的“冷数据”分开存储。这种策略有助于提高热数据的查询性能并且在存储和备份方面更加灵活。
示例1把一个大的任务表分离成任务表和历史任务表任务表里任务完成后移动到历史任务表。任务表是热数据历史任务表是冷数据提高查询性能。示例2或者将日志表分成日志表和历史日志表使用定时任务将三个月前的日志都迁移到历史日志表用户查看操作记录时默认只显示近三个月的数据从而提高性能。
3.2.2 联合查询改为中间关系表
对于复杂的数据库设计使用关系表是一种常见的方法特别是在多对多的关系中例如学生表和课程表、商品表和商品属性表、用户和角色表、作者和书籍表、部门和人员表。
示例
部门表 (departments)
department_id部门ID主键department_name部门名称
人员表 (employees)
employee_id员工ID主键employee_name员工名称
部门员工关联表 (department_employees)
id主键IDdepartment_id部门ID外键指向 departments 表employee_id员工ID外键指向 employees 表
关系表的优点
多对多关系通过中间表可以管理多对多的映射关系。支持扩展关系表可以添加额外的字段来描述关系的属性。提高查询性能减少表连接的次数提高查询性能。 3.2.3 遵循三个范式
数据库三范式
每个属性不可再分表必须有且只有一个主键非主键列必须直接依赖于主键
3.2.4 字段建议非空约束
①可能查询出现空指针问题
②导致聚合函数不准确因为它会忽略null
③不能用“”判断只能用is null判断
④null和其他值运算只能是null可能让你不小心把它当成0
⑤null值比空字符更占用空间空值长度是0null长度是1bit
⑥不覆盖索引情况下is not null无法用索引
3.2.5 反范式使用冗余字段
反范式为提高查询效率可添加不常更新的字段为冗余字段。
反范式化是数据库设计的一种策略通过在设计中引入冗余数据来提高查询性能简化查询操作或满足其他业务需求。
使用场景
将多读少写的字段增加为冗余字段从而不再需要每次都连表查询这个字段。需要注意每次数据更新时必须同步这个冗余字段。
示例
部门表 (departments)
department_id部门ID主键department_name部门名称
人员表 (employees)
employee_id员工ID主键employee_name员工名称
部门员工关联表 (department_employees)
id主键IDdepartment_id部门ID指向 departments 表employee_id员工ID指向 employees 表 注意 增加冗余字段后当这个冗余字段对应的数据改动后必须同步更改这个冗余字段。 例如成绩表除了有student_id字段外增加冗余字段student_name因为学生名基本不会变化但在修改姓名的接口里修改学生名后要同步修改成绩表的student_name字段。 3.2.6 数据类型优化
整数类型
考虑好数值范围前期可以使用int保证稳定性。非负数类型要用UNSIGNED同样字节数存储的数值范围更大。主键一般使用bigint,布尔类型tinint
能整数就不要用文本类型
跟文本类型数据相比大整数往往占用更少的存储空间。
避免使用TEXT、BLOB数据类
这两个大数据类型排序时不能使用临时内存表只能使用磁盘临时表效率很差建议别用或分表到单独扩展表里。LongBlob类型能存储4G文件
避免使用枚举类型
因为枚举类型排序很慢。
使用TIMESTAMP存储时间
TIMESTAMP使用4字节DATETIME使用8个字节同时TIMESTAMP具有自动赋值以及自动更新的特性。 缺点是只能存到2038年MySQL5.6.4版本可以参数配置自动修改它为BIGINT类型。
DECIMAL存浮点数
Decimal类型为精准浮点数在计算时不会丢失精度尤其是财务相关的金融类数据。占用空间由定义的宽度决定每4个字节可以存储9位数字并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。
3.3 索引优化
3.3.0 数据准备
3.3.0.1 创建学生表并生成50w条数据 本文使用MySQL版本 5.7.41 学员表插 50万 条班级表 插 1万 条。
步骤0建库
CREATE DATABASE test;
USE test;步骤1建表
CREATE TABLE class (id INT(11) NOT NULL AUTO_INCREMENT,className VARCHAR(30) DEFAULT NULL,address VARCHAR(40) DEFAULT NULL,monitor INT NULL ,PRIMARY KEY (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;CREATE TABLE student (id INT(11) NOT NULL AUTO_INCREMENT,stuno INT NOT NULL ,name VARCHAR(20) DEFAULT NULL,age INT(3) DEFAULT NULL,classId INT(11) DEFAULT NULL,PRIMARY KEY (id)#CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES t_class (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;步骤2设置参数
命令开启允许创建函数设置
set global log_bin_trust_function_creators1; # 不加global只是当前窗口有效。步骤3创建函数
保证每条数据都不同。
DELIMITER //CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
DETERMINISTIC
NO SQL
BEGINDECLARE chars_str VARCHAR(100) DEFAULT abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;DECLARE return_str VARCHAR(255) DEFAULT ;DECLARE i INT DEFAULT 0;WHILE i n DOSET return_str CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SET i i 1;END WHILE;RETURN return_str;
END //DELIMITER ;随机产生班级编号
DELIMITER //CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
DETERMINISTIC
NO SQL
BEGINDECLARE i INT DEFAULT 0;SET i FLOOR(from_num RAND()*(to_num - from_num1)) ;RETURN i;
END //DELIMITER ;步骤4创建存储过程
#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit 0; #设置手动提交事务
REPEAT #循环
SET i i 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((STARTi),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_stu;创建往class表中插入数据的存储过程
#执行存储过程往class表添加随机数据
DELIMITER //
CREATE PROCEDURE insert_class( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit 0;
REPEAT
SET i i 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_class;步骤5调用存储过程
class
#执行存储过程往class表添加1万条数据
CALL insert_class(10000);stu
#执行存储过程往stu表添加50万条数据
CALL insert_stu(100000,500000);3.3.0.2 创建删除所有索引的存储过程
创建删除索引的存储过程
DELIMITER //
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT ;DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schemadbname AND table_nametablename AND
seq_in_index1 AND index_name PRIMARY ;
#每个游标必须使用不同的declare continue handler for not found set done1来控制游标的结束DECLARE CONTINUE HANDLER FOR NOT FOUND set done2 ;
#若没有数据返回,程序继续,并将变量done设为2OPEN _cur;FETCH _cur INTO _index;WHILE _index DOSET str CONCAT(drop index , _index , on , tablename );PREPARE sql_str FROM str ;EXECUTE sql_str;DEALLOCATE PREPARE sql_str;SET _index;FETCH _cur INTO _index;END WHILE;CLOSE _cur;
END //
DELIMITER ;执行存储过程
CALL proc_drop_index(数据库名,表名);练习删除student表的所有索引 CALL proc_drop_index(test,student);验证 创建索引 CREATE INDEX idx_stuno_age on student(stuno,age); 查看索引 SHOW INDEX FROM student; 删除所有索引 CALL proc_drop_index(test,student);再次查看索引 SHOW INDEX FROM student; 3.3.1 考虑索引失效的11个场景 详细请参考 MySQL高级篇——索引失效的11种情况_vincewm的博客-CSDN博客 3.3.1.1.尽量全值匹配
全值匹配指的是查询条件完全匹配索引中的所有列。例如如果一个索引包含列 A 和 B那么查询条件应该包括这两个列才能完全匹配这个索引。
查询age and classId and name时(age,classId,name)索引比(age,classId)快。 验证 创建联合索引 CREATE INDEX idx_stuno_age on student(stuno,age); 全值匹配 EXPLAIN SELECT * FROM student WHERE stuno1 and age2; 可以看到type是ref即命中非唯一索引。 3.3.1.2.考虑最左前缀
联合索引把频繁查询的列放左。例如索引a,b,c只能查(a,b,c),(a,b),(a)。 验证 保持创建上面idx_stuno_age联合索引 没符合最佳左前缀原则 EXPLAIN SELECT * FROM student WHERE age2;可以看到type是all即全表扫描。 3.3.1.3.主键尽量有序
如果主键不有序需要查找目标位置再插入并且如果目标位置所在数据页满了就必须得分裂页造成性能损耗。可以选择自增策略或MySQL8.0有序UUID策略。 3.3.1.4.计算、函数导致索引失效
计算
当对索引列进行计算时MySQL 不能使用索引。因为索引存储的是列的原始值而计算改变了这些值。 验证 EXPLAIN SELECT * FROM student WHERE stuno12 可以看到type是all全表扫描没有走索引。 如果是等号右边的计算则索引不受影响依然生效因为这个计算预先就能计算得出 EXPLAIN SELECT * FROM student WHERE stuno21可以看出type是ref命中非唯一索引 函数
在索引列上使用函数会导致索引失效因为 MySQL 不能预先计算索引列的函数值并存储在索引中。 验证 EXPLAIN SELECT * FROM student WHERE abs(stuno)3 可以看到type是all全表扫描没有走索引。 如果是等号右边的函数则索引不受影响依然生效因为这个计算预先就能计算得出 EXPLAIN SELECT * FROM student WHERE stunoabs(2.3)可以看出type是ref命中非唯一索引 3.3.1.5.类型转换导致索引失效
隐式转换导致索引失效
当查询条件中的数据类型与索引列的数据类型不匹配时MySQL 会进行隐式类型转换。这种类型转换会导致 MySQL 无法利用索引从而导致全表扫描影响查询性能。
使用不同字符集时也会触发类型隐式转换导致索引失效。
注意MySQL官方文档有提到 In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers. 也就是说对比的时候MySQL 自身会有一套基本的规则来对应不同类型数据的比较而字符串与数字的对比中字符串会被转换成双精度浮点型数字之后再进行对比。
例如name123而不是name123则会导致索引失效。
而stuno12abc3而不是stuno123则并不会导致索引失效。 验证 保持创建索引idx_stuno_age、idx_name EXPLAIN SELECT * FROM student WHERE name 123; 可以看到type是ref这是走索引的。 而下面触发类型隐式转换type是all全表扫描没有走索引虽然字符串可以转数字但数字不能转字符串会导致索引失效 EXPLAIN SELECT * FROM student WHERE name 123; 显式转换导致索引失效
显式类型转换是指在查询条件中使用 CAST 或 CONVERT 函数将数据类型转换为匹配索引列的数据类型。这种方式同样会导致索引失效。 验证 给name字段创建索引 CREATE INDEX idx_name on student(name);正常查询name会走索引type是ref命中非唯一索引 EXPLAIN SELECT * FROM student WHERE name 123;将name类型转换成chartype是all全表扫描 3.3.1.6.没索引下推时范围条件右边的列索引失效
当查询条件使用了范围条件例如 、、、、BETWEEN时会导致索引右边的列失效因为范围查询会阻止 MySQL 使用复合索引的后续列。
例如a,b,c联合索引查询条件a,b,c如果b使用了范围查询那么b右边的c索引失效。所以建议把需要范围查询的字段放在最后。
索引下推
索引下推(ICPIndex Condition Pushdown)是MySQL 5.6中新特性是一种在存储引擎层使用索引过滤数据的一种优化方式。它可以使范围查询右侧索引失效的列依然可以在索引树上过滤而不需要回表。
如果开启了索引下推范围条件右边的列实际上是索引失效的但是可以直接在这颗联合索引树上直接过滤右边的这些字段。
例如索引(name,age)查询name like z% and age and addressz%是模糊查询实际上也是范围查询。使用索引下推时在联合索引树查询时不止查name还会判断后面的age。而如果关闭了索引下推联合索引里范围查询后面的字段age不能在联合索引树里直接条件判断必须回表到主键索引树后以之前过滤结果id查找到对应数据再过滤age列。 注意这里举例没有使用%z因为左模糊查询会使整个索引失效也就不会用到索引下推了。 验证 继续使用idx_stuno_age索引 关闭索引下推范围查询右侧的列索引失效 SET optimizer_switchindex_condition_pushdownoff;
EXPLAIN SELECT * FROM student WHERE stuno 123 and stuno129 and age2;可以看到type是range即范围查询extra是using where即用到了回表age字段是根据name过滤结果的id回表到主键索引数过滤的 打开索引下推范围查询右侧的列依然在索引树过滤 SET optimizer_switchindex_condition_pushdownon;
EXPLAIN SELECT * FROM student WHERE stuno 123 and stuno129 and age2;对age字段使用范围查询发现type是range即命中范围查询extra是using index condition即使用了索引下推。因为age在联合索引里所以直接在联合索引所在的非聚簇索引树中即可完成过滤 age2而不需要回表 tip范围查询过滤量过小时查询优化器连范围索引也不用 对stuno使用范围查询stuno123发现type是all即全表扫描。因为stuno123的记录有50w条索引直接全表扫描。 EXPLAIN SELECT * FROM student WHERE stuno 123 and stuno129 and age2;3.3.1.7.没覆盖索引时“不等于”导致索引失效
因为“不等于”不能精准匹配全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时联合索引数据量小加载到内存所需空间比聚簇索引树小且不需要回表因为直接在非聚簇索引树全表扫描比在聚簇索引树全表扫描要快索引效率优于全表扫描聚簇索引树。
覆盖索引一个索引包含了满足查询结果的数据就叫做覆盖索引不需要回表等操作。 聚簇索引和非聚簇索引 MySQL高级篇——存储引擎和索引_mysql存储索引的表-CSDN博客 验证 没覆盖索引不等于导致索引失效 EXPLAIN SELECT * FROM student WHERE stuno 3;type是all全表扫描 有覆盖索引即使不等于索引依然生效因为只查这两个字段直接在联合索引树上全表扫描肯定比在主键索引树上全表扫描快。 EXPLAIN SELECT stuno FROM student WHERE stuno 3; type是range即范围查询extra是using index即用到了覆盖索引extra又是using where即因为不等于导致索引失效所以在联合索引树上进行了全表扫描 覆盖索引走索引 EXPLAIN SELECT stuno FROM student WHERE stuno 3;type是ref命中非唯一索引extra是using index即使用了覆盖索引 3.3.1.8.没覆盖索引时左模糊查询导致索引失效 因为“左模糊查询”不能精准匹配全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时联合索引数据量小加载到内存所需空间比聚簇索引树小且不需要回表因为直接在非聚簇索引树全表扫描比在聚簇索引树全表扫描要快索引效率优于全表扫描聚簇索引树。
覆盖索引一个索引包含了满足查询结果的数据就叫做覆盖索引不需要回表等操作。
例如LIKE %abc。因为字符串开头都不能精准匹配。 验证 跟上面范围查询导致索引失效同理此处不再赘述。 3.3.1.9.没覆盖索引时is not null、not like无法使用索引
因为“is not null、not like”不能精准匹配全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时联合索引数据量小加载到内存所需空间比聚簇索引树小且不需要回表因为直接在非聚簇索引树全表扫描比在聚簇索引树全表扫描要快索引效率优于全表扫描聚簇索引树。
覆盖索引一个索引包含了满足查询结果的数据就叫做覆盖索引不需要回表等操作。 验证 跟上面范围查询导致索引失效同理此处不再赘述。 3.3.1.10.“OR”前后存在非索引列或不同索引列导致索引失效
or前后是不同索引列时整个语句的索引失效。例如ax or bx
or前后是同一索引列时命中范围索引。例如ax or axx
or前后是不同或相同的非索引列时索引失效。例如联合索引a,b,查询ax and (cx or cxx) 验证 清除并重新创建索引 CALL proc_drop_index(test,student);
CREATE INDEX idx_stuno_age_classid on student(stuno,age,classid); or前后是不同列时索引就一定会失效 EXPLAIN SELECT * FROM student WHERE stuno 3 and age2 or classid3可以看到type是all全表扫描即使stuno和age都在联合索引树的前两级 or前后是同一列时命中范围索引 EXPLAIN SELECT * FROM student WHERE stuno 3 or stuno2 and age3;type是rangeextra是索引下推。索引下推就是把stuno3和stuno2两个条件其中一个条件在联合索引树上走索引判断另一个条件在此树的过滤结果基础上再过滤而不需要在MySQL服务器上再回表过滤。 or前后是同一列时命中范围索引它后面的列依然在联合索引树上判断 EXPLAIN SELECT * FROM student WHERE stuno 3 or stuno2 and age3; key_len是9说明age也继续在联合索引树上走索引 or前后是非索引列索引失效 EXPLAIN SELECT * FROM student WHERE name 3 or name3;可以看到全表扫描 3.3.1.11.不同字符集导致索引失败
建议utf8mb4不同的字符集进行比较前需要进行 转换 会造成索引失效。 例如创建表时给不同字段设置不同字符集再判断这些字段时会导致索引失效 例如下面表给name_utf8和name_latin1创建联合索引再where name_utf8name_latin1时会导致索引失效 CREATE TABLE student2 (id INT(11) NOT NULL AUTO_INCREMENT,name_utf8 VARCHAR(20) CHARACTER SET utf8 DEFAULT NULL,name_latin1 VARCHAR(20) CHARACTER SET latin1 DEFAULT NULL,
) ENGINEInnoDB;3.3.2 遵循索引设计原则 详细请参考 MySQL高级篇——索引的创建与设计原则_vincewm的博客-CSDN博客 命名索引的字段个数尽量别超过5个命名格式“idx_col1_col2”在频繁查询特别是分组、范围、排序查询的列建立索引频繁更新的表不要创建过多索引唯一特性的字段适合创建索引很长的varchar字段适合根据区分度和长度创建前缀索引多个字段都要创建索引时联合索引优于单值索引避免创建过多索引避免索引失效尽量用有序的字段作为主键索引防止乱序时新主键前移到已满的数据页导致插入后分裂数据页造成性能损耗
3.3.3 连接查询优化 详细请参考 MySQL高级篇——索引失效的11种情况_vincewm的博客-CSDN博客 3.3.3.1 被驱动表连接字段加索引
外连接查询时右表就是被驱动表建议加索引。
原因因为MySQL连接查询底层是先查过滤条件下的左表按左表查询的结果再以连接字段作为条件查询结果所以右表的连接字段加索引将极大地提高查询性能。 驱动表与被驱动表 驱动表在连接操作中驱动表是首先被读取的表。MySQL会从驱动表中读取数据行然后在被驱动表中寻找匹配的行。被驱动表被驱动表是连接操作中第二个被读取的表。对于驱动表中的每一行MySQL会在被驱动表中寻找匹配的行。 验证 被驱动表连接字段没索引 下面SQL驱动表是班级表被驱动表是学生表学生表的连接字段classId没有创建索引所以查询性能不如上面的SQL EXPLAIN SELECT * FROM class c LEFT JOIN student s on c.ids.classId;这个连接查询相当于先查驱动表班级表再查被驱动表学生表。两个type都是all被驱动表学生表的条件是连接字段classId这个字段没有加索引所以type是all。 查询时长 被驱动表连接字段有索引 创建索引并分析 -- 因为被驱动表连接字段是classid所以给他创建索引
CREATE INDEX idx_classid on student(classid);
EXPLAIN SELECT * FROM class c LEFT JOIN student s on c.ids.classId;我们可以看到被驱动表学生表走了索引type是ref即命中非唯一索引。 查询时长 结论可以看到查询时长从180秒优化到了0.6秒。被驱动表的连接字段加索引和不加索引性能区别还是很大的。 注意我这里被驱动表用的是学生表而不是班级表因为如果用班级表作为被驱动表连接字段将成为被驱动表的idid主键默认加唯一索引不方便验证。 3.3.3.2 小表驱动大表
在MySQL连接查询时建议用小表驱动大表即“小表 left join 大表”或者“小表 right join 大表”。
小表驱动大表是为了减少连接次数
因为同样两张表相互连接小表驱动大表可以有效减少连接的次数。上一节有说过连接查询的原理是先查左表再根据连接字段查右表然后过滤右表的条件。因为相比普通的查询连接查询要左表右表都查一次肯定没有只查一次快所以连接次数越少越好所以要用小表驱动大表。
连接次数验证
现有两个表A与B 表A有200条数据表B有20万条数据 ; 按照循环的概念举个例子
小表驱动大表 A驱动表B被驱动表连接次数200次。 for(200条){for(20万条){...}}大表驱动小表 B驱动表A被驱动表连接次数20万次。 for(20万){for(200条){...}}所以
如果小的循环在外层对于表连接来说就只连接200次 ;如果大的循环在外层则需要进行20万次表连接从而浪费资源增加消耗 ; 验证-不加索引学生表50w数据班级表1w数据 先清除所有索引 为了防止索引带来的影响先去掉所有索引 CALL proc_drop_index(test,student);大表驱动小表 为了避免主键唯一索引对结果的影响我们连接字段不使用id而使用classId和monitor。 下面SQL驱动表是学生表被驱动表是班级表连接字段是班级表的monitor。 学生比班级多符合大表驱动小表 SELECT * FROM student s LEFT JOIN class c on s.classIdc.monitor;可以看到查询时长是206s。 小表驱动大表即班级表驱动学生表 SELECT * FROM class c LEFT JOIN student s on s.classIdc.monitor;可以看到查询时长只有189s 结论可以看出使用小表驱动大表后查询时长缩短了16s。 验证-加索引 CREATE INDEX idx_classid on student(classid);
CREATE INDEX idx_monitor on class(monitor); 大表驱动小表学生比班级多符合大表驱动小表 SELECT * FROM student s LEFT JOIN class c on s.classIdc.monitor;查询时长 小表驱动大表即班级表驱动学生表 SELECT * FROM class c LEFT JOIN student s on s.classIdc.monitor;结论可以看出使用小表驱动大表后查询时长由0.6s优化到了0.2s 3.3.3.3 两表连接字段类型必须一致
两个表JOIN字段数据类型保持绝对一致。防止MySQL查询优化器隐式的自动类型转换导致索引失效。
索引失效上面3.3.1.5有详细说此处不再赘述。 验证 -- 修改classId 字段类型
ALTER TABLE student MODIFY COLUMN classId VARCHAR(255);
-- 删除所有索引
CALL proc_drop_index(test,student);
-- 创建classId 字段索引
CREATE INDEX idx_classid on student(classid);学生表的classid字段设置成varchar类型而班级表的id是int类型再使用下面SQL将这两个字段连接起来就会导致索引失效。 EXPLAIN SELECT * FROM class c LEFT JOIN student s on s.classIdc.monitor;可以看到被驱动表学生表的查询计划中type是all即全表扫描索引失效了 结论连接查询时两个表的连接字段必须保持类型一致。 然后我们把classid字段类型改回来 ALTER TABLE student MODIFY COLUMN classId INT;3.3.4 子查询优化 详细请参考 MySQL高级篇——索引失效的11种情况_vincewm的博客-CSDN博客 优化方案
3.3.4.1.子查询优化成关联查询
性能 在数据量小、过滤条件简单时子查询效率高一点在数据量大、过滤条件复杂时关联查询效率高很多。综合考虑建议使用关联查询。子查询的缺点 嵌套查询子查询可能导致嵌套查询一个子查询套另一个子查询又套另一个子查询这会增加查询的复杂性并降低性能。数据重复检索子查询可能需要对数据进行多次检索尤其是在相关子查询中。而连接则允许数据库一次性检索所有需要的数据从而减少I/O操作和计算开销。语义清晰度还是建议使用关联查询阅读起来更直白、明确层次结构清晰后期维护成本也越低查询次数也一般更低一些。 子查询原理 子查询是指在一个SQL语句中嵌套另一个完整的SQL查询。它可以作为主查询的一部分也可以作为WHERE、FROM或HAVING子句的一部分。子查询的执行顺序是先执行子查询然后将其结果作为外部查询的条件或数据源。 验证子查询比关联查询快的场景 查询所有班长的学号、姓名 关联查询 SELECT DISTINCT s.* from class c LEFT JOIN student s on c.monitors.id;查询时长 子查询 SELECT * FROM student WHERE id in (SELECT DISTINCT monitor FROM class) 查询时长 验证子查询比关联查询慢的场景 查询所有学生及其对应的班级名称 关联查询 SELECT s.name, c.address
FROM student s
JOIN class c ON s.classId c.id; 子查询 SELECT name, (SELECT address FROM class WHERE id s.classId) AS address
FROM student s; 3.3.4.2.多次查询代替子查询
在复杂SQL中同一个子查询语句可能在整个SQL中多次出现这就导致了性能浪费。这种情况下结合Java代码多次查询而不用子查询可以使这个重复子查询语句只查一次从而提高性能。 通常情况下我们可能想多次查询肯定没有一次查询因为MySQL查询两次肯定就有两次I/O调用过程而查询一次也就只有一次调用过程。 但其实还是需要具体情况具体分析如果是简单SQL只调用了一次子查询那肯定是子查询快当同一个子查询语句可能在整个SQL中多次出现时用Java代码肯定是更快的虽然IO次数多但这么多次子查询缩减成了一次性能是快了的。 这也是日常开发中的一个原则任何性能优化都需要看具体的业务场景。 3.3.4.3.临时表代替子查询
如果一个复杂SQL中多次用到了同一个子查询可以尝试将其抽离出来优化成临时表。这样可以避免重复计算、减少查询次数从而提高查询性能。
并且可维护性也有效提高每次修改时只需要修改这一个临时表而不需要手动一个个修改子查询语句。
3.3.5 排序优化 详细请参考 MySQL高级篇——排序、分组、分页优化_vincewm的博客-CSDN博客 3.3.5.1 合理选择索引排序和FileSort排序
结论
查询优化器会自动选择索引排序和FileSort排序索引排序一般更快在一些索引失效的情况下FileSort可能效率更高 MySQL支持索引排序和FileSort排序索引保证记录有序性性能高推荐使用。 FileSort排序是内存中排序当数据量大时查询优化器会产生临时文件在磁盘里对数据排序。我们知道磁盘的IO速度是远低于内存的所以它的性能一般不如索引排序而且排序过程中会占用大量CPU。 当然任何事不是绝对的一些情况FileSort可能效率高。例如没覆盖索引的左模糊、“不等于”、not null等索引失效情况下全表扫描效率比非聚簇索引树遍历再回表更高。 创建索引 CREATE INDEX idx_name on student(name); 验证索引失效时FileSort更快 EXPLAIN SELECT * FROM student WHERE name like %34 order by name; 可以看到type是all全表扫描extra是using filesort即使用了FileSort排序全表扫描排序 覆盖索引时索引排序更快 而如果我们使用了覆盖索引在非聚簇索引树查询时将不需要回表所以使用了索引排序 EXPLAIN SELECT name FROM student WHERE name like %34 order by name;可以看到type是index即索引树上全表扫描extra是using index覆盖索引 3.3.5.2 排序字段符合最左前缀
当where后的条件符合最左前缀原则时要让排序也走索引需要排序字段也符合最佳左前缀原则。例如索引(a,b,c)查询where a1 order by a,b,c走索引而where a1 order by b,c不走索引。 验证 创建索引 CREATE INDEX idx_stuno_age_classid on student(stuno,age,classid); 标准的排序字段符合最左前缀 EXPLAIN SELECT * FROM student WHERE stuno 3 and age2 order by stuno;可以看到type是ref即命中非唯一索引extra是空 排序字段不符合最左前缀-使用索引下推 将排序字段由学号改为班号 EXPLAIN SELECT * FROM student WHERE stuno 3 and age2 order by classid;可以看到type是ref即命中非唯一索引extra是using index condition即使用索引下推。索引下推是MySQL5.6支持的当过滤的字段在索引树上并且索引失效时将直接在索引树上走过滤而不需要在MySQL服务器回表过滤。 排序字段不符合最左前缀-不使用索引下推 将排序字段由学号改为名字 EXPLAIN SELECT * FROM student WHERE stuno 3 and age2 order by name;可以看到type是ref即命中非唯一索引extra是using filesort即使用了FileSort排序。 3.3.5.3 全升序或者全降序
排序顺序必须要么全部DESC要么全部ASC尽量不要使用混合排序。因为索引树的数据以一个顺序排列的如果一些字段升序一些字段降序会导致整体性能较差。 验证 全升序运行时间0.025s EXPLAIN SELECT * FROM student WHERE stuno 3 and age2 order by stuno ,age,classid;乱序运行时间0.071s SELECT * FROM student WHERE stuno 3 and age2 order by stuno desc ,age,classid desc; 3.3.5.4 待排序数量大时尽管索引没失效索引效率不如filesort
待排序数据量大约超过一万个就不走索引走filesort了。建议用limit和where过滤减少数据量。数据量很大时索引排序完需要回表根据这些过滤后数据的id查所有数据性能很差还不如FileSort在内存中排序效率高。
注意并不是说使用limit一定会走索引排序关键看的是数据量数据量过大时优化器会使用FileSort排序。
3.3.5.5 范围查询使排序索引失效
前面3.3.1.6 有提到范围查询会使后续的列索引失效当然也会令排序无法走索引。 验证 索引排序 EXPLAIN SELECT * FROM student WHERE stuno 3 order by stuno;范围查询导致排序索引失效 EXPLAIN SELECT * FROM student WHERE stuno 3 order by stuno;3.3.5.6 优先范围字段加索引即使排序索引失效
当【范围条件】和【group by 或者 order by】的字段出现二选一时如果过滤的数据足够多而需要排序的数据并不多时优先把索引放在范围字段上。
因为MySQL底层是先where过滤按过滤结果再分组、排序所以优先给where后的字段加过滤过滤掉主要的数据然后再分组或排序性能可以快很多。
这样即使范围查询导致排序索引失效效率依然比只索引排序字段时候高。如果只能过滤一点点那就优先索引放到排序字段上。
3.3.5.7 调优FileSort
无法使用 Index 排序时需要对 FileSort 方式进行调优。
1.排序缓冲区大小
sort_buffer_size。增加 sort_buffer_size 可以让更多的数据在内存中排序从而减少对磁盘的依赖提高性能。
SET GLOBAL sort_buffer_size 1048576; -- 设置为1MB根据需求调整查看当前排序缓冲区大小 SHOW VARIABLES LIKE sort_buffer_size;2.排序数据最大长度
max_length_for_sort_data。增大这个值可以让 MySQL 采用更有效的排序方式但需要权衡内存使用量。
SET GLOBAL max_length_for_sort_data 1024; -- 设置为1KB根据需求调整3.3.6 分组优化
跟排序基本一个思路。
排序分组都比较耗费cpu能不用就不用。
当需要过滤数据时核心思路是能在where前过滤就别在having后过滤。因为where效率高于having。where是分组前过滤having是分组后过滤。
3.3.7 深分页查询优化
深分页Deep Pagination指在数据库查询中通过 LIMIT 和 OFFSET 子句来分页获取数据时偏移量OFFSET较大的情况。例如“limit 200000,10” 即查询学生表中第200000~200010 的数据。
一般情况下偏移量到达一万就可以称为深分页。但实际情况下还是需要根据具体的字段数量和类型去进行判断例如一个表里只有两个int型字段那么可能要十万以上才能算作深分页。
优化方法
如果主键有序 如果排序字段是主键则先过滤再排序过滤条件是上一页最后一条记录适用于app这种手滑翻页情景翻页时一定是顺序翻页如果排序字段不是主键则根据上一页最后一条记录按规律排序如果主键不有序 如果排序字段是主键则先给主键分页然后内连接原表查其他字段 验证 需求返回第200000~200010 的记录 常规查询 EXPLAIN SELECT * FROM student ORDER BY id limit 200000,10可以看到运行时长0.09s因为排序字段是id所以走了主键索引树type是index。 主键有序的表根据主键排序先过滤再排序 直接查范围之后的几个数据。 EXPLAIN SELECT * FROM student WHERE id 200000 LIMIT 10;运行时长0.048s可以看到性能变快。因为走范围索引后只对10个元素进行了排序而常规查询方案相当于对200010条数据排序了。 分析查询计划可以看到type是range即范围索引extra是using where 即回表了因为select *。 主键不有序的表根据主键排序先给主键分页然后内连接原表 当前表内连接排序截取后的主键表连接字段是主键。因为查主键是在聚簇索引树查不用回表排序和分页很快 EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 200000,10) a WHERE t.id a.id;可以看到运行时长明显缩短。 主键有序的表根据非主键排序 得到上一页最后一条记录x那么目标页码的所有记录id都比x.id小因为逆序且排序依据其实是age,id主键自增目标页码的所有记录age都比x.age小或等于。 EXPLAIN SELECT * FROM student WHERE id#{x.id} AND age#{x.age} ORDER BY age DESC LIMIT 10; 验证 常规方案0.378s SELECT * FROM student ORDER BY age,id limit 200000,10优化方案0.031s SELECT * FROM student WHERE id481690 AND age20 ORDER BY age,id LIMIT 10; 3.3.8 尽量覆盖索引 详细请参考 MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计_vincewm的博客-CSDN博客 一个索引包含了满足查询结果的数据。因为不需要回表所以查询效率高。覆盖索引时“左模糊”和“不等于”不能让索引失效。
示例
#没覆盖索引的情况下左模糊查询导致索引失效
CREATE INDEX idx_age_name ON student(age, NAME);
EXPLAIN SELECT * FROM student WHERE NAME LIKE %abc; 覆盖索引一个索引包含了满足查询结果的数据就叫做覆盖索引不需要回表等操作。
索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据当能通过读取索引就可以得到想要的数据那就不需要读取行了。
覆盖索引是非聚簇索引的一种形式它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 即建索引的字段正好是覆盖查询条件中所涉及的字段。简单说就是 索引列主键 包含 SELECT 到 FROM之间查询的列 。
3.3.9 字符串前缀索引
例如(email(6))给字符串前缀而不是整个字符串添加索引前缀长度要根据区分度和长度进行取舍。
示例
MySQL是支持前缀索引的。默认地如果你创建索引的语句不指定前缀长度那么索引就会包含整个字符串。
mysql alter table teacher add index index1(email);
#或
mysql alter table teacher add index index2(email(6));这两种不同的定义在数据结构和存储上有什么区别呢下图就是这两个索引的示意图。 如果使用的是index1索引包含整个字符串执行顺序是这样的
从index1索引树找到满足索引值是’ zhangssxyzxxx.com’的这条记录取得ID2的值回表到主键上查到主键值是ID2的行判断email的值是正确的将这行记录加入结果集取index1索引树上刚刚查到的位置的下一条记录发现已经不满足email zhangssxyzxxx.com ’的 条件了循环结束。
这个过程中只需要回主键索引取一次数据所以系统认为只扫描了一行。
如果使用的是index2索引包含字符串前缀email(6)执行顺序是这样的
从index2索引树找到满足索引值是’zhangs’的记录找到的第一个是ID1回表到主键上查到主键值是ID1的行判断出email的值不是’ zhangssxyzxxx.com ’这行记录丢弃取index2上刚刚查到的位置的下一条记录发现仍然是’zhangs’取出ID2再到回表到ID索引上取整行然后判断这次值对了将这行记录加入结果集重复上一步直到在index2上取到的值不是’zhangs’时循环结束。
也就是说使用前缀索引定义好长度就可以做到既节省空间又不用额外增加太多的查询成本。前面 已经讲过区分度区分度越高越好。因为区分度越高意味着重复的键值越少。 3.3.10 尽量使用MySQL5.6支持的索引下推
ICP索引下推在使用范围查询时允许 MySQL 在存储引擎层面如 InnoDB利用索引树来过滤数据而不是将数据传递给 MySQL 服务器层再进行过滤从而减少了需要从存储引擎传递到服务器层的数据量。
简而言之索引下推可以使范围查询右侧索引失效的列依然可以在索引树上过滤。
例如索引(name,age)查询name like z% and age and addressz%是模糊查询实际上也是范围查询。使用索引下推时在联合索引树查询时不止查name还会判断后面的age。而如果关闭了索引下推联合索引里范围查询后面的字段age不能在联合索引树里直接条件判断必须回表到主键索引树后以之前过滤结果id查找到对应数据再过滤age列。 注意这里举例没有使用%z因为左模糊查询会使整个索引失效也就不会用到索引下推了。 索引下推(ICPIndex Condition Pushdown)是MySQL 5.6中新特性是一种在存储引擎层使用索引过滤数据的一种优化方式。
如果没有ICP联合索引某字段是模糊查询非左模糊或范围查询时该字段进行条件判断后后面几个字段不能用来直接条件判断必须在MySQL服务器回表后再判断。启用ICP 后联合索引某字段是模糊查询非左模糊或范围查询时该字段进行条件判断后后面几个字段可以直接条件判断判断过滤后再回表对不包含在联合索引内的字段条件进行判断。主要优化点是在回表之前过滤减少回表次数。主要应用模糊查询非左模糊导致索引里该字段后面的字段无序必须要回表判断而使用了索引下推就不需要回表直接在联合索引树里判断。
举例
不支持索引下推的联合索引例如索引(name,age)查询name like z% and age模糊查询实际是范围查询导致右边的列age无法走索引。在联合索引树查询时只会查name后面的age乱序不能直接进行条件判断必须回表后再判断age。而支持索引下推的联合索引例如索引(name,age)查询name like z% and age and address在联合索引树查询时不止查name还会判断后面的age过滤后再回表判断address。 验证 删除所有索引 CALL proc_drop_index(test,student); 创建索引 CREATE INDEX idx_name_age ON student(name,age); 开启索引下推 SET optimizer_switchindex_condition_pushdownon;索引下推查询计划分析 #索引成功MySQL5.6引入索引下推where后面的name和age都在联合索引里可以又过滤又索引不用回表索引生效
EXPLAIN SELECT * FROM student WHERE name like bc% AND age30; 可以看出type是range即范围索引extra是using index condition即使用了索引下推。因为 age在联合索引name,age树里所以即使name是范围查询导致右侧的age索引失效了依然可以在这个索引树中过滤age条件 关闭索引下推 SET optimizer_switchindex_condition_pushdownoff;关闭索引下推后查询计划分析 #范围查询导致右侧列age索引失效因为关闭了索引下推
EXPLAIN SELECT * FROM student WHERE name like bc% AND age30; 可以看到type是range即使用了范围索引extra是using where即使用了回表。在走name列范围查询后age列索引失效转为回表将name过滤后的数据id作为条件在主键索引数中过滤age条件 3.3.11 读少写多的场景尽量用普通索引
结论读多写少用唯一索引读少写多用普通索引代码逻辑中去维护唯一性。
普通索引和唯一索引的区别
概念普通索引可重复唯一索引不能重复。查询性能 唯一索引查询性能略高特别是重复记录很多的时候。因为同样查k4普通索引查到第一个k4记录时还要继续查下去直到查到不满足k4的记录而唯一索引查到第一个k4记录将不再需要查下去。整体来说二者性能差距很小因为 InnoDB 是以页为单位读写的所以可能对于普通索引的扫描过程来说就是在内存中进行除非是需要跨页查询了那还要继续读取下一页数据。更新性能 普通索引更新性能更高特别是目标页不在内存中场景。因为普通索引有change buffer写缓存将更新后的数据页缓存到内存下次访问时或后台定期会执行merge操作将该数据页写入磁盘。change buffer在事务提交时会写入redo log保证数据持久化而唯一索引不支持写缓存而且插入前要判断唯一性这部分会影响性能。应用场景 数据唯一、多读少写使用唯一索引因为它查询性能高写性能差。数据唯一、少读多写使用普通索引代码逻辑保持唯一。 更新之后需要立刻查询关闭 change buffer。不然要经历“更新操作存入change buffer-加载数据页到内存缓冲池-更新-“change buffer删除对应更新操作”-查询”的过程影响性能。关闭后流程是“加载数据页到内存缓冲池-更新-查询”。更新之后不需要立刻查询保持change buffer打开。数据不唯一使用普通索引不能使用唯一索引。语句 普通索引不加任何限制条件如create index idx_name on student(name)。唯一索引UNIQUE参数限制索引唯一如create UNIQUE index idx_name on student(name)。 写缓存change buffer 当需要更新一个数据页时如果数据页在内存中就直接更新而如果这个数据页还没有在内存中的话 在不影响数据一致性的前提下 InooDB会将这些更新操作缓存在change buffer中 这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候将数据页读入内存然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。 merge 将change buffer中的操作应用到原数据页得到最新结果的过程称为 merge 。除了访问这个数据页会触发merge外系统有后台线程会定期merge。在数据库正常关闭shutdown的过程中也会执行merge 操作。 如果能够将更新操作先记录在change buffer 减少读磁盘 语句的执行速度会得到明显的提升。而且 数据读入内存是需要占用 buffer pool 的所以这种方式还能够 避免占用内存提高内存利用率。 唯一索引的更新就不能使用change buffer 实际上也只有普通索引可以使用。 做好区分 读数据用的是缓冲池buffer pool重做日志有个redo log buffer是将缓冲池里更新的数据写入redo log buffer事务提交时根据刷盘策略将redo log buffer刷盘到redo log file或page cache。 3.4 SQL优化 详细请参考 MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计_vincewm的博客-CSDN博客 3.4.1 合理选用 EXISTS 和 IN
遵循小表驱动大表原则左边表小就是EXISTS左边表大就用IN。 EXISTS适用于左边表较小的情况因为EXISTS子查询在找到满足条件的记录后会立即返回适合处理小表驱动大表的场景。 SELECT * FROM large_table WHERE EXISTS (SELECT 1 FROM small_table WHERE small_table.id large_table.id);IN适用于左边表较大的情况因为IN子查询会先执行并将结果集缓存下来再与外部查询匹配适合处理大表驱动小表的场景。 SELECT * FROM small_table WHERE id IN (SELECT id FROM large_table);3.4.2 统计数量COUNT(1) 或 COUNT(*)
count1:统计整个表的记录行数。括号里表示一个固定值可以是任何固定的数字字符是个常量。在InnoDB存储引擎中查询优化器会优先选择占用空间最小的二级索引树进行统计。COUNT(1)和COUNT(*)在性能上没有显著差别因为优化器会处理为相同的查询计划。MyISAM存储引擎中COUNT操作的时间复杂度为O(1)。count*:统计整个表的记录行数与count1执行结果相同但是执行会根据目标表的不同进行优化。count列名:统计某一列的非空记录数。它会统计指定列中不为NULL的行数忽略NULL值。count(distinct(列名)) 统计某一列的非空去重记录数。其实是 count(列名) distinct 的结果集指定列不为NULL并且在字段值重复的情况下只统计一次
在使用InnoDB存储引擎时COUNT(1),COUNT(*)时查询优化器会优先选用有索引的、占用空间最小的二级索引树进行统计只有找不到非聚簇索引树时才会采用使用聚簇索引树统计。
当然也能COUNT(最小空间二级索引字段)但很麻烦需要你自己找到最小空间并且建了索引的字段而且也要考虑null值不如交给优化器自动选择。
在使用MyISAM存储引擎时就无所谓了用哪个时间复杂度都是O(1)。
SELECT COUNT(*) FROM table_name;3.4.3 避免SELECT * 明确字段查询避免使用SELECT *明确列名不仅可以提高查询解析速度还可以利用覆盖索引减少回表操作。select *可能会多查一些字段提高了一些网络传输负担而且杜绝了覆盖索引的可能性性能较差。 SELECT id, name, age FROM student WHERE id 123;数据库引擎的通用查询流程 解析 SQL 语句数据库引擎先将 SQL 语句解析成内部的执行计划包括了查询哪些数据表、使用哪些索引、如何连接多个数据表等信息。优化查询计划数据库引擎对内部的执行计划进行优化根据查询的复杂度、数据量和系统资源等因素选择最优的执行计划。执行查询计划数据库引擎根据执行计划通过 I/O 操作读取数据表的数据进行数据过滤、排序、分组等操作最终返回结果集。缓存查询结果如果查询结果集比较大或者查询频率较高数据库引擎会将查询结果缓存在内存中以加速后续的查询操作。 MySQL执行一条select语句时会经过的流程 连接器主要作用是建立连接、管理连接及校验用户信息。查询缓冲查询缓冲是以key-value的方式存储key就是查询语句value就是查询语句的查询结果集如果命中直接返回。 8.0版本废弃注意MySQL 8.0已经删除了查询缓冲。从MySQL 5.6版本开始官方将Query Cache设置为了默认关闭。原因官方给出的原因是这个功能比较鸡肋而且减少性能的可变性确实通常比提高峰值吞吐量更重要尤其是在生产环境中。稳定的性能可以确保用户体验的一致性并减少系统出现瓶颈或宕机的风险。方案官方给出了所替代的解决方案建议——使用第三方工具客户端缓存ProxySQL 来代替Query Cache。分析器词法句法分析生成语法树。优化器指定执行计划选择查询成本最小的计划。执行器根据执行计划从存储引擎获取数据并返回客户端 ProxySQL 基本介绍一个MySQL中间件一个高性能的 MySQL 代理一个用 C 开发的轻量级产品。旨在提高 MySQL 服务器的性能、可伸缩性和可用性。MySQL官方推荐的Query Cache替换方案。同类产品DBproxy、MyCAT、OneProxy安装配置 安装 sudo yum install proxysql 配置 修改/etc/proxysql.cnf 直接配置或者访问管理接口配置 -- 连接到管理接口
mysql -u admin -padmin -h 127.0.0.1 -P 6032-- 添加MySQL服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 192.168.1.100, 3306);-- 加载配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;-- 保存配置到磁盘
SAVE MYSQL SERVERS TO DISK;功能 查询缓存负载均衡支持自动摘除宕机的DB读写分离 -- 主库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, 192.168.1.101, 3306);-- 从库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, 192.168.1.102, 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, 192.168.1.103, 3306);
-- 将所有写操作INSERT、UPDATE、DELETE定向到主库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES (1, 1, ^INSERT, 10);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES (2, 1, ^UPDATE, 10);
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES (3, 1, ^DELETE, 10);-- 将所有读操作SELECT定向到从库
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES (4, 1, ^SELECT, 20);
-- 加载并保存配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;实时监控连接池动态加载配置访问控制ProxySQL集群 3.4.4 全表扫描时尽量用 LIMIT
当进行全表扫描并且明确时使用LIMIT可以在达到指定数量后停止扫描减少不必要的开销。
例如根据学号查询学生根据身份证号查询人根据订单号查询订单当我们明确知道需要精准查询时用Limit 1 总错不了。
当然如果走了唯一索引就无需用limit了查到对应记录会直接返回如果走了普通索引并且对应记录重复数据很多的话用limit也会提高一些性能。
-- 根据学号假设学号是按班级隔离的和班级号精准查询学生
SELECT * FROM student where stuno 23 and classid1 LIMIT 1;3.4.5 使用 LIMIT N少用 LIMIT M, N 避免大偏移量的LIMIT在大表或M值较大时LIMIT M, N的性能较差因为需要扫描并丢弃前M条记录。可以通过记录上次查询的最大ID来优化分页。 SELECT * FROM large_table WHERE id last_id ORDER BY id ASC LIMIT 10;3.4.6 代码将长事务拆为多个小事务 多使用COMMIT长事务会持有锁和占用资源较长时间拆分为小事务并频繁COMMIT可以释放锁、减少资源占用。
示例
Transactional
public void fun(){// 1.查询a// 2.查询b// 3.数据处理// 4.保存c表// 5.保存b表
}
优化成
Transactional
public void fun(){// 1.查询a// 2.查询b// 3.数据处理// 4.落库savaFun();
}public void savaFun(){// 1.保存c表// 2.保存d表
} 3.4.7 删改前先查询 确保WHERE条件明确在执行UPDATE或DELETE操作前先SELECT一下并不会让性能变差它可以确保有明确的WHERE条件避免误操作和全表扫描。 UPDATE student SET age age 1 WHERE id 123;
DELETE FROM student WHERE id 123;阿里规约 【强制】 数据订正特别是删除、修改记录操作时要先 select 避免出现误删除确认无误才能执行更新语句。 3.4.8 尽量UNION ALL而不是UNION UNION ALLUNION ALL 和 UNION 都用于组合两个或多个查询结果集。UNION ALL在组合时不进行去重操作比UNION更快适用于不需要去重的场景。 SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;
