本文导读：删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行，而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似；但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少。下面介绍SQL中Truncate的用法当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。一、Truncate语法[ { database_name.[ schema_name ]. | schema_name . } ]   table_name [ ; ]参数database_name数据库的名称。schema_name表所属架构的名称。table_name要截断的表的名称，或要删除其全部行的表的名称。二、Truncate使用注意事项1、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 2、DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。3、TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。 4、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。5、TRUNCATE TABLE 不能用于参与了索引视图的表。 6、对用TRUNCATE TABLE删除数据的表上增加数据时，要使用UPDATE STATISTICS来维护索引信息。7、如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。 三、不能对以下表使用 TRUNCATE TABLE1、由 FOREIGN KEY 约束引用的表。（您可以截断具有引用自身的外键的表。）2、参与索引视图的表。3、通过使用事务复制或合并复制发布的表。4、对于具有以上一个或多个特征的表，请使用 DELETE 语句。5、TRUNCATE TABLE 不能激活触发器，因为该操作不记录各个行删除。四、TRUNCATE、Drop、Delete区别1.drop和delete只是删除表的数据(定义),drop语句将删除表的结构、被依赖的约束(constrain)、触发器 (trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态。2.delete语句是DML语言,这个操作会放在rollback segement中,事物提交后才生效;如果有相应的触发器(trigger),执行的时候将被触发。truncate、drop是DDL语言,操作后即 生效,原数据不会放到rollback中,不能回滚,操作不会触发trigger。3.delete语句不影响表所占用的extent、高水线(high watermark)保持原位置不动。drop语句将表所占用的空间全部释放。truncate语句缺省情况下将空间释放到minextents的 extent,除非使用reuse storage。truncate会将高水线复位(回到最初)。4.效率方面:drop > truncate > delete5.安全性:小心使用drop与truncate,尤其是在 没有备份的时候,想删除部分数据可使用delete需要带上where子句,回滚段要足够大,想删除表可以用drop,想保留表只是想删除表的所有数据、 如果跟事物无关可以使用truncate,如果和事物有关、又或者想触发 trigger,还是用delete,如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入、插入数据。6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。7、drop一般用于删除整体性数据 如表，模式，索引，视图，完整性限制等；delete用于删除局部性数据 如表中的某一元组8、DROP把表结构都删了；DELETE只是把数据清掉9、当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.

1.首先新建一个空的解决方案，并添加一个类库，代码如下，编译并生产dllusing System;  using System.Collections.Generic;  using System.Data.SqlTypes;  using System.Linq;  using System.Text;  namespace TEST  {    public class TestTrans    {      [Microsoft.SqlServer.Server.SqlFunction]      public static SqlString GenerateDecryptString(string name)      {        string decode = string.Empty;        decode = string.Format("HELLO WORLD {0}!", name);//DecryptString(dataXML.Value);        SqlString sqlValue = new SqlString(decode);        return sqlValue;      }    }  }2.启用CLR功能默认情况下，SQL Server中的CLR是关闭的，所以我们需要执行如下命令打开CLR：exec sp_configure 'clr enabled',1   reconfigure    Go3.将程序集引用到数据库中CREATE ASSEMBLY testHelloWorld FROM 'C:\TEST.dll'   --('C:/TEST.dll'w为错误写法)4.创建函数CREATE FUNCTION dbo.clrHelloWorld    (      @name as nvarchar(200)    )    RETURNS nvarchar(200)    AS EXTERNAL NAME testHelloWorld.[TEST.TestTrans].GenerateDecryptString5.调用函数SELECT dbo.clrHelloWorld('耿耿')6.执行结果HELLO WORLD  耿耿!

一.SQL Server Row_number函数简介ROW_NUMBER()是一个Window函数，它为结果集的分区中的每一行分配一个连续的整数。 行号以每个分区中第一行的行号开头以下是ROW_NUMBER()函数的语法实例：select *，row_number() over(partition by column1 order by column2) as n from tablename在上面语法中：PARTITION BY子句将结果集划分为分区。 ROW_NUMBER()函数分别应用于每个分区，并重新初始化每个分区的行号。PARTITION BY子句是可选的。如果未指定，ROW_NUMBER()函数会将整个结果集视为单个分区。ORDER BY子句定义结果集的每个分区中的行的逻辑顺序。 ORDER BY子句是必需的，因为ROW_NUMBER()函数对顺序敏感。二.Row_number函数的具体用法1.使用row_number（）函数对结果集进行编号，如：select ROW_NUMBER() over(order by recordid ) as 编号,* from [dbo].[Basal_Anormal_Solution]运行结果：              2.对结果集按照指定列进行分组，并在组内按照指定列排序（如：把班级的总分按照小组进行分组显示，分组后在组内进行从高分到低分的排序）select *,row_number() over(partition by createby order by actionOpeid) as n from [dbo].[Basal_Anormal_Solution]运行结果：         3.对结果集按照指定列去重（如：对学生成绩按小组进行分组显示，结果集中只显示每组中一位最高分的学生）select a.* from (select *,row_number() over(partition by createby order by actionOpeid)  as n from [dbo].[Basal_Anormal_Solution])as a where a.n<2运行结果：        注意：当我们求的是第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。这个时候就要用到另外一个函数，rank()和dense_rank()；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。这个时候我们通常会选择用rank()函数，灵活应用。

索引的分类1、索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、1.1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。1.1.1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。1.1.2、唯一索引：索引列中的值必须是唯一的，但是允许为空值，1.1.3、主键索引：是一种特殊的唯一索引，不允许有空值。1.2、组合索引在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说1.3、全文索引全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，如果感兴趣进一步深入使用它，那么看下面测试该索引时，会给出一个博文，供大家参考。1.4、空间索引空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。具体细节看下面sql 优化和常见索引失效的情况在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考。1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=03.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=205.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 36.下面的查询也将导致全表扫描：select id from t where name like ‘%abc%’7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*28.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=‘abc’–name以abc开头的id应改为:select id from t where name like ‘abc%’9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。11.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)12.很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。18.避免频繁创建和删除临时表，以减少系统表资源的消耗。19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。25.尽量避免大事务操作，提高系统并发能力。26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

【问题描述】配置sql on hadoop 查询报错 ERROR: Login failed on cn_5001, check your principal and keytab【分析过程】从报错信息上分析，是由于CN_5001 在登录 hadoop的时候没有成功，报出来的这个错误。首先怀疑是由于集群的网络原因导致的。通过ping nameNode节点，发现网络是通的。通过telnet nameNode节点，发现端口不通。查看nameNode 节点的防火墙配置：netstat -anp | grep xxxx【解决办法】    打开防火墙端口    

在 MySQL 中，InnoDB 行锁通过给索引上的索引项加锁来实现，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁。InnoDB 支持 3 种行锁定方式：行锁（Record Lock）：直接对索引项加锁。间隙锁（Gap Lock）：锁加在索引项之间的间隙，也可以是第一条记录前的“间隙”或最后一条记录后的“间隙”。Next-Key Lock：行锁与间隙锁组合起来用就叫做 Next-Key Lock。 前两种的组合，对记录及其前面的间隙加锁。默认情况下，InnoDB 工作在可重复读（默认隔离级别）下，并且以 Next-Key Lock 的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock 是行锁与间隙锁的组合，这样，当 InnoDB 扫描索引项的时候，会首先对选中的索引项加上行锁（Record Lock），再对索引项两边的间隙（向左扫描扫到第一个比给定参数小的值， 向右扫描扫到第一个比给定参数大的值， 然后以此为界，构建一个区间）加上间隙锁（Gap Lock）。如果一个间隙被事务 T1 加了锁，其它事务不能在这个间隙插入记录。 要禁止间隙锁的话，可以把隔离级别降为读已提交（READ COMMITTED），或者开启参数 innodb_locks_unsafe_for_binlog。注意：以上语句描述的情况，与 MySQL 所设置的事务隔离级别有较大的关系。开启一个事务时，InnoDB 存储引擎会在更新的记录上加行级锁，此时其它事务不可以更新被锁定的记录。下面我们以示例1演示此过程。例 下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。分别在 A 窗口和 B 窗口中查看事务隔离级别，A 窗口和 B 窗口的事务隔离级别需要保持一致。A 窗口查看隔离级别的 SQL 语句和运行结果如下所示：mysql> SHOW VARIABLES LIKE 'tx_isolation' \G *************************** 1. row *************************** Variable_name: tx_isolation         Value: REPEATABLE-READ 1 row in set, 1 warning (0.03 sec)B 窗口查看隔离级别 SQL 语句和运行结果如下所示：mysql> SHOW VARIABLES LIKE 'tx_isolation' \G *************************** 1. row *************************** Variable_name: tx_isolation         Value: REPEATABLE-READ 1 row in set, 1 warning (0.03 sec)结果显示，A窗口和 B窗口的事务隔离级别都为 REPEATABLE-READ。在 A窗口中开启一个事务，并修改 tb_student 表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0在 B窗口中也开启一个事务，并修改 tb_student 表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1;会发现 UPDATE 语句一直在执行。这时我们在 A 窗口中提交事务。mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)这时我们发现 B 窗口中的 UPDATE 语句执行成功。mysql> UPDATE test.tb_student SET age ='30' WHERE id = 1; Query OK, 0 rows affected (1 min 2.78 sec) Rows matched: 1  Changed: 0  Warnings: 0查询 tb_student 表中的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM test.tb_student; +----+------+------+------+------+ | id | name | age  | sex  | num  | +----+------+------+------+------+ |  1 | 张三 |   30 | 男   |    4 | |  2 | 李四 |   12 | 男   |    4 | |  3 | 王五 |   13 | 女   |    4 | |  4 | 张四 |   13 | 女   |    4 | |  5 | 王四 |   15 | 男   |    4 | |  6 | 赵六 |   12 | 女   |    4 | +----+------+------+------+------+ 6 rows in set (0.00 sec)如以上实例所示，当有不同的事务同时更新同一条记录时，另外一个事务需要等待另一个事务把锁释放，此时查看 MySQL 中 InnoDB 存储引擎的状态如下：mysql> SHOW ENGINE innodb status \G ...... ------------ TRANSACTIONS ------------ Trx id counter 19556 Purge done for trx's n:o < 19554 undo n:o < 0 state: running but idle History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283572223909376, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 19555, ACTIVE 54 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating UPDATE test.tb_student SET age ='30' WHERE id = 1 ------- TRX HAS BEEN WAITING 54 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table `test`.`tb_student` trx id 19555 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000004c62; asc     Lb;;从上面运行结果可以看出，SQL 语句 UPDATE test.tb_student SET age ='30' WHERE id = 1 在等待，RECORD LOCKS space id 197 page no 3 n bits 80 index PRIMARY of table `test`.`tb_student` trx id 19555 lock_mode X locks rec but not gap 表示锁住的资源，locks rec but not gap 代表锁住的是一个索引，不是一个范围。“MySQL thread id 14, OS thread handle 4568, query id 886 localhost ::1 root updating”表示第 2 个事务连接的 ID 为 14，当前状态为正在更新，同时正在更新的记录需要等待其它事务将锁释放。当超过事务等待锁允许的最大时间，此时会提示“ERROR 1205(HY000):Lock wait timeout exceeded; try restarting transaction" 及当前事务执行失败，则自动执行回滚操作。MySQL 数据库采用 InnoDB 模式，默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50s，一旦数据库锁超过这个时间就会报错。可通过以下命令查看当前数据库锁等待的时间。mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name            | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 120   | +--------------------------+-------+ 1 row in set, 1 warning (0.02 sec)下面演示了 InnoDB 间隙锁的实现机制。例 下面在保证 A 窗口和 B 窗口的前提下，将 tb_student 表中的 id 字段设为外键，并开启一个事务，修改 tb_student 表中 id 为 1 的 age。SQL 语句和运行结果如下：mysql> ALTER TABLE test.tb_student ADD unique key idx_id(id); Query OK, 0 rows affected (0.17 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test.tb_student SET age ='31' WHERE id = 1; Query OK, 0 rows affected (0.01 sec) Rows matched: 1  Changed: 0  Warnings: 0在 B 窗口中开启一个事务，修改 tb_student 表中 id 为 2 的 age，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql>  UPDATE test.tb_student SET age ='28'WHERE id=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0这时分别提交 A窗口和 B窗口的事务。mysql> COMMIT; Query OK, 0 rows affected (0.01 sec)查询 tb_student 表的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM test.tb_student; +----+------+------+------+------+ | id | name | age  | sex  | num  | +----+------+------+------+------+ |  1 | 张三 |   31 | 男   |    4 | |  2 | 李四 |   28 | 男   |    4 | |  3 | 王五 |   13 | 女   |    4 | |  4 | 张四 |   13 | 女   |    4 | |  5 | 王四 |   15 | 男   |    4 | |  6 | 赵六 |   12 | 女   |    4 | +----+------+------+------+------+ 6 rows in set (0.00 sec)在上述示例中，由于 InnoDB 行级锁为间隙锁，只锁定需要的记录，因此 B窗口中的事务可以更新其它记录，两个事务之间互不影响。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中，这种规则就是范式。范式是符合某一种级别的关系模式的集合。关系型数据库中的关系必须满足一定的要求，即满足不同的范式。目前关系型数据库有六种范式，分别为：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。要求最低的范式是第一范式。第二范式在第一范式的基础上又进一步的添加了要求，其余范式依次类推。一般说来，数据库只需满足第三范式就行了，而通常我们用的最多的就是第一范式、第二范式、第三范式，也就是接下来要讲的“三大范式”。1）第一范式第一范式（1NF）用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）。例如，客人住宿信息表 （姓名, 客人编号, 地址, 客房号, 客房描述, 客房类型, 客房状态, 床位数, 入住人数, 价格）。其中，“地址”列还可以细分为国家、省、市、区等，甚至有的程序还把“姓名”列也拆分为“姓”和“名”等。如果业务需求中不需要拆分“地址”和“姓名”列，则该数据表符合第一范式，如果需要将“地址”列拆分，则下列写法符合第一范式：客人住宿信息表（姓名, 客人编号, 国家, 省, 市, 区, 门牌号, 客房号, 客房描述, 客房类型, 客房状态, 床位数, 入住人数, 价格）。2）第二范式第二范式（2NF）在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。客人住宿信息表中的数据主要用来描述客人住宿信息，所以该表主键为（客人编号，客房号）:“姓名”列、“地址”列➡“客人编号”列。“客房描述”列、 “客房类型”列、“客房状态”列、“床位数”列、“入住人数”列、“价格”列➡“客房号”列。其中，“➡”符号代表依赖。以上各列没有全部依赖于主键（客人编号，客房号），只是部分依赖于主键，不符合第二范式。使用第二范式后，客人住宿信息表可以分解成以下两个表：客人信息表（客人编号，姓名，地址，客房号，入住时间，结账日期，押金，总金额），主键为“客人编号”列，其他列都全部依赖于主键列。客房信息表（客房号，客房描述，客房类型，客房状态，床位数，入住人数，价格），主键为“客房号”列，其他列都全部依赖于主键列。3）第三范式第三范式（3NF）在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。为了更好的理解第三范式，这里我们需要了解传递依赖。假设A、B 和 C 是关系 R 的三个属性，如果 A➡B 且 B➡C，则从这些函数依赖中，可以得出 A➡C。如上所述，依赖 A➡C 称之为传递依赖。以第二范式中的客房信息表为例，初看该表时没有问题，满足第三范式，每列都和主键列“客房号”相关，再细看会发现："床位数” 列、“价格”列➡“客房类型”列。“客房类型”列➡“客房号”列。“床位数”列、“价格”列➡“客房号”列为了满足第三范式，应该去掉“床位数”列，“价格”列和“客房类型”列，将客房信息表分解为如下两个表。客房表（客房号，客房描述，客房类型编号，客房状态，入住人数）客房类型表（客房类型编号，客房类型名称，床位数，价格）主键与外键在多表中的重复出现不属于数据冗余，非键字段的重复出现才是数据冗余。在客房表中客房状态存在冗余，需要进行规范化，规范化以后的表如下：客房表（客房号，客房描述，客房类型编号，客房状态编号，入住人数）。客房状态表（客房状态编号，客房状态名称）最后，满足三大范式的 E-R 图如下所示：满足三大范式的数据库模型图如下所示：4）反范式化不满足范式的数据库设计，就是反范式化。我们需要知道对于项目的最终用户来说，用户关心的是方便，清晰的数据结果。所以在设计数据库时，设计人员和客户在数据库的设计规范化和性能之间会有一定的矛盾。上面我们通过三大范式将客房表分解出两个表，为了满足客户的需求，最终可能需要通过三个或四个表之间的连接查询，来得到客户需要的数据结果，插入数据同样如此，对于客户输入的数据，我们需要分开插入到三个或四个不同的表中。由此可以看出，为了满足三大范式，我们的数据操作性能会受到相应的影响。所以，在实际的数据库设计中，既要考虑三大范式，避免数据的冗余和各种数据操作异常，又要考虑数据访问性能。为了减少表连接，提高数据库的访问性能，也可以允许适当的数据冗余列，这也许就是最合适的数据库设计方案。比如，有一张存放商品的基本表，数据表中包括“单价”、“数量”“金额”等字段。“金额”这个字段就说明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。与第三范式中介绍的冗余相比，前面介绍的冗余属于低级冗余，我们反对低级冗余，但这里的冗余为高级冗余，目的是提高数据的处理速度，增加“金额”列后，可以提高查询统计的速度，这是以空间换取时间的做法。注意：不要轻易违反数据库设计的规范化原则，如果处理不好，可能会适得其反，使应用程序运行速度更慢。优缺点最后我们来总结一下范式化和反范式化的优缺点。1）范式化优点如下：减少数据冗余范式化后的表中只有很少的重复数据，更新时只需要更新较少的数据，所以范式化的更新操作比反范式化更快范式化的表通常比反范式化更小缺点如下：范式化的表在查询时经常需要很多的关联，这回导致性能降低增加了索引优化的难度2）反范式化优点如下：可以减少表的关联可以更好的进行索引优化缺点如下：数据表存在数据冗余及数据维护异常对数据的修改需要更多的成本

麻雀虽小，五脏俱全。MySQL 虽然以简单著称，但其内部结构并不简单，本节主要介绍 MySQL 的整体架构组成。学习 MySQL 就好比盖房子，如果想把房子盖的特别高，地基一定要稳，基础一定要牢固。学习 MySQL 数据库前要先了解它的体系结构，这是学好 MySQL 数据库的前提。 MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为三层，即 MySQL Server 层、存储引擎层和文件系统层。MySQL Server 层又包括连接层和 SQL 层。如下是官方文档中 MySQL 的基础架构图：MySQL基础架构图上图中，Connection pool  为连接层，Management Services & Utilities ...Caches & Buffers 为 SQL 层，Pluggable Storage Engines 为存储引擎层，File system、Files & Logs 为文件系统层。Connectors 不属于以上任何一层，可以将 Connectors 理解为各种客户端、应用服务，主要指的是不同语言与 SQL 的交互。1. 连接层应用程序通过接口（如 ODBC、JDBC）来连接 MySQL，最先连接处理的是连接层。连接层包括通信协议、线程处理、用户名密码认证 3 部分。通信协议负责检测客户端版本是否兼容 MySQL 服务端。线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条 SQL 对应一个线程，一个线程对应一个逻辑 CPU，在多个逻辑 CPU 之间进行切换。密码认证用来验证用户创建的账号、密码，以及 host 主机授权是否可以连接到 MySQL 服务器。Connection Pool（连接池）属于连接层。由于每次建立连接都需要消耗很多时间，连接池的作用就是将用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。2. SQL层SQL 层是 MySQL 的核心，MySQL 的核心服务都是在这层实现的。主要包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。权限判断可以审核用户有没有访问某个库、某个表，或者表里某行数据的权限。查询缓存通过 Query Cache 进行操作，如果数据在 Query Cache 中，则直接返回结果给客户端，不必再进行查询解析、优化和执行等过程。查询解析器针对 SQL 语句进行解析，判断语法是否正确。预处理器对解析器无法解析的语义进行处理。查询优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的 API 接口，通过存储引擎层访问数据。Management Services & Utilities、SQL Interface、Parser、Optimizer 和 Caches & Buffers 属于 SQL 层，详细说明如下表所示。名称说明Management Services & Utilities MySQL 的系统管理和控制工具，包括备份恢复、MySQL 复制、集群等。SQL Interface（SQL 接口）用来接收用户的 SQL 命令，返回用户需要查询的结果。例如 SELECT FROM 就是调用 SQL Interface。Parser（查询解析器）在 SQL 命令传递到解析器的时候会被解析器验证和解析，以便 MySQL 优化器可以识别的数据结构或返回 SQL 语句的错误。Optimizer（查询优化器）SQL 语句在查询之前会使用查询优化器对查询进行优化，同时验证用户是否有权限进行查询，缓存中是否有可用的最新数据。它使用“选取-投影-连接”策略进行查询。例如 SELECT id, name FROM student WHERE gender = "女";语句中，SELECT 查询先根据 WHERE 语句进行选取，而不是将表全部查询出来以后再进行 gender 过滤。SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。Caches & Buffers（查询缓存）如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的，比如表缓存、记录缓存、key 缓存、权限缓存等。3. 存储引擎层Pluggable Storage Engines 属于存储引擎层。存储引擎层是 MySQL 数据库区别于其他数据库最核心的一点，也是 MySQL 最具特色的一个地方。主要负责 MySQL 中数据的存储和提取。因为在关系数据库中，数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。4. 文件系统层文件系统层主要是将数据库的数据存储在操作系统的文件系统之上，并完成与存储引擎的交互。

与其它数据库不同，MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。 SQL 模式定义了 MySQL 数据库所支持的 SQL 语法和数据校验（数据验证检查），这样可以更容易的在不同环境下使用 MySQL。 在 MySQL 中，SQL 模式常用来解决下面几类问题：通过设置 SQL Mode，可以完成不同严格程度的数据校验，有效地保障了数据的准确性。通过设置 SQL Mode 为 ANSI 模式，可以保证大多数 SQL 符合标准的 SQL 语法，使不同数据库之间进行迁移时，不需要进行较大的修改。在不同数据库之间进行数据迁移之前，设置 SQL Mode 可以使 MySQL 中的数据更方便地迁移到目标数据库中。sql_mode 系统变量的常用值下面列出了几种 SQL 模式常用的值。1) TRICT_ ALL_TABLES 和 STRICT_ TRANS_TABLES如果将 sql_mode 的值设置为 TRICT_ALL_TABLES 和 STRICT_TRANS_TABLES，那么 MySQL将启用“严格”模式。在严格模式下，MySQL 服务器会更加严格地对待接收到的不合格数据，它不会把这些不合格的数据转换为最为接近的有效值，而是会拒绝接收它们。简单来说 MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验，例如格式、长度和类型等。2) TRADITIONAL类似于严格模式，但是对于插入的不合格值会给出错误而不是警告。可以应用在事务表和非事务表，用于事务表时，只要出现错误就会立即回滚。如果你使用的是非事务存储引擎，建议不要把 SQL Mode 值设置为 TRADITIONAL，因为出现错误前进行的操作不会回滚，这样会导致操作只进行了一部分。3) ANSI_QUOTESMySQL 服务器会把双引号识别为一个标识符引用字符，而不是字符串的引号字符。所以在启用 ANSI_QUOTES 时，不能用双引号来引用字符串。4) PIPES_ AS_ CONCAT会让 MySQL 服务器把||当成一个标准的 SQL 字符串连接运算符，而不会把它当成是 OR 运算符的同义词。在 Oracle 等数据库中，||被视为字符串的连接操作符，所以在其它数据库中含有||操作符的 SQL 在 MySQL 中将无法执行，为了解决这个问题，MySQL 提供了这个值。5) ANSI会同时启用 ANSI_QUOTES、PIPES_ AS_CONCAT 和其它的几个模式值，使 MySQL 服务器的行为比它的默认运行状态更接近于标准 SQL。如何设置 sql_mode在设置 SQL 模式时，需要指定一个由单个模式值或多个模式值（多个模式值用逗号分隔）构成的值，或者指定一个空字符串，用以清除该值。模式值不区分大小写。 如果想在启动服务器时设置 SQL 模式，那么可以在 mysqld 命令行，或者在某个选项文件里设置系统变量 sql_mode。可以使用下面语句：sql_mode= "TRADITIONAL "sql_mode= "ANSI_ QUOTES, PIPES_ AS_ CONCAT"如果只是想在运行时更改 SQL 模式，那么可以使用 SET 语句来设置 sql_mode 系统变量。SET sql_mode = ' TRADITIONAL' ; 如果想设置全局性的 SQL 模式，则需要加上 GLOBAL 关键字：SET GLOBAL sql_mode = ' TRADITIONAL';设置全局变量需要具备 SUPER 管理权限。新设置的全局变量值将成为此后连入客户端的默认 SQL 模式。 如果想获取当前会话或全局的 SQL 模式值，则可以使用如下语句:SELECT @@SESSION.sql_mode;SELECT @@GLOBAL. sql_mode;其返回值由当前启用的所有模式构成，两个模式之间以逗号隔开。如果当前没有启用任何模式，则返回一个空值。

简而言之，性能优化就是在不影响系统能正确运行的前提下，运行速度更快，完成特定功能所需的时间更短。我们可以通过某些有效的方法来提高 MySQL 数据库的性能，目的是让 MySQL 数据库的运行速度更快、占用的磁盘空间更小。性能优化包括很多方面，例如优化查询速度、优化更新速度和优化 MySQL 服务器等。通过不同的优化方式达到提高 MySQL 数据库性能的目的。优化数据库是数据库管理员和开发人员的必备技能。下面将为读者介绍优化的基本知识。MySQL 数据库的用户和数据非常少时，很难判断数据库性能的好坏。只有当长时间运行，并且有大量用户进行频繁操作时，MySQL 数据库的性能才能体现出来。例如，一个每天有几万用户同时在线的大型网站，它的数据库性能的优劣就很明显。这么多用户同时连接 MySQL 数据库，并且进行查询、插入和更新的操作。如果 MySQL 数据库的性能很差，很可能无法承受如此多用户的同时操作。另外，如果用户查询一条记录需要花费很长时间，那么用户很难会喜欢这个网站。因此，为了提高 MySQL 数据库的性能，需要进行一系列的优化措施。一方面是找出系统的瓶颈，提高 MySQL 数据库整体的性能，另一方面需要合理的数据库结构设计和参数调整，来提高用户操作响应的速度，同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。例如，通过优化文件系统，提高磁盘 I\O 的读写速度；通过优化操作系统调度策略，提高 MySQL 在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。如果 MySQL 数据库中需要进行大量的查询操作，那么就需要对查询语句进行优化。对于耗费时间的查询语句进行优化，可以提高整体的查询速度。如果连接 MySQL 数据库的用户很多，那么就需要对 MySQL 服务器进行优化。否则，大量的用户同时连接 MySQL 数据库，可能会造成数据库系统崩溃。那么我们应该如何进行系统的分析，来尽快定位效率低下的 SQL 呢？主要有以下两种方法：1. 使用 SHOW STATUS 命令数据库管理员可以使用 SHOW STATUS 语句查询 MySQL 数据库的性能参数，了解各种 SQL 的执行频率。语法形式如下：SHOW STATUS LIKE 'value';其中，value 参数是常用的几个统计参数，常用参数介绍如下：Connections：连接 MySQL 服务器的次数；Uptime：MySQL 服务器的上线时间；Slow_queries：慢查询的次数；Com_select：查询操作的次数；Com_insert：插入操作的次数，对于批量插入操作，只累加一次；Com_update：更新操作的次数；Com_delete：删除操作的次数。以上参数针对于所有存储引擎的表，下面几个参数只针对 InnoDB 存储引擎。Innodb_rows_read：表示 SELECT 语句查询的记录数；Innodb_rows_inserted：表示 INSERT 语句插入的记录数；Innodb_rows_updated：表示 UPDATE 语句更新的记录数；Innodb_rows_deleted：表示 DELETE 语句删除的记录数。比如，需要查询 MySQL 服务器的连接次数，可以执行下面的 SHOW STATUS 语句：SHOW STATUS LIKE 'Connections';查询其它参数的方法和以上参数的查询方法相同。通过以上几个参数，可以很容易的了解当前数据库的应用是以插入为主还是以查询为主，以及各种类型的 SQL 语句的大致执行比例。然后根据分析结果，进行相应的性能优化。2. 使用慢查询日志慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

索引是数据库优化中最常用也是最重要的手段之一，通过索引可以帮助用户解决大多数的 SQL 性能问题。多数情况下，查询速度很慢时，加上索引便能解决问题。但也并非总是如此，因为优化不是件简单的事情。但是如果你不使用索引，在许多情况下，尝试通过其它途径来提高性能都纯粹是在浪费时间。应该首先使用索引来最大程度的改善性能，然后再看看是否还有其它有用的技术。索引提供了高效访问数据的方法，能够快速的定位表中的某条记录，加快数据库查询的速度，从而提高数据库的性能。如果查询时不使用索引，那么查询语句将查询表中的所有字段。这样查询的速度会很慢。使用索引进行查询，查询语句不必读完表中的所有记录，而只查询索引字段。这样可以减少查询的记录数，达到提高查询速度的目的。下面通过对比使用索引和不使用索引来分析索引对查询速度的影响。例 为了便于读者更好的理解，分析之前，我们先查询一下 tb_students_info 数据表中的记录，SQL 语句和运行结果如下：mysql> SELECT * FROM tb_students_info; +----+------+ | id | name | +----+------+ |  1 | 张三 | |  2 | 李四 | |  3 | 王五 | |  4 | 赵六 | |  5 | 周七 | |  6 | 吴八 | |  7 | 朱九 | |  8 | 苏十 | +----+------+ 8 rows in set (0.02 sec)使用 EXPLAIN 分析未使用索引时的查询情况，SQL 语句和运行结果如下：mysql> EXPLAIN SELECT * FROM tb_students_info WHERE name='张三' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_students_info    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 8      filtered: 12.50         Extra: Using where 1 row in set, 1 warning (0.00 sec)由结果可以看到，rows 列的值是 8，说明查询语句扫描了表中的 8 条记录。没有索引的表就相当于一组无序的行，如果我们想找到某条记录就必须检查表的每一行，看看它是否与那个期望值相匹配。这是一个全表扫描操作，其效率很低，如果表很大，而且仅有少数几条记录与搜索条件相匹配，那么整个扫描过程的效率将会超级低。在 tb_students_info 表的 name 字段添加索引，SQL 语句和运行结果如下：mysql> CREATE INDEX index_name ON tb_students_info(name); Query OK, 8 rows affected (0.14 sec)使用 EXPLAIN 再次执行上面的查询语句，SQL 语句和运行结果如下：mysql> EXPLAIN SELECT * FROM tb_students_info WHERE name='张三' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_students_info    partitions: NULL          type: ref possible_keys: index_name           key: index_name       key_len: 63           ref: const          rows: 1      filtered: 100.00         Extra: NULL 1 row in set, 1 warning (0.00 sec)结果显示，rows 列的值为 1，表示这个查询语句只扫描了表中的 1 条记录。创建索引后访问的行由 8 行减少到 1 行，其查询速度自然比扫描 8 条记录快。而且 possible_keys 和 key 的值都是 index_name，这说明查询时使用了 index_name 索引。所以，在查询操作中，使用索引不仅能自动优化查询效率，还会降低服务器的开销。注意：由于 tb_students_info 表中记录较少，所以在这没有分析运行时间。表中记录多时，运行时间的差异也会体现出索引对查询速度的影响。

分析了mysqld进程关闭的过程，以及如何安全、缓和地关闭MySQL实例，对这个过程不甚清楚的同学可以参考下。关闭过程：1、发起shutdown，发出  SIGTERM信号2、有必要的话，新建一个关闭线程(shutdown thread)如果是客户端发起的关闭，则会新建一个专用的关闭线程如果是直接收到 SIGTERM 信号进行关闭的话，专门负责信号处理的线程就会负责关闭工作，或者新建一个独立的线程负责这个事当无法创建独立的关闭线程时（例如内存不足），MySQL Server会发出类似下面的告警信息：Error: Can't create thread to kill server3、MySQL Server不再响应新的连接请求关闭TCP/IP网络监听，关闭Unix Socket等渠道4、逐渐关闭当前的连接、事务空闲连接，将立刻被终止；当前还有事务、SQL活动的连接，会将其标识为 killed，并定期检查其状态，以便下次检查时将其关闭；（参考 KILL 语法）当前有活跃事务的，该事物会被回滚，如果该事务中还修改了非事务表，则已经修改的数据无法回滚，可能只会完成部分变更；如果是Master/Slave复制场景里的Master，则对复制线程的处理过程和普通线程也是一样的；如果是Master/Slave复制场景里的Slave，则会依次关闭IO、SQL线程，如果这2个线程当前是活跃的，则也会加上 killed 标识，然后再关闭；Slave服务器上，SQL线程是允许直接停止当前的SQL操作的（为了避免复制问题），然后再关闭该线程；在MySQl 5.0.80及以前的版本里，如果SQL线程当时正好执行一个事务到中间，该事务会回滚；从5.0.81开始，则会等待所有的操作结束，除非用户发起KILL操作。当Slave的SQL线程对非事务表执行操作时被强制 KILL了，可能会导致Master、Slave数据不一致；5、MySQL Server进程关闭所有线程，关闭所有存储引擎；刷新所有表cache，关闭所有打开的表；每个存储引擎各自负责相关的关闭操作，例如MyISAM会刷新所有等待写入的操作；InnoDB会将buffer pool刷新到磁盘中（从MySQL 5.0.5开始，如果innodb_fast_shutdown不设置为 2 的话），把当前的LSN记录到表空间中，然后关闭所有的内部线程。6、MySQL Server进程退出关于KILL指令从5.0开始，KILL 支持指定  CONNECTION | QUERY两种可选项：KILL CONNECTION和原来的一样，停止回滚事务，关闭该线程连接，释放相关资源；KILL QUERY则只停止线程当前提交执行的操作，其他的保持不变；提交KILL操作后，该线程上会设置一个特殊的 kill标记位。通常需要一段时间后才能真正关闭线程，因为kill标记位只在特定的情况下才检查：1、执行SELECT查询时，在ORDER BY或GROUP BY循环中，每次读完一些行记录块后会检查 kill标记位，如果发现存在，该语句会终止；2、执行ALTER TABLE时，在从原始表中每读取一些行记录块后会检查 kill 标记位，如果发现存在，该语句会终止，删除临时表；3、执行UPDATE和DELETE时，每读取一些行记录块并且更新或删除后会检查 kill 标记位，如果发现存在，该语句会终止，回滚事务，若是在非事务表上的操作，则已发生变更的数据不会回滚；4、GET_LOCK() 函数返回NULL；5、INSERT DELAY线程会迅速内存中的新增记录，然后终止；6、如果当前线程持有表级锁，则会释放，并终止；7、如果线程的写操作调用在等待释放磁盘空间，则会直接抛出“磁盘空间满”错误，然后终止；8、当MyISAM表在执行REPAIR TABLE 或 OPTIMIZE TABLE 时被 KILL的话，会导致该表损坏不可用，指导再次修复完成。安全关闭MySQL几点建议想要安全关闭 mysqld 服务进程，建议按照下面的步骤来进行：0、用具有SUPER、ALL等最高权限的账号连接MySQL，最好是用 unix socket 方式连接；1、在5.0及以上版本，设置innodb_fast_shutdown = 1，允许快速关闭InnoDB（不进行full purge、insert buffer merge），如果是为了升级或者降级MySQL版本，则不要设置；2、设置innodb_max_dirty_pages_pct = 0，让InnoDB把所有脏页都刷新到磁盘中去；3、设置max_connections和max_user_connections为1，也就最后除了自己当前的连接外，不允许再有新的连接创建；4、关闭所有不活跃的线程，也就是状态为Sleep  且 Time 大于 1 的线程ID；5、执行 SHOW PROCESSLIST  确认是否还有活跃的线程，尤其是会产生表锁的线程，例如有大数据集的SELECT，或者大范围的UPDATE，或者执行DDL，都是要特别谨慎的；6、执行 SHOW ENGINE INNODB STATUS 确认History list length的值较低（一般要低于500），也就是未PURGE的事务很少，并且确认Log sequence number、Log flushed up to、Last checkpoint at三个状态的值一样，也就是所有的LSN都已经做过检查点了；7、然后执行FLUSH LOCKAL TABLES 操作，刷新所有 table cache，关闭已打开的表（LOCAL的作用是该操作不记录BINLOG）；8、如果是SLAVE服务器，最好是先关闭 IO_THREAD，等待所有RELAY LOG都应用完后，再关闭 SQL_THREAD，避免 SQL_THREAD 在执行大事务被终止，耐心待其全部应用完毕，如果非要强制关闭的话，最好也等待大事务结束后再关闭SQL_THREAD；9、最后再执行 mysqladmin shutdown。10、紧急情况下，可以设置innodb_fast_shutdown = 1，然后直接执行 mysqladmin shutdown 即可，甚至直接在操作系统层调用 kill 或者 kill -9 杀掉 mysqld 进程（在innodb_flush_log_at_trx_commit = 0 的时候可能会丢失部分事务），不过mysqld进程再次启动时，会进行CRASH RECOVERY工作，需要有所权衡。啰嗦那么多，其实正常情况下执行 mysqladmin shutdown 就够了，如果发生阻塞，再参考上面的内容进行分析和解决吧，哈哈：）

为了保证并发时操作数据的正确性，数据库都会有事务隔离级别的概念。1) 脏读脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。2) 不可重复读不可重复读是指在一个事务内，多次读取同一个数据。在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。3) 幻读幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。为了解决以上这些问题，标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。MySQL 包括的事务隔离级别如下：读未提交（READ UNCOMITTED）读提交（READ COMMITTED）可重复读（REPEATABLE READ）串行化（SERIALIZABLE）MySQL 事务隔离级别可能产生的问题如下表所示：隔离级别脏读不可重复读幻读READ UNCOMITTED√√√READ COMMITTED×√√REPEATABLE READ××√SERIALIZABLE×××MySQL 的事务的隔离级别由低到高分别为 READ UNCOMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少。下面根据实例来一一阐述它们的概念和联系。1. 读未提交（READ UNCOMITTED，RU）顾名思义，读未提交就是可以读到未提交的内容。如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。例 1 主要演示了在读未提交隔离级别中产生的脏读现象。示例 11) 先在 test 数据库中创建 testnum 数据表，并插入数据。SQL 语句和执行结果如下：mysql> CREATE TABLE testnum(     -> num INT(4)); Query OK, 0 rows affected (0.57 sec) mysql> INSERT INTO test.testnum (num) VALUES(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.09 sec) 2) 下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。在 A 窗口中修改事务隔离级别，因为 A 窗口和 B 窗口的事务隔离级别需要保持一致，所以我们使用 SET GLOBAL TRANSACTION 修改全局变量。SQL 语句如下：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.04 sec) flush privileges; Query OK, 0 rows affected (0.04 sec) 查询事务隔离级别，SQL 语句和运行结果如下：mysql> show variables like '%tx_isolation%'\G *************************** 1. row *************************** Variable_name: tx_isolation         Value: READ-UNCOMMITTED 1 row in set, 1 warning (0.00 sec)结果显示，现在 MySQL 的事务隔离级别为 READ-UNCOMMITTED。 3) 在 A 窗口中开启一个事务，并查询 testnum 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM testnum; +------+ | num  | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)4) 打开 B 窗口，查看当前 MySQL 的事务隔离级别，SQL 语句如下：mysql> show variables like '%tx_isolation%'\G *************************** 1. row *************************** Variable_name: tx_isolation         Value: READ-UNCOMMITTED 1 row in set, 1 warning (0.00 sec)确定事务隔离级别是 READ-UNCOMMITTED 后，开启一个事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test.testnum SET num=num*2 WHERE num=2; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 05) 现在返回 A 窗口，再次查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * FROM testnum; +------+ | num  | +------+ |    1 | |    4 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.02 sec)由结果可以看出，A 窗口中的事务读取到了更新后的数据。 6) 下面在 B 窗口中回滚事务，SQL 语句和运行结果如下：mysql> ROLLBACK; Query OK, 0 rows affected (0.09 sec)7) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * FROM testnum; +------+ | num  | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)当 MySQL 的事务隔离级别为 READ UNCOMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新但未提交之前， A 窗口中的事务就已经读取到了更新后的数据。但由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。使用读提交隔离级别可以解决实例中产生的脏读问题。2. 读提交（READ COMMITTED，RC）顾名思义，读提交就是只能读到已经提交了的内容。如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。这是大多数数据库系统的默认事务隔离级别（例如 Oracle、SQL Server），但不是 MySQL 默认的。例 2 演示了在读提交隔离级别中产生的不可重复读问题。示例 21) 使用 SET 语句将 MySQL 事务隔离级别修改为 READ COMMITTED，并查看。SQL 语句和运行结果如下：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%tx_isolation%'\G *************************** 1. row *************************** Variable_name: tx_isolation         Value: READ-COMMITTED 1 row in set, 1 warning (0.00 sec)2) 确定当前事务隔离级别为 READ COMMITTED 后，开启一个事务，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) 3) 在 B 窗口中开启事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql>  UPDATE test.testnum SET num=num*2 WHERE num=2; Query OK, 1 row affected (0.07 sec) Rows matched: 1  Changed: 1  Warnings: 04) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * from test.testnum; +------+ | num  | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)5) 提交 B 窗口中的事务，SQL 语句和运行结果如下：mysql> COMMIT; Query OK, 0 rows affected (0.07 sec)6) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * from test.testnum; +------+ | num  | +------+ |    1 | |    4 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)当 MySQL 的事务隔离级别为 READ COMMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新并提交后，A 窗口中的事务读取到了更新后的数据。在该过程中，A 窗口中的事务必须要等待 B 窗口中的事务提交后才能读取到更新后的数据，这样就解决了脏读问题。而处于 A 窗口中的事务出现了不同的查询结果，即不可重复读现象。使用可重复读隔离级别可以解决实例中产生的不可重复读问题。3. 可重复读（REPEATABLE READ，RR）顾名思义，可重复读是专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。例 3 演示了在可重复读隔离级别中产生的幻读问题。示例 31) 在 test 数据库中创建 testuser 数据表，SQL 语句和执行结果如下：mysql> CREATE TABLE testuser(     -> id INT (4) PRIMARY KEY,     -> name VARCHAR(20)); Query OK, 0 rows affected (0.29 sec)2) 使用 SET 语句修改事务隔离级别，SQL 语句如下：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec)3) 在 A 窗口中开启事务，并查询 testuser 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM test.testuser where id=1; Empty set (0.04 sec)4) 在 B 窗口中开启一个事务，并向 testuser 表中插入一条数据，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql>  INSERT INTO test.testuser VALUES(1,'zhangsan'); Query OK, 1 row affected (0.04 sec) mysql> COMMIT; Query OK, 0 rows affected (0.06 sec)5) 现在返回 A 窗口，向 testnum 数据表中插入数据，SQL 语句和运行结果如下：mysql> INSERT INTO test.testuser VALUES(1,'lisi'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql>  SELECT * FROM test.testuser where id=1; Empty set (0.00 sec)使用串行化隔离级别可以解决实例中产生的幻读问题。4. 串行化（SERIALIZABLE）如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。

顾名思义，临时表就是临时用来存储数据的表，是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时，可以把中间结果或小的子集放到一个临时表里，再对这些表进行查询，以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。一般情况下，临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。外部临时表外部临时表也可称为会话临时表，这种临时表只对当前用户可见，它的数据和表结构都存储在内存中。当前会话中断或结束后，数据表数据就会丢失，MySQL 会自动删除表并释放其所占空间。1）创建临时表创建临时表很容易，在 CREATE TABLE 语句上添加 TEMPORARY 关键字，如下所示：CREATE TEMPORARY TABLE <表名>...临时表的命名可以和非临时表同名，但是同名后非临时表将对当前会话不可见，直到临时表被删除。2）查询临时表创建了临时表之后，运行 SHOW TABLES 命令不会列出临时表，以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息，这不是 Bug，而是设计就是如此。我们可以使用以下命令来查看临时表：SHOW CREATE TABLE <表名>; 3）删除临时表当然，我们也可以在当前会话中手动销毁临时表，SQL 语句如下：DROP TABLE <表名>;例 下面是创建 tmp_table 表，并对它进行操作。mysql> CREATE TEMPORARY TABLE tmp_table (     -> id INT NOT NULL,     -> name VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_test    | +-------------------+ | product           | | product_price     | | score             | | student           | | student_comment   | | tb_student        | | tb_student_course | | tb_students_info  | | tb_students_score | | tb_usertest       | +-------------------+ 10 rows in set (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G *************************** 1. row ***************************        Table: tmp_table Create Table: CREATE TEMPORARY TABLE `tmp_table` (   `id` int(11) NOT NULL,   `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE tmp_table; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话，再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表，会发现数据库中没有该表的存在，因为在退出会话时该临时表已经被销毁了。外部临时表也有一些限制，使用时需注意以下几点：所用数据库账号需要有建立和使用临时表的权限在同一条 SQL 语句中，不能关联2次相同的临时表不能用 RENAME 来重命名一个临时表，可以用 ALTER TABLE 来代替。内部临时表内部临时表是一种特殊轻量级的临时表，不同于手工创建的临时表，它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色，MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。以下是生成内部临时表的几种可能：使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表Distinct 和 ORDER BY 联合使用多表连接需要保存中间结果集内部临时表有两种类型，一种是 HEAP 临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要 IO 操作；另一种是 OnDisk 临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。以下情况可能阻碍 MySQL 使用 HEAP 临时表，改用 OnDisk 临时表：数据表中含有 BLOB 类型或 TEXT 类型字段列；在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列；如果使用了 UNION 或 UNION ALL，而且 SELECT 列中含有任何超过 512 字节的列；如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值，HEAP 临时表将会被自动转换成 OnDisk 临时表。MySQL 5.7 版本中，OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。

行列互转，是一个经常遇到的需求。实现的方法，有case when方式和2005之后的内置pivot和unpivot方法来实现。在读了技术内幕那一节后，虽说这些解决方案早就用过了，却没有系统性的认识和总结过。为了加深认识，再总结一次。行列互转，可以分为静态互转，即事先就知道要处理多少行（列）;动态互转,事先不知道处理多少行(列)。--创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL  DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders (  orderid  int    NOT NULL PRIMARY KEY NONCLUSTERED,  orderdate datetime  NOT NULL,  empid   int    NOT NULL,  custid  varchar(5) NOT NULL,  qty    int    NOT NULL ); CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid  ON dbo.Orders(orderdate, orderid); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30001, '20020802', 3, 'A', 10); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(10001, '20021224', 1, 'A', 12); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(10005, '20021224', 1, 'B', 20); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(40001, '20030109', 4, 'A', 40); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(10006, '20030118', 1, 'C', 14); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(20001, '20030212', 2, 'B', 12); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(40005, '20040212', 4, 'A', 10); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(20002, '20040216', 2, 'C', 20); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30003, '20040418', 3, 'B', 15); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30004, '20020418', 3, 'C', 22); INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)  VALUES(30007, '20020907', 3, 'D', 30); GO行转列-静态方案：--行转列的静态方案一：CASE WHEN,兼容sql2000 select custid, sum(case when YEAR(orderdate)=2002 then qty end) as [2002], sum(case when YEAR(orderdate)=2003 then qty end) as [2003], sum(case when YEAR(orderdate)=2004 then qty end) as [2004] from orders group by custid; GO --行转列的静态方案二：PIVOT,sql2005及以后版本 select * from (select custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in([2002],[2003],[2004]))as p GO行转列-动态方案：加入了xml处理和SQL注入预防判断--既然是用到了动态SQL,就有一个老话题:SQL注入。建一个注入性字符的判断函数。 CREATE FUNCTION [dbo].[fn_CheckSQLInjection] (  @Col nvarchar(4000) ) RETURNS BIT --如果存在可能的注入字符返回true,反之返回false AS BEGIN DECLARE @result bit;  IF     UPPER(@Col) LIKE UPPER(N'%0x%')  OR UPPER(@Col) LIKE UPPER(N'%;%')  OR UPPER(@Col) LIKE UPPER(N'%''%')  OR UPPER(@Col) LIKE UPPER(N'%--%')  OR UPPER(@Col) LIKE UPPER(N'%/*%*/%')  OR UPPER(@Col) LIKE UPPER(N'%EXEC%')  OR UPPER(@Col) LIKE UPPER(N'%xp_%')  OR UPPER(@Col) LIKE UPPER(N'%sp_%')  OR UPPER(@Col) LIKE UPPER(N'%SELECT%')  OR UPPER(@Col) LIKE UPPER(N'%INSERT%')  OR UPPER(@Col) LIKE UPPER(N'%UPDATE%')  OR UPPER(@Col) LIKE UPPER(N'%DELETE%')  OR UPPER(@Col) LIKE UPPER(N'%TRUNCATE%')  OR UPPER(@Col) LIKE UPPER(N'%CREATE%')  OR UPPER(@Col) LIKE UPPER(N'%ALTER%')  OR UPPER(@Col) LIKE UPPER(N'%DROP%')  SET @result=1  ELSE  SET @result=0  return @result END GO --行转列的动态方案一：CASE WHEN,兼容sql2000 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T  SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @SQL NVARCHAR(4000)=N''; WHILE @Y IS NOT NULL BEGIN  SET @SQL=@SQL+N',sum(case when YEAR(orderdate)='+CAST(@Y AS NVARCHAR(4)) +N' then qty end) as '+QUOTENAME(@Y);  SET @Y=(SELECT MIN(years) from @T where years>@Y); END IF dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL=N'SELECT custid'+@SQL+N' FROM orders group by custid' PRINT @SQL EXEC sp_executesql @SQL GO --行转列的动态方案二：PIVOT,sql2005及以后版本 DECLARE @T TABLE (years INT NOT NULL PRIMARY KEY); INSERT INTO @T  SELECT DISTINCT YEAR(orderdate) from orders; DECLARE @Y INT; SET @Y=(SELECT MIN(years) from @T); DECLARE @SQL NVARCHAR(4000)=N'';   --这里使用了xml处理来处理类组字符串 SET @SQL=STUFF((SELECT N','+QUOTENAME(years) FROM @T  FOR XML PATH('')),1,1,N''); IF dbo.fn_CheckSQLInjection(@SQL)=0 SET @SQL=N'select * from (select DISTINCT custid,YEAR(orderdate) as years,qty from orders) as ord pivot(sum(qty) for years in('+@SQL+N'))as p'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL; GO列转行：--列转行的静态方案：UNPIVOT,sql2005及以后版本 SELECT * FROM dbo.pvtCustOrders SELECT custid,years,qty from dbo.pvtCustOrders unpivot(qty for years in([2002],[2003],[2004]))as up GO --列转行的动态方案：UNPIVOT,sql2005及以后版本 --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。 DECLARE @SQL NVARCHAR(4000)=N''; SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION>1 AND TABLE_NAME='PvtCustOrders' FOR XML PATH('')),1,1,N'') SET @SQL=N'SELECT custid,years,qty      from dbo.pvtCustOrders      unpivot(qty for years in('+@SQL+'))as up'; PRINT @SQL; EXEC SP_EXECUTESQL @SQL;总结：学无止境，在解决问题中成长。