mysql索引优化怎么学

来源：蜘蛛抓取(WebSpider) 时间：2018-09-29 05:33 标签： mysql索引优化

Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是我经常看到很多性能较差的sql没有利用这个优化规律，下面将结合一些实际的案例来分析说明：

一条sql执行非常的慢，执行时间为：

在注意到sql中满足过滤条件end_time>now()的有113549行，在加上剩余的条件中含有order by，这样会造成排序的结果集非常的大，执行非常的耗费资源；于是分析sql，在sql中包括了order by desc limit这样的排序条件后，新增适当的索引满足排序的条件，同时由于有limit的限制结果集，当扫描到满足条件的行数后退出查询，那么我们来看看优化效果：

再次执行sql，观察其执行时间：

可以看到执行时间已经降到了毫秒以下,查看其执行计划：

可以看到优化器已经选择了ind_gmt_create索引扫描，这样的话就避免了对结果集进行排序的过程，同时优化器预估扫描14行数据就会得到满足查询条件的数据（END_TIME > now()），执行计划非常的理想。 


  

  

    

  

  

    我们从执行计划上分析来看，表的连接顺序为：b―>r_a―>a―>k，可以看到执行计划的第一行中需要扫描49212行的数据,同时由于status采用的是in的方式，instance_no即使在索引中也用不上，这样就导致了排序使用到了临时表,这也是导致sql执行慢的原因。我们看到sql中的最后一个排序为order by b.instance_no asc limit
    37300,50，这里我们好像可以看到优化的曙光，调整数据库的索引以满足B表的排序需求：

  

  

    

  

  

    调整索引后查看执行计划：

  

  

    

  

  

    我们加上force index强制走我们新加的索引：

  

  

    

  

  

    可以看到在加上提示符后，使用到了我们新加的索引，扫描的行数为54580行，执行时间：

  

  

    

  

  

    

  

  

    Order by desc/asc limit的优化技术有时候在你无法建立很好索引的时候，往往会得到意想不到的优化效果，但有时候有一定的局限性，优化器可能不会按照你既定的索引路径扫描，优化器需要考虑到查询列的过滤性以及limit的长度，当查询列的选择性非常高的时候，使用sort的成本是不高的，当查询列的选择性很低的时候，那么使用order by +limit的技术是很有效的。
  



                            

                                                    

                                

  

           通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还 是以查询操作为主，以及各种类型的 SQL大致的执行比例是多少。对于更新操作的计 数，是对执行次数的计数，不论提交还是回滚都会累加。

          对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回 滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于我们了解数据库的基本情况：
  

  

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

  

    MySQL 在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看 SQL 执行情况， 同时对一些锁表操作进行优化。
  

  

    

             索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT 操作性能的最佳途径。

             查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列 索引，那么只有查询条件使用了多列关键字最左边的前缀时（前缀索引），才可以使用索引，否则 将不能使用索引。
    

    

            3、使用or分割的条件，如果or前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用。

            4、如果创建复合索引，如果条件中使用的列不是索引列的第一部分；（不是前缀索引）

            5、对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转 为字符串，但是不使用索引。
    

    

      2. 查看索引使用情况
    

    

              如果索引正在工作， Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使 用。

      Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，

      该值较高。通常说明表索引不正确或写入的查询没有利用索引。
    

  

  

    1. 查询进行优化，应尽量避免全表扫描
  

  

        对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
  

  

            NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默  认值。
  

  

            不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列    就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not
    null的语句优化器是不允许使用索引的。
  

  

      5).下面的查询也将导致全表扫描：
  

  

     7). 如果在 where 子句中使用参数，也会导致全表扫描。
  

  

           因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推 迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
  

  

     8). 应尽量避免在 where 子句中对字段进行表达式操作，
  

  

    9). 应尽量避免在where子句中对字段进行函数操作，
  

  

    10).不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，
  

  

    11). 索引字段不是复合索引的前缀索引
  

  

           例如 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  

  

    2 .其他一些注意优化：
  

  

    12). 不要写一些没有意义的查询，
  

  

    14). 并不是所有索引对查询都有效，
  

  

           SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
  

  

    15). 索引并不是越多越好，
  

  

           索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  

  

           因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
  

  

    17).尽量使用数字型字段，
  

  

          若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  

  

          因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  

  

         用具体的字段列表代替“*”，不要返回用不到的任何字段。
  

  

    20). 尽量使用表变量来代替临时表。
  

  

        如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。 
  

  

    21).避免频繁创建和删除临时表，以减少系统表资源的消耗。
  

  

    22).临时表并不是不可使用，
  

  

         适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
  

  

    23).在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；
  

  

    24). 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
  

  

    25).尽量避免使用游标，
  

  

          因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 
  

  

    26).使用基于游标的方法或临时表方法之前，
  

  

          应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
  

  

    27).与临时表一样，游标并不是不可使用。
  

  

         对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
  

  

    29).尽量避免大事务操作，提高系统并发能力。
  

  

    30).尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
  

  

    

      　　这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和 count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。
    

    

      　　count(*) 是表示整个结果集有多少条记录
    

    

      1)innodb引擎在统计方面和myisam是不同的，Myisam内置了一个计数器，
    

    

      2. 但是当有查询条件的时候，两者的查询效率一致。
    

    

      [1]     数据文件和索引文件存储在一个文件中，主键索引默认直接指向数据存储位置。
    

    

      [2]     二级索引存储指定字段的索引，实际的指向位置是主键索引。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，所以主键索引统计没有二级索引效率高。
    

    

      [3]     由于主键索引直接指向实际数据，所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。
    

  

  

    

           MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录，但由于MySQL在查询时最多只能使用一个索引。因此，如果WHERE条件已经占用了索引，那么在排序中就不使用索引了，这将大大降低查询的速度。我们可以看看如下的SQL语句:     SELECT
       这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意，在使用这个方法时，要确保WHERE子句中没有排序字段，在上例中就是不能用SALE_DATE进行查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询又会慢下来。
    

  

  

    where条件和order by使用相同的索引，并且order by 的顺序和索引顺序相 同，并且order by的字段都是升序或者都是降序。例如：下列sql可以使用索引。
  

  

    

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

    

    

    

      显示这一行的数据是关于哪张表的 

    

    

    

      该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

      当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

      对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，

              这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，

    

    

    

         显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中
    


    

    

    

      使用的索引的长度。在不损失精确性的情况下，长度越短越好 
    

    

    

      显示索引的哪一列被使用了，如果可能的话，是一个常数 
    

    

    

      MYSQL认为必须检查的用来返回请求数据的行数 (扫描行的数量)
    

    

    

      一旦MYSQL找到了与行相联合匹配的行，就不再搜索了 
    

    

      Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。
    

    

      Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
    

    

      Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上 
    

    

      Using where使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，
    

  

  

    

      3）用SELECT创建记录和表
    

    

      　　INSERT语句与DELETE语句和UPDATE语句有一点不同，它一次只操作一个记录。然而，有一个方法可以使INSERT 语句一次添加多个记录。要作到这一点，你需要把INSERT语句与SELECT语句结合起来，象这样:
    

    

      　　当为一个表中的记录建立备份时，这种形式的INSERT语句是非常有用的。在删除一个表中的记录之前，你可以先用这种方法把它们拷贝到另一个表中。
    

    

      　　如果你需要拷贝整个表，你可以使用SELECT INTO语句。例如，下面的语句创建了一个名为newtable的新表，该表包含表mytable的所有数据:
    

    

      　　你也可以指定只有特定的字段被用来创建这个新表。要做到这一点，只需在字段列表中指定你想要拷贝的字段。另外，你可以使用WHERE子句来限制拷贝到新表中的记录。下面的例子只拷贝字段second_columnd的值等于'Copy Me!'的记录的first_column字段。
    

    

      　　使用SQL修改已经建立的表是很困难的。例如，如果你向一个表中添加了一个字段，没有容易的办法来去除它。另外，如果你不小心把一个字段的数据类型给错了，你将没有办法改变它。但是，使用本节中讲述的SQL语句，你可以绕过这两个问题。
    

    

      　　例如，假设你想从一个表中删除一个字段。使用SELECT INTO语句，你可以创建该表的一个拷贝，但不包含要删除的字段。这使你既删除了该字段，又保留了不想删除的数据。
    

    

      　　如果你想改变一个字段的数据类型，你可以创建一个包含正确数据类型字段的新表。创建好该表后，你就可以结合使用UPDATE语句和SELECT语句，把原来表中的所有数据拷贝到新表中。通过这种方法，你既可以修改表的结构，又能保存原有的数据。
    

  



                            

                                                    

                                

  

    点击上方“铅笔学园”，关注后置顶
  

  

    你关注的人，决定你看到的世界
  

  

    前些天，有个同事跟我说：“我写了个SQL，SQL很简单，但是查询速度很慢，并且针对查询条件创建了索引，然而索引却不起作用，你帮我看看有没有办法优化？”。
  

  

    我对他提供的case进行了优化，并将优化过程整理了下来。
  

  

    我们先来看看优化前的表结构、数据量、SQL、执行计划、执行时间等。
  

  

    隐藏了部分不相关字段后，可以看到表足够简单， 并且在order_code上创建了唯一性索引uni_order_code。
  

  

    这个数据量还是比较小的，不过如果SQL足够差，一样会查询很慢。
  

  

    哇，SQL足够简单，不过有时候越简单也越难优化。
  


  

    全表扫描、文件排序，注定查询慢！
  

  

    那为什么MySQL没有利用索引（uni_order_code）扫描完成查询呢？因为MySQL认为这个场景利用索引扫描并非最优的结果。我们先来看下执行时间，然后再来分析为什么没有利用索引扫描。
  


  

    的确，执行时间太长了，如果表数据量继续增长下去，性能会越来越差。
  

  

    我们来分析下MySQL为什么使用全表扫描、文件排序，而没有使用索引扫描、利用索引顺序：
  

  

    1. 全表扫描、文件排序：
  

  

    虽然是全表扫描，但是扫描是顺序的（不管机械硬盘还是SSD顺序读写性能都是高的），并且数据量不是特别大，所以这部分消耗的时间应该不是特别大，主要的消耗应该是在排序上。
  

  

    2. 利用索引扫描、利用索引顺序：
  

  

    uni_order_code是二级索引，索引上保存了（order_code,id），每扫描一条索引需要根据索引上的id定位（随机IO）到数据行上读取order_amount，需要1000次随机IO才能完成查询，而机械硬盘随机IO的效率是极低的（机械硬盘每秒寻址几百次）。
  

  

    根据我们自己的分析选择全表扫描相对更优。如果把limit 1000改成limit 10，则执行计划会完全不一样。
  

  

    既然我们已经知道是因为随机IO导致无法利用索引，那么有没有办法消除随机IO呢？
  

  

    我们来看看利用覆盖索引优化后的索引、执行计划、执行时间。
  


  

    执行计划显示查询会利用覆盖索引，并且只扫描了1000行数据，查询的性能应该是非常好的。
  


  

    从执行时间来看，SQL的执行时间提升到原来的1/20，已经达到我们的预期。
  

  

    覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列，还包含查询条件、排序等。
  

  

    要写出性能很好的SQL不仅需要学习SQL，还要能看懂数据库执行计划，了解数据库执行过程、索引的数据结构等。
  

  

    2.MySQL 分库分表方案，总结的非常好！
  

  

    4.彻底解决MySQL中文乱码
  

  

    初级程序员的指明灯|资源共享|知识分享
  




                            

                                                

                    

                

            

            


            

                
我要回帖

                

                    

                        
                        
                    

                

            

            

                        

                
更多关于 mysql索引优化 的文章

                

                    
                

            

                    

        

            

                
 

                


                

            

            

            

                
随机推荐