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.umaske.com

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