sql优化_sql优化面试 - CSDN
精华内容
参与话题
  • 常见的SQL优化

    万次阅读 多人点赞 2020-08-21 10:07:42
    SQL优化: 个人理解:主要是对查询的优化。对于sql的优化主要就是下面几个方面的优化, 1.避免全表扫描 2.避免索引失效 3.避免排序,不能避免,尽量选择索引排序 4.避免查询不必要的字段 5.避免临时表的创建,...

    SQL优化:

    个人理解:主要是对查询的优化。对于sql的优化主要就是下面几个方面的优化,

    1.避免全表扫描

    2.避免索引失效

    3.避免排序,不能避免,尽量选择索引排序

    4.避免查询不必要的字段

    5.避免临时表的创建,删除

    6....

    一.插入数据

    1.导入大批量数据

         这个需要先关闭索引,插入数据后再打开索引。

    1.1.针对MyISAM引擎可以通过以下方式提高导入数据效率,但是Innodb并不能提高这个效率。

        命令:alter table user disable keys; //关闭所有索引

                  alter table user enable keys;  // 开启索引

    1.2.对于Innodb类型的表,由于Innodb的表是根据主键的顺序保存的,所以将导入的数据按照主键的顺序,可以提高效率

          1. 2.1在导入数据前关闭唯一性校验也可以提高效率 set unique_checks=0

          1.2.2 关闭自动提交,set auto-commit=0 采用手动提交也可以提高效率

    2.优化insert语句

        2.1如果同时插入多行,采用多个值表更好。例如:

         insert into test values(1,2),(1,3),(2,3);

    二.排序 order by

         目标:尽量减少额外的排序,通过索引直接返回有序数据。

    排序情况有以下两种结果:

       1.通过有序索引顺序扫描直接返回有序数据。这里分析结果Extra:Using index ;例如:

             explain select customer_id from customer order by store_id;  //这里customer_id是主键,store_id是索引

        2. 通过返回数据进行排序,explain 返回的Extra结果是Using fileSort。这个是不好的。例如:

             explain select * from customer order by store_id;//store_id是一个索引

       方案:1>:where和order by使用相同的索引,并且order by 的顺序和索引顺序(如果复合索引)相同

                   2>:order by 字段都是同为升序或者降序,否则索引不生效,使用了Using fileSort

                  3>:当filesort索引无法避免情况下,想办法加快fileSort操作,设计到两次扫描算法和一次扫描算法,看情况使用某一种。

         两次扫描算法:根据条件取出排序字段和行指针信息,之后再排序区排序,如果排序区不够在新建临时表。完成排序后在通过指针回表读取记录。

       一次扫描算法:一次性取出满足条件的行的所有字段。然后再排序区完成排序后,直接输出数据,这个排序的时候内存消耗比较大,但是相比两次效率又高。

    三.分组group by 优化

          默认情况下group by 对字段分组的时候,会排序。这和在查询order by 的情况类似。

            1. 如果在在分组的时候不需要排序,最好关掉排序命令:order by null。例如:

            explain select name sum(money) from user group by name order by null;

    四. 优化嵌套查询

          .某些子查询可以通过join来代替。理由:join不需要在内存中创建一个临时表来存储数据。

            explain select * from customer where customer_id not in (select customer_id from payment) ;

           上面的语句用下面的语句代替

                 explain select * from customer a left join payment b on a.customer_id=b.customer where b.customer_id is null;

      五.优化or条件

          1. 对于单独的两个索引

            explain select  * from sales where id=2 or year =1998;    //id和year都是索引

            这两个索引都是被使用到了的,但是这个查询时分别对两个条件进行查询,然后union两个结果的。

          2. 如果对复合索引(id 和year是复合索引),那么就不能使用到了索引,采用的全文扫描。

     六.优化分页查询

          常见的分页查询,查询到“limit 2000,20”;时候就会出现先查询前面2200个,然后抛弃前面2000个,造成查询和排序代价非常大。优化方式如下:

          1.在索引上完成排序分页的操作。根据主键关联回原表查询所需要的其他内容。例如:

               explain select a.last_name , a.first_name from user a inner join (select id from user order by id limit 2000,20) b on a.id=b.id;

         2.把limit查询转换成某一个位置查询。可以通过把上一页的最后一条记录记下来。

               例子:select * from payment order by rental_id desc limit 2000,20;    //这样效率非常低下

          如上面是通过 rental_id 降序来排列的 ,那么我们在查询 limit 1800,20时候,记录下2000位置的rental_id,加入这里的rental_id的值,假设这里的值是“5000” ,那么sql语句就可以转换成如下:

                            select * from payment where rental_id < 5000 order by rental_id desc limit 10;

               注意事项: 这个只适合在排序字段不会出现重复值的特定环境,能够减轻分页翻页的压力,如果排序字段出现重复值,那么就会出现记录丢失。

      七.使用SQL提示

        常见的SQL提示如下:

      1. use index  这个表示希望sql去参考的索引,就可以让mysql不在考虑其他可用的索引了

           explain select count(*) from user user index(idx_user_id);

      2.ingore index 只是单纯的希望mysql忽略一个索引,或者多个。例如:

         explain select count(*) from rental ignore index(idx_rental_date)

    3.force index 强制mysql使用一个索引

        explain select * from user  use index (idx_fk_inventory_id) where inventory_id >1;

       默认inventory_Id都是大于1的,所以一般会全表扫描,如果强制使用这个所以,那么msyql还是会使用这个索引。

    八.查询的一些注意项

         1.慎用模糊查询,使用 like  两边加“%”--造成索引失效;左边没有%,这个索引不会失效

         2.尽量不要使用select *  ,使用需要的具体字段查询

         3.不要在查询条件where后面对字段做函数处理

         4.优先使用union all ,避免使用union.

             UNION 因为会将各查询子集的记录做比较,故比起UNION ALL ,通常速度都会慢上许多。一般来说,如果使用UNION ALL能满足要求的话,务必使用UNION ALL。还有一种情况,如果业务上能够确保不会出现重复记录

         5.使用not exists代替not in

             如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not exists的子查询依然能用到表上的索引

        6.in 和 exists区别选择。

          in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。因此,in用到的是外表的索引, exists用到的是内表的索引。

         如果查询的两个表大小相当,那么用in和exists差别不大,

         如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in

       7.避免在索引列上作如下操作,当在索引列上使用如上操作时,索引将会失效,造成全表扫描

    •      避免在索引字段上使用<>,!=
    •      避免在索引列上使用IS NULL和IS NOT NULL
    •      避免在索引列上出现数据类型转换(比如某字段是String类型,参数传入时是int类型)

         

    展开全文
  • 数据库优化 - SQL优化

    万次阅读 多人点赞 2019-12-13 10:33:07
    以实际SQL入手,带你一步一步走上SQL优化之路!
    前面一篇文章从实例的角度进行数据库优化,通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢,影响业务流程。本文从SQL角度进行数据库优化,提升SQL运行效率。

    判断问题SQL

    判断SQL是否有问题时可以通过两个表象进行判断:

    • 系统级别表象
      • CPU消耗严重
      • IO等待严重
      • 页面响应时间过长
      • 应用的日志出现超时等错误

    可以使用sar命令,top命令查看当前系统状态。

    也可以通过Prometheus、Grafana等监控工具观察系统状态。(感兴趣的可以翻看我之前的文章)640?wx_fmt=png

    • SQL语句表象
      • 冗长
      • 执行时间过长
      • 从全表扫描获取数据
      • 执行计划中的rows、cost很大

    冗长的SQL都好理解,一段SQL太长阅读性肯定会差,而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手,如下所示:640?wx_fmt=png

    执行计划告诉我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基本可以判断这是一段"有味道"的SQL。

    获取问题SQL

    不同数据库有不同的获取方法,以下为目前主流数据库的慢查询SQL获取工具

    • MySQL

      • 慢查询日志
      • 测试工具loadrunner
      • Percona公司的ptquery等工具
    • Oracle

      • AWR报告
      • 测试工具loadrunner等
      • 相关内部视图如v$、$session_wait等
      • GRID CONTROL监控工具
    • 达梦数据库

      • AWR报告
      • 测试工具loadrunner等
      • 达梦性能监控工具(dem)
      • 相关内部视图如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为例,看看执行计划是什么。(每个数据库的执行计划都不一样,需要自行了解)explain sql640?wx_fmt=png

    字段 解释
    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;
      
    • 查看数据量  

      640?wx_fmt=png

    • 原执行时间640?wx_fmt=png

    • 原执行计划640?wx_fmt=png

    • 初步优化思路

    1. SQL中 where条件字段类型要跟表结构一致,表中user_id 为varchar(50)类型,实际SQL用的int类型,存在隐式转换,也未添加索引。将b和c表user_id 字段改成int类型。
    2. 因存在b表和c表关联,将b和c表user_id创建索引
    3. 因存在a表和b表关联,将a和b表seller_name字段创建索引
    4. 利用复合索引消除临时表和排序

    初步优化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`);
    

    查看优化后执行时间

    640?wx_fmt=png

    查看优化后执行计划640?wx_fmt=png

    查看warnings信息640?wx_fmt=png

    继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;

    查看执行时间

    640?wx_fmt=png

    查看执行计划640?wx_fmt=png

    总结

    1. 查看执行计划 explain
    2. 如果有告警信息,查看告警信息 show warnings;
    3. 查看SQL涉及的表结构和索引信息
    4. 根据执行计划,思考可能的优化点
    5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
    6. 查看优化后的执行时间和执行计划
    7. 如果优化效果不明显,重复第四步操作
     

    系列文章

     
     

    温馨提示

    如果你喜欢本文,请关注我的个人公众号!或者关注我的个人博客www.javadaily.cn

     

     

    展开全文
  • 常用SQL优化方法

    千次阅读 2020-03-12 07:26:53
    2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对字段进行 null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id...

    个人博客请访问 http://www.x0100.top  

    1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

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

    3、应尽量避免在 where 子句中对字段进行 null值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num is null

    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    select id from t where num=0

    4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num=10 or num=20

    可以这样查询:

    select id from t where num=10unionallselect id from t where num=20

    5、下面的查询也将导致全表扫描:(不能前置百分号)

    select id from t where name like ‘%c%’

    若要提高效率,可以考虑全文检索。

    6、in 和 not in 也要慎用,否则会导致全表扫描,如:

    select id from t where num in(1,2,3)

    对于连续的数值,能用 between 就不要用 in 了:

    select id from t where num between 1 and 3

    7、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    select id from t where num=@num

    可以改为强制查询使用索引:

    select id from t with(index(索引名)) wherenum=@num

    8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where num/2=100

    应改为:

    select id from t where num=100*2

    9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t wheresubstring(name,1,3)=’abc’–name以abc开头的idselect id from t wheredatediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

    应改为:

    select id from t where name like‘abc%’select id from t where createdate>=’2005-11-30′ andcreatedate<’2005-12-1′

    10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

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

    12、不要写一些没有意义的查询,如需要生成一个空表结构:

    select col1,col2 into #t from t where 1=0

    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

    create table #t(…)

    13、很多时候用 exists 代替 in 是一个好的选择:

    select num from a where num in(select numfrom b)

    用下面的语句替换:

    select num from a where exists(select 1from b where num=a.num)

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

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

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

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

    18、尽可能的使用varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    19、任何地方都不要使用 select *from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

    20、尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

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

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

    23、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

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

    25、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

    26、使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

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

    28、在所有的存储过程和触发器的开始处设置SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

    29、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

    30、尽量避免大事务操作,提高系统并发能力。

    关注微信公众号和今日头条,精彩文章持续更新中。。。。。

     

    展开全文
  • sql优化的几种方法

    2020-07-26 23:32:35
    sql优化的几种方法sql优化的几种方法sql优化的几种方法sql优化的几种方法sql优化的几种方法
  • MySQL版SQL优化

    千人学习 2019-07-08 10:47:39
    本课程通过Centos版的MySQL讲解了SQL优化的一些常见手段。 其中讲解了MySQL的分层、存储引擎等底层逻辑,并讲解了常见的索引优化手段。在讲解时,先通过理论对先关的优化知识进行了铺垫,然后使用实际的案例详细...
  • 真·mysql中的SQL优化

    千次阅读 2018-08-01 18:15:42
    1. 优化SQL语句中的一般步骤 通过show status命令了解各种SQL的执行频率 定位执行效率较低的SQL语句 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL...

    1. 优化SQL语句中的一般步骤


    通过show status命令了解各种SQL的执行频率

    这里写图片描述
    这里写图片描述
    这里写图片描述

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

    • 可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL语句,用-log-slow-queries[=file_name]选 项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志 文件。

    • 慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢 询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程, 包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操 作进行优化。

    通过EXPLAIN分析低效的SQL执行计划

    这里写图片描述

    这里写图片描述
    每个列的简单解释如下:

    • select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接
      或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或
      者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。

    • table:输出结果集的表。

    • type:表示表的连接类型,性能由好到差的连接类型为 system(表中仅有一行,即
      常量表)、const(单表中最多有一个匹配行,例如 primary key 或者 unique index)、 eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接 中使用primarykey或者uniqueindex)、re(f 与eq_ref类似,区别在于不是使用primary key 或者 unique index,而是使用普通的索引)、ref_or_null(与 ref 类似,区别在于 条件中包含对 NULL 的查询)、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似, 区别在于 in 的后面是查询非唯一索引字段的子查询)、range(单表中的范围查询)、 index(对于前面的每一行,都通过查询索引来得到数据)、all(对于前面的每一行,都通过全表扫描来得到数据)。

    • possible_keys:表示查询时,可能使用的索引。

    • key:表示实际使用的索引。

    • key_len:索引字段的长度。

    • rows:扫描行的数量。

    • Extra:执行情况的说明和描述。

    确定问题并且采取相应的优化措施

    这里写图片描述
    这里写图片描述
    这里写图片描述

    *2. 索引问题


    • 索引的存储分类

            MyISAM 存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件;InnoDB 存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。
            MySQL 中索引的存储类型目前只有两种(BTREE 和 HASH),具体和表的存储引擎相关: MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引;MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。
            MySQL 目前不支持函数索引,但是能对列的前面某一部分进索引,例如 name 字段,可 以只取 name 的前 4 个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计 表结构的时候也可以对文本列根据此特性进行灵活设计。

    • MySQL如何使用索引
      这里写图片描述

            索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作 性能的最佳途径。
            查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那 么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

    使用索引

    在 MySQL 中,下列几种情况下有可能使用到索引。
    (1)对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用, 举例说明如下。
    首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:
    然后按 company_id 进行表查询,具体如下:
    这里写图片描述
    这里写图片描述
    可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能 用到,这就是索引的前缀特性。但是如果只按 moneys 条件查询表,那么索引就不会 被用到,具体如下:
    这里写图片描述

    (2)对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会 被使用,来看下面两个执行计划:
    这里写图片描述
    这里写图片描述
    可以发现第一个例子没有使用索引,而第二例子就能够使用索引,区别就在于“%”的位置 不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。 另外,如果如果 like 后面跟的是一个列的名字,那么索引也不会被使用。

    (3)如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%…%’。

    (4)如果列名是索引,使用column_name is null将使用索引。如下例中查询name为null 的记录就用到了索引:
    这里写图片描述

    存在索引但不使用索引

    在下列情况下,虽然存在索引,但是 MySQL 并不会使用相应的索引。
    (1) 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如如果列
    key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:

    SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

    (2) 如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。
    (3) 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引, 那么涉及到的索引都不会被用到,例如:
    这里写图片描述
    这里写图片描述
    从上面可以发现只有 year 列上面有索引,来看如下的执行计划:
    这里写图片描述
    可见虽然在 year 这个列上存在索引 ind_sales_year,但是这个 SQL 语句并没有用到这个索引, 原因就是 or 中有一个条件中的列没有索引。
    (4) 如果不是索引列的第一部分,如下例子:
    这里写图片描述
    可见虽然在 money 上面建有复合索引,但是由于 money 不是索引的第一列,那么在查询中 这个索引也不会被 MySQL 采用。
    5) 如果 like 是以%开始,例如:
    这里写图片描述
    这里写图片描述
    可见虽然在 name 上建有索引,但是由于 where 条件中 like 的值的“%”在第一位了,那么 MySQL 也不会采用这个索引。

    (6) 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引 起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的 常量值进行转换以后才进行检索。如下面的例子中 company2 表中的 name 字段是字符型的, 但是 SQL 语句中的条件值 294 是一个数值型值,因此即便在 name 上有索引,MySQL 也不能 正确地用上索引,而是继续进行全表扫描。
    这里写图片描述
    这里写图片描述
            从上面的例子中可以看到,第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列 name,那么虽然在 name 列上有索引,但是也没有用到;而第二个 SQL 语句就可以正确使 用索引。

    查看索引使用情况

            如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的 次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。
            Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值 的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描, Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引,具体如下。
    这里写图片描述
    从上面的例子中可以看出,目前使用的 MySQL 数据库的索引情况并不理想。

    3. 两个简单实用的优化方法


            对于大多数开发人员来说,可能只希望掌握一些简单实用的优化方法,对于更多更复杂的优 化,更倾向于交给专业 DBA 来做。

    定期分析表和检查表

    分析表的语法如下:

    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

            本语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信 息,使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计 划,执行一次分析表可能会解决问题。在分析期间,使用一个读取锁定对表进行锁定。这对 于 MyISAM, BDB 和 InnoDB 表有作用。对于 MyISAM 表,本语句与使用 myisamchk -a 相当,下例中对表 msgs 做了表分析:
    这里写图片描述
    检查表的语法如下:

    CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

    检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。 对于 MyISAM 表,关键字统计数据被更新,例如:
    这里写图片描述
    CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在,举例如 下。
    (1)首先我们创建一个视图。
    这里写图片描述
    (2)然后 CHECK 一下该视图,发现没有问题。
    这里写图片描述
    (3)现在删除掉视图依赖的表。
    这里写图片描述
    (4)再来 CHECK 一下刚才的视图,发现报错了。
    这里写图片描述

    定期优化表

    优化表的语法如下:

    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

    如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表(含有 VARCHAR、 BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE 命令来进行表优化。这个 命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但 OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。
    以下例子显示了优化表 sales 的过程:
    这里写图片描述

    4. 常用的SQL优化


    前面我们介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中,除了使用查询外,我 们还会使用一些其他的常用 SQL,比如 INSERT、GROUP BY 等。

    大批量插入数据

    当用 load 命令导入数据的时候,适当的设置可以提高导入的速度。
    对于 MyISAM 存储引擎的表,可以通过以下方式快速的导入大量的数据。
    这里写图片描述
    DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入 大量的数据到一个非空的 MyISAM 表时,通过设置这两个命令,可以提高导入的效率。对于 导入大量数据到一个空的 MyISAM 表,默认就是先导入数据然后才创建索引的,所以不用进 行设置。
    下面例子中,用 LOAD 语句导入数据耗时 115.12 秒:
    这里写图片描述
    而用 alter table tbl_name disable keys 方式总耗时 6.34 + 12.25 = 18.59 秒,提高了 6 倍多。
    这里写图片描述
    这里写图片描述
    上面是对MyISAM表进行数据导入时的优化措施,对于InnoDB类型的表,这种方式并不 能提高导入数据的效率,可以有以下几种方式提高InnoDB表的导入效率。
    (1)因为 InnoDB 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺 序排列,可以有效地提高导入数据的效率。
    例如,下面文本film_test3.txt是按表film_test4的主键存储的,那么导入的时候共耗时 27.92秒。
    这里写图片描述
    而下面的 film_test4.txt 是没有任何顺序的文本,那么导入的时候共耗时 31.16 秒。
    这里写图片描述
    从上面例子可以看出当被导入的文件按表主键顺序存储的时候比不按主键顺序存储的时候 快 1.12 倍。
    (2)在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行 SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
    例如,当 UNIQUE_CHECKS=1 时:
    这里写图片描述
    当 SET UNIQUE_CHECKS=0 时:
    这里写图片描述
    可见比 UNIQUE_CHECKS=0 的时候比 SET UNIQUE_CHECKS=1 的时候要快一些。
    (3)如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自
    动提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。 例如,当 AUTOCOMMIT=1 时:
    这里写图片描述
    当 AUTOCOMMIT=0 时:
    这里写图片描述
    对比一下可以知道,当 AUTOCOMMIT=0 时比 AUTOCOMMIT=1 时导入数据要快一些。

    优化 INSERT 语句

    当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。

    • 如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大
      缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语 句快(在一些情况中几倍)。下面是一次插入多值的一个例子:
    insert into test values(1,2),(1,3),(1,4)...
    • 如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有 真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其 他用户对表的读写完后才进行插入;

    • 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);

    • 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 MyISAM 表使用;

    • 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

    优化 GROUP BY 语句

            默认情况下,MySQL 对所有 GROUP BY col1,col2….的字段进行排序。这与在查询中指定 ORDER BY col1,col2…类似。因此,如果显式包括一个包含相同的列的 ORDER BY 子句,则 对 MySQL 的实际执行性能没有什么影响。
            如果查询包括 GROUP BY 但用户想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序,如下面的例子:
    这里写图片描述
    这里写图片描述
    这里写图片描述
    从上面的例子可以看出第一个 SQL 语句需要进行“filesort”,而第二个 SQL 由于 ORDER BY NULL 不需要进行“filesort”,而 filesort 往往非常耗费时间。

    优化 ORDER BY 语句

    在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序。 WHERE 条件和 ORDER BY 使用相同的索引,并且 ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者都是降序。
    这里写图片描述
    但是在以下几种情况下则不使用索引:
    这里写图片描述

    优化嵌套查询

            MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查 询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很 多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起 来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。
            在下面的例子中,要从 sales2 表中找到那些在 company2 表中不存在的所有公司的信息:

    这里写图片描述
    这里写图片描述
    果使用连接(JOIN)来完成这个查询工作,速度将会快很多。尤其是当 company2 表 中对 id 建有索引的话,性能将会更好,具体查询如下:
    这里写图片描述
    从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。
    连接(JOIN)之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这 个逻辑上的需要两个步骤的查询工作。

    MySQL 如何优化 OR 条件

            对于含有 OR 的查询子句,如果要利用索引,则 OR 之间的每个条件列都必须用到索引; 如果没有索引,则应该考虑增加索引。
            例如,首先使用 show index 命令查看表 sales2 的索引,可知它有 3 个索引,在 id、year 两个字段上分别有 1 个独立的索引,在 company_id 和 year 字段上有 1 个复合索引。
    这里写图片描述
    这里写图片描述
    这里写图片描述
    然后在两个独立索引上面做 OR 操作,具体如下:
    这里写图片描述
    可以发现查询正确的用到了索引,并且从执行计划的描述中,发现 MySQL 在处理含有 OR 字句的查询时,实际是对 OR 的各个字段分别查询后的结果进行了 UNION。 但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候,却不能用到索引, 具体结果如下:
    这里写图片描述这里写图片描述

    使用 SQL 提示

    SQL 提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在 SQL 语句中加入一些 人为的提示来达到优化操作的目的。
    下面是一个使用 SQL 提示的例子:

       SELECT SQL_BUFFER_RESULTS * FROM...

    这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后,所有表上的锁 定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助,因为 可以尽快释放锁资源。
    下面是一些在 MySQL 中常用的 SQL 提示。

    1.USE INDEX

    在查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可 以让 MySQL 不再考虑其他可用的索引。
    这里写图片描述

    2.IGNORE INDEX

    如果用户只是单纯地想让 MySQL 忽略一个或者多个索引,则可以使用 IGNORE INDEX 作
    为 HINT。同样是上面的例子,这次来看一下查询过程忽略索引 ind_sales2_id 的情况:
    这里写图片描述
    这里写图片描述
    从执行计划可以看出,系统忽略了指定的索引,而使用了全表扫描。

    3.FORCE INDEX

    为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 HINT。例如, 当不强制使用索引的时候,因为 id 的值都是大于 0 的,因此 MySQL 会默认进行全表扫描, 而不使用索引,如下所示:

    这里写图片描述
    但是,当使用 FORCE INDEX 进行提示时,即便使用索引的效率不是最高,MySQL 还是选择使 用了索引,这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示 后再次执行上面的 SQL:
    这里写图片描述
    这里写图片描述
    果然,执行计划中使用了 FORCE INDEX 后的索引。

    SQL优化调试在日常的开发工作中很重要,快速定位到问题不仅需要一定的技巧,更需要的是经验,熟练使用explain关键字和慢查询日志。

    展开全文
  • sql优化

    千次阅读 2019-03-25 16:21:44
    7sql优化 1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建 立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行 全表扫描,如: ...
  • SQL优化

    千次阅读 2020-05-05 17:31:29
    文章目录一、Linux下RPM版MYSQL安装、启停1.1 环境1.2 MySQL版本1.3 MySQL安装二、MySQL启动问题、配置文件、编码问题三、MySQL分层、存储引擎...索引的利弊五、B树与索引5.1 B树5.2 索引六、SQL优化准备6.1 SQL性能...
  • sql优化的几种方式

    万次阅读 多人点赞 2019-11-11 09:15:50
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也...二、SQL优化的一些方法 1.对查询进行优化,应尽量避免全表扫描...
  • sql优化(面试必问一)

    万次阅读 多人点赞 2018-03-28 12:37:51
    这时候就需要你谈一下sql优化相关的内容 , 一下几个方面1、慢查询2、索引3、拆分表数据库索引变快全部检索(扫描)系统集成二叉树算法--》索引文件 物理位置log2N 检索10次可以检索2的10次方个数(1024)全文索引,...
  • Sql性能优化看这一篇就够了

    万次阅读 多人点赞 2020-03-03 16:57:58
    一个优秀开发的必备技能:性能优化,包括:JVM调优、缓存、Sql性能优化等。本文主要讲基于Mysql的索引优化。 首先我们需要了解执行一条查询SQL时Mysql的处理过程: 其次我们需要知道,我们写的SQL在Mysql的执行...
  • SQL查询优化

    万次阅读 2019-12-25 17:58:53
    SQL查询优化 一、获取有性能问题SQL的三种方法 通过用户反馈获取存在性能问题的SQL 通过慢查询日志获取存在性能问题的SQL 实时获取存在性能问题的SQL 二、慢查询日志介绍 1、使用慢查询日志获取有性能问题...
  • 数据库SQL优化大总结之 百万级数据库优化方案

    万次阅读 多人点赞 2016-06-23 10:35:05
    网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉好的...
  • SQL优化新书《SQL优化核心思想》终于出版了

    千次阅读 热门讨论 2020-05-11 09:54:16
    耗时三年,SQL优化大作终于出版了,有想提升SQL优化水平的同学,可以买本看看本书共10章内容:第一章介绍SQL优化的基础知识;第二章讲解统计信息相关内容;第三章讲解执行计划,快速找出SQL性能瓶颈;第四章讲解...
  • 给大家分享数据库sql优化视频,分享目的在于让小白都可以很好的免费学习,里面包含笔记,语句,视频全套,某些人买了还要赚取别人的钱,我真看不过去了,本人决定花钱购买放到云盘供大家学习。 第1周 从案例中推导...
  • SQL优化工具SQLAdvisor使用

    千次阅读 2020-05-27 19:19:40
    例行SQL优化,不仅可以提升程序性能,还能够降低线上故障的概率。目前常用的SQL优化方式包括但不限于:业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。索引...
  • 本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划”详细介绍各种执行计划的含义与操作,为后面的深入分析打下基础。重点讲解执行计划在sql语句执行的...
  • Oracle SQL优化 总结

    万次阅读 多人点赞 2017-12-02 16:51:55
    这篇BLog只看SQL 优化的相关的注意事项,数据库优化部分以后有空在整理。 SQL优化主要涉及几个方面: (1) 相关的统计信息缺失或者不准确 (2) 索引问题 (3) SQL 的本身的效率问题,比如使用绑定变量,...
  • SQL 优化:连接表查询优化

    千次阅读 2019-03-26 14:59:12
    SQL 优化:连接表查询优化 两天前,在项目中遇到一个需要优化SQL的情况,现在优化已完成,记录下方法。 问题:项目中某个页面的搜索功能耗时很长,需要重新编写SQL或者优化 解决: 一、分析原SQL: 查看原来的...
  • MySQL中SQL优化案例

    千次阅读 2019-02-27 09:20:19
     关于SQL优化,作为一个数据库运维者,这是必须掌握的技能,对于我个人来说,为了提升自己,借助工作闲暇时间不得不去研究数据库优化方面的东西,以下通过一个简单的例子大体了解,如果遇到慢SQL该如何去优化,应该...
1 2 3 4 5 ... 20
收藏数 419,564
精华内容 167,825
关键字:

sql优化