今天看书讲T-SQL，看到了UNBOUNDED PRECEDING，就想比对下ROW_NUMBER()的运行速度。sql及相关的结果如下，数据库中的数据有5W+。ROW_NUMBER()： SET STATISTICS TIME ON SELECT   ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id FROM   dbo.T_MyCourse运行结果：              UNBOUNDED PRECEDING SET STATISTICS TIME ON SELECT   SUM(1) OVER(ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id FROM   dbo.T_MyCourse运行结果：            通过运行之后，看到结果，使用微软官方提供的方法进行编号排序，速度明显的提高。不过我使用上述方法进行分页获取数据的时候结果又有点不一样。分页获取数据：ROW_NUMBER() 分页获取数据：SET STATISTICS TIME ON SELECT   * FROM   (   SELECT     ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id   FROM     dbo.T_MyCourse   )a WHERE   a.rn BETWEEN 55 AND 444             执行sql命令：DBCC DROPCLEANBUFFERS ，清除数据库缓存后的结果           UNBOUNDED分页获取数据：SET STATISTICS TIME ON SELECT   * FROM   (   SELECT     SUM(1) OVER(ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id   FROM     dbo.T_MyCourse   )a WHERE   a.rn BETWEEN 22 AND 444           UNBOUNDED这个方式下执行了DBCC DROPCLEANBUFFERS 清除缓存的sql也没有用，执行时间没有变化。通过上述结果，看到ROW_NUMBER()获取分页的数据明显更快，我猜测是微软对ROW_NUMBER()这个方法做了优化，可能是有缓存，读取的缓存中的数据然后进行分页。

sql server 锁定模式有三种：共享（ S锁），更新（U锁），排他（X锁）；S锁是共享锁，如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。个人理解为，对数据A的操作就只能是SELECT ,(联想下，S锁，不就是Select的首字母么），其他事务对A数据的UPDATE ,DELETE都不能进行；U锁是更新锁。用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。原理解释：更新锁       更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。       如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。        第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。      如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。      个人理解，打个比方，A,B都拿把枪指着对方说：”我等你放下枪我就放下枪“，大家都等对方放下枪，于是就这么死僵持着......X锁是排他锁，也叫独占锁，事务对A资源的insert ,update ,delete要用独占，不然的话乱套了；这是为了保护数据再一次操作中的准确性；个人理解就是，一次一个....

MySQL中SQL Mode的查看与设置MySQL可以运行在不同的模式下，而且可以在不同的场景下运行不同的模式，这主要取决于系统变量 sql_mode 的值。本文主要介绍一下这个值的查看与设置，主要在Mac系统下。对于每个模式的意义和作用，网上很容易找到，本文不做介绍。按作用区域和时间可分为3个级别，分别是会话级别，全局级别，配置（永久生效）级别。会话级别：查看-select @@session.sql_mode;修改-set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode'session均可省略，默认session，仅对当前会话有效全局级别：查看-select @@global.sql_mode;修改-set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode';需高级权限，仅对下次连接生效，不影响当前会话（亲测过），且MySQL重启后失效，因为MySQL重启时会重新读取配置文件里对应值，如果需永久生效需要修改配置文件里的值。配置修改（永久生效）：打开 vi /etc/my.cnf在下面添加[mysqld] sql-mode = "xx_mode"注意：[mysqld]必须加，且sql-mode中间是“-”，而不是下划线。保存退出，重启服务器，即可永久生效。因为Mac下安装MySQL没有配置文件，所以需要自己手动添加。ps最后额外加一点东西，就是Mac下MySQL的启动、停止、重启等操作。主要有两种方式，一是点击”系统偏好设置“对应的MySQL面板，可实现管理。二是命令行方式。MySQL相关的执行脚本，常用的主要是下面两个：/usr/local/mysql/support-files/mysql.server /usr/local/mysql/bin/mysqlmysql.server是控制服务器的启停等操作。mysql.server start|stop|restart|statusmysql主要用于连接服务器。mysql -uroot -p **** -h **** -D **有些需要sudo权限，且可将相关路径添加到环境变量，可简化书写，至于如何添加是不做介绍了。知识点扩展：Strict Mode阐述根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制：1).不支持对not null字段插入null值2).不支持对自增长字段插入''值，可插入null值3).不支持 text 字段有默认值看下面代码：(第一个字段为自增字段)$query="insert into demo values('','$firstname','$lastname','$sex')";上边代码只在非strict模式有效。Code代码$query="insert into demo values(NULL,'$firstname','$lastname','$sex')";上边代码只在strict模式有效。把空值''换成了NULL.

MySQL 可以基于多表查询更新数据。对于多表的 UPDATE 操作需要慎重，建议在更新前，先使用 SELECT 语句查询验证更新的数据与自己期望的是否一致。下面我们建两张表，一张表为 product 表，用来存放产品信息，其中有产品价格字段 price；另外一张表是 product_price 表。现要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。操作前先分别查看两张表的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM product; +----+-----------+-----------------------+-------+----------+ | id | productid | productname           | price | isdelete | +----+-----------+-----------------------+-------+----------+ |  1 |      1001 | C语言中文网Java教程   |   100 |        0 | |  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 | |  3 |      1003 | C语言中文网Python教程 |   120 |        0 | |  4 |      1004 | C语言中文网C语言教程  |   150 |        0 | |  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 | +----+-----------+-----------------------+-------+----------+ 5 rows in set (0.02 sec) mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |  NULL | |  2 |      1002 |  NULL | |  3 |      1003 |  NULL | |  4 |      1004 |  NULL | |  5 |      1005 |  NULL | +----+-----------+-------+ 5 rows in set (0.01 sec)下面是 MySQL 多表更新在实践中的几种不同写法。执行不同的 SQL 语句，仔细观察 SQL 语句执行后表中数据的变化，很容易就能理解多表联合更新的用法。1. 使用UPDATE在 MySQL 中，可以使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新，SQL 语句和运行结果如下：mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId; Query OK, 5 rows affected (0.02 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)2. 通过INNER JOIN另外一种方法是使用 INNER JOIN 来多表更新。SQL 语句如下：mysql> UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8; Query OK, 5 rows affected (0.09 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)3. 通过LEFT JOIN也可以使用 LEFT JOIN 来做多表更新，如果 product_price 表中没有产品价格记录的话，将 product 表的 isdelete 字段设置为 1。在 product 表添加 1006 商品，且不在 product_price 表中添加对应信息，SQL 语句如下。mysql> UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL; Query OK, 1 row affected (0.04 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM product; +----+-----------+-----------------------+-------+----------+ | id | productid | productname           | price | isdelete | +----+-----------+-----------------------+-------+----------+ |  1 |      1001 | C语言中文网Java教程   |   100 |        0 | |  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 | |  3 |      1003 | C语言中文网Python教程 |   120 |        0 | |  4 |      1004 | C语言中文网C语言教程  |   150 |        0 | |  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 | |  6 |      1006 | C语言中文网Spring教程 |  NULL |        1 | +----+-----------+-----------------------+-------+----------+ 6 rows in set (0.00 sec)4. 通过子查询也可以通过子查询进行多表更新，SQL 语句和执行过程如下：mysql> UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid); Query OK, 5 rows affected (0.00 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)另外，上面的几个例子都是在两张表之间做关联，只更新一张表中的记录。MySQL 也可以同时更新两张表，如下语句就同时修改了两个表。UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8, p.dateUpdate = CURDATE()两张表做关联，同时更新了 product_price 表的 price 字段和 product 表的 dateUpdate 两个字段。值得一提的是，日常开发中，一般都是用单表 UPDATE 语句，很少写多表关联的 UPDATE。

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。子查询在 WHERE 中的语法格式如下：WHERE <表达式> <操作符> (子查询)其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。1）IN | NOT IN当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。2）EXISTS | NOT EXISTS用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。例 1使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info      -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java'); +-------+ | name  | +-------+ | Dany  | | Henry | +-------+ 2 rows in set (0.01 sec)结果显示，学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。1）首先单独执行内查询，查询出 tb_course 表中课程为 Java 的 id，SQL 语句和运行结果如下。mysql> SELECT id FROM tb_course      -> WHERE course_name = 'Java'; +----+ | id | +----+ |  1 | +----+ 1 row in set (0.00 sec)可以看到，符合条件的 id 字段的值为 1。2）然后执行外层查询，在 tb_students_info 表中查询 course_id 等于 1 的学生姓名。SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info      -> WHERE course_id IN (1); +-------+ | name  | +-------+ | Dany  | | Henry | +-------+ 2 rows in set (0.00 sec)习惯上，外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。例 2与例 1 类似，在 SELECT 语句中使用 NOT IN 关键字，查询没有学习 Java 课程的学生姓名，SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info      -> WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java'); +--------+ | name   | +--------+ | Green  | | Jane   | | Jim    | | John   | | Lily   | | Susan  | | Thomas | | Tom    | | LiMing | +--------+ 9 rows in set (0.01 sec)可以看出，运行结果与例 1 刚好相反，没有学习 Java 课程的是除了 Dany 和 Henry 之外的学生。例 3使用=运算符，在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名，SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info     -> WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python'); +------+ | name | +------+ | Jane | +------+ 1 row in set (0.00 sec)结果显示，学习 Python 课程的学生只有 Jane。例 4使用<>运算符，在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名，SQL 语句和运行结果如下。mysql> SELECT name FROM tb_students_info     -> WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python'); +--------+ | name   | +--------+ | Dany   | | Green  | | Henry  | | Jim    | | John   | | Lily   | | Susan  | | Thomas | | Tom    | | LiMing | +--------+ 10 rows in set (0.00 sec)可以看出，运行结果与例 3 刚好相反，没有学习 Python 课程的是除了 Jane 之外的学生。例 5查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中的记录，SQL 语句和运行结果如下。mysql> SELECT * FROM tb_students_info     -> WHERE EXISTS(SELECT course_name FROM tb_course WHERE id=1); +----+--------+------+------+--------+-----------+ | id | name   | age  | sex  | height | course_id | +----+--------+------+------+--------+-----------+ |  1 | Dany   |   25 | 男   |    160 |         1 | |  2 | Green  |   23 | 男   |    158 |         2 | |  3 | Henry  |   23 | 女   |    185 |         1 | |  4 | Jane   |   22 | 男   |    162 |         3 | |  5 | Jim    |   24 | 女   |    175 |         2 | |  6 | John   |   21 | 女   |    172 |         4 | |  7 | Lily   |   22 | 男   |    165 |         4 | |  8 | Susan  |   23 | 男   |    170 |         5 | |  9 | Thomas |   22 | 女   |    178 |         5 | | 10 | Tom    |   23 | 女   |    165 |         5 | | 11 | LiMing |   22 | 男   |    180 |         7 | +----+--------+------+------+--------+-----------+ 11 rows in set (0.01 sec)由结果可以看到，tb_course 表中存在 id=1 的记录，因此 EXISTS 表达式返回 TRUE，外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询，返回所有的记录。EXISTS 关键字可以和其它查询条件一起使用，条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。例 6查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录，SQL 语句和运行结果如下。mysql> SELECT * FROM tb_students_info     -> WHERE age>24 AND EXISTS(SELECT course_name FROM tb_course WHERE id=1); +----+------+------+------+--------+-----------+ | id | name | age  | sex  | height | course_id | +----+------+------+------+--------+-----------+ |  1 | Dany |   25 | 男   |    160 |         1 | +----+------+------+------+--------+-----------+ 1 row in set (0.01 sec)结果显示，从 tb_students_info 表中查询出了一条记录，这条记录的 age 字段取值为 25。内层查询语句从 tb_course 表中查询到记录，返回 TRUE。外层查询语句开始进行查询。根据查询条件，从 tb_students_info 表中查询 age 大于 24 的记录。拓展子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写。一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。

修改事件在 MySQL 中，事件创建之后，可以使用 ALTER EVENT 语句修改其定义和相关属性。修改事件的语法格式如下：ALTER EVENT event_name     ON SCHEDULE schedule     [ON COMPLETION [NOT] PRESERVE]     [ENABLE | DISABLE | DISABLE ON SLAVE]     [COMMENT 'comment']    DO event_body;另外，ALTER EVENT 语句还有一个用法就是让一个事件关闭或再次让其活动。例 1修改 e_test 事件，让其每隔 30 秒向表 tb_eventtest 中插入一条数据，SQL 语句和运行结果如下所示：mysql> ALTER EVENT e_test ON SCHEDULE EVERY 30 SECOND     -> ON COMPLETION PRESERVE     -> DO INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW()); Query OK, 0 rows affected (0.04 sec) mysql> TRUNCATE TABLE tb_eventtest; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user  | createtime          | +----+-------+---------------------+ |  1 | MySQL | 2020-05-21 13:23:49 | |  2 | MySQL | 2020-05-21 13:24:19 | +----+-------+---------------------+ 2 rows in set (0.00 sec)由结果可以看出，修改事件后，表 tb_eventtest 中的数据由原来的每 5 秒插入一条，变为每 30 秒插入一条。使用 ALTER EVENT 语句还可以临时关闭一个已经创建的事件。例 2临时关闭事件 e_test 的具体代码如下所示：mysql> ALTER EVENT e_test DISABLE; Query OK, 0 rows affected (0.00 sec)查询 tb_eventtest 表中的数据，SQL 语句如下：SELECT * FROM tb_eventtest;为了确定事件已关闭，可以查询两次（每次间隔 1 分钟）tb_eventtest 表的数据，SQL 语句和运行结果如下所示：mysql> TRUNCATE TABLE tb_eventtest; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM tb_eventtest; Empty set (0.00 sec) mysql> SELECT * FROM tb_eventtest; Empty set (0.00 sec)由结果可以看出，临时关闭事件后，系统就不再继续向表 tb_eventtest 中插入数据了。删除事件在 MySQL 中，可以使用 DROP EVENT 语句删除已经创建的事件。语法格式如下：DROP EVENT [IF EXISTS] event_name;例 3删除事件 e_test，SQL 语句和运行结果如下：mysql> DROP EVENT IF EXISTS e_test; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM information_schema.events \G Empty set (0.00 sec)

SQL 注入SQL 注入（SQL Injection）是发生在 Web 程序中数据库层的安全漏洞，是网站存在最多也是最简单的漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。SQL 注入已经成为互联网世界 Web 应用程序的最大风险，我们有必要从开发、测试、上线等各个环节对其进行防范。下面介绍 SQL 注入的原理及避免 SQL 注入的一些方法。SQL注入的原理SQL 注入的原理主要有以下 4 点：1）恶意拼接查询我们知道，SQL 语句可以查询、插入、更新和删除数据，且使用分号来分隔不同的命令。例如：SELECT * FROM users WHERE user_id = $user_id其中，user_id 是传入的参数，如果传入的参数值为“1234; DELETE FROM users”，那么最终的查询语句会变为：SELECT * FROM users WHERE user_id = 1234; DELETE FROM users如果以上语句执行，则会删除 users 表中的所有数据。2）利用注释执行非法命令。SQL 语句中可以插入注释。例如：SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version=$version如果 version 包含了恶意的字符串'-1' OR 3 AND SLEEP(500)--，那么最终查询语句会变为：SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version='-1' OR 3 AND SLEEP(500)--以上恶意查询只是想耗尽系统资源，SLEEP(500) 将导致 SQL 语句一直运行。如果其中添加了修改、删除数据的恶意指令，那么将会造成更大的破坏。3）传入非法参数SQL 语句中传入的字符串参数是用单引号引起来的，如果字符串本身包含单引号而没有被处理，那么可能会篡改原本 SQL 语句的作用。 例如：SELECT * FROM user_name WHERE user_name = $user_name如果 user_name 传入参数值为 G'chen，那么最终的查询语句会变为：SELECT * FROM user_name WHERE user_name ='G'chen'一般情况下，以上语句会执行出错，这样的语句风险比较小。虽然没有语法错误，但可能会恶意产生 SQL 语句，并且以一种你不期望的方式运行。4）添加额外条件在 SQL 语句中添加一些额外条件，以此来改变执行行为。条件一般为真值表达式。例如：UPDATE users SET userpass='$userpass' WHERE user_id=$user_id;如果 user_id 被传入恶意的字符串“1234 OR TRUE”，那么最终的 SQL 语句会变为：UPDATE users SET userpass= '123456' WHERE user_id=1234 OR TRUE;这将更改所有用户的密码。避免SQL注入对于 SQL 注入，我们可以采取适当的预防措施来保护数据安全。下面是避免 SQL 注入的一些方法。1. 过滤输入内容，校验字符串过滤输入内容就是在数据提交到数据库之前，就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤，还可以使用正则表达式匹配安全的字符串。如果值属于特定的类型或有具体的格式，那么在拼接 SQL 语句之前就要进行校验，验证其有效性。比如对于某个传入的值，如果可以确定是整型，则要判断它是否为整型，在浏览器端（客户端）和服务器端都需要进行验证。2. 参数化查询参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值。MySQL 的参数格式是以“?”字符加上参数名称而成，如下所示：UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4在使用参数化查询的情况下，数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理，而是在数据库完成 SQL 语句的编译之后，才套用参数运行。因此就算参数中含有破坏性的指令，也不会被数据库所运行。3. 安全测试、安全审计除了开发规范，还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查，在测试环节使用工具进行扫描，上线后定期扫描安全漏洞。通过多个环节的检查，一般是可以避免 SQL 注入的。有些人认为存储过程可以避免 SQL 注入，存储过程在传统行业里用得比较多，对于权限的控制是有一定用处的，但如果存储过程用到了动态查询，拼接 SQL，一样会存在安全隐患。下面是在开发过程中可以避免 SQL 注入的一些方法。1. 避免使用动态SQL避免将用户的输入数据直接放入 SQL 语句中，最好使用准备好的语句和参数化查询，这样更安全。2. 不要将敏感数据保留在纯文本中加密存储在数据库中的私有/机密数据，这样可以提供了另一级保护，以防攻击者成功地排出敏感数据。3. 限制数据库权限和特权将数据库用户的功能设置为最低要求；这将限制攻击者在设法获取访问权限时可以执行的操作。4. 避免直接向用户显示数据库错误攻击者可以使用这些错误消息来获取有关数据库的信息。一些编程框架对于写出更安全的代码也有一定的帮助，因为它提供了一些处理字符串的函数和使用查询参数的方法。但同样，你仍然可以编写出不安全的 SQL 语句。所以归根到底，我们需要有良好的编码规范，并能充分利用参数化查询、字符串处理和参数校验等多种办法来保护数据库和程序的安全。说明：SQL 注入技术不是单凭一篇文章就可以讲完的，这里只带领大家掌握 SQL 注入的原理及常见的几种防止 SQL 注入的方法。

MySQL创建事件（CREATE EVENT）在 MySQL 中，可以通过 CREATE EVENT 语句来创建事件，其语法格式如下：CREATE EVENT [IF NOT EXISTS] event_name     ON SCHEDULE schedule     [ON COMPLETION [NOT] PRESERVE]     [ENABLE | DISABLE | DISABLE ON SLAVE]     [COMMENT 'comment']     DO event_body;从上面的语法可以看出，CRATE EVENT 语句由多个子句组成，各子句的详细说明如下表所示。子句说明DEFINER可选用于定义事件执行时检查权限的用户IF NOT EXISTS可选用于判断要创建的事件是否存在EVENT event_name必选用于指定事件名称，event_name 的最大长度为 64 个字符如果未指定 event_name，则默认为当前的 MySQL 用户名（不区分大小写）ON SCHEDULE schedule必选用于定义执行的时间和时间间隔schedule 表示触发点ON COMPLETION [NOT] PRESERVE可选用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVEENABLE | DISABLE | DISABLE ON SLAVE可选，用于指定事件的一种属性。其中，关键字 ENABLE 表示该事件是活动的，即调度器检查事件是否必须调用；关键字 DISABLE 表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该调用；关键字 DISABLE ON SLAVE 表示事件在从机中是关闭的。如果不指定以上 3 个选项中的任何一个，默认为 ENABLECOMMENT 'comment'可选，用于定义事件的注释DO event_body必选用于指定事件启动时所要执行的代码，可以是任何有效的 SQL 语句、存储过程或者一个计划执行的事件。如果包含多条语句，则可以使用 BEGIN..END 复合结构在 ON SCHEDULE 子句中，参数 schedule 的值为一个 AT 子句，用于指定事件在某个时刻发生，其语法格式如下：AT timestamp [+ INTERVAL interval]...     | EVERY interval     [STARTS timestamp [+ INTERVAL interval] ...]     [ENDS timestamp[+ INTERVAL interval]...]参数说明如下：timestamp：一般用于只执行一次，表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。EVERY 子句：用于事件在指定时间区间内每隔多长时间发生一次，其中 STARTS 子句用于指定开始时间；ENDS 子句用于指定结束时间。interval：一般用于周期性执行，表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示 4 周，使用“'1:10'HOUR_MINUTE”表示 1 小时 10 分钟。间隔的长短用 DATE_ADD() 函数支配。interval 参数可以是以下值：YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |     WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |     DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND一般情况下，不建议使用不标准（以上未加粗关键字）的时间单位。例 1在 test 数据库中创建一个名称为 e_test 的事件，用于每隔 5 秒向表 tb_eventtest 中插入一条数据。创建 tb_eventtest 表，SQL 语句和运行结果如下：mysql> CREATE TABLE tb_eventtest(     -> id INT(11) PRIMARY KEY AUTO_INCREMENT,     -> user VARCHAR(20),     -> createtime DATETIME); Query OK, 0 rows affected (0.07 sec)创建 e_test 事件，SQL 语句和运行结果如下：mysql> CREATE EVENT IF NOT EXISTS e_test ON SCHEDULE EVERY 5 SECOND     -> ON COMPLETION PRESERVE     -> DO INSERT INTO tb_eventtest(user,createtime)VALUES('MySQL',NOW()); Query OK, 0 rows affected (0.04 sec)创建事件后，查询 tb_eventtest 中的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user  | createtime          | +----+-------+---------------------+ |  1 | MySQL | 2020-05-21 10:41:39 | |  2 | MySQL | 2020-05-21 10:41:44 | |  3 | MySQL | 2020-05-21 10:41:49 | |  4 | MySQL | 2020-05-21 10:41:54 | +----+-------+---------------------+ 4 rows in set (0.01 sec)从结果可以看出，系统每隔 5 秒插入一条数据，这说明事件创建执行成功了。

sql分类：（1）DDL：数据库定义语言，用于定义表，列，索引等数据库对象. create,drop,alter等（2）DML：数据库操纵语言，增删改查数据库的记录insert，delete，update，select等（3）DCL：数据库控制语言，定义了数据库的表，字段，用户的访问权限和安全级别 grant，revoke等2.DDL语句（1）创建数据库:create database test1;（2）删除数据库：drop database test1；（3）创建表：create table user(name varchar(10),age int(2));（4）删除表：drop table user;（5）修改表：alter table user modify name varchar(20);         alter table user add column sex varchar(10);        alter table user drop column age;         Alter table user change age age1 int(4);     Alter table user rename user1;3.DML语句（1）插入记录insert into user(name,age)values(‘zhangsan’,18);（2）删除记录：delete from user where name=’张三’（3）更新记录：update user set age=1 where name=’ 张三’（4）查询记录：select * from user;（5）查询不重复的记录j：select distinct name from user;（6）排序查询：select from user order by age desc;（7）限制排序：select * from user order by age limit 1,30;（8）聚合：select name,age from user group by age;（9）聚合后条件：select name,age from user group by age having age > 18;（having和where的区别：having是聚合后条件，where是聚合前条件）（10）表连接： ①内连接：Select ename,deptname from emp,dept where emp.deptno=dept.deptno;②外连接：左连接：（查询 emp 中所有用户名和所在部门名称：） select ename,deptname from emp left join dept on emp.deptno=dept.deptno; 右连接：select ename,deptname from emp left join dept on emp.deptno=dept.deptno;· 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录· 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录（11）子查询：需要的条件是另一个select的结果（子查询的关键字主要包括in, not in,=,!=,exists,not exists ）（如果查询结果数唯一，可用=代替in）select * from emp where deptno in(select deptno from dept);select * from emp where deptno = (select deptno from dept limit 1);（12）记录联合：select * from t1 union select * from t2select * from t1 union all select * from t2（union和union all的区别：union是将union all查询出来的结果进行了一次distinct，去除重复后的结果）4.DCL语句（1）授权：创建一个数据库用户 z1，具有对 sakila 数据库中所有表的 SELECT/INSERT 权限：grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';（2）收回权限：由于权限变更，需要将 z1 的权限变更，收回 INSERT，只能对数据进行 SELECT 操作：revoke insert on sakila.* from 'z1'@'localhost';mysql支持的数据类型数字类型，字符串类型，日期和时间类型1.数值类型2.字符串类型3.日期类型mysdql中的运算符（1）算数运算符（2）比较运算符（3）逻辑运算符（4）位运算符mysql中常用的函数1.字符串函数2.数值函数3.日期时间函数4.其它常用函数表类型（存储引擎的选择）1.查询当前数据库支持的存储引擎Show engines \G2.Mysql常见存储引擎对比    (Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB)（1）MyisamMysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDBMyisam不支持事务和外键优势是访问速度快（2）InnodbInnodb提供事务和外键占用磁盘空间保留索引，写的效率比innodb差（3）Memory数据存放在内存中访问速度非常快（使用hash索引）但服务一旦关闭，表中的数据丢失

存储过程的概念存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。存储过程的优点                        A、 存储过程允许标准组件式编程                                存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。                                而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，                                从而极大的提高了程序的可移植性。                       B、  存储过程能够实现较快的执行速度                                如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。                                因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。                                而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。                      C、   存储过程减轻网络流量                              对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，                               那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。                               从而减轻了网络流量，降低了网络负载。                       D、 存储过程可被作为一种安全机制来充分利用                               系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，                               避免非授权用户对数据的访问，保证数据的安全。  重点给大家介绍SqlServer存储过程定义的三种方法，一起看看吧！第一种：declare @p_text varchar(max) SELECT @p_text= text  FROM syscomments  WHERE id = ( SELECT id FROM sysobjects WHERE name = ' 存储过程名称 ') print @p_text但当存储过程内容比较长的时候，text的内容显示不完全。第二种：declare @p_text varchar(max) SELECT @p_text= definition FROM sys.sql_modules  JOIN sys.objects ON sys.sql_modules.object_id=sys.objects.object_id --and type='P'  and sys.objects.name='sp_MSupd_dboInd_IndependenceReportTo' print @p_text内容格式良好，但是Print本身不能显示超过 8000字节的内容，所以存储过程内容太长这种方式还是不行。第三种：exec sp_helptext '[sp_MSupd_dboRpt_Customer]'返回定义的内容但是一个文本行内容一个记录行，这样就可以解决上面的不足了。

Transact-SQLTransact-SQL（又称 T-SQL），是在 Microsoft SQL Server 和 Sybase SQL Server 上的 ANSI SQL 实现，与 Oracle 的 PL/SQL 性质相近（不只是实现 ANSI SQL，也为自身数据库系统的特性提供实现支持），在 Microsoft SQL Server 和 Sybase Adaptive Server 中仍然被使用为核心的查询语言。数据库1、创建数据库USE master ;  GO  CREATE DATABASE Sales  ON ( NAME = Sales_dat,   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',   SIZE = 10,   MAXSIZE = 50,   FILEGROWTH = 5 )  LOG ON ( NAME = Sales_log,   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',   SIZE = 5MB,   MAXSIZE = 25MB,   FILEGROWTH = 5MB ) ;  GO2、查看数据库SELECT name, database_id, create_date  FROM sys.databases ;3、删除数据库DROP DATABASE Sales;表1、创建表CREATE TABLE PurchaseOrderDetail  (   ID uniqueidentifier NOT NULL  ,LineNumber smallint NOT NULL  ,ProductID int NULL  ,UnitPrice money NULL  ,OrderQty smallint NULL  ,ReceivedQty float NULL  ,RejectedQty float NULL  ,DueDate datetime NULL );2、删除表DROP TABLE dbo.PurchaseOrderDetail;3、重命名表EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';列1、添加列ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;2、删除列ALTER TABLE dbo.doc_exb DROP COLUMN column_b;3、重命名列EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';约束1、主键--在现有表中创建主键 ALTER TABLE Production.TransactionHistoryArchive  ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);   --在新表中创建主键 CREATE TABLE Production.TransactionHistoryArchive1  (   TransactionID int IDENTITY (1,1) NOT NULL   , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  ) ;   --查看主键  SELECT name FROM sys.key_constraints  WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';  GO  --删除主键 ALTER TABLE Production.TransactionHistoryArchive  DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;  GO视图1、创建视图CREATE VIEW V_EmployeeHireDate  AS SELECT p.FirstName, p.LastName, e.HireDate  FROM HumanResources.Employee AS e JOIN Person.Person AS p  ON e.BusinessEntityID = p.BusinessEntityID ;  GO2、删除视图DROP VIEW V_EmployeeHireDate;存储过程1、创建存储过程CREATE PROCEDURE P_UspGetEmployeesTest   @LastName nvarchar(50),   @FirstName nvarchar(50)  AS  SELECT FirstName, LastName, Department   FROM HumanResources.vEmployeeDepartmentHistory   WHERE FirstName = @FirstName AND LastName = @LastName   AND EndDate IS NULL;  GO2、删除存储过程DROP PROCEDURE P_UspGetEmployeesTest;3、执行存储过程EXEC P_UspGetEmployeesTest N'Ackerman', N'Pilar';  -- Or  EXEC P_UspGetEmployeesTest @LastName = N'Ackerman', @FirstName = N'Pilar';  GO  -- Or  EXECUTE P_UspGetEmployeesTest @FirstName = N'Pilar', @LastName = N'Ackerman';  GO4、重命名存储过程EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2';5、带有输出参数的存储过程CREATE PROCEDURE P_UspGetEmployeeSalesYTD  @SalesPerson nvarchar(50),  @SalesYTD money OUTPUT  AS   SELECT @SalesYTD = SalesYTD   FROM SalesPerson AS sp   JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID   WHERE LastName = @SalesPerson;  RETURN  GO   --调用 DECLARE @SalesYTDBySalesPerson money;  EXECUTE P_UspGetEmployeeSalesYTD   N'Blythe',   @SalesYTD = @SalesYTDBySalesPerson OUTPUT;  GO数据类型                                

由于工作需要，需要把数据把列根据指定的内容拆分数据其中一条数据实例 select id , XXXX FROM BIZ_PAPER where  id ='4af210ec675927fa016772bf7dd025b0'  拆分方法：select t3.id ,t3.XXXX as XXXX from ( select A.id , B.XXXX from ( SELECT id, XXXX = CONVERT(xml,'<root><v>' + REPLACE(XXXX , ',', '</v><v>') + '</v></root>') FROM BIZ_PAPER) A outer apply( SELECT XXXX = N.v.value('.', 'varchar(100)') FROM A.XXXX .nodes('/root/v') N(v)) B) t3 where t3.id ='4af210ec675927fa016772bf7dd025b0'结果在自己研究这行代码之后，做出如下讲解，如果有错误的地方还请指教SELECT id, XXXX = CONVERT(xml,'<root><v>' + REPLACE(XXXX , ',', '</v><v>') + '</v></root>') FROM BIZ_PAPER这一行的重点在于CONVERT，XML是指类型，xml 数据类型实例拆分为关系数据，则 nodes() 方法非常有用，至于XML类型的数据，我后面进行补充REPLACE 指按照 ' , ' 进行替换，并且按照指定的内容进行拼接最后的结果为outer apply这个就是表的关联，就像是left join ，但是没有on 作为关联条件，所以通过拆分之后多出来的数据就是通过这个进行关联后产生的SELECT XXXX = N.v.value('.', 'varchar(100)') FROM A.XXXX .nodes('/root/v') N(v)N.v.value('.','varchar(100)')，N是表，别名，v是列，value函数是读取标签之间的值，对于这个列子，读取的为<v>和</v>中间的值；这个可以去了解 xml类型的常用的三个方法 ：value()、nodes()、exist()            value的第一个参数是一个字符串文字，从 XML 实例内部检索数据。            XQuery 必须最多返回一个值。 否则，将返回错误；value的第二个参数是指将查询结果转化为何种类型的数据。此处，'.'表示当前目录，即<v>目录，另外'..'表示上级目录，'/'表示根目录，这个跟Linux是一样的总的来说，这个语句的重点在于xml类型的使用和outer apply的使用，其他的都很好理解。这个就是我自己理解后的讲解，部分位置我自己也还没有理解透

什么样的列必须要创建索引？ 当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL 查询性能。当然了，如果表只有几百 条数据，那我们就不用创建索引了。 什么样的列需要收集直方图？ 当列出现在 where条件中，列的选择性小于1%并且该列没有收 集过直方图，这样的列就应该收集直方图。注意： 千万不能对没有出现在where条件中 的列收集直方 图。对没有出现在where条件中的列收集直方图完 全是做无用功，浪费数据库资源。 执行计划中Rows是真的吗？ 执行计划里面的Rows是 假的。执行计划中的Rows是根据统计信息以及一些数学公式计算出来的。 在做SQL优化的时候，经常需要做的工作就是 帮助CBO计算出比较准确的Rows。注意：我们说 的是比较准确的Rows。CBO是无法得到精确的 Rows 的，因为对表收集统计信息的时候，统计信息 一般都不会按照100%的标准采样收集，即使按照 100%的标准采样收集了表的统计信息，表中的数 据也 随时在发生变更。另外计算Rows的数学公式目 前也是有缺陷的，CBO永远不可能计算得到精确的 Rows。怎么快速找到执行计划的入口？ 回答：我们可以利用光标移动**，先将光标放在Id=0这一步，然后一直向下向右 移动光标，直到找到没有儿子的Id，这个Id就是执行计划的入口。 怎么判断是哪个表与哪个表进行关联的？ 回答：我们先找到表在执行计划中的Id，然后看这个Id（或者是这个Id的父亲）与 谁对齐（利用光标上下移动），它与谁对齐，就与谁进行关联。为什么有时候索引扫描比全表扫描更慢？ 假设一个表有100万行数据，表的段大小为1GB。如果对表进行全表扫描，最理想的情况下，每次I/O都读取1MB数据（128个块），将1GB的表从磁盘读 入buffer cache需要1 024次I/O。在实际情况中，表的段前16个extent，每个extent都只有8个块，每次I/O只能读取8个块，而不是128个块，表中有部分 块会被缓存在buffer cache中，会引起I/O中断，那么将1GB的表从磁盘读入buffer cache可能需要耗费1 500次物理I/O。 从表中查询5万行数据，走索引。假设一个索引叶子块能存储100行数据，那么5万行数据需要扫描500个叶子块（单块读），也就是需要500次物理 I/O，然后有5万条数据需要回表，假设索引的集群因子很小（接近表的块数），假设每个数据块存储50行数据，那么回表需要耗费1 000次物理I/O（单 块读），也就是说从表中查询5万行数据，如果走索引，一共需要耗费大概1 500次物理I/O。如果索引的集群因子较大（接近表的总行数），那么回表 要耗费更多的物理I/O，可能是3 000次，而不是1000次。 走索引返回的数据越多，需要耗费的I/O次数也就越多，因此，返回大量数据应该走全表扫描或者是INDEX FAST FULLSCAN，返回少量数据才走索引 扫描。根据上述理论，我们一般建议返回表中总行数5%以内的数据，走索引扫描，超过5%走全表扫描。两表关联走不走NL是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少？ 回答：如果两个表是1∶N关系，驱动表为1，被驱动表为N并且N很大，这时即使驱动表返回数据量很少，也不能走嵌套循环，因为两表关联之后返回的 数据量会很多。所以判断两表关联是否应该走NL应该直接查看两表关联之后返回的数据量，如果两表关联之后返回的数据量少，可以走NL；返回的数 据量多，应该走HASH连接。 此外，还需要关注被驱动表的过滤条件和是否需要回表 大表是否可以当嵌套循环（NL）驱动表？ 回答：可以，如果大表过滤之后返回的数据量很少就可以当NL驱动表。select * from a,b where a.id=b.id; 如果a有100条数据，b有100万行数据，a与b是1∶N关系，N很低，应该怎么优化SQL? 回答：因为a与b是1∶N关系，N很低，我们可以在b的连接列（id）上创建索引，让a与b走嵌套循环（a nl b），这样b表会被扫描100次，但是每次扫描 表的时候走的是id列的索引（范围扫描）。怎么优化HASH连接？ 回答：因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，我们应该尽量避免书写select * from....语句，将需要的列放在select list中， 这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。怎么优化排序合并连接？ 回答：如果两表关联是等值关联，走的是排序合并连接，我们可以将表连接方式改为HASH连接。如果两表关联是非等值关联，比如>，>=，<，<=， <>，这时我们应该先从业务上入手，尝试将非等值关联改写为等值关联，因为非等值关联返回的结果集“类似”于笛卡儿积，当两个表都比较大的时候， 非等值关联返回的数据量相当“恐怖”。如果没有办法将非等值关联改写为等值关联，我们可以考虑增加两表的限制条件，将两个表数据量缩小。当执行计划中有笛卡儿连接应该怎么优化呢？ 首先应该检查表是否有关联条件，如果表没有关联条件，那么应该询问开发与业务人员为何表没有关联条件，是否为满足业务需求而故意不写关联条件。 其次应该检查离笛卡儿连接最近的表是否真的返回1行数据，如果返回行数真的只有1行，那么走笛卡儿连接是没有问题的，如果返回行数超过1行，那就需要 检查为什么Rows会估算错误，同时要纠正错误的Rows。纠正错误的Rows之后，优化器就不会走笛卡儿连接了。 我们可以使用HINT /+opt_param('_optimizer_mjc_enabled', 'false') / 禁止笛卡儿连接。

嵌套循环 嵌套循环的算法 驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。 在执行计划中，离NESTED LOOPS关键字最近的表就是驱动表。 嵌套循环使用场景 驱动表应该返回少量数据，关联条件的索引命中的数据必须很少。 嵌套循环被驱动表必须走索引。如果嵌套循环被驱动表的连接列没包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大 的时候，SQL 就执行不出结果。 嵌套循环被驱动表走索引只能走INDEXUNIQUE SCAN或者INDEX RANGE SCAN。HASH连接 HASH连接的算法 两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运 算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的workarea），对被驱动表的 连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。 HASH连接的适用场景 哈希连接只支持等值连接。 哈希连接的适用场景就是嵌套循环的不适用场景，即两表连接条件关联的结果集比较大。 Used-Mem表示HASH连接消耗了多少PGA，当驱动表太大、PGA不能完全容纳驱动表时，驱动表就会溢出到临时表空间，进而产生磁盘 HASH连接， 这时候HASH连接性能会严重下降。嵌套循环不需要消耗PGA。 外连接的HASH 对于左外连接，Oracle会选择小表为hash表（执行计划上面的表）。 Oracle如果想改变hash表，需要使用swap_join_inputs提示（leading不起作用）。排序合并连接（SORT MERGE JOIN） 适用场景 排序合并连接主要用于处理两表非等值关联，比如>，>=，<，<=，<>，但是不能用于instr、substr、like、regexp_like关联，instr、substr、like、 regexp_like关联只能走嵌套循环。 如果两表是等值关联，一般不建议走排序合并连接。因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并 连接与HASH连接相比，需要耗费更多的PGA。 算法介绍 排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表，然后从驱动表中取出连接列的值，到已经排好序的被驱动 表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被 驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的workarea）匹配数据。 1.Both relations are sorted on the join attributes. 2.Then, both relations are scanned in the order of the join attributes. Tuples that satisfy the join condition are merged to form the result relation。笛卡儿连接 两个表关联没有连接条件的时候会产生笛卡儿积，这种表连接方式就叫笛卡儿连接。 执行计划中MERGE JOIN CARTESIAN就表示笛卡儿连接。 在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的Rows算为1行（注意必须是1行），这个时候也可能发生笛卡儿连接。标量子查询什么是标量子查询？ 当一个子查询介于select与from之间，这种子查询就叫标量子查询 。标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。标量子查询中子查询的表 连接列也必须包含在索引中。 标量子查询优化建议 尽量避免使用标量子查询，假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。 当SQL里面有标量子查询，我们可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。半连接（SEMI JOIN） 什么是半连接？ 两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists。 in和exists一般情况下都可以进行等价改写。 SQL> select * from dept where deptno in (select deptno from emp); SQL> select * from dept where exists (select 1 from emp where dept.deptno=emp.deptno); 半连接和内连接的等价写法（性能不如半连接） SQL> select d.* from dept d, (select deptno from emp group by deptno) e where d.deptno = e.deptno; 半连接以子查询为驱动，对主表嵌套循环 适用于子查询的数据集非常小并且主表有索引的场景。Oracle hint小技巧 1。对于in子查询可以用/*+ qb_name(a) */定义子查询，然后在提示中用a引用对应的表 2。Oracle会为每个查询块定义一个名字，提示中也可以用这个名字，block名字通过下面这种方式获得 SQL> explain plan for select * from dept where deptno in (select deptno from emp);                                          SQL> select * from table(dbms_xplan.display(null, null,'advanced -projection -outline -predicate'));                                            

ardinalityCardinality表示一个结果集的数据量大小，可以对应执行计划中某一步返回的数据量。Cardinaity represents the number of rows in a row set.Here, the row set can be a base table, a view, or the result of ajoin or GROUP BY operato.NDVNDV(唯一值数)是指列的唯一值数量。组合索引、函数索引也会计算对应的NDV。Selectivity当数据分布不均匀时，了解数据分布非常重要。Cost某个列唯一键(Distinct_Keys）的数量叫作基数。比如性别列，该列只有男女之分，所以这一列基数是2。主键列的基数等于表的总行数。Selectivity指一组谓词（条件）在一个数据集中命中的比例。一般命中5%以内的数据走索引，超过5%的数据使用全表扫描。Cost表示优化器计算出来的SQL执行代价，优化器通过Cost的大小选择最优的执行计划，Cost的绝对值没有太大的意义。直方图（HISTOGRAM)为什么需要直方图?对没有出现在where条件中的列收集直方图没有意义，浪费数据库资源。直方图记录什么数据?直方图是用来帮助CBO在对NDV很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows 。当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图。执行图是把每个列的分组数据记录到数据字典中: select owner,count(*) from test group by owner;当NDV小于桶数时，为频率直方图，否则为高度直方图。1）NDV比较小，重复值比较多，查询的值实际不存在SQL> select CATEGORY, count(*) from PBI_EDITION t group by CATEGORY;SQL> select * from pbi_edition_test where category = '402';2）如果使用绑定参数3）当NDV很高时如果支持变量窥视，可以根据实际参数值生成执行计划，否则一般按照1NDV估计。Oracle对应特性为自适应游标共享。由于直方图的桶数有限（一般为256个)，当NDV很高，数据又不均衡，可能估算的值与实际情况差距较大虽然查询的是一个不存在的值，优化器估计的Rows不是0，而是1/NDV(不同数据库实现可能不同)回表: TABLE ACCESS BY INDEX ROWID什么是回表?当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。如果只访问索引列或者使用组合索引可以避免回表。回表的重要性回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。在进行SQL优化的时候，一定要注意回表次数!特别是要注意回表的物理lO次数!小技巧 SQLPLUS的参数arraysize表示Oracle服务器每次传输多少行数据到客户端，默认为15。如果一个块有150行数据，那么这个块就会被读10次，因为每次只传 输15行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。 SQL> show arraysize; arraysize 15 SQL> set arraysize 5000聚集因子（CLUSTERING FACTOR）  什么是聚集因子 聚集因子表示一个索引对应数据在表中的顺序性，它的大小介于表的块数和表行数之间。 如果聚集因子与块数接近，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样，回表只需要读取少量的数据块就能完成。 如果聚集因子与表记录数接近，说明表的数据和索引顺序差异很大，回表会读取更多的数据块。 聚集因子如何计算  首先我们比较2、3 对应的ROWID是否在同一 个数据块，如果在同一个数据块， Clustering Factor +0；如果不在同一个数据块，那么Clustering Factor 值加1。 然后我们比较3、4对应的ROWID是否在同一 个数据块，如果在同一个数据块， Clustering Factor 值不变；如果不在同一个数据块，那么Clustering Factor值加1。 像上面步骤一样，一直这样有序地比较下去， 直到比较完索引中最后一个键值。 根据算法我们知道集群因子介于表的块数和表 行数之间  聚集因子的影响 聚集因子只会影响索引范围扫描（INDEX RANGE SCAN）以及索引全扫描（INDEX FULL SCAN），因为只有这两种索引扫描方式会有大量数据回表。 扫描同样的数据量，通过不同的索引性能差距可能很大，聚集因子是一个重要因素。 CBO在计算cost时，也会考虑聚集因子的大小。 如果降低聚集因子的影响 1）重建表，按照某个索引列的顺序 2）通过组合索引避免回表  3）使用Oracle的cluster table 4）增大buffer cache将大部分数据缓存到内存或者使用内存表 6统计信息 什么是统计信息 收集统计信息是为了让优化器选择最佳执行计划，以最少的代价（成本）查询出表中的数据。 统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。 统计信息的内容表的统计信息主要包含表的总行数（num_rows）、表的块数（blocks）以及行平均长度（avg_row_len）。 列的统计信息主要包含列的NDV、列中的空值数量以及列的数据分布情况（直方图）。 索引的统计信息主要包含索引blevel（索引高度-1）、叶子块的个数（leaf_blocks）以及聚集因子（clustering_factor）。收集统计信息重要参数设置 Granularity：表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO，表示让Oracle根据分区类型自己判断如何收集分区表的统计信息。estimate_percent：表示采样率，范围是0.000 001～100。一般情况下，为了确保统计信息比较准确，建议采样率不低于30%。method_opt ：用于控制收集直方图策略。method_opt => ‘for all columns size 1’表示所有列都不收集直方图。method_opt => ‘for all columns size auto'表示对出现在where条件 中的列自动判断是否收集直方图。no_invalidate：表示共享池中涉及到该表的游标是否立即失效，默认值为DBMS_STATS.AUTO_INVALIDATE，表示让Oracle自己决定是否立即失效。我们建议将no_invalidate参 数设置为FALSE，立即失效。Degree：表示收集统计信息的并行度，默认为NULL。如果表没有设置degree，收集统计信息的时候后就不开并行；如果表设置了degree，收集统计信息的时候就按照表的 degree来开并行。 Cascade：表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让Oracle自己判断是否级联收集索引的统计信息。 我们一般将其设置为TRUE，在收集表的统计信息的时候，级联收集索引的统计信息