sql性能分析和sql优化

在介绍常见的sql优化前我们先了解一下sql性能优化的相关知识。一. 查看数据库的sql执行频率MySQL 客户端连接成功后通过show [session|global] status命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次SHOW GLOBAL STATUS LIKE Com_______%;select权重占比高的则需要考虑进行sql优化二. 慢日志查询慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认 10 秒的所有 SQL 语句的日志。MySQL 的慢查询日志默认没有开启需要在 MySQL 的配置文件/etc/my.cnf中配置如下信息-- 开启MySQL慢日志查询开关 slow_query_log1 -- 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志 long_query_time2三. profileshow profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数能够看到当前 MySQL 是否支持 profile 操作SELECT have_profiling;默认 profiling 是关闭的可以通过 set 语句在 session/global 级别开启 profilingSET profiling 1;执行一系列的业务 SQL 的操作然后通过如下指令查看指令的执行耗时-- 查看每一条SQL的耗时基本情况 show profiles; -- 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; -- 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;四. explain关键字EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。语法-- 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;explain执行计划各字段含义Idselect 查询的序列号表示查询中执行 select 子句或者是操作表的顺序id 相同执行顺序从上到下id 不同值越大越先执行。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION 中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE 之后包含了子查询等。type表示连接类型性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all。possible_key显示可能应用在这张表上的索引一个或多个。Key实际使用的索引如果为 NULL则没有使用索引。Key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好。rowsMySQL认为必须要执行查询的行数在 innodb 引擎的表中是一个估计值可能并不总是准确的。filtered表示返回结果的行数占需读取行数的百分比filtered 的值越大越好。Extra: 这一行展示的是额外信息常见的重要值有.Using index通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高。Using where需要回表再过滤。Using index conditionMySQL 在存储引擎层利用索引中的列先过滤一部分数据再决定是否回表Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序。主要关注type possible_keys key ken_len rows Extra五. sql优化5.1 插入优化I.批量插入Insert into tb_test values(1,Tom),(2,Cat),(3,Jerry); 手动提交事务 start transaction; insert into tb_test values(1,Tom),(2,Cat),(3,Jerry); insert into tb_test values(4,Tom),(5,Cat),(6,Jerry); insert into tb_test values(7,Tom),(8,Cat),(9,Jerry); commit;主键顺序插入主键乱序插入8 1 9 21 88 2 4 15 89 5 7 3主键顺序插入1 2 3 4 5 7 8 9 15 21 88 89II. 大批量插入数据操作如果一次性需要插入大批量数据使用Insert语句插入性能较低此时可以使用MYSQL数据库提供的load指令进行插入。操作如下MySQL 本地数据导入配置-- 客户端连接服务端时加上参数 --local-infile mysql --local-infile -u root -p -- 设置全局参数local_infile为1开启从本地加载文件导入数据的开关 set global local_infile1; -- 执行load指令将准备好的数据加载到表结构中 load data local infile /root/sql1.log into table tb_user fields terminated by , lines terminated by \n;关键参数说明–local-infile客户端连接参数允许从本地文件系统加载数据文件。local_infile1MySQL 全局开关控制是否允许本地数据文件导入。fields terminated by ‘,’指定字段分隔符为逗号。lines terminated by ‘\n’指定行分隔符为换行符。local infile关键字声明从本地文件导入数据。5.2 主键优化主键设计原则· 满足业务需求的情况下尽量降低主键的长度。· 插入数据时尽量选择顺序插入选择使用 AUTO_INCREMENT 自增主键。· 尽量不要使用 UUID 做主键或者是其他自然主键如身份证号。· 业务操作时避免对主键的修改主键尽量选择顺序插入主键乱序插入时可能会导致页分裂什么是页分裂 当向页中插入数据时如果页空间不足Mysql就会创建一个新的页把原页中的部分数据移动到新页。这个过程就叫页分裂。页分裂会带来额外的IO需要写新页移动数据更新索引。并且可能导致BTree的结构调整从而导致树高度增加。而且页分裂会产生数据碎片页不再连续磁盘的随机IO增加。在这里介绍一下页分裂的反过程页合并合并页的阈值可以通过修改MERGE_THRESHOLD 来修改默认为50%。5.3 order by优化mysql中order by 优化的核心目标主要是为了避免Using filesort。可以通过explain关键字的extra列返回的信息判断具体是哪一种如果是desc倒序会出现Backward index scan倒序扫描索引。order by优化时主要注意下面几点· 根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。· 尽量使用覆盖索引· 避免在order by中使用函数或表达式这样索引会失效。· where 和 order by尽量使用同一个联合索引· 多字段排序一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC· 如果不可避免的出现filesort,大数据排序时可以适当增大排序缓冲区大小sort_buffer_size(默认256k)例如-- 根据age, phone进行降序一个升序一个降序 explain select id,age,phone from tb_user order by age asc, phone desc; -- 创建索引 create index idx_user_age_phone_ad on tb_user(age asc, phone desc); -- 根据age, phone进行降序一个升序一个降序 explain select id,age,phone from tb_user order by age asc, phone desc;5.4 group by优化-- 删除掉目前的联合索引 idx_user_pro_age_sta drop index idx_user_pro_age_sta on tb_user; -- 执行分组操作根据profession字段分组 explain select profession,count(*) from tb_user group by profession; -- 创建索引 Create index idx_user_pro_age_sta on tb_user(profession, age, status); -- 执行分组操作根据profession字段分组 explain select profession,count(*) from tb_user group by profession; -- 执行分组操作根据profession字段分组 explain select profession,count(*) from tb_user group by profession, age;核心优化原则在分组操作时可以通过索引来提高效率。分组操作时索引的使用也满足最左前缀法则。5.5 limit优化limit优化主要解决深分页问题。优化策略一使用索引order byegSELECT * FROM orders ORDER BY id LIMIT 10;此时如果id是主键索引BTree索引顺序扫描读取十条结束速度非常快。优化策略二延迟关联错误写法SELECT * FROM orders ORDER BY id LIMIT 100000,10;此时需要回表100000 IO非常大优化SELECT id FROM orders ORDER BY id LIMIT 100000,10;再回表SELECT * FROM orders WHERE id IN ( SELECT id FROM orders ORDER BY id LIMIT 100000,10 )优化策略三使用覆盖索引子查询的形式优化解决深分页limit优化的经典策略SELECT * FROM orders WHERE id IN ( SELECT id FROM orders ORDER BY create_time LIMIT 1000000,10 );创建合适的覆盖索引CREATE INDEX idx_create_time_id ON orders(create_time, id);5.6 count优化count 的几种用法count (主键)InnoDB 引擎会遍历整张表把每一行的主键 id 值都取出来返回给服务层。服务层拿到主键后直接按行进行累加主键不可能为 null。count (字段)没有 not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层服务层判断是否为 null不为 null 则计数累加。有 not null 约束InnoDB 引擎会遍历整张表把每一行的字段值都取出来返回给服务层直接按行进行累加。count(1)InnoDB 引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字 “1” 进去直接按行进行累加。count(*)InnoDB 引擎并不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。效率排序count(字段) count(主键 id) count(1) ≈ count()所以尽量使用 count()。5.7 update语句优化在进行更新操作时一定要根据索引字段进行更新操作否则会将行锁升级成为表锁降低并发性能。InnoDB的行锁是针对索引加的锁并且该索引不能失效否则会从行锁升级成表锁。5.8 小表驱动大表SELECT * FROM t_user u WHERE EXISTS (SELECT 1 FROM t_order o WHERE o.user_id u.id);执行逻辑IN 关键字优先执行子查询IN 内部语句再执行外部查询。若子查询数据量少条件查询速度更快。EXISTS 关键字优先执行主查询EXISTS 左侧语句将结果作为条件与右侧子查询匹配匹配成功则保留数据否则过滤。场景示例order 表10000 条大表user 表100 条小表若 order 表在左侧使用 IN 性能更好。总结IN适用于左边大表右边小表的场景。EXISTS适用于左边小表右边大表的场景。