精华内容
下载资源
问答
  • MySQL查询优化

    千次阅读 2019-08-04 17:28:02
    要么可能访问了太多的行,要么可能访问了太多的列,要么可能MySQL查询的时候需要分析大量超过需要的行。 查询优化 一个查询根据不同的场景,一般可以通过下面的方式来进行优化: 使用COUNT * 在使用COUNT统计...
    为什么查询会慢?

    查询慢的原因可能很多,比如网络原因、锁争用等等。但最基本的原因可能是访问的数据太多。要么可能访问了太多的行,要么可能访问了太多的列,要么可能MySQL在查询的时候需要分析大量超过需要的行。

    查询优化

    一个查询根据不同的场景,一般可以通过下面的方式来进行优化:

    • 使用COUNT *

    在使用COUNT统计行数时,COUNT(*)是性能最高的。

    • 使用LIMIT

    有时候查询并不需要返回所有的结果集,这时候可以加LIMIT限制返回多少。这在分页的场景下很常见。

    MySQL在进行UNION操作的时候,如果把LIMIT写在外查询里,会先将两个子查询的所有数据放在临时表,然后取前xx行。这样性能其实是比较低的,更推荐的做法是把LIMIT同时也到子查询里。

    有时候可以使用LIMIT 1来代替MINMAX函数。因为这两个函数需要全表扫描。

    • 不要查询所有列

    有时候为了方便,会直接写SELECT *来查询所有列的数据。但可能使用的时候不一定要使用到所有列。这个时候可以只查询部分需要用到的列。

    但有时候可能也有例外,因为这样做确实可以简化开发,而且有时候应用程序里面可能会有缓存机制,这样多个查询可能可以复用这个缓存。

    • 经常查询的列建立索引

    在前面的文章《MySQL索引原理》《MySQL索引使用策略和优化》中,我们介绍了MySQL的索引。在经常查询的列上面建立索引,可以让MySQL尽可能地扫描少的行。

    但这里也需要注意,维护索引是有代价的,所以需要自己去权衡时候使用索引。还有一点就是MySQL的索引是基于“最左匹配”原则的。详情可以参考之前的文章。

    • 分解复杂查询

    有时候一个复杂查询可能会“很大”,需要扫描许多行,关联很多表。这个时候可以把它分解成很多个小的查询。这样做有很多好处:

    1.让缓存的效率更高
    2.执行单个查询可以减少锁的竞争
    3.更容易对数据库进行拆分
    4.减少冗余记录的查询:有时候关联查询可能会重复查询同样的数据行。
    

    当然,分解后也会带来弊端,就是发生的查询数量多了,MySQL的连接数就多了,可能会带来网络上的一些开销。但MySQL一般是放在内网的,网络一般会很快,所以不会有太大的影响。

    • 反范式设计数据表

    在设计数据表的时候,为了性能上的优化,可以反范式地设计表。这样可以减小表的关联,加快查询的速度。

    • 使用IN

    MySQL在查询的时候使用IN()时,会先将IN列表中的数据进行排序,然后通过二分查找的方式来确定列表中的值时候满足条件,这是一个O(log n)复杂度的操作,而如果是OR的话,复杂度是O(n),所以如果对IN列表中有大量取值的时候,使用IN会更快一些。

    但尽量不要使用IN加子查询。因为MySQL会将相关的外层表压到子查询中,效率很低。如果是需要用到IN加子查询的时候,建议使用EXISTS()等效查询来做。

    • 不使用OFFSET

    OFFSET在分页的时候很常见。一旦页码过大,OFFSET和导致MySQL扫描大量不需要的行然后丢掉。那如何解决这个问题呢?有两种方式。

    第一种方式是从前端就限制了用户不能直接访问太大的页码。比如百度搜索结果下面的分页,限制了用户很难访问到很大的页码。

    第二种方式是使用主键来辅助。比如上一页返回的id的1000,那下一页就可以直接WHERE查找id大于1000且小于1020。这样查的时候就可以不用OFFSET了,MySQL也只需要扫描很少的行。但这样有一个弊端,就是有些数据可能被删掉了,那每页的范围和数量就不太好控制。

    总结

    总的来说,主要是针对MySQL本身的查询流程和引擎实现来做优化。但实际我们开发程序的时候,在应用层可以做缓存来优化性能,这样可以大大减少MySQL的请求和查询次数。

    另一方面,MySQL的索引非常重要,理解索引的原理,创建合适的索引可以极大地提升查询的性能。使用索引的主要好处就是可以在查询的时候扫描更少的行。

    展开全文
  • 描述 MySQL 查询优化器的工作原理。 MySQL 查询优化器主要为执行的查询决断最有效的路线
  • MySQL查询优化之一-WHERE语句优化

    千次阅读 2018-01-18 13:51:34
    MySQL查询优化之一-WHERE语句优化 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论WHERE语句的优化。 这些示例使用SELECT...

    MySQL查询优化之一-WHERE语句优化


    如需转载请标明出处:http://blog.csdn.net/itas109
    QQ技术交流群:129518033

    环境:
    MySQL版本:5.5.15
    操作系统:windows

    本文讨论WHERE语句的优化。 这些示例使用SELECT语句,但是相同的优化适用于DELETE和UPDATE语句中的WHERE语句。

    查询速度比较快的一些示例:

    SELECT COUNT(*) FROM tbl_name;
    
    SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
    
    SELECT MAX(key_part2) FROM tbl_name
      WHERE key_part1=constant;
    
    SELECT ... FROM tbl_name
      ORDER BY key_part1,key_part2,... LIMIT 10;
    
    SELECT ... FROM tbl_name
      ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

    如果索引列是数字型的,MySQL仅使用二级索引解决以下查询:

    SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
    
    SELECT COUNT(*) FROM tbl_name
      WHERE key_part1=val1 AND key_part2=val2;
    
    SELECT key_part2 FROM tbl_name GROUP BY key_part1;

    以下查询使用索引数据按排序顺序检索行,而无需单独的排序传递:

    SELECT ... FROM tbl_name
      ORDER BY key_part1,key_part2,... ;
    
    SELECT ... FROM tbl_name
      ORDER BY key_part1 DESC, key_part2 DESC, ... ;

    您可能会试图重写您的查询来加快算术运算,同时牺牲可读性。 因为MySQL会自动进行类似的优化,所以通常可以避免这种工作,并将查询留在更易于理解和维护的形式。 MySQL执行的一些优化如下:

    1.删除不必要的括号

    Removal of unnecessary parentheses

    减少语法分许的or和and树层,减少cpu消耗

       ((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)

    2.常量传递

    Constant folding

    尽量不使用变量

       (a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5

    3.消除无用的SQL条件

    Constant condition removal (needed because of constant folding)

       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
    -> B=5 OR B=6

    4.被索引使用的常量表达式只被计算一次

    Constant expressions used by indexes are evaluated only once

    5.COUNT(*)优化

    COUNT(*)在没有WHERE的单个表上时直接从MyISAM和MEMORY表的表信息中检索。 当仅与一个表使用时,这也适用于任何NOT NULL表达式。
    对于诸如InnoDB之类的事务性存储引擎,存储确切的行数是有问题的,因为可能正在发生多个事务,每个事务都可能影响计数。

    6.尽早检测无效的常量表达式。

    MySQL快速检测到一些SELECT语句是不可能的,并且不返回任何行。

    Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.

    7.尽量将WHERE、HAVING合并

    如果不使用GROUP BY或集合函数(COUNT(),MIN()等),尽量将HAVING与WHERE合并。

    HAVING is merged with WHERE if you do not use GROUP BY or aggregate functions (COUNT(), MIN(), and so on).

    select * from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age<25;
    -> select*from t1,tab as t2 where t1.age>10 and t2.age<25 and t2.id>10;

    具体步骤:

    1) from与from合并,修改相应参数
    2) where与where合并,用and连接
    3) 修改相应的谓词(in改=)

    8.对于join中的每个表,构造一个更简单的WHERE,以获得表的快速WHERE评估,并尽快跳过行。

    For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.

    9.在查询中的任何其他表之前首先读取所有常量表。

    常数表是以下任一项:
    1) 一张空表或者仅有一行数据的表
    2)与主键或唯一索引中的WHERE子句一起使用的表,其中所有索引部分都与常量表达式进行比较,并被定义为NOT NULL。
    A table that is used with a WHERE clause on a PRIMARY KEY or a UNIQUE index, where all index parts are compared to constant expressions and are defined as NOT NULL.

    以下所有表格均用作常量表格:

    SELECT * FROM t WHERE primary_key=1;
    SELECT * FROM t1,t2
      WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

    10.优化连接组合

    尽可能找到join表的最佳连接组合。 如果ORDER BY和GROUP BY子句中的所有列都来自同一个表,则在加入时首先选择该表。

    11.ORDER BY、GROUP BY与临时表

    如果存在ORDER BY子句和不同的GROUP BY子句,或者ORDER BY或GROUP BY包含来自join队列中第一个表以外的表的列,则会创建一个临时表。

    12.SQL_SMALL_RESULT修饰符

    如果使用SQL_SMALL_RESULT修饰符,则MySQL使用内存中的临时表

    13.使用最佳索引

    查询每个表的索引,并使用最佳索引,除非优化器认为使用表扫描更高效。 有一次,根据最佳索引是否超过了表格的30%来使用扫描,但是固定百分比不再决定使用索引或扫描之间的选择。 优化器现在更加复杂,并且根据附加因素(如表大小,行数和I/O块大小)进行估计。

    Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

    14.索引数字列

    在某些情况下,MySQL甚至可以从索引中读取行,而无需询问数据文件。 如果索引中使用的所有列都是数字,则仅使用索引树来解析查询。

    In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.

    15.在输出每行之前,跳过与HAVING子句不匹配的行

    16.优化选择条件的排列顺序

    把能过滤更多数据的条件放在前面,过滤少的条件放后面

    MySQL左到右的顺序处理条件,把能过滤更多数据的条件放在前面,过滤少的条件放后面

     select * from employee
    where salary >1000     --条件1,过滤的数据较少
    and   dept_id='01'    --条件2,过滤的数据比条件1多

    上面的SQL就不符合我们的原则了,应该把过滤数据更多的条件放在前面,因此改为下面这样更好

    select * from employee
    where   dept_id='01'     --过滤更多数据的条件放在前面
    and   salary > 1000

    Reference:
    https://dev.mysql.com/doc/refman/5.5/en/where-optimization.html


    觉得文章对你有帮助,可以用微信扫描二维码捐赠给博主,谢谢!
    微信
    如需转载请标明出处:http://blog.csdn.net/itas109
    QQ技术交流群:129518033

    展开全文
  • MySQL查询优化查询优化

    千次阅读 2009-08-20 16:45:00
    MySQL查询优化查询优化器phpma.com  当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看...

    MySQL查询优化之查询优化器phpma.com

      当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看MySQL参考手册。phpma.com

      当然,MySQL查询优化器也利用了索引,但是它也使用了其它一些信息。例如,如果你提交如下所示的查询,那么无论数据表有多大,MySQL执行它的速度都会非常快:

    SELECT * FROM tbl_name WHERE 0; phpma.com


      在这个例子中,MySQL查看WHERE子句,认识到没有符合查询条件的数据行,因此根本就不考虑搜索数据表。你可以通过提供一个 EXPLAIN语句看到这种情况,这个语句让MySQL显示自己执行的但实际上没有真正地执行的SELECT查询的一些信息。如果要使用EXPLAIN, 只需要在EXPLAIN单词放在SELECT语句的前面:

    mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0/G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: NULL
    type: NULL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: NULL
    Extra: Impossible WHERE


      通常情况下,EXPLAIN返回的信息比上面的信息要多一些,还包括用于扫描数据表的索引、使用的联结类型、每张数据表中估计需要检查的数据行数量等非空(NULL)信息。

      优化器是如何工作的 phpma.com

       MySQL查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交 SELECT语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够 首先进行最严格的测试,查询就可以执行地更快。假设你的查询检验了两个数据列,每个列上都有索引:

    SELECT col3 FROM mytable
    WHERE col1 = ’some value’ AND col2 = ’some other value’;


      假设col1上的测试匹配了900个数据行,col2上的测试匹配了300个数据行,而同时进行的测试只得到了30个数据行。先测试 Col1会有900个数据行,需要检查它们找到其中的30个与col2中的值匹配记录,其中就有870次是失败了。先测试col2会有300个数据行,需 要检查它们找到其中的30个与col1中的值匹配的记录,只有270次是失败的,因此需要的计算和磁盘I/O更少。其结果是,优化器会先测试col2,因 为这样做开销更小。phpma.com

      你可以通过下面一个指导帮助优化器更好地利用索引:phpma.com

       尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候,请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例 如,INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10),但是与CHAR(12)或 VARCHAR(12)不同。如果你所比较的数据列的类型不同,那么可以使用ALTER TABLE来修改其中一个,使它们的类型相匹配。phpma.com

      尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列,MySQL就不会使用索引,因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免,但是很多情况下你可以重新编写一个查询让索引列独立地出现。phpma.com

      下面的WHERE子句显示了这种情况。它们的功能相同,但是对于优化目标来说就有很大差异了:

    WHERE mycol < 4 / 2
    WHERE mycol * 2 < 4


      对于第一行,优化器把表达式4/2简化为2,接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式,MySQL必须检 索出每个数据行的mycol值,乘以2,接着把结果与4进行比较。在这种情况下,不会使用索引。数据列中的每个值都必须被检索到,这样才能计算出比较表达 式左边的值。phpma.com

      我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询,就不会使用这个索引:phpma.com

    SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;


      这个表达式不会把1990与索引列进行比较;它会把1990与该数据列计算出来的值比较,而每个数据行都必须计算出这个值。其结果是, 没有使用date_col上的索引,因为执行这样的查询需要全表扫描。怎么解决这个问题呢?只需要使用文本日期,接着就可以使用date_col上的索引 来查找列中匹配的值了:

    WHERE date_col < ’1990-01-01’phpma.com


      但是,假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种:

    WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
    WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
    WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)


      对于第一行,不会用到索引,因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff 和TO_DAYS(CURDATE())都是常量,因此在处理查询之前,比较表达式的右边可以被优化器一次性计算出来,而不需要每个数据行都计算一次。但 是date_col列仍然出现在函数调用中,它阻止了索引的使用。第三行是这几个中最好的。同样,在执行查询之前,比较表达式的右边可以作为常量一次性计 算出来,但是现在它的值是一个日期。这个值可以直接与date_col值进行比较,再也不需要转换成天数了。在这种情况下,会使用索引。

      在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句:phpma.com

    WHERE col_name LIKE ’%string%’


      如果你希望找到那些出现在数据列的任何位置的字符串,这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两边。如果你在 查找出现在数据列开头的字符串,就删掉前面的"%"。假设你要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情 况下,WHERE子句如下所示:

    WHERE last_name LIKE ’Mac%’phpma.com


      优化器查看该模式中词首的文本,并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式:phpma.com

    WHERE last_name >= ’Mac’ AND last_name < ’Mad’


      这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。

      帮助优化器更好的判断索引的效率。在默认情况下,当你把索引列的值与常量进行比较的时候,优化器会假设键值在索引内部是均匀分布的。在决定进行 常量比较是否使用索引的时候,优化器会快速地检查索引,估计出会用到多少个实体(entry)。对应MyISAM、InnoDB和BDB数据表来说,你可 以使用ANALYZE TABLE让服务器执行对键值的分析。它会为优化器提供更好的信息。phpma.com

      使用EXPLAIN验证优化器的操作。EXPLAIN语句可以告诉你是否使用了索引。当你试图用另外的方式编写语句或检查添加索引是否会提高查询执行效率的时候,这些信息对你是有帮助的。phpma.com

       在必要的时候给优化器一些提示。正常情况下,MySQL优化器自由地决定扫描数据表的次序来最快地检索数据行。在有些场合中优化器没有作出最佳选择。如 果你察觉这种现象发生了,就可以使用STRAIGHT_JOIN关键字来重载优化器的选择。带有STRAIGHT_JOIN的联结类似于交叉联结,但是强 迫数据表按照FROM子句中指定的次序来联结。phpma.com

      在SELECT语句中有两个地方可以指定STRAIGHT_JOIN。你可以在SELECT关键字和选择列表之间的位置指定,这样会对语句中所有的交叉联结产生影响;你也可以在FROM子句中指定。下面的两个语句功能相同:phpma.com

    SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
    SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;


      分别在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的情况下运行这个查询;MySQL可能因为什么原因没有按照你认为最好的次序使用索引(你可以使用EXPLAIN来检查MySQL处理每个语句的执行计划)。phpma.com

      你还可以使用FORCE INDEX、USE INDEX或IGNORE INDEX来指导服务器如何使用索引。

       利用优化器更加完善的区域。MySQL可以执行联结和子查询,但是子查询是最近才支持的,是在MySQL 4.1中添加的。因而在很多情况下,优化器对联结操作的调整比对子查询的调整要好一些。当你的子查询执行地很慢的时候,这就是一条实际的提示。有一些子查 询可以使用逻辑上相等的联结来重新表达。在可行的情况下,你可以把子查询重新改写为联结,看是否执行地快一些。phpma.com

       测试查询的备用形式,多次运行。当你测试查询的备用形式的时候(例如,子查询与等同的联结操作对比),每种方式都应该多次运行。如果两种形式都只运行了 一次,那么你通常会发现第二个查询比第一个快,这是因为第一个查询得到的信息仍然保留在缓存中,以至于第二个查询没有真正地从磁盘上读取数据。你还应该在 系统负载相对平稳的时候运行查询,以避免系统中其它的事务影响结果。

          避免过度地使用MySQL自动类型转换。MySQL会执行自动的类型转换,但是如果你能够避免这种转换操作,你得到的性能就更好了。例如,如果num_col是整型数据列,那么下面这些查询将返回相同的结果:

    SELECT * FROM mytbl WHERE num_col = 4;
    SELECT * FROM mytbl WHERE num_col = ’4’;


      但是第二个查询涉及到了类型转换。转换操作本身为了把整型和字符串型转换为双精度型进行比较,使性能恶化了。更严重的情况是,如果num_col是索引的,那么涉及到类型转换的比较操作不会使用索引。phpma.com

      相反类型的比较操作(把字符串列与数值比较)也会阻止索引的使用。假设你编写了如下所示的查询:

    SELECT * FROM mytbl WHERE str_col = 4;


      在这个例子中,不会使用str_col上的索引,因为在把str_col中的字符串值转换成数值的时候,可能有很多值等于4(例如’4’、’4.0’和’4th’)。分辨哪些值符合要求的唯一办法是读取每个数据行并执行比较操作。 phpma.com

    展开全文
  • MySQL查询优化器工作原理解析

    万次阅读 2016-05-28 21:06:31
    手册上MYSQL查询优化器概述;个人对MySQL优化器的理解;分析优化器优化过程中的信息;调节MySQL优化器的优化等

    手册上查询优化器概述

    查询优化器的任务是发现执行SQL查询的最佳方案。大多数查询优化器,包括MySQL的查询优化器,总或多或少地在所有可能的查询评估方案中搜索最佳方案。对于联接查询,MySQL优化器所调查的可能的方案数随查询中所引用的表的数目呈指数增长。对于小数量的表(典型小于7-10),这不是一个问题。然而,当提交的查询更大时,查询优化所花的时间会很容易地成为服务器性能的主要瓶颈。
    查询优化的一个更加灵活的方法是允许用户控制优化器详尽地搜索最佳查询评估方案。一般思想是优化器调查的方案越少,它编译一个查询所花费的时间越少。另一方面,因为优化器跳过了一些方案,它可能错过一个最佳方案。
    优化器关于方案数量评估的行为可以通过两个系统变量来控制:

    • optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些方案。我们的试验显示该类“有根据的猜测”很少错过最佳方案,并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为on(optimizer_prune_level=1)。然而,如果你认为优化器错过了一个更好的查询方案,则该选项可以关闭(optimizer_prune_level=0),风险是查询编辑花费的时间更长。请注意即使使用该启发,优化器仍然可以探测呈指数数目的方案。

    • ptimizer_search_depth变量告诉优化器对于每个未完成的“未来的”方案,应查看多深,以评估是否应对它进一步扩大。optimizer_search_depth值较小会使查询编辑次数大大减小。例如,如果optimizer_search_depth接近于查询中表的数量,对12、13或更多表的查询很可能需要几小时甚至几天的时间来编译。同时,如果用optimizer_search_depth等于3或4编辑,对于同一个查询,编译器编译时间可以少于1分钟。如果不能确定合理的optimizer_search_depth值,该变量可以设置为0,告诉优化器自动确定该值。
      我们可以通过show variables 来查看这些参数
      这里写图片描述
      备注(手册网址:http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter

    个人理解

    从官方手册上看,可以理解为,MySQL采用了基于开销的优化器,以确定处理查询的最解方式,也就是说执行查询之前,都会先选择一条自以为最优的方案,然后执行这个方案来获取结果。在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,或者是提供太多的相关数据信息,估测就不那么友好了。
    但是感觉手册上,并没有说MySQL怎么去寻找最优方案呢?
    通过查询相应的资料,个人理解如下
    MySQL优化器中,一个主要的目标是只要可能就是用索引,而且使用条件最严格的索引来尽可能多、尽可能快地排除那些不符合索引条件的数据行,说白了就是选择怎样使用索引,当然优化器还受其他的影响。为了更直观,下面将通过例子来说明。
    创建一个表:

    CREATE TABLE t8(
    id1 INT NOT NULL ,
    id2 INT NOT NULL,
    KEY id1_key(`id1`),
    KEY id2_key(`id2`)
    ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
    

    插入几行数据如下:
    这里写图片描述
    当我执行如下查询语句时候,查询优化器会怎样进行优化呢?

    select * from t8 where id1=1 and id2=0;

    当然,MySQL不会傻到,从t8表中的一行开始,然后一行行的去比较,id1与id2。优化器会先分析数据表,得知有索引id1_key与id2_key,如果先判断id1_key的话,然后需要从4行数据中排除3行数据;如果先判断id2_key的话,然后需要从2行中排除1行。对人来说,这两种方式没有什么区别,但是对于程序而言,先判断id2_key需要较少的计算和磁盘输入输出。因此,查询优化器会规定程序,先去检验id2_key索引,然后在从中挑出id2为0的数据行。
    通过下图,我们可以看出,可以选择的索引有id1_key与id2_key,但是实际用到的索引只有id2_key
    这里写图片描述
    如果将SQL语句改为 select * from t8 where id1=1 and id2=0;执行情况也是一样的,不区分前后。如下图:
    这里写图片描述

    当然,如果将程序,修改为如下

    select * from t8 where id1=5 and id2=0;

    也可以分析得出,会使用id1_key索引
    这里写图片描述

    当然,如果在创建一个复合索引

    ALTER TABLE t8 ADD KEY id1_id2_key(`id1`,`id2`)

    此时,在此执行select * from t8 where id1=1 and id2=0; 当然会考虑使用id1_id2_key索引。
    这里写图片描述
    通过上面的例子,可以理解查询优化器在查询的时候,是选择哪一个索引作为最合适的索引。除此,也提示我们,要慎重选择创建索引。如,上面创建了三个索引(id1_key、id1_key、id1_id2_key),但是优化器优化程序时候,每次只能从中选择一个最合适的,如果创建过多,不仅仅是给数据的更新和插入带来了压力,同时也增加了优化器的压力。

    分析优化器优化过程中的信息

    其实,在上面已经查看过优化器优化过程中的信息,无非就是使用explain。在这里,在集中说说,里面的参数意义。如下图
    这里写图片描述
    id: MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
    select_type:查询类型,SIMPLE、PRIMARY、UNION、DEPENDENT UNION等。
    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如何解析查询的额外信息。

    调节MySQL优化器的优化

    影响索引的选择

    当我们在执行select * from t8 where id1=1 and id2=0; 语句的时候,优化器会id1_id2_key索引,但我们可以通过IGNORE INDEX、 IGNORE INDEX来影响索引的选择

    强制索引

    通过FORCE INDEX(索引1[,索引2])或者使用USE INDEX(索引1[,索引2]),来指定使用哪个索引,也可以指定多个索引,让优化器从中挑选。
    这里写图片描述

    这里写图片描述

    忽略索引

    可以使用IGNORE INDEX(索引1[,索引2])来忽略一些索引,这样优化器,就不会考虑使用这些所有,减少优化器优化时间。
    这里写图片描述

    影响优化器使用数据表的顺序

    一般情况下,MySQL优化器会自行决定按照哪种顺序扫描数据表才能最快地检索出数据,但是我们可以通过STRAGHT_JOIN强制优化器按特定的顺序使用数据表,毕竟优化器做的判断不一定都是最优的。使用原则是,让限制最强的选取操作最先执行。STRAIGHT_JOIN可以放在SELECT后面,也可以放在FROM子句中。
    如下图
    这里写图片描述

    这里写图片描述
    可以看出,无论from t8,t6还是from t6,t8,都是先检索t6中的表。但是使用STRAIGHT_JOIN的话,就会按照SQL中顺序。
    这里写图片描述
    为什么优化器要选择先判断t6中的数据呢?一个主要的原因,因为t6中数据更少。
    这里写图片描述
    如果将t8中数据删除几行后,很明显MySQL优化器选择顺序数据表的顺序就会发生变化。
    这里写图片描述

    控制SQL语句的优先权

    在高并发的网站中,因为MySQL默认的是写优先,有可能导致一些读操作有效时间内得不到执行机会,HIGH_PRIORITY可以使用在selectinsert操作中,让MYSQL知道,这个操作优先进行。
    这里写图片描述
    LOW_PRIORITY可以使用在insertupdate操作中,让mysql知道,这个操作将优先权将降低。
    这里写图片描述
    INSERT DELAYED告诉MySQL,这个操作将会延时插入。
    INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多,因为它较少了I/O操作。坏处是,不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。
    这里写图片描述

    控制查询缓冲

    在实际开发中,一些数据对实时性要求特别高,或者并不经常使用(可能几天就执行一次或两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找该数据,每次都会从磁盘中读取。因为如果实时性要求特别高,缓存中数据可能和磁盘中的就不同步,如果数据不经常使用,被缓存起来,就会占用内存。
    在my.ini中的query_cache_type,使用来控制表缓存的。这个变量有三个取值:0,1,2,分别代表了off、on、demand。
    0:表示query cache 是关闭。
    1:表示查询总是先到查询缓存中查找,即使使用了sql_no_cache仍然查询缓存,因为sql_no_cache只是不缓存查询结果,而不是不使用查询结果。
    2:表示只有在使用了SQL_CACHE后,才先从缓冲中查询数据,仍然将查询结果缓存起来。
    我本地缓存是关闭的,,如下图。
    这里写图片描述
    关于MySQL缓存可以参考这里
    http://blog.csdn.net/hsd2012/article/details/51526707

    展开全文
  • MySQL查询优化之二-范围优化(Range Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论范围的优化...
  • MySQL查询优化之三-索引合并优化(Index Merge Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论...
  • MySql查询优化,高并发控制

    千次阅读 2016-07-23 16:15:50
    MySql查询优化以及高并发控制
  • 针对 MySQL查询优化,非常给力!针对 MySQL查询优化,非常给力!针对 MySQL查询优化,非常给力!针对 MySQL查询优化,非常给力!针对 MySQL查询优化,非常给力!针对 MySQL查询优化,非常给力!
  • MySQL查询优化之十-ORDER BY优化(ORDER BY Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论ORDER BY优化...
  • MySQL查询优化之九-IS NULL优化(IS NULL Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论IS NULL优化(IS ...
  • mysql查询优化

    千次阅读 2019-03-15 00:55:44
    优化器分类 传统关系型数据库里面的优化器分为CBO和RBO两种。 RBO— Rule_Based Potimizer 基于规则的优化器: RBO :RBO所用的判断规则是一组内置的规则,这些规则是硬编码在数据库的编码中的,RBO会根据这些规则...
  • MySQL调优(五):MySQL查询优化分析

    万次阅读 2020-06-27 01:38:53
    思考:下面这个limit怎么通过子查询进行优化? 避免: 1、避免查询不需要的记录 我们常常会误以为mysql会只返回需要的数据,实际上mysql却是先返回全部结果再进行计算,在日常的开发习惯中,经常是先用select语句查
  • MySQL查询优化之五-嵌套循环连接算法(Nested-Loop Join Algorithms) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论嵌套...
  • MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它...
  • mysql查询优化器提示( hint )

    千次阅读 2019-05-28 17:17:13
    查询优化器提示( hint ) : 一般指改变 mysql 优化器的执行计划, 除非业务需要, 不建议这样做。 查询优化器提示( hint ) 1. HIGH_PRIORITY 、 LOW_PRIORITY HIGH_PRIORITY: 提示mysql该语句优先执行 LOW_...
  • mysql 查询优化 索引 type级别

    千次阅读 2020-08-10 11:09:57
    mysql查询速度优化 这是type级别 越左边 查询速度越快 system>const>eq_ref>ref>range>index>ALL 怎么样才能让type靠左呢 就是建立索引 最左边的俩级 基本达不到 我们让查询type达到第三级和第四...
  • MySQL查询优化:GROUP BY

    万次阅读 多人点赞 2019-03-05 19:46:16
    group by 优化方法 — 索引 松散索引扫描(Loose Index Scan) 为什么松散索引扫描的效率会很高? 紧凑索引扫描(Tight Index Scan) group by 优化方法 — 直接排序 二、group by 与 distinct 三、排序不一致...
  • 一、mysql查询类型(默认查询出所有数据列) 1、内连接   默认多表关联查询方式,查询出两个表中所有字段;可省略inner join 关键字 2、外连接 查询出某一张表中的所有数据 (1)左连接  查询出第一张表的...
  • hash类型的索引:查询单条快,范围查询慢 btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它) -- 不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务,支持行级别锁定,...
  • 最近,发现业务线程卡死在读取数据库(MySQL 5.7),数据库CPU飙升到了100%,经过定位发现是下面这样一个SQL(由用户触发的查询,可以理解为查看这个群组的所有用户买过哪些商品)导致的: select * from ...
  • Mysql查询优化器浅析(上)

    千次阅读 2007-12-13 11:58:00
    Mysql查询优化器浅析(上)译者:杨万富 1 定义 Mysql查询优化器的工作是为查询语句选择合适的执行路径。查询优化器的代码一般是经常变动的,这和存储引擎不太一样。因此,需要理解最新版本的查询优化器是如何组织...
  • 因为这一慢查询的执行,可能引起其他sql的效率低下,有可能引起链式反应像核爆炸那样耗尽服务器资源。 我们尽量要在生产环境避免这类语句执行。如果一定要,那么在满足一定条件下可以使用下面的小技巧来优化。 第一...
  • mysql查询优化以及索引使用

    千次阅读 2016-03-05 11:16:05
    如果应用程序使用了mysql,其中包含一些查询速度慢的sql,我们要去优化它们,优化的思路需要如何进行呢?主要是以下两点: 1、应用程序是否在检索大量超过需要的数据(行、列); 2、mysql服务器层是否在分析...
  • [玩转MySQL之六]MySQL查询优化

    千次阅读 2018-12-21 14:55:27
    注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<...MySQL查询语句在数据库中的执行分为5个阶段,具体如下: 1.1 SQL输入 数据库接收用户输入的SQL语句,准备执行。 1.2...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 389,885
精华内容 155,954
关键字:

mysql查询优化

mysql 订阅