旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

MySQL数据库SQL语句优化【MySQL教程】,mysql,语句优化

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:71评论:0


导读:推断问题SQL推断SQL是不是有问题时能够经由历程两个表象举行推断:体系级别表象CPU斲丧严峻IO守候严峻页面相应时刻太长...

推断问题SQL

推断SQL是不是有问题时能够经由历程两个表象举行推断:

  • 体系级别表象
    • CPU斲丧严峻
    • IO守候严峻
    • 页面相应时刻太长
    • 运用的日记涌现超时等毛病

能够运用sar敕令,top敕令检察当前体系状况。

也能够经由历程Prometheus、Grafana等监控东西视察体系状况。

  • SQL语句表象
    • 冗杂
    • 实行时刻太长
    • 从全表扫描猎取数据
    • 实行计划中的rows、cost很大

冗杂的SQL都好明白,一段SQL太长浏览性肯定会差,而且涌现问题的频次肯定会更高。更进一步推断SQL问题就得从实行计划入手,以下所示:

实行计划通知我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基础能够推断这是一段"有滋味"的SQL。

猎取问题SQL

差别数据库有差别的猎取要领,以下为现在主流数据库的慢查询SQL猎取东西

  • MySQL
    • 慢查询日记
    • 测试东西loadrunner
    • Percona公司的ptquery等东西
  • Oracle
    • AWR报告
    • 测试东西loadrunner等
    • 相干内部视图如v$sql、v$session_wait等
    • GRID CONTROL监控东西
  • 达梦数据库
    • AWR报告
    • 测试东西loadrunner等
    • 达梦机能监控东西(dem)
    • 相干内部视图如v$sql、v$session_wait等

SQL编写技能

SQL编写有以下几个通用的技能:

• 合理运用索引

索引少了查询慢;索引多了占用空间大,实行增编削语句的时刻须要动态保护索引,影响机能
选择率高(反复值少)且被where频仍援用须要竖立B树索引;平常join列须要竖立索引;庞杂文档范例查询采纳全文索引效力更好;索引的竖立要在查询和DML机能之间获得均衡;复合索引建立时要注意基于非前导列查询的状况

• 运用UNION ALL替换UNION

UNION ALL的实行效力比UNION高,UNION实行时须要排重;UNION须要对数据举行排序

• 防止select * 写法

实行SQL时优化器须要将 * 转成细致的列;每次查询都要回表,不能走掩盖索引。

• JOIN字段发起竖立索引

平常JOIN字段都提早加上索引

• 防止庞杂SQL语句

提拔可浏览性;防止慢查询的几率;能够转换成多个短查询,用营业端处置惩罚

• 防止where 1=1写法

• 防止order by rand()相似写法

RAND()致使数据列被屡次扫描

SQL优化 实行计划

完成SQL优化一定要先读实行计划,实行计划会通知你哪些地方效力低,那里能够须要优化。我们以MYSQL为例,看看实行计划是什么。(每一个数据库的实行计划都不一样,须要自行相识)

字段 诠释
id 每一个被自力实行的操纵标识,标识对象被操纵的递次,id值越大,先被实行,假如雷同,实行递次从上到下
select_type 查询中每一个select 字句的范例
table 被操纵的对象称号,通常是表名,但有其他花样
partitions 婚配的分区信息(关于非分区表值为NULL)
type 衔接操纵的范例
possible_keys 大概用到的索引
key 优化器现实运用的索引(最主要的列) 从最好到最差的衔接范例为consteq_regrefrangeindexALL。当涌现ALL时示意当前SQL涌现了“坏滋味”
key_len 被优化器选定的索引键长度,单元是字节
ref 示意本行被操纵对象的参照对象,无参照对象为NULL
rows 查询实行所扫描的元组个数(关于innodb,此值为估计值)
filtered 前提表上数据被过滤的元组个数百分比
extra 实行计划的主要补充信息,当此列涌现Using filesort , Using temporary 字样时就要警惕了,极大概SQL语句须要优化

接下来我们用一段现实优化案例来申明SQL优化的历程及优化技能。

优化案例

  • 表构造

  • CREATE TABLE `a` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_id` bigint(20) DEFAULT NULL, `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `b` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_name` varchar(100) DEFAULT NULL, `user_id` varchar(50) DEFAULT NULL, `user_name` varchar(100) DEFAULT NULL, `sales` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `c` ( `id` int(11) NOT NULLAUTO_INCREMENT, `user_id` varchar(50) DEFAULT NULL, `order_id` varchar(100) DEFAULT NULL, `state` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );
  • 三张表关联,查询当前用户在当前时刻前后10个小时的定单状况,并依据定单建立时刻升序分列,细致SQL以下

  • select a.seller_id,          a.seller_name,          b.user_name,          c.state   from a,        b,        c   where a.seller_name = b.seller_name     and b.user_id = c.user_id     and c.user_id = 17     and a.gmt_create       BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)       AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)   order by a.gmt_create
  • 检察数据量

  • 原实行时刻

  • 原实行计划

  • 开端优化思绪

  • SQL中 where前提字段范例要跟表构造一致,表中user_id 为varchar(50)范例,现实SQL用的int范例,存在隐式转换,也未增添索引。将b和c表user_id 字段改成int范例。

  • 因存在b表和c表关联,将b和c表user_id建立索引

  • 因存在a表和b表关联,将a和b表seller_name字段建立索引

  • 应用复合索引消弭暂时表和排序

  • 开端优化SQL

  • alter table b modify `user_id` int(10) DEFAULT NULL;   alter table c modify `user_id` int(10) DEFAULT NULL;   alter table c add index `idx_user_id`(`user_id`);   alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);   alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
  • 检察优化后实行时刻

  • 检察优化后实行计划

  • 检察warnings信息

  • 继承优化

  • alter table a modify "gmt_create" datetime DEFAULT NULL
  • 检察实行时刻

  • 检察实行计划

优化总结

  1. 检察实行计划 explain
  2. 假如有告警信息,检察告警信息 show warnings;
  3. 检察SQL触及的表构造和索引信息
  4. 依据实行计划,思索大概的优化点
  5. 根据大概的优化点实行表构造变动、增添索引、SQL改写等操纵
  6. 检察优化后的实行时刻和实行计划

假如优化结果不明显,反复第四步操纵

引荐 《mysql视频教程》

以上就是MySQL数据库SQL语句优化的细致内容,更多请关注ki4网别的相干文章!

标签:mysql语句优化


欢迎 发表评论: