常用优化

  1. 3. 优化ORDER BY 
  2.     1.适当增大sort_buffer_size和max_length_for_sort_data值
  3.     2.SELECT需要的字段,尽量不要SELECT *
  4. 4. 优化GROUP BY
  5.     1.强制不排序 ORDER BY NULL
  6.     2.JOIN取代嵌套子查询
  7. 5. 优化OR
  8.     OR的各个字段都要有索引
  9. 6. 优化分页查询
  10.     1.使用关联查询,先ORDER BY且LIMIT后的分页数据,主键JOIN回表
  11.     2.记录last_page_record,利用LIMIT n查询(只用于排序字段不会重复场景)

MySQL Server优化

  1. InnoDB优化： 
  2. 1.增大innodb_buffer_pool_size 
  3. 2.调整old sublist大小，SHOW GLOBAL VARIABLES LIKE '%innodb_old_blocks_pct%' 
  4. 3.调整innodb_old_blocks_time 
  5. 4.减少缓存池数量，减少内部争用，调整innodb_buffer_pool_instances 
  6. 5.控制innodb buffer刷新，innodb_max_dirty_pages_pct,innodb_io_capacity 
  7. 6.打开doublewrite，SHOW GLOBAL VARIABLES LIKE '%doublewrite%' 
  10. 并发优化： 
  11. 1.调整max_connections 
  12. 2.调整back_log 
  13. 3.调整table_open_cache 
  14. 4.调整thread_cache_size 
  15. 5.调整innodb_lock_wait_timeout
  1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  2. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
  3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
  1.    select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

  1. select id from t where num=0
  1. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
  1. select id from t where num=10 or num=20

可以这样查询：

  1. select id from t where num=10
  3. union all
  5. select id from t where num=20
  1. in 和 not in 也要慎用，否则会导致全表扫描
  1. select id from t where num in(1,2,3)
  1. 对于连续的数值，能用 between 就不要用 in 了
  1. select id from t where num between 1 and 3
  1. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
  1. select id from t where substring(name,1,3)='abc'
  1. 不让使用 COUNT(列名)或 COUNT(常量)来替代 COUNT(*)呢
    • count(*) 会统计值为null的行，而count(列名)不会统计此列为null值的行
  2. 操作符<>优化
    • 通常<>操作符无法使用索引，举例如下，查询金额不为100元的订单：
  1. select id from orders where amount != 100;

改

  1. (select id from orders where amount > 100)
  2.  union all
  3. (select id from orders where amount < 100 and amount > 0)
  1. 排序字段应该是唯一索引
  1. 优化关联查询
    • 确保ON和USING子句的列上有索引，需要注意的是在创建关联索引时需要考虑关联顺序，如果A JOIN B时，就不需要在A表的对应的列上创建索引，只需要在B表上的创建相应的索引即可。因为A表上的索引不会执行。
    • 确保GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MYSQL才有可能使用索引来优化。
    • 优化UNION：尽量不要使用UNION,MYSQL处理union的方式是把每个查询的结果放到临时表中，然后再把数据返回，很多优化的策略在union中都不能做到很好。如果非要用UNION时，要加上ALL关键字，即UNION ALL, 如果没有ALL关键字，MYSQL会给临时表加上DISTINCT选项，会导致该表的数据做唯一行检查。

优化建议

  1. EXPLAIN你的SELECT查询
  2. 当只要一行数据时使用LIMIT 1
  3. 千万不要 ORDER BY RAND()
  4. 尽可能的使用 NOT NULL
  5. 索引列不能参与计算
  6. 主键推荐用自增id，而不是uuid，uuid随机，会导致页的分裂
  7. mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

join 的优化

  1. 小结果集驱动大结果集。用数据量小的表去驱动数据量大的表，这样可以减少内循环个数，也就是被驱动表的扫描次数。
  2. 用来进行 join 的字段要加索引，会触发 INLJ 算法，如果是主键的聚簇索引，性能最优。例子：第一个子查询是 72075 条数据，join 的第二条子查询是 50w 数据，主要的优化还是驱动表是小表，后面的是大表，on 的条件加上了唯一索引。

select * from table t1 join table t2 on t1.id = t2.id

  1. 如果无法使用索引，那么注意调整 join buffer 大小，适当调大些
  2. 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）