MySQL

前言：
SQL(structure query language)—结构化查询语句。
数据库大体可以分为关系型数据库和非关系型数据库

关系型数据库（RDBMS）
基于SQL实现，是指采用了关系模型（库、表、行、列）来组织数据的数据库。
比如：
oracle：甲骨文公司，适合大型项目，适用于做复杂的业务逻辑。
SQL Server：微软公司，适合中大型项目，部署在windows上。
MySQL：甲骨文公司，不适合做复杂的业务逻辑。
MariaDB：基于MySQL的一个开源产品。

非关系型数据库
更多的值NoSQL数据库，不规定基于SQL实现。
比如：
基于键值对(key-value)：如memcached、redis。
基于文档型：如mongodb。
基于列族：如hbase。
基于图形：neo4j

一、SQL语言
1、DDL 数据定义语言（库和表的基本操作）
create、drop、alter、show
2、DML 数据操作语言（数据的基本操作）
insert、delete、update、select
3、DCL数据控制语言（权限管理和事务）
grant、revoke
4、DQL数据库查询语言（数据的查找）
select、from、where

service mysqld  start   开启
service mysqld  stop   关闭
service mysqld  restart  重启服务器


mysqld  -u  root -p xxx  连接服务器  

1、DDL
显示当前数据库

show databases;//每一条数据库操作语句都应该以分号 ; 结尾

创建数据库

//--创建名为db_test的数据库。
create database db_test; 
//如果系统没有db_test的数据库，则创建一个名叫db_test的数据库，否则不创建。
create database if not exists db_test;

使用数据库

use db_test;

删除数据库

drop database [if exists] db_test;

2、DML

表的操作
查看表结构

desc tablename;

创建表

create table stu_test(
	id int,
    name varchar(20),
    age int,
    amout decimal(13,2),
    birthday timestamp,
    resume text
);

删除表

drop table [if exists] stu_test;

3、数据库的约束
NULL约束

--创建表时可以指定某列不为空
create table student(
	id int not null,
    sn int,
    name varchar(20)
);

UNIQUE：唯一约束

--创建表时指定sn列为唯一的、不重复的
create table student(
	id int not null,
    sn int unique,
    name varchar(20)
);

DEFAULT：默认值约束

--指定插入数据时，name列为空，默认值为无名氏
create table student(
	id int not null,
    sn int unique,
    name varchar(20) default '无名氏'
);

PRIMARY KEY：主键约束

--指定id列为主键
create table student(
	id int primary key,
    sn int unique,
    name varchar(20) default '无名氏'
);

FOREIGN KEY：外键约束

--外键用于关联其他表的主键或唯一键
create table student(
	id int primary key,
    sn int unique,
    name varchar(20) default '无名氏',
    class_id int,
    foreign key (class_id) references classes(id)
);

CHECK约束

--mysql使用时不报错，但忽略该约束
create table student(
	id int primary key,
    name varchar(20),
    sex varchar(1),
    check (sex = '男' or sex = '女')
);

4、DML

4.1、添加数据

insert  into  stu  values("001","zhangsan","man","19");

插入部分元素

insert  into  stu(id, name, ssex,)values("001","zhangsan","man");

批量插入

//小批量
insert  into  stu  values("001","zhangsan","man","19"),
       ("002","lisi","man","17"),
       ("003","wangwu","man","22"),
       ...........................
       ("006","xxx","man","xxxx");
//大批量 load  

4.2、删除数据

delete from stu  where id = "002";  //where 过滤条件
delete from stu ://删除 stu中的所有数据

4.3修改数据

update  stu set  ssex ="woman" where id ="006";
update  stu set  ssex ="woman";

select *from stu;查看

4.4、查询数据：

关键字	返回值
count	返回查询到数据的数量
sum	返回查询到数据的总量
avg	返回查询到数据的平均值
max	返回查询到数据的最大值
min	返回查询到数据的最小值

聚合查询

--count
--统计班级共有多少同学
select count(*) from student;
--统计班级的id有多少个，为NULL不会计入
select count(id) from studentl;

--sum
--统计学号总和
select sum(sn) from student;
--学号小于1002的总和
select sum(sn) from student where sn < 1002;

--avg
--统计平均学号
select avg(sn) from student;
--统计id和学号平均和
select avg(sn + id) from student;

--max
--返回最高的学号
select max(sn) from student;

--min
--返回最低的学号
select min(sn) from student;

分组查询
select中使用group by子句可以对指定列进行分组查询

--测试案例
create table emp( 
 id int primary key auto_increment, 
 name varchar(20) not null, 
 role varchar(20) not null, 
 salary numeric(11,2) 
);

insert into emp(name, role, salary) values 
('张三','门卫', 1000.20), 
('李四','大厅服务员', 2000.99), 
('王五','采购', 999.11), 
('孙六','会计', 333.5), 
('马七','司机', 700.33), 
('吉八','董事长', 12000.66);

--查询每个角色的最高工资、最低工资和平均工资
select role, max(salary), min(salary), avg(salary) from emp group by role;

group by子句进行分组之后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要用having。

--查询平均工资低于1500的角色和它的平均工资
select role, avg(salary) from emp group by role having avg(salary) < 1500;

联合查询
实际开发中往往数据来自不同的表，所以需要多表联合查询。

--案例
insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), 
('中文系2019级3班','学习了中国传统文学'), 
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values 
('09982','黑旋风李逵','xuanfeng@qq.com',1), 
('00835','菩提老祖',null,1), 
('00391','白素贞',null,1), 
('00031','许仙','xuxian@qq.com',1), 
('00054','韩信',null,1), 
('51234','李白','say@qq.com',2), 
('83223','tellme',null,2), 
('09527','外国人','foreigner@qq.com',2);

insert into course(name) values 
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高等数学'),('英文')

--内连接:inner join(两张表中的数据的交集)
--查询许仙的成绩
select sco.score from student stu 
inner join score sco on stu.id=sco.student_id and stu.name='许仙';
--查询所有同学的成绩和个人信息
select stu.id,stu.sn,stu.NAME,sco.score,cou.id,cou.NAME 
from student stu 
join score sco on stu.id=sco.student_id
join course cou on cou.id=sco.course_id
order by stu.id;

--左连接:left join(以左表的数据作为基表数据，在右表中找寻符合条件的数据，找不到以NULL展示)
select * from student stu 
left join score sco on stu.id=sco.student_id;

--右连接:right join(以右表的数据作为基表数据，在左表中找寻符合条件的数据，找不到以NULL展示)
select * from score sco 
right join student stu on stu.id=sco.student_id;

--自连接:将自己的表进行连接，需要对表名进行别名显示
--查询成绩表中计算机原理比java成绩好的信息
select s1.* from score s1 
join score s2 on s1.student_id=s2.student_id
and s1.score<s2.score
and s1.course_id=1
and s2.course_id=3;

子查询
查询的条件是另一条语句的结果

--查询与不想毕业同学的同班同学
select * from student where classes_id=(select classes_id from student where name='不想毕业');

--查询语文或英文课程的成绩信息
select * from score where course_id in (select id from course where name='Java' or name='

合并查询

--union(会自动去重)
--查询id小于3，或者名字为英文的课程
select * from course where id<3
union
select * from course where name='英文';

select * from course where id<3 or name='英文';
--使用or会忽略索引，在海量数据查询中性能会降低

--union all(不会去重)
select * from course where id<3
union all
select * from course where name='英文';

去重查询distinct

select distinct age from stu;

5、DCL

新建的库没有任何权限（包括授权权限）需要root用户授权
grant 授权
revoke 回收权限

grant select,delete  on TL13,* to u0804;
revoke delete  on TL13,* to u0804;

root —r1----r2-------r3------r4
root分配给r1的权限只能由root回收

二、MySQL
存储引擎、索引及底层实现、事务、锁机制、触发器和存储过程

1、存储引擎（数据的存取方式）
MyISAM
不支持事务，支持全文索引，但是对于一些在线分析处理操作速度快。
文件组成由 myd 存放数据的 myi存放索引的
InnoDB
支持事务，主要是面向在线事务处理方面的应用，特点是行锁设计，并支持外键。Innodb采用聚集索引的方式。没有主键，没有唯一键，为每一行生产一个6字节的行id,作为主键。
Memory
将数据放在内存中，如果数据库重启或者宕机，表数据就会丢失。非常适合存储一些临时表，默认的是哈希索引，不是B+树索引，varchar()默认是按照char()存储的，浪费内存。
不支持text和BLOB类型。如果数据中有text和BLOB类型，数据库会把这些数字转换到磁盘上。
Archive
只支持INSERT和SELECT操作，使用压缩算法将数据进行压缩后存储，压缩比例一般是1:10，主要提供插入和压缩功能。

2、索引及底层实现
关于MySQL索引的好处，如果正确合理设计并且使用索引的mysql是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。其多个数据表都会对经常被查询的字段添加索引
MySQL索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部200条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？上面的两个图片鲜明的用时对比已经给出了答案（注：一般数据库默认都会为主键生成索引）。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

MySQL索引的类型
1. 普通索引
这是最基本的索引，它没有任何限制， MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

01	–直接创建索引
02	CREATE INDEX index_name ON table(column(length))
03	–修改表结构的方式添加索引
04	ALTER TABLE table_name ADD INDEX index_name ON (column(length))
05	–创建表的时候同时创建索引
06	CREATE TABLE `table` (
07	`id` int(11) NOT NULL AUTO_INCREMENT ,
08	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
09	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
10	`time` int(10) NULL DEFAULT NULL ,
11	PRIMARY KEY (`id`),
12	INDEX index_name (title(length))
13	)
14	–删除索引
15	DROP INDEX index_name ON table

2. 唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

01	–创建唯一索引
02	CREATE UNIQUE INDEX indexName ON table(column(length))
03	–修改表结构
04	ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
05	–创建表的时候直接指定
06	CREATE TABLE `table` (
07	`id` int(11) NOT NULL AUTO_INCREMENT ,
08	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
09	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
10	`time` int(10) NULL DEFAULT NULL ,
11	PRIMARY KEY (`id`),
12	UNIQUE indexName (title(length))
13	);

3. 全文索引（FULLTEXT）
MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

01	–创建表的适合添加全文索引
02	CREATE TABLE `table` (
03	`id` int(11) NOT NULL AUTO_INCREMENT ,
04	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
05	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
06	`time` int(10) NULL DEFAULT NULL ,
07	PRIMARY KEY (`id`),
08	FULLTEXT (content)
09	);
10	–修改表结构添加全文索引
11	ALTER TABLE article ADD FULLTEXT index_content(content)
12	–直接创建索引
13	CREATE FULLTEXT INDEX index_content ON article(content)

4. 单列索引、多列索引
多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
5. 组合索引（最左前缀）
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

1	–使用到上面的索引
2	SELECT * FROM article WHREE title='测试' AND time=1234567890;
3	SELECT * FROM article WHREE utitle='测试';
4	–不使用上面的索引
5	SELECT * FROM article WHREE time=1234567890;

MySQL索引的优化
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

1. 何时使用聚集索引或非聚集索引？
   

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。其实这个具体用法我还不是很理解，只能等待后期的项目开发中慢慢学学了。
2. 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3. 使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
4. 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5. like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6. 不要在列上进行运算
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

Show index on tablename;
Explain select * from table;

3、事务
3.1什么是事务
事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

3.2为什么要讲InnoDB的事务呢？
严格上来说，事务必须同时满足4个特性，即通常所说事务的ACID特性。虽然理论上定义了严格的事务要求，但是数据库厂商出于各种目的并没有严格满足事务的ACID标准。例如，对于MYSQL的NDB Cluster引擎，虽然支持事务，但是不满足D的要求，即持久性的要求。对于Oracle数据库来说，其默认的事务隔离级别为READ COMMITTED，不满足I的要求，即隔离性的要求。对于InnoDB存储引擎而言，默认的事务隔离级别是READ REPRATABLE，完全遵循和满足事务的ACID特性。

3.3什么是事务的ACID

1. A(atomicity) 原子性。一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。
2. C(consistency) 一致性。一个事务的执行不应该破坏数据库的完整性约束。如果上述例子中第2个操作执行后系统崩溃，保证A和B的金钱总计是不会变的。
3. I(isolation) 隔离性。通常来说，事务之间的行为不应该互相影响。然而实际情况中，事务相互影响的程度受到隔离级别的影响。文章后面会详述。
4. D(durability) 持久性。事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

3.4、事务不隔离会产生什么问题
1）第一类丢失更新
：在没有事务隔离的情况下，两个事务都同时更新一行数据，但是第二个事务却中途失败退出， 导致对数据的两个修改都失效了。

例如：

张三的工资为5000，事务A中获取工资为5000，事务B获取工资为5000，汇入100，并提交数据库，工资变为5100，

随后
事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。

2）脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
例如：
　　张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
　　与此同时，
　　事务B正在读取张三的工资，读取到张三的工资为8000。
　　随后，
　　事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
　　最后，
　　事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

3）不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
　　在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B把张三的工资改为8000，并提交了事务。
　　随后，
　　在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

4）第二类丢失更新：不可重复读的特例。有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。

例如：

在事务A中，读取到张三的存款为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B，存储1000，把张三的存款改为6000，并提交了事务。
　　随后，
　　在事务A中，存储500，把张三的存款改为5500，并提交了事务，这样事务A的更新覆盖了事务B的更新。

**5）幻读：**是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
　　目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
　　此时，
　　事务B插入一条工资也为5000的记录。
　　这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

提醒：
不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

1.READ UNCOMMITTED(未提交读)。在RU的隔离级别下，事务A对数据做的修改，即使没有提交，对于事务B来说也是可见的，这种问题叫脏读。这是隔离程度较低的一种隔离级别，在实际运用中会引起很多问题，因此一般不常用。

2.READ COMMITTED(提交读)。在RC的隔离级别下，不会出现脏读的问题。事务A对数据做的修改，提交之后会对事务B可见，举例，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，会读到最新的数据2。在RC的隔离级别下，会出现不可重复读的问题。这个隔离级别是许多数据库的默认隔离级别。

3.REPEATABLE READ(可重复读)。在RR的隔离级别下，不会出现不可重复读的问题。事务A对数据做的修改，提交之后，对于先于事务A开启的事务是不可见的。举例，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，仍然只能读到1。在RR的隔离级别下，会出现幻读的问题。幻读的意思是，当某个事务在读取某个范围内的值的时候，另外一个事务在这个范围内插入了新记录，那么之前的事务再次读取这个范围的值，会读取到新插入的数据。Mysql默认的隔离级别是RR，然而mysql的innoDB引擎间隙锁成功解决了幻读的问题。

4.SERIALIZABLE(可串行化)。可串行化是最高的隔离级别。这种隔离级别强制要求所有事物串行执行，在这种隔离级别下，读取的每行数据都加锁，会导致大量的锁征用问题，性能最差。
查询默认的隔离级别 ：
select @@tx_isolation;

第一个小人，可能读到1-20之间的任何一个。因为未提交读的隔离级别下，其他事务对数据的修改也是对当前事务可见的。第二个小人可能读到1，10和20，他只能读到其他事务已经提交了的数据。第三个小人读到的数据去决于自身事务开启的时间点。在事务开启时，读到的是多少，那么在事务提交之前读到的值就是多少。第四个小人，只有在A end 到B start之间开启，才有可能读到数据，而在事务A和事务B执行的期间是读不到数据的。因为第四小人读数据是需要加锁的，事务A和B执行期间，会占用数据的写锁，导致第四个小人等待锁。

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。mysql中支持事务的存储引擎有innoDB和NDB。innoDB是mysql默认的存储引擎，默认的隔离级别是RR，并且在RR的隔离级别下更进一步，通过多版本并发控制（MVCC，Multiversion Concurrency Control ）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此innoDB的RR隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。
事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。说到事务日志，不得不说的就是redo和undo。
1.redo log
在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
在系统启动的时候，就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。如下一个简单示例：
记录1：<trx1, insert…>
记录2：<trx2, delete…>
记录3：<trx3, update…>
记录4：<trx1, update…>
记录5：<trx3, insert…>
2.undo log
undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
以下是undo+redo事务的简化过程
假设有2个数值，分别为A和B,值为1，2

1. start transaction;
2. 记录 A=1 到undo log;
3. update A = 3；
4. 记录 A=3 到redo log；
5. 记录 B=2 到undo log；
6. update B = 4；
7. 记录B = 4 到redo log；
8. 将redo log刷新到磁盘
9. commit
   在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化。若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。
   所以，redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。

伪事务（锁定）
1、在MySQL中根据不同的需求，提供了很多存储引擎，但是有的存储引擎不支持事务，对于这种情况，可以使用表锁定来代替事务。
2、对于不支持事务的存储引擎MYISAM类型数据表，当用户插入，修改，删除时，这些操作都会立即保存到磁盘中，当多用户同时操作某个表时，可以使用表锁定来避免同一时间有多个用户对数据库中指定表进行操作，这样可以避免在用户操作数据表过程中受到干扰。只有但用户释放表的操作锁定后，其他 用户才可以访问这些修改的数据表。

三、SQL优化
1.大批量插入 使用load

load data infile '/home/mysql/film_test.txt' into table film_test2;
myisam

DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入
大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于
导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。
如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自
动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
Innodb
因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺
序排列，可以有效地提高导入数据的效率。
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行
SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。(保证主键列数据是唯一的)
2 优化 INSERT 语句
当进行数据 INSERT 的时候，可以考虑采用以下几种优化方式。
 如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大
缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语
句快(在一些情况中几倍)。下面是一次插入多值的一个例子：
insert into test values(1,2),(1,3),(1,4)…
 如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。
DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有
真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其
他用户对表的读写完后才进行插入；
 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
 如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，
这只能对 MyISAM 表使用；
 当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语
句快 20 倍。
3 优化 GROUP BY 语句
默认情况下，MySQL 对所有 GROUP BY col1，col2…的字段进行排序。这与在查询中指定
ORDER BY col1，col2…类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则
对 MySQL 的实际执行性能没有什么影响。
如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL
禁止排序，如下面的例子：

explain select id,sum(moneys) from sales2 group by id\G;
explain select id,sum(moneys) from sales2 group by id order by null\G;

4 优化 ORDER BY 语句：
在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。
WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且
ORDER BY 的字段都是升序或者都是降序。
例如，下列 SQL 可以使用索引。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是在以下几种情况下则不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

–用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2；

–对不同的关键字使用 ORDER BY：

5 优化嵌套查询
MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查
询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很
多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起
来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。
在下面的例子中，要从sales2表中找到那些在company2表中不存在的所有公司的信息：

explain select * from sales2 where company_id not in ( select id from
company2 )\G;

如果使用连接（JOIN）来完成这个查询工作，速度将会快很多。尤其是当 company2 表
中对 id 建有索引的话，性能将会更好，具体查询如下：

explain select * from sales2 left join company2 on sales2.company_id =
company2.id where sales2.company_id is null\G;

从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。
连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

6 MySQL 如何优化 OR 条件
对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；
如果没有索引，则应该考虑增加索引。
例如，首先使用 show index 命令查看表 sales2 的索引，可知它有 3 个索引，在 id、year
两个字段上分别有 1 个独立的索引，在 company_id 和 year 字段上有 1 个复合索引。
show index from sales2\G;
可以发现查询正确的用到了索引，并且从执行计划的描述中，发现 MySQL 在处理含有 OR
字句的查询时，实际是对 OR 的各个字段分别查询后的结果进行了 UNION。
但是当在建有复合索引的列company_id 和 moneys上面做 OR 操作的时候，却不能用到索引，
具体结果如下：

explain select * from sales2 where company_id = 3 or moneys = 100\G;

四、触发器
触发器是一个特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL
也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。
REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。
INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。

创建触发器
语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与临时表表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
· INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
· UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
· DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
请注意，trigger_event与以表操作方式激活触发程序的SQL语句并不很类似，这点很重要。
例如，关于INSERT的BEFORE触发程序不仅能被INSERT语句激活，也能被LOAD DATA语句激活。

可能会造成混淆的例子之一是INSERT INTO … ON DUPLICATE UPDATE …语法：BEFORE INSERT触发程序对于每一行将激活，后跟AFTER INSERT触发程序，或BEFORE UPDATE和AFTER UPDATE触发程序，具体情况取决于行上是否有重复键。
对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
例如，对于某一表，不能有两个BEFORE UPDATE触发程序。
但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

trigger_stmt是当触发程序激活时执行的语句。
如果你打算执行多个语句，可使用BEGIN … END复合语句结构。这样，就能使用存储子程序中允许的相同语句。

创建一个单执行语句的触发器

CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @SUM=@SUM+new.amount;

首先创建一个account表，表中有两个字段，分别为：acct_num字段（定义为int类型）
amount字段（定义成浮点类型）；其次创建一个名为ins_sum的触发器，触发的条件是向数据表account插入数据之前，
对新插入的amount字段值进行求和计算

DECLARE @num INT
SET @num=0
INSERT INTO account VALUES(1,1.00),(2,2.00)
SELECT @num

首先创建一个account表，在向表account插入数据之前，计算所有新插入的account表的amount值之和，
触发器的名称为ins_num，条件是在向表插入数据之前触发。

创建有多个执行语句的触发器，语法如下：

DELIMITER |
 
CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END

查看触发器
查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。
可以使用SHOW TRIGGERS 和在TRIGGERS 表中查看触发器信息

SHOW TRIGGERS 
 

Trigger  Event   Table    Statement                 Timing  Created  sql_mode  Definer         character_set_client  collation_connection  Database Collation
-------  ------  -------  ------------------------  ------  -------  --------  --------------  --------------------  --------------------  ------------------
ins_sum  INSERT  account  set @sum=@sum+new.amount  BEFORE  (NULL)             root@localhost  utf8                  utf8_general_ci       utf8_general_ci   

EVENT表示激活触发器的事件，这里的触发事件为插入操作INSERT，TABLE表示激活触发器的对象表，这里为account 表
Timing表示触发器的时间，为插入之前（BEFORE）；Statement 表示触发器执行的操作，还有一些其他信息，比如SQL模式，触发器的定义帐户和字符集等

在TRIGGERS 表中查看触发器信息
information_schema数据库的TRIGGERS 表中，可以通过查询查看触发器信息

SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='ins_sum'

TRIGGER_CATALOG  TRIGGER_SCHEMA  TRIGGER_NAME  EVENT_MANIPULATION  EVENT_OBJECT_CATALOG  EVENT_OBJECT_SCHEMA  EVENT_OBJECT_TABLE  ACTION_ORDER  ACTION_CONDITION  ACTION_STATEMENT          ACTION_ORIENTATION  ACTION_TIMING  ACTION_REFERENCE_OLD_TABLE  ACTION_REFERENCE_NEW_TABLE  ACTION_REFERENCE_OLD_ROW  ACTION_REFERENCE_NEW_ROW  CREATED  SQL_MODE  DEFINER         CHARACTER_SET_CLIENT  COLLATION_CONNECTION  DATABASE_COLLATION
---------------  --------------  ------------  ------------------  --------------------  -------------------  ------------------  ------------  ----------------  ------------------------  ------------------  -------------  --------------------------  --------------------------  ------------------------  ------------------------  -------  --------  --------------  --------------------  --------------------  ------------------
def              school          ins_sum       INSERT              def                   school               account                        0  (NULL)            set @sum=@sum+new.amount  ROW                 BEFORE         (NULL)                      (NULL)                      OLD                       NEW                       (NULL)             root@localhost  utf8                  utf8_general_ci       utf8_general_ci   

TRIGGER_SCHEMA 表示触发器所在的数据库
TRIGGER_NAME表示触发器的名称
EVENT_OBJECT_TABLE表示在哪个表上触发
ACTION_STATEMENT 表示触发器触发的时候执行的具体操作
ACTION_ORIENTATION是ROW，表示在每条记录上都触发
ACTION_TIMING表示触发的时刻是BEFORE

删除触发器
使用DROP TRIGGER 语句可以删除MYSQL中已经定义的触发器，删除触发器的基本语法

DROP TRIGGER [schema_name.]trigger_name

其中（schema_name）是可选的
如果省略了schema（方案），将从当前方案中舍弃触发程序。
删除ins_sum触发器

DROP TRIGGER `school`.`ins_sum`


触发器ins_sum删除成功

总结
对于相同的表，相同的事件只能创建一个触发器，比如对表account创建了BEFORE INSERT触发器
那么如果对表account再次创建一个BEFORE INSERT触发器，MYSQL就会报错，此时，只可以在表account上
创建AFTER INSERT或者BEFORE UPDATE类型的触发器

五、存储过程
MySQL中，创建存储过程的基本形式如下：
1.CREATE PROCEDURE sp_name ([proc_parameter[,…]])
2. [characteristic …] routine_body
其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。
proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下：
1.[ IN | OUT | INOUT ] param_name type

其中，IN表示输入参数；OUT表示输出参数； INOUT表示既可以是输入，也可以是输出； param_name参数是存储过程的参数名称；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。
characteristic参数有多个取值。其取值说明如下：
LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。
[NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。
COMMENT ‘string’：注释信息。
技巧：创建存储过程时，系统默认指定CONTAINS SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为NO SQL。而且，存储过程中最好在COMMENT部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。

【示例14-1】 下面创建一个名为num_from_employee的存储过程。代码如下：

1.CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )  
2.          READS SQL DATA  
3.          BEGIN  
4.              SELECT  COUNT(*)  INTO  count_num  
5.              FROM  employee  
6.              WHERE  d_id=emp_id ;  
7.          END 

上述代码中，存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录，并用COUNT(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中。代码的执行结果如下：

1.mysql> DELIMITER &&  
2.mysql> CREATE  PROCEDURE  num_from_employee
(IN emp_id INT, OUT count_num INT )  
3.    -> READS SQL DATA  
4.    -> BEGIN  
5.    -> SELECT  COUNT(*)  INTO  count_num  
6.    -> FROM  employee  
7.    -> WHERE  d_id=emp_id ;  
8.    -> END &&  
9.Query OK, 0 rows affected (0.09 sec)  
10.mysql> DELIMITER ; 

代码执行完毕后，没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程，数据库中会执行存储过程中的SQL语句。
说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来 结束。为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。

2 创建存储函数
在MySQL中，创建存储函数的基本形式如下：

1.CREATE FUNCTION sp_name ([func_parameter[,...]])  
2.        RETURNS type  
3.        [characteristic ...] routine_body 

其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；RETURNS type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的，请读者参照14.1.1小节的内容；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。
func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
1.param_name type
其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是MySQL数据库的任意数据类型。
【示例14-2】 下面创建一个名为name_from_employee的存储函数。代码如下：

1.CREATE  FUNCTION  name_from_employee (emp_id INT )  
2.          RETURNS VARCHAR(20)  
3.          BEGIN  
4.              RETURN  (SELECT  name  
5.              FROM  employee  
6.              WHERE  num=emp_id );  
7.          END 

上述代码中，存储函数的名称为name_from_employee；该函数的参数为emp_id；返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录，并将该记录的name字段的值返回。代码的执行结果如下：

1.mysql> DELIMITER &&  
2.mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )  
3.    -> RETURNS VARCHAR(20)  
4.    -> BEGIN  
5.    -> RETURN  (SELECT  name  
6.    -> FROM  employee  
7.    -> WHERE  num=emp_id );  
8.    -> END&&  
9.Query OK, 0 rows affected (0.00 sec)  
10.mysql> DELIMITER ; 

结果显示，存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。

3、变量的使用
在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。
1．定义变量
MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

1.DECLARE  var_name[,...]  type  [DEFAULT value] 

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。
【示例14-3】 下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

1.DECLARE  my_sql  INT  DEFAULT 10 ; 

2．为变量赋值
MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

1.SET  var_name = expr [, var_name = expr] ... 

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
【示例14-4】 下面为变量my_sql赋值为30。代码如下：

1.SET  my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

1.SELECT  col_name[,…]  INTO  var_name[,…]  
2.    FROM  table_name  WEHRE  condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。
【示例14-5】 下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

1.SELECT  d_id  INTO  my_sql  
2.        FROM  employee  WEHRE  id=2 ;