Mysql优化之如何写一手好SQL?

十年 2020-07-07 AM 346℃ 0条
  • 数据表设计

    • 数据类型(数据类型的选择原则:更简单或者占用空间更小。)

      • 如果长度能够满足,整型尽量使用tinyint、smallint、medium_int而非int
      • 如果字符串长度确定,采用char类型。
      • 如果varchar能够满足,不采用text类型。
      • 精度要求较高的使用decimal类型,也可以使用BIGINT,比如精确两位小数就乘以100后保存。
      • 尽量采用timestamp而非datetime。(相比datetime,timestamp占用更少的空间,以UTC的格式储存自动转换时区。)
    • 避免空值

      • MySQL中字段为NULL时依然占用空间,会使索引、索引统计更加复杂。从NULL值更新到非NULL无法做到原地更新,容易发生索引分裂影响性能。尽可能将NULL值用有意义的值代替,也能避免SQL语句里面包含is not null的判断。
    • text类型优化

      • 由于text字段储存大量数据,表容量会很早涨上去,影响其他字段的查询性能。建议抽取出来放在子表里,用业务主键关联。
  • 索引优化

    • 索引分类

      • 普通索引:最基本的索引。
      • 组合索引:多个字段上建立的索引,能够加速复合查询条件的检索。
      • 唯一索引:与普通索引类似,但索引列的值必须唯一,允许有空值。
      • 组合唯一索引:列值的组合必须唯一。
      • 主键索引:特殊的唯一索引,用于唯一标识数据表中的某一条记录,不允许有空值,一般用primary key约束。
      • 全文索引:用于海量文本的查询,MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳,更多的企业选择Elasticsearch。
    • 索引优化

      • 分页查询很重要,如果查询数据量超过30%,MYSQL不会使用索引。
      • 单表索引数不超过5个、单个索引字段数不超过5个。
      • 字符串可使用前缀索引,前缀长度控制在5-8个字符。
      • 字段唯一性太低,增加索引没有意义,如:是否删除、性别。
      • 合理使用覆盖索引,如下所示:

        • select login_name, nick_name from member where login_name = ?;(login_name, nick_name两个字段建立组合索引,比login_name简单索引要更快。)
  • SQL优化

    • 操作符<>优化

      • 通常<>操作符无法使用索引,举例如下,查询金额不为100元的订单:

        • select id from orders where amount != 100;
      • 如果金额为100的订单极少,这种数据分布严重不均的情况下,有可能使用索引。鉴于这种不确定性,采用union聚合搜索结果,改写方法如下:

        • (select id from orders where amount > 100) union all (select id from orders where amount < 100 and amount > 0);
    • OR优化

      • 在Innodb引擎下or无法使用组合索引,比如:

        • select id,product_name from orders where mobile_no = '13421800407' or user_id = 100;
      • OR无法命中mobile_no + user_id的组合索引,可采用union,如下所示:

        • (select id,product_name from orders where mobile_no = '13421800407') union (select id,product_name from orders where user_id = 100);(此时id和product_name字段都有索引,查询才最高效。)。
    • IN优化

      • IN适合主表大子表小,EXIST适合主表小子表大。由于查询优化器的不断升级,很多场景这两者性能差不多一样了。
      • 尝试改为join查询,举例如下:

        • select id from orders where user_id in (select id from user where level = 'VIP');
      • 采用JOIN如下所示:

        • select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
    • 不做列运算

      • 通常在查询条件列运算会导致索引失效,如下所示(查询当日订单表):

        • select id from order where date_format(create_time,'%Y-%m-%d') = '2019-07-01';
      • date_format函数会导致这个查询无法使用索引,改写后:

        • select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';
    • 避免Select all

      • 如果不查询表中所有的列,避免使用SELECT *,它会进行全表扫描,不能有效利用索引。
    • Like优化

      • like用于模糊查询,举个例子(field已建立索引):

        • SELECT column FROM table WHERE field like '%keyword%';
      • 这个查询未命中索引,换成下面的写法:

        • SELECT column FROM table WHERE field like 'keyword%';(去除了前面的%查询将会命中索引,但是产品经理一定要前后模糊匹配呢?全文索引fulltext可以尝试一下,但Elasticsearch才是终极武器。)。
    • Join优化

      • join的实现是采用Nested Loop Join算法,就是通过驱动表的结果集作为基础数据,通过该结数据作为过滤条件到下一个表中循环查询数据,然后合并结果。如果有多个join,则将前面的结果集作为循环数据,再次到后一个表中查询数据。
      • 驱动表和被驱动表尽可能增加查询条件,满足ON的条件而少用Where,用小结果集驱动大结果集。
      • 被驱动表的join字段上加上索引,无法建立索引的时候,设置足够的Join Buffer Size。
      • 禁止join连接三个以上的表,尝试增加冗余字段。
    • Limit优化

      • limit用于分页查询时越往后翻性能越差,解决的原则:缩小扫描范围,如下所示:

        • select * from orders order by id desc limit 100000,10;,耗时0.4秒。
        • select * from orders order by id desc limit 1000000,10;,耗时5.2秒。
      • 先筛选出ID缩小查询范围,写法如下:

        • select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10;,耗时0.5秒。
      • 如果查询条件仅有主键ID,写法如下:

        • select id from orders where id between 1000000 and 1000010 order by id desc;,耗时0.3秒。
    • 如果以上方案依然很慢呢?只好用游标了,感兴趣的朋友阅读JDBC使用游标实现分页查询的方法

  • 其他数据库

    • (键值型) Memcache 用于内容缓存,大量数据的高访问负载。

    • (键值型) Redis 用于内容缓存,比Memcache支持更多的数据类型,并能持久化存储数据。

    • (列式存储) HBase Hapdoop体系的核心数据库,海量结构化数据存储,大数据必备。

    • (文档型) MongDb 知名型文档数据库,也可以用于数据缓存。

    • (文档型) CouchDB Apache的开源项目,专注于易用性,支持REST API。

    • (图型) Neo4J 用于社交网络构建关系图谱,推荐系统等。

标签: Mysql

本博文章为转载文章。

评论啦~