MySQL高级篇-史上最全关于存储引擎、索引、优化的知识总结-刘宇
作者：刘宇
CSDN博客地址：https://blog.csdn.net/liuyu973971883
有部分资料参考，如有侵权，请联系删除。如有不正确的地方，烦请指正，谢谢。
一、mysql基础设置
1.1、目录解析
| 参数 | 路径 | 解释 | 备注 |
|---|---|---|---|
| --basedir | /usr/bin | 相关命令目录 | mysqladmin mysqldump等命令 |
| --datadir | /var/lib/mysql/ | mysql数据库文件的存放路径 | |
| --plugin-dir | /usr/lib64/mysql/plugin | mysql插件存放路径 | |
| --log-error | /var/lib/mysql/jack.atguigu.err | mysql错误日志路径 | |
| --pid-file | /var/run/mysqld/mysqld.pid | 进程pid文件 | |
| --socket | /var/lib/mysql/mysql.sock | 本地连接时用的unix套接字文件 | |
| /usr/share/mysql | 配置文件目录 | mysql脚本及配置文件 | |
| /etc/init.d/mysql | 服务启停相关脚本 |
1.2、修改数据库字符集
- 修改字符集配置
#1.编辑配置文件
vim /etc/my.cnf
#2.在[mysqld]下方添加如下设置
character_set_server=utf-8
#3.重启mysql服务
systemctl restart mysqld
- 修改已生成的库字符集
alter database mytest character set 'utf8';
- 修改数据表的字符集
alter table user convert to character set 'utf8';
1.3、修改数据库模式
- 临时修改数据库模式，重启失效
#查看当前模式
show variables like 'sql_mode';
#设置当前模式
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
- 永久修改数据库模式
#1.编辑配置文件
vim /etc/my.cnf
#2.在[mysqld]下方添加如下设置
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
#3.重启mysql服务
systemctl restart mysqld
二、mysql的逻辑架构介绍
与其它数据库相比较而言，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
-
连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 -
服务层
- Management Serveices & Utilities： 系统管理和控制工具
- SQL Interface: SQL接口 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
- Parser: 解析器 SQL命令传递到解析器的时候会被解析器验证和解析。
- Optimizer: 查询优化器。 SQL语句在查询之前会使用查询优化器对查询进行优化。 用一个例子就可以理解： select uid,name from user where gender= 1; 优化器来决定先投影还是先过滤。
- Cache和Buffer： 查询缓存。 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 缓存是负责读，缓冲负责写。
-
引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB -
存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
三、mysql的存储引擎
3.1 、查看当前MySQL提供什么引擎
查询命令：
show engines;
结果：
3.2 、MyISAM和InnoDB的区别
| 对比 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发操作 | 行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 关注点 | 节省资源、消耗少 | 并发写、事务、更大资源 |
| 默认安装 | Y | Y |
| 默认使用 | N | Y |
| 自带系统表使用 | Y | N |
3.3、各个存储引擎介绍
- InnoDB存储引擎：InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况
- MyISAM存储引擎：MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
- Archive引擎：Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
- Blackhole引擎：Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
- CSV引擎：CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。CSV引擎可以作为一种数据交换的机制，非常有用。CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
- Memory引擎：如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis)
- Federated引擎：Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。因为是远程操作，效率很低，只能执行简单的查询语句，再复杂的如分组等就不行了。
四、MySQL的索引
4.1、为什么SQL会变慢?
4.2、SQL的机读顺序
4.3、什么是索引？
索引它的本质是数据结构，主要用于提高查询效率。就比如书的目录、字典等，我们查询“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a~z。
上图示例中的树结构是使用的二叉树，而二叉树弊端之一就是很可能会发生两边不平衡的情况，所以MySQL采用的是B+TREE树。
4.3.1、B树
B树是一种多路自平衡搜索树，它类似普通的二叉树，但是B树允许每个节点有更多的子节点。B树示意图如下：
B树的特点：
- 所有键值分布在整个树中
- 任何关键字出现且只出现在一个节点中
- 搜索有可能在非叶子节点结束
- 在关键字全集内做一次查找，性能逼近二分查找算法
4.3.2、B+树
从图中也可以看到，B+树与B树的不同在于：
- 所有关键字存储在叶子节点，非叶子节点不存储真正的data
- 为所有叶子节点增加了一个链指针
4.3.3、为什么mysql的索引使用B+树？
- B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
- mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。
4.3.4、索引的优劣
优势：
- 类似大学图书馆建书目录索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，比如表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
4.3.5、索引的使用场景
适用场景：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，组合索引性价比更高
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不适用场景：
- 表记录太少
- 经常增删改查的表或者字段
- where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引，如：性别，男/女
4.4、聚簇索引和非聚簇索引
- 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。“聚簇”表示数据行和相邻的键值聚簇的存储在一起。
- 主键索引使用的是聚簇索引，其他非主键索引都是使用的非聚簇索引。
- 如下图，左侧索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。
4.5、索引分类及使用
4.5.1、单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
4.5.2、唯一索引
索引列的值必须唯一，但允许有空值
4.5.3、复合索引
即一个索引包含多个列
4.5.4、主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇（聚集）索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
4.5.5、索引的语法
创建
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名...)
删除
DROP INDEX 索引名 ON 表名
查看
SHOW INDEX FROM 表名\G
使用ALTER命令添加索引
ALTER TABLE 表名 ADD PRIMARY KEY (字段名,...)；
# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE 表名 ADD UNIQUE [索引名] (字段名,...)；
# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次)
ALTER TABLE 表名 ADD INDEX [索引名] (字段名,...)；
# 添加普通索引，索引值可出现多次。
ALTER TABLE 表名 ADD FULLTEXT [索引名] (字段名,...)；
#该语句指定了索引为 FULLTEXT ，用于全文索引。
4.6、Explain
4.6.1、什么是Explain
使用Explain关键字可以模拟优化器执行SQL查询语句，可以查看SQL执行的过程，从而分析SQL语句或者表结构的性能瓶颈。
作用：
- 分析表的读取顺序
- 哪些索引可以使用
- 数据读取操作的操作类型
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
4.6.2、Explain的使用
4.6.2.1、语法
-
语法：
Expalin+SQL语句
-
执行计划包含的信息如下：
4.6.2.2、各字段解释
-
id：
- 含义：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- 关注点：id号每个号码，标识一个独立的查询。一个sql的查询次数越少越好
- 三种情况：
- id相同：执行顺序由上至下
- id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同/不同，同时存在：
-
select_type：
- 含义：查询语句的类型
- 分类：
-
table：
- 含义：显示这一行的数据是关于哪张表的
-
partitions：
- 含义：代表分区表中的命中情况，非分区表，则该项为null
-
type：
- 含义：显示查询的时候使用了哪种类型，是较为重要的一个指标
- 分类：
- 详细分类：
- 结果值排序（最好到最坏）：
(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。
-
key：
- 含义：实际使用的索引。如果为null，则表示没有使用索引。如果查询中使用了覆盖索引，则该索引和查询的select字段重叠。
-
key_len：
- 含义：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。不损失精度性的情况下，长度越短越好，key_len字段能够帮你检查是否充分的利用上了索引
-
ref：
- 含义：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
-
rows：
- 含义：真正物理扫描的行数，越少越好
-
filtered：
- 含义：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比。
-
Extra：
- 含义：额外字段，如group by、order by中使用索引的情况
- 分类：
4.6.2.3、什么是覆盖索引
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据; 当能通过读取索引就可以得到想要的数据，那就不需要读取行了。
案例：
select id , name from t_xxx where age=18;
有一个组合索引 idx_id_name_age_xxx 包含了 id,name,age三个字段。查询时直接将建立了索引的列读取出来了，这样就不需要读取数据文件去查询所在行的其他数据了, 所以很高效。
4.7、索引优化
4.7.1、单表优化
4.7.1.1、案例分析
-
案例1：单值索引
#创建索引 create index idx_age on emp(age);#查询分析 explain select SQL_NO_CACHE * from emp where age=30;从下面的结果可以看出，使用了索引
-
案例2：复合索引
#创建复合索引 create index idx_age_deptid on emp(age,deptid);#创建索引后再次进行分析 explain select SQL_NO_CACHE * from emp where age=30 and deptid=4; #从结果可以得知，采用了索引，字段分别是age和deptid，因为它们都是int类型，每个int长度是5，所以长度为10。
-
案例3：查询判断的顺序需要和复合索引一一对应
#创建复合索引 create index idx_age_deptid_name on emp(age,deptid,name);#分别再次进行查询分析 explain select SQL_NO_CACHE * from emp where age=30 and deptid=4 and emp.name='abcd';
#索引不失效，因为有优化器，优化器会在语句执行前将deptid=4和age=30调换位置 explain select SQL_NO_CACHE * from emp where deptid=4 and age=30 and emp.name='abcd';
#age的右边索引失效，因为复合索引的age后面应该是deptid，而查询后面直接是name，所以导致了只有age字段的索引生效了 explain select SQL_NO_CACHE * from emp where age=30 and emp.name='abcd';
-
案例4：函数会导致索引失效，以及左%也会导致索引失效
#创建索引 create index idx_name on emp(name);#索引生效 explain select SQL_NO_CACHE * from emp where emp.name like 'abc%';
#索引失效，因为无法确定首各字符，也就不发利用索引插座 explain select SQL_NO_CACHE * from emp where emp.name like '%abc%';
#索引失效，因为只要带上函数，则所有索引就会失效 explain select SQL_NO_CACHE * from emp where left(emp.name,3) = 'abc';
-
案例5：范围查询的字段会导致后面的所有失效
#创建复合索引 create index idx_age_deptid_name on emp(age,deptid,name);#这里索引只使用了age和deptId的长度，name的索引并没有使用到，因为范围判断后面的索引将会失效 explain select SQL_NO_CACHE * from emp where emp.age=30 and emp.deptId<10 and emp.name ='abc';
解决方法：#所以我们将范围判断索引放在最后面，调整方向即可 create index idx_age_deptid_name on emp(age,name,deptid);#从下面的分析可以得知，索引使用长度为73，三个字段都用上了。 explain select SQL_NO_CACHE * from emp where emp.age=30 and emp.name ='abc' and emp.deptId>20;
-
案例6：不等于判断会使索引失效
#创建索引 create index idx_name on emp(name);#不等于判断会使索引失效 explain select SQL_NO_CACHE * from emp where emp.name <> 'abc';
-
案例7：is not null判断会使索引失效
#创建索引 create index idx_age on emp(age);#索引生效 explain select SQL_NO_CACHE * from emp where emp.age is null;
#索引失效，不等于空判断会使索引失效 explain select SQL_NO_CACHE * from emp where emp.age is not null;
4.7.1.2、索引失效总结
关于复合索引为什么需要一一对应并且遵循最佳左前缀法，大家可以看一下复合索引的B+树的数据结构：大佬博客
4.7.1.3、一般性建议
- 对于单键索引，尽量选中针对当前query过滤性更好的索引，如：身份证，手机号等，性别就是过滤性不好的字段。
- 在选择组合索引的时候，当前query中过滤性最好的字段在所有字段顺序中应该排最前面。
- 在选中组合索引的时候，尽量选中可以能够包含当前query中的where子句中更多字段的索引。
- 在选中组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引顺序的最后面。
- 书写sql语句时，尽量避免造成索引失效的情况。
4.7.2、关联查询优化
4.7.2.1、案例分析
-
案例1：关联表的角色之分
# 为两个表分别创建索引 create index idx_book_card on book(card); create index idx_class_card on class(card);explain select SQL_NO_CACHE * from class left join book on class.card = book.card;两个表关联查询有角色之分，主表为驱动表，从表为被驱动表，从下面的结果我们可以看出驱动表虽然使用了索引，但是还是避免不了要全表扫描，所以驱动表添加索引没有意义
-
案例2：inner join
#为book表创建索引 create index idx_book_card on book(card);#进行分析 explain select SQL_NO_CACHE * from class inner join book on class.card = book.card;如果是inner join，那么mysql会自己选中驱动表，有索引的那个表则会被晋升为驱动表
如果两张表都没有索引，那么mysql会选中数据量小的表作为驱动表
-
案例3：虚表不能使用索引
explain select SQL_NO_CACHE ab.`name`,c.`name` ceoname from (select a.`name`,b.`CEO` from emp a left join dept b on a.deptid=b.id)ab left join emp c on ab.ceo=c.id;该结果需要在5.5上运行，因为5.7上对虚表做了优化。
从下面的结果我们可以看到该虚表作为了驱动表，所以只出现了两个全表查询
explain select SQL_NO_CACHE c.`name`,ab.name ceoname from emp c left join (select a.`name`,b.`id` from emp a inner join dept b on b.ceo=a.id)ab on c.`deptId`=ab.id;该结果需要在5.5上运行，因为5.7上对虚表做了优化。
从下面的结果我们可以看到该虚表作了被驱动表，因为虚表无法使用索引，所以出现了三个全表查询
4.7.2.2、优化建议
- 保证被驱动表的join字段已经被索引
- left join时，选择小表作为驱动表，大表作为被驱动表
- inner join时，mysql会自己帮你把小结果集的表选为驱动表
- 子查询的虚表尽量不要放在被驱动表，有可能使用不到索引
- 能直接多表关联的尽量直接关联，不用子查询
4.7.3、子查询优化
4.7.3.1、案例分析
-
案例1：查询非掌门的员工信息
方式一：
select * t_emp a where a.`id` not in( select d.`CEO` from t_dept d where d.`CEO` is not null);方式二：
select * t_emp a left join t_dept d on d.`CEO` = a.`id` where b.`id` is null;以上两种方式都能实现结果，但是方式一因为出现了not in和not null，这样会导致索引失效，而第二种方式却能正确使用索引。
4.7.3.2、优化建议
- 尽量不要使用not in或者not exists，使用left outer join on xxx is null代替。
- 有索引的情况下，用inner join是最好的，其次是in，exists最糟糕
- 无索引的情况下用小表驱动大表，因为join方式需要distinct ，没有索引distinct消耗性能较大，所以exists性能最佳，in其次，join性能最差
- 无索引的情况下大表驱动小表in和exists 的性能应该是接近的，都比较糟糕，但是inner join由于使用了join buffer所以快很多，left join则最慢。
4.7.4、排序、分组优化
4.7.4.1、案例分析
-
案例1：排序需要添加过滤条件，否则排序索引无效
#创建索引 create index idx_age_deptid on emp(age,deptid);# 无过滤条件排序查询 explain select SQL_NO_CACHE * from emp ORDER BY age,deptid;无过滤条件时，我们发现type为all，说明它没有使用索引，并且扩展字段中使用了using filesort，说明order by也没有使用索引。这里为什么说不添加过滤条件会导致无效，是因为不添加过滤条件就是查询所有的数据，再加上select *，那么势必需要回表取数据，那么优化器就不会走索引。
# 添加过滤条件排序查询 explain select SQL_NO_CACHE * from emp ORDER BY age,deptid limit 10;当我们添加过滤条件时，我们发现使用了索引
-
案例2：顺序错也会导致排序索引无效
#创建索引 create index idx_age_deptid_name on emp(age,deptid,name);#这两个查询正常使用了索引，因为由过滤条件where，并且索引字段的顺序一致。 explain select SQL_NO_CACHE * from emp where age=45 order by deptid; explain select SQL_NO_CACHE * from emp where age=45 order by deptid,name;#虽然age、deptid顺序符合，但是empno没有在索引内，所以触发了using filesort，排序未使用索引 #正确做法应该创建一个age,deptid,empno的索引 explain select SQL_NO_CACHE * from emp where age=45 order by deptid,empno;
#字段顺序与索引顺序不一致，导致排序时未使用索引失效出现using filesort #这里的order by的顺序mysql的优化器是不能为我们优化的，因为如果优化的话会影响到我们的结果 #正确做法应该是创建一个顺序为age，name，deptid的索引 explain select SQL_NO_CACHE * from emp where age=45 order by name,deptid;
-
案例3：顺序方式不一致也会导致索引失效
#创建索引 create index idx_age_deptid_name on emp(age,deptid,name);#索引生效，都是采用降序排序 explain select SQL_NO_CACHE * from emp where age=45 order by deptid desc,name desc;
#排序时索引失效，出现了using filesort，因为deptid和name的排序方式不一致 explain select SQL_NO_CACHE * from emp where age=45 order by deptid asc,name desc;
-
案例4：索引选择问题
索引一：#创建索引 create index idx_age_name on emp(age,name);#为什么不创建age，empno，name索引，因为enmno是范围查询，范围右边索引全部失效。 explain select SQL_NO_CACHE * from emp where age=30 and empno<101000 order by name;从分析结果可以看出，使用了索引，行数是47184
索引二：
#创建索引 create index idx_age_empno on emp(age,empno);explain select SQL_NO_CACHE * from emp where age=30 and empno<101000 order by name;从分析结果可以看出，出现了using filesort，排序未使用索引，但是行数只有46，很显然，这个索引的效率更高，所以如果同时出现这两种索引，mysql会优先选择该索引。
4.7.4.2、排序优化
尽量使用Index方式排序,避免使用FileSort方式排序
MySQL支持二种方式的排序：
- Index排序：效率高，它指MySQL扫描索引本身完成排序。
- FileSort排序：效率较低，尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如何才能使用Index方式排序：
- ORDER BY 语句使用索引最左前列
- 使用Where子句与Order BY子句条件列组合满足索引最左前列
- where子句中如果出现索引的范围查询(即explain中出现range)，where中in多个等于条件在排序查询中也属于范围查询，会导致order by 索引失效。但是in多个等值条件在非排序查询中不会导致索引失败。
排序优化总结：
4.7.4.3、FileSort优化
如果不在索引列上，那么将使用filesort排序，它有两种算法：双路排序、单路排序。
双路排序：
- MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出 - 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。（两次）
- 缺点：取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
单路排序：
- 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出。
- 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
- 结论及引申出的问题：
- 结论：由于单路是后出的，总体而言好过双路
- 问题：在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
优化策略：
4.7.4.4、分组优化
分组优化和排序优化一致，不一样的地方就是：没有过滤条件的时候也能使用索引。
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了。
五、查询截取分析
5.1、慢查询日志
5.1.1、什么是慢查询日志
- MySQL的慢查询日志是MySQL提供的一种日志记录，它主要用来记录MySQL中查询过慢的一些SQL。通过设置long_query_time的值来判定是否将SQL语句记录到日志中。
- long_query_time默认的时间是10秒
5.1.2、如何使用
查看日志是否开启以及日志保存路径：
# 查看
SHOW VARIABLES LIKE '%slow_query_log%';
开启慢查询日志：
# 设置开启，只对当前数据库生效
set global slow_query_log=1;
设置慢查询阈值：
# 查看
SHOW VARIABLES LIKE 'long_query_time%';
# 设置1秒
set global long_query_time=1;
# 需要重新连接或新开一个会话才能看到修改值
SHOW VARIABLES LIKE 'long_query_time%';
# 改变当前session变量
set session long_query_time=1;
查看当前系统有多少慢查询的记录：
show global status like '%Slow_queries%';
5.1.3、日志分析工具
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
常用的参数：
- s：表示按何种方式排序
- c：访问次数
- l：索引时间
- r：返回记录
- t：查询时间
- al：平均锁定时间
- ar：平均返回记录数
- at：平均查询时间
- t：返回top N的数据
- g：后面搭配一个正则匹配，不区分大小写
5.2、Show Profile
5.2.1、什么是show profile
主要是用来分析当前会话中执行的语句的资源消耗的情况，可以用于SQL的调优的测量。它在默认情况下关闭的，并保存最近15次的执行情况。
5.2.2、分析步骤
开启Show Profile：
Show variables like 'profiling';
set profiling=1; # 开启
查看记录的执行情况：
show profile;
分析SQL：
show profile cpu,block io for query n (n就是上面查询出来的query id);
命令参数：
- ALL：显示所有的开销信息
- BLOCK IO：显示块IO相关的开销
- CONTEXT SWITCHES：上下文切换相关开销
- CPU：显示CPU相关的开销信息
- IPC：显示发送和接受相关开销信息
- MEMORY：显示内存相关开销信息
- PAGE FAULTS：显示页面错误相关开销信息
- SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息
- SWAPS：显示交换次数相关开销的信息
5.3、全局日志
注：尽量不要在生产环境开启这个功能，因为它非常的消耗资源。
5.3.1、配置文件启用
在mysql的my.cnf配置文件中添加如下配置：
# 开启
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE
5.3.2、命令启用
set global general_log=1;
set global log_output='TABLE';
#这样所有的sql语句的执行都会记录在mysql库中的general_log表中
查看命令：
select * from mysql.general_log;
1115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
