MySQL优化经验总结
线上SQL的调优经验
slow_query_log日志中收集到的慢 SQL ，结合explain分析是否命中索引。- 减少索引扫描行数，有针对性的优化慢 SQL。
- 建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。
- 还可以使用虚拟列和联合索引来提升复杂查询的执行效率。
- 监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql
- 打开数据库慢查询日志功能
- 简化业务逻辑
- 代码重构、优化
- 异步处理
- sql优化
- 索引优化
SQL优化
分页优化。比如电梯直达，
limit 100000,10先查找起始的主键id，再通过id>#{value}往后取10条尽量使用
覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。
where子句 避免对字段进行
null值判断、表达式操作等；
where表之间的连接，必须写在其他where条件之前； 可过滤掉最大数量记录的条件必须写在
where子句的末尾，having最后；优先考虑在
where及order by涉及的列上建立索引；避免在索引列上使用函数运算、
is null和is not null；避免使用
select *;使用
union all代替unionexists代替innot exists代替not in连接查询代替子查询；
批量操作
小表驱动大表；
多用limit
in中值太多 分页
增量查询
高效的分页
join表不宜过多
控制索引数量
选择合理的字段类型
提升group by 的效率
索引优化
设计优化（避免使用NULL、用简单数据类型如int、减少 text 类型、分库分表）。
硬件优化（使用SSD 减少 I/O 时间、足够大的网络带宽、尽量大的内存）
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2019-03-20
MySQL主从复制与读写分离
主从同步 master主库，有数据更新，将此次更新的事件类型写入到主库的binlog文件中 主库会创建log dump 线程通知slave有数据更新 slave从库，向master节点的 log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的Relay log 中继日志中 slave 再开启一个SQL 线程读取Relay log事件，并在本地执行redo操作。将发生在主库的事件在本地重新执行一遍，从而保证主从数据同步 主从延迟 指一个写入SQL操作在主库执行完后，将数据完整同步到从库会有一个时间差，称之为主从延迟。 主库生成一条写入SQL的binlog，里面会有一个时间字段，记录写入的时间戳 t1 binlog 同步到从库后，一旦开始执行，取当前时间 t2 t2-t1，就是延迟时间 注意：不同服务器要保持时钟一致。 主从延迟排查方法 通过 show slave status 命令输出的Seconds_Behind_Master参数的值来判断 为零：表示主从复制良好 正值：表示主从已经出现延时，数字越大，表示从库延迟...
2019-03-20
MySQL主键
主键使用自增ID还是UUID?能说说原因吗？ 自增ID和UUID作为主键的考虑主要有两方面， 一个是性能 另一个就是存储的空间大小， 一般没有特定的业务要求都不推荐使用UUID作为主键。 因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。 而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。 另一方面就是存储空间， 自增主键一般整形只要4个字节，长整形才占8字节的大小空间， 而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘， 在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低， 所以不推荐使用。 自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。 所以尽量不要使用字符串（如：UUID）作为主键。
2019-05-10
索引的优缺点
一句话 索引以”读快、写慢、占空间”为代价换查询性能。 加索引前先想：这字段查得多吗？选择性够不够？ 一、优点 优点 说明 加速 WHERE 查询 从 O(N) 全表扫描 → O(log N) B+ 树查找 加速 ORDER BY / GROUP BY 索引天然有序，省去 filesort 加速 JOIN 关联字段有索引时大幅提速 唯一索引保证唯一性 数据约束 + 查询双重价值 覆盖索引免回表 二级索引就能返回所有需要的列 二、缺点 缺点 说明 量化 占空间 每个索引都是一棵 B+ 树 一个 INT 索引在 100w 行表上约 30MB 写入变慢 INSERT/UPDATE/DELETE 都要维护所有相关索引 每多一个索引，写入慢 5-15% 优化器选错索引 索引太多反而干扰优化器 用 FORCE INDEX 兜底 维护成本 在线加索引要锁表/影响性能 大表用 pt-online-schema-change 三、加索引的判断标准✅ 该加： WHERE / ORDER BY...
2021-03-20
SQL查询语句的流程
当Mysql执行一条查询的SQl的时候大概发生了以下的步骤： 客户端发送查询语句给服务器。 服务器首先进行用户名和密码的验证以及权限的校验。 然后会检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。注意：Mysql 8就把缓存这块给砍掉了。 接着进行语法和词法的分析，对SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。 Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。服务器将查询的结果返回客户端。 Mysql中语句的执行都是都是分层执行，每一层执行的任务都不同，直到最后拿到结果返回，主要分为Service层和引擎层。 在Service层中包含：连接器、分析器、优化器、执行器。引擎层以插件的形式可以兼容各种不同的存储引擎，主要包含的有InnoDB和MyISAM两种存储引擎。具体的执行流程图如下所示：
2019-05-20
索引失效的 12 种原因
一句话 索引失效 = MySQL 优化器决定不用你建的索引去执行。 永远用 EXPLAIN 验证，不要靠记忆。 12 种失效场景1. WHERE 列上做函数 / 表达式1234567-- ❌ 索引失效SELECT * FROM users WHERE YEAR(created_at) = 2024;SELECT * FROM users WHERE id + 1 = 100;-- ✅ 改写：把函数挪到右边SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';SELECT * FROM users WHERE id = 99; MySQL 8.0+ 支持 函数索引：ALTER TABLE users ADD INDEX idx_year ((YEAR(created_at))) 2. 隐式类型转换123456-- 字段是 VARCHAR，传了 INT-- ❌ 走全表扫SELECT * FROM...
2020-03-20
MySQL存储引擎
InnoDB和MyISAM的区别 （1）InnoDB和MyISAM都是Mysql的存储引擎，现在MyISAM也逐渐被InnoDB给替代，主要因为InnoDB支持事务和行级锁，MyISAM不支持事务和行级锁，MyISAM最小锁单位是表级。因为MyISAM不支持行级锁，所以在并发处理能力上InnoDB会比MyISAM好。 （2） 数据的存储上：MyISAM的索引也是由B+树构成，但是树的叶子结点存的是行数据的地址，查找时需要找到叶子结点的地址，再根据叶子结点地址查找数据。 InnoDB的主键索引的叶子结点直接就是存储行数据，查找主键索引树就能获得数据： 若是根据非主键索引查找，非主键索引的叶子结点存储的就是，当前索引值以及对应的主键的值，若是联合索引存储的就是联合索引值和对应的主键值。 （3）数据文件构成：MyISAM有三种存储文件分别是扩展名为：.frm（文件存储表定义）、.MYD (MYData数据文件)、.MYI (MYIndex索引文件)。而InnoDB的表只受限于操作系统文件的大小，一般是2GB （4）查询区别：对于读多写少的业务场景，MyISAM会更加适合，而对...
评论
