数据库SQL调优的几种方式汇总

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

char vs varchar

1、如果文本字段始终是固定长度的（例如，US 邮编，其始终具有“XXXXX-XXXX”形式的规范表示），那么推荐使用char。varchar 类型的长度是可变的，而 char 类型是一个定长的字段，以 char(10) 为例，不管真实的存储内容多大或者是占了多少空间，都会消耗掉 10 个字符的空间，通俗来讲，当定义为 char(10) 时，即使插入的内容是 'abc' 3 个字符，它依然会占用 10 个字节，其中包含了 7 个空字节。

2、CHAR 在快速、随机访问时效率很高。使用 VARCHAR，如果你想读取下一个字符串，不得不先读取到当前字符串的末尾，查找效率比较低。

3、char 长度最大为 255 个字符，varchar 长度最大为 65535 个字符

为什么varchar(255)而不是varchar(258),varchar(257)呢？

使用255是因为它是8位数字可以计算的最大字符数。它最大限度地利用了8位计数，而不需要另一个整字节来计算255以上的字符。

开启慢查询日志来定位查询慢的语句

启动慢查询日志，看哪些语句慢。默认是禁用慢查询日志的，--slow_query_log[={0|1}] 不指定或者为1，将启用日志。如果参数为0，此选项将禁用日志。--slow_query_log_file=file_name 来改变慢查询日志名称。

先找到慢查询的原因：

1、打开数据库慢查询日志，定义超时时间，比如超过2S就是慢查询

2、定位执行效率低的慢查询

show processlist

3、也可以通过explain来分析执行计划，explain得到的信息要主要关注：type字段，

possible_keys字段，key字段，key_len字段，rows，extra字段等

一般情况下慢查询的原因有以下这些：

没用索引或者索引失效

用了索引但是走了全表扫描

慢查询如何优化

索引+sql语句+架构优化+数据库结构优化

索引：避免索引失效

sql语句：优化insert语句：多条插入写一条，数据有序的插入；分页优化：通过子查询优化，比如查询2w起后面10条数据，先通过子查询拿到20000的id,然后通过主键索引，通过B+树定位到拿到的id对应的行数据，然后再向后取10条数据；

架构优化：数据库读写分离，主库写，从库读；

数据库结构优化：将字段比较多的表分解成多个表，将字段使用频率高的和字段使用频率低的分开，对于一些要进行联合查询的表，可以考虑建立中间表

合理使用关键字

比如mysql还有其他更危险的关键字，应该谨慎使用。其中包括INSERT DELAYED，它告诉MySQL立即插入数据并不重要（例如，在日志记录情况下）。问题是，在高负载情况下，插入可能会无限期延迟，导致插入队列停滞。

优化查询缓存

查询缓存相关的服务器变量：

query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit：单个查询结果能缓存的最大值，单位字节，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND

查询缓存相关的状态变量：
show gloable status like 'Qcache%' ;

innodb_buffer_pool_size：这是任何使用innodb的安装都要考虑的#1设置。缓冲池是缓存数据和索引的地方：使其尽可能大将确保大多数读取操作使用内存而不是磁盘。典型值为5-6GB（8GB RAM）、20-25GB（32GB RAM）、100-120GB（128GB RAM）。
innodb_log_file_size:这是重做日志的大小。重做日志用于确保写入速度和持久性，以及在崩溃恢复期间。在MySQL 5.1之前，很难进行调整，因为您既需要大的重做日志来获得良好的性能，也需要小的重做日志来实现快速的崩溃恢复。幸运的是，自MySQL 5.5以来，崩溃恢复性能有了很大提高，因此您现在可以拥有良好的写性能和快速的崩溃恢复。在MySQL 5.5之前，重做日志的总大小限制为4GB（默认为有2个日志文件）。这在MySQL 5.6中得到了提升。

max_connections:如果经常遇到“连接数过多”错误，则最大连接数太低。由于应用程序无法正确关闭与数据库的连接，因此经常需要比默认的151个连接多得多的连接。

查询缓存的优化路线

数据库SQL调优的几种方式汇总

在早期版本mysql均支持缓存，但是随着Redis等内存型高性能的缓存技术兴起，mysql已经抛弃自己的缓存功能，mysql8.0以后不再支持缓存功能。

适当使用索引

每个索引都需要与表中的行数成比例的空间，因此太多的索引最终会占用更多内存。由于每次写入都需要更新相应的索引，因此写入操作的性能也会受到影响。通过分析代码，可以发现一个平衡点。这因系统和实施而异。

查询（SELECT、GROUP BY、ORDER BY、JOIN）的列如果用了索引会更快
索引通常表示为自平衡的 B 树，可以保持数据有序，并允许在对数时间内进行搜索，顺序访问，插入，删除操作
设置索引，会将数据存在内存中，占用了更多内存空间
写入操作会变慢，因为索引需要被更新
加载大量数据时，禁用索引再加载数据，然后重建索引，这样也许会更快

索引何时会失效？

索引 (Index) 是帮助 MySQL 高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。

当索引不起作用时，会引起全表查询，索引就会失效，引起慢查询，有以下几种情况：

模糊查询，比如以%开头的like查询。
在索引列上操作or, not in , !=,<>,等操作
如果查询条件有or,并且or的前后条件中有一个列没有索引，则涉及的索引都不会用到

1、避免索引失效，减少%like这种索引失效语句

2、合理创建索引

3、分页查询优化，可以通过子查询，关联查询优化，比如要查询从10000行开始的10行数据，看似只返回了10条数据，但是数据库引擎需要查询10010数据，然后将前面的10000条数据丢弃，性能可想而知，针对这种情况，我们可以先定位到上次分页的id,然后对id做条件索引查询；或者将原有的sql拆成2步，首先查询出一页数据中的最小id,然后通过索引树，定位到最小id索引树节点位置，通过偏移量来读取后面的10条数据

4、避免使用select *

select *的查询过程：先在字段的二级索引B+树上，查出对应的主键id列表

然后进行回表操作，在主键索引中查询id对应的行数据

5.通过explain来分析SQL执行计划，看是否用到了索引

explain得到的字段key（使用到的索引）,rows（MYSQL估计为了查找目标行而需要读取的行数：）,possible_keys（查询可能会使用的索引）等主要关注type

Type:以怎样的方式查找表中的行的方式（性能逐渐变好）

All: 全表扫描

Index: 根据索引的次序进行全表扫描，若在extra出现using index表示使用覆盖索引，而非全表扫描

Range: 根据索引实现的范围扫描

Ref: 根据索引返回表中匹配某单个值的所有行

Eq_ref: 仅返回一个行，但需要和某个参考值作比较

Const,system: 根据具有唯一性的索引（比如主键）查找时，返回的是一行

NULL：类似于覆盖查询

Id:当前查询中，每个Select语句的编号

复杂类型的查询三种：

简单子查询：
用于from中的子查询
联合查询，union

注意：union查询的分析结果中会出现一个额外的匿名临时表

Select_type:

简单查询为simple

复杂查询：

Subquery:简单子查询

Derived:用于from中的子查询

Union:用于union第一个之后的select语句

possible_keys: 查询可能会使用的索引

Key: 使用到的索引

Key_len: 索引中使用的字节数，比如索引有70个字节数，只是用了20个

ref: 在利用key所表示的索引完成查询时，所用的列或某常量值

rows: MYSQL估计为了查找目标行而需要读取的行数：

Extra:额外信息

Using index:会使用覆盖索引，以避免访问表

Using where:服务器将在存储引擎检索后，再进行一次过滤

Using temporary:对结果排序时会使用临时表

Using filesort:对结果使用一个外部索引排序

例如：

mariadb [testdb]> explain select sname,age from stu union select tname,age from teacher\G;

*************************** 1. row ***************************

           id: 1
  select_type: PRIMARY
        table: stu
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra:

*************************** 2. row ***************************

           id: 2
  select_type: UNION
        table: teacher
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:

*************************** 3. row ***************************

           id: NULL   联合前两个表   匿名临时表
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.00 sec)
ERROR: No query specified