Mysql优化是一个万年不变的话题,难道是因为mysql太烂了,所以需要不断的优化?当然不是了,无数的开发者都在用,怎么可能烂。废话不多说,直接总结百万级别数据库的优化方案,哈哈,你数据库要是只有几万几十万的数据貌似是不需要怎么优化的。

sql的软优化不管怎么优化,本质上都是往如何更好的利用索引这个方向靠拢。为啥?可以参考一下我的另一篇文章:索引的数据结构

  1. 创建索引
    对于查询的优化,最重要的一点就是建立合适的索引。很多时候的性能问题都是我们忘记加索引导致全表扫描引起的。
    但是,也不是所有情况都适合建立索引,比如某个字段有大量重复的值,性别只有男女这样的,建立索引不仅没什么优势,还会影响更新速度,这就是过度索引。
  2. 复合索引
    复合索引会比单个索引能带来更高的效率。
    举个例子:select * from table_a where gender=1 and area=’shanghai’
    对于这条sql,如果我们是单独在gender和area建立索引,那么由于mysql查询每次只能用一个索引,这样相对于不做全表扫描确实提升了效率,但是如果我们在gender和area上建立复合索引,那将带来更高的效率。
    最左原则:如果我们建立(gender,area,salary)这样的复合索引,那就相当于创建了(gender,area,salary),(gender,area)和(salary)三个索引,要用到这个索引,必须依次命中最左边的索引,才能使用,所以要把最常使用的字段放在最左边,依次递减,这就是最佳左前缀原则
  3. 索引不能包含NULL值的列
    只要列中含有NULL值的就都不会被包含在索引里,所以我们在设计数据库的时候,不要让字段的默认值为NULL。
  4. 使用短索引
    短索引用的比较少,不过也是优化的一个点。加入有个CHAN(255)的列,如果前10个字符都是一样的,可能是公共前缀,那么就不要对整个列进行索引,而是使用短索引,提高查询效率,节省磁盘空间和减少IO操作
  5. 排序的索引问题
    mysql查询只能使用一个索引,所以where里使用了索引,那么order by的列是用不上索引的,因此,数据库默认排序可以满足需求的话,尽量不要使用排序,如果非要用,那么请建立合适的复合索引
  6. like语句的索引问题
    一般不建议使用like语句,效率不高,并且还有一个问题,like %xxxx这样的以%开头的用不到索引,like xxxx%这样的可以。
  7. 不要在列上进行运算
    eg:select from table_a where YEAR(create_time) > ‘2020’;
    这样会导致create_time的索引失效,引发全表扫描。
    优化的方向,让这条sql命中索引
    select
    from table_a where create_time > ‘2020-11-05’;
  8. 不使用 NOT IN 和 <> 和 != 操作
    NOT IN , <> 和 !=操作都不会使用索引,NOT IN 可以用 NOT EXISTS代替,id<>10 可以用 id<10 or id>10代替
select id from t where num in(1,2,3)
// 对于连续的数值,能用 between 就不要用 in 了
select id from t where num between 1 and 3
// 很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
// 用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
  1. 尽量使用数字型字段
    若只包含数值信息的字段,尽量不要使用字符型,会降低查询和连接的性能,增加存储开销
    因为数据库引擎比较字符串是逐一比较每个字符,而数值只要比较一次
  2. 禁止使用 select
    在任何时候都应该禁止使用 select
    ,要用到什么字段就写什么字段
  3. 少使用子查询
    子查询会建立临时表,尽量使用JOIN来代替

唠了这么多,都是sql的优化,当然还有硬优化

  1. 硬件优化:内存、CPU、磁盘
  2. 架构优化:主从架构,做读写分离
  3. 分表分库:减轻单表单库的压力