史上最全存储引擎、索引使用及SQL优化的实践
共 11807字，需浏览 24分钟
·
2021-04-19 14:30
整个MySQL Server由以下组成 ：
Connection Pool ：连接池组件
Management Services & Utilities ：管理服务和工具组件
SQL Interface ：SQL接口组件
Parser ：查询分析器组件
Optimizer ：优化器组件
Caches & Buffers ：缓冲池组件
Pluggable Storage Engines ：存储引擎
File System ：文件系统
1）连接层
上层是一些客户端和链接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2）服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
3）引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
4）存储层
数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
2.1 存储引擎概述
和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表，而不是基于库的。所以存储引擎也可被称为表类型。
Oracle、SqlServer等数据库只有一种存储引擎。MySQL提供插件式的存储引擎架构。所以MySQL存在多种存储引擎，
可以根据需要使用相应的引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含 ：InooDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、
CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事物安全表，其他存储引擎是非事物安全表。
可以通过指定show engines，来查询当前数据库支持的存储引擎 ：
创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。
查看MySQL数据库默认的存储引擎 ，指令 ：
show variables like ‘%storage_engine%’;
下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别，如下表所示 ：
2.2.1 InnoDB
InnoDB存储引擎是MySQL的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，
InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点 ：
事务控制
测试发现InnoDB中是存在事务的。
外键约束
MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建
对应的索引。
下面是两张表中，country_innodb是父表，country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于
country_innodb表的主键country_id
在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、CASCADE、SET NULL和NO ACTION。
RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新；
CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；
SET NULL则表示父表在更新或者删除的时候，子表的对应字段被SET NULL.
针对上面创建的两个表，子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应的记录，则子表对应更新。
ON DELETE RESTRICT ----> 删除主表数据时，如果有关联记录，不删除；
ON UPDATE CASCADE ----> 更新主表时，如果子表有关联记录，更新子表记录。
表中数据如下图所示 ：
存放方式
InnoDB存储表和索引有以下两种方式 ：
（1）使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
（2）使用躲表空间存储，这种方式创建的表的表结构任然存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。
2.2.2 MyISAM
MyISAM不支持事务、也不支持外键，其优势是访问速度快，对事物的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个
引擎来创建表。有以下两个比较重要的特点 ：不支持事务
通过测试，我们发现，在MyISAM存储引擎中，是没有事务控制的。
MySQL客户端连接成功后，通过show [session | global] status命令可以提高服务器状态信息。show [session | global] status 可以根据需要加上参数 “session”或者“global”来显示session级（当前连接）的计结果和global级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
下面的命令显示了当前session中所有统计参数的值 ：
show status like “Com_”;
show status like ‘Innodb_rows_%’;
Com_xxx表示每个xxx语句执行的次数，我能通常比较关心的是以下几个统计参数
Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** ：这几个参数只是针对InooDB存储引擎，累加的算法也略有不同。
可以通过以下两种方式定位执行效率较低的SQL语句。
1）慢查询日志 ：通过慢查询日志定位那些执行效率较低的SQL语句，用–log-slow-queries[=file_name]选型启动时，mysqld写一个包含索引执行时间超过long_query_time秒的SQL语句的日志文件。
2）show processlist : 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
1）id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4）db列，显示这个进程目前连接的哪个数据库
5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6）time列，显示这个状态持续的时间，单位是秒
7）state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成。
8）info列，显示这个sql语句，是判断问题语句的一个重要依据
通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
查询SQL语句的执行计划 ：
explain select * from tb_item where id = 1;
explain select * from tb_item where title = ‘阿尔卡特（ot-979）冰川白 联通3G手机3’;
id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id情况有三种 ：
1）id相同表示加载表的顺序是从上到下。
explain select * from t_role r,t_user u,user_role ur where r.id = ur.role_id and u.id = ur.user_id;
2）id不同id值越大，优先级越高，越先被执行。
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = ‘stul’))
3）id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，优先执行。
EXPLAIN SELECT * FROM t_role r,(SELECT * FROM user_role ur WHERE ur.‘user_id’ = ‘2’) a WHERE r.id = a.role_id;
3.3.3 explain 之 select_type
表示SELECT的类型，常见的取值，如下表所示 ：
SIMPLE ：简单的select查询，查询中不包含子查询或者UNION
PRIMARY ：查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY ：在SELECT或WHERE列表中包含了子查询
DERIVED ：在FROM列表中包含的子查询，被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表中
UNION ：若第二个SELECT出现在UNION之后，则标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ：DERIVED
UNION RESULT ：从UNION表获取结果的SELECT
展示这一行的数据是关于哪一张表的
type显示的是访问类型，是较为重要的一个指标，可取值为 ：
NULL ：MySQL不访问任何表，索引，直接返回结果
system ：表只有一行记录（等于系统表），这是const类型的特例，一般不会出现
const ：表示通过索引一次就找到了，const用于比较primary key（主键）或者unique（唯一）索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常亮。const于将“主键”或“唯一”索引的所有部分与常量值进行比较。
eq_ref ：类似ref，区域在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref ：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range ：只检索给定返回的行，使用一个索引来选择行。where之后出现between，<，>，in等操作。
index ：index与ALL的区别为 index类型只是遍历了索引树，通常比ALL快，ALL是遍历数据文件。
all ：将遍历全表以找到匹配的行
结果值从最好到最坏依次是 ：
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > range > index > ALL
一般来说，我们需要保证查询至少达到range基本，最好达到ref。
possible_keys : 显示可能应用在这张表的索引，一个或多个。
key ：实际使用的索引，如果为null，则没有使用索引。
key_len : 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
扫描行的数量
其他的额外的执行计划信息，在该列展示。
using filesort : 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”。效率低
using temporary ：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by 和group by。效率低
using index ：表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。
当extra列出现using filesort和using temporary时就需要进行sql优化了。
还有一个问题，很多人认为当出现效率低的情况，加索引，一味的加索引就认为能解决问题？实际上这种想法是错误的，索引不是想加就加的，每个索引都需要深思熟虑过的，不是因为业务需要而去加索引，这是一种错误的做法。索引是为了提升获取数据库数据的获取效率而加的。而业务的需要可以用其他方式去实现。比如排序，很多人第一时间想到数据库order by去排序，而需要排序的字段又是一些特殊的字段。我不认为这个时候去加索引是一种很好解决方案，可以尝试使用ES。
MySQL从5.0.37版本开始增加了对 show profiles和show profile语句的支持。show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
通过have_profiling参数，能够看到当前MySQL是否支持profile；
默认profiling是关闭的，可以通过set语句在Session级别开启profiling；
set profiling=1; // 开启profiling开关；
通过profile，我们能够更清楚地了解SQL执行的过程。
首先，我们可以执行一系列的操作，如下图所示 ：
show databasesl
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
执行完上述命令之后，再执行show profiles指令，来查看SQL语句执行的耗时 ：
通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间 ：
TIP :
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：
show PROFILE ALL for QUERY 58; 可以查看到所有的状态耗时。
MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。
打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
set optimizer_trace=“enabled=on”,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
执行SQL语句 ：
select * from tb_item where id < 4;
最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace\G;
开源的小米SQL优化工具SOAR可以了解一下。
索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
在表中存储了300万条记录；
A. 根据ID查询
select * from tb_item where id = 1999;
查询速度很快，接近0秒，主要的原因是因为id 为主键，有索引；
如果查询条件没有索引那么查询效率会很低。
1）.全值匹配，对索引中所有列都指定具体值。
改情况，索引生效，执行效率高。
explain select * from tb_seller where name = ‘小米科技’ and status = ‘1’ and address = ‘北京市’;
show status like ‘Handler_read%’;
show global status like ‘Handler_read%’;
Handler_read_first : 索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
Handler_read_key : 如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next : 按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev : 按照键顺序读取前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd : 根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描正整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next : 在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
当使用load命令导入数据的时候，适当的设置可以提高导入的效率。
对于InnoDB类型的表，有以下几种方式可以提高导入的效率 ：
1）主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列做为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
插入ID顺序排列的数据 ：
下图是有序数据导入的时间
下图是无序数据导入的时间
2） 关闭唯一性校验
在导入数据前执行SET_UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET_UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
3）手动提交事务
如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句；这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
示例，原始方式为 ：
优化后的方案为 ：
在事务中进行数据插入。
1）第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
2）第二种通过有序索引顺序扫描直接返回有序数据，这种情况既为using index，不需要额外排序，操心效率高。
多字段排序
1）order by多字段时，要么全部升序，要么全部降序。并且排序字段使用复合索引字段。
2）当为多字段排序时，排序字段顺序要和创建复合索引的字段顺序保持一致。
了解MySQL的排序方式，优化目标就清晰了 ：
尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。
通过创建合适的索引，能够减少FileSort的出现，但是在某些情况下，条件限制不能让FileSort消失，那就需要加快FileSort的排序操作。对于FileSort，MySQL有两种排序算法 ：
1）两次扫描算法 ：MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2）一次扫描算法 ：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_sort_data更大，那么使用第二种优化之后的算法 ：否则使用第一种。
可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。 
由于GROUP BY实际上也同样会进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用来其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用索引。
如果查询包含group by但是用户想要避免排序结果的消耗，则可以执行oerder by null禁止排序。如下 ：
drop index inx_emp_age_salary on emp;
explain select age,count() from emp group by age;
优化后
explain select age,count() from emp group by age order by null;
从上面的例子可以看出，第一个SQL语句需要进行“filesort”，而第二SQL由于order by null不需要进行“filesort”，而上文提过FileSort往往非常耗费时间。
也可以通过创建索引提高分组列的效率
create index idx_emp_age_salary on emp(age,salary);
MySQL4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表死锁，并且写起来也很容易。但是，有些情况下，子查询是可以被更搞笑的连接（JOIN）替代。
示例 ：查找有角色的所有的用户信息 ：
explain select * from t_user where id in (select user_id user_role);
执行计划为 ：
优化后 ：
explain select * from t_user u, user_role ur where u.id = ur.user_id;
对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须使用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。
获取emp表的所有索引 ：
示例 ：
explain select * from emp where id = 1 or age = 30；
建议使用union 替换or ：
这里的type中const要性能远高于range
我们来比较下重要指标，发现主要差别是type和ref这两项
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是 ：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL
UNION语句的type值为const，OR语句的type值为range，可以看到这是一个很明显的差距。
UNION语句的ref值为const，OR语句的type值为null，const表示是常量值引用，非常快这两项的差距就说明来
UNION要优于OR。
一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又非常头痛的问题就是 limit 20000000，10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
两个SQL的执行计划如下
该方案适用于主键自增的表（不能出现主键断层），可以把limit查询转换成某个位置的查询。
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
在查询语句中表名的后面，添加use index来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
create index idex_seller_name on tb_seller(name);
如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用ignore index作为hint
explain select * from tb_seller ignore index(idx_seller_name) where name = ‘小米科技’;
为强制MySQL使用一个特定的索引，可在查询中使用force index作为hint。
create index inx_seller_address on tb_seller(address);
END
