mysql优化之sql优化

sql优化的基本思路是优化索引的使用,实践证明查询只要是正确使用了索引,查询效率就会快很多

查看sql执行频率

  1. show ( global全局) status 'com_______'//7个下划线,占位符
  2. show global status like 'Innodb_rows_%';
  3. Explain
  4. Show profiles
    1. select @@have_profiling查看是否开启
    2. Show profile (all|cpu等) for query [id]

sql优化

  1. innodb类型的表,大批量插入,如何提高导入效率
    1. 用load data local infile [filepath] into table tablename fields terminated by ',' lines terminated by '\n'
    2. 主键按顺序插入
    3. load data之前关闭唯一性校验,unique_check,load data之后开启
    4. 事务改成手动提交,set autocommit =0,导入之后,再开启
  2. 优化insert语句 3. 多个插入语句改为一个。例:insert into tbname values(1,'xx1'),(2,'xx2'),(3,'xx3') 2. 在事务中进行数据插入,原理和第1条一样,就是多个语句一次执行 3. 数据有序插入,例如:插入顺序按id 1,2,3 ,4插入比3,2,1,4快
  3. 优化order by
    1. explain查出orderby排序有2中,分别是using filesort、using index。而using index是使用索引排序,速度较快,所以能用索引排序就用索引排序。
    2. 适当根据系统性能提高max_length_for_sort_data和sort_buffer_size这2个系统变量的设置。原因是mysql中的两次扫描算法和一次扫描算法。如果这2个变量的值够大,mysql使用的是一次扫描算法,效率比两次的要高。
  4. 优化group by
    1. 如果group by之后不需要排序,可以再后面加上order by null
    2. 跟order by一样,使用索引
  5. 优化嵌套查询
    1. 把子查询改成使用join的方式
  6. 优化or条件
    1. 包含or的语句要利用索引,or之间的每个条都必须用到索引,而且不能使用复合索引
  7. 优化分页查询(当数据比较大的时候,例如30万,查询越往后,效率越低)
    1. 在索引上完成分页操作,然后根据主键关联回原表查询所需要的数据
    2. 如果主键是自增,并且没有断层,那么可以把limit换成某个位置的查询,例如:select * from tb where id>300000 limit 10
  8. 使用sql提示
    1. use index 提示mysql使用index
    2. ignore index 提示mysql忽略index
    3. force index 提示mysql强制使用index(无论索引查询是快是慢)

林明潭blog
请先登录后发表评论
  • latest comments
  • 总共0条评论