【数据库】Mysql 进阶【上】
目录一.存储引擎
1）MySQL 的体系布局
2）InnoDB 存储引擎
3）MySIAM 存储引擎
4）Memory 存储引擎
二.索引
1）索引布局
2）索引的分类与语法
3）性能分析
4）索引使用规则
5）索引设计原则
三.SQL 优化
1）Insert 语句优化
2）order by 和 group by 优化
3）limit 和 count 优化
4）update 优化
前篇：【数据库】Mysql 基础
一.存储引擎
1）MySQL 的体系布局
在 MySQL 的服务端的体系布局是分层的：
[*]毗连层：主要用于接收客户端的毗连，完成毗连的处理以及一些授权认证的利用、安全检查等等。如用户名和密码的校验，校验每一个客户端拥有的权限。
[*]服务层：举行绝大多数的主要利用，如 SQL 接口、查询剖析器、查询优化器、查询缓存，以及全部跨存储引擎的实现（如DDL、DML语句的封装等）。
[*]存储引擎层：拥有很多存储引擎供选择，可插拔式的存储引擎，存储引擎控制着数据的存储和提取的方式。而索引在该层实现，所以差别的存储引擎索引的布局差别。而 InnoDB 是 MySQL 5.5 版本后默认使用的存储引擎。
[*]存储层：主要用于存储数据库的相关数据，包罗一系列的文件和日记。
接下来主要了解其中的第三层——存储引擎层
存储引擎是 MySQL 中特有的，是存储数据、创建索引、更新查询数据等技能的实现方式，而差别的存储引擎的实现原理差别。而存储引擎是基于表的，差别的表可以使用差别的存储引擎，所以存储引擎也可称作表类型。
https://i-blog.csdnimg.cn/direct/fe65e6ffcd5d4f2793972e30cbeaf234.png
而我们通过查询建表语句，可以看到默认使用 InnoDB （MySQL 5.5 版本后默认）
https://i-blog.csdnimg.cn/direct/9d21a936991f4b39be0c216124ffb5f2.png
其中 AUTO_INCREMENT=24 表示下一次插入的数据 id 自增为 5
DEFUALT CHARSET=utf8mb4 表示当前表的默认字符集为 utf8mb4
COLLATE 表示排序方式
那么如何指定存储引擎呢？
在此之前我们必要使用下方的 sql 查询当前数据库所支持的全部存储引擎
show engines
https://i-blog.csdnimg.cn/direct/59bd908f98254e4fa7ebf7eaa6747f8d.png
其中，Support：是否支持该引擎 Comment：解释 Transaction：是否支持事件
XA：是否支持 XA 协议 Savepoints：是否支持存储点
所以，我们如果想要指定想要使用的存储引擎，就可以像上图所示，在建表语句后跟：
ENGINE = [ 搜索引擎 ] 就可以指定使用的搜索引擎了。
2）InnoDB 存储引擎
InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，也是在 MySQL 5.5 版本后的默认存储引擎。
特点：1.DML 利用依照 ACID 模型，支持事件。
2.行级锁，进步并发访问性能。
3.支持外键 FOREIGN KEY 束缚，保证数据的完备性与正确性。
而 InnoDB 引擎的每张表又会有一个 如 表名.ibd 这样格式的表空间文件，用于存储该表的布局、数据和索引。
表空间文件：早期使用 frm 文件，但在 8.0 版本后表布局都存储在了 sdi 这个数据字典当中，而 sdi 又融入到了 ibd 表空间文件当中。
而这又涉及到了一个参数——innodb_file_per_table，决定是否多张表共用一个共享表空间，或是每一个表都对应一个表空间文件。同时，我们可以使用下面的指令查询这个参数，这个开关默认开启，代表每一个表都对应一个表空间文件。
https://i-blog.csdnimg.cn/direct/9c8144b41b2f40028a182d6d6bffff2f.png
逻辑存储布局（简单讲解）：
逻辑存储布局主要分为五块，分别是表空间（TableSpece）、段（Segment）、区（Extent）、页（Page）、行（Row）。而在 InnoDB 的逻辑布局中，页是磁盘利用的最小单位，一个区的巨细是固定为 1 M（兆），页的巨细是 16 K，所以一个区可以包罗 64 个页。
https://i-blog.csdnimg.cn/direct/e4d6adb2269142baae196ec52088a73b.png
页中包罗的行就是表布局中所存储的一行一行的数据，行中包罗着最后一次事件利用的 id，以及它的一些指针、接着便是一个一个的字段信息等数据。
3）MySIAM 存储引擎
MySIAM 存储引擎是 MySQL 早期的默认存储引擎。
特点：1.不支持事件，同时也不支持外键。
2.支持表锁，不支持行锁。
3.访问速度快。
文件：1.表名.sdi：用于存储表布局信息。
2.表名.MYD：由于存储数据。
3.表名.MYI：用于存储索引。
4）Memory 存储引擎
Memory 存储引擎的数据是存储在内存中的，所以受到硬件和电源问题的影响，只能将使用该引擎的表作为临时表大概缓存使用。
特点：1.内存存放，访问速度快。
2.支持 hash 索引（默认）
文件：表名.sdi：用于存储表布局信息
我们列出了三种存储引擎各自的特点及文件，还具体了解了 InnoDB 存储引擎。它们之间存在着区别，如 InnoDB 和 MyISAM 之间，前者支持事件和外键，且同时支持行级锁和表锁；但后者不支持事件和外键，且只支持表锁。那么该如何选择呢？
▶InnoDB：如果对事件的完备性有要求，保持并发条件下数据的同等性，除了插入和查询利用外，还包罗更新和删除利用的焦点事件，就可以选择 InnoDB 存储引擎。
▶MyISAM：如果只是以读和插入利用为主，只有很少的更删利用且对事件完备度、并发性要求不高的非焦点事件，可以选择 MyISAM 存储引擎。
▶Memory：将全部的数据存储在内存中，访问速度快，通常用作临时表或缓存，但对表的巨细有限制，并且无法保证数据的安全性。
二.索引
从这里开始，我们将使用 Linux 情况下的 MySQL 举行学习。发起在虚拟机（CentOS7）上安装 MySQL。
由于之前我已经通过 Docker 摆设好了 MySQL，所以这里不再过多赘述，跳过安装的贫苦环节，直接进入到学习的过程中。
https://i-blog.csdnimg.cn/direct/0f7c4cddf8984a39b30e584461198e73.png
索引是资助 MySQL 高效获取数据的数据布局（有序），当在没有索引的情况下举行条件查询利用时会举行全表扫描，而这种方式的服从极低，必要遍历整张表的数据。这就必要索引来举行高效的查询了。
长处缺点进步数据检索的服从，低沉数据库的 IO 本钱索引必要占用空间通过索引对数据举行分列，低沉了数据分列的本钱，也低沉了CPU的斲丧进步了查询服从，但也低沉了增、删、改等更新表利用的服从 IO 本钱：IO 本钱是指数据从磁盘加载到内存所需的时间本钱，主要涉及用户态和内核态的上下文切换。在 MySQL 中，IO 本钱默认必要花费 1个单位的本钱，CPU 本钱默认必要花费 0.2 个 单位本钱。
1）索引布局
由于 MySQL 的索引是在存储引擎层实现的，所以差别的存储引擎有差别的索引布局：
索引布局形貌B+Tree 索引最常见的索引类型，大部分引擎都支持Hash 索引底层由哈希表实现，只支持精准匹配，性能高，但无法范围查询R-tree（空间索引）MyISAM 引擎的特殊索引类型，主要用于处理地理空间数据，不常使用Full-text（全文索引）通过倒排索引，快速匹配文档的方式，雷同于 ElasticSearch
二叉树
二叉树的布局可由下图表示，但在序次插入时，会形成一个链表，从而导致查询性能大大低沉。在大量数据的情况下，层级较深，检索速度慢。所以我们会想到使用红黑树来解决树的平衡问题，但都会存在层级较深，检索速度慢的缺点。于是就会使用到 B-Tree （多路平衡查找树），它的一个节点下包罗多个子节点。
https://i-blog.csdnimg.cn/direct/4a0bbb18674c499d812bdc4d490dc140.png
B-Tree 中通过最大度数来规定每一个节点下的最大子节点数，如下图最大度数为 5，则每个节点最多存储 4 个 key，5 个指针。当插入数据后超过最大度数所能容纳的 key 时，会将中间元素向上裂变。
https://i-blog.csdnimg.cn/direct/966b99887d594f89b3a5a6ee9e46f4b1.png
B+Tree
以下图最大度数为 4 的 B+Tree 为例，全部的数据都会出现在叶子节点上，而非叶子节点只是充当索引的作用。在插入数据时，与 B-Tree 差别的是，中间元素在向上裂变时仍会在叶子节点上并生成单向链表。
https://i-blog.csdnimg.cn/direct/c993b88541354aac89b80aeb4418c888.png
在 MySQL 中索引数据布局对本来的 B+Tree 举行了优化，增加了一个指向相邻叶子节点的链表指针，形成了带有序次指针的 B+Tree ，进步了访问区间的性能，所以最终布局如下图所示。
https://i-blog.csdnimg.cn/direct/96c6c29ad57649f8b224a08d6f231e21.png
其中的页是 InnoDB 存储引擎的逻辑存储布局中的内容，最大为 16 K。
Hash 索引
Hash 索引是使用肯定的 hash 算法，将键的值换算成新的 hash 值，映射到对应的槽位上，存储在 hash 表中，但是处于大数据量中，会出现两个键值换算后相称映射到同一个槽位上造成 hash 冲突（或 hash 碰撞），这种情况我们可以通过链表来解决。
https://i-blog.csdnimg.cn/direct/f62dba43840d4f5a8cc24eaa91bca302.png
特点：
[*]Hash 索引只能用于对等比力（=，in），不支持范围查询（between，<，>）
[*]无法利用索引完成排序利用
[*]查询服从高，通常只需举行一次检索，且服从通常要高于 B+Tree
通常：不查询 hash 碰撞的情况下。
在 MySQL 中，只有 Memory 存储引擎支持 hash 索引，但 InnoDB 存储引擎具有自顺应 hash 功能，指 MySQL 会根据查询条件在指定条件下将 B+Tree 索引自动构建为 Hash 索引。
思考：为什么 InnoDB 存储引擎会选择使用 B+Tree 索引布局，而不是别的？
[*]二叉树：序次输入会形成链表，服从低。
[*]红黑树：本质上也是二叉树，存在问题。
[*] 相对于二叉树，层级更少，搜索服从更高。
[*]B-Tree：相对于来说，B-Tree 的叶子节点和非叶子节点上都会存放数据，从而导致一页（16 K）中存储的键值减少，指针也随之减少，与 B+Tree 相比要保存同样巨细的数据只能增加树的高度，导致性能低沉。
[*]Hash 索引：B+Tree 支持范围匹配和排序利用，而这些功能都是 Hash 索引不支持的。
2）索引的分类与语法
分类
索引名作用存在数量关键字主键索引针对于表中主键创建的索引默认自动创建，只有一个PRIMARY唯一索引克制同一表中某一字段值的重复可以有多个UNIQUE常规索引快速定位特定的数据可以有多个全文索引查找文本中的关键字，而不是比力索引中的值可以有多个FULLTEXT 如果表中有主键会自动的对表中主键创建一个主键索引；在某一字段上加唯一束缚时会自动为该字段创建一个唯一索引。
而在 InnoDB 存储引擎中又因索引存储情势的差别分为了以下两种索引：
索引名作用存在数量聚集索引（Clustered Index）数据存储与索引在一起，索引布局的叶子节点保存了行数据必须有，且只有一个二级索引（Secondary Index）数据与索引分开存储，索引节点的叶子节点关联的是对应主键可以存在多个 如果存在主键，则主键索引就是聚集索引；如果不存在主键，则第一个唯一索引为聚集索引，如果都没有，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
当主键索引为聚集索引时，聚集索引的叶子节点下挂的数据就是这一行的数据，而二级索引叶子节点下挂的数据是对应行的 Id。
所以在对二级索引举行查询相关全部数据时，底层会先查找其叶子节点下的 Id 数据，再根据 Id回到聚集索引中举行回表查询，拿到这一行的行数据。
语法
创建索引
CREATE [ UNIQUE/FULLTEXT] INDEX 索引名 ON 表名（字段名，...）；
其中 UNIQUE 和 FULLTEXT 是可选的，如果不写则为常规索引。索引名是本身起的，一样寻常规范为 idx(index 简写)_表名_字段名。同时一个索引可以关联多个字段，称之为联合索引（或组合索引），也可以只关联一个字段，称之为单列索引。
查看索引
SHOW INDEX FROM 表名；
用于查看表中的全部索引
删除索引
DROP INDEX 索引名ON 表名；
删除某一表中的某一个索引
3）性能分析
[*]SQL 的实行频次
在举行 SQL 语句的优化时，主要是对 Select 查询语句举行优化，所以我们要对 SQL 的实行频次举行分析，判断是以增编削为主还是以查为主。
而在 MySQL 中提供了 SHOW [ session/global ] STATUS 语句来查看服务器的状态信息，而在其后加上 LIKE ‘Com_______’ 就可查看当前数据库的各语句实行情况。（注意‘_’一共有 7 个）
https://i-blog.csdnimg.cn/direct/4c6443fb6e204b77a26f3b22b68c5109.png
[*]慢查询日记
慢查询日记中记录了全部实行时间超过指定参数（long_query_time，单位：秒，默认为 10 秒）的全部 SQL 语句的日记，可以资助我们快速的定位到服从较低的 SQL。但 MySQL 的慢查询日记必要手动开启，其默认是关闭的（有的是默认开启的），必要在 MySQL 的配置文件（/etc/my.cnf）中配置。
[*]开启慢查询日记的开关：slow_query_log=1
[*]设置慢日记的时间参数：long_query_time=2（只要 SQL 语句实行时间超过 2 秒，就会被视为慢查询并记录）
在虚拟机内，可以通过 /var/lib/mysql/localhost-slow.log 文件查看慢查询日记
[*]profile 详情
指令 show profiles 可以大概资助我们了解时间的耗费去向，而我们必要向查看 have_profiling 参数查看当前数据库是否支持 profile 利用。
查看体系变量 have_profiling：SELECT @@have_profiling;（YES）
同时，我们也必要查看 profiling 开关是否开启，如果为 0 表示未开启。
查看体系变量 profiling：SELECT @@profiling;
开启 profiling 开关：set profiling = 1;
打开开关后，我们就可以通过以下指令查看指令的实行耗时
查看每一条 SQL 的基本耗时情况：
show profiles；
查看指定的 SQL 语句各阶段的耗时情况
show profile for query [ 查询id ];
查看指定语句 CPU 使用情况
show profile cpu for query [ 查询id ];
[*]explain 实行筹划
explain 大概 desc 命令获取 MySQL 如何实行 SELECT 语句的信息，包括语句在实行过程中表是如何毗连的和毗连的序次。
语法：直接在查询语句前加上 explain/desc 关键字即可
以下方的查询为例，在查询语句前加上了 explain 关键字。
https://i-blog.csdnimg.cn/direct/ec4c95dbe30049f9a863f2431c7d56e7.png
其中各字段含义字段含义idSQL 语句中表的实行序次（id 相同，从上至下；id 差别，值越大越先实行）。在多表查询中体现select_typeSELECT 语句的类型，常见的有 SIMPLE（简单表，不使用表毗连或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个大概背面的查询语句）、SUBQUERY（SELECT/WHERE 后包罗了子查询）等等table对应的表名partitions分区type 毗连类型性能由好到差分别为 NULL、system、const、eq_ref、ref、range、index、all。但只有在查询时不访问表才会是 NULL，const 是根据唯一索引查询时；而 ref 是根据非唯一性索引查询时；range 是在 ref 的基础上举行了范围匹配；index 是用了索引但仍对整个索引树举行了遍历；all 是举行全表扫描时
possible_keys该表可能用到的一个或多个索引key实际使用到的索引，若没有效到则为 NULLkey_len索引使用的字节数，也是索引字段的最大可能长度，并非实际的长度，不损失精度的条件下，越短越好 rows MySQL 认为要实行查询的行数，在 InnoDB 引擎的表中是一个估计值，禁绝确
filtered效果返回行数占总读取行数的百分比，值越大越好，性能越高Extra额外信息，展示前面字段未展示的值
4）索引使用规则
索引对于查询利用的服从进步具有明显的作用，但也会存在一些使用规则：
联合索引
[*]最左前缀法则
如果索引关联了多列（联合索引），就必要遵守最左前缀法则，该法则指在查询时从索引的最左列开始，且不跳过索引中的列。如果跳过，索引将会部分失效（背面的字段索引失效）。而如果不从最左端的索引开始查起，则不会走索引举行查询。
如 name、age、status 三个字段被同一个索引相关联，则在查询时如果只根据 age、status举行查询的话，则不会使用索引，而若是根据 name 和 status 举行查询时，则 status 的索引将会失效。（与其查询的位置无关，只与是否存在有关）
[*]范围查询
在联合索引中如果出现了范围查询（>,<），则范围查询右侧的列索引失效。
如在根据 name、age、status 举行查询的语句中，如果 age 举行了范围查询，则其右侧的 status 的索引将会失效。 所以必要只管使用（>=,=<）来规避索引的失效。
索引列运算
不要在查询时对索引列举行运算利用，否则其索引将会失效。如通过函数举行利用，如字符串函数 substring。
字符串类型字段
如果在查询与字符串字段相关联的索引时，如果查询的字符串不加 ‘ ’ 引号，存在隐式类型转换，索引将会失效。 如语句：select * from emp where phone = 12324232133 其索引就会失效。
模糊查询
如果仅仅是尾部举行模糊匹配，索引不会失效。而如果是头部举行模糊匹配，则索引将会失效（如 name = ‘%亮’ 或 ‘%佳%’ 等存在头部模糊匹配），因其会举行全表扫描举行匹配，所以要只管规避在头部使用模糊匹配。
or 毗连的条件
用 or 分割开的条件，如果 or 毗连的前面的条件中的列有索引，而背面的条件没有索引，那么涉及的索引都不会被用到。如果前后两个列都有索引，那么涉及的索引都会被用到。所以在想要使用索引时，要只管保证 or 前后的两个条件中的列都存在索引。
数据分布影响
如果在实行语句时颠末 MySQL 的优化器评估使用索引并举行全表扫描的还要慢，则不会走索引反而举行全表扫描。
SQL 提示
在查询时如果一个字段同时存在联合索引与单列索引，而 MySQL 的优化器自动选择使用联合索引，而若是想要指定索引，必要用到 SQL 提示功能，就是在 SQL 语句中参加人为的提示来举行优化。这也是优化数据库的一个紧张手段。
use index（索引名）：发起使用该索引（不肯定使用，评估服从）
ignore index（索引名）：忽略该索引
force index（索引名）：必须使用该索引
使用： select * from 表名 use/ignore/force index(索引名) where 条件;
覆盖索引
在查询中，要只管使用覆盖索引，如查询中使用了索引，且必要返回的列在该索引中存在。克制查询 * 全部列。因其会造成回表查询，低沉查询的服从。
前缀索引
当字段类型为字符串时，会存在必要索引很长的字符串或大文本字符串，使索引变得很大，导致到查询时浪费大量的磁盘 IO，影响查询的服从。此时我们就必要使用前缀索引来解决，只取字符串的一段前缀创建索引，从而大大节省索引空间，进步索引服从。
语法：create index 字段名 on 表名（字段名（n））
只需在字段后加上（n）就表示想要截取对应 n 长度的前缀
可根据索引的选择性来决定前缀长度，选择性指不重复的索引值和数据表的记录总数的比值，选择性越高服从越高。而唯一索引选择性为 1，性能最好。
盘算选择性
select count(distinct substring(字段名,1,10))count(*) from 表名
distinct：去重 substring：截取字符串
单列索引与联合索引
单列索引：一个索引包罗了一个列
联合索引：一个索引包罗了多个列
推荐使用联合索引因其不易举行回表查询，而使用单列索引时容易造成回表查询。
5）索引设计原则
[*]针对于数据量大，查询较频仍的表创建索引。
[*]对常作用查询条件、排序、分组利用的字段创建索引。
[*]只管选择区分度高的列作为索引，只管创建唯一索引，区分度越高，索引服从越高。
[*]如果是字符串类型的字段，若字段的长度过长，可以针对字段的特点，创建前缀索引。
[*]只管使用联合索引，减少单列索引，查询时，很多时候联合索引都可以覆盖索引，节省存储空间，克制回表，进步查询服从。
[*]要控制索引的数量，索引不是多多益善，越多导致维护的本钱也在进步，会影响增编削的服从
[*]如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 非空束缚它，由于当优化器知道每列是否包罗 NULL 值时，可以更好的选择索引举行查询。
三.SQL 优化
本章内容主要是关与其他 SQL 语句（增编削）的优化。
1）Insert 语句优化
[*]批量插入
插入数据通常是一条一条数据的插入，而想要举行优化，其中一个方法便是批量插入。可以在 values 后以 “,” 为分隔写入数据，但最好不要超过 1000 条数据。但若是想要插入几万条的数据，就必要将其分为多条 insert 语句实行。
[*]手动提交事件
在举行事件的提交时，推荐开启事件的手动提交。因在自动提交事件中，每实行一次 insert 语句都必要开启并提交事件，在实行多条语句时会造成频仍的开启和提交事件。全部发起使用手动提交事件的方式同一提交事件。
[*]主键序次插入
在插入数据时，按主键序次插入的性能要高于乱序插入。
大批量插入数据
而若是涉及到了大批量的数据插入，就不发起使用 insert 语句了。而是使用 load 指令插入。
步骤：
1）在客户端毗连服务端时加上参数 --local-infile 表示必要加载本地的文件
mysql --local-infile -u root -p
2）设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
3）实行 load 指令将预备好的数据，加载到表布局中
load data local infile ‘文件路径’ into table ‘表名’ fields terminated by ‘,’ lines terminated by ‘\n’
fields terminated by：每一个字段之间以 xx 举行分隔
lines terminated by：每一行之间以 xx 举行分隔
主键优化
在 InnoDB 引擎当中表数据是根据主键按序次组织存放的，因聚集索引默认是主键索引，这种存储方式的表就称为索引组织表。而在底层插入数据时是往页中插入的，一个页的巨细是 16K。
https://i-blog.csdnimg.cn/direct/e4d6adb2269142baae196ec52088a73b.png
页可以为空，也可以填凑数据。每个页至少包罗两行数据，如果一行的数据过大则会造成行溢出，且内容根据主键举行分列。
在序次插入中，在填满第一个数据页后会写入第二个页，且之间维护一个双向指针。
而在乱序插入中，如下图情况所示写入一个主键 50 的数据。
https://i-blog.csdnimg.cn/direct/b2825dd15bd943d2940b340aafa8fd7c.png
它的第一个数据页已满不能在写入且不会直接写入第三个数据页。它会与前面的主键所在页的一半的数据组合写入新的数据页，并将链表指针重新设置序次。这种征象叫做叶分裂。
https://i-blog.csdnimg.cn/direct/23ed621c83634e0781650b1e6abd6eb4.png
在举行一行数据的删除时，它的记录并不是举行物理删除，而是被打上标记为删除且该空间可以被其他记录声明使用，而在下图中当页中数据的删除量到达一半时，InnoDB 会判断上一页和下一页的数据页可否举行归并，如下图可以和三号数据页归并，则会与其归并为一个页。这种行为称作叶归并。
https://i-blog.csdnimg.cn/direct/6d5c7d5bb34246bab482be835ef0570f.png
https://i-blog.csdnimg.cn/direct/852ab4da47234c8a802427fbb2d16fe0.png 叶归并的阈值（MERGE_THRESHOLD）默认为一半（50%），但该值可以自行设置，在创建表或索引时指定。
主键设计原则
[*]在满足业务需求的同时，只管减少主键的长度。因二级索引的叶子节点上挂的是主键，所以必要控制主键的长度，减少耗费的磁盘空间和磁盘 IO。
[*]插入数据时只管使用序次插入，选择 AUTO_INCREMENT 自增主键，克制乱序插入产生叶分裂征象。
[*]克制在业务利用时对主键举行修改，因其关联了索引布局，修改代价太大。
2）order by 和 group by 优化
Order by 优化
在 MySQL 中排序的方式有两种：
① Using filesort：通过索引或全表扫描，读取满足条件的数据行，在排序缓存区 sort buffer 中完成排序利用。
② Using index：通过有序索引序次扫描直接返回有序数据，这种方式不必要额外排序，利用服从高。
所以在对排序举行优化时，我们必要只管优化为第二种方式 Using index。
可以看到在下方的语句中使用 Using filesort 方式，服从低。
https://i-blog.csdnimg.cn/direct/5a582aa3f26446169356eecf4fd369c7.png
而在给相关字段添加联合索引后，实行同样的语句后，方式变为了 Using index。进步了服从
https://i-blog.csdnimg.cn/direct/c3cd08613f7f471b9943a7ea48b8b0fc.png
若是在排序时举行倒序排序，则会显示 Backward index scan 表示必要反向扫描索引。
https://i-blog.csdnimg.cn/direct/a223dd467df948ff836849af8aabdc3f.png
而如果将前后两个排序的字段序次颠倒，则会导致出现了 Using filesort 方式，因其违反了最左前缀法则，order by 会按从左到右的序次依次对字段举行排序，而在联合索引中 status 是最左端的字段，但 order by 会先对 ordered 字段排序，但索引是先按 status 举行排序的，在 status 相同时再对 ordered 的值举行排序的。因此导致 ordered 字段走 Using filesort 方式，status 字段走 Using index 方式。
https://i-blog.csdnimg.cn/direct/664bb995b7a24990a74914954a9828ce.png
若是前一个字段升序分列，后一个字段举行倒序分列，也会导致使用 Using filesort 方式，想要优化掉这个问题，我们可以针对这两个字段重新创建一个索引：
create index 索引名 on 表名( 字段1 asc，字段2 desc )；
语句表示索引中字段1升序分列，字段2降序分列。
https://i-blog.csdnimg.cn/direct/96da219c06e24cde88b3feb7f2c9f9ed.png
再次对字段举行查询时，发现走新建的索引，并且是 Using index 方式举行排序。
https://i-blog.csdnimg.cn/direct/d8710bab3f724c618f4016929363f7a4.png
注：以上方式的条件是使用了覆盖索引，也就是对字段排序的同时对相关字段举行查询。而在举行大数据量的排序时，可以得当增加排序缓冲区的巨细 sort_buffer_size（默认 256K），提升排序的服从。
Group by 优化
在举行 group by 分组时，通过查看实行情况可以看到并没有使用到索引且 Using temporary 使用临时表服从并不高。我们要将其同样优化为 Using index，表示相应的查询语句使用了覆盖索引，直接读取了索引中的数据，进步了服从。
https://i-blog.csdnimg.cn/direct/997ea665d5674d33b0963b4a81acbe72.png
所以我们选择创建对应字段的联合索引来进步服从，如前面创建的关于 status 和 ordered 字段的联合索引，在举行排序时满足覆盖索引要求。这样就可以看到使用到了该索引并且是 Using index 方式。
https://i-blog.csdnimg.cn/direct/a7cf656023c246fea3756f26689b91b9.png
而在 group by 中索引的使用也必须依照最左前缀法则，即例中索引的第二个字段 ordered 不能单独出现，不然也会走 Using temporary 使用临时表从而低沉服从。
3）limit 和 count 优化
limit 分页查询优化
在举行对大数据量的分页查询时，尤其是在对越来越靠后的数据举行查询时，所查询的服从越低，耗时越长。前面所查询到的大量数据都会抛弃，只返回相应的数据，查询排序的代价非常大。
优化：覆盖索引+子查询
在大数据量前，我们可以通过对 id 举行分页查询并排序返回 id，之后把查询到的 id 效果视为一张表作为子查询举行多表联查：
<strong> select a.* from 表名1 a, (子查询) b where a.id = b.id;</strong>
这样就可以节省大量的时间，从而进步在大数据情况下分页查询的服从。
count 优化
count（）是一个聚合函数，对返回的效果集一行行的判断，若参数不是 NULL ，累计值就加 1，否则就不加，最后返回累加值。
用法：
● count（*）：举行了优化，不取值，服务层直接按行举行累加
● count（主键）：InnoDB 引擎遍历整张表，取出每一行的主键 id 值，返回服务层。而服务层在拿到主键后直接按行举行累加（主键不可能为 null）
● count（字段）：
[*]没有 not null 束缚：InnoDB 引擎会遍历整张表取出每一行的字段值，返回服务层判断是否为 null，若不是 null，则计数累加
[*]有 not null 束缚：InnoDB 引擎遍历整张表取出每一行的字段值，返回服务层直接按行举行累加
● count（1）：InnoDB 引擎遍历整张表，不取值，服务层对于返回的每一行放入数字 1，直接按行举行累加
按服从举行排序： count（字段）< count（主键）< count（1）≈ count（*）
同样的，在大数据量的情况下，耗时也会增大。而这归根结底是因 InnoDB 引擎的处理方式的。
在 MyISAM 引擎中将一个表的总行数存在了磁盘上，因此实行 count(*) 时会直接返回这个数，服从很高。而在 InnoDB 引擎中，会将数据一行以上的读出累计计数。优化的方式很少，所以想要举行优化，只有自行计数这一方式（繁琐）。如使用redis 数据库举行存储。
4）update 优化
在举行对 update 的优化中，主要是为了克制行锁升级为表锁。在并发情况下，开启事件对一行的数据举行利用时，会产生行锁，克制其他线程利用。但如果更新语句的条件字段变为没有索引的字段，则行锁会升级为表锁。
所以想要解决这个问题，必要为条件的相关字段创建索引，根据索引字段举行更新，且该索引不能失效，否则行锁就将升级为表锁，造成并发性能的低沉。
【完】
如有不敷，请多多指出
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]