一手好SQL是如何练成的

于 2020-05-11 18:48:45 发布
阅读量658 收藏 3
点赞数 2
分类专栏： # Mysql
原文链接：https://www.cnblogs.com/xiaoyangjia/p/11267191.html
版权

一手好SQL是如何练成的

一. Mysql性能

1.1 最大数据量

抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。

文件系统单文件大小限制
FAT32最大4GB
NTFS最大64GB
NTFS5.0最大2TB
EXT2块大小为1024个字节，文件最大容量16GB；块大小为4096个字节，文件最大容量2TB
EXT3块大小为4KB，文件最大容量4TB
EXT4理论上可以大于16TB

《阿里巴巴Java开发手册》提出单表超过500万行或者单表的容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务的复杂度，影响程度依次是硬件配置、Mysql配置、数据库设计、索引优化。500万这个值仅供参考。博主层操纵过超过4亿行数据的单表，分页查询最新的20条记录耗时0.6秒，sql大致如下:

select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20;

prePageMinId是上一页数据记录的最小ID。虽然当时查询速度还凑合，随着数据不断增长，有朝一日必定不堪重负。分库分表是个周期长而风险高的大活儿，应该尽可能在当前结构上优化，比如升级硬件、迁移历史数据等等，实在没辙了再分。对分库分表感兴趣的同学可以阅读分库分表的基本思想。

1.2 最大并发数

并发数是指同一个时间段数据库能处理的请求个数，由max_connections和max_user_connections决定。max_connections指的是Mysql实例的最大连接数，上限值是16384。max_user_connections是指每个数据库用户的最大连接数。Mysql会为每个连接提供缓冲区，但这意味着要消耗更多的内存。如果连接数设置太高，会导致硬件吃不消，太低又不能充分利用硬件。一般要求两者的比值超过10%，计算方法如下:

max_user_connections / max_connections * 100%

查看最大连接数和数据库用户最大连接数:

show variable like ‘%max_connections%’;
show variable like ‘%max_user_connections%’;

在配置文件my.cnf中修改最大连接数:

[mysqld]
max_connections = 100
max_used_connections = 20

1.3 查询耗时0.5秒

建议将单次查询耗时控制在0.5秒之内，0.5秒是一个经验值，源于用户体验的3秒原则。如果用户的操作在3秒内没有收到响应，将会厌烦甚至退出。
响应时间 = 客户端UI渲染耗时 + 网络请求耗时 + 应用程序处理耗时 + 查询数据库耗时
0.5秒就是留给数据库的1/6处理时间。

1.4 实施原则

相比NoSQL数据库，Mysql数据库比较娇气，它扩容难、容量小并发低、SQL约束太多。如今大家都会搞点分布式，应用程序扩容比数据库扩容容易的多，所以实施的原则是数据库少干活，应用程序多干活。

  • 充分利用索引但不滥用索引，须知索引也会消耗磁盘和CPU。
  • 不推荐使用数据库函数格式化数据，尽可能交给应用程序处理。
  • 不推荐使用外键约束，用应用程序保证数据的准确性。
  • 写多读少的场景，不推荐使用唯一索引，用应用程序保证唯一性。
  • 适当的冗余字段，尝试创建中间表，用应用程序计算中间结果，用空间换时间。
  • 不允许执行极度耗时的事务，配合应用程序拆分成更小的事务。
  • 预估重要数据表(订单表)的负载和数据增长态势，提前优化。

二. 数据表设计

2.1 数据类型

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

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

char、varchar、text的特性:

  1. char的长度固定，即每条数据占用等长字节空间。
  2. varchar可变长度，可以设置最大长度，适合用在长度可变的属性上。
  3. text不设置长度，当不知道属性的最大长度时，使用text。

从空间上来看，当varchar大于某些数值的时候，会自动转换成text，规则如下:

  1. 大于varchar(255)变成tinytext
  2. 大于varchar(500)变成text
  3. 大于varchar(20000)变成mediumtext

总体来说：

  1. char存定长，执行速度快，但存在空间浪费的可能，会处理尾部空格，存储的字符上限255。
  2. varchar变长，不存在空间浪费，不处理尾部空格，虽然上限存储65535个字符，实际上只有65532的字符空间可用。
  3. text 存变长大数据，不存在空间浪费，不处理尾部空格，上限存储65525个字符，并且会使用额外的空间存储数据长度，因此可以用满65525个字符。
类型占据节点描述
datetime8字节‘1000-01-01 00:00:00.000000’ to '9999-12-31 23:59:59.999999’
timestamp4字节‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’

相比datetime,timestamp占用更少的空间，以UTC的格式存储自动转换时区。

Q: 为什么char类型数据执行效率比varchar和text都要高？

2.1 避免空值

Mysql中null依然会占用空间，也会使用索引，导致索引统计时更加复杂。从Null值更新到非Null值无法做到原地更新，容易发生索引分裂影响性能。尽可能的将Null值用更有意义的值来代替，也能避免SQL语句里包含is not null的判断。

2.2 text类型优化

由于text字段存储大量数据，表容量会很快涨上去，影响同表内其它字段的查询性能。建议抽取出来放到子表中，用业务主键来关联。

三. 索引优化

3.1 索引分类

  1. 普通索引:

ALTER TABLE table_name ADD INDEX index_name ( column )

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

  1. 唯一索引:

ALTER TABLE table_name ADD UNIQUE ( column )

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。

  1. 主键索引

ALTER TABLE table_name ADD PRIMARY KEY ( column )

主键索引是一个特殊的唯一索引，用于唯一标识数据表的某条记录，不允许出现空值。

  1. 外键索引

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, …) REFERENCES tbl_name(index_col_name,…) [ON DELETE reference_option] [ON UPDATE reference_option]
点我参考

  1. 复合索引(组合索引)

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

在多个字段上建立的索引，能够加速符合条件的查询速度。

  1. 全文索引

创建方法:
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(’column)
使用方法:
SELECT * FROM student WHERE MATCH(name,address) AGAINST('小明, ‘美国’)

用于海量文本的查询，Mysql5.6之后的InnoDB和MyISAM均支持全文检索，但由于查询精度和扩展性不佳，因此更多的企业选择使用Elastisearch来做全文检索。

3.2 索引的优化

  1. 分页查询很重要，如果查询的数据量超过30%，则Mysql不会使用索引。
  2. 单表的索引数不要超过5个，单个索引字段不超过5个。
  3. 字符串可以使用前缀索引，前缀的长度控制在5-8个字符。
  4. 如果字段的唯一性太低，那么增加索引将没有意义，如: 是否删除、性别等。
  5. 合理的使用覆盖索引，如下所示:

select login_name, nick_name from member where login_name = ?

login_name, nick_name两个字段建立组合索引，比login_name简单索引要更快。

四. SQL优化

4.1 分批处理

一次性对大量数据进行处理(更新、删除、不带分页的查询)可能会导致整个Mysql数据库处于阻塞状态
比如:

update status=0 FROM coupon WHERE expire_date <= #{currentDate} and status=1;

如果大量优惠券需要更新为不可用状态，执行这条SQL可能会堵死其他SQL，所以我们需要使用分批处理的思想。
伪代码如下:

int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
    List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
    if (CollectionUtils.isEmpty(batchIdList)) {
        return;
    }
    update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
    pageNo ++;
}

4.2 操作符<>和!=的优化

通常<>和!=操作符无法使用索引，举个例子，查询金额不为100的订单:

select id from order where amount != 100;

如果金额为100的订单非常少(数据分布不均匀)，那么本次查询很可能使用的是"全表扫描(full table scan)"。介于这种不确定性，我们采用union聚合搜索，方法如下:

(select id from orders where amount > 100)
union all
(select id from orders where amount < 100 and amount > 0)

4.3 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字段都有索引，查询才最高效。

4.4 IN优化

  1. IN适合主表大，子表小。EXISTS适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。
  2. 尝试改成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’;

4.5 不做列运算

通常在查询条件中做列运算会导致索引失效，比如:
查询当日订单

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’;

4.6 避免Select all

如果不查询表中的所有列，避免使用select *，因为它会进行全表扫描，不能有效的利用索引。

4.7 Like优化

like用于模糊查询，举个例子（field已建立索引）：

SELECT column FROM table WHERE field like ‘%keyword%’;

这个查询未命中索引，换成下面的写法：

SELECT column FROM table WHERE field like ‘keyword%’;

去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢？全文索引fulltext可以尝试一下，但Elasticsearch才是终极武器。

4.8 Join优化

join的实现是采用Nested Loop Join算法，通过驱动表的结果集作为基础数据，根据两张表的连接条件，将驱动表的每一条记录放在另一张表中查询数据，然后将查询到的记录合并，得到最终结果。如果有多个join，则将前面的结果作为循环数据，再到后面一张表中查询数据。

  1. 驱动表和被驱动表尽可能的增加查询条件和满足ON的条件，而少用where,用小结果集驱动大结果集。
  2. 被驱动表的join字段上加上索引，但执行时发现无法使用索引时，请设置足够大的Join Buffer Size。
  3. 禁止Join连接三个以上表，尝试增加冗余字段。

4.9 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使用游标实现分页查询的方法

五. 其它数据库

作为一名后端开发人员，务必精通作为存储核心的MySQL或SQL Server，也要积极关注NoSQL数据库，他们已经足够成熟并被广泛采用，能解决特定场景下的性能瓶颈。

分类数据库特性
键值型Memcache用于内容缓存，大量数据的高访问负载
键值型Redis用于内容缓存，比Memcache支持更多的数据类型，并能持久化数据
列式存储HBaseHadoop体系的核心数据库，海量结构化数据存储，大数据必备。
文档型MongoDb知名文档型数据库，也可以用于缓存
文档型CouchDBApache的开源项目，专注于易用性，支持REST API
文档型SequoiaDB国内知名文档型数据库
图型Neo4J用于社交网络构建关系图谱，推荐系统等
爱喝咖啡的程序员
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值