2020-04-22 12:41:49 晓掌柜 版权声明:本文为站长原创文章,转载请写明出处
之前一致想写一个关于mysql优化的文章,由于各种原因一致被搁浅了。趁着最近做sql改造的时机就把这个坑给填了吧。
你的操作数据已经很庞大了吗
你定义的字段是最优的吗
你定义了那些索引,你的sql使用到索引了吗
你的sql有雷区吗
这里引用阿里巴巴开发手册中的一段描述:
14. 【推荐】单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
①为什么要分
随着业务量的增加,单表,单库的数据会越来越庞大的。相应的数据的增删改查的开销也会越来越大。
分库分表前 | 分库分表后 | |
---|---|---|
并发情况 | 没有分布式部署,单机性能支持不了高并发 | 数据库集群,并发及数据吞吐上限提升 |
硬件设备 | 一台设备总会有瓶颈,磁盘等负荷越来越大 | 压力分摊 |
数据库性能 | 随着数据量的增加,sql越来越慢 | 单表数量少,压力分摊,性能提升 |
②怎么分
垂直拆分
.例如根据不同的业务进行拆分的,拆分成不同的数据库,比如会员数据库、订单数据库、支付数据库、消息数据库等,
垂直拆分在大型电商项目中使用比较常见。
水平拆分
.把同一张表中的数据拆分到不同的数据库中进行存储、或者把一张表拆分成 n 多张小表。
③会引发问题
事务问题
跨库跨表数据关联查询问题
数据同步问题
...
①在字段定义时遵循一个原则,尽量简单、占用空间最小。如:
如果长度能够满足,整型尽量使用tinyint、smallint、medium_int而非int。
如果字符串长度确定,采用char类型。
如果varchar能够满足,不采用text类型。
精度要求较高的使用decimal类型,也可以使用BIGINT,比如精确两位小数就乘以100后保存。
尽量采用timestamp而非datetime。
②尽量避免null值,text字段能容纳大量数据,但会印象到其他字段的查询性能(可以抽取到子表中)
①索引类型
普通索引:最基本的索引。
组合索引:多个字段上建立的索引,能够加速复合查询条件的检索。
唯一索引:与普通索引类似,但索引列的值必须唯一,允许有空值。
组合唯一索引:列值的组合必须唯一。
主键索引:特殊的唯一索引,用于唯一标识数据表中的某一条记录,不允许有空值,一般用primary key约束。
全文索引:用于海量文本的查询,MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳,
更多的企业选择Elasticsearch。
②那些情况下索引会失效
.条件中有 or
.对于多列索引,不是使用的第一部分(第一个),则不会使用索引
.like查询是以%开头
.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
.查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
.not in ,not exist.
① <>的优化
<>无法使用索引,建议使用 != 或者 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;
本文章部分来源于网络,不正之处敬请斧正。同时也会持续更新完善