mysql分析之profile详解（过去用法，但常用）+ MySQL 索引

「这是我参与2022首次更文挑战的第12天，活动详情查看：2022首次更文挑战」

在正式学习前需要测试当前的 MySQL 版本是否支持 profile ，具体命令如下所示：

select @@have_profiling

使用该命令可以获取一个查询在整个执行过程中各个资源消耗情况，例如 CPU，IO，SWAP…… 如果支持还需要查看一下 profile 是否开启，命令如下：

select @@profiling

如果没有开启，可以通过 set profiling = 1 开启该配置。

接下来建造一张百万数据的表，然后执行下述查询。

select * from 表名;

查询一次数据，然后执行 show profiles; ，（该语句用来查看上一条SQL语句的开销信息）

在展示出的 profiles 【剖析】中找到 QueryID ，然后在使用如下命令查看详情。

show profile for query query_id;

例如使用刚刚得到的 QueryID ，即 97 进行查询。

show profile for query 97;

如果查询 profiles 的同时查看了 CPU 相关值。

show profile cpu for query 97;

• CPU_user：当前用户占用的 CPU；
• CPU_system：当前系统占用的CPU。

查看 MEMORY 部分的开销，不过这个应该是还没有实现，查询之后并无效果。

show profile memory for query 47

查看 io 部分的开销。

show profile block io for query 3

查看所有参数，使用如下命令：

show profile all for query 41

在未来的版本中 show profiles; 可能不在支持，建议使用 Performance Schema ，具体命令如下所示

select * from information_schema.profiling

接下来重点说明下表内容

• System lock：内核锁；
• Sending data：从服务端发送到客户端的数据，数据量大时会出现耗时长情况，注意该内容不是网络发送，是硬盘读取；

如果学习更多可以借助一下官方手册 show-profile。

本篇博客再次去复盘 MySQL 中的索引，从该角度学习 MySQL 的优化。

索引

索引不是越多越好，因为数据库在插入，删除，更新数据时，都会更新索引，导致效率降低。

新建索引的时候优先选择离散度高的索引（列中不同值多的，适合做索引，例如 id 适合，status 状态不适合，因为一般只有2个值），如果一个列中 NULL 值特别多，也不适合做索引列。

如果仅依赖一列无法做到唯一，可以选择几列同时作为索引，即复合索引，复合索引要优先把离散度高的列放在前面，形式上是左侧。

优化细节

尽量避免 select * 的出现，在仅有主键索引的情况下，下述命令时间差别（假设查询的是一个超过百万数据的表格）

select * from 百万表名 # 耗时 1.9s
select id,name,age from 百万表名 # 耗时 1.5s

如果确定一张表中主键没有间隔，即完整连续，可以使用下述分页办法提高查询效率。

select id,name from 百万表名 limit 1000000,10000; # 0.25秒

select id,name from 百万表名 where id > 1000000 limit 10000; # 0.024秒

在仅有主键索引的情况下，速度提高了 10 倍。

where 查询注意事项

1. 优先使用带索引的列，例如下述两个查询，得到的结果一样，但耗时却相差较大

select id,name from 百万表名 where id =5 # 0.02秒
select id,name from 百万表名 where tid = 88888 # 0.3秒

2. 先使用索引，然后在进行正向过滤（=，in，<，>），最后执行复杂条件（子查询，like 模糊查询）
3. 优先过滤数字型字段，然后在选择字符串；
4. 条件里面字段类型要明确，例如数字型字段=数字，字符型字段=字符
5. 不用OR，而用 union 或 union all 进行组合 union 操作符用于合并两个或多个 select 语句的结果集，前提是结果集的列数相同，每列的数据类型也相同。

• union：合并两个查询结果中相同的数据；
• union all：不合并两个查询结果中的相同数据 可以对比下述语句的执行计划

select * from `百万表名` where id = 5 or id = 1 order by id desc 

(select * from `百万表名` where id = 5 order by id desc)
union all
(select * from `百万表名` where id = 1 order by id desc)
order by null

6. 尽量使用 = 替换 in，尽量避免使用非正向过滤，例如 !，!=，not in，not exists，not like
7. 尽量不使用 %匹配内容。
8. 如果在 in 中查询的数据是连续的，可以使用 between...and... 代替；
9. 实战中也可以使用 exists 代替 in

select id from `百万表名` where exists(select 1 from 另一张表 where id=`百万表名`.id)； 

影响数据库性能的参数

公共参数及其默认值

同时最大连接数

max_connections = 151

查询排序时缓冲区大小，仅限制 order by 和 group by

sort_buffer_size = 2M

打开文件数限制

open_files_limit = 1024 

InnoDB参数及其默认值

查看所有配置使用如下命令

show variables like 'innodb_%';

索引和数据缓冲区大小

innodb_buffer_pool_size = 128M # 建议设置内存大小的60%~70%

缓冲池实例个数

innodb_buffer_pool_instances = 1   # 建议设置为 4 或者 8

控制MySQL的磁盘写入策略

innodb_flush_log_at_trx_commit = 1  

这部分找到一篇讲解的不错的 博客，可以参考学习

是否共享表空间

innodb_file_per_table = OFF 

关闭独立表空间将导致共享表空间 idbdata 持续增大，从而影响 I/O 性能，可以修改为开启独立表空间模式。

innodb_file_per_table = 1 # 为使用独占表空间
innodb_file_per_table = 0 # 为使用共享表空间

与之对应的几个配置如下所示： 修改独占空表空间的数据存储位置

innodb_data_home_dir = "C:\mysql\data\" # 数据库文件所存放的目录
innodb_log_group_home_dir = "C:\mysql\data\" #  日志存放目录
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1

日志缓冲区大小

innodb_log_buffer_size = 8M  

记录时间

今天是持续写作的第 288 / 365 天。 可以关注我，点赞我、评论我、收藏我啦。