【Mysql】——存储引擎及SQL优化

💻博主现有专栏：

                C51单片机（STC89C516），c语言，c++，离散数学，算法设计与分析，数据结构，Python，Java基础，MySQL，linux，基于HTML5的网页设计及应用，Rust（官方文档重点总结），jQuery，前端vue.js，Javaweb开发，设计模式、Python机器学习等
🥏主页链接：

                Y小夜-CSDN博客

目录

🎯InnoDB 、 MyISAM 、  MEMORY、 MERGE有什么不同？

🎯explain分析中的意思

🎯避免索引失效的方法有哪些？

🎯以下SQL操作，分别有哪些优化方法？

🎃大批量插入数据

🎃Insert语句

🎃Order by语句

🎃Group by语句

🎃嵌套查询

🎃Or条件

🎃分页查询

---

🎯InnoDB 、 MyISAM 、  MEMORY、 MERGE有什么不同？

•  InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高  的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，  那么InnoDB存储引擎是比较合适的选择。  InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，  还 可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
•   MyISAM  ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发 性要求不是很高，那么选择这个存储引擎是非常合适的。
•   MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。 MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数 据库异常终止后表中的数据是可以恢复的。  MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结 果。
•   MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。  MERGE表的优 点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善，MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

🎯explain分析中的意思

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、 PRIMARY（主查询，即外层的查询）、  UNION（ UNION 中的第二个或者后面的查询语  句）、 SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------>   all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
filtered	按条件过滤后查询到的记录的百分比
extra	执行情况的说明和描述

🎯避免索引失效的方法有哪些？

1). 全值匹配  ，对索引中所有列都指定具体值。

改情况下，索引生效，执行效率高。

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

3). 范围查询右边的列，不能使用索引 。

4). 不要在索引列上进行运算操作，  索引将失效。

5). 字符串不加单引号，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

7). 用or分割开的条件，  如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

8). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。

10). is NULL  ， is NOT NULL 有时索引失效。

11). in 走索引，   not in 索引失效。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

🎯以下SQL操作，分别有哪些优化方法？

🎃大批量插入数据

1 ）主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数 据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个 主键，将可以利用这点，来提高导入数据的效率。

2 ）关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢 复唯一性校验，可以提高导入的效率。

3 ）手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1 ，打开自动提交，也可以提高导入的效率。

🎃Insert语句

        如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户 端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

🎃Order by语句

两种排序方式

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序 都叫 FileSort 排序。

. 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index ，不需要额外排序，操作效率高。

Filesort 的优化

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加 快 Filesort的排序操作。对于Filesort  ， MySQL 有两种排序算法：

1 ）两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区  sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据 行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2 ）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时 内存开销较大，但是排序效率比两次扫描算法要高。

🎃Group by语句

        由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分 组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY一样也可以利用到索引。

        如果查询包含 group by 但是用户想要避免排序结果的消耗，  则可以执行order by null 禁止排序。

🎃嵌套查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把  这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接 （JOIN）替代。

🎃Or条件

        对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引  ，而且不能使用到复合索 引； 如果没有索引，则应该考虑增加索引。

🎃分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10  ， 此时需要MySQL排序前5000010 记录，仅仅返回500000 - 500010 的记录，其他记录丢弃，查询排序的代价非   常大 。

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。