MySql优化指南

2020-04-23 20:41:49  卢浮宫  阅读量:1720  版权声明:本文为站长原创文章,转载请写明出处


一、背景

    之前一致想写一个关于mysql优化的文章,由于各种原因一致被搁浅了。趁着最近做sql改造的时机就把这个坑给填了吧。

二、优化思路,应该从何处入手

    1、数据量处理(你的操作数据已经很庞大了吗)

    2、数据库字段定义(你定义的字段是最优的吗)

    3、索引(你定义了那些索引,你的sql使用到索引了吗)

    4、sql语句优化(你的sql有雷区吗)

三、针对上述的四点我们做一一描述

    1、数据量处理

        这里引用阿里巴巴开发手册中的一段描述:

            14. 【推荐】单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。

            说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。
        这里对分库分表做下简单描述(因为我也是没有操作过呀)

        ①为什么要分

            随着业务量的增加,单表,单库的数据会越来越庞大的。相应的数据的增删改查的开销也会越来越大。            

 分库分表前 分库分表后 
 并发情况 没有分布式部署,单机性能支持不了高并发 数据库集群,并发及数据吞吐上限提升
 硬件设备 一台设备总会有瓶颈,磁盘等负荷越来越大 压力分摊
 数据库性能 随着数据量的增加,sql越来越慢 单表数量少,压力分摊,性能提升


        ②怎么分

            垂直拆分

                .例如根据不同的业务进行拆分的,拆分成不同的数据库,比如会员数据库、订单数据库、支付数据库、消息数据库等,垂直拆分在大型电商项目中使用比较常见。

            水平拆分

                .把同一张表中的数据拆分到不同的数据库中进行存储、或者把一张表拆分成 n 多张小表。

        ③会引发问题

            事务问题

            跨库跨表数据关联查询问题

            数据同步问题

                ...

    2、数据表字段定义

        ①在字段定义时遵循一个原则,尽量简单、占用空间最小。如:

            如果长度能够满足,整型尽量使用tinyint、smallint、medium_int而非int。

            如果字符串长度确定,采用char类型。
            如果varchar能够满足,不采用text类型。
            精度要求较高的使用decimal类型,也可以使用BIGINT,比如精确两位小数就乘以100后保存。
            尽量采用timestamp而非datetime。

        ②尽量避免null值,text字段能容纳大量数据,但会印象到其他字段的查询性能(可以抽取到子表中)

    3、索引

        ①索引类型

            普通索引:最基本的索引。

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

        ②那些情况下索引会失效

            .条件中有 or

            .对于多列索引,不是使用的第一部分(第一个),则不会使用索引

            .like查询是以%开头

            .如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

            .查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)

            .not in ,not exist.

    4、sql优化

        ① <>的优化

            <>无法使用索引,建议使用 != 或者 union all 关联

        ② or的优化

            or无法使用索引,同样建议使用 union all 关联

        ③ in的优化

           IN适合主表大子表小,EXIST适合主表小子表大。 可以使用 join 关联查询

        ④ 不做列运算

        ⑤不做 select * 

        ⑥ like 优化

            避免使用 %开头的模糊查询,或者直接使用全文索引

        ⑦join的优化

            join查询是将前面的结果及作为循环数据,然后再后面的join中查询数据的。

            尽量多匹配 on 的条件,少使用where 约束

        ⑧limit的优化

            limit 分页越往后性能越差。

            可以缩小其查询的范围

            select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10;

四、本文章部分来源于网络,不正之处敬请斧正。同时也会持续更新完善[坏笑]

            

        

    

最新评论: