MySQL优化全攻略：索引、SQL与分库分表的最佳实践

SQL优化一般步骤

慢日志定位

通过慢查日志等定位那些执行效率较低的SQL语句
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
explain 分析SQL的执行计划

需要重点关注type、rows、filtered、extra。
type由上至下，效率越来越高
<ul>ALL：全表扫描
index：索引全扫描
range：索引范围扫描，常用语 '2021-01-01 00:00:00' and order_status = 10[/code]范围查询还有“IN、between”
相关原理可以看这篇文章 唯一索引范围查询
覆盖索引优化

覆盖索引是指 SQL 中 查询的所有字段，在这个二级索引 B+Tree 的叶子节点上都能找得到那些字段，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，就可以避免回表的操作。
asc和desc混用

1. SHOW PROFILES ;
2. SHOW PROFILE FOR QUERY  #{id};

复制代码

desc 和asc混用时会导致索引失效
避免更新索引列值

每当索引列的值发生变化时，数据库必须更新相应的索引结构，更新索引列值可能导致这些树结构的重平衡或重新构建，增加了额外的计算和I/O开销。
不等于、不包含不能用到索引的快速搜索

1. //函数计算
2. select * from t_user where length(name)=6;
4. //表达式计算
5. select * from t_user where id + 1 = 10;

复制代码

在索引上，避免使用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 进行排序。

1. KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

复制代码

耗时 0.669 秒
当调换排序顺序，就无法走索引了，此时针对custom_id的索引排序就是失效了。

1. select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

复制代码

耗时 1.645 秒
即order by也需满足联合索引的最左匹配原则
order by后跟的排序字段是desc和asc 组合（），不论排序顺序是否和组合索引顺序一致，必然会出现Using filesort

1. select * from _t where a=1 order by b desc, c asc

复制代码

order by索引注意：

• 无过滤条件(无where和limit)的order by 必然会出现 Using filesort
  
• 过滤条件中的字段和order by 后跟的字段的顺序不一致，必然会出现 Using filesort，也就是说order by需要遵循最左匹配原则
  
• order by后跟的排序字段是desc和asc 组合，不论排序顺序是否和组合索引顺序一致，必然会出现Using filesort
  
• where条件的值确定(where xx = )，且order by后跟了where条件的排序字段(order by 字段去除定值字段后剩余单字段)，即使order by后跟的字段和组合索引字段顺序不一致，也不会出现 Using filesort
  

SQL优化

大分页 limit

Mysql中常使用limit语句进行分页，如

1. select * from _order where shop_id=1 and order_status not in (1,2)
2. select * from _order where shop_id=1 and order_status != 1

复制代码

今天看到一个问题，为什么以下两个查询语句的速度差那么多？

1. SELECT customer_id from orders by customer_id, order_date

复制代码

执行第一条语句，执行计划是index，走Primary主键索引。

第一行sql语句意思是，查询以id为500001开始的10条内容
执行第二条语句，执行计划是All，走的是全表扫描

我们知道，执行器实际上会将 select * 中的 * 符号，扩展为表上的所有列，因此第二行sql语句意思是，查询表中第500001行开始的10条内容
原因：在使用limit的时候没有对字段进行排序的时候，如果用id查走的是索引，按 索引的存储位置 取数据，* 是查全表，按 表中记录实际的存储位置 取结果，如果查询结果一样那么只是巧合而已。所以在不使用order by 排序时出现的结果实际上是不同的。
对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式， 一种是把上一次的最后一条数据，也即上面的c传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行。
方法1：延迟关联

采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下

1. SELECT customer_id from orders by order_date,customer_id

复制代码

方法2：根据id主键进行排序

将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。

1. SELECT customer_id from orders by customer_id desc, order_date asc

复制代码

通过主键索引，每次定位到start_id的位置，然后往后遍历10个数据，这样不管数据多大，查询性能都较为稳定
update

update这种加锁的语句要确保 where 条件中带上了索引列，并且测试确认该语句是否走的是索引扫描，防止因为扫描全表，结果全表加锁
复杂查询

1. mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15

复制代码

如果是统计某些数据，可能改用数仓进行解决；
如果是业务上就有那么复杂的查询，可能就不建议继续走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分区

按照范围分区。比如按照时间范围分区

1. SELECT id from table limit 500000, 10; //0.1秒
2. SELECT * from table limit 500000, 10; //1.2秒

复制代码

在/var/lib/mysql/data/可以找到对应的数据文件，每个分区表都有一个使用#分隔命名的表文件：

1. select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

复制代码

list分区

list分区和range分区相似，主要区别在于list是枚举值列表的集合，range是连续的区间值的集合。对于list分区，分区字段必须是已知的，如果插入的字段不在分区时的枚举值中，将无法插入.

1. select * from xxx where id > start_id order by id limit 10;

复制代码

hash分区

可以将数据均匀地分布到预先定义的分区中。

1. select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
2. select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 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操作，因此也就只能进行逻辑删除。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！