MySQL整理02-存储引擎、索引及SQL调优

最新推荐文章于 2023-03-13 19:55:40 发布

高石柯南

最新推荐文章于 2023-03-13 19:55:40 发布

阅读量331

点赞数

本文链接：https://blog.csdn.net/li210530/article/details/104113358

版权

一、MySQL体系结构

在这里插入图片描述
整个 MySQL Server由以下组成

Connection Pool : 连接池组件
Management Services & Utilities : 管理服务和工具组件
SQL Interface : SQL 接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buffers : 缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统

二、存储引擎

1.概述
和大多数数据库不一样，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择不同的存储引擎。
存储引擎就是存储数据、建立索引、更新查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的。
Oracle、SqlServer等数据库只有一种存储引擎，MySQl提供了插件式的存储引擎，可以根据需要选择，也可以自己编写存储引擎。
相关命令：
show engines;
show variables like ‘%storage_engine%’;
2.各种存储引擎
在这里插入图片描述
2.1 InnoDB
InnoDB存储引擎是Mysql的默认存储引擎。提供了具有提交、回滚、奔溃恢复能力方面的事务安全。但是对比MyISAM的存储引擎，InnoDB的写能力会差一点，并且会占用更多的磁盘空间一保留数据和索引。
存储方式
/var/lib/mysql
InnoDB 存储表和索引有以下两种方式：
①. 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个件。
②. 使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。
2.2 MyISAM
不支持事务
存储方式
每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：
.frm (存储表定义)；
.MYD(MYData , 存储数据)；
.MYI(MYIndex , 存储索引)；

2.3 MEMORY
Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中。

三、SQL优化

3.1 查看SQL执行频率
下面的命令显示了当前 session 中所有统计参数的值：
show status like ‘Com_______’; （7个占位符）
全局信息：
show global status like ‘Com_______’;
InnoDB引擎的数据：
show status like ‘Innodb_rows_%’;
在这里插入图片描述
3.2定位低效率执行SQL
查询实时的状态：
show processlist;

3.3 explain分析执行计划
在这里插入图片描述

3.3.1 explain之id
id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id值越大，越先被执行。

3.3.2 explain之select_type
在这里插入图片描述
3.3.3 explain 之 table
展示这一行的数据是关于哪一张表的

3.3.4 explain之type
type 显示的是访问类型，是较为重要的一个指标。
在这里插入图片描述
一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。

3.3.5 explain之key
possible_keys : 显示可能应用在这张表的索引，一个或多个。
key ：实际使用的索引，如果为NULL，则没有使用索引。
key_len : 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前
提下，长度越短越好。

3.3.6 explain之rows
扫描行的数量

3.3.7 explain之extra
其他的额外的执行计划信息。
在这里插入图片描述
3.4 show profile分析SQL
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling 参数，能够看到当前MySQL是否支持profile。
select @@profiling;
是否开启：
select @@profiling;
开启：
set profiling=1;
使用：
show profiles;
show profile for query query_id;
show profile for query 3;
在这里插入图片描述
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。
show profile cpu for query 3;

3.5 trace分析优化器执行计划
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;

四、索引的使用

4.1 避免索引失效

全值匹配，对索引中所有列都指定具体值。
该情况下，索引生效，执行效率高。
最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
范围查询右边的列，不能使用索引。
explain select * from tb_seller where name=‘小米科技’ and status>‘1’ and address=‘北京市’;
其中最后一个条件address不会走索引。
不要在索引列上进行运算操作，索引将失效。
explain select * from tb_seller where substring(name,3,2)=‘科技’;
字符串不加单引号，造成索引失效。
explain select * from tb_seller where name=‘小米科技’ and status=1;
其中status不走索引。
由于，在查询是，没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效
尽量使用覆盖索引，避免select *
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
以%开头的Like模糊查询，索引失效。
如果仅仅是尾部加%，不会失效。如果是头部加%，不会失效。
explain select * from tb_seller where name like ‘%小米科技’;
解决方案：使用覆盖索引解决，如
explain select sellerid,name from tb_seller where name like ‘%小米科技’;
如果MySQL评估使用索引比全表更慢，则不使用索引。
is NULL ， is NOT NULL 有时索引失效。
in走索引，not in索引失效。
尽量使用复合索引，而少使用单列索引。
CREATE INDEX ind_many ON city(id,name,country);
相当于创建三个索引：
id
id+name
id+name+country
数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。
简单说明：随着设计算法的升级，以上并非绝对的最终法则。索引设计的初衷，是为了减少遍历总数。如最左前缀法则，索引的核心不在于最左或者最右，而在于能否进行最小比例的查找甚至精确查找，最左是因为实现简单。如有些时候创建索引也不会生效，如查询某一列字段，重复率很高，查询不会使用索引，和数据库的数据相关。是否使用索引总的原则就是扫描最少行，由mysql优化器进行分析决定。

4.2 查看索引使用情况（参考）
show status like ‘Handler_read%’;
show global status like ‘Handler_read%’;
在这里插入图片描述

五、SQL优化

5.1 大批量插入数据
如果需要同时对一张表插入很多行数据时，应该尽量较少insert语句数量，，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。
1、可以在一个语句中插入多条
insert into tb_test values(1,‘Tom’),(2,‘Cat’)，(3,‘Jerry’);
2、多条语句一次提交
start transaction;
insert into tb_test values(1,‘Tom’);
insert into tb_test values(2,‘Cat’);
insert into tb_test values(3,‘Jerry’);
commit;
5.2 优化order by语句
排序方式有两种：
1、第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
在这里插入图片描述
2、第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。
3、Filesort的优化
通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。
1）两次扫描算法：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

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

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。
在这里插入图片描述
5.3 优化group by
由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

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

5.4 优化or
1、对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。
2、建议使用union替换or
在这里插入图片描述
UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距。
UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快。
这两项的差距就说明了 UNION 要优于 OR 。

5.5 优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 9900000,10 ，此时需要MySQL排序前9900010 记录，仅仅返回9900000 - 9900010 的记录，其他记录丢弃，查询排序的代价非常大。
在这里插入图片描述
优化思路：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

5.6 使用SQL提示
1、use index
在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引（数据库不一定会真正使用）。
explain select * from teacher use index(idx_name) where name=‘name1’;
在这里插入图片描述
2、ignore index
如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。
explain select * from teacher ignore index(idx_name) where name=‘name1’;

3、force index
为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。
explain select * from teacher force index(idx_name) where name=‘name1’;
在这里插入图片描述

相关链接：
数据库安装及数据库对象
https://blog.csdn.net/li210530/article/details/104100741
存储引擎、索引及SQL调优
https://blog.csdn.net/li210530/article/details/104113358
参数调优及数据库锁
https://blog.csdn.net/li210530/article/details/104128217
常用工具及日志分析
https://blog.csdn.net/li210530/article/details/104146098