一.表设计优化

a.字段类型优化

           
一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量。
           
            1.
尽量使用数字类型字段,若只含数值信息的字段尽量不要设计为字符型,这将会降低查询和连接性能,增加开销,因为如果是字符串的话,数据库会将每个字符串的字符逐个比较,而数字型只要比较一次
           
            2. 尽量使用 tinyint、 smallint、 mediumint 作为整数类型而非 int
           
            3. 尽可能的使用 varchar/nvarchar 代替 char/nchar,且长度只分配真正需要的空间
           
            4. 使用枚举或整数代替字符串类型
           
            5. 尽量使用 TIMESTAMP而非 DATETIME
           
            6. 单表不要有太多字段,建议在20以内
           
            7. 避免使用NULL字段,很难查询优化且占用额外索引空间

 b.索引优化

            1. 考虑在 where 及 order by 和 join 涉及的列上建立索引,能够对查询进行优化,从而避免全表扫描
           
            2. 不要在where条件中对字段进行关于null的判断,否则会导致放弃使用索引而进行全表扫描(列如: select id
from a where id != null),所以在设计表的时候尽量不要插入null值,而是用 0 或其他值表示
           
            3.
当某一列有大量重复数据,如sex性别字段中男女各一半,即使给这个字段建立索引,索引也可能并不有效,因为sql是根据表中数据进行查询优化的
           
            4.
索引太多的话虽然能够提高查询效率,但是insert和update时可能会重建索引,插入和更新效率将会降低,一般一个表的索引数最好不要超过6个
           
            5.
尽量避免更新索引列的数据,因为索引数据列的顺序是表记录的物理存储顺序,一旦改变数据值将导致整个表记录的顺序调整,耗费大量资源,所以当某列需要频繁更新并频繁查询,那么需要全面衡量是否将该列设为索引

 c.临时表优化

             1. 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)
            
            2. 避免频繁创建和删除临时表,以减少系统表资源的消耗。
           
            3.
临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
           
            4. 在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量
log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
          
            5. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop
table ,这样可以避免系统表的较长时间锁定。
         
            6. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
         
            7. 使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

二.sql优化 

a.where语句优化

    1.以下为放弃索引而导致全表扫描的条件查询,应该避免

        1.1 在 where 子句中使用 != 或 <>操作符
        
        1.2 在 where 子句中使用!=或<>操作符
        
        1.3 在 where 子句中使用 or 来连接条件,我们可以用 union 来代替 or
        
        1.4 在 where 子句中使用 in 和 not in,对于连续的数据,我们可以使用 between
        
        1.5 在 where 子句中"="的左边进行函数,算数运算或其他表达式运算,如:select id from t where
num/2= 1; select id from t where sum(num,1)= 2
       
        1.6 在 where 子句中的模糊查询 like 后, 使用左模糊匹配或全模糊匹配,如 "%xxx"
       
        1.7 在 where 子句中使用参数

   2.效率优化

        2.1 在 where 子句中,or 可以改写成 in, or 的效率是n级别,in的效率是log(n)级别,in 的个数建议控制在 200
以内
        2.2 在如 select num from a where num in(select num from b)
这样的查询语句,如果b表比a表大,则可以用 exists 代替 in

 

b.其他查询优化

       1. 永远不要使用 select * from t, 应该用具体的字段代替 "*"
      
      2. sql语句要尽可能简单:一条sql只能在一个cpu运算,所以大语句要拆成拆小语句,减少锁时间(一条大sql可以堵死整个库)
      
      3. 少用函数和触发器,关于数据的操作尽量在应用程序实现
      
      4. 尽量不要进行全表搜索,而是使用 limit 来分页且每页数量不要太大
     
      5. 只有一条查询结果时,推荐使用 “limit 1”,“limit 1” 可以避免全表扫描,找到对应结果就不会再继续扫描了
      
      6. 排序尽量使用升序
      
      7. Innodb上用 select count(*),因为 Innodb 会存储统计信息;
      
      8. 复合索引高选择性的字段排在前面
      
      9. 避免返回大量数据,如果数据量过大,应该考虑需求是否合理
      
      10.对于相同结果集的多次查询,保持 SQL 语句前后一致,这样可以充分利用查询缓冲

 

c.事务sql优化

      1. 尽量规避大事务的 SQL,大事务的 SQL 会影响数据库的并发性能及主从同步;
      
      2. 删除表所有记录请用 truncate,不要用 delete
      
      3. 尽量不要使用存储过程

技术
©2019-2020 Toolsou All rights reserved,
Qt学习2——.pro文件和.h文件介绍LinkedHashMap基本用法&使用实现简单缓存pytorch之ResNet18(对cifar10数据进行分类准确度达到94%)华为Mate 40 Pro+ 5G曝光:徕卡电影镜头、陶瓷机身统计字母个数(java语言实现)JavaScript中的 Call 和 Apply1190 反转每对括号间的子串 leetcode记一次EventBus内存泄露导致的项目问题浅谈uni-app页面传值问题 Chrome OS,对程序员和Windows意味着什么?,互联网营销