MySQL中的相关优化

CocoaAndYy

已于 2024-07-29 22:48:32 修改

阅读量805

点赞数 9

文章标签： mysql android 数据库

于 2024-07-29 22:47:54 首次发布

本文链接：https://blog.csdn.net/m0_45164511/article/details/140782721

版权

1.索引上的优化

2.MySQL中的排序优化

3.使用order by和group by的优化

1.2根据非主键字段排序的分页查询（关键）

7.Join关联查询优化

8.in和exists优化

9.count(*)查询优化

1.索引上的优化

首先，需要了解有哪些情况会导致索引失效。索引失效指的是，在查询时，不通过用户建立的索引进行查找。

在索引列上做以下操作（计算、调用函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描；
如果使用了范围条件，那么范围条件中右边的列会失效；
在使用不等于（！=或者<>），not in，not exists的时候，会导致索引失效而转向全表扫描；is null，is not null一般情况下也无法使用索引，null可能在索引中的单端集中存储；
like模糊查找以通配符开头，mysql索引会失效，从而变成全表扫描；解决like`%字符串%`索引不被使用的方法：1）使用覆盖索引，查询字段必须是建立覆盖索引的字段；2）如果不能使用覆盖索引，则可能需要借助搜索引擎。
在SQL语句的书写规范上，如果字符串不加单引号，会导致索引失效；
范围查询可能导致索引失效，也可能不失效。由Mysql内部优化器决定（Mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引）

总结，索引失效的情况：

在索引列做计算、函数、类型转换
范围查询右边的列会索引失效
使用!=或者<>，not in，not exists
使用is null，is not null
like以通配符开头
字符串不加单引号

2.MySQL中的排序优化

Mysql支持两种方式的排序：filesort和index

Using index是指Mysql扫描索引本身就能完成排序，效率较高；

Using filesort指的是通过sort buffer（排序缓冲区）对数据进行排序，效率较低。

filesort主要分为两种：

1）单路排序：一次性取出满足条件行的所有字段，然后在sort_buffer中进行排序；

2）双路排序（又叫回表排序模式）：首先根据相应的条件取出排序字段和可以直接定位行数据的行ID，然后在sort_buffer中排序，排序完后需要再次取出其他需要的字段。（需要回表）

注意：Mysql通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式。如果排序字段小于max_length_for_sort_data，则使用单路排序；否则，使用双路排序。

注意：如果在extra列中出现Using filesort，需要考虑索引优化，考虑修改索引为联合索引、覆盖索引等。

3.使用order by和group by的优化

优化总结：

1.order by满足两种情况会使用Using index：（SQL语句满足索引最左前缀原则用index排序）

1）order by语句满足索引最左前缀原则；

2）使用where子句与order by子句条件组合满足索引最左前缀原则。

2.如果order by的条件不在索引列上，就会使用filesort。

3.group by与order by很类似，其实质是先排序后分组，遵循索引创建顺序的最左前缀法则。对于group by的优化，如果不需要排序就可以加上order by null禁止排序。

注意：where优先级高于having，能写在where中的限定条件就不要去having限定了。

4.索引设计原则

1.代码先行，索引跟上

2.联合索引尽量覆盖条件

3.不要在小基数字段上建立索引

4.长字符串可以采用前缀索引

5.where与order by产生冲突时，优先对where建立索引

（因为大多数情况，where可以以最快速度筛选出想要的少部分数据，然后可以降低排序的成本）

6.基于SQL慢查询做优化

5.慢查询的优化

1.概念

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录响应时间超过阀值的语句。

具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。

默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件和数据库表。

2.部分参数

long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

log-queries-not-using-indexes：该系统变量指定未使用索引的查询需要被记录到慢查询日志中（可选项）。

log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库。

优化方法：开启慢查询日志，查看日志中运行时间较长的SQL语句，分析是否可以重写该语句，或者是否为该SQL语句中的字段加索引。（仅提供参考，满查询日志可以帮助我们找到慢SQL，然后具体问题具体分析）

6.分页查询优化

很多时候，我们的业务系统实现分页功能会用如下SQL实现：

mysql> select * from employees limit 10000,10;

表示从表employees中取出从10001行开始的10行记录。看似只查询了10条记录，实际这条SQL是先读取10010条记录，然后抛弃前10000条记录，最后读到后面10条想要的数据。

因此如果要查询一张大表中比较靠后的数据，那它的执行效率是非常低的。

以下是分页查询的优化技巧：

1.1自增且连续的主键排序的分页查询

需满足两个条件：1）主键自增且连续；2）结果是按照主键排序的。

优化前的SQL语句：

mysql> select * from employees limit 90000,5;

该SQL表示查询从第90001开始的五行数据，没单独添加order by，表示通过主键排序。

优化后：

mysql> select * from employees where id > 90000 limit 5;

这条SQL语句会从覆盖索引中直接拿到90000后的5条数据，而不是拿出90005条数据再筛选出后面5条。

但是，这种优化在很多场景并不适用，因为表中可能某些记录被删后，主键空缺，导致结果不一致。

1.2根据非主键字段排序的分页查询（关键）

优化前：

mysql> select * from employees order by name limit 90000,5;

这种情况，Mysql可能不走name的索引，因为扫描整个索引并查找到没加索引的行（可能产生回表）的成本比扫描全表的成本更高，所以优化器放弃使用索引。

优化的关键是：让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键（在name索引中查询），然后根据主键查到对应的记录（在聚簇索引中查询）。(先通过name的索引查出主键，然后利用主键去聚簇索引找到结果)

优化后：

mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

7.Join关联查询优化

mysql常见的表关联算法有两种：

1）Nested-Loop Join（嵌套循环连接算法）；

2）Block Nested-Loop Join（基于块的嵌套循环连接算法）

当关联字段有索引时，使用Nested-Loop Join，因为Nested-Loop Join可以经过一次扫描直接找到结果。

当关联字段没有索引时，使用Block Nested-Loop Join，通过join_buffer来减少扫描次数，但是增加了比较次数（内存计算相比于IO比较快）。

Block Nested-Loop Join的实现：把驱动表的数据读入到join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来和join_buffer中的数据做对比。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下驱动表的所有数据的话，策略很简单，就是分段放。

优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表；当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

使用了 NLJ算法：一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

关联SQL的优化：

1.关联字段加索引，让Mysql做join操作时尽量选择NLJ算法；

2.小表驱动大表。（小表：过滤完后，数据量小的那个表是小表）

8.in和exists优化

小表驱动大表，即小的数据集驱动大的数据集

9.count(*)查询优化

常见优化方法：

1.将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

2.增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

补充说明：
对于MyISAM：因为MySQL对该引擎的count有对应优化，精确的行数会被储存在存储引擎中，因此此类没有where条件的单表总行数查询会迅速返回结果。
对于InnoDB：因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。