建表规约
1、【强制】每张表必须设置一个主键ID,并且这个主键ID要自增(在满足需要的情况下尽量短),除非是分库分表
理解:由于InnoDB存储引擎决定了需要有一个主键,而且这个主键ID是自增的话可以有效提高插入的性能,避免过多的页分裂,减少表碎片提高空间的利用率。
但是在分库分表下,会有分片规则,这个时候需要统一分配各个表中的主键值,从而避免整个逻辑表中主键重复,一般我们会使用雪花ID来实现。
2、【强制】必须使用utf8mb4字符集
理解:在MySQL中的UTF-8并非是真正的"UTF-8",而utf8mb4才是真正的"UTF-8"。
3、【强制】数据库表、表字段必须加入中文注释
理解:程序员都不要给我懒,这样可以让后来者更好的上手和理解。
4、【强制】库名、表名、字段名必须小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用
理解:在linux环境下MySQL是区分大小写的,而在windows是不区分大小写的,所以统一使用小写+下划线可以避免不必要的混淆。
5、【强制】单表列数目必须小于30,若超过则应该考虑将表拆分
理解:单表列数太多会使得MySQL处理InnoDB返回数据之间的映射成本太高。
6、【强制】禁止使用外键,如果有外键完整性约束,需要应用程序控制
理解:外键会导致表与表之间耦合,UPDATE与DELETE操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。
7、【强制】必须把字段定义为NOT NULL并且提供默认值
理解:NULL需要更多的存储空,无论是表还是索引中每行中的NULL的列都需要额外的空间来标识。NULL这种类型需要MySQL内部进行特殊处理,增加了数据库处理记录的复杂性,同等条件下,表中较多NULL字段会导致数据库处理性能下降。
8、【强制】禁止使用保留字,如DESC、RANGE、MAX等
理解:请参考MySQL官方保留字。
9、【强制】如果存储的字符串长度几乎相等,使用CHAR定长字符串类型
理解:能够减少空间碎片,节省存储空间。
10、【强制】小数类型为 decimal,禁止使用 float 和 double
理解:float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。
11、【强制】表必备三字段:id, gmt_create, gmt_modified。
理解:其中 id 必为主键,类型为 bigint unsigned、单表时自增、步长为 1。gmt_create,gmt_modified 的类型均为 datetime 类型,前者现在时表示主动创建,后者过去分词表示被动更新。
12、【推荐】在一些场景下,考虑使用TIMESTAMP代替DATETIME
理解:TIMESTAMP可以表达1970-2038年,而且TIMESTAMP需要4字节存储空间,而DATETIME需要8字节,存储1001-9999年
13、【推荐】对于自动生成的schema不要太过信任,最好自己手动写
理解:对于一些数据库客户端不要过分信任。
索引规约
1、【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引
理解:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
2、【强制】超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引
理解:多表的join操作会影响SQL的性能
3、【强制】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可
理解:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
4、【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决
理解:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
5、【推荐】如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能
理解:组合索引的顺序会影响到SQL的查询性能
6、【推荐】利用覆盖索引来进行查询操作,避免回表
理解:覆盖索引只需要通过索引即可拿到所需的数据,而不再需要再次回表查询,提高了SQL的查询效率。
7、【推荐】利用延迟关联或者子查询优化超多分页场景
理解:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。
8、【推荐】建组合索引的时候,区分度最高的在最左边
理解:区分度最高的放左边,能够在一开始过滤掉很多无用数据,提高索引的效率。需要注意的是各个条件的顺序尽量和索引的顺序一致。
9、【推荐】防止因字段类型不同造成的隐式转换,导致索引失效
SQL规约
1、【强制】不要使用 count(列名)或 count(常量)来替代 count(*),count(*)是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关
理解:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。
2、【强制】使用 ISNULL()来判断是否为 NULL 值
理解:NULL与任何值直接比较都为NULL。
- NULLNULL返回结果是NULL,而不是false。
- NULL=NULL返回结果是NULL,而不是false。
- NULL1返回结果是NULL,而不是true。
3、【强制】在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句
理解:提高SQL的效率,避免不必要的无用查询。
4、【强制】不得使用外键与级联,一切外键概念必须在应用层解决
理解:外键会导致表与表之间耦合,UPDATE与DELETE操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。
5、【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性
理解:避免不必要的维护。
6、【推荐】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。
理解:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
阿里开发手册mysql_MySQL 开发准则
命名规范
【强制】对象名称必须用小写或者小写、下划线、数字组成。
name;user_name
【强制】对象名称禁止使用 MySQL 保留关键字。
如 ORDER 等
【强制】对象名称要见名知其意,不要超过 32 个字符。
nick_name,student_age,create_time
【强制】临时表要以 tmp 为前缀,日期为后缀。
tmp_export_user_20200721
【强制】备份表要以 bak 为前缀,日期为后缀。
bak_user_20200721
【强制】表名不使用负数名词。
【强制】表字段表示是否概念,用 is_xxx 表示。
is_default
【强制】索引名称,用特定_字段表示。
普通索引:idx_xxx
联合索引:un_xxx_xxx
唯一索引:uk_xxx
表设计规范
【强制】如无特殊需求,存储引擎使用 InnoDB。支持事务
行级锁
并发性能好
【强制】数据库和表的字符集统一使用 utf8 或者 utf8mb4。
不同字符集转化可能会产生乱码。
不同字符集比较前会进行字符转换,索引失效。
UTF8 每个字符占用3字节,占用空间小,但是不能存储 emoj,emoj 占用4字节。
UTF8MB4 每个字符占用4字节,是真正的 UTF8,推荐使用。
【强制】数据库表和字段都需要添加备注,更好理解建表思路。
【建议】数据库表考虑分库分表细节,推荐使用snowflake作为ID主键。
单表设计存储数据少于 500 万条或单表容量超过 2G。
不建议使用分区表,容易造成全表死锁,跨分区查询效率低。
【强制】表每一行中的每列数据大小相加不能大于 65535 byte。
【强制】不要设置预留字段,更改会锁表。
【强制】不要保存文件等大的二进制数据。应放到文件服务器中。
【建议】InnoDB 字符集默认排序使用 _general_ci 和 _unicode_ci,推荐使用 _general_ci。
ci不区分大小写
cs区分大小写
general速度更快,准确性稍低
【建议】表必备三个字段,id、create_time、modify_time。
id:unsigned bigint。单表时主键单表时自增1,需要分表使用snowflake。
create_time:datatime。创建时间。
modify_time:datatime。修改数据更新时间。
【强制】存储过程设计要合理,尽量少用。
过度复制逻辑容易死锁。
可以替换为在后端业务层或者脚本实现。
【强制】不要使用触发器。
高并发情况下不理想。
可以用事务替代。
【强制】数据量大的表要使用pt工具修改表结构。
pt-online-schema-change。
原理是新建一张表并复制原表结构与数据,最终删除原表,可以有效避免行锁及表锁。
字段设计规范
【强制】表字段表示是否概念,即is_xxx。
1表示是,0表示否
使用 unsigned tinyint
【强制】小数类型都使用decimal型。
decimal精确。
如果超出decimal范围建议分两个字段存储。
【强制】ip及手机号类固定长度字段,要用char。
【强制】选择合适的存储长度。
可以减少表存储空间。
可以减少索引长度,增加索引效率。
【建议】避免使用TEXT、BLOB数据类型。
内存临时表不支持TEXT和BLOB。会使用磁盘临时表,降低查询速度。
TEXT和BLOB需要单独成表,提高查询效率。
【建议】避免使用ENUM数据类型。
枚举类型order by效率低。
禁止使用数字作为枚举值。
在与php使用上1和’1’差别大,PHP是弱引用很容易把’1’写为1,1为key,’1’为内容。
【建议】尽可能把列定义设置为 NOT NULL。
索引NULL列,会额外增加开销,占用更多表空间。
要做计算或者比较时,会对 NULL 做特别处理。
在 SQL 中对 NULL 进行判断会全表扫描。
【强制】时间类型不要使用 varchar 或 int 等。
使用 timestamp,占用4字节与int相同,查询计算比int快。
timestamp 取值范围,1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
使用 datatime,占用8字节,明确日期时间,超出timestamp用datatime。
索引设计规范
【强制】不要使用外键和级联,应放在应用层去做。
外键与级联更适合单机及低并发,不适合分布式和高并发集群。
外键即外键约束,会影响写操作(插入速度),降低性能。
级联更新是强阻塞,存在更新风暴的风险。
【强制】一张表不要超过5个索引。
索引过多会降低性能。
合理分配索引会提高性能。
【强制】联合索引的左侧原则可以减少每个字段单独建立索引。
避免每个字段都建立索引。
联合索引区分度高的放在最左边。
联合索引如果存在非等号和等号混合时,把等号的索引放在最左边。
联合索引左侧原则,一定要注意顺序。
【强制】InnoDB 必须有主键。
结合建表四个必要字段,id作为主键。
InnoDB 属于索引组织表,逻辑顺序和索引的顺序相同。
单表时主键自增1。
预计三年内达到500万条,需要使用 snowflake 等分布式id生成主键。
不要使用 uuid、hash、md5 等作为主键,要有顺序概念。
【建议】查询想走特定索引时可以用force index。
MySQL的 optimizer 会执行它认为最优索引,但是往往不是我们需要或者最优的。
使用 force index 可以强制使用索引,结合 explain 使用,确认为最优。
【强制】有唯一索引需求,该字段就应设置唯一索引。
即使该字段是在联合索引内,也要单独设置唯一索引。
唯一索引对insert速度影响可以忽略,但是提高查询速度和唯一性是明显的。
应用层也建议做校验控制,但是根据墨菲定律,只要有可能就会出现脏数据。
【强制】varchar型设置索引要设置索引长度。
不设置默认是全部长度。
建议索引长度为20,区分度可以达到90%。
区分度计算公式:select count(distinct left(列名, 索引长度))/count(*) FROM 表名。可以查出区分度百分比。
【强制】模糊查询最好用搜索引擎。
禁止使用like %str和like %str%。因为不走索引。
可以使用like str%。走索引。
也可以走全文索引,但是需要看配置,还是推荐搜索引擎。
【强制】order by 需要注意索引的有序性。
order by后接索引应该索引的一部分,如果是联合索引,应该是联合索引的最后,避免出现file_sort,影响查询性能。where a=? and b=? order by c那么索引是(a,b,c)。
file_sort出现是没有走索引或者联合索引。出现情况:where a=? order by b索引是a。改进优化:where a=? order by b索引是(a,b)。
【强制】避免冗余索引。
重复索引:primary key(id)、index(id)、unique index(id)。
冗余索引:key(a,b,c)、key(a,b)、key(a)。
【强制】查询频率较高的sql语句,应该使用覆盖索引。
覆盖索引不是真正的索引,是一种使用索引方式。
原理是从索引中查询出想要内容,而不用回表查询,提高查询效率。
表现是explain的extra为Using index。
例如select user_no from user order user_age = 28索引为user_no时效率低,索引为(user_no,user_age)时为覆盖索引,查询效率高。
【强制】避免隐式类型转换。
定义和使用不同数据会造成隐式转换。
隐式转换会不走索引,降低查询效率。
如select user_age from user where user_no='111'
【强制】避免在字段位置写表达式,不走索引。
反例:select user_no from user where user_age*2 = 36。
正例:select user_no from user where user_age = 36/2。
查询优化
【强制】SQL性能优化目标,由高到低。
const。基本是只有一行匹配。
ref。基本是走普通索引。
range。基本是走范围索引。
index。走索引最差,和全表查询相似。
NULL。不走索引,全表查询。
【强制】不适用索引的几种情况。
不等式:!=、<>。
null判断:is null、is not null。
like模糊查询:like %a、like %a%
not in。
【建议】避免使用IN操作,如果避免不了,需小于1000条。
多表查询IN会影响查询效率。
可以用between替代。
IN(select * from)索引会失效,可以使用join(left、right、inner、full)来实现。
【建议】join优化。
最好在三张表之内,最多不要超过5个,理论可以61个。
on关联字段类型要相同。
每关联一个表就会多分配一个关联缓存,和join_buffer_size设置相关。占用内存过大会形成溢出,影响性能和稳定性。
left join的驱动表是左侧表。
inner join的驱动表是数据少的表。
right join的驱动表是右侧表。
MySQL没有full join,可以用SQL实现。例如:select * from A left join B on B.name = A.name where B.name is null union all select * from B。
尽量利用小表驱动大表,可以减少循环嵌套次数。
straight join的使用。前提是inner join内连接。inner join优先查询小表,但有group by、order by等file_sort,Using temporary时会想改变优先查询表顺序,这时可以使用straight join。straight join强制优先查询表为左侧表。
一定要是内连接才能使用straight join,否则数据可能不准确。
【强制】禁止select * 出现。
select * 增加额外解析成本。
增减字段对前端映射不一致。
无用字段增加网络消耗。
无法使用覆盖索引。
【强制】禁止使用不带字段的insert出现。
正例:insert into user(user_no,user_age) values (123,18)
反例:insert into user values (123,18)
【强制】尽量避免子查询。
子查询一般在IN中。
子查询会创建临时表,不会存在索引。
结果集大的子查询,性能越差。
可以使用join替代。
【强制】查询一条或者是否有数据时,要使用limit 1。
索引效率最高。
explain的type为const。
【建议】order by字段没有索引就不要排序。
order by字段有索引会按索引排序。没有索引影响效率。
可以设置索引,或者覆盖索引。
【建议】尽量不使用or。
同一字段用IN、between等替代or,因为很多情况不会走索引。
多字段下or两边都需要是索引且其他条件也是索引,才会走索引。
最好使用union、union all来替换。
【建议】尽量用union all替代union。
union会集合后进行唯一性去重,涉及到排序,加大资源开销。
在没有重复数据情况强制使用union all。
【建议】拆分大且复杂的SQL。
一条SQL只会使用一个CPU。
拆成多个小SQL可以通过并行提高查询效率。
【强制】禁止使用ORDER BY RAND()
随机排序性能差。
可以用其他SQL替换。
原:select id from 'dynamic' order by rand() limit 1;,
新:select id from 'dynamic' t1 join (select rand() * (select max(id) from 'dynamic') as nid) t2 on t1.id > t2.nid limit 1;注意,此查询只能随机一条id,并连续查询该id的顺序条数,具体情况具体分析,适用随机取一条,不应用随机取多条。
随机取多条解决方案:先查询所有id->在后端业务层做随机id->IN该id组。
rand()取值范围:[ 0 , 1 )。
【强制】禁止对where条件字段进行函数转换。
不走索引。
正例:select user_age from user where create_time>'20190320'
反例:select user_age from user where date(create_time)>'20190320'
【建议】in、exists、not in、not exists。
in是子查询,优先查询驱动表为内表,所以适合内表数据小的情况。
exists优先查询驱动表为外表,适合外表数据小的情况。
不建议使用not in和not exists,不走索引且容易混淆。
建议用其他SQL替代。
反例:select a.user_age from user a where a.user_no not in (select b.user_no from user_info b)。
正例:select a.user_age from user a left user_info b on a.user_no = b.user_no where b.user_no is null。
【建议】offset偏移量、分页。
分页数据量大的情况会影响查询效率,因为不是跳过offset行,而是查询offset+N行,然后抛弃offset行。
优化举例1:select user_age from user where user_no > 13333 limit 20。
优化举例2:select a.user_age from user a,(select user_no from user limit 13333,20)b where a.user_no = b.user_no。
【强制】范围查询注意。
between、>、
【强制】count()相关。
统计行数要使用count(*),不要使用count(列名)。
count(*)会统计NULL数据,count(列名)不会统计NULL数据。
当某一列的值全为NULL时,count(列名)返回的结果为0,但sum(列名)结果为NULL,因此使用sum(列名)需要使用IFNULL判断。
例如:select if(ifnull(sum(user_name)),0,sum(user_name))user_age from user
转载于:https://blog.csdn.net/weixin_42098104/article/details/113634100
阿里开发手册mysql_MySQL 开发准则(总结自阿里巴巴开发手册)
你就应该
于 2021-02-02 17:11:48 发布
556 收藏 1文章标签: 阿里开发手册mysql版权命名规范
【强制】对象名称必须用小写或者小写、下划线、数字组成。
name;user_name;
【强制】对象名称禁止使用 MySQL 保留关键字。
如 ORDER 等
【强制】对象名称要见名知其意,不要超过 32 个字符。
nick_name,student_age,create_time
【强制】临时表要以 tmp 为前缀,日期为后缀。
tmp_export_user_20200721
【强制】备份表要以 bak 为前缀,日期为后缀。
bak_user_20200721
【强制】表名不使用负数名词。
【强制】表字段表示是否概念,用 is_xxx 表示。
is_default
【强制】索引名称,用特定_字段表示。
普通索引:idx_xxx
联合索引:un_xxx_xxx
唯一索引:uk_xxx
表设计规范
【强制】如无特殊需求,存储引擎使用 InnoDB。支持事务
行级锁
并发性能好
【强制】数据库和表的字符集统一使用 utf8 或者 utf8mb4。
不同字符集转化可能会产生乱码。
不同字符集比较前会进行字符转换,索引失效。
UTF8 每个字符占用3字节,占用空间小,但是不能存储 emoj,emoj 占用4字节。
UTF8MB4 每个字符占用4字节,是真正的 UTF8,推荐使用。
【强制】数据库表和字段都需要添加备注,更好理解建表思路。
【建议】数据库表考虑分库分表细节,推荐使用snowflake作为ID主键。
单表设计存储数据少于 500 万条或单表容量超过 2G。
不建议使用分区表,容易造成全表死锁,跨分区查询效率低。
【强制】表每一行中的每列数据大小相加不能大于 65535 byte。
【强制】不要设置预留字段,更改会锁表。
【强制】不要保存文件等大的二进制数据。应放到文件服务器中。
【建议】InnoDB 字符集默认排序使用 _general_ci 和 _unicode_ci,推荐使用 _general_ci。
ci不区分大小写
cs区分大小写
general速度更快,准确性稍低
【建议】表必备三个字段,id、create_time、modify_time。
id:unsigned bigint。单表时主键单表时自增1,需要分表使用snowflake。
create_time:datatime。创建时间。
modify_time:datatime。修改数据更新时间。
【强制】存储过程设计要合理,尽量少用。
过度复制逻辑容易死锁。
可以替换为在后端业务层或者脚本实现。
【强制】不要使用触发器。
高并发情况下不理想。
可以用事务替代。
【强制】数据量大的表要使用pt工具修改表结构。
pt-online-schema-change。
原理是新建一张表并复制原表结构与数据,最终删除原表,可以有效避免行锁及表锁。
字段设计规范
【强制】表字段表示是否概念,即is_xxx。
1表示是,0表示否
使用 unsigned tinyint
【强制】小数类型都使用decimal型。
decimal精确。
如果超出decimal范围建议分两个字段存储。
【强制】ip及手机号类固定长度字段,要用char。
【强制】选择合适的存储长度。
可以减少表存储空间。
可以减少索引长度,增加索引效率。
【建议】避免使用TEXT、BLOB数据类型。
内存临时表不支持TEXT和BLOB。会使用磁盘临时表,降低查询速度。
TEXT和BLOB需要单独成表,提高查询效率。
【建议】避免使用ENUM数据类型。
枚举类型order by效率低。
禁止使用数字作为枚举值。
在与php使用上1和’1’差别大,PHP是弱引用很容易把’1’写为1,1为key,’1’为内容。
【建议】尽可能把列定义设置为 NOT NULL。
索引NULL列,会额外增加开销,占用更多表空间。
要做计算或者比较时,会对 NULL 做特别处理。
在 SQL 中对 NULL 进行判断会全表扫描。
【强制】时间类型不要使用 varchar 或 int 等。
使用 timestamp,占用4字节与int相同,查询计算比int快。
timestamp 取值范围,1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
使用 datatime,占用8字节,明确日期时间,超出timestamp用datatime。
索引设计规范
【强制】不要使用外键和级联,应放在应用层去做。
外键与级联更适合单机及低并发,不适合分布式和高并发集群。
外键即外键约束,会影响写操作(插入速度),降低性能。
级联更新是强阻塞,存在更新风暴的风险。
【强制】一张表不要超过5个索引。
索引过多会降低性能。
合理分配索引会提高性能。
【强制】联合索引的左侧原则可以减少每个字段单独建立索引。
避免每个字段都建立索引。
联合索引区分度高的放在最左边。
联合索引如果存在非等号和等号混合时,把等号的索引放在最左边。
联合索引左侧原则,一定要注意顺序。
【强制】InnoDB 必须有主键。
结合建表四个必要字段,id作为主键。
InnoDB 属于索引组织表,逻辑顺序和索引的顺序相同。
单表时主键自增1。
预计三年内达到500万条,需要使用 snowflake 等分布式id生成主键。
不要使用 uuid、hash、md5 等作为主键,要有顺序概念。
【建议】查询想走特定索引时可以用force index。
MySQL的 optimizer 会执行它认为最优索引,但是往往不是我们需要或者最优的。
使用 force index 可以强制使用索引,结合 explain 使用,确认为最优。
【强制】有唯一索引需求,该字段就应设置唯一索引。
即使该字段是在联合索引内,也要单独设置唯一索引。
唯一索引对insert速度影响可以忽略,但是提高查询速度和唯一性是明显的。
应用层也建议做校验控制,但是根据墨菲定律,只要有可能就会出现脏数据。
【强制】varchar型设置索引要设置索引长度。
不设置默认是全部长度。
建议索引长度为20,区分度可以达到90%。
区分度计算公式:select count(distinct left(列名, 索引长度))/count(*) FROM 表名。可以查出区分度百分比。
【强制】模糊查询最好用搜索引擎。
禁止使用like %str和like %str%。因为不走索引。
可以使用like str%。走索引。
也可以走全文索引,但是需要看配置,还是推荐搜索引擎。
【强制】order by 需要注意索引的有序性。
order by后接索引应该索引的一部分,如果是联合索引,应该是联合索引的最后,避免出现file_sort,影响查询性能。where a=? and b=? order by c那么索引是(a,b,c)。
file_sort出现是没有走索引或者联合索引。出现情况:where a=? order by b索引是a。改进优化:where a=? order by b索引是(a,b)。
【强制】避免冗余索引。
重复索引:primary key(id)、index(id)、unique index(id)。
冗余索引:key(a,b,c)、key(a,b)、key(a)。
【强制】查询频率较高的sql语句,应该使用覆盖索引。
覆盖索引不是真正的索引,是一种使用索引方式。
原理是从索引中查询出想要内容,而不用回表查询,提高查询效率。
表现是explain的extra为Using index。
例如select user_no from user order user_age = 28索引为user_no时效率低,索引为(user_no,user_age)时为覆盖索引,查询效率高。
【强制】避免隐式类型转换。
定义和使用不同数据会造成隐式转换。
隐式转换会不走索引,降低查询效率。
如select user_age from user where user_no='111'
【强制】避免在字段位置写表达式,不走索引。
反例:select user_no from user where user_age*2 = 36。
正例:select user_no from user where user_age = 36/2。
查询优化
【强制】SQL性能优化目标,由高到低。
const。基本是只有一行匹配。
ref。基本是走普通索引。
range。基本是走范围索引。
index。走索引最差,和全表查询相似。
NULL。不走索引,全表查询。
【强制】不适用索引的几种情况。
不等式:!=、<>。
null判断:is null、is not null。
like模糊查询:like %a、like %a%
not in。
【建议】避免使用IN操作,如果避免不了,需小于1000条。
多表查询IN会影响查询效率。
可以用between替代。
IN(select * from)索引会失效,可以使用join(left、right、inner、full)来实现。
【建议】join优化。
最好在三张表之内,最多不要超过5个,理论可以61个。
on关联字段类型要相同。
每关联一个表就会多分配一个关联缓存,和join_buffer_size设置相关。占用内存过大会形成溢出,影响性能和稳定性。
left join的驱动表是左侧表。
inner join的驱动表是数据少的表。
right join的驱动表是右侧表。
MySQL没有full join,可以用SQL实现。例如:select * from A left join B on B.name = A.name where B.name is null union all select * from B。
尽量利用小表驱动大表,可以减少循环嵌套次数。
straight join的使用。前提是inner join内连接。inner join优先查询小表,但有group by、order by等file_sort,Using temporary时会想改变优先查询表顺序,这时可以使用straight join。straight join强制优先查询表为左侧表。
一定要是内连接才能使用straight join,否则数据可能不准确。
【强制】禁止select * 出现。
select * 增加额外解析成本。
增减字段对前端映射不一致。
无用字段增加网络消耗。
无法使用覆盖索引。
【强制】禁止使用不带字段的insert出现。
正例:insert into user(user_no,user_age) values (123,18)
反例:insert into user values (123,18)
【强制】尽量避免子查询。
子查询一般在IN中。
子查询会创建临时表,不会存在索引。
结果集大的子查询,性能越差。
可以使用join替代。
【强制】查询一条或者是否有数据时,要使用limit 1。
索引效率最高。
explain的type为const。
【建议】order by字段没有索引就不要排序。
order by字段有索引会按索引排序。没有索引影响效率。
可以设置索引,或者覆盖索引。
【建议】尽量不使用or。
同一字段用IN、between等替代or,因为很多情况不会走索引。
多字段下or两边都需要是索引且其他条件也是索引,才会走索引。
最好使用union、union all来替换。
【建议】尽量用union all替代union。
union会集合后进行唯一性去重,涉及到排序,加大资源开销。
在没有重复数据情况强制使用union all。
【建议】拆分大且复杂的SQL。
一条SQL只会使用一个CPU。
拆成多个小SQL可以通过并行提高查询效率。
【强制】禁止使用ORDER BY RAND()
随机排序性能差。
可以用其他SQL替换。
原:select id from 'dynamic' order by rand() limit 1;,
新:select id from 'dynamic' t1 join (select rand() * (select max(id) from 'dynamic') as nid) t2 on t1.id > t2.nid limit 1;注意,此查询只能随机一条id,并连续查询该id的顺序条数,具体情况具体分析,适用随机取一条,不应用随机取多条。
随机取多条解决方案:先查询所有id->在后端业务层做随机id->IN该id组。
rand()取值范围:[ 0 , 1 )。
【强制】禁止对where条件字段进行函数转换。
不走索引。
正例:select user_age from user where create_time>'20190320'
反例:select user_age from user where date(create_time)>'20190320'
【建议】in、exists、not in、not exists。
in是子查询,优先查询驱动表为内表,所以适合内表数据小的情况。
exists优先查询驱动表为外表,适合外表数据小的情况。
不建议使用not in和not exists,不走索引且容易混淆。
建议用其他SQL替代。
反例:select a.user_age from user a where a.user_no not in (select b.user_no from user_info b)。
正例:select a.user_age from user a left user_info b on a.user_no = b.user_no where b.user_no is null。
【建议】offset偏移量、分页。
分页数据量大的情况会影响查询效率,因为不是跳过offset行,而是查询offset+N行,然后抛弃offset行。
优化举例1:select user_age from user where user_no > 13333 limit 20。
优化举例2:select a.user_age from user a,(select user_no from user limit 13333,20)b where a.user_no = b.user_no。
【强制】范围查询注意。
between、>、
【强制】count()相关。
统计行数要使用count(*),不要使用count(列名)。
count(*)会统计NULL数据,count(列名)不会统计NULL数据。
当某一列的值全为NULL时,count(列名)返回的结果为0,但sum(列名)结果为NULL,因此使用sum(列名)需要使用IFNULL判断。