文档章节

SQL查询优化——数据结构设计

 蜗牛奔跑
发布于 2016/11/24 13:40
字数 1804
阅读 6
收藏 0
点赞 0
评论 0

本文部分内容会涉及mysql,可能在其他数据库中并不适用。本章节只针对数据库结构设计做讨论,查询优化的其他内容待续。

数据库设计及使用是WEB开发程序员必备的一项基础技能,在大数据量和高并发场景,合理的数据结构及SQL查询优化对项目来说都会显得格外重要。大部分有经验的程序员都能了解到,程序的瓶颈往往不在程序本身,而在数据访问层。造成数据访问效率低下的原因有很多,如何解决这些问题,直接影响到应用的稳定性、健壮性。以下列举几个常见的问题:

  • 数据库锁表,查询阻塞
  • 高并发场景下,链接数量瓶颈
  • 查询效率低下,程序长时间无法退出
  • 写入性能低下,造成读写竞争激烈

以上只是列出了数据库使用过程中比较常见的问题,出现这些问题的常见原因列举如下:

  • 数据结构设计不合理
  • 索引设计糟糕
  • 程序维护数据链接不合理
  • 程序员太懒惰,数据库做了不擅长的工作
  • 数据冗余
  • SQL太渣

本节只对数据结构设计不合理进行讨论,后续章节会继续讨论其他内容。

一直觉得作为一个中级以上水平的程序员,查询优化是一项必备的基础技能。良好的数据结构设计,直接影响到后期软件的性能、健壮性、可维护性、可扩展性。见过很多因为数据结构设计不合理而造成软件最终难以扩展,难以维护的场景。要避免这些问题,我们就要掌握良好的数据结构设计能力。

怎样的数据结构才是合理的?这并没有一个完美通用的解决方案,要考虑具体的应用场景。但有一些准则,使我们应该尝试去遵守的。列举如下:

  1. 根据业务查询场景,考虑数据结构分布
  2. 如果没有业务主键,应建立ID自增主键
  3. 保证使用较小的数据类型,避免空间浪费
  4. 合理控制表的字段数量,必要时分表存储
  5. 添加字段注释

针对以上几点,分别详述如下:

1、根据业务场景,考虑数据结构分布

业务场景,决定了你要存储什么样的数据,但它不会决定你要如何存储这些数据。你可以简单的将这些信息存储到一张表里,例如user表。但当我们需要更多的信息,例如用户的附属属性(学校,住址等),如果全部塞到一张表里,对于小数据量的数据库不会有太大问题,但当遇到大数据量的场景时,查询就有可能变的缓慢。分表会是一个更好的解决方案,根据不同的业务场景,将这些信息分为两类,存储在不同的表里,是更加合理的解决方案。

这里要说的其实是,不要为了方便把所有的东西都塞到一张表里,虽然这样会让你的程序编写起来容易很多,但是会造成更多的问题。例如有些人会把1:N的关系存储到一张表里,这样就会带来数据冗余,坏处有很多,例如:针对N的写改删查都会变得很复杂;表体积变大、字段增多,造成查询缓慢;其他表链表查询时速度缓慢等等。

2、如果没有业务主键,应建立ID自增主键

主键是一条记录的唯一标志,没有主键在很多时候我们无法得心应手的操作数据。可能在某些场景下,我们确实没有设置主键的必要,但无论你是否主动设置主键,数据库都会有一个主键(如果你没有主动设置,数据库默认会有一个ROW_ID列,而这一列是你看不到的)。主键在连表、查询等方面业务提供很大帮助,所以无论如何,建立一个主键是很必要的

3、保证较小的数据类型,避免空间浪费

较小的数据类型意味着较小的存储代价,且数据库能够更高效的利用缓存空间。存储引擎都会采用不同的方式对索引或者数据缓存在内容中,较小的数据类型意味着在有限的内容空间中,你能够存储更多有价值的数据。对于可变长度的varchar类型,如果我们设置的是20长度,但实际占用的只有10个长度,在载入内存时,占用的空间依旧是20而不是10。所以对于可变长度类型,合理的长度更为重要。

4、合理控制表的字段数量,必要时分表存储

字段数量过多如果不是因为业务需且数据结构设计合理,大多会产生以下几个问题:

  • 数据冗余
  • 索引过多
  • 表体积大

这里要提醒避免不必要的数据冗余,针对数据冗余的讨论我们暂且放在后面。

因为字段数据量多,往往查询场景也会非常复杂多变,所以索引也就跟着变多了。索引多会直接影响到表的写入性能,这个性能的损耗是非常大的,可能是数以十倍计算的时间损耗。在写入频繁的场景,有可能会出现写入瓶颈。由于写入而影响读取性能的问题也很多。

表体积大意味着数据库在读取数据的时候需要扫描更多更大的数据块,载入内存做缓存时也不能充分利用缓存带来的效果。表大小对于表的性能也是由为重要的。

分表是解决字段过多的一个解决方案,数据库分表后,程序可能会改动比较大,但我们应该追求合理完美的软件设计,摒弃糟粕。分表后使用链表查询,或者在程序中做两次查询。有些人可能会觉得连表,性能一定很差,其实不然。连表意味着我们在同一个SQL中,可以使用两个索引,但是单表查询我们只能使用一个索引。如果索引设计合理,在大多数场景下(应该是大数据量场景),连表查询会比单表查询性能更高,甚至高出太多。曾经有过这样的场景,优化分表后画面变得没好多了。

5、添加字段注释

这里只是为了提示规范化数据库设计。

© 著作权归作者所有

共有 人打赏支持
粉丝 34
博文 595
码字总数 112669
作品 0
海淀
浅谈 MySQL 子查询及其优化

使用过oracle或者其他关系数据库的DBA或者开发人员都有这样的经验,在子查询上都认为数据库已经做过优化,能够很好的选择驱动表执行,然后在把该经验移植到mysql数据库上,但是不幸的是,mys...

xrzs ⋅ 2014/07/09 ⋅ 1

一个例子与 InnoDB 索引的几个概念

1、一个简单的sql语句问题 假设当前我们有一个表记录用户信息,结构如下: a) 表结构 CREATE TABLE ( int(11) NOT NULL DEFAULT ’0′, int(1) unsigned, ….. PRIMARY KEY (), KEY () ) ENG...

xrzs ⋅ 2012/09/28 ⋅ 2

【数据库优化专题】MySQL视图优化(一)

本期数据库优化专题分享,为大家带来的是DBA+社群MySQL领域原创专家——李海翔所著的MySQL视图优化系列文章。以下是第一部分的内容,未完部分敬请关注后续更新。 专家简介 李海翔 网名:那海...

李海翔 ⋅ 2015/11/06 ⋅ 0

1300万数据的复杂查询,要求在3s内返回结果

有一个需求,请求支援, 目前有1300万数据,需要进行group by的max查询 。 请问用什么设计方法可以在3秒内遍历查询出结果,希望有高手出手~~~ 表结构设计: CREATE TABLE ( int(10) NOT NUL...

c61811 ⋅ 2014/07/29 ⋅ 25

mysql性能优化-慢查询分析、优化索引和配置

目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) inte...

zyt_1978 ⋅ 2016/09/18 ⋅ 0

mysql性能优化-慢查询分析、优化索引和配置

目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) inte...

蓝狐乐队 ⋅ 2014/07/11 ⋅ 0

SQL性能优化前期准备-清除缓存、开启IO统计

原文出处:VicentRen 如果需要进行SQl Server下的SQL性能优化,需要准备以下内容: 一、SQL查询分析器设置: 1、开启实际执行计划跟踪。 2、每次执行需优化SQL前,带上清除缓存的设置SQL。 ...

VicentRen ⋅ 2016/08/21 ⋅ 0

mysql性能优化-慢查询分析、优化索引和配置

目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) inte...

Linland ⋅ 2015/06/19 ⋅ 0

mysql性能优化-慢查询分析、优化索引和配置

目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) maxconnections 2) backlog 3) intera...

again-Y ⋅ 2014/11/20 ⋅ 0

10条SQL优化语句,让你的MySQL数据库跑得更快!

慢SQL消耗了70%~90%的数据库CPU资源; 1 不使用子查询 例:SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在MySQL5.5版本里,内部执行计划器是这样执行的...

kangjunfei ⋅ 2017/12/14 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

HiSDP —— 高效的C++软件开发平台

目前阿里集团每天有近1000PB的数据是通过LogAgent采集的,为了让LogAgent做到资源占用节省和高效采集,背后是基于HiSDP去构建的。 缘由 当决定采用C++编程语言去开发一个软件时,紧接着所面临...

阿里云云栖社区 ⋅ 18分钟前 ⋅ 0

zookeeper-3.4.12 下载与安装教程

一、zookeeper下载地址 http://mirrors.hust.edu.cn/apache/zookeeper/ 二、启动教程 把压缩包放在指定目录下 第三: 进入 conf文件夹底下 zoo_sample.cfg 文件名改成 zoo.cfg 第四步: 进入b...

泉天下 ⋅ 20分钟前 ⋅ 0

Oracle 中文日期转换

SELECT TO_date('2011年11月11日', 'yy"年"mm"月"dd"日"') FROM DUAL; 1. Oracle无法识别中文格式,所以添加双引号。 2. 后面的格式是指字符串在转换前的格式,而不是指转换后的格式。...

江戸川 ⋅ 21分钟前 ⋅ 0

MySell:API Spring Boot

起步 类目 商品 订单

BeanHo ⋅ 23分钟前 ⋅ 0

Spring方法拦截器MethodInterceptor

参考资料 1、Spring方法拦截器MethodInterceptor 2、Sharding JDBC源码分析-JdbcMethodInvocation类的作用

哎小艾 ⋅ 27分钟前 ⋅ 0

正则表达式

元字符 元字符,又叫字符集,就是用一些特殊符号表示特定种类的字符或位置。 匹配字符 . 匹配除换行符以外的任意字符 \w 匹配字母或数字或下划线或汉字 \s 匹配任意的空白符 \d 匹配数字 匹配...

wangchen1999 ⋅ 27分钟前 ⋅ 0

数据库数据导入Elasticsearch案例分享

基于bboss持久层和bboss elasticsearch客户端实现数据库数据导入es案例分享(支持各种数据库和各种es版本) 1.案例对应的源码 https://gitee.com/bboss/bboss-elastic/blob/master/bboss-el...

bboss ⋅ 28分钟前 ⋅ 0

动手---sbt(2)

参考 https://blog.csdn.net/leishangwen/article/details/46225587 建立一个chisel_max目录,文件内容如后面所述,现在开始执行命令: joe@joe-Aspire-Z3730:/media/sdb4/download/scala$ c......

whoisliang ⋅ 34分钟前 ⋅ 0

纯js实现最简单的文件上传(后台使用MultipartFile)

<!DOCTYPE html><html><head> <meta charset="UTF-8"> <title>XMLHttpRequest上传文件</title> <script type="text/javascript"> //图片上传 var xhr......

孟飞阳 ⋅ 39分钟前 ⋅ 0

iOS宇宙大战游戏、调试工具、各种动画、AR相册、相机图片编辑等源码

iOS精选源码 日期时间选择器,swift Space Battle 宇宙大战 SpriteKit游戏源码 LLDebugTool - 便捷的IOS调试工具(新增截屏功能) 相机扫描or长按识别二维码、FMDB、键盘动态高度、定位等 动画...

sunnyaigd ⋅ 40分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部