前言
在面试时，有的面试官会往深了问一些sql相关的八股文的内容，其中最重要且问的最多的就是sql调优。很多人听到sql调优这个词就觉得很高大上。的确！但是我们作为一个普通的程序员的话，90%的情况不会用到sql调优，大多数情况只需要理解好八股文的一些内容即可。下面我罗列出一系列常见的八股文内容供大家学习参考。
面试官：“听说你会sql优化，那么说说sql优化的方式有哪些？”
这时候，不要一上来就说什么索引优化，子查询优化等。这样显得你好像是背的。你应该按照“排查问题–>发现问题–>解决问题”的方式去说。
这里给出一些方案来回答这个问题。（排查问题–>发现问题–>解决问题）
1.排查问题
我们怎么排查问题，也就是怎么找出慢sql? 这里给出四种方案
- 肉眼去看/压力测试。我们通过一些测试点击，或者通过一些测试工具，发现一些接口性能不达标，甚至好几秒才出结果的接口，这时我们就需要去分析这个接口慢的原因，是不是某个sql太慢导致的。
- 慢查询日志。慢查询日志记录了所有执行时间超过指定参数的所有sql语句的日志。mysql的慢查询日志没有开启，需要在配置文件中(/etc/my.cnf)配置一些信息（具体怎么配置有兴趣自己去看，一般面试官也不会问），可以配置开启慢日志记录，配置多少秒的查询属于慢日志查询。这样mysql就会将查询超过这个时间的sql进行记录。我们就可通过这个日志去查询是哪些sql需要优化 。
- sql的执行频率。mysql可以通过
show global status like 'com__'进行查看当前数据库的增删改查的次数，通过增删改的次数，我们大致可以确定，这个数据库是否需要优化。（一般咱们用不上，这个面试的时候提一嘴就好） - 使用查询分析工具：
有一些开源和商业工具可以帮助分析和优化 MySQL 查询
1）pt-query-digest（Percona Toolkit）：用于分析慢查询日志，生成报告。
2）MySQL Enterprise Monitor：提供实时监控和查询分析功能。
3）第三方监控工具：如 New Relic、Datadog、PMM（Percona Monitoring and Management）等。
这些工具有兴趣了解下就行，不需要了解具体怎么用。
以上四种就是排查问题的思路。主要着重第1，2个就行。其他的提一嘴。
2. 发现问题
排查出来的确是某个sql执行过慢导致的，这时我们要去确定，这条sql执行慢的原因 ：调用了子查询导致的？没加索引导致的？没走索引导致的？那么发现问题这一块可以给面试官详细说一下。咱们发现问题主要有三种方法
2.1 explain 关键字（explain 执行计划）
非常重要！非常重要！非常重要！答错了基本就凉凉了。这里不具体说这个关键字的作用以及用法了，想要详细了解的自行去b站或者blog搜索相关的教学内容，这里只给需要给面试官说出口的一些内容。
我们通过:
explain select … from …
来得到相关的“执行计划”。执行计划罗列出每个子查询（如果存在复杂的连接查询的话），罗列出表是如何连接以及连接顺序，以及是否走索引等,如下图所示：
这里具体的我们就要说下explain的每个字段的含义，其中有12个字段，只有部分字段比较重要要给面试官说清楚：
| 字段名称 | 字段含义 | example |
|---|---|---|
| id | select 查询的序列号，表示查询中执行select子句或者操作表的顺序。其中id相同，执行顺序是从上到下，id不同，值越大，越先执行 | |
| select_type | 表示select的类型，常见的取值有：1.SIMPLE(简单表，不使用表连接或者子查询）、2.PRIMARY(主查询，即外层的查询）、3.UNION(union中的第二个或者后面的查询的语句、 3.SUBQUERY（select/where之后包含子查询） | |
| ref | 表示当前使用索引列等值查询时，与索引列进行等值匹配的对象的信息 | |
| rows | 预估需要读取的记录条数（比如从10000条记录中查询2条，这个10000条记录就是预估需要读的条数）这个rows越小越好 | |
| filtered | 某个表经过搜索条件过滤之后，剩余的记录的条数的百分比，这个值越大越好 | |
| type（重点） | 表示连接类型，性能由好到差的连接类型为： NULL,System,const,eq_ref,ref,range,index,all | 他们的具体意思见表格下面的注释 |
| possible_key | 显示可能用在这张表上的索引，一个或多个 | |
| key(重点） | 实际使用到的索引，如果为吧NULL,此时没有使用索引 | |
| key_len(重点） | 表示索引中使用到的字节数，该值为索引字段的最大的可能的长度，并不是实际的使用的长度，在不损失精确性的前提下，长度越短越好 | |
| extra | MySQL解决查询的详细信息,比如这条sql是否用到了where过滤，是否用到索引等 | 具体例子见表格下面的注释 |
1）type注释：
- NULL: 表示查询不访问任何表，eg: select ‘A’ 。一般不可能不访问任何表。
- System：如果一个sql语句是这个性能的话，一般来说性能最高的。当只有一条数据的系统表的时候会出现这个System。所以一般也不可能出现这个情况。
- const：表示当前查询通过主键索引或者唯一索引定位到表中的唯一一条记录（比如selectById方法查询），这种查询的速度也是非常高的，仅次于system查询。
- eq_ref：表示本次查询也是走了主键索引或者唯一索引，但是还需要进一步进行表关联查询。
- ref：表示本次查询走了普通索引。
- range：表示本次查询利用索引进行范围查询。
- index：表示本次查询利用了索引进行全索引扫描。
- all：本次查询进行了全表扫描，是性能最低的查询。
2 ）extra注释：
extra表示额外信息，这一列是非常重要的：
- 比如当我们看到extra这一列出现“using index”时，表示我们当前这个查询使用了索引覆盖，比如我们上面的这个例子最后就使用了索引覆盖。
- 当我们看到“using filesort”时，表示当前查询的排序使用了文件排序，文件排序性能是比较低的，那就要考虑是否要优化了。
- 当我们看到extra列显示“using temporary”时，表示查询使用了临时表。比如select distinct查询一般就会使用到临时表，MySQL会创建一张临时表，利用这张临时表进行去重。
特别注意：这里的explain会因为同一条sql语句的不同查询的范围以及查询的表里的数据的多少等情况出现不同的结果。比如：当我们的where只涉及到一条记录的时候，这时候type可能会出现const的情况，但是如果where过滤之后还是得到一张表中大多数的结果的时候，这时候可能是all的性能情况。
2.2 profile 详情（一般用的不多，了解即可，面试的时候提一嘴就好）
show profile 能够在做sql优化的时候帮助我们了解时间都耗费在哪里去了，通过have_profile参数，能够看到当前Mysql是否支持profile操作。然后开启profile。记住，开启用完之后记得关闭profile，因为profile是一个非常耗性能的操作。具体的操作流程如下：
SHOW VARIABLES LIKE 'profiling';
--如果返回的Value是OFF，你需要开启它：
SET profiling = 1;
开启Profile后，MySQL会记录你执行的每条SQL语句的性能分析信息。
具体如下
show profile 这条命令会列出最近执行的一系列查询及其查询编号。找到你刚才执行的查询对应的编号，然后用以下命令查看详细的性能分析：
# 此处的Query_ID就是show profiles 查询到的Query_ID，比如上面的select * from t_user 的query_id就是80
show profile cpu,block io for query Query_ID
其他参数：
ALL：显示所有的开销信息。
BLOCK IO：显示块IO开销。
CONTEXT SWITCHES：上下文切换开销。
CPU：显示CPU开销信息。
IPC：显示发送和接收开销信息。
MEMORY：显示内存开销信息。
PAGE FAULTS：显示页面错误开销信息。
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS：显示交换次数开销信息。
我们可以给定不同的参数来查看我们需要的信息。
最后记得关闭profile:
set profiling=0;
2.3 业务逻辑分析
上面都是通过工具分析sql慢的原因，我们开始的时候，可以直接通过实际的业务逻辑来判断这条sql是否合理，比如，子查询过多，连接查询过多导致等。有时候直接通过看sql就能定位出慢的原因，这样就可以针对问题进行优化。在进行测试，最后看结果是否符合预期。
3.解决问题
解决问题就是所谓的“sql优化”了，这里的sql优化的内容有：
- 索引优化（最重要）
- sql语句优化
- 系统、参数、硬件等方面的优化（实际中不要轻易使用）
- 加缓存（具体可以聊到redis的缓存和mysql之间的设计）
- 读写分离、分区分表
3.1 索引优化
索引是什么？
索引是帮助Mysql高效的获取数据的数据结构，它既有优点，也有缺点。
优点：提高数据库的检索效率，降低数据库的IO成本，通过索引对数据库进行排序，降低数据库的排序成本，降低CPU的消耗。
缺点：索引也需要占用空间，索引大大提高查询效率的同时也会降低更新表的速度，因为每次更新和插入数据的时候，会构建索引树。
3.1.1 索引的分类
索引根据不同的角度，可以划分为不同的类型，以下根据物理结构、功能逻辑、存储方式将索引分类
一、索引的物理结构分类：
Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包括如下几个：
1.B+Tree 索引：最常见的索引，大部分的存储引擎都支持这个索引
2. Hash索引
3. R-Tree(空间索引）
4. Full-Text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式，类似于ES
其中，InnoDB仅仅支持B+Tree索引，这个也是用的最多，问的最多的索引，其他的索引结构了解即可。平常所说的索引，如果没有特别的说明，都是指B+树 结构的索引
MyISAM存储引擎 支持的索引比较多，有B+Tree、R-Tree、Full-Text
Memory存储引擎支持 B+Tree和Hash索引
二、索引的功能逻辑分类
| 名称 | 作用 | 数量 | 英文名称 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个。也可以自己指定，比如联合主键索引，自己指定了就不会自动创建了 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定的数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本的关键词而不是一个比较索引中的值 | 可以多个 | FULLTEXT |
三、索引的存储方式分类
| 分类 | 含义 |
|---|---|
| 聚集索引（主键索引） | 将数据存储和索引放到一块，索引结构的叶子结点保存了行数据 |
| 二级索引（非主键索引都是二级索引） | 将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键 |
3.1.2 索引的结构–B+Tree索引
面试的时候，问索引的话，很多时候会问，为什么需要用B+tree树索引？B+树相对其他树结构的优点有哪些等问题，下面就给大家整理下B+Tree的结构和特点。
1.B+树的结构特点：自行观看黑马的这个视频：B+tree的数据结构
2. B+树的特点总结：
1）B+树中，叶子结点存放数据，非叶子节点是用来索引（也就是你建立索引的时候，指定的那个字段的值就是索引值），不会存储数据ROW。非叶子节点的索引总能在叶子节点中找到。
2）当叶子节点向上裂变的时候，叶子节点不会像B树那样直接裂变到上面，而是复制一份到上面。
3）B树的节点都挂了数据，所以可以分配为键的空间就少了，而B+树的非叶子节点没有存储数据，所以可以用来存放更多的键（这点是B+Tree相比较B树的优点，面试的时候可以提一嘴）
4）聚集索引（也就是主键索引，我们建立primary key的时候，就会建立一个主键索引，一张表中只能有一个主键和主键索引。非主键索引也就都叫非聚集索引）叶子节点下面挂的都是Row(一行数据），非聚集索引的叶子节点下面挂的是主键的值，以供回表查询。
5) 叶子结点之间会用单向链表链接（Mysql是形成双向链表）
3.为什么InnoDB存储引擎会选择使用B+Tree结构
1）相对于二叉树，层级更少，搜索效率更高（如果索引数据有序的话，二叉树容易形成单链，但是B+树不会）。
2）对于B-Tree,B-Tree的无论是叶子结点还是非叶子结点，都会保存数据，这样就会导致一页中存储的键值减少，指针跟着减少，要同样保存大量的数据，只能增加树的高度。
3）相对于Hash索引，B+树支持范围匹配和排序操作。
3.1.3、索引的设计原则（什么情况下适合建立索引）
前面了解了索引的分类以及索引的结构之后，下面来说说什么情况下适合建立索引，这块也是索引优化的重点。
索引不是越多越好，不是想加在哪里就加在哪里，很多时候即使加了索引，查询也可能不会命中索引树，结果查询不仅没提速，反而浪费磁盘的存储空间（因为索引也是占用空间的），甚至会对查询和修改等操作降速（因为插入修改会重新调整索引树）。索引设计的最终原则就是------通过索引让查询变快。下面列举了一些索引的设计原则，只要遵循下面的原则，一般情况下设计的索引都是符合规则的索引。
一、索引的创建方式
创建索引的方式有多种，大致可以分为：1.创建表的时候创建索引，2.创建表之后创建索引。
这里不做重点详解，有需要的可以去网上查询。这里稍微记住下第二种创建索引的方式，有可能会问：
ALTER TABLE your_table_name ADD INDEX your_index_name(column1, column2, column3,......);
CREATE INDEX your_index_name ON your_table_name(column1, column2, column3,......);
二、索引的设计原则
| 设计原则 |
|---|
| 针对数据量大的，且查询比较频繁的表建立索引（100多万以上的数据才算比较大） |
| 针对常用作查询条件（where)、排序（order by)、分组（group by) 操作的字段建立索引 |
| 尽量选择区分度高的列作为索引，尽量建立唯一索引，唯一索引不仅区分度高，效率还高 |
| 如果是字符串类型的字段，字段的长度较长，可以根据字段的特点建立前缀索引 |
| 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，防止回表查询，提高查询效率 |
| 要控制索引的数量，索引并不是越多越好，索引越多，维护索引结构的代价就越大，会影响增删改的效率 |
| 如果索引列不能存储NULL值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含NULL值时，他可以更好确定哪个索引更有效的用于查询。 |
3.1.3 索引的使用原则 （怎么才能走索引｜重点 🌟）
下面的结论也是问题索引失效的原因
说完了什么时候应该加索引。有时候即使按照规则加了，不当的sql也会导致索引失效，那么加了索引应该怎样才能防止索引失效呢？下面就是索引使用的常见原则
| 原则名称 | 注意事项 | 举例 | 应用 |
|---|---|---|---|
| 最左匹配原则（重点） | 如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并不能跳过索引中的列 | index(a,b,c) 做where 查询的时候，必须where a and b/where a/where a and b and c/where a and c才行，如果跳着查询，例如：where b,c/where c 等，都会导致索引失效。 如果查的是a and c，也会生效，不过仅仅只有a列会走索引，c列不会走索引。这里索引的放置和位置顺序没关系，只要存在就行。例如 b,a,c /c,a 这样不会失效，也是正确的。 | 平时我们用mybatis做sql拼接的时候就要注意了，拼接where条件的字段尽量将经常用于过滤的字段放在联合索引靠前的位置，且和mybatis里if的顺序一致。这样可以尽量的避免因为拼接不到最左字段导致索引失效 |
| 范围查询 | 联合索引中，出现范围查询（>,<)等，范围查询右侧的索引会失效（是右侧的失效，自身的不会失效）。但是>=,<= 则不会失效 | …where a = 1 and b > 30 and c = 20。这时，a,b不会失效，c就会失效。这里就会顺序有关系了，不等放在哪里就会截断后面的索引。 | 同上，做mybatis动态拼接sql的时候，就要注意这个原则 |
| 索引的列运算 | 不要在索引列上进行运算操作,否则会失效 | select * from tb_user where substring(phone,10,2) = '15’这里就对列phone做了运算，如果phone添加了索引，就会失效 | 可以将运算留在代码层运算 |
| 字符串不加引号 | 字符串类型的字段不 加上引号就会导致索引失效 | eg phone 是字符串类型，当在查询的时候不加索引，索引将失效：select * from tb_user where phone = 123456 | 开发的时候注意这个细节就行 |
| 模糊查询* | 对于一个索引列，如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，则会失效 | like '%工程%‘ 会失效 like '工程%‘不会失效 like ‘%工程’ 会失效 | 开发的时候，注意有索引列的想要不失效，尽量不要用like头部模糊匹配 |
| or条件连接 | 用or分隔开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引列都不会生效 | 简单而言，用or连接的条件必须都要有索引: where a = 1 or b = 2，如果 a或者任意一个没有索引，则都不会生效 | 想要用or做条件判断，前后字段尽量加上索引 |
| 数据的分布影响 | 如果mysql优化器评估走索引比走全表更加慢，则不会走索引 | 优化器会对sql进行评估，假如一条查询会查询出所有的记录(select * from tb_table),此时涉及全表扫描，因为我需要的数据几乎涉及到所有的记录了，这时候走索引反而更慢。 | 想要通过sql做统计的时候，eg select count(*) from … 如果过滤的之后还是涉及到大多数的数据，那么此时这个sql可能会非常慢，这时候，可以适当的缩小过滤的范围或者调整下过滤的条件，防止一次过滤掉的记录太少，参与统计的记录记录过多而不走索引。 |
| sql提示 | sql提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的 | 使用方法： select * from tb_user use index(idx_user_pro) where …(建议，不一定会生效） select * from tb_user ignore index(idx_user_pro) where…(忽略某个索引，不一定会生效） select * from tb_user force index(idx_user_pro) where …(强制要求使用某个索引，一定会生效） | 有时候因为某些原因没有走我们的索引，可以使用这个sql提示功能，让他走索引，提高查询效率。或者不想让它走某个索引等 |
| 覆盖索引 | 尽量使用覆盖索引（select 的时候，使用到了索引，然后返回的列的字段，在该联合（或单列）索引中都能找到），这样能够防止回表查询。 | 当需要返回的字段都在索引当中的时候，那么该查询不会“回表查询”，反之会回表查询。因为创建的的联合索引属于二级索引，二二级索引的节点本身挂着联合索引的字段的值，故不需要回表查询，当存在一个或者多个字段不属于联合索引的时候，那么在对应的节点处是找不到相应的字段的，于是他会找到聚集索引，然后去聚集索引中找到这个记录返回。 | 尽量覆盖索引查询，或者通过主键查询eg:selectById（Long id); |
| 前缀索引 | 当字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变的很长，查询的时候，浪费大量的磁盘IO,影响查询效率，此时可以只将字符串的一部分前缀建立索引，这样可以大大的节约空间，从而提高效率 | create index index_name on table_name(column1(n),column2(n)) | 长的字段注意加上这个长度的限制。 前缀的长度怎么取? 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性。说白了，尽量让参加索引的那段的区分度尽量高 |
| 单列索引/联合索引 | 在业务场景中，如果存在多个查询条件，考虑对于查询字段建立索引的时候，建议建立联合索引，而非单列索引。 多条件联合查询的时候，Mysql优化器会评估哪个字段的索引的效率更高，会选择该索引完成本次查询，如果你发现这个索引没有被舍弃的高，那么你可以使用上面的sql提示去建议优化器使用你指定的索引。 | 这里注意了： 1.建立多个单列/联合索引的时候，一次查询不会走多个索引树，只会选择其中一个，也就是explain里的key 永远只有一个或零个。 2.联合索引建立的时候例如：index(A,B)，这里构建索引树的时候，会先对A进行排序，再对B进行排序 | 能够用联合所有不要用单列索引 |
以上就是索引使用常见的注意事项，这也是mysql优化的一部分内容，面试的时候，讲到索引优化的时候，可以将这些大致罗列出来。或者，有时候面试官会问，索引失效的场景的时候，也可以将里面导致索引失效的场景罗列出来。既然我们使用索引有这么多注意（优化）的地方，创建索引也是有一套原则的，下面就来列举下创建索引的一些原则。
3.2 sql语句优化
sql 除了索引优化之外，还可以根据业务来优化语句，这里的内容比较分散，没有固定的招式，只能一点点的积累，不然就是搞懂mysql的底层查询逻辑（显然这个大部分人还是把握不住的）。这里我从增、改、查来罗列一些可以优化的地方
3.2.1 sql insert新增优化
| 优化内容 |
|---|
| 尽量批量插入：因为每次插入时都会与数据库网络建立连接，建立传输从而造成极大的损耗，故而批量插入是最好的 |
| 尽量主键顺序插入：因为在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（ITO),如果主键乱序插入，就是导致页分裂，从而降低数据的插入速度（页分裂不了解的自行搜索） |
| 大批量插入数据：如果一次需要插入大批量数据，使用insert语句插入的性能比较低，此时可以使用Mysql数据库提供的load指令进行插入 |
3.2.2 sql update修改优化
| 优化内容 |
|---|
| InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且改索引不能失效，否则会升级成表锁。也就是说where后面的字段不是索引，则会走表锁。所以为了防止升级为表锁，修改的时候尽量用updateById()。 |
3.2.3 sql select 查询优化
查询优化是mysql语句优化非常重要的内容，大部分的sql优化语句优化都发生在查询优化上。这不仅仅包含查询，还包含主键的设
| 优化内容 | 解释 |
|---|---|
| 主键设计原则 | 1.满足业务需求的情况下，尽量降低主键的长度，因为第一，太长占用太多磁盘空间，第二，其他的二级索引都会挂载主键（用于回表查询）。 2.插入数据的时候，尽量顺序插入（inserty优化的时候有讲到)。 3.尽量不要使用UUID做主键或其他自然主键，因为可能会乱序排序。 4.业务操作的时候，避免对主键进行修改，因为修改主键会动所有的索引结构 |
| ORDER BY设计原则 | Using filesort :它在explain 里的extra会有提示。如果出现Using filesort，就表示通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，所有的不是通过索引直接返回排序的结果的排序都叫FileSort排序。 Using index:它在explain 里的extra会有提示。如果出现Using index ,就表示通过有序索引顺序扫描直接返回有序的数据，这种情况即为Using index,不需要额外的排序，操作更加高效。 Order by的设计原则： 1.根据排序字段建立合适的索引，多字段排序的时候，也遵循最左前缀法则。 2.尽量使用覆盖索引进行排序，因为不是覆盖索引，那么会回表查询，最后也是Using FileSort。 3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时候的规则（ASC/DESC) eg:create index index_name on tb_user(age asc,phone desc)。 4.如果不可避免的出现fileSort,大数据量排序的时候，可以适当的增加排序缓冲区的大小 sort_buffer_size(默认256k) |
| GROUP BY 优化 | 常规的group by 可能用到临时表 1.在分组操作的时候，可以通过索引提高效率。 2.sql分组操作时，也会遵循最左前缀法则。 3.前面where中加了最左的索引（比如a)后面可以用b来分组也会走索引 |
| limit 优化 | 一个常见而又头疼的问题是 limit 2000000,10,此时需要Mysql查询前2000010条数据，然后返回2000000-200010条数据，其他的记录丢弃，查询的代价非常大。 优化1：一般分页查询的时候，通过创建覆盖索引能较好滴提高性能，可以通过覆盖索引加子查询的形式进行（就不用limit，通过覆盖索引查询覆盖条件的记录，然后获得id,在用in() 来查询相应的id。 优化2：利用自增索引，将limit分页操作中的全表扫描转换为范围查询扫描，可以有效的缩短分页查询的时间，eg: emp limit 10000,10替换为 where id >= 10000 limit 10。注意：这种优化非常有局限性，只能分页id递增且id递增的必须连续。不可在where后面添加其他的查询条件，否则会导致查询的含义发生变化。 优化3:条件查询转换为基于主键的查询：eg; ename 加了索引，但是我们需要查询全字段数据，这时候可以利用id是主键索引的特点，加快查询速度: eg: select * from emp where ename = ‘aaa’ limit 100000,10 =>(改为） select * from emp where id in(select id from emp where ename = ‘aaa’ limit 1000000,10 ) 这种也有缺点，in关键字本身只有1000条数据量额限制，in本身也会存在效率的问题，因此，改优化也是最不理想的优化方式之一。 优化4: 条件查询转换为基于主键id的查询 + inner join:既然in存在局限性，所以使用inner join代替in操作进行优化： select * from emp where ename = ‘aaa’ limit 1000000,10; =>(改为） select * from emp inner join (select id from emp where ename = ‘aaa’ limit 1000000, 10 ) using(id) |
| 循环优化，子查询 | 内层循环次数的多少不会影响到外层的次数，但是外层循环会直接影响到哪层循环的次数，外层循环每多一次，内层循环就需要多完整的一次（类比两层for循环）。所以优化的目的是使外层循环减少，总结来说就是，小表驱动大表，尽量减少外层循环的次数。 |
3.2.4 sql delete 优化
| 优化内容 | 解释 |
|---|---|
| 物理删除 | 当需要对数据进行物理删除的时候，尽量通过索引字段做删除条件 |
| 大批量物理删除 | 当需要删除的数据量过多的时候，一下子全部删除会导致数据库压力过大，或者锁超时的问题。这里解决方案是在删除语句后面加上一个 limit n 用一个while循环删除，当删除影响的行数为零的时候跳出循环。 |
| 逻辑删除 | 如果数据过多，逻辑删除不删除实际的数据，为了不影响查询，可以另外做个同样的表，里面仅仅存放删除的数据，这样就能够减少查询表的数据量。 |
3.2.4 其他的sql优化
sql优化比较宽泛，要根据mysql本身的设计逻辑以及特点来进行选择sql的优化方案，同时在我们编写sql的时候，尽量按照给定的一些规范来书写。我们只需记住如下的一些常用的sql优化的方案即可。
| 优化方案 | 举例 |
|---|---|
| count 的优化 | 1.MyISAM引擎会把一个表的总行数放在磁盘上，我们如果没有其他的业务要求，可以在创建表时，选择MYISAM 2.自己计数，业务侵入性比较强。 3.count(*) >= count(1) > count(主键） > count(字段） |
| NOT NULL优化 | 如如果确定某个字段不能为null，则记得加not null索引。因为第一这样可以尽量避免业务层发生空指针异常。同时在count(*) 计数的时候，没有not null的约束，InnoDB引擎会遍历整张表把每一行的字段值都取出来，服务层会判断是否为null，不为null则计数累加，所以加上not null会快一些。服务层直接累加。 |
| limit n 优化 | 1.如果设计全表扫描的数据查找，你确定每次查询只会返回n条，则记得在最后加上一个 limit n。这样每次查询的时候，如果找到n个符合条件的，就会立刻返回，不会再继续进行查找了。 2.如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。 |
| select 明确字段 | 在表查询中，建议明确字段，不要使用 select * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因: 1.MySQL 在解析的过程中，会通过查询数据字典将“*”按序转换成所有列名，这会大大的耗费资源和时间。 2. 无法使用覆盖索引。 |
| 多使用COMMIT | 只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。COMMIT 所释放的资源： 1.回滚段上用于恢复数据的信息。 2.被程序语句获得的锁。 3.redo / undo log buffer 中的空间。 4.管理上述 3 种资源中的内部花费。 |
3.3 系统、参数、硬件等方面的优化
这方面的优化一般不要轻易使用，很多时候我们应该先考虑代码问题，代码没问题再去最后考虑调整参数或者扩容等。如下的方式了解即可，不是很重要。
3.3.1 参数优化
- 关闭不必要的服务和日志：调优结束关闭慢查询日志；
- 调整最大连接数：max_connections ；
- 线程池缓存线程数：thread_cache_size，缓存空闲线程，有连接时直接分配该线程处理连接；
- 缓冲池大小：innodb_buffer_pool_size 。
3.3.2 硬件优化
服务器加内存条、升级SSD固态硬盘、把磁盘I/O分散在多个设备、配置多处理器。
3.3.3 定期清理垃圾
对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。
3.3.4 存储引擎优化（选择合适的存储引擎）
MyISAM：适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址）。
InnoDB：适合并发写入的场景（因为行级锁、B+树叶存记录）。
InnoDB：支持外键和事务，行锁适合高并发，缓存索引和数据，内存要求高（因为要缓存索引和记录），适合存大数据量，增删改性能更优（行级锁高并发），耗费磁盘（因为有多个非聚簇索引，索引可能比记录空间还大）。
MyISAM：不支持外键和事务，表锁不适合高并发，缓存索引和数据地址，内存要求低（因为不用缓存记录），查询性能更优（因为查询时InnoDB要维护MVCC一致，而且多缓存了记录），节省磁盘（因为磁盘不存完整记录）。
对比 InnoDB MyISAM 特点 支持外键和事务 不支持外键和事务 行表锁 行锁，操作时只锁某一行，不对其它行有影响， 适合高并发的操作 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 缓存 缓存索引和数据，对内存要求较高，而且内存大小对性能有决定性的影响 只缓存索引，不缓存真实数据 关注点 事务：并发写、事务、更大资源 性能：节省资源、消耗少、简单业务、查询快 默认使用 5.5及其之后 5.5之前
3.4 缓存优化
1.mysql调整缓冲池大小等参数；因为InnoDB使用缓冲池缓存记录和索引。
2. 引入redis、elasticsearch、rabbitMq等缓存中间件。减少查询直接打入数据库。
5. 读写分离、分区分表
这一块就涉及到数据库的集群问题了，主从集群的设计。（正在更新…)
4.总结
mysql的 优化内容还有很多，但是想要去应付面试上面的这些就够了。想要成为真正的sql优化大师，那可不仅仅背一些已知的结论，还得去自己了解底层原理，自己根据具体业务情况进行具体的优化设计。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
