SQL优化一般步骤慢日志定位通过慢查日志等定位那些执行效率较低的SQL语句MySQL的慢查询日志是MySQL提供的一种日志记录它用来记录在MySQL中响应时间超过阀值的语句具体指运行时间超过long_query_time值的SQL则会被记录到慢查询日志中。long_query_time的默认值为10意思是运行10S以上的语句。默认情况下Mysql数据库并不启动慢查询日志需要我们手动来设置这个参数当然如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件也支持将日志记录写入数据库表。explain 分析SQL的执行计划需要重点关注type、rows、filtered、extra。type由上至下效率越来越高ALL全表扫描index索引全扫描range索引范围扫描常用语,,,between,in等操作ref使用非唯一索引扫描或唯一索引前缀扫描返回单条记录常出现在关联查询中eq_ref类似ref区别在于使用的是唯一索引使用主键的关联查询const/system单条记录系统会把匹配行中的其他列作为常数处理如主键或唯一索引查询nullMySQL不访问任何表或索引直接返回结果 虽然上至下效率越来越高但是根据cost模型假设有两个索引idx1(a, b, c),idx2(a, c)SQL为select * from t where a 1 and b in (1, 2) order by c;如果走idx1那么是type为range如果走idx2那么type是ref当需要扫描的行数使用idx2大约是idx1的5倍以上时会用idx1否则会用idx2ExtraUsing filesortMySQL需要额外的一次传递以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序并按排序顺序检索行。Using temporary使用了临时表保存中间结果性能特别差需要重点优化Using index表示相应的 select 操作中使用了覆盖索引Coveing Index,避免访问了表的数据行效率不错如果同时出现 using where意味着无法直接通过索引查找来查询到符合条件的数据。Using index conditionMySQL5.6之后新增的ICPusing index condtion就是使用了ICP索引下推在存储引擎层进行数据过滤而不是在服务层过滤利用索引现有的数据减少回表的数据。索引下推在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。show profile 分析了解SQL执行的线程的状态及消耗的时间。默认是关闭的开启语句“set profiling 1;”SHOW PROFILES ; SHOW PROFILE FOR QUERY #{id};tracetrace分析优化器如何选择执行计划通过trace文件能够进一步了解为什么选择A执行计划而不选择B执行计划。确定问题并采用相应的措施优化索引优化SQL语句修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤改用其他实现方式ES、数仓等数据碎片处理优化索引(索引失效)如果Mysql索引失效会进行全表扫描将极大的影响查询效率因此应该尽量避免索引失效以下说明都是在已创建了相关字段的索引情况下描述的。当使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效因为索引 B 树是按照「索引值」有序排列存储的只能根据前缀进行比较。如果使用的like x% 匹配的时候是可以走索引的所以可以根据 x 的这个前缀进行匹配当在查询条件中对索引列做了计算、函数、类型转换操作这些情况下都会造成索引失效对索引使用左或者左右模糊匹配当使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。计算(使用函数)因为索引保存的是索引字段的原始值而不是经过计算后的值自然就没办法走索引了。函数计算或者表达式计算都没办法走索引//函数计算 select * from t_user where length(name)6; //表达式计算 select * from t_user where id 1 10;不过从 MySQL 8.0 开始索引特性增加了函数索引即可以针对函数计算后的值建立一个索引也就是说该索引的值是函数计算后的值所以就可以通过扫描索引来查询数据。类型转换如果索引字段是字符串类型但是在条件查询中输入的参数是整型的话那么这条语句会走全表扫描。但是如果索引字段是整型类型查询条件中的输入参数即使字符串是不会导致索引失效还是可以走索引扫描。原因在于MySQL在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。也就是说如果索引字段是整型类型查询条件中的输入参数是字符串会自动转换成整型所以索引不会失效。而索引字段是字符串而输入的是整型由于是字符串转数字而索引不是整型类型所以索引失效了。因此在使用sql语句时数值类型禁止加引号字符串类型必须加引号联合索引非最左匹配联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效。原因是在联合索引的情况下数据是按照索引第一列排序第一列数据相同时才会按照第二列排序。比如如果创建了一个 (a, b, c) 联合索引如果查询条件是以下这几种就可以匹配上联合索引where a1where a1 and b2 and c3where a1 and b2因为有查询优化器所以 a 字段在 where 子句的顺序并不重要。但是如果查询条件是以下这几种因为不符合最左匹配原则所以就无法匹配上联合索引联合索引就会失效:where b2where c3where b2 and c3对于where a 1 and c 0 这个语句前面的a 1是会走索引的后面的c不走索引。不应使用 or在 WHERE 子句中or如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。OR 的含义就是两个只要满足一个即可因此只有一个条件列是索引列是没有意义的只要有条件列不是索引列就会进行全表扫描。in尽量使用IN代替OR。但是IN包含的值不应过多应少于1000个。因为 IN 通常是走索引的当IN后面的数据在数据表中超过30%的匹配时是全表的扫描不会走索引其实就是 Mysql优化器会根据当前表的情况选择最优解。 Mysql优化器认为走全表扫描 比 走索引回表快 那就不会走索引范围查询阻断后续字段不能走索引索引KEY idx_shopid_created_status (shop_id, created_at, order_status)SQL语句select * from _order where shop_id 1 and created_at 2021-01-01 00:00:00 and order_status 10范围查询还有“IN、between”相关原理可以看这篇文章 唯一索引范围查询覆盖索引优化覆盖索引是指 SQL 中 查询的所有字段在这个二级索引 BTree 的叶子节点上都能找得到那些字段从二级索引中查询得到记录而不需要通过聚簇索引查询获得就可以避免回表的操作。asc和desc混用select * from _t where a1 order by b desc, c ascdesc 和asc混用时会导致索引失效避免更新索引列值每当索引列的值发生变化时数据库必须更新相应的索引结构更新索引列值可能导致这些树结构的重平衡或重新构建增加了额外的计算和I/O开销。不等于、不包含不能用到索引的快速搜索select * from _order where shop_id1 and order_status not in (1,2) select * from _order where shop_id1 and order_status ! 1在索引上避免使用NOT、!、、!、!、NOT EXISTS、NOT IN、NOT LIKE等not in一定不走索引吗答案是不一定。Mysql优化器会根据当前表的情况选择最优解。主要在于如果 MySQL 认为 全表扫描 比 走索引回表效率高 那么他会选择全表扫描。重要SQL必须被索引update、delete的where条件列、order by、group by、distinct字段、多表join字段on后面的字段例如select id from table_a where name seven order by address ; 此时建立 name address的联合索引比较好(此处name条件必须是 ,如果是范围则无效)如果是order by主键则只需要在name字段建立索引即可因为name索引表中是包含主键的也就是所谓了避免了回表操作。避免使用子查询通常情况下一般建议使用连接查询代替子查询原因如下连接查询JOIN子查询在执行连接查询时数据库会根据查询优化器的策略将多个表的数据进行合并然后进行过滤和选择。子查询要先执行内部查询然后再使用其结果进行外部查询。嵌套子查询需要多次扫描数据并且每次子查询都可能会触发独立的扫描操作这增加了开销。数据库优化器在处理连接查询时有更多的优化手段如排序合并连接、哈希连接和嵌套循环连接等。优化器可以根据统计信息和查询结构进行调整选择最优的执行计划。子查询有时不能充分利用优化器的优化策略特别是在嵌套子查询的情况下优化器可能会生成次优的执行计划。由于连接查询一次性扫描多个表并进行合并所以可以充分利用数据缓存减少I/O操作。子查询可能会导致多次扫描相同的数据特别是在嵌套子查询和相关子查询的情况下子查询每次执行都可能触发新的数据扫描增加了I/O开销。可以通过JOIN条件有效地过滤数据减少中间结果的大小。可能会产生较大的中间结果集需要多次筛选和处理增加了内存和计算的开销。order by的坑已知存在custom_id 和 order_date 的联合索引。在对数据进行custom_id 排序的情况下再对 order_date 进行排序。SELECT customer_id from orders by customer_id, order_date耗时 0.669 秒当调换排序顺序就无法走索引了此时针对custom_id的索引排序就是失效了。SELECT customer_id from orders by order_date,customer_id耗时 1.645 秒即order by也需满足联合索引的最左匹配原则order by后跟的排序字段是desc和asc 组合不论排序顺序是否和组合索引顺序一致必然会出现Using filesortSELECT customer_id from orders by customer_id desc, order_date ascorder by索引注意无过滤条件(无where和limit)的order by 必然会出现 Using filesort过滤条件中的字段和order by 后跟的字段的顺序不一致必然会出现 Using filesort也就是说order by需要遵循最左匹配原则order by后跟的排序字段是desc和asc 组合不论排序顺序是否和组合索引顺序一致必然会出现Using filesortwhere条件的值确定(where xx )且order by后跟了where条件的排序字段(order by 字段去除定值字段后剩余单字段)即使order by后跟的字段和组合索引字段顺序不一致也不会出现 Using filesortSQL优化大分页 limitMysql中常使用limit语句进行分页如mysql SELECT * FROM table LIMIT 5,10; //检索记录行6-15今天看到一个问题为什么以下两个查询语句的速度差那么多SELECT id from table limit 500000, 10; //0.1秒 SELECT * from table limit 500000, 10; //1.2秒执行第一条语句执行计划是index走Primary主键索引。第一行sql语句意思是查询以id为500001开始的10条内容执行第二条语句执行计划是All走的是全表扫描我们知道执行器实际上会将 select * 中的 * 符号扩展为表上的所有列因此第二行sql语句意思是查询表中第500001行开始的10条内容原因在使用limit的时候没有对字段进行排序的时候如果用id查走的是索引按索引的存储位置取数据* 是查全表按表中记录实际的存储位置取结果如果查询结果一样那么只是巧合而已。所以在不使用order by 排序时出现的结果实际上是不同的。对于大分页的场景可以优先让产品优化需求如果没有优化的有如下两种优化方式 一种是把上一次的最后一条数据也即上面的c传过来然后做“c xxx”处理但是这种一般需要改接口协议并不一定可行。方法1延迟关联采用延迟关联的方式进行处理减少SQL回表但是要记得索引需要完全覆盖才有效果SQL改动如下select * from xxx where id (select id from xxx order by id limit 500000, 1) order by id limit 10;方法2根据id主键进行排序将所有的数据根据id主键进行排序然后分批次取将当前批次的最大id作为下次筛选的条件进行查询。select * from xxx where id start_id order by id limit 10;通过主键索引每次定位到start_id的位置然后往后遍历10个数据这样不管数据多大查询性能都较为稳定updateupdate这种加锁的语句要确保 where 条件中带上了索引列并且测试确认该语句是否走的是索引扫描防止因为扫描全表结果全表加锁复杂查询select sum(amt) from _t where a 1 and b in (1, 2, 3) and c 2020-01-01; select * from _t where a 1 and b in (1, 2, 3) and c 2020-01-01 limit 10;如果是统计某些数据可能改用数仓进行解决如果是业务上就有那么复杂的查询可能就不建议继续走SQL了而是采用其他的方式进行解决比如使用ES等进行解决。分库分表当单表的数据量达到1000W或100G以后优化索引、添加从库等可能对数据库性能提升效果不明显此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担缩短查询的时间。数据切分可以分为两种方式垂直划分和水平划分。垂直划分垂直划分数据库是根据业务进行划分例如购物场景可以将库中涉及商品、订单、用户的表分别划分出成一个库通过降低单库的大小来提高性能。同样的分表的情况就是将一个大表根据业务功能拆分成一个个子表例如商品基本信息和商品描述商品基本信息一般会展示在商品列表商品描述在商品详情页可以将商品基本信息和商品描述拆分成两张表。优点行记录变小数据页可以存放更多记录在查询时减少I/O次数。缺点主键出现冗余需要管理冗余列会引起表连接JOIN操作可以通过在业务服务器上进行join来减少数据库压力依然存在单表数据量过大的问题。水平划分水平划分是根据一定规则例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致但是数据得以拆分从而提升性能。优点单库表的数据量得以减少提高性能切分出的表结构相同程序改动较少。缺点分片事务一致性难以解决跨节点join性能差逻辑复杂数据分片在扩容时需要迁移分区表(分片)说在前面目前分区表有几项限制只支持水平分区不支持垂直分区null值无法通过分区列来过滤分区个数也是有限的随着分区个数的增加分区表的性能会下降只能通过主键过着唯一列进行分区如果查询中没有分区列查询则无法通过分区列进行过滤想要重组分区开销较大总的来说一般是不建议使用分区表的不感兴趣的也可以不用看这部分内容了分区分区是把一张表的数据分成N多个区块。分区表是一个独立的逻辑表但是底层由多个物理子表组成。当查询条件的数据分布在某一个分区的时候查询引擎只会去某一个分区查询而不是遍历整个表。在管理层面如果需要删除某一个分区的数据只需要删除对应的分区即可。分区一般都是放在单机里的用的比较多的是时间范围分区方便归档。只不过分库分表需要代码实现分区则是mysql内部实现。分库分表和分区并不冲突可以结合使用。分片MySQL分片查询是指将数据分散在不同的服务器上并使用查询语句在多个服务器上并行查询以提高查询效率。首先需要准备多台MySQL服务器。每台服务器上需要有相同的数据表表结构和表数据也要相同。接着在应用程序中需要使用分片算法将数据分散到不同的服务器上。常用的分片算法有hash分片、range分片和lookup分片。hash分片是将数据根据hash值分散到不同的服务器上range分片是根据数据段的范围进行分片lookup分片是通过路由表将数据指向相应的服务器。最后需要使用MySQL集群中的代理节点来进行查询。代理节点收到查询请求后会将请求分发到不同的服务器上并行查询并将结果合并返回给应用程序。综上所述MySQL分片查询通过将数据分散到多个服务器上并在代理节点上进行并行查询可以提高查询效率提高系统的吞吐量。分区表类型range分区按照范围分区。比如按照时间范围分区CREATE TABLE test_range_partition( id INT auto_increment, createdate DATETIME, primary key (id,createdate) ) PARTITION BY RANGE (TO_DAYS(createdate) ) ( PARTITION p201801 VALUES LESS THAN ( TO_DAYS(20180201) ), PARTITION p201802 VALUES LESS THAN ( TO_DAYS(20180301) ), PARTITION p201803 VALUES LESS THAN ( TO_DAYS(20180401) ), PARTITION p201804 VALUES LESS THAN ( TO_DAYS(20180501) ), PARTITION p201805 VALUES LESS THAN ( TO_DAYS(20180601) ), PARTITION p201806 VALUES LESS THAN ( TO_DAYS(20180701) ), PARTITION p201807 VALUES LESS THAN ( TO_DAYS(20180801) ), PARTITION p201808 VALUES LESS THAN ( TO_DAYS(20180901) ), PARTITION p201809 VALUES LESS THAN ( TO_DAYS(20181001) ), PARTITION p201810 VALUES LESS THAN ( TO_DAYS(20181101) ), PARTITION p201811 VALUES LESS THAN ( TO_DAYS(20181201) ), PARTITION p201812 VALUES LESS THAN ( TO_DAYS(20190101) ) );在/var/lib/mysql/data/可以找到对应的数据文件每个分区表都有一个使用#分隔命名的表文件-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt -rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd ...list分区list分区和range分区相似主要区别在于list是枚举值列表的集合range是连续的区间值的集合。对于list分区分区字段必须是已知的如果插入的字段不在分区时的枚举值中将无法插入.create table test_list_partiotion ( id int auto_increment, data_type tinyint, primary key(id,data_type) )partition by list(data_type) ( partition p0 values in (0,1,2,3,4,5,6), partition p1 values in (7,8,9,10,11,12), partition p2 values in (13,14,15,16,17) );hash分区可以将数据均匀地分布到预先定义的分区中。create table test_hash_partiotion ( id int auto_increment, create_date datetime, primary key(id,create_date) )partition by hash(year(create_date)) partitions 10;分区的问题打开和锁住所有底层表的成本可能很高。当查询访问分区表时MySQL 需要打开并锁住所有的底层表这个操作在分区过滤之前发生所以无法通过分区过滤来降低此开销会影响到查询速度。可以通过批量操作来降低此类开销比如批量插入、LOAD DATA INFILE和一次删除多行数据。维护分区的成本可能很高。例如重组分区会先创建一个临时分区然后将数据复制到其中最后再删除原分区。所有分区必须使用相同的存储引擎。count(*) 和 count(1)哪个快按照性能排序是count(*) count(1) count(主键字段) count(字段)count(主键字段)的执行过程比如说id是主键字段。如果表里只有主键索引那么InnoDB 循环遍历聚簇索引将读取到的记录返回给 server 层然后读取记录中的 id 值并根据 id 值判断是否为 NULL如果不为 NULL就将 count 变量加 1。如果表里有二级索引时InnoDB 循环遍历的对象就不是聚簇索引而是二级索引。因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间所以二级索引树比聚簇索引树小这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小因此「优化器」优先选择的是二级索引。count(1) 的执行过程如果表里只有主键索引没有二级索引时。那么InnoDB 循环遍历聚簇索引主键索引将读取到的记录返回给 server 层但是不会读取记录中的任何字段的值因为 count 函数的参数是 1不是字段所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL因此 server 层每从 InnoDB 读取到一条记录就将 count 变量加 1。显然count(1) 相比 count(主键字段) 少一个步骤就是不需要读取记录中的字段值所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。但是如果表里有二级索引时InnoDB 循环遍历的对象就二级索引了。count(*) 的执行过程(mysql官方文档推荐)count(*) 其实等于 count(0)也就是说当你使用 count(*) 时MySQL 会将 * 参数转化为参数 0 来处理。所以count(*) 执行过程跟 count(1) 执行过程基本一样的性能没有什么差异。count(字段) 的执行过程采用全表扫描的方式来统计逻辑删除逻辑删除也称为软删除是一种在数据库中标记记录为已删除而不是实际从数据库中物理删除记录的方法。在MySQL中逻辑删除通常通过向表中添加一个额外的字段如deleted来实现该字段用于指示记录是否被删除。优点数据恢复逻辑删除允许在需要时恢复已删除的记录因为数据实际上并未从数据库中移除。审计和历史记录逻辑删除保留了完整的数据历史记录这对于审计和分析非常有用。数据完整性在某些情况下逻辑删除可以更好地保持数据的完整性和一致性尤其是在外键约束和关联数据存在的情况下。性能对于频繁删除和恢复操作的场景逻辑删除可能比物理删除更高效因为它避免了实际的删除操作和可能的索引重建。简化备份和恢复由于数据未被物理删除备份和恢复过程可能更简单因为不需要特殊处理已删除的数据。缺点数据膨胀逻辑删除会导致数据库中的数据量逐渐增加因为被标记为删除的记录仍然占用存储空间。查询复杂性会导致数据库表垃圾数据越来越多并且在查询时需要考虑逻辑删除的字段从而影响查询效率索引维护逻辑删除的记录仍然存在于索引中这可能会影响查询性能尤其是在大型表中。事务管理在处理逻辑删除时需要确保事务的一致性这可能需要额外的工作和注意。安全性逻辑删除可能不足以保护敏感数据因为数据仍然存在于数据库中只是被标记为删除。在决定是否使用逻辑删除时需要根据具体的应用场景和需求来权衡这些优缺点。在一些情况下结合使用逻辑删除和定期物理删除清理策略可能是一个有效的解决方案。因此我不太推荐采用逻辑删除功能如果数据不能删除可以采用把数据迁移到其它表删除表的办法。可以写个定时任务在半夜等业务较少时执行扫描表内已删除的数据将其迁移到删除表中但是也有一些大厂对delete这种的权限控制都较严格普通场景下一般不允许delete操作因此也就只能进行逻辑删除。