一手好SQL是如何练成的

最新推荐文章于 2021-12-09 20:44:04 发布
最新推荐文章于 2021-12-09 20:44:04 发布
阅读量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
    评论
专栏目录
sql练习工具
01-07
mysql数据库连接工具，亲测sql初学者练习最佳
轻松在线练SQL
Aldeo
11-22 291
0x00 SQLZOO 网址：https://sqlzoo.net/ 是SQLZOO包括了 SQL 学习的教程和参考资料，支持 SQL Sever、Oracle、MySQL、DB2、PostgreSQL等多个 SQL 搜索引擎，现在支持英德日中四国语言，不过中文是繁体的，看起来别别扭扭，建议还是用英文来看，就算英语不好，翻译大法好… 遇到不会的题，祭出 Google 大法，看看别人的一些...
SQL练习
王星星
09-04 1928
SQLTraining 写在前面，最近做了一些SQL OJ的题，把题解和想法记录下来 关于DB members(部分) memid surname firstname address zipcode telephone recommendedby joindate 0 GUEST GUEST GUEST 0 (000) 000-0000 None 2012-07-01 00:0...
sql练习
weixin_38168081的博客
04-25 120
表（一）Student (学生use) 属性名 数据类型 可否为空 含义 Sno varchar(20) 否 学号（主码） Sname varchar(20) 否 学生姓名 ...
【SQL学习+练习篇】基础训练，适用于初学者（一）
小慌慌的博客
12-09 3万+
SQL简介 SQL, 全称为Structured Query Language（结构化查询语言)。 要讲SQL就绕不开database(数据库), 平时所说的数据库，一般就是指的Relational database(关系型数据库). 大家知道数据库是用来存储大量数据的一种软件，那么SQL呢是用来操作数据里的数据，具体来说SQL可以做数据查询，数据更新，写入数据等等。 初识 这里的电影表Movies后续会一直用到 --电影表 Id Title Director ...
Oracle Sql语句转换成Mysql Sql语句
02-17
当需要将一个基于Oracle SQL的应用程序迁移到MySQL环境时，就需要进行SQL语句的转换工作。本项目提供了一个Java源码工具，能够帮助用户便捷地将Oracle SQL语句转换为MySQL SQL语句。 Oracle SQL与MySQL SQL的主要...
一款免安装sqlserver客户端管理工具，好用
01-30
标题中的“一款免安装sqlserver客户端管理工具，好用”指的是SqlDbxPersonal，这是一款专为SQL Server设计的轻量级、无需安装的数据库管理工具。它允许用户方便地进行SQL查询、数据库管理和开发工作，而无需经历传统...
sql去掉最后一个字符
01-10
sql去掉最后一个字符,sql去掉最后一个字符
mysql转化成sql server sql转化成mysql工具
04-28
标题提到的“mysql转化成sql server”和“sql转化成mysql工具”就是解决这个问题的关键。 首先，我们来讨论MySQL到SQL Server的转换。MySQL是一种开源、轻量级的DBMS，适合小型到大型的应用场景。而SQL Server则...
基于CodeMirror的一个在线sql编辑工具
04-17
* 左侧提供数据库表字段树形结构，可拖拽到sql编辑框。 * 输入首字母可提示表名字段名（表名字段名可自定义） 。 *括号高亮匹配。 * 执行与中断功能。 * 自定义外部接口获取sql关键词、函数、表字段等，用于sql编辑...
经典sql练习
10-25
经典sql练习，链接给出题型： https://www.jobui.com/mianshiti/it/shujuku/5838/ 本意无需资源分数，但csdn变坏了，没有0分可以选，没有分数的，发qq邮箱索要1943680751
SQL基本练习
qq_31628323的博客
08-15 184
select * from tab_user; – 删除id为5的 delete from tab_user where u_id =5; – 删除表里的所有内容 delete from tab_user; – 删除表 drop table 表名； – 删除表里的所有记录 – 先drop table 再create – TRUNCATE 要比delete的效率高 ...
SQL训练汇总
GoodJob
05-02 278
SQL训练汇总（每天更新10道题）1.查找最晚入职员工的所有信息CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT N...
经典SQL练习——详细到令人发指（未完待续）
weixin_43222373的博客
06-04 894
最近几次面试，每一次回来都会感到自己的不足。 比如上一次，一家做教育的公司笔试题里有三道数据库题目，都是关于学生和成绩的查询，看着题目我不禁陷入沉思，人家日常用的SQL语句我都写不来的话，那我的数据库是不是白学了。 由于用时太长，最后还是面试官过来问我：做完没有。 无地自容的我几乎想当场逃走。 好在回来谷歌一下，发现是知乎上的SQL原题，而知乎又是转载自CSDN。 不禁有一种原来如此的感觉，随之决定也做一下这个全套练习， 为贯彻费曼学习法，尽量以“真正的初学者”作为目标读者。 PS：基于MySQL5.7.1
sql每日一练 进来练一下 这个有一点难 都进来看一下吧
phphot
02-23 1878
有1－16的数字在SQL中输出 输出的样式是1 12 11 102 13 16 93 14 15 84 5 6 7 --================================================--绕圈的SQL算法（感觉很笨，不知道有没有更好的算法）--作者：冷箫轻笛--日期：2008-02-21--执行方式（参数必须为整数的平方，但大小
MySql sql语句训练（一）
哦哦一人
09-28 283
一直在做前端的我某一天如果做后端那我能不能做好？ 一个这样的问题缠绕在我脑海中，于是我开始分析后端开发最需要的是什么 首先 我们应该知道web中为什么分前端和后端 前端，简单来说就是显示数据和样式，把数据库的数据直接展示到页面。 而后端就是处理数据 操作数据库。 而这段话中最重要的一点就是----数据 前端的数据是需要数据库和后端支持，后端的数据也是需要数据库来支持 而对于我来说，只要对数据库的操作到位，那我也是一个合格的后端开发 （以上理解仅为我个人的理解，可能不对，但是对我来说就是这个意思） 但对于我
SQL练习合集
Neko
04-18 5932
175 组合两个表 combine-two-tables 表1: Person 列名 类型 PersonId int FirstName varchar LastName varchar PersonId 是上表主键 表2: Address 列名 类型 AddressId int PersonId int City varchar State...
如何1小时快速上手SQL？（小白篇）
java06051515的博客
12-05 802
如何1小时快速上手SQL？ 为了帮大家尽快实现学习Hive SQL、提数，在大数据领域大干一场的愿望，帮你准备好了数据，准备好了SQL样例，照着学，边学边练，主要是解决学一门新手艺起点高入手难的问题。 这个自编的课程分成如下的几个部分，欢迎大家选读选练。 特别说明 准备环境 两张示例表 SQL示例 简单select查询 使用where语句按条件筛选 使用distinct语句排重 使用orde...
非常好的SQL练习题
码出幸福人生
09-16 1684
一、            设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。                  表1-1数据库的表结构 表（一）Student (学生表)   
深入理解Hive SQL编译成MapReduce的全过程
通过理解这个编译过程，美团团队得以解决Hive相关问题，优化SQL性能，定制化需求，从而更好地管理海量数据和数据分析。对于任何使用Hive的组织来说，掌握这一核心机制有助于提升数据处理的效率和灵活性。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值