文章目录

  • SQL规范性检查
  • select检查
  • from检查
  • where检查
  • join检查
  • group by检查
  • order by检查
  • limit检查
  • 表结构检查
  • 索引检查


SQL规范性检查

select检查

  • UDF用户自定义函数
    SQL语句的select后面使用了自定义函数UDF，SQL返回多少行，那么UDF函数就会被调用多少次，影响性能。
  • text类型检查
    如果select出现text类型的字段，就会消耗大量的网络和IO宽带，如果返回的内容过大超过max_allowed_packet设置就会导致程序报错，看情况使用。
  • group_concat
    group_concat是一个字符串聚合函数，影响SQL的响应时间，和text字段一样，返回的值过大的话，程序报错。
  • 内联子查询
    如果select语句后面有子查询的情况的话称为内联子查询，SQL返回多少行，子查询就需要执行多少次，影响性能
  • select指明字段名称
    SELECT * 增加了cpu，io，内存，网络宽带等消耗，杜绝了使用覆盖索引使用的可能性。覆盖索引即只需要通过索引就可以拿到所需的DATA，不需要再次会标查询，所以效率很高。

from检查

  • 表的链接方式
    在Mysql中不建议使用Left Join，即使ON过滤条件列索引，一些情况也不会走索引，导致大量数据被扫描，SQL性能变差。
  • 子查询
    由于Mysql的优化器CBO对子查询的处理能力较弱，不建议使用子查询，可以改写成Ineer Join

where检查

  • 索引列被运算
  • 当一个字段被索引，同时出现where条件后面，是不能进行任何运算，会导致索引失效
  • 类型转换
    对于Int类型的字段，传varchar类型的值是可以走索引，Mysql内部做了隐式类型转换，尽量避免隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应类型的值。
    比如：select id，name，sex where id = ‘123456’（id是bigInt类型，传入varchar可以走索引）
    select id，name，sex where id = 123456（id是varchar类型，传入Int值是无法走索引的）
  • 列字符集
    从MySQL 5.6开始建议所有对象字符集应该使用utf8mb4，包括Mysql实例字符集，数据库字符集，列字符集，表字符集。避免在关联查询Join时字段字符集不匹配导致索引失效。而且只有utf8mb4支持emoji表情存储。
  • in条件
    SQL语句中In包含的值不应过多，对于连续的值能用between就别用in，再或者使用连接来替换。
  • 区分In 和 EXISTS
    语句：select * from A where id in （select id from B）相当于
    select * from A where exists （select * from B where B.id = A.id）
    如果是exists，那么以外层表为驱动先被访问，如果是In那么先执行子查询。所以In适合于外表大内表小的情况；exists适用于外表小而内表大的情况。
  • or条件
    or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成查询不走索引的情况，使用union all或者union的方式代替or会更好
  • 尽量用union all代替union
    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，会增加cpu运算，当然，union all的前提是两个结果集没有重复数据。
  • 避免对字段进行null值判断
    这会导致引擎放弃使用索引而进行全表扫描
  • 不建议使用%前缀模糊查询
    例如LIKE "%name"或者LIKE “%name%”，会导致索引失效而全表查询，但是LIKE "name%"可以使用索引。如果非要使用%的前缀查询则可以使用全文索引。MySQL5.6之后的InnoDB和MyISAM均支持全文索引，但是查询精度和拓展性不高，推荐Elasticsearch。
    语句：ALTER TABLE table_name ADD FULLTEXT INDEX idx_user_name (user_name) 创建全文索引
    select id，user_name from table_name where match(user_name) against (zhangsan in boolean mode)；
  • 避免where子句中对字段进行表达式操作
    会导致引擎放弃使用索引。
  • where条件中使用范围查询时，索引最多用于一个范围条件，超过一个则后边的不走索引
  • <>优化
    一般无法使用索引，建议使用union

join检查

  • 尽量使用inner join 避免left join
    inner join会自动选择两张表中较小的表作为驱动表，而left join遵循的是左边驱动右边的原则。right join则是右边为驱动表。
  • 利用小表驱动大表
  • 被驱动的表的索引字段作为on的限制字段
  • JOIN的表不允许超过五个，需要JOIN的字段，数据类型必须绝对一致，被关联的字段需要索引

group by检查

  • 前缀索引
    group by后面的列有索引，索引可以消除排序带来的CPU开销，但是前缀索引，是不能消除排序的。
    语句：alter table user add index idx_user_id(user_id(64))（user_id字段类型为varchar（200），创建前缀索引）
  • 函数运算
    假如需要统计某月每天的新增用户量，如下的SQL语句，虽然可以走create_time的索引，但是不能消除排序，可以考虑冗余一个date类型的字段来解决。
    语句：select DATE_FORMAT(create_time, ‘%Y-%m-%d’)，count(*) from user where create_time between 时间 group by DATE_FORMAT(create_time, ‘%Y-%m-%d’)；

order by检查

  • 前缀索引
    order by后面的列有索引，可以消除排序带来的CPU开销，但是如果是前缀索引，是不能消除排序的。
  • 字段顺序
    排序字段的顺序，asc/desc升降要保持和索引一致，利用索引的有序性消除排序带来的CPU开销
  • 排序字段没有使用到索引，就尽量少排序
  • 不使用ORDER BY RAND()

limit检查

  • limit m，n
    越往后翻页SQL消耗时间越长，应该先取出主键ID跟原表进行jion关联查询。
  • 只需要一条数据的数据，使用limit 1
    为了使EXPLAIN中的type列达到const类型

表结构检查

  • 表，列名关键字
    表名和字段名不能使用MySQL关键字，比如 where desc order，group等。
    设置lower_case_table_names = 1 表明不区分大小写。
  • 表存储引擎
    OLTP业务系统，建议使用InnoDB引擎获取更好性能，可以通过参数default_storage_engine控制。
    InnoDB支持事务、行级锁、并发性能更好，CPU以及内存缓存页优化是的资源利用率更高。
  • AUTO_INCREMENT属性
    建表主键ID带有AUTO_INCREMENT属性，单调递增可以有效提高插入的性能，避免过多的页分裂，减少表碎片提高空间的使用率，而且 AUTO_INCREMENT=1，在InnoDB内部是通过一个系统全局变量dict_sys.row_id来计数的，row_id是一个8字节的bigint unsigned，InnoDB在设计时只给row_id保留了6字节的长度，取值范围为0到2^48-1，如果id的值到达最大值，下一个从0开始继续循环递增，且代码中禁止指定主键ID值插入。
    语句：create table user（id bigint（20）NOT NULL AUTO_INCREMENT COMMENT 主键id，~~~~）engine = InnoDB auto_increment = 0；
  • NOT NULL 属性
    尽量字段加上NOT NULL属性，存储大量NULL，影响索引稳定性。
  • DEFAULT属性
    建议每个字段尽量都有默认值，禁止DEFAULT NULL，而是对字段类型填充相应的默认值。
  • COMMENT属性
    备注要明确字段作用，尤其是表示状态的字段，写出所有可能的状态值及含义。
  • TEXT类型
    不建议使用Text数据类型，表上的DML操作会变慢。建议使用es或者对象存储OSS来存储和检索。
  • 使用utf8mb4字符集
  • 库名、表名、字段名均小写，下划线命名风格，不超过32个字符
  • 单表列必须小于30，若超过则考虑分表
  • 禁止使用外键、级联
    如果有外键完整性约束，需要应用程序控制。外键会导致表之间耦合，UPDATE和DELETE操作都会涉及相关联的表，影响SQL的性能，甚至会死锁。级联更新是强阻塞，存在数据库更新风暴的风险。
  • 如果存储的字符串长度几乎相等，使用CHAR定长字符串类型
  • 在一些场景下，使用TIMESTAMP代替DATETIEM
    都可以表达“yyyy-MM-dd HH:mm:ss”的格式。TIMESTAMP只需要占用4个字节的长度，可以存储的范围为（1970-2038），在各个时区，展示的时间不一样。而DATETIEM类型占用8个字节，对时区不敏感，可以存储的范围为（1001-9999）。
  • 长度满足，整形尽量使用tinyint、smallint、medium_int而非int，字符串同理。
  • 精确度要求较高的使用decimal类型，float会失准。

索引检查

  • 索引属性
    索引基数指的是被索引的列唯一值的个数，唯一值越多接近表的 count (*) 说明索引的选择率越高，通过索引扫描的行数就越少，性能就越高，例如主键 id 的选择率是 100%，在 MySQL 中尽量所有的 update 都使用主键 id 去更新，因为 id 是聚集索引存储着整行数据，不需要回表，性能是最高的。
mysql> select count() from member_info;
 ±---------+
 | count() |
 ±---------+
 | 148416 |
 ±---------+
 1 row in set (0.35 sec)mysql> show index from member_base_info;
 ±-----------------±-----------±---------------------------±-------------±------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 ±-----------------±-----------±---------------------------±-------------±------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
 | member_info | 0 | PRIMARY | 1 | id | A | 131088 | NULL | NULL | | BTREE | | |
 | member_info | 0 | uk_member_id | 1 | member_id | A | 131824 | NULL | NULL | | BTREE | | |
 | member_info | 1 | idx_create_time | 1 | create_time | A | 6770 | NULL | NULL | | BTREE | | |
 ±-----------------±-----------±---------------------------±-------------±------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+


#Table：表名
#Non_unique ：是否为unique index，0-是，1-否。
#Key_name：索引名称
#Seq_in_index：索引中的顺序号，单列索引-都是1；复合索引-根据索引列的顺序从1开始递增。
#Column_name：索引的列名
#Collation：排序顺序，如果没有指定asc/desc，默认都是升序ASC。
#Cardinality：索引基数-索引列唯一值的个数。
#sub_part：前缀索引的长度；例如index (member_name(10)，长度就是10。
#Packed：索引的组织方式，默认是NULL。
#Null：YES:索引列包含Null值；’’:索引不包含Null值。
#Index_type：默认是BTREE，其他的值FULLTEXT，HASH，RTREE。
#Comment：在索引列中没有被描述的信息，例如索引被禁用。
#Index_comment：创建索引时的备注。

  • 前缀索引

对于变长字符串类型 varchar (m)，为了减少 key_len，可以考虑创建前缀索引，但是前缀索引不能消除 group by， order by 带来排序开销。如果字段的实际最大值比 m 小很多，建议缩小字段长度。

语句：alter table member_info add index idx_member_name_part(member_name(10));

  • 复合索引顺序

有很多人喜欢在创建复合索引的时候，总以为前导列一定是唯一值多的列，例如索引 index idx_create_time_status (create_time, status)，这个索引往往是无法命中，因为扫描的 IO 次数太多，总体的 cost 的比全表扫描还大，CBO 最终的选择是走 full table scan。

MySQL 遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引列，到遇到第一个范围查询（>=, >,<, <=, between …… and ….）就停止扫描，索引正确的索引顺序应该是 index idx_status_create_time (status, create_time)。

语句：select account_no, balance from accounts where status = 1 and create_time between ‘2020-09-01 00:00:00’ and ‘2020-09-30 23:59:59’;

  • 时间列索引

对于默认字段 created_at (create_time)、updated_at (update_time) 这种默认就应该创建索引。

  • 必要时使用 force index来强制查询走某个索引
  • 避免在更新比较频繁、区分度不高的列上单独建立索引
    区分度不高的列单独创建索引的优化效果很小，频繁更新的会让索引维护成本更高。
  • 字段唯一性太低，增加索引没有意义，如：是否删除，性别
  • 单表索引不超过五个，单个索引字段不超过五个