mysql数据库优化

1 工作中常见的优化代码任务场景

假设工作中领导告诉我们某个接口要50秒才能返回结果，要求我们优化某个接口返回数据的速度，此时首先通过接口确认server，再确认sql语句，如果sql语句执行速度很快，那么就说明是server的业务代码出现的问题

如果是server出问题，那就要想办法精简代码，能用中间件实现的功能就用中间件实现，这就得针对具体业务场景具体分析，存在一定的经验之谈

如果确认是sql语句执行速度很慢，那就要针对sql语句进行优化

2 EXPLAIN

给sql语句添加前缀EXPLAIN，可以查看当前执行的SQL语句的执行情况,索引命中,行扫描情况,索引类型等待信息,可以提供开发者用来优化SQL的依据

字段 释义
id 选择标识符
select_type 表示查询的类型
table 输出结果集的表
partitions 匹配的分区
type 表的连接类型
possible_keys 查询时，可能使用的索引
key 实际使用的索引
key_len 索引字段的长度
ref 列与索引的比较
rows 扫描出的行数(参考)
filtered 按表条件过滤的行百分比
Extra 执行情况的描述和说明

3 SHOW WARNINGS

show warnings需要配合explain使用，同时执行之后，可以得到sql语句的执行情况，以及针对这条sql语句，mysql提供的优化建议

把mysql提供的优化建议粘贴出来，结果如下，可以看出，官方不建议使用，而是把展开，指定具体的库名、表名、字段名，再给上别名，但实际开发时如果需要这样严格准守，许多人都受不了（笑），大部分情况下给个字段名，多个字段用逗号隔开就完事了

4 Explain返回的字段说明

4.1 id

查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序

    - id相同，执行顺序从上往下

    - id不同，id值越大，优先级越高，越先执行

举例：

这是一个连表查询，id显示了它们是顺序执行，如果存在子查询，那么子查询的id会比外层查询的id大

4.2 select_type

这个属性标识执行的查询语句的类型和执行顺序，与id属性相依相存

    1. SIMPLE(简单SELECT，不使用UNION或子查询等)

    2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

    3. UNION(UNION中的第二个或后面的SELECT语句)

    4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

    5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

    6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

    7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

    8. DERIVED(派生表的SELECT, FROM子句的子查询)

    9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

举例：

下面是一个带子查询的查询语句，结果如图

4.3 table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

4.4 type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL

从左到右，性能从差到好，能把sql语句的type优化为ref、eq_ref、const这三个级别就算很完美了，绝对不能是ALL

类型 说明
ALL Full Table Scan， MySQL将遍历全表以找到匹配的行
index Full Index Scan，index与ALL区别为index类型只遍历索引树
range 只检索给定范围的行，使用一个索引来选择行
ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

4.5 possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

4.6 Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，显示为NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

4.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好 (预估)

4.8 ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

4.9 rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

4.10 filtered

表示执行sql语句时实际用上的行数除以扫描的行数的百分比，通常要为100才合适

4.11 Extra

包含不适合在其他列中显示，但是十分重要的额外信息，适用于sql特别长的时候，分析sql语句使用，如果是几十行的sql，一眼就能看出来这些信息，这个属性就没什么用

  1. Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”

  2. Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。

  3. Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。

  4. Using where :表明使用where过滤

  5. using join buffer:使用了连接缓存

  6. impossible where:where子句的值总是false，不能用来获取任何元组

  7. select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  8. distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

5 实际开发时优化sql场景

实际开发中可能遇到上千行的sql语句，几百个字段，这种语句通常是因为堆需求堆出来的，后面接手的人又不好改，因为一改就要跟着改entity，跟着改server，有可能改的时间比整个项目推倒重来耗费的时间还要久，只能不断在原基础上增加字段

5.1 不改动sql语句情况下的优化手段

硬件上：增加硬件，但这种花钱的事情一般并不能得到领导或者甲方的同意

软件上：自己在jvm上增加一条线程，提取储存查询数据并放到一个map中，当用户执行查询时直接从这个map中取值，实现“查询提速”

5.2 改动sql语句情况下的优化手段

  • 拓展：添加索引实现sql语句优化

    根据EXPLAIN所给信息，找到type为ALL的表，给它的主键id增加索引

    栏位设置为主键id，名字可以不用管，或者按照“index_库名_字段名”给上，索引类型设为默认，索引方法设为“BTREE”

    现在再执行一次EXPLAIN，可以发现type变了，rows也减少了

优化思路

    1. 使用Explain来确诊慢查询的sql语句 查看type和rows

    2. 调整sql执行顺序，降低子查询的结果范围（如果查询语句存在子查询，要把子查询的结果集降到最少，因为子查询结果越多，就会导致最终查询结果越多）

    3. 对于经常使用查询的字段，建立索引，但索引不是越多越好，索引会降低insert和update的效率

    4. 只要一个查询很慢的sql语句发现了ALL类型，就要想办法改掉，比如增加索引

    5. sql执行模糊查询时，如果需要优化模糊查询，那么就只使用右like(例如'玄幻%'），而不是两个%，因为左like会导致索引失效

        如果一定要用左like，而且还要求速度快，那么mysql解决不了，需要换别的方法

优化指标：使用EXPLAIN指令，想办法减少sql语句的rows值，以及优化type类型

posted @ 2021-10-10 22:59  夏·舍  阅读(48)  评论(0)  编辑  收藏  举报