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优化的几种方法
  • sql优化的几种方式

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

    一、为什么要对SQL进行优化

    我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也无法判断SQL对程序的运行效率有多大,故很少针对SQL进行专门的优化,而随着时间的积累,业务数据量的增多,SQL的执行效率对程序的运行效率的影响逐渐增大,此时对SQL的优化就很有必要。

    二、SQL优化的一些方法

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。    
        
    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:    
    select id from t where num is null    
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:    
    select id from t where num=0    
        
    3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。    
        
    4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:    
    select id from t where num=10 or num=20    
    可以这样查询:    
    select id from t where num=10    
    union all    
    select id from t where num=20    
        
    5.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    
        
    6.下面的查询也将导致全表扫描:    
    select id from t where name like '%abc%'    
        
    7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
    select id from t where num/2=100    
    应改为:    
    select id from t where num=100*2    
        
    8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
    select id from t where substring(name,1,3)='abc'--name以abc开头的id    
    应改为:    
    select id from t where name like 'abc%'    
        
    9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。    
        
    10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。    
        
    11.不要写一些没有意义的查询,如需要生成一个空表结构:    
    select col1,col2 into #t from t where 1=0    
    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:    
    create table #t(...)    
        
    12.很多时候用 exists 代替 in 是一个好的选择:    
    select num from a where num in(select num from b)    
    用下面的语句替换:    
    select num from a where exists(select 1 from b where num=a.num)    
        
    13.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。    
        
    14.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,    
    因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。    
    一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。    
        
    15.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。    
    这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。    
        
    16.尽可能的使用 varchar 代替 char ,因为首先变长字段存储空间小,可以节省存储空间,    
    其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。    
        
    17.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。    
        
    18.避免频繁创建和删除临时表,以减少系统表资源的消耗。

    19.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。    
        
    20.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,    
    以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    21.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。    
        
    22.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。    
        
    23.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

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

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

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

     

     

    展开全文
  • SQL优化

    2020-10-14 22:53:37
    文章目录序1....本文以开发人员的角度介绍SQL优化,旨在帮助开发人员在开发过程中书写出高性能SQL语句,若是专业DBA级别数据库优化请忽略。 1. 系统常见瓶颈 系统常见的问题:性能下降、SQL慢、执行时间
  • 真·mysql中的SQL优化

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

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

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

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

    万次阅读 2019-12-25 17:58:53
    SQL查询优化 一、获取有性能问题SQL的三种方法 通过用户反馈获取存在性能问题的SQL 通过慢查询日志获取存在性能问题的SQL 实时获取存在性能问题的SQL 二、慢查询日志介绍 1、使用慢查询日志获取有性能问题...
  • SQL优化新书《SQL优化核心思想》终于出版了

    千次阅读 热门讨论 2020-05-11 09:54:16
    耗时三年,SQL优化大作终于出版了,有想提升SQL优化水平的同学,可以买本看看本书共10章内容:第一章介绍SQL优化的基础知识;第二章讲解统计信息相关内容;第三章讲解执行计划,快速找出SQL性能瓶颈;第四章讲解...
  • sql语句where in子查询优化

    万次阅读 2018-06-01 14:22:26
    好好理解好好理解 转载请注明出处。 作者:wuxiwei 出处:http://www.cnblogs.com/wxw16/p/6105624.html
  • sql server 性能优化和日常管理维护

    万人学习 2019-11-20 16:04:13
    介绍数据库规划设计、事务和锁的基本知识,如何处理阻塞和死锁,优化索引和读懂统计信息和执行计划等知识,同时详细介绍SQL 2014新功能特性内存优化表、列存储索引、alwayOn高可用。
  • sql优化in语句

    千次阅读 2016-11-03 08:57:56
    在很多时候我们在sql中会用到in语句,in语句会使得sql查询不使用索引,这也大大减低了sql执行的效率,为了能够让sql在查询中使用索引,有很多种方式可以优化,比如如果in中的类型是确定值,那么可以用 字段=确定值 ...
  • 163.Oracle数据库SQL开发之 SQL优化——优化工具 欢迎转载,转载请标明出处: 其他一些优化工具如下。 1. OEM Diagnostics Pack Oracle Enterprise Manager DiagnosticsPack(Oracle 企业管理器诊断包)捕获操作系统...
  • 记一次sql优化经历(优化in语句)

    万次阅读 2019-06-03 16:44:59
    业务背景: 根据客户群组查询标签,群组和标签的对应关系在tb_biz_type_tags中 根据标签查询客户的手机号和机构标识(tb_customer_tags) ...原始sql: select a.* from tb_account a where a.mobile in(...
  • 使用 Toad 实现 SQL 优化

    万次阅读 2012-03-21 15:36:48
    It is very easy for us to implement sql tuning by toad. We need to do is just give complex sql statement to toad. The articlegive you some examples to demonstrate how to tuning sql by toad for oracle
  • MySQL数据库:SQL语句优化、性能优化详细总结

    千次阅读 多人点赞 2020-09-13 22:16:10
    一、优化SQL语句的一般步骤: 二、MySQL 常用的SQL语句优化方法: 三、MySQL 常用的索引优化方法: 四、MySQL数据库的优化目标、常见误区和基本原则: 五、MySQL数据库的表结构优化: 六、MySQL数据库的缓存...
  • mysql 查看优化器重写后的sql

    千次阅读 2016-04-23 19:40:03
    sql优化器会重写sql  sql在执行时,并不一定就会按照我们写的顺序执行,mysql优化器会重写sql,如何才能看到sql优化器重写后的sql呢?这就要用到explain extended和show warnings了。 explain extended sql语句,...
1 2 3 4 5 ... 20
收藏数 423,188
精华内容 169,275
关键字:

sql优化