IT学习笔记--MySQL

MySQL

基础知识：

1、检索不同的值，即去重，可使用关键字DISTINCT。但是该关键字应用于所有列，而不是前置它的列，即在查询多列时，除非指定的多列都不同，否则所有行都会被检索出来。

2、关键字LIMIT可以限制结果，即只查询第一行或前几行。LIMIT可以带两个参数值，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数；带两个值的LIMIT，第一个值为开始行数，第二个值为返回的行数。

3、结果排序可使用ORDER BY。对多列进行排序时，列之间用逗号隔开，排序按列从左往右的顺序进行多列排序，第一个具有最高优先级。默认为升序，需要降序时，可使用关键字DESC。如果要对多列进行降序排序，必须对每一个列指定DESC关键字。

4、检查某个范围的值，可使用BETWEEN …AND…操作符。指定的两个范围值必须用AND关键字隔开，匹配的值包括指定的开始值和结束值。

5、逻辑操作符，即AND操作符和OR操作符，进行数据过滤。AND操作符用来指示检索满足所有给定条件的行；OR操作符用来指示检索匹配任一条件的行。在AND和OR组合使用时，AND的计算次序要比OR优先级高。

6、IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。而IN取的合法值由逗号隔开，并全都括在圆括号中。IN操作符与OR操作符功能相同，但是IN操作符一般比OR操作符执行的快，IN的最大优点是可以包含其它SELECT语句，使得能够更动态地建立WHERE子句。NOT操作符与IN操作符相反。

7、在搜索子句中使用通配符，必须使用LIKE操作符。

    1）最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。

     2）下划线（_）通配符。它与%用途一样，但是下划线只能匹配单个字符而不是多个字符。

8、关键字REGEXP表示正则表达式过滤。

9、Concat()拼接串，即把多个串连接起来形成一个较长的串。；Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

10、RTrim()函数去掉值右边的所有空格。LTrim()去掉值左边所有的空格。Trim()函数去掉左右两边所有的空格。

11、别名的使用，使用关键字AS赋予。别名是一个字段或值的替换名。

12、文本处理函数：

    1）Upper()函数将文本转换为大写。

    2）Left()返回串左边的字符；

    3）Locate()返回串的一个子串；

    4）Length()返回串的长度；

    5）Right()返回串右边的字符；

    6）Soundex()返回串的SOUNDEX值；

    7）SubString()返回子串的字符；

13、日期和时间处理函数

    需要注意的是：无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期格式必须为yyyy-mm-dd。

14、数值处理函数

15、 聚集函数

1）AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。要得到多个列的平均值，必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。

2） COUNT(*)函数对表中行的数目进行计数，不管表列中包含的是空值还是非空值；COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

16、HAVING非常类似于WHERE，两者唯一的差别是WHERE过滤行，而HAVING过滤分组（ORDER BY）。

17、外键即为某个表中的一列，它包含了另一个表的主键值，定义了两个表之间的关系。

18、内部联结可使用INNER JOIN，而联结条件用特定的ON子句。

19、在SELECT语句中使用OUTER JOIN来指定联结的类型为外部联结，它与内部联结的区别是，外部联结返回的还包括没有关联行的行。而在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

20、UNION（并）操作符可用来组合数条SQL查询，利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。UNION的使用很简单，所需做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。在使用UNION时，重复的行被自动取消。这是它的默认行为，可以改变它，若想返回匹配的所有行，可使用UNION ALL而不是UNION。

21、全文本搜索：一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULL TEXT子句，它给出被索引列的一个逗号分隔的列表。在SELECT语句中进行全文本搜索时，需要使用Match()和Against()两个函数，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。注意：传递给Match()的值必须与FULLTEXT()定义中的相同，如果指定多个列，则必须列出它们（而且次序正确）。

22、插入检索出来的数据，即由一条INSERT语句和一条SELECT语句组成。

23、每个表中只允许一个AUTO_INCREMENT列，而且它必须被索引（如通过使它成为主键）。

24、常用的几个数据库引擎：

    1）InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；

    2）MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快；

    3）MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

25、视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

26、存储过程，即为以后的使用而保存的一条或多条MySQL语句的集合。使用存储过程的3个主要好处是简单、安全和高性能。

    执行存储过程的语句为CALL，CALL接受存储过程的名字以及需要传递给它的任意参数。

    CREATE PROCEDURE 关键字来创建存储过程，其中BEGIN和END语句来限定存储过程体。

    存储过程实际上是一种函数，所以存储过程名后需要有（）符号（即使不传递参数也需要）。

    存储过程创建后被保存在服务器上以供使用，直至被删除。删除使用DROP PROCEDURE关键字。

    在存储过程体中使用DECLARE关键字定义局部变量，并要求指定变量名和数据类型。

27、游标：是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。

    游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

    游标用OPEN CURSOR语句来打开。游标处理完成后，应当使用CLOSE语句关闭游标。

    在一个游标打开后，可用FETCH语句分别访问它的每一行；FETCH指定检索什么数据（所需的列），以及将检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

28. 修改表

1）添加字段：alter table student add sgender varchar(2);  其中student 为表名，sgender为新添加的字段

2）删除字段：alter table student drop sgender;

3）修改字段类型：alter table student modify sgender varchar(100);

4）修改字段名称：alter table student change sgender gender varchar(2); 其中gender为修改后的字段名

5）修改表名称：alter table student rename to teacher; 

29.增删改数据

1）增加数据

• 插入所有字段。一定依次按顺序插入
• 注意不能少或多字段值

2）修改数据

修改多个字段,注意: SET 字段名=值,字段名=值,....

如：UPDATE student SET gender='男',age=30 WHERE id=2;

3）删除数据

delete from 与 truncate table区别：

（1）delete from: 可以全表删除     

• 可以带条件删除 
• 只能删除表的数据，不能删除表的约束    
• 使用delete from删除的数据可以回滚（事务）

（2）truncate table: 可以全表删除  

• 不能带条件删除
• 既可以删除表的数据，也可以删除表的约束
• 使用truncate table删除的数据不能回滚

如：TRUNCATE TABLE student;

4）查询数据

（1）查询时添加常量列

-- 需求： 在查询student表时添加一个班级列，内容为“java就业班”

SELECT id,NAME,gender,age,'java就业班' AS '年级'  FROM student;

（2）查询时合并列

-- 需求： 查询每个学生的servlet和jsp的总成绩

SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;

注意：合并列只能合并数值类型的字段

（3）条件查询

• 比较条件： >   <   >=  <=  =  <>(不等于)     between and (包前包后 等价于>= 且 <=)

• 判空条件(null 空字符串)：  is null / is not null / =''  / <>''

-- null：表示没有值

-- 空字符串：有值的！

• 模糊条件： like

 通常使用以下替换标记：

% : 表示任意个字符            _ : 表示一个字符   如：

需求： 查询姓‘李’，且姓名只有两个字的学生

SELECT * FROM student WHERE NAME LIKE '李_';

（4）聚合查询

• 常用的聚合函数： sum()  avg()  max()  min()  count()
• 注意：count（）函数统计的数量不包含null的数据

（5）分页查询

• 起始行从0开始

 分页：当前页  每页显示多少条

• 分页查询当前页的数据的sql: SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;

（6）查询排序（order by）

语法 ：order by 字段 asc/desc

• asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）
• desc: 倒序，反序。数值：递减，字母：自然反序(z-a)

​​​​​​​（7）分组查询（group by）

分组查询后筛选需求： 查询总人数大于2的性别

• 查询男女的人数
• 筛选出人数大于2的记录(having)

注意： 分组之前条件使用where关键字，分组之后条件使用having关键字

SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;

30. 数据约束

即：对用户操作表的数据进行约束

1）默认值（DEFAULT）

作用： 当用户对使用默认值的字段不插入值的时候，就使用默认值。

注意：         

• 对默认值字段插入null是可以的。
• 对默认值字段可以插入非null

2）非空（NOT NULL）

作用： 限制字段必须赋值

注意：

• 非空字符必须赋值
• 非空字符不能赋null

​​​​​​​3）唯一（UNIQUE）

作用： 对字段的值不能重复（只作用于有值的情况，对于NULL不起作用）

 注意：

• 唯一字段可以插入null                               
• 唯一字段可以插入多个null

​​​​​​​4）主键（PRIMARY KEY）

作用： 非空+唯一

 注意：                      

• 通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
• 建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的 id字段。

​​​​​​​5）自增长（AUTO_INCREMENT）

作用： 自动递增

自增长，从0开始  ZEROFILL 零填充

6）外键（FOREIGN KEY）

作用：约束两种表的数据

出现两种表的情况： 解决数据冗余高问题： 独立出一张表               

 例如： 员工表  和  部门表

问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！   

使用外键约束：约束插入员工表的部门ID字段值

 解决办法： 在员工表的部门ID字段添加一个外键约束               

-- 部门表（主表） CREATE TABLE dept(          id INT PRIMARY KEY,          deptName VARCHAR(20) ) -- 修改员工表（副表/从表） CREATE TABLE employee(          id INT PRIMARY KEY,          empName VARCHAR(20),          deptId INT,-- 把部门名称改为部门ID          -- 声明一个外键约束          CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)          --           外键名称                  外键               参考表(参考字段) )

    注意：

• 被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！
• 主表的参考字段通用为主键！
• 添加数据： 先添加主表，再添加副表
• 修改数据： 先修改副表，再修改主表
• 删除数据： 先删除副表，再删除主表

7） 级联操作

当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现！

级联修改： ON UPDATE CASCADE

级联删除： ON DELETE CASCADE                                          

CREATE TABLE employee(          id INT PRIMARY KEY,          empName VARCHAR(20),          deptId INT,-- 把部门名称改为部门ID          -- 声明一个外键约束          CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：级联修改          --           外键名称                  外键               参考表(参考字段) ) 注意： 级联操作必须在外键基础上使用

31.数据库设计三大范式

第一范式： 要求表的每个字段必须是不可分割的独立单元

                    student     :   name              -- 违反第一范式

                                           张小名|狗娃                                            

                       sutdent    ： name    old_name    --符合第一范式

                                            张小名    狗娃

第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。                                              

                       employee（员工）: 员工编号  员工姓名 部门名称   订单名称  --违反第二范式

                       员工表：员工编号  员工姓名 部门名称  

                       订单表：  订单编号  订单名称             -- 符合第二范式                                                    

第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

          员工表： 员工编号（主键） 员工姓名  部门编号  部门名 --符合第二范式，违反第三范式                                                                                                                                                       （数据冗余高）

                       员工表：员工编号（主键） 员工姓名  部门编号    --符合第三范式（降低数据冗余） 

                       部门表：部门编号  部门名

32.关联查询(多表查询)

多表查询规则：1）确定查询哪些表   2）确定哪些哪些字段   3）表与表之间连接条件 (规律：连接条件数量是表数量-1)

1）内连接查询：只有满足条件的结果才会显示(使用最频繁)

SELECT empName,deptName       -- 2）确定哪些哪些字段

         FROM employee,dept    -- 1）确定查询哪些表

         WHERE employee.deptId=dept.id  -- 3）表与表之间连接条件       

内连接的另一种语法

SELECT empName,deptName

         FROM employee

         INNER JOIN dept

         ON employee.deptId=dept.id;

2）左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

 注意： 左外连接：左表的数据一定会完成显示

SELECT d.deptName,e.empName

         FROM dept d

         LEFT OUTER JOIN employee e

         ON d.id=e.deptId;

3）右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

注意： 右外连接：右表的数据一定会完成显示

SELECT d.deptName,e.empName

         FROM employee e

         RIGHT OUTER JOIN dept d

         ON d.id=e.deptId;

33.存储过程

存储过程，即：带有逻辑的sql语句

1）存储过程特点

• 执行效率非常快！存储过程是在数据库的服务器端执行的！
• 移植性很差！不同数据库的存储过程是不能移植。

2）存储过程语法​​​​​​​

-- 创建存储过程 DELIMITER $       -- 声明存储过程的结束符 CREATE PROCEDURE pro_test()           --存储过程名称(参数列表) BEGIN             -- 开始          -- 可以写多个sql语句;          -- sql语句+流程控制          SELECT * FROM employee; END $            -- 结束 结束符 -- 执行存储过程 CALL pro_test();          -- CALL 存储过程名称(参数); 参数： IN：   表示输入参数，可以携带数据带存储过程中 OUT： 表示输出参数，可以从存储过程中返回结果 INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

3）带有输入参数的存储过程

需求：传入一个员工的id，查询员工信息

DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
BEGIN
	SELECT * FROM employee WHERE id=eid;
END $ 

-- 调用
CALL pro_findById(4);

4）带有输出参数的存储过程

DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
BEGIN
        -- 给参数赋值
	SET str='helljava';
END $

-- 删除存储过程
DROP PROCEDURE pro_testOut;

***mysql的变量******

--  全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）

        -- 查看所有全局变量： show variables

        -- 查看某个全局变量： select @@变量名

        -- 修改全局变量： set 变量名=新值

        -- character_set_client: mysql服务器的接收数据的编码

        -- character_set_results：mysql服务器输出数据的编码       

--  会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！

        -- 定义会话变量: set @变量=值

        -- 查看会话变量： select @变量      

-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！

定义一个会话变量name, 2)使用name会话变量接收存储过程的返回值
CALL pro_testOut(@NAME);
-- 查看变量值
SELECT @NAME;

5）带有输入输出参数的存储过程

DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 输入输出参数
BEGIN
   -- 查看变量
   SELECT n;
   SET n =500;
END $

-- 调用
SET @n=10;

CALL pro_testInOut(@n);

SELECT @n;

6）带有条件判断的存储过程

需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;

DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num=1 THEN
		SET str='星期一';
	ELSEIF num=2 THEN
		SET str='星期二';
	ELSEIF num=3 THEN
		SET str='星期三';
	ELSE
		SET str='输入错误';
	END IF;
END $

CALL pro_testIf(4,@str);
 
SELECT @str;

7）带有循环功能的存储过程

需求： 输入一个整数，求和。例如，输入100，统计1-100的和

DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE vsum INT DEFAULT 0;
	WHILE i<=num DO
	      SET vsum = vsum+i;
	      SET i=i+1;
	END WHILE;
	SET result=vsum;
END $

DROP PROCEDURE pro_testWhile;

CALL pro_testWhile(100,@result);

SELECT @result;

8）使用查询的结果赋值给变量（INTO）

DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
	SELECT empName INTO vname FROM employee WHERE id=eid;
END $

CALL pro_findById2(1,@NAME);

SELECT @NAME;

34.触发器

触发器作用：当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！

例如： 当向员工表插入一条记录时，希望同时往日志表插入数据

-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
     
-- 插入数据
INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);
INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);

-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
     
 -- 修改
 UPDATE employee SET empName='eric' WHERE id=7;
 
-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');
  
 -- 删除
 DELETE FROM employee WHERE id=7;

35. 备份恢复

备份：

mysqldump -uroot -p day17 > c:/bak.sql

恢复：

mysql -uroot -p day17 < d:/back.sql

注意：不需要登陆

36.权限

lGRANT 权限 ON 数据库.表 TO '账户名'@'账户类型' IDENTIFIED BY '密码';

权限： select insert delete update drop create/  或,all

flush privileges

进阶：

事务并发问题：

一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库。数据库中的相同数据可能同时被多个事务访问，如果没有采取必要的隔离措施，就会导致各种并发问题，破坏数据的完整性。这些问题可以归结为5类，包括3类数据读问题（ 脏读、 不可重复读和 幻读）以及2类数据更新问题（ 第一类丢失更新和 第二类丢失更新）。

1. 脏读

A事务读取到B事务尚未提交的更新数据，并在这个数据的基础上操作。如果恰巧B事务回滚，那么A事务读到的数据根本是不被承认的。

2. 不可重复读

 A事务读取了B事务已经提交的更改数据。即：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

3. 幻读

A事务读取B事务提交的新增数据。即：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

4. 第一类丢失更新

A事务撤销时，把已经提交的B事务的更新数据覆盖了。

5. 第二类丢失更新

A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失。

索引实现原理：

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。需要注意的是存储引擎是表级别的概念，在一个数据库中不同的表可以采用不同的存储引擎。

1. MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址。设Col1为主键，则下图是一个MyISAM表的主索引（Primary key）示例：

可以看出MyISAM的索引文件仅仅保存数据记录的地址。

在MyISAM中，主索引和辅索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅索引的key可以重复。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2. InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。

从上文知：

• MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
• 而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅索引data域存储相应记录主键值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如：
 

 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅索引都引用主索引，过长的主索引会令辅索引变得过大。

用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

InnoDB索引的B+树节点的大小默认被锁定在16K（四个磁盘页的大小），可以通过下面这个命令查看：

SHOW GLOBAL STATUS LIKE 'Innodb_page_size'

3. MySQL数据文件

前面我们说到了MyISAM存储引擎是非聚集索引，而InnoDB存储引擎采用的是聚集索引，我们可以通过MySQL存储的本地文件来证明这一点。

首先我们需要确定MySQL存储文件的位置，我们进入命令行界面，执行如下命令得到本地数据文件地址：

SHOW VARIABLES like 'datadir';

进入数据库所在文件目录，可以看到数据文件: 

 归纳：

1. MySQL的两个常用存储引擎（MyISAM和InnoDB）都采用B+树的数据结构来创建索引。
2. MyISAM的索引是非聚集索引，叶子节点只存储数据的地址；InnoDB的主键索引是聚集索引，并且表本身就是一个B+树索引。
3. InnoDB辅助索引的叶子节点只存储数据的主键值，需要通过主键索引查询到数据；MyISAM辅助索引存储的数据的物理地址，可直接读取到数据行。
4. 采用InnoDB存储引擎的表，必须包含主键，主键建议为整形值，并且为自增；MyISAM存储引擎不做要求。

 事务的隔离级别：

事务在并发情况下会出现读问题的情况，而数据库事务的隔离级别就是用来解决相应读问题而产生的的，事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。

1. 读未提交（Read uncommitted）

 顾名思义，就是一个事务可以读取另一个未提交事务的数据。

2. 读已提交（Read committed）

 顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但如果一个事务范围内两个相同的查询可能返回不同数据，这就是不可重复读。

3. 可重复读（Repeatable read） 

 就是在开始读取数据（事务开启）时，不再允许修改操作。

重复读可以解决不可重复读问题。不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

4.  串行化（Serializable）

 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。

MVCC机制：

MVCC (Multiversion Concurrency Control) 中文全称叫多版本并发控制 ，是现代数据库（包括 MySQL 、 Oracle 、 PostgreSQL 等）引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能 。

实现原理：

 先来理解一下版本链的概念。在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

• DATA_DATA_TRX_ID

记录最近更新这条行记录的 事务 ID ，大小为 6 个字节

• DATA_ROLL_PTR

每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个DATA_ROLL_PTR就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

• DB_ROW_ID

行标识（隐藏单调自增 ID ），大小为 6 字节，如果表没有主键， InnoDB 会自动生成一个隐藏主键，因此会出现这个列（如果已经有了主键则不会存在ROW_ID）。另外，每条记录的头信息（ record header ）里都有一个专门的 bit （ deleted_flag ）来表示当前记录是否已经被删除。

 假如数据库中现在拥有这么一条数据：

 现在有个事务id是60的执行的这条记录的修改语句:

update xxx set name = '小明1' where id = 1

此时在undo日志中就存在版本链：

事务ID为60的事务操作过程：

1. 对 DB_ROW_ID = 1 的这行记录加排他锁
2. 把该行原本的值拷贝到 undo log 中， DB_DATA_TRX_ID 和 DB_ROLL_PTR 都不动
3. 修改该行的值这时产生一个新版本，更新 DATA_DATA_TRX_ID 为修改记录的事务 ID ，将 DATA_ROLL_PTR 指向刚刚拷贝到 undo log 链中的旧版本记录，这样就能通过 DB_ROLL_PTR 找到这条记录的历史版本。如果对同一行记录执行连续的 UPDATE ， Undo Log 会组成一个链表，遍历这个链表可以看到这条记录的变迁
4. 记录 redo log ，包括 undo log 中的修改。

 ReadView：

ReadView主要包含4个比较重要的内容：

• m_ids：在生成ReadView时，当前系统中活跃的读写事务ID的列表。
• min_trx_id：在生成ReadView时，当前系统中活跃的读写事务中最小的ID；也就是m_ids中的最小值。
• max_trx_id：在生成ReadView时，系统应该分配给下一个事务的事务ID；注意max_trx_id不一定是m_ids中的最大值。事务ID是递增分配的。比如现在有事务ID分别是1、2、3的这3个事务，之后事务ID为3的事务提交了，那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值为1，max_trx_id就为4。
• creator_trx_id：生成ReadView的事务的事务ID。需要注意的是，只有对表中记录进行改动时（INSERT、UPDATE、DELETE）才会为事务分配唯一的事务ID，否则一个事务的事务ID都默认值为0；

在访问某条记录时，只需要按照下面步骤来判断记录版本是否可见。

• 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

• 如果被访问版本的trx_id属性值小于ReadView中min_trx_id，那说明生成该版本的事务在当前事务生成ReadView时就已经提交了，所以该版本可以被当前事务访问。

• 如果被访问版本的trx_id属性值大于或等于ReadView中max_trx_id，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不能被当前事务访问。

• 如果被访问版本的trx_id属性值在min_trx_id和max_trx_id之间，则需要判断trx_id属性值是否在m_ids列表中。如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

这些记录都是去版本链里面找的，先找最近记录，如果最近这一条记录事务id不符合条件，不可见的话，再去找上一个版本再比较当前事务的id和这个版本事务id看能不能访问，以此类推直到返回可见的版本或者结束。

已提交读和可重复读的区别就在于它们生成ReadView的策略不同。已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

MySQL日志：

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

事务(Transaction)实现着重于实现事务的ACID属性，即:

• 原子性(Atomic)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Duration)

事务的隔离性由锁机制和MVCC实现，原子性（Atomic）由Undo Log实现，持久性由Redo Log实现，一致性由Undo Log和Redo Log共同实现(即：数据库总是从一个一致状态转移到另一个一致状态)。

 Redo Log:

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。
比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。
InnoDB 存储引擎的存储数据存放在磁盘中，同时提供内存缓存(Buffer Pool)包含磁盘中部分数据页的映射，作为数据库访问的缓冲。Buffer Pool中修改的脏页数据会定期刷新到磁盘中。
如果MySQL宕机，而Buffer Pool的数据没有完全刷新到磁盘，就会导致数据丢失，无法保证持久性。 因此引入Redo Log解决这个问题。

• 当数据修改时，首先写入Redo Log，再更新到Buffer Pool，保证数据不会因为宕机而丢失，保证持久性。
• 当事务提交时会调用fsync将redo log刷至磁盘持久化。MySQL宕机时，通过读取Redo Log中的数据，对数据库进行恢复。

Redo Log实现:
redo log由两部分组成：

• 内存中的重做日志缓冲(redo log buffer)
• 重做日志文件(redo log file)

InnoDB通过Force Log at Commit机制保证持久性：当事务提交(COMMIT)时，必须先将该事务的所有日志缓冲写入到重做日志文件进行持久化，才能COMMIT成功。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。因此，磁盘的性能决定了事务提交的性能，也就是数据库的性能。

innodb_flush_log_at_trx_commit参数控制重做日志刷新到磁盘的策略：

• 0：事务提交时不进行写入重做日志操作，仅在master thread每秒进行一次。
• 1：事务提交时必须调用一次fsync操作。
• 2：仅写入文件系统缓存，不进行fsync操作。

log buffer根据如下规则写入到磁盘重做日志文件中：

• 事务提交时
• 当log buffer中有一半的内存空间已经被使用
• log checkpoint时

log block:

重做日志以512字节进行存储，重做日志缓存、重做日志文件都是以块(block)的方式进行保存的，称为重做日志块(redo log block)。
重做日志块由：日志快头(log block header)、日志、日志快尾(log block tailer)三部分组成。

Log Sequence Number (LSN):

LSN日志序列号占用8字节，记录重做日志当前总字节量，是单调递增的。LSN不仅记录在重做日志中，还存在于每个页中，在每个页的头部，值FIL_PAGE_LSN记录该页的LSN。表示页最后刷新时LSN的大小。

数据库启动时，页中的LSN用来判断页是否需要进行恢复操作：

• 重做日志LSN > 页中LSN，需要进行恢复操作。
• 重做日志LSN < 页中LSN，不许进行恢复操作。

SHOW ENGINE INNODB STATUS可以查看当前数据库LSN情况。

Undo Log:

Undo Log(回滚日志)用来实现事务的原子性(回滚)和隔离性(MVCC)。它和Redo Log正好相反，记录的是数据被修改前的信息，并且只记录逻辑变化，基于Undo Log进行的回滚只是对数据库进行一个相反的操作，而不是直接恢复物理页。

• 针对每个DELETE操作，生成Insert Log插入Undo Log。
• 针对每个UPDATE操作，生成相反的Update Log插入Undo Log。

Undo Log格式：

InnoDB中，undo log分为：

• Insert Undo Log：

Insert Undo Log是INSERT操作产生的undo log。
INSERT操作的记录由于是该数据的第一个记录，对其他事务不可见，该Undo Log可以在事务提交后直接删除。

• Update Undo Log：

Update Undo Log记录对DELETE和UPDATE操作产生的Undo Log。
Update Undo Log会提供MVCC机制，因此不能在事务提交时就删除，而是放入undo log链表，等待purge线程进行最后的删除。

Undo Log存储管理：

InnoDB基于Rollback Segment管理Undo Log，每个Rollback Segment记录1024个Undo Segment，Rollback Segment默认存储在共享表空间中。
当事务没有提交时，InnoDB必须保留该事务对应的Undo Log。但是当事务提交时，依然不能删除Undo Log，因为要支持MVCC，可能有其他处于Repeatable Read隔离级别下的事务，正在读取对应版本的数据。
事务提交时，虽然不会立即删除Undo Log，但是会将对应的Undo Log放入一个删除列表中，未来通过purge线程来进行判断并删除。

 Bin Log：

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。而 binlog 是逻辑日志，记录内容是语句的原始逻辑。
不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
由于 binlog 采用追加写的的逻辑，默认保存两周时长，所以理论上来说，配合全量备份，可以恢复两周内任意时刻的数据库数据。

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement：

记录的内容是SQL语句原文，同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

• row：

记录的内容不再是简单的SQL语句了，还包含操作的具体数据。update_time=now()会变成具体的时间，保证同步数据的一致性。但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

• mixed：

记录的内容是前两者的混合。MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

写入机制：

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

 binlog日志刷盘流程如下：

• 上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
• 上图的 fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

• 设置为0：表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
• 设置为1：表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
• 设置为N(N>1)：表示每次提交事务都write，但累积N个事务后才fsync。

MySQL逻辑架构

MySQL从整体上可以分为Server层和存储引擎层。

Server层：

大多数的MySQL的核心服务功能都是在Server层，它包括连接器、查询缓存、解析器、优化器、执行器。

Server层涵盖了MySQL的大部分功能，包括查询解析、分析、优化、缓存以及所有的内置函数(例如：日期、时间、数学和加密函数)，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

连接器：

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行。
当客户端应用连接到MySQL服务器时，首先接待它的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接器的连接又分为长连接和短连接：

• 长连接：连接成功后，如果客户端持续有请求，则一直使用通过一个连接。
• 短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个连接。

建立连接的过程比较复杂，现在绝大部分的服务都是使用的长连接；如果建立连接之后，客户端一直没有请求，这个时候连接就会断开。这个时间由参数wait_timeout控制，默认为8小时。

查询缓存：

建立连接之后，我们就可以执行sql语句了。它不是直接去查询表里的数据，而是先查询缓存，如果缓存中存在则直接返回缓存中的数据，就不需要后面的解析和执行步骤，效率会大大提高；缓存中不存在再去表里查询数据，然后将查询到的结果添加到缓存里。
MySQL缓存的数据是以key-value的形式存在的，key就是我们的查询sql语句，value就是这个sql语句对应的查询结果。
MySQL只要表的数据或者表结构有变化，这张表的所有缓存都会失效。
自MySQL 5.6（2013）以来，默认情况下已禁用查询缓存；MySQL8.0版本已经完全把缓存删除了。

解析器：

开始真正执行sql语句时，解析器会先分析我们输入的sql语句，MySQL解析器将sql语句解析成内部数据结构(解析树)，然后优化器就可以对其优化。
给解析器的sql语句是字符串和空格组成的，解析器第一步是解析出来字符串，识别出里面的每个字符串代表的意思。解析器会识别sql关键字，从而知道我们是在查询还是更新。
解析器会验证语法，还会根据解析到的表和列验证表和列是否存在。如果表或者列不存在，或者语法有问题，我们可以收到错误信息。

优化器：

经过解析器处理，得到了解析树。这个时候MySQL已经明确知道自己要做什么了，但是在开始执行之前还会对sql进行优化。
优化器对sql语句的优化包括：重写查询、决定表的读写顺序、选择合适的索引等。经过优化器之后，sql语句的执行方案就已经确定了，解析出来就进入执行器开始执行了。

执行器：

执行器执行sql语句的时候，会先验证是否有对这个表的权限，如果没有权限就会返回没有权限的错误信息。如果有权限，则会打开表继续执行。打开表的时候，执行器就会根据表的执行引擎，去使用执行引擎提供的接口。

存储引擎：

存储引擎层负责数据的存储和提取。存储引擎是插件式的，支持InnoDB、MyISAM、Memory等多种存储引擎，MySQL也提供了一些第三方的存储引擎，这种插件式的结构设计，使得不同的公司可以根据自己的需求选择不同的引擎。
现在最常用的存储引擎是InnoDB，它是MySQL5.5.5版本之后默认的存储引擎，如果我们在建表时不指定存储引擎类型，默认使用的就是InnoDB。
不同的存储引擎是公用Server层的；不同的引擎保存数据和索引的方式是不相同的，但是表的定义是MySQL服务层负责的，这个是一致的。

MySQL分区表：

分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。
分区功能并不是在存储引擎层完成的，常见的存储引擎InnoDB、MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

MySQL数据库在5.1版本及以上时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

分区的好处：

• 可以让单表存储更多的数据
• 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快（查询条件尽量扫描少的分区）
• 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备
• 可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
• 可以备份和恢复单个分区。

分区的限制和缺点：

• 一个表最多只能有1024个分区
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
• 分区表无法使用外键约束
• NULL值会使分区过滤无效
• 所有分区必须使用相同的存储引擎

表分区与分表的区别：

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

分区表分类：

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
• 复合分区：在MySQL 5.6版本中，只支持RANGE和LIST的子分区，且子分区的类型只能为HASH和KEY。

分区表限制：

• 分区键必须包含在表的所有主键、唯一键中。
• MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。
• 最大分区数： 不使用NDB存储引擎的给定表的最大可能分区数为8192（包括子分区）。如果当分区数很大，但是未达到8192时提示 Got error … from storage engine: Out of resources when opening file,可以通过增加open_files_limit系统变量的值来解决问题，当然同时打开文件的数量也可能由操作系统限制。
• 不支持查询缓存： 分区表不支持查询缓存，对于涉及分区表的查询，它自动禁用。 查询缓存无法启用此类查询。
• 分区的innodb表不支持外键。
• 服务器SQL_mode影响分区表的同步复制。 主机和从机上的不同SQL_mode可能会导致sql语句; 这可能导致分区之间的数据分配给定主从位置不同，甚至可能导致插入主机上成功的分区表在从库上失败。 为了获得最佳效果，您应该始终在主机和从机上使用相同的服务器SQL模式。
• 全文索引。 分区表不支持全文索引，即使是使用InnoDB或MyISAM存储引擎的分区表。
• 分区表无法使用外键约束。