MySql性能优化

一.表设计优化

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. 尽量不要使用存储过程