当前位置：首页 > news >正文

说说SQL调优

news 2025/5/11 9:00:48

SQL调优主要可以从三个方面来讲，分别是硬件调优、架构调优和sql语句调优。

硬件调优

升级服务器硬件配置，例如cpu、内存和磁盘；

适当调大buffer pool的内存，调大connection的连接数

架构调优

主从架构读写分离，

搭建集群，

引入redis缓存，

分库分表

sql语句调优

定长的字符串字段，使用固定长度char类型，字符串长度动态变化的则使用varchar类型

时间类型字段尽量使用timestamp，占用字节数少，并且够用

一些枚举尽量使用int或者tniyint

禁止使用select *，需要查询什么列数据就查询什么；

尽量使用小表驱动大表；

limit大分页优化：select * from user where id>10000 limit 10;

建立合适的索引，提高索引的命中率，减少回表次数；

多个字段建立联合索引，查询时where后面的查询条件要满足最左前缀匹配原则；

sql执行慢不走索引，通过explain分析工具查看sql执行情况，对症下药修改sql；

索引失效，过explain分析工具查看sql执行情况，并修改sql语句，减少索引失效；

索引列上使用函数、like模糊查询以%通配符开头、范围查询未放到最后面

拓展知识

explain关键字的使用

Explain sql语句可以清楚的掌握sql的执行计划和sql使用索引的情况以及排序字段是否用到索引，排序是否在内存中排序的还是放在磁盘中排序的。

type列

type列为访问类型，包含：all、index、range、ref、eq_ref、const、system共7种。效率从高到低顺序为： system > const > eq_ref > ref > > range > index > ALL

all：全表扫描，最好不要出现这种情况，最慢的查询情况

index：扫描二级索引，如果Extra有Using index表示正在使用覆盖索引

range：范围扫描，当使用=、<>、>、>=、BETWEEN时，用常量比较关键字列时

ref：它返回所有匹配某个单个值的行，只有当使用非唯一性索引会发生，使用索引的最左前缀

eq_ref：join的时候最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生

const：用主键和唯一键进行查询时

system：const特例

possible_keys

查询中可能使用的索引列表。

key列

实际使用的索引名称

key_len列

联合索引或者主键索引实际走了多少个字节，实际使用到的索引长度 (即：字节数)

帮你检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

Extra

额外的信息，描述 MySQL 执行查询时的额外操作，例如：

Using where：查询中有 where 子句的过滤。
Using index：只使用索引，即索引覆盖，没有访问表数据。
Using index condition：有些搜索条件中虽然有索引列，但却不能使用到索引
Using filesort：查询需要额外的排序操作（MySQL 的内部排序算法）。

为什么小表驱动大表效率就高很多？

小表驱动大表创建连接的次数少，速度快

小表驱动大表需要加载到join Buffer连接缓冲池的数据小，速度快

Join Buffer(连接缓冲区)是优化器用于处理连接查询操作时的临时缓冲区。简单来说当我们需要比较两个或多个表的数据进行Join操作时，Join Buffer可以帮助MySQL临时存储结果，以减少磁盘读取和CPU负担，提高查询效率。需要注意的是每个join都有一个单独的缓冲区。

Block nested-loop join(BNL算法)会将驱动表数据加载到join buffer里面，然后再批量与非驱动表进行匹配；如果驱动表数据量较大，joinbuffer无法一次性装载驱动表的结果集，将会分阶段与被驱动表进行批量数据匹配，会增加被驱动表的扫描次数，从而降低查询效率。所以开发中要遵守小表驱动大表的原则。

分阶段匹配过程如下:

1、先把student表前15条数据读到join buffer中。

2、然后用scores表去匹配joinbuffer中的前15条。

3、记录下匹配结果。

4、清空joinbuffer。

5、再把student表后15条读取join buffer中。

6、然后用scores表去匹配join buffer中的后15条。

7、记录下匹配结果。