SQLServer数据表用法,面向程序员的数据库访问性

2019-12-14 21:12 来源:未知

面向程序员的数据库访问性能优化法则

数据表定义

数据表(或称表)是数据库最重要的组成部分之一,数据库中以表为组织单位存储数据,数据库只是一个框架,数据表才是其实质内容。数据库管理工具中可以显示数据库中的所有数据表,数据表是数据库中一个非常重要的对象,是其他对象的基础。

 

 

创建数据表

方式一:打开数据库管理工具-》登录-》选择数据库-》选择表-》右键点击,选择新建表-》在右边视图中输入列名、数据类型、是否可空、在列属性中设置需要的属性-》点击保存(或ctrl+s)-》保存成功,刷新即可显示

图片 1

图片 2

方式二:使用T-SQL脚本新建表

 1 --数据库声明
 2 use testss
 3 --建表语法声明
 4 create table test1
 5 (
 6 --字段声明
 7 id int identity(1,1) not null,
 8 name nvarchar(50) null,
 9 sex nvarchar(50) null,
10 age nvarchar(50) null,
11 classid int,
12 primary key clustered(id asc) with(ignore_dup_key=off) on [primary]    --主键索引声明
13 )on [primary]
14 
15 --字段注释声明
16 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'id主键' , @level0type=N'SCHEMA',
17 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'id';
18 
19 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',
20 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'name';
21 
22 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',
23 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'sex';
24 
25 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',
26 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'age';
27 
28 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级id' , @level0type=N'SCHEMA',
29 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'classid';
30 
31 go

 示例结果如下:图片 3

Preface

特别说明:

删除数据表

方式一:打开数据库管理工具-》登录-》选择数据库-》选择表-》右键点击,选择删除-》点击弹出框中的确定删除-》刷新数据库

图片 4图片 5

方式二:使用T-SQL脚本删除:drop table test2;

图片 6

 

1、  本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识;

修改数据表

方式一:打开数据库管理工具-》登录-》选择数据库-》选择表-》右键点击,选择重命名-》重新输入表名-》按enter键确定-》刷新可查看新表名

 图片 7

方式二:使用T-SQL脚本修改表名:exec sp_rename 'test1','test2';

图片 8

    In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well).

2、  本文许多示例及概念是基于Oracle数据库描述,对于其它关系型数据库也可以参考,但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库;

查看数据表

--查询数据库下所有的表
--xtype='U'查询用户表
select name from sysobjects where xtype='U'
-- xtype='S'查询系统表
select name from sysobjects where xtype='S'

--查看test1表的所有信息
exec sp_help test1;

--查询test1表中所有的列
select * from syscolumns where id=Object_Id('test1')

--查看test1表中所有列数据
select * from test1;

  •     full mysqldump backup + binlog on master(master was normally running)
  •     full Xtrabackup backup + binlog on master(master was normally running)**
  •     full Xtrabackup backup + binlog on binlog server( binlog server had acquired binlogs before master crashed.)

3、  本文未深入数据库优化中最核心的执行计划分析技术。

 

 

    Les't see another way to achieve the goal more simply.

读者对像:

 

开发人员:如果你是做数据库开发,那本文的内容非常适合,因为本文是从程序员的角度来谈数据库性能优化。

Procedure

架构师:如果你已经是数据库应用的架构师,那本文的知识你应该清楚90%,否则你可能是一个喜欢折腾的架构师。

 

DBA(数据库管理员):大型数据库优化的知识非常复杂,本文只是从程序员的角度来谈性能优化,DBA除了需要了解这些知识外,还需要深入数据库的内部体系架构来解决问题。

***Step

 

  1. Destroy***

引言

 

在网上有很多文章介绍数据库优化知识,但是大部份文章只是对某个一个方面进行说明,而对于我们程序员来说这种介绍并不能很好的掌握优化知识,因为很多介绍只是对一些特定的场景优化的,所以反而有时会产生误导或让程序员感觉不明白其中的奥妙而对数据库优化感觉很神秘。

Check the table on master.

很多程序员总是问如何学习数据库优化,有没有好的教材之类的问题。在书店也看到了许多数据库优化的专业书籍,但是感觉更多是面向DBA或者是PL/SQL开发方面的知识,个人感觉不太适合普通程序员。而要想做到数据库优化的高手,不是花几周,几个月就能达到的,这并不是因为数据库优化有多高深,而是因为要做好优化一方面需要有非常好的技术功底,对操作系统、存储硬件网络、数据库原理等方面有比较扎实的基础知识,另一方面是需要花大量时间对特定的数据库进行实践测试与总结。

 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: sysbench
 6 
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest1            |
11 | sbtest2            |
12 | sbtest3            |
13 | sbtest4            |
14 | sbtest5            |
15 +--------------------+
16 5 rows in set (0.00 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
19 +----------+
20 | count(*) |
21 +----------+
22 |    10000 |
23 +----------+
24 1 row in set (0.01 sec)

作为一个程序员,我们也许不清楚线上正式的服务器硬件配置,我们不可能像DBA那样专业的对数据库进行各种实践测试与总结,但我们都应该非常了解我们SQL的业务逻辑,我们清楚SQL中访问表及字段的数据情况,我们其实只关心我们的SQL是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化?如何能快速定位SQL性能问题并找到正确的优化方向?

 

面对这些问题,笔者总结了一些面向程序员的基本优化法则,本文将结合实例来坦述数据库开发的优化知识。

**Generate a full Xtrabackup of master.**

一、数据库访问优化法则简介

要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能会是瓶颈点,为什么这些一般的工作我们能快速确认瓶颈点呢,因为我们对这些慢速设备的性能数据有一些基本的认识,如网络带宽是2Mbps,硬盘是每分钟7200转等等。因此,为了快速找到SQL的性能瓶颈点,我们也需要了解我们计算机系统的硬件基本性能指标,下图展示的当前主流计算机性能指标数据。

 

 

从图上可以看到基本上每种设备都有两个指标:

延时(响应时间):表示硬件的突发处理能力;

带宽(吞吐量):代表硬件持续处理能力。

 

从上图可以看出,计算机系统硬件性能从高到代依次为:

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

由于SSD硬盘还处于快速发展阶段,所以本文的内容不涉及SSD相关应用系统。

根据数据库知识,我们可以列出每种硬件主要的工作内容:

CPU及内存:缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算;

网络:结果数据传输、SQL请求、远程数据库访问(dblink);

硬盘:数据访问、数据写入、日志记录、大数据量排序、大表连接。

 

根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容,可以整理出如下图所示的性能基本优化法则:

 

 

这个优化法则归纳为5个层次:

1、  减少数据访问(减少磁盘访问)

2、  返回更少数据(减少网络传输或磁盘访问)

3、  减少交互次数(减少网络传输)

4、  减少服务器CPU开销(减少CPU及内存开销)

5、  利用更多资源(增加资源)

 

由于每一层优化法则都是解决其对应硬件的性能问题,所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法,因此针对低速设备问题的可优化手段也更多,优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案,而不应该首先想到的是增加资源解决问题。

以下是每个优化法则层级对应优化效果及成本经验参考:

 

 

优化法则

性能提升效果

优化成本

减少数据访问

1~1000

返回更少数据

1~100

减少交互次数

1~20

减少服务器CPU开销

1~5

利用更多资源

@~10

 

 

接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。

 

 1 [root@zlm2 06:28:44 /data/mysql/mysql3306/data]
 2 #innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: 
 4 xtrabackup: recognized client arguments: 
 5 180731 06:29:03 innobackupex: Starting the backup operation
 6 ... //Omitted.
 7 
 8 180731 06:29:31 Executing UNLOCK TABLES
 9 180731 06:29:31 All tables unlocked
10 180731 06:29:31 [00] Copying ib_buffer_pool to /data/backup/2018-07-31_06-29-03/ib_buffer_pool
11 180731 06:29:31 [00]        ...done
12 180731 06:29:31 Backup created in directory '/data/backup/2018-07-31_06-29-03/'
13 MySQL binlog position: filename 'mysql-bin.000043', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'
14 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/backup-my.cnf
15 180731 06:29:31 [00]        ...done
16 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/xtrabackup_info
17 180731 06:29:31 [00]        ...done
18 xtrabackup: Transaction log of lsn (10141400402) to (10141400411) was copied.
19 180731 06:29:31 completed OK!

二、Oracle数据库两个基本概念

 

数据块(Block)

数据块是数据库中数据在磁盘中存储的最小单位,也是一次IO访问的最小单位,一个数据块通常可以存储多条记录,数据块大小是DBA在创建数据库或表空间时指定,可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构,一个数据库可以包括多个数据文件,一个数据文件内又包含多个数据块;

 

 

**Continue to executing some dml operations on the target table and then kill the mysqld.**

ROWID

ROWID是每条记录在数据库中的唯一标识,通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号,如下图所示:

 

 1 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 2000;
 2 Query OK, 2000 rows affected (0.10 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 3000;
 5 Query OK, 3000 rows affected (0.07 sec)
 6 
 7 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
 8 +----------+
 9 | count(*) |
10 +----------+
11 |     5000 |
12 +----------+
13 1 row in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest5;
16 Query OK, 0 rows affected (0.01 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
19 Query OK, 0 rows affected (0.02 sec)
20 
21 (zlm@192.168.1.101 3306)[sysbench]>show master status;
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | mysql-bin.000044 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730232 |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 1 row in set (0.00 sec)
28 
29 [root@zlm2 06:38:14 ~]
30 #pkill mysqld
31 
32 [root@zlm2 06:38:18 ~]
33 #ps aux|grep mysqld
34 root      4050  0.0  0.0 112640   956 pts/0    R+   06:38   0:00 grep --color=auto mysqld

三、数据库访问优化法则详解

 

1、减少数据访问

**Scp the Xtrabackup backup to another server zlm3 with newly initialized instance**

1.1、创建并使用正确的索引

数据库索引的原理非常简单,但在复杂的表中真正能正确使用索引的人很少,即使是专业的DBA也不一定能完全做到最优。

索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销,正确的索引可以让性能提升100,1000倍以上,不合理的索引也可能会让性能下降100倍,因此在一个表中创建什么样的索引需要平衡各种业务需求。

索引常见问题:

索引有哪些种类?

常见的索引有B-TREE索引、位图索引、全文索引,位图索引一般用于数据仓库应用,全文索引由于使用较少,这里不深入介绍。B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等,以下是B-TREE索引的简单介绍:

B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。

叶子节点内容:索引字段内容+表记录ROWID

根节点,分支节点内容:当一个数据块中不能放下所有索引字段数据时,就会形成树形的根节点或分支节点,根节点与分支节点保存了索引树的顺序及各层级间的引用关系。

         一个普通的BTREE索引结构示意图如下所示:

 

 

如果我们把一个表的内容认为是一本字典,那索引就相当于字典的目录,如下图所示:

 

 

 

 

 

图中是一个字典按部首+笔划数的目录,相当于给字典建了一个按部首+笔划的组合索引。

一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等)。

一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录。

SQL什么条件会使用索引?

当字段上建有索引时,通常以下情况会使用索引:

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%'(后导模糊查询)

T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)

 

SQL什么条件不会使用索引?

 

 

查询条件

不能使用索引原因

INDEX_COLUMN <> ?

INDEX_COLUMN not in (?,?,...,?)

不等于操作不能使用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || 'a' = ?

经过普通运算或函数运算后的索引字段不能使用索引

INDEX_COLUMN like '%'||?

INDEX_COLUMN like '%'||?||'%'

含前导模糊查询的Like语法不能使用索引

INDEX_COLUMN is null

B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引

NUMBER_INDEX_COLUMN='12345'

CHAR_INDEX_COLUMN=12345

Oracle在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。

a.INDEX_COLUMN=a.COLUMN_1

给索引查询的值应是已知数据,不能是未知字段值。

注:

经过函数运算字段的字段要使用可以使用函数索引,这种需求建议与DBA沟通。

有时候我们会使用多个字段的组合索引,如果查询条件中第一个字段不能使用索引,那整个查询也不能使用索引

如:我们company表建了一个id+name的组合索引,以下SQL是不能使用索引的

Select * from company where name=?

Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题,但是通过index skip scan提高性能的条件比较特殊,使用不好反而性能会更差。

 

 

我们一般在什么字段上建索引?

这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

1、字段出现在查询条件中,并且查询条件可以使用索引;

2、语句执行频率高,一天会有几千次以上;

3、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

小表(记录数小于10000行的表):筛选比例<10%;

大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

      单条记录长度≈字段平均内容长度之和+字段数*2

 

以下是一些字段是否需要建B-TREE索引的经验分类:

 

 

 

字段类型

常见字段名

需要建索引的字段

主键

ID,PK

外键

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

有对像或身份标识意义字段

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

索引慎用字段,需要进行数据分布及使用场景详细评估

日期

GMT_CREATE,GMT_MODIFIED

年月

YEAR,MONTH

状态标志

PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

类型

ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

区域

COUNTRY,PROVINCE,CITY

操作人员

CREATOR,AUDITOR

数值

LEVEL,AMOUNT,SCORE

长字符

ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

不适合建索引的字段

描述备注

DESCRIPTION,REMARK,MEMO,DETAIL

大字段

FILE_CONTENT,EMAIL_CONTENT

 

 

如何知道SQL是否使用了正确的索引?

简单SQL可以根据索引使用语法规则判断,复杂的SQL不好办,判断SQL的响应时间是一种策略,但是这会受到数据量、主机负载及缓存等因素的影响,有时数据全在缓存里,可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用,需要到数据库中查看SQL真实的执行计划,这个话题比较复杂,详见SQL执行计划专题介绍。

 

索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?

这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。

 

1 [root@zlm2 06:43:42 ~]
2 #scp -r /data/backup/2018-07-31_06-29-03/ zlm3:/data/backup
3 root@zlm3's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.

1.2、只通过索引访问数据

有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。

如:select id,name from company where type='2';

如果这个SQL经常使用,我们可以在type,id,name上创建组合索引

create index my_comb_index on company(type,id,name);

有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表。

还是拿字典举例:有一个需求,需要查询一本汉语字典中所有汉字的个数,如果我们的字典没有目录索引,那我们只能从字典内容里一个一个字计数,最后返回结果。如果我们有一个拼音目录,那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页,拼音目录有20页,那我们的数据访问成本相当于全表访问的50分之一。

切记,性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里,所以这种只通过索引访问数据的方法一般只用于核心应用,也就是那种对核心表访问量最高且查询字段数据量很少的查询。

 

1.3、优化SQL执行计划

SQL执行计划是关系型数据库最核心的技术之一,它表示SQL执行时的数据访问算法。由于业务需求越来越复杂,表数据量也越来越大,程序员越来越懒惰,SQL也需要支持非常复杂的业务逻辑,但SQL的性能还需要提高,因此,优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外,还需要有一套优秀的算法库来提高SQL性能。

目前ORACLE有SQL执行计划的算法约300种,而且一直在增加,所以SQL执行计划是一个非常复杂的课题,一个普通DBA能掌握50种就很不错了,就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法,但并不表示我们无法优化执行计划,因为我们常用的SQL执行计划算法也就十几个,如果一个程序员能把这十几个算法搞清楚,那就掌握了80%的SQL执行计划调优知识。

由于篇幅的原因,SQL执行计划需要专题介绍,在这里就不多说了。

 

**Step 2. Rescue***

2、返回更少的数据


2.1、数据分页处理

一般数据分页方式有:

 

2.1.1、客户端(应用程序或浏览器)分页

将数据从应用服务器全部下载到本地应用程序或浏览器,在应用程序或浏览器内部通过本地代码进行分页处理

优点:编码简单,减少客户端与应用服务器网络交互次数

缺点:首次交互时间长,占用客户端内存

适应场景:客户端与应用服务器网络延时较大,但要求后续操作流畅,如手机GPRS,超远程访问(跨国)等等。

Restore the backup on zlm3.

TAG标签:
版权声明:本文由澳门金莎娱乐网站发布于数据库,转载请注明出处:SQLServer数据表用法,面向程序员的数据库访问性