精华内容
下载资源
问答
  • 本篇文章是对MySQL优化sql语句查询常用的30种方法进行了详细的分析介绍,需要的朋友参考下
  • mysql 优化sql语句的几种方法

    千次阅读 2019-06-10 22:38:09
    优化sql语句的几种方法 1、通过show status命令了解SQL的执行效率 show [session|global]status like 'com_%'; 2、定位执行效率较低的SQL语句 1)慢查询  开启方法 linux: 配置文件(/...

    转载:https://www.cnblogs.com/kongzhongqijing/articles/3544822.html

    优化sql语句的几种方法

    1、通过show status命令了解SQL的执行效率

    • show [session|global]status like 'com_%';

    2、定位执行效率较低的SQL语句

    1)慢查询

      开启方法

    复制代码

    linux:
    配置文件(/etc/my.cnf)的[mysqld]中增加
    log-slow-queries=/var/log/mysql/slowquery.log(指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)
    long_query_time=2(记录超过的时间,默认为10s)
    log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
    log-long-format (如果设置了,所有没有使用索引的查询也将被记录)
    
    windows:
    在my.ini的[mysqld]添加如下语句:
    log-slow-queries =E:\web\mysql\log\mysqlslowquery.log
    long_query_time = 2(其他参数如上)

    复制代码

      查看慢查询方法

    复制代码

    使用mysql自带命令mysqldumpslow查看
    mysqldumpslow -s c -t 20 -g "left join" host-slow.log
    -s,表示按照何种方式排序,c,t,l,r分别表示按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒序;
    -t,是top n的意思,即为返回前面多少条的数据;
    -g,后边可以写一个正则匹配模式,大小写不敏感的;
    

     mysqldumpslow -s c -t 20 host-slow.log

     mysqldumpslow -s r -t 20 host-slow.log

     上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

     mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。

    复制代码

      查看慢查询还有一个方法即使用mysqlsla工具,大部分都使用这个方法,方法可参考 mysqlsla快速入门

    慢查询开启与方法可见mysqldumpslow和mysqlsla分析mysql慢查询日志

    MySQL慢日志查询全解析:从参数、配置到分析工具


    2)show processlist

     

    3、使用explain分析SQL.在 explain的帮助下,您就知道什么时候该给表添加索引,以使用索引来查找记录从而让select 运行更快。

    explain [extended] select … from … where …

    如果使用了extended,那么在执行完explain语句后,可以使用show warnings语句查询相应的优化信息。

    复制代码

    执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000 得到如下结果:
    
    
    显示结果分析:
    table |  type | possible_keys | key |key_len  | ref | rows | Extra 
    EXPLAIN列的解释: 
    table 
    显示这一行的数据是关于哪张表的 
    type 
    这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 
    possible_keys 
    显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 
    key 
    实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 
    key_len 
    使用的索引的长度。在不损失精确性的情况下,长度越短越好 
    ref 
    显示索引的哪一列被使用了,如果可能的话,是一个常数 
    rows 
    MYSQL认为必须检查的用来返回请求数据的行数 
    Extra 
    关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 
    
    extra列返回的描述的意义 
    Distinct 
    一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 
    Not exists 
    MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 
    就不再搜索了 
    Range checked for each 
    Record(index map:#) 
    没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 
    Using filesort 
    看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 
    Using index 
    列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 
    Using temporary 
    看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上 
    Where used 
    使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题
    
    不同连接类型的解释(按照效率高低的顺序排序) 
    system 
    表只有一行:system表。这是const连接类型的特殊情况 
    const 
    表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待 
    eq_ref 
    在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用 
    ref 
    这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好 
    range 
    这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况 
    index 
    这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据) 
    ALL 
    这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

    复制代码

     

    可参考:mysql  explain语句查询索引效率

     

    4、性能优化器 profile 

    可以先使用

    mysql> SELECT @@profiling;

    +-------------+

    | @@profiling |

    +-------------+

    |          0 |

    +-------------+

    1 row in set (0.00 sec)来查看是否已经启用profile,如果profilng值为0,可以通过

    mysql> SET profiling = 1;

    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT @@profiling;

    +-------------+

    | @@profiling |

    +-------------+

    |          1 |

    +-------------+

    1 row in set (0.00 sec)

    来启用。启用profiling之后,我们执行一条查询语句,比如:

    SELECT * FROM res_user ORDER BY modifiedtimeLIMIT 0,1000

    mysql> show profiles;

    +----------+------------+-------------------------------------------------------------+

    | Query_ID | Duration   | Query                                                      |

    +----------+------------+-------------------------------------------------------------+

    |        1| 0.00012200 | SELECT @@profiling                                         |

    |        2| 1.54582000 | SELECT res_id FROM res_user ORDER BY modifiedtime LIMIT 0,3 |

    +----------+------------+-------------------------------------------------------------+

    2 rows in set (0.00 sec)  注意:Query_ID表示刚执行的查询语句

     

    mysql> show profile for query 2;

    +--------------------------------+----------+

    | Status                         | Duration |

    +--------------------------------+----------+

    | starting                       | 0.000013 |

    | checking query cache for query | 0.000035 |

    | Opening tables                 | 0.000009 |

    | System lock                    | 0.000002 |

    | Table lock                     | 0.000015 |

    | init                           | 0.000011 |

    | optimizing                     | 0.000003 |

    | statistics                     | 0.000006 |

    | preparing                      | 0.000006 |

    | executing                      | 0.000001 |

    | Sorting result                 | 1.545565 |

    | Sending data                   | 0.000038 |

    | end                            | 0.000003 |

    | query end                      | 0.000003 |

    | freeing items                  | 0.000069 |

    | storing result in query cache  | 0.000004 |

    | logging slow query             | 0.000001 |

    | logging slow query             | 0.000033 |

    | cleaning up                    | 0.000003 |

    +--------------------------------+----------+

    19 rows in set (0.00 sec)

     

    结论:可以看出此条查询语句的执行过程及执行时间,总的时间约为1.545s。这时候我们再执行一次。

     

    mysql> SELECT res_id FROM res_user ORDERBY modifiedtime LIMIT 0,3;

    +---------+

    | res_id |

    +---------+

    | 1000305 |

    | 1000322 |

    | 1000323 |

    +---------+

    3 rows in set (0.00 sec)

     

    mysql> show profiles;

    +----------+------------+-------------------------------------------------------------+

    | Query_ID | Duration   | Query                                                      |

    +----------+------------+-------------------------------------------------------------+

    |       1 | 0.00012200 | SELECT @@profiling                                          |

    |       2 | 1.54582000 | SELECT res_id FROM res_userORDER BY modifiedtime LIMIT 0,3 |

    |        3 | 0.00006500 | SELECT res_id FROMres_user ORDER BY modifiedtime LIMIT 0,3 |

    +----------+------------+-------------------------------------------------------------+

    3 rows in set (0.00 sec)

     

    mysql> show profile for query 3;

    +--------------------------------+----------+

    | Status                         | Duration |

    +--------------------------------+----------+

    | starting                       | 0.000013 |

    | checking query cache for query | 0.000005|

    | checking privileges on cached  | 0.000003 |

    | sending cached result to clien | 0.000040|

    | logging slow query             | 0.000002 |

    | cleaning up                    | 0.000002 |

    +--------------------------------+----------+

    6 rows in set (0.00 sec)

     

    结论:可以看出此次第二次查询因为前一次的查询生成了cache,所以这次无需从数据库文件中再次读取数据而是直接从缓存中读取,结果查询时间比第一次快多了(第一次查询用了1.5秒而本次用了不到5毫秒)。

     

    5、统计信息

    表统计信息 — SHOW TABLE STATUS

    索引统计信息 — SHOW INDEX from table 

     


    mysql profile explain slow_query_log分析优化查询

    在做性能测试中经常会遇到一些sql的问题,其实做性能测试这几年遇到问题最多还是数据库这块,要么就是IO高要么就是cpu高,所以对数据的优化在性能测试过程中占据着很重要的地方,msyql性能调优过程中经常用到的三件利器:

    1、慢查询 (分析出现问题的sql)

    2、Explain (显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句)

    3、Profile(查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.)

     


    日志相关命令

    是否启用了日志: show variables like 'log_%';

    日志状态: show master status;

    日志信息: show master logs;

     

    参考资料:

    1、mysqldumpslow和mysqlsla分析mysql慢查询日志

    2、mysqlsla快速入门

    3、如何用一款小工具大大加速MySQL SQL语句优化(附源码)

    展开全文
  • 本篇文章我们将了解ORDER BY语句优化,在文中给大家提到了mysql中的两排序方式,需要的朋友参考下吧
  • Mysql语句优化最新入门,公司要求的基础优化,程序员必须掌握的基础sql优化
  • 主要介绍了MySQL对limit查询语句优化方法,分别讲解了offset参数比较小和offset参数比较大的时候,如何优化查询语句,需要的朋友可以参考下
  • mysql的sql语句优化方法面试题总结.docxmysql的sql语句优化方法面试题总结.docxmysql的sql语句优化方法面试题总结.docxmysql的sql语句优化方法面试题总结.docxmysql的sql语句优化方法面试题总结.docx
  • Mysql性能优化 - 优化INSERT语句

    千次阅读 2017-09-17 18:13:10
    Mysql性能优化 - 优化INSERT语句1.插入流程 1.连接到数据库服务器 2.向服务器发送sql 3.解析sql 4.打开欲操作的表 5.执行insert 6.更新索引 7.结束2.一次插入多条数据insert into tab_nm1 (key1,key2) values ...

    Mysql性能优化 - 优化INSERT语句

    1.插入流程

    
    1.连接到数据库服务器
    2.向服务器发送sql
    3.解析sql
    4.打开欲操作的表
    5.执行insert
    6.更新索引
    7.结束

    2.一次插入多条数据

    insert into tab_nm1 
    (key1,key2) 
    values
    (value1,value2)
    (value1,value2)
    (value1,value2)
    (value1,value2)
    (value1,value2)

    3.LOAD_DATA_INFILE

    // 如果你从文本文件加载表,那么load_data_infile的速度比insert快20倍左右

    4.利用默认值

    只有当需要插入的值与默认值不同的时候,才填写该值。可以加快sql的解析速度,并提高insert效率

    5.批量insert优化

    1.在使用批量insert的时候,数据最好不要超过1000行,特别需要注意的是,如有有涉及到表复制争用的时候。
    2.在进行大批量的insert之前,最好根据主键或者unique键先进行排序要插入的行,这样做的目的是帮助缓存
    快速流动,并尽量避免死锁
    展开全文
  • MySQL优化系列17-MySQL SQL语句优化

    千次阅读 2021-07-21 14:05:49
    备注:测试数据库版本为MySQL 8.0 文章目录一.优化select语句1.1 where...优化DML语句2.1 优化insert语句2.2 优化update语句2.3 优化delete语句参考: 一.优化select语句 1.1 where子句优化 剔除重复的条件 ((a AND b

    备注:测试数据库版本为MySQL 8.0

    一.优化select语句

    1.1 where子句优化

    1. 剔除重复的条件
       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)
    
    1. 常数合并
       (a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5
    
    1. 常数条件删除
       (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
    -> b=5 OR b=6
    

    1.2 IS NULL 优化

    如果WHERE子句包含声明为NOT NULL的列的col_name IS NULL条件,则该表达式将被优化掉。如果列无论如何都可能产生NULL(例如,如果它来自LEFT JOIN右侧的表),则不会发生这种优化。

    MySQL还可以优化col_name = expr OR col_name IS NULL的组合,这种形式在已解析的子查询中很常见。当使用此优化时,EXPLAIN显示ref_or_null。

    这种优化可以处理任何关键部分的一个IS NULL。

    1.3 ORDER BY优化

    1. 使用索引来满足ORDER BY
      在这个查询中,(key_part1, key_part2)上的索引使优化器能够避免排序:
    SELECT * FROM t1
      ORDER BY key_part1, key_part2;
    

    但是,查询使用SELECT *,它可能比key_part1和key_part2选择更多的列。在这种情况下,扫描整个索引并查找表行以找到索引中没有的列,可能比扫描表并对结果排序代价更高。如果是这样,优化器可能不会使用索引。如果SELECT *只选择索引列,则使用索引并避免排序。

    如果t1是一个InnoDB表,表的主键是隐式索引的一部分,索引可以用来解析这个查询的ORDER BY:

    SELECT pk, key_part1, key_part2 FROM t1
      ORDER BY key_part1, key_part2;
    
    1. 使用文件排序满足ORDER BY
      如果索引不能满足ORDER BY子句,MySQL将执行一个filesort操作,读取表行并对它们进行排序。文件排序在查询执行中构成了一个额外的排序阶段。

    为了获得用于filesort操作的内存,从MySQL 8.0.12开始,优化器根据需要递增分配内存缓冲区,直到sort_buffer_size系统变量指定的大小,而不是像MySQL 8.0.12之前那样预先分配固定数量的sort_buffer_size字节。这允许用户将sort_buffer_size设置为更大的值,以加快更大的排序,而不必担心小排序使用过多内存。(对于Windows上的多个并发排序来说,这个好处可能不会出现,因为Windows具有弱多线程malloc。)

    如果结果集太大,内存无法容纳,那么filesort操作将根据需要使用临时磁盘文件。有些类型的查询特别适合于完全在内存中的文件导出操作。例如,在没有临时文件的情况下,优化器可以使用filesort在内存中有效地处理以下形式的查询(和子查询)的ORDER BY操作:

    SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
    SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
    
    1. 影响order by优化
      对于没有使用filesort的低速ORDER BY查询,请尝试将max_length_for_sort_data系统变量降低到适合触发filesort的值。(将该变量的值设置得过高的症状是磁盘活动过多而CPU活动过少。)这种技术只适用于MySQL 8.0.20之前。从8.0.20开始,max_length_for_sort_data已被弃用,因为优化器的更改使其过时且无效。

    为了提高ORDER BY的速度,请检查是否可以让MySQL使用索引而不是额外的排序阶段。如果这是不可能的,试试下面的策略:

    增加sort_buffer_size变量值。理想情况下,该值应该足够大,以使整个结果集适合排序缓冲区(以避免写入磁盘和合并传递)。

    要考虑到排序缓冲区中存储的列值的大小受max_sort_length系统变量值的影响。例如,如果元组存储长字符串列的值,你增加max_sort_length的值,排序缓冲区元组的大小也会增加,可能需要你增加sort_buffer_size。

    要监视合并传递(合并临时文件)的数量,请检查Sort_merge_passes状态变量。

    增加read_rnd_buffer_size变量值,以便一次读取更多行。

    将系统变量tmpdir更改为指向具有大量空闲空间的专用文件系统。变量值可以列出几种循环方式使用的路径;您可以使用此特性将负载分散到多个目录。在Unix上用冒号(:)分隔路径,在Windows上用分号(;)分隔路径。路径应该命名位于不同物理磁盘上的文件系统中的目录,而不是同一磁盘上的不同分区。

    1. ORDER BY 可用的执行计划信息
    "filesort_summary": {
      "rows": 100,
      "examined_rows": 100,
      "number_of_tmp_files": 0,
      "peak_memory_used": 25192,
      "sort_mode": "<sort_key, packed_additional_fields>"
    }
    

    Peak_memory_used表示排序过程中任意时刻使用的最大内存。这个值相当于系统变量sort_buffer_size的值,但不一定比它大。在MySQL 8.0.12之前,输出显示的是sort_buffer_size,表示sort_buffer_size的值。(在MySQL 8.0.12之前,优化器总是为排序缓冲区分配sort_buffer_size字节。从8.0.12开始,优化器增量地分配排序缓冲区内存,从少量开始,必要时增加更多,直到sort_buffer_size字节。)

    1.4 group by 优化

    满足GROUP BY子句最通用的方法是扫描整个表并创建一个新的临时表,其中每个组中的所有行都是连续的,然后使用这个临时表发现组并应用聚合函数(如果有的话)。在某些情况下,MySQL能够做得更好,并通过使用索引访问避免创建临时表。

    为GROUP BY使用索引的最重要的先决条件是,所有GROUP BY列引用来自同一索引的属性,并且索引按顺序存储键(例如,对于BTREE索引是这样的,而对于HASH索引则不是)。是否可以用索引访问代替临时表的使用还取决于在查询中使用索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。

    有两种方法可以通过索引访问执行GROUP BY查询,详细内容将在下面的部分中介绍。第一种方法与所有范围谓词(如果有的话)一起应用分组操作。第二种方法首先执行范围扫描,然后对产生的元组进行分组。

    1.4.1 宽松的索引扫描

    处理GROUP BY最有效的方法是使用索引直接检索分组列。使用这种访问方法,MySQL使用一些索引类型的属性,键是有序的(例如,BTREE)。此属性允许在索引中使用查找组,而不必考虑索引中满足所有WHERE条件的所有键。这种访问方法只考虑索引中键的一部分,因此称为松散索引扫描。当没有WHERE子句时,松散索引扫描读取与组数量一样多的键,组的数量可能比所有键的数量要少得多。如果WHERE子句包含范围谓词,那么松散索引扫描将查找满足范围条件的每个组的第一个键,并再次读取尽可能小的键数。这在下列情况下是可能的:

    1. 查询只针对一个表。

    2. GROUP BY只命名构成索引最左边前缀的列,而不命名其他列。(如果查询有DISTINCT子句,而不是groupby,则所有不同的属性都指向构成索引最左边前缀的列。)例如,如果表t1在(c1,c2,c3)上有索引,那么如果查询具有groupby c1,c2,则适用松散索引扫描。如果查询具有GROUP BY c2, c3(列不是最左边的前缀)或GROUP BY c1, c2, c4 (c4不在索引中),则不适用。

    3. 在选择列表中使用的唯一聚合函数(如果有的话)是MIN()和MAX(),它们都指向同一列。列必须在索引中,并且必须紧跟着GROUP BY中的列。

    4. 除了查询中引用的GROUP BY之外,索引的其他部分必须是常量(也就是说,它们必须在带有常量的等式中引用),MIN()或MAX()函数的参数除外。

    5. 对于索引中的列,必须索引完整的列值,而不仅仅是一个前缀。例如,对于c1 VARCHAR(20), INDEX (c1(10)),索引只使用c1值的前缀,不能用于松散索引扫描。

    1.4.2 紧索引扫描

    紧索引扫描可以是完整索引扫描,也可以是范围索引扫描,这取决于查询条件。

    当不满足松散索引扫描的条件时,仍然可以避免为GROUP BY查询创建临时表。如果WHERE子句中有range条件,此方法只读取满足这些条件的键。否则,执行索引扫描。因为这个方法读取WHERE子句定义的每个范围中的所有键,或者如果没有范围条件,则扫描整个索引,所以它被称为紧索引扫描。使用紧索引扫描,只有在找到满足范围条件的所有键后才执行分组操作。

    要使此方法工作,对于引用GROUP BY键的部分之前或之间的部分的查询中的所有列,有一个常量相等条件就足够了。相等条件中的常量将填入搜索键中的任何“空白”,这样就可以形成索引的完整前缀。这些索引前缀可以用于索引查找。如果GROUP BY结果需要排序,并且有可能形成索引前缀的搜索键,MySQL也避免了额外的排序操作,因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

    假设表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3,c4)。以下查询不能使用前面描述的松散索引扫描访问方法,但仍然可以使用紧密索引扫描访问方法。

    1. GROUP BY中有一个缺口,但它被条件c2 = 'a’所覆盖:
    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
    
    1. GROUP BY不以键的第一部分开始,但有一个条件为该部分提供一个常量:
    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
    

    1.5 distinct优化

    在许多情况下,DISTINCT结合ORDER BY需要一个临时表。

    因为DISTINCT可能使用GROUP BY,所以了解MySQL如何使用ORDER BY或HAVING子句来处理不属于所选列的列。

    在大多数情况下,一个DISTINCT子句可以被认为是GROUP BY的特殊情况。例如,下面两个查询是等价的:

    SELECT DISTINCT c1, c2, c3 FROM t1
    WHERE c1 > const;
    
    SELECT c1, c2, c3 FROM t1
    WHERE c1 > const GROUP BY c1, c2, c3;
    

    由于这种等价性,适用于GROUP BY查询的优化也可以应用于具有DISTINCT子句的查询。因此,关于DISTINCT查询的优化可能性的更多细节。

    当LIMIT row_count和DISTINCT组合时,MySQL一旦发现row_count唯一的行就会停止。

    如果没有使用查询中命名的所有表中的列,MySQL一旦找到第一个匹配项就停止扫描任何未使用的表。在下面的例子中,假设t1在t2之前使用(你可以用EXPLAIN检查),MySQL在t2中找到第一行时停止从t2中读取(对于t1中的任何特定行):

    SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
    

    1.6 Limit优化

    如果只需要结果集中指定的行数,则在查询中使用LIMIT子句,而不是获取整个结果集并扔掉额外的数据。

    MySQL有时会优化一个有LIMIT row_count子句和没有HAVING子句的查询:

    1. 如果使用LIMIT只选择几行,MySQL在通常倾向于执行全表扫描的情况下,会在某些情况下使用索引。

    2. 如果将LIMIT row_count和ORDER BY结合,MySQL在找到排序结果的第一个row_count行时就会停止排序,而不是对整个结果进行排序。如果排序是通过使用索引来完成的,那么这是非常快的。如果必须执行文件排序,那么将选择不带LIMIT子句的所有匹配查询的行,并在找到第一个row_count之前对它们中的大多数或所有行进行排序。在找到初始行之后,MySQL不会对结果集的任何剩余部分进行排序。
      这种行为的一种表现是,带和不带LIMIT的ORDER BY查询可能以不同的顺序返回行。

    3. 如果你把LIMIT row_count和DISTINCT结合起来,MySQL一旦发现row_count唯一的行就会停止。

    4. 在某些情况下,可以通过按顺序读取索引(或对索引进行排序)来解析GROUP BY,然后计算摘要,直到索引值发生变化。在这种情况下,LIMIT row_count不计算任何不必要的GROUP BY值。

    5. 一旦MySQL向客户端发送了所需的行数,它就会中止查询,除非您使用SQL_CALC_FOUND_ROWS。在这种情况下,可以使用SELECT FOUND_ROWS()检索行数。

    6. LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用MySQL API的应用程序中获取结果列的类型,该API使结果集元数据可用。在mysql客户端程序中,你可以使用——column-type-info选项来显示结果列类型。

    7. 如果服务器使用临时表来解析查询,它将使用LIMIT row_count子句来计算需要多少空间。

    8. 如果没有为ORDER BY使用索引,但也有LIMIT子句,那么优化器可能能够避免使用合并文件,并使用内存中的文件ort操作对内存中的行进行排序。

    1.7 避免全表扫描

    当MySQL使用全表扫描来解析查询时,EXPLAIN的输出在类型列中显示ALL。这种情况通常发生在以下情况下:

    1. 表很小,执行表扫描比查找键要快得多。对于少于10行且行长度较短的表,这很常见。

    2. 索引列的ON或WHERE子句中没有可用的限制。

    3. 您正在将索引列与常量值进行比较,而MySQL已经计算出(基于索引树)常量覆盖了表的很大一部分,并且表扫描会更快。

    4. 您正在通过另一列使用具有低基数的键(许多行匹配键值)。在这种情况下,MySQL假设通过使用键可能需要很多键查找,并且表扫描会更快。

    对于小型表,表扫描通常是合适的,性能影响可以忽略不计。对于大型表,尝试以下技术以避免优化器错误地选择表扫描:

    1. 使用ANALYZE TABLE tbl_name更新被扫描表的键分布。

    2. 对扫描的表使用FORCE INDEX,告诉MySQL表扫描比使用给定的索引昂贵:

    SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
      WHERE t1.col_name=t2.col_name;
    

    使用–max-seek -for-key=1000选项启动mysqld,或者使用SET max_seeks_for_key=1000来告诉优化器假设没有键扫描导致超过1000个键寻找。

    二.优化DML语句

    2.1 优化insert语句

    为了优化插入速度,将许多小操作组合成一个大操作。理想情况下,创建一个连接,一次发送多个新行数据,并将所有索引更新和一致性检查延迟到最后。

    插入一行所需的时间由下列因素决定,其中数字表示近似比例:

    1. 连接:(3)
    2. 向服务器发送查询:(2)
    3. 解析查询:(2)
    4. 插入行:(1 ×行大小)
    5. 插入索引:(1 ×索引数)
    6. 关闭:(1)

    这并不考虑打开表的初始开销,对于每个并发运行的查询,初始开销都要执行一次。

    假设索引是B-tree,表的大小会使索引的插入速度慢log N。

    您可以使用以下方法加快插入速度:

    1. 如果您在同一时间插入来自同一客户端的多行,那么使用INSERT语句和多个VALUES列表来一次插入多行。这比使用单独的单行INSERT语句要快得多(在某些情况下快很多倍)。如果要向非空表添加数据,可以调优bulk_insert_buffer_size变量,使数据插入更快。

    2. 当从文本文件加载表时,使用LOAD DATA。这通常比使用INSERT语句快20倍。

    3. 利用列具有默认值这一事实。只有当要插入的值与默认值不同时才显式插入值。这减少了MySQL必须做的解析,并提高了插入速度。

    2.2 优化update语句

    更新语句与SELECT查询一样进行了优化,但增加了写入的开销。写的速度取决于要更新的数据量和要更新的索引数量。未更改的索引不会更新。

    获得快速更新的另一种方法是延迟更新,然后在稍后连续进行许多更新。同时执行多个更新比一次只执行一个更新要快得多。

    对于使用动态行格式的MyISAM表,将一行更新为更长的总长度可能会拆分该行。如果您经常这样做,那么偶尔使用OPTIMIZE TABLE是非常重要的。

    2.3 优化delete语句

    在MyISAM表中删除单个行所需的时间与索引的数量刚好成正比。要更快地删除行,可以通过增加key_buffer_size系统变量来增加键缓存的大小。

    要删除MyISAM表中的所有行,TRUNCATE table tbl_name比delete from tbl_name更快。截断操作不是事务安全的;在活动事务或活动表锁的过程中尝试使用一个锁时,会发生错误。

    参考:

    1. https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html
    展开全文
  • mysql 语句优化

    千次阅读 2018-11-07 10:37:31
    检查代码,寻找到一处sql 语句,然后分析其执行计划。 这段SQL想要得到的结果是appid 为100032下面,打包状态小于3,或者测试状态小于3 且测试状态不等于初始态的结果集。 通过mysql explain 分析其执行计划。...

    在项目中发现一个页面加载速度超级慢,时长超过5s 时间,简直不能忍受,

    检查代码,寻找到一处sql 语句,然后分析其执行计划。

    这段SQL想要得到的结果是appid 为100032下面,打包状态小于3,或者测试状态小于3 且测试状态不等于初始态的结果集。

    通过mysql explain 分析其执行计划。发现虽然在appid 这个字段上建立了索引。但是索引并未生效。近乎全表式的扫描。

    当数据量大的时候,查询性能会大打折扣。

     

    分析原因是因为加了or ,并且or 后面的语句并未加入索引查询条件,索引or 后面的语句进行了全表扫描。

    然后修正了SQL语句写法,加入appid =100032条件,再执行explain 分析,整个sql已经使用到了索引,并且ref = const.

    扫描了145行就将整个结果集进行返回。相较于之前的sql,全表扫描,数据量大大减少。

    从下图可以看出,SQL语句并不是条件越多越好,越多的条件,可能反而使查询语句得不到正确的索引优化效果。

    合理使用条件,尽可能高效精准使用到索引。

     

    最后,之前的select * 这样的写法实在是不高明的举动,每条记录全部取出,造成资源浪费。所以需要优化成,只取自己需要的字段。减少数据流量,减少网络带宽,对数据加载性能有不错的提高。

    二:再分析前端页面的组成部分,原本前端是将版本和渠道双层遍历渲染,会出现55*10 = 550 层次级别的循环,将这些数据一次性渲染到模板文件中,整个网页页面代码行数出现15000多行,整个数据返回结果集有1.2M大小。通过改进,前端传递什么版本的信息,就取哪个版本的结果集,给到前端,将结果集分批次渲染到前端页面。极大的加快了渲染速度,整个数据包,只有114k 大小,代码行数只有900多行。最终测试的结果,页面加载速度只用了300多ms左右,快了接近十倍左右的速度

     

     

     

    展开全文
  • 比如我要查一张菜单表,想查询名字为‘product’和子菜单名字为‘product’的菜单,下面是我写的语句。 请问这种怎么用join才代替in,或者有没有别的办法让效率变得高点 select * FROM menu As m where m.name = '...
  • mysql in语句优化

    千次阅读 2018-05-05 17:34:52
    mysql in语句优化 mysql会对sql语句优化, in 后面的条件不超过一定数量仍然会使用索引。 mysql 会根据索引长度和in后面条件数量判断是否使用索引。 另外,如果是in后面是子查询,则不会使用索引。 一个...
  • MySQL中特别实用的几SQL语句送给大家

    万次阅读 多人点赞 2020-06-11 17:23:49
    在写SQL时,经常灵活运用一些SQL语句编写的技巧,可以大大简化程序逻辑。...高能预警,这是一篇干货满满的MySQL技术文章,总有一天,你必然会用到,记得收藏! -- 来自一位被技术经理毒打多年的程序员的忠.
  • Mysql常用30SQL查询语句优化方法

    万次阅读 多人点赞 2016-12-05 15:32:37
    2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id...
  • MySQL语句性能优化

    千次阅读 2018-03-18 16:34:27
    怎么定位慢查询---------------------数据库索引的优化、索引原理SQL语句调优数据库读写分离--MyChar---------------------分组 having存储过程、触发器、函数存储过程:写了一块sql语句,类似Java中方法,只需调用...
  • MySQL语句优化利器EXPLAIN

    千次阅读 2019-08-23 15:19:26
    使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。EXPLAIN可以帮助选择更好的索引和写出更优化的查询语句。在MySQL5.6以及以后的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 182,003
精华内容 72,801
关键字:

优化mysql语句10种方法

mysql 订阅