SQL(Structure Query
Language)语言是数据库的核心语言,主要介绍其中3大类:数据定义语言DDL,数据操纵语言DML,数据控制语言DCL。

   SQL Server 其实从SQL Server
2005开始,也提供了类似ORACLE中固定执行计划的功能,只是好像很少人使用这个功能。当然在SQL
Server中不叫”固定执行计划”这个概念,而是叫”执行计划指南”(Plan Guide
很多翻译是计划指南,个人觉得执行计划指南稍好一些)。当然两者虽然概念与命名不同,实质上它们所说的是相同的事情,当然商业包装是很常见的事情。个人还是觉得“固定执行计划”这个概念叫起来顺口,通俗易懂,执行计划指南(Plan
Guide)叫起来老感觉非常拗口,不知所云(后面会在这两个概念切换,你知道我所说的是一件事情就好)。其实我以前也很少使用这些功能,直到最近在SQL
Server
2014数据库中使用固定执行计划解决了几个SQL的性能问题,所以觉得还是有必要总结、归纳一下。

图片 1

  DDL:数据定义语言,主要用于管理数据库组件,例如表、索引、视图、用户、存储过程,命令有CREATE、ALTER、DROP等;
  DML:数据操纵语言,主要用管理表中的数据,实现数据的增、删、改、查,命令有INSERT,
DELETE, UPDATE, SELECT等;
  DCL:数据控制语言,用来授予或回收访问数据库的某种特权,命令有GRANT,
REVOKE等;

 

约束的种类

  • 主键约束   primary key

这个字段的值必须满足非空且该字段的值不能重复

一张表中只能有一个主键

 

  • 唯一性约束   unique

这个字段的值不能重复

一张表中的唯一性约束个数不受限制

不能检查NULL值

 

  • 非空约束    not null

这个字段的值不能是NULL值

一张表中的非空约束个数不受限

 

  • 检查约束    check(条件)

字段的值必须符合检查条件(条件由自己指定,where里写的这里都可以写)

 

  • 外键约束    references(参考/ 关联/ 引用)   

建立两张表的关联关系

          foreign key(外键)   指定字段

on  delete  cascade      级联删除

on delete  set  nill   级联置空

DDL

 DDL是对数据库内部的对象进行创建、删除、修改的操作语言,它和DML语言的最大区别是:DML
只是对表内部数据的操作,而不涉及到表的定义、结构的修改。

  • 创建数据库

    CREATE {DATABASE | SCHEMA} [IF
NOT EXISTS] db_name;

    注意:创建数据库时可以指明字符集跟排序规则:
      [DEFAULT] CHARACTER SET [=] charset_name (指明字符集)
      [DEFAULT] COLLATE [=] collation_name (排序规则)
      查看支持的所有字符集:SHOW CHARACTER SET
      查看支持的所有排序规则:SHOW COLLATION

    示例:CREATE DATABASE IF NOT EXISTS students CHARACTER SET
utf8 COLLATE utf8_general_ci; (创建字符集为utf8的students数据库)
      SHOW CREATE DATABASE students;
(查看自己创建的数据库相关信息)
      SHOW DATABASES; (显示所有数据库)
      USE students; (使用students数据库)

  • 修改数据库

    ALTER {DATABASE | SCHEMA}
[db_name];

      [DEFAULT] CHARACTER SET [=] charset_name;
      [DEFAULT] COLLATE [=] collation_name;

*    示例:*ALTER DATABASE students CHARACTER SET gbk COLLATE
gbk_chinese_ci; (修改数据库字符集)

  • 删除数据库

    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

    示例:DROP DATABASE IF EXISTS students; (删除数据库)

  • 查看数据库

    SHOW DATABASES LIKE  ”;

    示例:SHOW DATABASES LIKE ‘%db%’;
(查看数据库名中包含’db’的数据库)

  • 创建表

    CREATE TABLE [IF NOT EXISTS]
tbl_name (create_defination) [table_options]

    其中create_defination可以包含字段、键、索引:
      字段:col_name data_type
      键:
        PRIMARY KEY (col1, col2, …)
        UNIQUE KEY (col1, col2,…)
        FOREIGN KEY (column)
      索引:
        KEY|INDEX [index_name] (col1, col2, …)
    其中table_options可以包含存储引擎:
      ENGINE [=] engine_name
      查看数据库支持的所有存储引擎类型:mysql> SHOW
ENGINES;

      查看某表的存储引擎类型:mysql> SHOW TABLE STATUS
[LIKE] tbl_name;

    示例:CREATE TABLE students(id int auto_increment,name
varchar(20) not null,age tinyint unsigned,sex tinyint(1) not null
default 1,subject_name varchar(20) not null,subject_no smallint not
null,primary key(id)); (创建students表)
      SHOW TABLES; (查看数据库中所有的表)
      DESC students; (查看students表结构)
      SHOW TABLE STATUS like ‘students’\G; (查看表信息)

  • 修改表 

    ALTER [ ONLINE|OFFLINE]
[IGNORE] TABLE tbl_name
[alter_specification[,alter_specification] …]

    其中 alter_specification:
      字段:
        添加:ADD [COLUMN] col_name data_type [
FIRST|AFTER col_name ]

        删除:DROP [COLUMN] col_name
        修改:
          CHANGE [COLUMN] old_col_name new_col_name
column_definition [ FIRST|AFTER col_name ]

          MODIFY [COLUMN] col_name column_definition [
FIRST|AFTER col_name]

        键:
          添加:ADD { PRIMARY|UNIQUE|FOREIGN } KEY
(col1,col2,…)

          删除:
            主键:DROP PRIMARY KEY
            外键:DROP FOREIGN KEY fk_symbol
        索引:
          添加:ADD { INDEX|KEY } [index_name]
(col1,col2,…)

          删除:DROP { INDEX|KEY } index_name
          查看表上的索引的信息:mysql> SHOW INDEXES FROM
tbl_name;

        表选项:
*          ENGINE [=] engine_name

  •           RENAME new_tbl_name

      示例:ALTER TABLE students RENAME student;
(重命名表)
        ALTER TABLE student ADD subject_score smallint;
(添加subject_score字段)
        ALTER TABLE student DROP age; (删除age字段)
        ALTER TABLE student modify subject_score smallint not
null; (修改subject_score字段数据类型)
        ALTER TABLE student CHANGE sex gender tinyint(1) not
null default 1; (变更sex字段为gender字段)
        ALTER TABLE student ADD index id_name (id,name);
(添加索引信息id_name)
        SHOW INDEXES from student\G; (查看student表索引信息)

  •  删除表

    DROP TABLE [IF EXISTS]
tbl_name[,tbl_name] …

    示例:DROP TABLE IF EXISTS student; (删除表)

  • 索引管理
    • 创建索引

      CREATE [
UNIQUE|FULLTEXT|SPATIAL ] INDEX index_name [ BTREE|HASH ] ON
tbl_name (col1,col2, …)

        示例:CREATE index id_name ON student(id,name);
(创建id_name索引)

    • 删除索引

      DROP INDEX index_name ON
tbl_name

        示例:DROP index id_name ON student;
(删除索引id_name)

为什么要固定执行计划?

 约束的实现

  • 列级约束

在建立表时,直接在表的某一列之后加约束限制

  • 表级约束

在建立(定义)完表的所有列之后,再选择某些列加约束限制

DML

DML
操作是指对数据库中表记录的操作,主要包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select)。

  • 插入数据

  INSERT [INTO] tbl_name [(col1,
…)] { VALUES|VALUE }(val1, …),(…),…

  注意:字符型数据要加引号,数值型数据不能加引号。

  示例:INSERT INTO student values(1,”Will”,1,”C++”,0001,70);
    INSERT INTO student
VALUE(2,”Will”,1,”C”,0002,60),(3,”Walter”,1,”C++”,001,80);
    INSERT INTO
student(name,subject_name,subject_no,subject_score)
VALUES(“Alex”,”C#”,0003,75),(“Walter”,”C”,0002,67);
    INSERT INTO
student(name,gender,subject_name,subject_no,subject_score)
VALUES(“Alice”,0,”C++”,0001,86),(“Rose”,0,”C#”,0003,82);

  • 更新数据

  UPDATE [LOW_PRIORITY] [IGNORE]
tbl_name SET col_name1=value1[,col_name2=value2]…[WHERE
where_condition] [ORDER BY…] [LIMIT row_count]

  示例:UPDATE student set subject_no=2 WHERE id=4;
(修改id=4的数据的subject_no字段数值为2)
    SELECT * FROM student ORDER BY subject_score desc;
(降序查看表内容)
    SELECT * FROM student ORDER BY subject_score;
(默认升序查看表内容)

  • 查看数据
    • SELECT * FROM tbl_name;
    • SELECT col1,col2,… FROM tbl_name;
      (注意:字段可以显示为别名,col_name AS col_alias)
    • SELECT col1,… FROM tbl_name WHERE clause;  (注意:WHERE
      clause用于指明挑选条件,如 age > 30)

      • 常用的操作符有:>, <, >=, <=, ==, !=, and, or,
        not, BETWEEN…AND…, LIKE ‘PATTERN’, IS NULL, IS NOT NULL
      • 通配符:%,任意长度的任意字符;_:任意单个字符;RLIKE
        ‘PATTERN’:正则表达式对字符串做模式匹配
    • SELECT col1,…FROM tbl_name [WHERE clause] ORDER BY
      col_name,col_name2,…[ASC|DESC];(ASC:升序;DESC: 降序)
  • 删除数据

  DELETE FROM tbl_name [WHERE
where_condition] [ORDER BY …] [LIMIT row_count]

  示例:DELETE FROM student WHERE subject_score<=70;
(删除subject_score<=70的数据)

 

 主键的列级约束实现

create table testcolumn_cons(id primary key, name varchar2(30));

图片 2

 

insert into testcolumn_cons values(1, 'test1');

图片 3

出错:00001,唯一性错误

如果不给约束起名字,则系统会自动为约束建立一个唯一的名字(但该名字比较难以理解)

 

DCL

用于控制不同数据段直接的许可和访问级别的语句,定义了数据库、表、字段、用户的访问权限和安全级别。

  • 创建用户

  CREATE USER ‘username’@’host’
[IDENTIFIED BY ‘password’];

  示例:CREATE USER ‘test’@’192.168.4.%’ IDENTIFIED BY ‘123456’;
(创建只允许)
    SELECT User,Host,Password FROM mysql.user; (查看用户)

  • 删除用户

  DROP USER ‘user’@’host’
[,user@host]…

  示例:DROP USER ‘test’@’192.168.4.%’;

  • 授权

  GRANT priv_type,… ON
db_name.tbl_name TO ‘user’@’host’ [IDENTIFIED BY
‘password’];

  其中 priv_type: ALL[PRIVILEGES]
    db_name.tbl_name:
        \.*:所有库的所有表         db_name.*:指定库的所有表         db_name.tbl_name:指定库的特定表         db_name.routine_name:指定库上的存储过程或存储函数   查看指定用户所获得的授权:       SHOW GRANTS FOR CURRENT_USER;*

  示例:GRANT ALL ON studb.student to ‘test’@’192.168.4.%’;
(授权192.168.4网段的test用户所有操纵studb数据库的student表的权限)
    SHOW GRANTS FOR ‘test’@’192.168.4.%’; (查看test用户的授权请看)

  • 回收权限

  REVOKE priv_type,… ON
db_name.tbl_name FROM ‘user’@’host’;

  注意:MariaDB服务进程启动时,会读取mysql库的所有授权表至内存中:
    (1)
GRANT或REVOKE命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表,权限修改会立即生效;

    (2) 其它方式实现的权限修改,要想生效,必须手动运行FLUSH
PRIVILEGES命令

*  示例:*REVOKE DELETE,UPDATE ON studb.student FROM
‘test’@’192.168.4.%’; (回收test用户对student表的delete,update权限)

 

为什么要使用固定执行计划(Plan Guid)呢?
个人简单的从下面几个方面介绍一下,如有不足,敬请指正。个人也是在探索当中。

如何给约束起名字(出错判断时,知道是哪一个出错了)

constraint(关键字)  约束名(表名_加约束的字段名_约束类型)

create table testcolumn_cons(id number constraint testcolumn_cons_idpl primary key, name varchar2(30));

 图片 4

这样出错时就知道是哪里出了错(起名要有规律)

 

主键的列级约束

由于一些特殊原因(例如Parameter
Sniffing、统计信息的变化或采样比例低造成的统计信息出现偏差、或其他像SQL
Server 2014新的基数评估(Cardinality
Estimator)特性引起优化器选择不合适的JOIN操作等等),导致某个SQL的执行计划出现很大偏差,当数据库优化器为SQL选择了一个糟糕的执行计划时,就可能出现严重性能问题,我就碰到过这样一个例子,在SQL
Server
2014中,有一个SQL的执行频率较频繁,有时候优化器突然选择了一个较差的执行计划时,这时就会出现严重的性能问题。所以,这个时候,我们就必须使用Plan
Guide固定这个执行计划,从而让优化器使用正确的执行计划,从而解决这样的性能问题。

演示:

  • 建立一张表,建立之前先删表
  • 这张表有id  number是主键    
  • fname  varchar2(30)   要求非空   
  • sname varchar2(30)  要求唯一
  • 要求给约束起名字

  

drop table testcolunm_cons;

图片 5

 

create table testcolumn_cons(
  id number constraint testcolumn_cons_id_pk primary key,
  fname varchar2(30) constraint testcolumn_cons_fname_nn not null,
  sname varchar2(30) constraint testcolumn_cons_sname_un unique
);

图片 6

  

  查看:

desc table testcolumn_cons;

图片 7

增加检查约束:salary  number 检查条件:要求工资大于3500。

 

create table testcolumn_cons(id number constraint testcolumn_cons_id_pk primary key,
  fname varchar2(30) constraint testcolumn_cons_fname_nn not null,
  sname varchar2(30) constraint testcolumn_cons_sname_un unique,
  salary number constraint testcolumn_cons_salary_ck check(salary>3500)
);

图片 8

 

  检测一下: 

insert into testcolumn_cons values(1, 'a', 'b', 3499);

  图片 9

 

主键的表级约束

  • 在约束关键字后面(要约束的字段名),非空不让有表级约束。
  • 定义完所有的字段和类型后,加“,”再加上表级约束。

另外一方面,因为优化器生成执行计划本身是很复杂的过程,我们所能干涉的不多,最多使用HINT提示来改变执行计划。而且优化器基于一些算法和开销考虑,也有可能生成的执行计划不是最优执行计划,而Plan
Guid是DBA管理数据库的一件利器,如果你发现了一个比当前更好的执行计划,也能使用执行计划指南固定这个SQL的执行计划。当然这种情况非常、非常少,至少我在生产环境使用得不多。

演示:

  • 建立一张表  建立之前先删表
  • 这张表有id  number 是主键
  • fname varchar2(30)
  • sname varchar2(30)   要求唯一
  • salary number  要求工资大于3500
  • 要求给约束起名字
  • 增加约束,检查约束

    create table testtable_cons(
      id number,
      fname varchar2(30),
      sname varchar2(30),
      salary number,
      constraint testtable_cons_id_pk primary key(id),
      constraint testtable_cons_sname_un unique(sname),
      constraint testtable_cons_salary_ck check(salary>3500),
    );

图片 10

not null  没有表级约束  

 

联合约束

在表级约束时,指定约束的字段时,可以约束多个字段

没有“联合非空”的说法,所以非空没有表级约束

有时候,某个系统是购买供应商的,你发现数据库里面有大量几乎相同的SQL解析,然后缓存了,其实你发现这些SQL完全可以只解析一次,完全可以参数化,没有必要大量解析。但是现在供应商没有提供技术支持了,不可能去优化代码里面的SQL语句,那么你也可以使用执行计划指南来帮你解决这个问题。

演示:拿上面的例子再做一次,把id和fname绑成一个主键——联合主键(理论绑多个)                                                                                                     

把id和fname联合起来非空且唯一

这样是只有一个主键的!id+fname是主键

id    fname

1     a

1     b

1     NULL

都可以,id重复了也没关系,因为主键是联合的;

fname是NULL也没关系,联合的不为空就行。

 

create table testtable_cons(
      id number,
  fname varchar2(30),
  sname varchar2(30),
  salary number,
  constraint testtable_cons_id_pk primary key(id),
  constraint testtable_cons_sname_un unique(sname),
  constraint testtable_cons_salary_ck check(salary>3500),
);

图片 11

 

表级约束  vs  列级约束

  • 表级约束:可以做联合约束
  • 列级约束:书写简单直观,无法完成联合约束

实际应用中混着用

唯一性(unique)只能区分非空值,不可以区分空值(NULL)

两个或多个NULL无法区分唯一性,实际中可以再设一个非空就可以了 

 

还有就是使用Plan
Guide来调优,对比不同的执行计划的优劣。当然应该还有一些其它应用场景,只是我没有碰到过而已。

 

如何固定执行计划?

 

Plan
Guide主要用到下面几个存储,关于这些系统存储过程的使用方法、功能介绍,官方文档有详细的介绍。在此就不画蛇添足了。

sys.sp_create_plan_guide,

sys.sp_create_plan_guide_from_handle,

sys.sp_control_plan_guide

下面我们还是看看一些应用场景案例吧!构造一个合适、贴切的例子实在是太花精力和时间,生产环境案例又不能搬出来,我们先来看看官方文档提供的例子吧,如下SQL所示,在测试数据库AdventureWorks2014,该SQL使用Nested
Loop关联两个表

SELECT COUNT(*) AS c

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

  ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';

图片 12

 

假如(注意这里是假设)发现如果这个SQL中,两个表使用MERGE
JOIN的方式,效率更高,那么我们可以使用sp_create_plan_guide来创建执行计划指南(固定执行计划),如下所示

EXEC sp_create_plan_guide 

    @name = N'my_table_jon_guid',

    @stmt = N'SELECT COUNT(*) AS c

FROM Sales.SalesOrderHeader AS h

INNER JOIN Sales.SalesOrderDetail AS d

  ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate >= ''20000101'' AND h.OrderDate <=''20050101'';',

    @type = N'SQL',

    @module_or_batch = NULL,

    @params = NULL,

    @hints = N'OPTION (MERGE JOIN)';

 

那么此时再执行这个SQL时,你就会发现执行计划就会变成Merge Join方式了。
这样好过在SQL Server中使用HINT,为什么呢?
有可能这个SQL是写死在应用程序里面,如果以后这个执行计划变成了一个糟糕的执行计划,维护的成本非常高(一方面如果没有记录,需要耗费精力去定位、查找这段SQL,另外一方面,DBA是没有权限接触这些应用程序代码的,可能需要你沟通、协调开发人员、运维人员。耗费无数的时间、精力…..,还有可能其他接手维护的人不了解情况等等),而使用执行计划指南,那么你查找、禁用、删除这个执行计划指南即可。非常方便、高效,也许你一分钟就能搞定,如果是Hint,说不定处理完,需要几天,想必这样的耗费精力沟通、协调的事情很多人都遇到过。

SELECT COUNT(*) AS c

FROM Sales.SalesOrderHeader AS h

INNER MERGE JOIN Sales.SalesOrderDetail AS d

  ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate >= '20000101' AND h.OrderDate <='20050101';

图片 13

 

另外,我们再来构造一个例子,模拟系统里面出现大量解析的SQL语句的案例,如下所示

USE AdventureWorks2014;

GO

SET NOCOUNT ON;

GO

DROP TABLE TEST

GO

CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(8));

GO

CREATE INDEX PK_TEST ON TEST(OBJECT_ID);

GO

 

DECLARE @Index INT =1;

 

WHILE @Index <= 10000

BEGIN

    INSERT INTO TEST

    SELECT @Index, 'kerry';

   

    SET @Index = @Index +1;

END

GO

UPDATE STATISTICS  TEST WITH FULLSCAN;

GO

 

构造了上面案例后,我们清空该数据库所有缓存的执行计划(仅仅是为了干净的测试环境,避免以前缓存的执行计划影响实验结果),生产环境你不能使用DBCC
FREEPROCCACHE清空所有缓存的执行计划,但是可以用DBCC
FREEPROCCACHE删除特定的执行计划。

DBCC FREEPROCCACHE;

GO

然后我们开始测试我们的例子,假设系统里面有大量类似的SQL语句,数量惊人(我们仅仅测试四个)。如果这个系统是从供应商那里购买的,现在又没有技术支持和Support的人(或者及时有人Support,但是不严重影响使用的情况,人家不想花费精力去优化),没有人协助你优化这些SQL,你又不能将数据库参数“参数化”从简单设置为强制(因为影响太大,而且没有测试,不确定是否带来潜在的性能问题)…..

SELECT * FROM TEST WHERE OBJECT_ID=1;

GO

SELECT * FROM TEST WHERE OBJECT_ID=2;

GO

SELECT * FROM TEST WHERE OBJECT_ID=3;

GO

SELECT * FROM TEST WHERE OBJECT_ID=4;

GO

....................................................................

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注