一、MySQL 优化概述

​ 页面动态化，memcache 是通过缩小对 mysql 操作来晋升访问速度。

​ 然而一个网站总是要操作数据库，如何晋升对 mysql 的操作速度。

方针：

1. 存储层：数据表 ” 存储引擎 ” 选取、字段类型选取、逆范式(三范式)
2. 设计层：索引、分区 / 分表、存储过程、sql 语句的优化
3. 架构层：分布式部署(集群)(读写拆散)，须要减少硬件
4. sql 语句层：后果一样的状况下，要抉择效率高、速度快、节俭资源的 sql 语句执行

二、存储引擎的抉择

1、存储引擎介绍

​ 相熟的存储引擎：Myisam、InnoDB、memory

1、什么是存储引擎

数据表存储数据的一种格局。数据存储在不同的格局里边，改格局体现的个性也是不一样的。

例如：

​ InnoDB 存储引擎的个性有反对事务、反对行级锁。mysiam 反对的个性有压缩机制等。

mysql 中的数据是通过各种不同的技术 (格局) 存储在文件 (或者内存) 中的。技术和自身的个性就称为“存储引擎”。

2、存储引擎的了解

现实生活中，楼房、平房就是具体存储人的存储引擎。楼房、平房有本人独特的技术个性。

例如楼房有楼梯、电梯、平房能够本人打井喝水等。

3、存储引擎所处的地位

存储引擎，处于 MySql 服务器的最底层，间接存储数据，导致下层的操作，依赖于存储引擎的抉择。

客户端 -》网络连接层 -》业务逻辑层（编译，优化，执行 SQL）-》存储引擎层

​ 查看以后 mysql 反对的存储引擎列表：show engines

4、罕用存储引擎

1. Myisam：表锁，全文索引
2. Innodb：行 (记录) 锁，事务（回滚），外键
3. Memory：内存存储引擎，速度快、数据容易失落

2、innodb 存储引擎

​ >=5.5 版本中默认的存储引擎，MySql 举荐应用的存储引擎。提供事务，行级锁定，存储引擎。

​ 事务平安型存储引擎，更加重视数据的完整性和安全性。

1、存储格局

innodb 存储引擎 每个数据表有独自的“构造文件”——*.frm

数据 ， 索引 集中存储，存储于同一个 表空间文件 中——ibdata1。

ibdata1 就是 InnoDB 表的共享存储空间，默认 innodb 所有表的数据都在一个 ibdata1 里。

例：

-- 创立 innodb 表
create table t1(id int,name varchar(32)) engine innodb charset utf8;

.frm 表构造文件。

innodb 表空间文件：存储 innodb 的数据和索引。

默认，所有的 innodb 表的 数据 和索引 在同一个表空间文件中，

通过配置能够达到每个 innodb 的表对应一个表空间文件。

show variables like ‘innodb_file_per_table%’

例：

-- 开启该配置
set  global  innodb_file_per_table=1;

​ 创立一个 innodbd 的表进行测试应用。

create table t2(id int,name varchar(32)) engine innodb charset utf8;

查看表对应的文件本人独立的“数据 / 索引”文件

系统配置参数 innodb_file_per_table 前期无论产生任何变动，t2 都有本人独立的“数据 / 索引”文件。

留神

​ 相比拟之下，应用独占表空间的效率以及性能会更高一点。

​ innodb 数据表不能间接进行文件的复制 / 粘贴进行备份还原，能够应用如下指令

mysqldump  -uroot - p 明码  数据库名字 > f:/ 文件名称.sql  [备份]

mysql -uroot  - p 明码 数据库   <  f:/ 文件名称.sql  [还原]

2、数据是依照主键顺序存储

在 innodb 数据表，数据的写入程序 与 存储的程序不统一，须要依照主键的程序把记录摆放到对应的地位下来，速度比 Myisam 的要稍慢。

create table t3(
id int primary key auto_increment,
name varchar(32) not null
)engine innodb charset utf8;
-- 给 innodb 数据表写入 4 条记录信息(主键 id 值程序不同)
insert into t3 values(223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');

插入时做排序工作，效率低。

3、并发解决

善于解决并发的。

行级锁定(row-level locking)，实现了行级锁定，在肯定状况下，能够抉择行级锁来晋升并发性，也反对表级锁定，innodb 依据操作抉择。

锁机制

​ 当客户端操作表（记录）时，为了保障操作的隔离性（多个客户端操作不能相互影响），通过加锁来解决

操作方面：

​ 读锁：读操作时减少的锁，也叫共享锁，S-lock。特色是所有人都只能够读，只有开释锁之后才能够写。

​ 写锁：写操作时减少的锁，也叫独占锁或排他锁，X-lock。特色，只有锁表的客户能够操作（读写）这个表，其余客户读都不能读。

锁定粒度（范畴）

​ 表级锁：开销小，加锁快，产生锁抵触的概率最高，并发度最低。myisam 和 innodb 都反对。

​ 行级锁：开销大，加锁慢，产生锁抵触的概率最低，并发度也最高。innodb 反对

3、Mysiam 存储引擎

<=5.5mysql 默认的存储引擎。

（ISAM——索引程序拜访办法）是 Indexed Sequential Access Method(索引程序存取方法)的缩写

它是一种索引机制，用于高效拜访文件中的数据行，善于与解决高速读与写。

1、存储形式

数据，索引，构造别离存储于不同的文件中。

create table t4(id int,name varchar(32)) engine myisam charset utf8;

mysiam 存储引擎数据表，每个数据表都有三个文件.frm（构造文件）.MYD(数据文件) *.MYI(索引文件)

这三个文件反对物理复制、粘贴操作(间接备份还原)。

2、数据的存储程序为插入程序

create table t5(
id int primary key auto_increment,
name varchar(32) not null
)engine myisam  charset utf8;
insert into t5 values(2223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');

数据查问的程序，与写入的程序统一。

数据写入时候，没有依照主键 id 值给予排序存储，该特点导致数据写入的速度十分快

3、并发性

mysiam 的并发性较比 innodb 要稍逊色（mysiam 不反对事务）因为数据表是“表锁”

myisam 和 innodb 的取舍

​ 如果表对事务的要求不高，同时是以查问和增加为主，咱们思考应用 MyISAM 存储引擎，比方 bbs 中的发帖表，回复表。

​ 对事务要求高，保留的数据都是重要数据，咱们倡议应用 INNODB，比方订单表，库存表，商品表，账号表等等。

4、memory 存储引擎

内存存储引擎，

特点：外部数据运行速度十分快，长期存储一些信息

毛病：服务器如果断电，重启，就会清空该存储引擎的全副数据

create table t6(id int,name varchar(32)) engine memory charset utf8;

mysql 服务，重启后，数据失落。

三、查找须要优化语句

1、慢查问日志

是一种 mysql 提供的日志，记录所有执行工夫超过某个工夫界线的 sql 的语句。这个工夫界线，咱们能够指定。在 mysql 中默认没有开启慢查问，即便开启了，只会记录执行的 sql 语句超过 10 秒的语句。

形式一、长期启动慢查问记录日志

mysqld.exe   --slow-query-log 

留神：先把 mysql 敞开后，再执行以上指令启动。

通过慢查问日志定位执行效率较低的 SQL 语句。慢查问日志记录了所有执行工夫超过 long_query_time 所设置的 SQL 语句。

1. 在默认状况下，慢查问日志是存储到 data 目录上面的。依据配置文件外面的配置，找到 data 的存储门路。

1. 能够通过命令查看慢查问日志的工夫

   show variables like 'long_query_time';

批改慢查问日志工夫：

-- 改为 0.5s
set  long_query_time=0.5;

1. 测试查问

   -- benchmark(count,expr)函数能够测试执行 count 次 expr 操作须要的工夫。select benchmark(100000000,90000000*4);

   查看慢查问日志

1. 个别状况下，一个 sql 语句执行比较慢，起因是没有索引

   没有增加索引之前查问工夫如下：

   增加索引之前, 索引文件大小如下；

   增加索引：

   alter  table  emp add index(empno);

   增加索引后，索引文件变大。

   增加索引之后须要的工夫；

论断：创立完索引后，索引文件会变大，增加索引会显著的进步查问速度。

形式二、间接批改配置文件

在配置文件中指定：

log-slow-queries=”d:/slow-log”

慢查问日志文件存储的门路，以后是把慢查问日志存储到 d: 盘上面，文件名为 slow-log

long_query_time=1

指定慢查问的工夫，默认是 10 秒，咱们自定义成 1 或 0.05 秒，也就是说当一个 sql 语句的执行速度超过 1 秒时，会把该语句增加到慢查问日志外面，

留神：通过配置文件是永远的开启慢查问日志

2、准确记录查问工夫

​ 应用 mysql 提供 profile 机制实现。

​ profile 记录每次执行的 sql 语句的具体工夫，准确工夫到小数点 8 位

开启 profile 机制:

set profiling = 1;

执行须要剖析的 sql 语句（自动记录该 sql 的工夫）

查看记录 sql 语句的执行工夫：

show profiles;

留神：不须要剖析时，最好将其敞开

set profiling=0;

四、索引解说

1、索引的根本介绍

利用关键字，就是记录的局部数据（某个字段，某些字段，某个字段的一部分），建设与记录地位的对应关系，就是索引。

索引的作用：是用于疾速定位理论数据地位的一种机制。

例如：字典的  检索
    写字楼  导航

2、索引的类型

​ 主键索引，惟一索引，一般索引，全文索引

​ 无论任何类型，都是通过建设 关键字 与地位 的对应的关系来实现的。

​ 以上类型的差别，是对关键字的要求不同。

​ 关键字：记录的局部数据（某个字段，某些字段，某个字段的一部分）

一般索引：对关键字没有要求。

惟一索引：要求关键字不能反复，同时减少惟一束缚。

主键索引：要求关键字不能反复，也不能为 NULL。同时减少主键束缚。

全文索引：关键字的起源不是所有字段的数据，而是从字段中提取的特地关键词。

关键词的起源：能够是某个字段，也能够是某些字段（复合索引）。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。

alter table emp add   index (field1,field2);

3、索引治理语法

1、创立索引

-- 该语句增加一个主键，这意味着索引值必须是惟一的，且不能为 null
alter table 表名 add primary key (column_list);    
        
-- 该语句创立索引的值必须是惟一的（除了 null 外，null 可能会呈现屡次）alter table 表名 add unique [索引名] (column_list);
 
-- 增加一般索引，索引值可呈现屡次
alter table 表名 add index [索引名] (column_list);            
 
-- 该语句指定了索引为 fulltext，用于全文索引
alter table 表名 add fulltext [索引名] (column_list);

2、删除索引

-- 主键索引的删除，如果没有 auto_increment 属性则应用 alter table  表名 drop primary key
alter table  表名 drop primary key; 

-- 如果在删除主键索引时，该字段中有 auto_increment 则先去掉该属性再删除。alter table 表名 modify id int unsigned not null comment '主键';

-- 删除一般索引，惟一索引，全文索引，复合索引；alter table 表名  drop index 索引的名称;
如果没有指定索引名，则能够通过查看索引的办法失去索引名（个别依赖于索引字段的名字）

3、查看索引

show indexes from 表名;
show index from 表名 \G
show create table 表名;
show keys from 表名;
desc 表名;

4、创立索引注意事项

第一：较频繁的作为查问条件字段 应该创立索引

​ select * from emp where empno = 1

第二：唯一性太差的字段不适宜独自创立索引，即便频繁作为查问条件

​ select * from emp where sex = ‘ 男‘

第三：更新十分频繁的字段不适宜创立索引

​ select * from emp where logincount = 1

第四：不会呈现在 WHERE 子句中字段不该创立索引

五、执行打算

次要用于剖析 sql 语句的执行状况（并不执行 sql 语句）失去 sql 语句 是否 应用了 索引 ，应用了 哪些索引

explain sql 语句 \G   
    或 
desc sql 语句 \G 

1、增加索引进行查看

2、删除索引时，在看执行打算

六、索引的数据结构

查看索引的类型

show keys from 表名;

1、myisam 的存储引擎的索引构造

索引的节点中存储的是数据的物理地址（磁道和扇区）

在查找数据时，查找到索引后，依据索引节点中记录的物理地址，查找到具体的数据内容。

2、innodb 的存储引擎的索引构造

innodb 的主键索引文件上 间接寄存该行数据, 称为 聚簇索引,

非主索引指向对主键的援用（非主键索引的节点存储是主键的 id）

比方要通过 nam 创立的索引，查问 name=’采臣’的, 先依据 name 建设的索引，找出该条记录的主键 id，再依据主键的 id 通过主键索引找出该条记录。

留神: innodb 来说：

1: 主键索引 既存储索引值, 又存储行的数据

2: 如果没有主键, 则会 Unique key 做主键

3: 如果没有 unique, 则系统生成一个外部的 rowid 做主键.

4: 像 innodb 中, 主键的索引构造中, 既存储了主键值, 又存储了行数据, 这种构造称为”聚簇索引”

聚簇索引

​ 劣势: 依据主键查问条目比拟少时, 不必回行(数据就在主键节点下)

​ 劣势: 如果碰到不规则数据插入时, 造成频繁的页决裂（索引的节点挪动）.

区别：

innodb 的主索引文件上 间接寄存该行数据, 称为聚簇索引, 非主索引指向对主键的援用

myisam 中, 主索引和非主索引, 都指向物理行(磁盘地位).

七、索引笼罩

如果查问的列恰好是索引的一部分，那么查问只须要在索引区上进行，不须要到数据区再找数据，这种查问速度十分快，称为“索引笼罩”

索引笼罩就是，我要在书里 查找一个内容，因为目录写的很具体，我在目录中就获取到了，不须要再翻到该页查看。

筹备两张表来测试应用；

案例 1，比方给 id 建设了主键索引，应用 id 查问数据。

在 user 表外面，给 name 字段增加索引，查问 name, 就用到了索引笼罩。

案例 2：比方给 id 和 name 建设了复合索引，应用 name 作为条件查问。

典型状况如下：

学生表：共 30 个字段。常常查问某几个字段，就把某几个字段独自做成复合索引。

Alter table student add index (name, id, height, gender, class_id);
select name, id, height, gender, class_id from student;

负面影响，减少了索引的尺寸。

建设复合索引时，应保障该索引的使用率尽可能高，索引笼罩才有意义。

八、索引的应用准则

1、列独立

只有参加条件表达式的字段独立在关系运算符的一侧，该字段才可能应用到索引。

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

2、like 查问

在应用 like(含糊匹配)的时候，在右边没有通配符的状况下，才能够应用索引。

在 mysql 里，以 % 结尾的 like 查问，用不到索引。

留神：如果 select 的字段正好就是索引，那么会用到索引即索引笼罩

如果该表改为 innodb 引擎，

alter table user engine innodb;

因为非主键索引中存储的是 id,select 的字段是 id 因而用到了索引笼罩。

比方对 name 建设了索引，如下查问，就用到了索引笼罩。

留神以下查问会用到索引：

3、OR 运算都具备索引

如果呈现 OR(或者)运算，要求所有参加运算的字段都存在索引，才会应用到索引。

如下：name 有索引，classid 没有索引

如下：id 有索引，name 有索引

4、复合索引应用

以后查问环境：

最左准则：对于创立的多列 (复合) 索引，只有查问条件应用了最右边的列，索引个别就会被应用。

留神：在多列索引外面，如果有多个查问条件，要想查问效率比拟高，

比方如下建设的索引，index(a,b,c,d) 要保障最右边的列用到索引。则 a = 12 and b = 12 and c = 23

5、mysql 智能抉择

如果 mysql 认为，全表扫描不会慢于应用索引，则 mysql 会抉择放弃索引，间接应用全表扫描。

个别当取出的数据量超过表中数据的 20%，优化器就不会应用索引，而是全表扫描。

6、优化 group by 语句

默认状况下，mysql 对所有的 group by col1,col2 进行排序。

-- 依据 classid 分组，主动依据 classid 进行 了排序
select classid,sum(age) from user group by classid;

这与在查问中指定 order by col1,col2 相似，如果查问中包含 group by，但用户想要防止排序后果的耗费，则能够应用 order by null 禁止排序。

-- 如果不想依据 classid 排序，则能够在前面应用 order by nulll
select classid,sum(age) from user group by classid order by null;

通过剖析语句发现：

九、MySQL 中锁机制

1、利用场合

比方有如下操作：

（1）从数据库中取出 id 的值(比方 id=100)

（2）把这个值 -1(id=100-1)

（3）再把该值存回到数据库(id=99)

如果有两个过程（用户）同时操作，

应用锁机制来实现，同时操作时，只有一个过程取得锁，其余过程就期待，

2、mysql 外面的锁的几种模式

锁机制：

当客户端操作表（记录）时，为了保障操作的隔离性（多个客户端操作不能相互影响），通过加锁来解决。

1、操作方面：

读锁：读操作时减少的锁，也叫共享锁，S-lock。

特色是所有人都只能够读，只有开释锁之后才能够写。

写锁：写操作时减少的锁，也叫独占锁或排他锁，X-lock。

特色，只有锁表的客户能够操作（读写）这个表，其余客户读都不能读。

2、锁定粒度（范畴）

表级锁：开销小，加锁快，产生锁抵触的概率最高，并发度最低。

myisam 引擎的表反对表锁，

行级锁：开销大，加锁慢，产生锁抵触的概率最低，并发度也最高。

innodb 引擎的表反对行锁与表锁。

3、语法

-- 增加锁：lock table 表名 1 read|write, 表名 2 read|write

-- 开释锁：unlock tables

3、表锁的演示

建设测试表，并增加测试数据：

create table user(
    id int primary key auto_increment,
    name varchar(32) not null default '',
    age tinyint unsigned not null default 0,
    email varchar(32) not null default '',
    classid int not null default 1
)engine myisam charset utf8;
insert into user values(null,'xiaogang',12,'gang@sohu.com',4),
(null,'xiaohong',13,'hong@sohu.com',2),
(null,'xiaolong',31,'long@sohu.com',2),
(null,'xiaofeng',22,'feng@sohu.com',3),
(null,'xiaogui',42,'gui@sohu.com',3);

1、增加读锁

另外一个用户登录后，不能执行批改操作，能够执行查问操作。

留神：增加读锁后，本人和其余的过程（用户）只能对该表查问操作，本人也不能执行批改操作。

留神：增加表的锁定后，针对锁表的用户，只能操作锁定的表，不能操作没有锁定的表。

执行开释锁，

开释锁之后，另外的一个过程，能够执行批改的操作了。

2、增加写锁

只有锁表的客户能够操作（读写）这个表，其余客户读都不能读。

其余的用户，读都不能读，

4、行锁的演示

innodb 存储引擎是通过给索引上的索引项加锁来实现的，

这就意味着：只有通过索引条件 (主键) 检索数据，innodb 才会应用行级锁，否则，innodb 应用表锁。

语法：

begin;
执行语句；commit; 

以后用户增加行锁，另外的一个用户登录，进行操作。

5、通过 php 代码来实现锁机制

在 apache 外面有一个 bin 目录 上面有一个 ab.exe 工具，该工具能够模仿多个并发测试。

语法：

ab.exe  –n 总的申请数  -c 并发数  url 地址;

（1）创立一个表，表外面就只有一个字段，便于咱们演示

（2）通过代码来实现，

从数据库中取出 id 的值(比方 id=100)

把这个值 +1(id=100+1)

再把该值存回到数据库(id=101)

（3）应用 ab 工具模仿并发操作。

查看表外面的 id 的值，咱们执行了代码 50 次，应该 id 的值是 150 才对，则阐明咱们申请的 50 个并发，有几个并发是同时执行的。

（4）增加锁机制来进行测试。

把表外面的数据，再改为 100；

批改如下代码，增加锁机制

开始执行 50 个并发

测试成果如下；

2、应用 mysql 外面锁机制毛病

就是阻塞，如果有一张 goods 表，goods 表外面有一个库存的字段，以后下订单时，如果锁定了 goods 表，还能执行查问 goods 表吗？

会阻塞拖慢整个网站的速度，一但锁定 goods 表（增加写锁，要更改库存），则其余过程就无奈查问 goods 表。

3、能够应用文件锁

error_reporting(0);
$conn = mysql_connect('localhost','root','root');
mysql_query('use demo');
mysql_query('set names utf8');
// 关上该文件
$fh = fopen('./lock.txt','w');
// 增加锁
flock($fh,LOCK_EX);
//mysql_query('lock table a write');// 增加写锁
// 取出 id 的值
$res = mysql_query('select id from a');
$row = mysql_fetch_assoc($res);
$id = $row['id'];
// 执行该 id+ 1 运算
$id = $id+1;
// 执行后果，再写入数据库
mysql_query("update a set id = $id");
// 开释锁
flock($fh,LOCK_UN);
//mysql_query('unlock tables');

十、查问缓存

1、具体应用

mysql 服务器提供的，用于缓存 select 语句后果的一种外部内存缓存零碎。

如果开启了查问缓存，将所有的查问后果，都缓存起来，应用同样的 select 语句，再次查问时，间接返回缓存的后果即可

查看缓存设置状况，并给缓存空间设置大小：

 show variables  like‘query_cache%’;   #查看缓存应用状况
 
 query_cache_size: 缓存空间大小
 query_cache_type: 是否有开启缓存

如何开启查问缓存，并设置缓存空间大小

在 my.ini 中对上边的两个变量进行配置：

配置实现，之后须要重启 mysql，

查看缓存开启胜利：show variables like‘query_cache%’;

sql 语句第一次执行没有缓存，之后就有缓存了

2、无缓存

1、缓存生效

数据表的数据 (数据有批改) 有变动 或者 数据表构造 (字段的增、减) 有变动，则会清空全副的缓存数据，即缓存生效。

update emp set job=’123456’where empno=123456;

上图，执行了一个 update 语句，导致之前存在缓存 (empno=1234567) 被清空了

2、不应用缓存

sql 语句有变动表达式，则不会生成 / 应用缓存。

例如有 工夫信息、随机数等

select ename,job,now() from emp where empno=123456;

上图，在 sql 语句中有“工夫”变动的表达式，则不应用缓存

select * from emp order by rand() limit 4;

上图，sql 语句中有“随机数”的表达式，不给应用缓存

3、生成多个缓存

生成缓存的 sql 语句对 “空格”、“大小写” 比拟敏感

雷同后果的 sql 语句，因为空格、大小写问题就会 别离 生成多个缓存。

留神：雷同后果的 sql 语句，因为大小写问题会别离生成缓存：

4、禁用缓存

sql_no_cache #不进行缓存

#意思是以后查问后果不应用查问缓存；select  sql_no_cache  * from emp where empno=123456;

3、查看缓存空间应用状况

show status like 'Qcache%';  // 查看缓存应用状况

如下图，再次应用一个缓存，并读取一次，发现缓存相干参数有变动：

十一、分区技术

1、分区介绍

把一个表，从逻辑上分成多个区域，便于存储数据。

采纳分区的前提，数据量十分大。

如果数据表的记录十分多，比方达到上亿条，数据表的活性就大大降低，数据表的运行速度就比较慢、效率低下，影响 mysql 数据库的整体性能，就能够采纳分区解决，

分区是 mysql 自身就反对的技术。

# 查看以后 mysql 软件是否反对分区；show variables like '%partition%';

以上的构造，在创立（批改）表时，能够指定表，能够被分成几个区域。

利用表选项：partition 实现。

create table  表名(
    字段信息，索引，)engine myisam charser utf8
partition by 分区算法 (分区字段)(分区选项);

分区算法：

条件分区：list (列表)、range(范畴)、取模轮询（hash,key）

2、分区算法

1、list 分区

list : 条件值为一个数据列表。

通过预约义的列表的值来对数据进行宰割

例子：如果你创立一个如下的一个表，该表保留有全国 20 家分公司的职员记录，这 20 家分公司的编号从 1 到 20. 而这 20 家分公司散布在全国 4 个区域，如下表所示：

职员表：emp

id name store_id(分公司的 id)

12 小宝 1

14 二宝 6

北部 1,4,5,6,17,18

南部 2,7,9,10,11,13

东部 3,12,19,20

西部 8,14,15,16

create table p_list(
    id int,
    name varchar(32),
    store_id int
)engine myisam charset utf8
partition by list (store_id)(partition p_north values in (1,4,5,6,17,18),
    partition p_east values in(2,7,9,10,11,13),
    partition p_south values in(3,12,19,20),
    partition p_west values in(8,14,15,16)
);

创立分区表后查看文件，

增加几条数据，测试是否用到了分区：

留神：在应用分区时，where 前面的字段必须是分区字段，能力应用到分区。

explain partitions select * from p_list where store_id=18\G

如下查问，没有分区条件，则会到所有的分区外面去查找，即便如此，查问效率也要比单表查问高。

2、Range（范畴）

这种模式容许将数据划分不同范畴。例如能够将一个表通过月份划分成若干个分区

create table p_range(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by range (month(birthday))(partition p_1 values less than (4),
    partition p_2 values less than(7),
    partition p_3 values less than(10),
    partition p_4 values less than MAXVALUE
);

less than   小于;
MAXVALUE 可能的最大值 

插入的数据如下；

分区的成果如下；

3、Hash（哈希）

这种模式容许通过对表的一个或多个列的 Hash Key 进行计算，最初通过这个 Hash 码不同数值对应的数据区域进行分区。

例如能够建设一个对表主键进行分区的表。

create table  p_hash(
    id  int,
    name varchar(20),
    birthday date
)engine myisam charset utf8 
partition by hash(month(birthday)) partitions 5;

4、Key（键值）

下面 Hash 模式的一种延长，这里的 Hash Key 是 MySQL 零碎产生的。

create table p_key(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by key (id) partitions 5;

3、分区治理

具体就是对曾经存在的分区进行 减少 、 缩小 操作。

（1）删除分区

在 key/hash 畛域不会造成数据失落(删除分区后数据会从新整合到残余的分区去)

在 range/list 畛域会造成数据失落

# 求余形式(key/hash):
alter table 表名 coalesce partition 数量;
#范畴形式(range/list):
alter table 表名 drop partition 分区名称;

1）删除 hash 类型分区

删除分区之前，数据如下

执行删除分区的操作：alter table p_hash coalesce partition 4

上图，把 5 个分表中的 4 个都删除，只剩下一个

残余一个分表成果：

残余惟一一个分区的时候，就禁止删除了，然而能够 drop 掉整个数据表

2）删除 range 类型分表(数据有对应失落)

alter table p_range drop partition   p_1;

（2）减少分区

# 求余形式：key/hash
alter table 表名  add  partition partitions  数量;
#范畴形式：range/list
    alter table 表名 add partition(partition 名称 values  less than (常量)
           或
           partition 名称 values  in (n,n,n)
       );

1) 给 p_hash 减少 hash 分表

alter table p_hash  add partition partitions 6;

减少后，一共有 7 个分表体现：

上图，分表减少好后，又把数据均匀地调配给各个分表存储。

4、特地留神：

create table p_range2(
    id int primary key auto_increment,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by range (month(birthday))(partition p_1 values less than (4),
    partition p_2 values less than(7),
    partition p_3 values less than(10),
    partition p_4 values less than MAXVALUE
);

留神：创立分区的字段必须是主键或惟一索引的一部分

create table p_range2(
    id  int auto_increment,
    name varchar(32),
    birthday date,
    primary key(id,birthday)
)engine myisam charset utf8
partition by range (month(birthday))(partition p_1 values less than (4),
    partition p_2 values less than (7),
    partition p_3 values less than(10),
    partition p_4 values less than MAXVALUE
);

create table p_range3(
    id  int auto_increment,
    name varchar(32),
    birthday date,
    unique key(id,birthday)
)engine myisam charset utf8
partition by range (month(birthday))(partition p_1 values less than (3),
    partition p_2 values less than (6),
    partition p_3 values less than(9),
    partition p_4 values less than MAXVALUE
);

十二、分表技术

物理形式分表设计

本人手动创立多个数据表进去

php 程序须要思考分表算法：数据往哪个表写，从哪个表读

1、程度分表

程度分表：是把一个表的全副 记录 信息别离存储到不同的分表之中。

QQ 的登录表。假如 QQ 的用户有 10 亿，如果只有一张表，每个用户登录的时候数据库都要从这 10 亿中查找，会很慢很慢。如果将这一张表分成 100 份，每张表有 1000 万条，就小了很多，比方 qq0,qq1,qq1…qq99 表。

用户登录的时候，能够将用户的 id%100，那么会失去 0 -99 的数，查问表的时候，将表名 qq 跟取模的数连接起来，就构建了表名。比方 123456789 用户，取模的 89，那么就到 qq89 表查问，查问的工夫将会大大缩短。

注册时，如何存储到多张表外面？

$user_id = $redis->incr(‘user_id’);

表单提交过去的内容；

$username =‘大宝’;

如果咱们要分四张表来存储；

$user_id%4 = 获取余数

如果 $user_id= 8 了 余数是 0，那咱们就存储到 user_0 表外面了，

user_0 表外面的字段 id $user_id‘大宝’

$redis->set($username_register_name,$user_id)

登录时，如何晓得查问那张表？

登录时，咱们用名称来登录；

$username =‘大宝’, 如何晓得该名称在那张表外面呢？

$username（通过 redis）->user_id-> 通过 user_id 算出存储的表；

2、垂直分表（比拟罕用）

垂直分表：是把一个表的全副 字段 别离存储到不同的表里边。

有的时候，一个数据表设计好了，里边有许多字段，然而这些字段有的是 常常应用 的，有的是 不罕用 的。在进行失常数据表操作的时候，不罕用的字段也会占据肯定的资源，对整体操作的性能造成肯定的 烦扰、影响。

为了缩小资源的开销、晋升运行效率，就能够把 不罕用的字段 给创立到一个专门的辅表中去。

同一个业务表的 不同字段 别离存储到不同数据表的过程就是“垂直分表”。

例如：

会员数据表有如下字段：

会员表：user_id 登录名 明码 邮箱 手机号码

​ 身高 体重 性别 家庭地址 身份证号码

为了使得常用字段运行速度更快、效率更高，把常用字段给调进去, 因而数据表做以下垂直分表设计：

会员表(主)user 字段：user_id 登录名 明码 邮箱 手机号码

会员表(辅)user_fu 字段：user_id 身高 体重 性别 家庭地址 身份证号码

以上把会员表依据字段是否罕用给分为两个表的过程就是垂直分表。

再如：存储文章

常常查问的数据 title（题目）author（作者）

十三、数据碎片与保护

在长期的数据更改过程中，索引文件和数据文件，都将产生 空洞，造成碎片，咱们能够通过一个操作（不产生对数据本质影响的操作）来批改表，

create table t1(id int)engine myisam;
insert into t1 values(1),(2),(3)
insert into t1 select * from t1;

表的原始大小：

删除了一部分数据，应该表的容量会缩小一部分，然而没有减掉，

开始整顿：

1、optimize table 表名;

整顿后的后果，容量缩小了一部分。

2、从新批改表引擎

alter table 表名 engine 引擎

留神：修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐，这个过程，如果表的行数比拟大，也是比拟消耗资源的操作，所以，不能频繁的修复。

如果表的 update，delete 操作很频繁，能够按周月来修复。

十四、表的范式解说

第一范式

确保每列的原子性

若每列都是不可再分的最小数据单元(也称最小原子单元)，则满足第一范式。

第二范式

若一个关系满足 1NF，并且除了主键以外的其余列，必须齐全依赖于该主键(打消非主属性对主属性的局部函数依赖)，则满足第二范式。

第三范式

若一个关系满足 2NF，并且除了主键以外的其余列 都不传递依赖于主键列(打消传递依赖)，则满足第三范式。

逆范式

有的时候基于性能思考，须要无意违反 三范式，适度的冗余，以达到进步查问效率的目标。

相册浏览次数设计案例：

十五、视图

1、视图的定义

视图是由 查问后果 造成的一张 虚构表，是表通过某种运算失去的一个投影。

创立视图的语法：

create  view  视图名 as  select 语句

阐明：

（1）视图名跟表名是一个级别的名字，隶属于数据库；

（2）该语句的含意能够了解为：就是将该 select 命名为该名字（视图名）；

（3）视图也能够设定本人的字段名，而不是 select 语句自身的字段名——通常不设置。

（4）视图的应用，简直跟表一样！

2、视图的作用

筹备测试数据；goods 表和 category 表；

（1）能够简化查问

案例 1：查问平均价格前 3 高的栏目。

传统的 sql 语句写法

select cat_id,avg(shop_price)  pj  from goods  group by cat_id order by pj  desc limit 3;

创立一个视图

create   view   goods_avg_price  as select cat_id,avg(shop_price) pj from goods_avg_price group by cat_id;

创立好了视图，再次查问平均价格前 3 高的栏目时，咱们就能够间接查问视图

select * from goods_avg_price  order by pj desc limit 3;

案例 2: 查问出商品表，以及所在的栏目名称；

传统的写法

select a.*, b.cat_name from goods a left join category b on a.cat_id=b.id;

创立一个视图

create view  goods_cat  as  select a.*, b.cat_name from goods a left join category b on a.cat_id=b.id;

查问视图；

select * from goods_cat;

（2）能够进行权限管制

把表的权限关闭，然而凋谢相应的视图权限，视图里只凋谢局部数据，

比方某张表，用户表为例，2 个网站搞单干，能够查问对方网站的用户，须要向对方凋谢用户表的权限，然而呢，又不想凋谢用户表中的明码字段。

再比方一个 goods 表，两个网站搞单干，能够互相查问对方的商品表，比方进货价格字段不能让对方查看。

案例：

1）、创立一个 goods 表，增加几条数据

给测试的 goods 表增加一个 in_price(进货价格)字段；

2）创立一个视图

create view  goods_v1 as select id,goods_name,shop_price from goods;

3）受权一个账号

grant  权限  on 数据库名称. 视图名或表名  to  '用户名称'@'%'  identified by '明码'

示意创立了一个 dahei 的用户，明码是 123456，权限是在 php69 库上面的 goods_v1 视图具备查问的权限；

4）案例测试

3、查问视图

语法：

# 视图和表一样，能够增加 where 条件
select * from 视图名 [where 条件] 

4、批改视图

alter   view   视图名  as   select   XXXX 

5、删除视图

drop  view  视图名称

6、查看视图构造

# 和表一样的，语法，desc 视图名称

7、查看所有视图

留神：没有 show views 语句；

# 和表一样，语法：show   tables;

8、视图与表的关系

视图是表的查问后果，天然表的数据扭转了，影响视图的后果。

1）视图的数据与表的数据一一对应时，能够批改

2）视图增删改也会影响表，然而视图并不是总是能增删改的

create view goods_avg_price as select cat_id,max(shop_price) as pj from goods group by cat_id; 
mysql> update goods_avg_price set pj=5678 where cat_id=2; 
ERROR 1288 (HY000): The target table goods_avg_price of the UPDATE is not updatable 

3）对于视图 insert 还应留神，视图必须蕴含表中没有默认值的列。

留神：向视图外面插入数据时，视图必须蕴含表中没有默认值的列，能力插入胜利，否则就插入失败。

留神：在理论的开发中，不要对视图进行增删改。

十六、SQL 编程

1、变量申明

1、会话变量

定义模式：

set  @变量名 =  值；

阐明：

1，跟 php 相似，第一次给其赋值，就算定义了

2，它能够在编程环境和非编程环境中应用！

3，应用的任何场合也都带该“@”符号。

2、一般变量

定义模式：

declare  变量名  类型【default  默认值】；

阐明：

1、它必须先申明（即定义），此时也能够赋值；

2、赋值跟会话变量一样：set 变量名 = 值；

3、它只能在编程环境中应用！！！

阐明：什么是编程环境？

编程环境是指（1）存储过程（2）函数（3）触发器。

3、变量赋值模式

语法 1：

# 此语法中的变量必须先应用 declare 申明，在编程环境中应用
set 变量名 = 表达式;

语法 2：

# 此形式能够无需 declare 语法申明，而是间接赋值，相似 php 定义变量并赋值。set @变量名 = 表达式;

语法 3：

# 此语句会给该变量赋值，同时还会作为一个 select 语句输入‘后果集’。select @变量名:= 表达式;

语法 4：

# 此语句尽管看起来是 select 语句，但其实并不输入‘后果集’，而是给变量赋值。select 表达式 into @变量名;

2、运算符

（1）算术运算符

+、-、*、/、%

留神：mysql没有 ++ 和—运算符

（2）关系运算符

>、>=、<、<=、=（等于）、<>（不等于）!=（不等于）

（3）逻辑运算符

and（与）、or（或）、not（非）

3、语句块蕴含符

所谓语句块蕴含符，在 js 或 php 中，以及绝大部分的其余语言中，都是大括号：{}

它用在很多场合：if，switch, for, function

而 mysql 编程中的语句块蕴含符是 begin end 构造。

4、if 判断

MySQL 反对两种判断，第一个是 if 判断，第二个 case 判断

单分支

if 条件 then 

    #代码

end if; 

双分支

if 条件 then 

    代码 1 

else 

    代码 2 

end if; 

多分支

if 条件 then 

    代码 1 

elseif 条件 then 

    代码 2 

else 

    代码 3

end if; 

案例：接管 4 个数字，

如果输出 1 则输入春天，2=》夏天 3=》秋天 4 =》冬天 其余数字 =》出错

留神：

通常状况下，“；“示意 SQL 语句完结，同时向服务器提交并执行。

然而存储过程中有很多 SQL 语句，每一句都要以分号隔开，这时候咱们就须要应用其余符号来代替向服务器提交的命令。

通过 delimiter 命令更改语句结束符。

create procedure  p1(num int)
begin
    if num=1 then
        select '春天' as '节令';
    elseif num=2 then
        select '夏天' as '节令';
    elseif num=3 then
        select '秋天' as '节令';
    elseif num=4 then
        select '冬天' as  '节令';
    else
        select '无法无天' as '节令';
    end if;
end$

5、case 判断

语法：

case  变量

when 值 then   语句; 

when 值 then   语句; 

else  语句; 

end case ; 

案例：接管 4 个数字，

如果输出 1 则输入春天，2=》夏天 3=》秋天 4 =》冬天 其余数字 =》出错

create procedure  p2(num int)
begin
    case num
    when 1 then  select '春天' as '节令';
    when 2 then  select '夏天' as '节令';
    when 3 then  select '秋天' as '节令';
    when 4 then  select '冬天' as '节令';
    else  select '无法无天' as '节令';
    end case;
end$

6、循环

MySQL 反对的循环有 loop、while、repeat 循环

1、loop 循环

语法：

标签名:loop 

    leave 标签名  #退出循环

end loop; 

# 案例：创立一个存储过程，实现计算 1 到 n 的和。

create procedure  p3(n int)
begin
    declare i int default 1;
    declare s int default 0;
    aa:loop
        set s=s+i;
        set i=i+1;
        if i>n then
            leave aa;
        end if;
    end loop;
    select s;
end$

2、while 循环

 [标签:]while 条件 do 
    #代码
end while; 

# 案例：创立一个存储过程，实现计算 1 到 n 的和。

create procedure  p4(n int)
begin
    declare i int default 1;
    declare s int default 0;
    while  i<=n  do 
        set s=s+i;
        set i=i+1;
    end while;
    select s;
end$

十七、函数

1、自定义函数

1、定义语法

create function 函数名(参数) returns 返回值类型
begin 
    #代码
end 

阐明：

（1）函数外部能够有各种编程语言的元素：变量，流程管制，函数调用；

（2）函数外部能够有增删改等语句！

（3）但：函数外部不能够有 select（或 show 或 desc）这种返回后果集的语句！

2、调用

跟零碎函数调用一样：任何须要数据的地位，都能够调用该函数。

案例 1：返回两个数的和

create function sumhe(num1 int,num2 int) returns int

begin

     return num1+num2;

end$

案例 2：定义一个函数，返回 1 到 n 的和。

create function nhe(n int)  returns int
begin
    declare i int default 1;
    declare s int default 0;
    while i<=n do 
        set s=s+i;
        set i=i+1;
    end while;
    return s;
end$

留神：创立的函数，是隶属于数据库的，只能在创立函数的数据库中应用。

2、零碎函数

（1）数字类

select rand();# 返回 0 到 1 间的随机数
select * from it_goods  order by rand() limit 2;# 随机取出 2 件商品

select  floor(3.9); #输入 3 
select  ceil(3.1); #输入 4 
select  round(3.5); #输入 4 四舍五入
select goods_name,round(shop_price) from goods limit 10;

（2）大小写转换

select ucase('I am a boy!');    -- 转成大写
select lcase('I am a boy!');    -- 转成小写

（3）截取字符串

select left('abcde',3);            -- 从右边截取
select right('abcde',3);        -- 从左边截取
select substring('abcde',2,3);   -- 从第二个地位开始，截取 3 个，地位从 1 开始
select left(goods_name,1),round(shop_price) from goods limit 10

 select concat('welcome',':beijing');   -- 字符串相连
 select concat(left(goods_name,1),'...'),round(shop_price) from goods limit 10;

# coalesce(str1,str2)：如果第 str1 为 null，就显示 str2 
select coalesce(null,123); 

select goods_name, coalesce(goods_thumb,'无图') from goods limit 10;

select length('锄禾日当午'); #   输入 10   显示字节的个数
select char_length('锄禾日当午'); # 输入 5   显示字符的个数
select length(trim('abc')); #  trim 用来去字符串两边空格
select replace('abc','bc','pache');   #将 bc 替换成 pache 

（4）工夫类

 select unix_timestamp();      -- 工夫戳

-- 将工夫戳转成日期格局 from_unixtime(unix_timestamp(),'%Y-%m-%d-%h-%i-%d')
select from_unixtime(unix_timestamp());

# 返回明天的工夫日期：select  curdate();
-- 取出以后工夫
select now()    

案例 1：比方一个电影网站，求出明天增加的电影；在增加电影时，有一个增加的工夫戳。

select id, title from dede_archives where (from_unixtime(增加工夫,'%Y-%m-%d') = curdate());

案例 2：比方一个电影网站，求出 昨天 增加的电影；在增加电影时，有一个增加的工夫戳。

扩大，如何取出昨天或者指定某个工夫的电影：date_sub

根本用法：

date_sub(工夫日期工夫, interval 数字 工夫单位)

阐明：

（1）工夫单位：能够是 year month day hour minute second

（2）数字：能够是负数和正数。

# 比方：取出昨天的日期：select date_sub(curdate(),interval 1 day);
#比方：取出上一个月日期：select date_sub(curdate(),interval 1 month);

如下案例是：求出前第 2 天增加的电影数据

select id, title, from_unixtime(add_time,'%Y-%m-%d') from movie where (from_unixtime(add_time,'%Y-%m-%d')) = date_sub(curdate(),interval 2 day);

十八、存储过程

1、概念

存储过程(procedure)

概念相似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，能够通过调用该存储过程来实现。

在封装的语句体外面，能够应用 if/else ,case,while 等控制结构。

能够进行 sql 编程。

# 查看现有的存储过程。show procedure status 

2、存储过程的长处

存储过程（Stored Procedure）是在大型数据库系统中，一组为了实现特定性能的 SQL 语句集，存储在数据库中，通过第一次编译后再次调用不须要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

（1）存储过程只在发明时进行编译，当前每次执行存储过程都不需再从新编译，而个别 SQL 语句每执行一次就编译一次, 所以应用存储过程可 进步数据库执行速度。

（2）当对数据库进行简单操作时 ( 如对多个表进行 Update,Insert,Query,Delete 时)，可将此简单操作用存储过程 封装起来 与数据库提供的事务处理联合一起应用。

（3）存储过程能够 重复使用, 可缩小数据库开发人员的工作量

（4）安全性高, 可设定只有某些用户才具备对指定存储过程的使用权

3、创立存储过程

语法：

create procedure 存储过程名(参数 1, 参数 2,…) 

begin 

     #代码

end 

参数的类型：

in（输出参数）：示意该形参只能承受实参的数据 —— 这是默认值，不写就是in；

out（输入参数）：示意该形参其实是用于将外部的数据“传出”到内部给实参；

inout（输入输出参数）：具备上述 2 个性能。

案例 1：查问一个表外面某些语句

create procedure p6(goods_id int)
begin
    select * from goods;
end$

案例 2：应用参数

create procedure p8(price float)
begin
select * from goods where shop_price > price;
end$

阐明：

（1）存储过程中，可有各种编程元素：变量，流程管制，函数调用；

（2）还能够有：增删改查等各种 mysql 语句；

（3）其中 select（或 show，或 desc）会作为存储过程执行后的“后果集”返回；

（4）形参能够设定数据的“进出方向”：

（5）存储过程是属于数据库，在哪个数据库外面定义的，就在哪个数据库外面调用。

如下图，在别的数据库外面调用其余数据库外面定义的存储过程时，会报如下提醒。

4、调用存储过程

语法：

call 存储过程名称(参数)

在 php 外面如何调用，

mysql_query('call p7(5)');

5、创立简单的存储过程

案例 1，领会“控制结构”;

定义一个存储过程，有两个参数，第一个参数是价格，第二个参数是一个字符串，

如果该字符串等于’h’则就取出大于该价格（第一个参数）商品数据，其余则输入小于该价格的商品；

create procedure p8(price float,str char(1))
begin
    if str='h' then
    select id,goods_name,shop_price from goods where shop_price >= price;
    else
    select id,goods_name,shop_price from goods where shop_price < price;
    end if;
end$

案例 2：带有输入参数的存储过程

create procedure p9(in num int,out res int)
begin
    set  res = num*num;
end$

留神：在调用具备输入参数的存储过程时，要应用一个变量来接管。

call p9(8,@res);select @res;

案例 3：带有输入输出参数的存储过程

create procedure p10(inout num int)
begin
    set   num=num*num;
end$

留神：在调用时先创立一个变量，调用存储过程时，应用该变量接管。

6、删除存储过程

语法：

drop   procedure   存储过程的名称

十九、触发器

1、简介

（1）触发器是一个非凡的存储过程，它是 MySQL 在 insert、update、delete 的时候主动执行的代码块。

（2）触发器必须定义在特定的表上。

（3）主动执行，不能间接调用，

作用：监督某种状况并触发某种操作。

触发器的思路：

监督 it_order 表，如果 it_order 表外面有增删改的操作，则主动触发 it_goods 外面增删改的操作。

比方新增加一个订单，则 it_goods 表，就主动缩小对应商品的库存。

比方勾销一个订单，则 it_goods 表，就主动减少对应商品的库存缩小的库存。

2、触发器四因素

监督地点：就是设置监督的表

监督事件；设置监督的那张表的 insert ,update,delete 操作；触发工夫：设置触发工夫，监督表的操作之前，还是之后；触发事件：满足条件了，设置的触发的操作；

筹备测试数据；

3、创立触发器

语法：

create  trigger  trigger_name 
after/before  insert/update/delete  on  表名
for each row
begin 
    #sql 语句：（触发的语句一句或多句）end 

案例 1：第一个触发器，购买一头猪，缩小1 个库存。

剖析：

监督地点：it_order 表

监督事件：it_order 表的 insert 操作；

触发工夫：it_order 表的 insert 操作之后

触发事件：it_goods 表猪的库存减 1 操作；

create  trigger  t1
after   insert  on  it_order
for each row
begin
update it_goods set goods_number=goods_number-1 where id=1;
end$

留神：以上触发器是有问题的，无论买谁，都是缩小的猪的数量，而且数量是 1，

案例 2：购买商品，缩小对应库存

特地留神：

create  trigger  t1
after   insert  on it_order
for each row
begin
update it_goods set goods_number = goods_number - new.much where id = new.goods_id;
end$

留神：如果在触发器中援用行的值。

对于 insert 而言，新增的行用 new 来示意，行中的每一列的值，用 new. 列名 来示意。

测试后果：

案例 3：勾销订单时，减掉的库存要增加回来

剖析：

监督地点：it_order 表

监督事件：it_order 表的 delete 操作；

触发工夫：it_order 表的 delete 操作之后

触发事件：it_goods 表减掉库存再加回来；

create  trigger  t2
after   delete  on  it_order
for each row
begin
update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
end$

留神：

对于 delete 而言，it_order 表删除的行用 old 来示意，行中的每一列的值，用 old. 列名 来示意。

案例 4：批改订单时，库存也要做对应批改(批改的数据，有商品的数量，类型)

剖析：

（1）勾销订单

（2）从新下单

留神：

对于 update 而言，批改之前行用 old 来示意，行中的每一列的值，用 old. 列名 来示意。

批改之后，用 new 来示意，行中的每一列的值，用 new. 列名 来示意

create  trigger  t3
after   update  on  it_order
for each row
begin
update it_goods set goods_number=goods_number+old.much where id=old.goods_id;
update it_goods set goods_number=goods_number-new.much where id=new.goods_id;
end$

4、删除触发器

语法：

drop trigger 触发器的名称

5、查看触发器

语法：

show triggers 

6、before 和 after 的区别

after 是先实现数据的增删改，再触发，触发器中的语句晚于监督的增删改，无奈影响后面的增删改动作。

就相似于先吃饭，再付钱。

before 是先实现触发，再增删改，触发的语句先于监督的增删改产生，咱们有机会判断批改行将产生的操作。

就相似于先付钱，再吃饭

典型案例：对于已下的订单，进行判断，如果订单的数量 >5，就认为是歹意订单，强制把所定的商品数量改成 5

剖析：

监督的表 :it_order

监督的事件：it_order 表的 insert 操作

触发的工夫：it_order 表的 insert 操作之前

触发的事件：如果订单数量大于 5，则改成 5

create  trigger  t4
before  insert  on  it_order
for each row
begin
    if new.much>5 then
    set new.much=5;
    end if;
end$

二十、事务操作

MySQL 事务次要用于解决操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既须要删除人员的根本材料，也要删除和该人员相干的信息，如信箱，文章等等，这样，这些数据库操作语句就形成一个事务！

在 MySQL 中只有应用了 Innodb 数据库引擎的数据库或表才反对事务。

事务处理能够用来保护数据库的完整性，保障成批的 SQL 语句要么全副执行，要么全副不执行。

事务用来治理 insert,update,delete 语句

语法：

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

测试数据如下；

$pdo  = new PDO('mysql:host=localhost;dbname=php69','root','root');
$pdo->exec('set names utf8');
// 启动事务
$pdo->beginTransaction();
$res1 = $pdo->exec("insert into user values(null,'name3',12,'email1',1)");
$res2 = $pdo->exec("insert into user values(null,'name4',12,'email2',2))");
if(!$res1 || !$res2){$pdo->rollback();
}else {$pdo->commit();
}
echo 'ok';

事务特色：ACID

（1）原子性（Atomicity，或称不可分割性）：一个事务（transaction）中的所有操作，要么全副实现，要么全副不实现，不会完结在两头某个环节。事务在执行过程中产生谬误，会被回滚（Rollback）到事务开始前的状态，就像这个事务素来没有执行过一样。（2）一致性（Consistency）：在事务开始之前和事务完结当前，数据库的完整性没有被毁坏。（3）隔离性（Isolation，又称独立性）：数据库容许多个并发事务同时对其数据进行读写和批改的能力，隔离性能够避免多个事务并发执行时因为穿插执行而导致数据的不统一。-------------------------------------------------------------------------------

    一个事务的执行不能被其余事务烦扰。即一个事物外部的操作及应用的数据对并发的其余事务是隔离的，并发执行的各个事务之间不能相互烦扰。如果一个客户端在应用事务操作一个数据 (一行 / 整表) 的时候，另外一个客户端不能对该数据进行操作。行被隔离？整表被隔离？如果条件中应用了索引(主键)，那么零碎是依据主键间接找到某条记录，这个时候与其余记录无关，那么只隔离一条记录。反之，如果说零碎是通过全表检索(每一条记录都去查看：没有索引)，被检索的所有数据都会被锁定(整表)
-------------------------------------------------------------------------------（4）持久性：事务处理完结后，对数据的批改就是永恒的，即使系统故障也不会失落。

事务隔离等级

数据库事务有不同的隔离级别，不同的隔离级别对锁的应用是不同的，锁的利用最终导致不同事务的隔离级别。

多个事务之间是隔离的，互相独立的。

然而如果多个事务操作同一批数据，则会引发一些问题，

1、脏读：一个事务，读取到另一个事务中没有提交的数据

2、不可反复读(虚读)：在同一个事务中，两次读取到的数据不一样。

3、幻读：一个事务操作 (DML 增删改) 数据表中所有记录，而另一个事务增加了一条数据，则第一个事务查问不到本人的批改。

设置不同的隔离级别就能够解决这些问题

1、读未提交（Read uncommitted）产生的问题：脏读、不可反复读、幻读

2、读已提交（read committed）(Oracle 默认的) 产生的问题：不可反复读、幻读

3、可反复读（repeatable read）(MySQL 默认的) 产生的问题：幻读

4、串行化（Serializable）—- 相当于单线程 能够解决所有的问题

留神：隔离级别从小到大安全性越来越高，然而效率越来越低

设置事务的隔离级别

innodb 存储引擎提供事务的隔离级别有：

            read uncommitted、read committed、repeatable read 和 serializable。

语法：

set global transaction isolation level 级别字符串;         

案例：

# 设置事务的隔离级别为 读未提交
set global transaction isolation level Read uncommitted;

#开启事务
start transaction;

#执行 sql 操作
update account set money = money -500 where id =1;
update account set money = money +500 where id =2;

#此时还没提交，另外一个事务能够拜访到这个事务还未提交的数据

二十一、DCL 治理用户

用户治理

1、增加用户
语法：

create user '用户名'@'主机名' identified by '明码';

例：

create user 'zhangsan'@'localhost' identified by '321';
create user 'lisi'@'%' identified by '321';

2、删除用户

语法：

drop user '用户名'@'主机名';

例：

drop user 'zhangsan'@'localhost';
drop user 'lisi'@'%';

3、批改用户明码

MySQL 中提供了多种批改的形式：基本上都必须应用对应提供的一个零碎函数：password()函数，须要靠该函数对明码进行加密解决

3.1、update user set password = password(‘ 新密码 ’) where user = ‘ 用户名 ’;

update user set password = password('def') where user = 'zhangsan' and host = 'localhost';

update user set password = password('abc') where user = 'lisi' and host = '%';

3.2、set password for ‘ 用户名 ’@’ 主机名 ’ = password(‘ 新密码 ’)

set password for 'zhangsan'@'localhost' = password('321')

set password for 'lisi'@'%' = password('321')

4、查问用户

# 切换到 mysql 数据库
use mysql;

#查问 user 表
select user,host from user;

留神：通配符 % 示意能够在任意主机应用用户登录数据库

权限治理

在 mysql 中将权限治理分为三类：

        1、数据权限：增删改查(select/update/delete/insert)
        2、构造权限：构造操作(create/drop)
        3、管理权限：权限治理(create user/grant/revoke)，通常只给管理员

1、查问权限

语法：

show grants for '用户名'@'主机名';

例：

show grants for 'lisi'@'%';

2、授予权限

语法：

grant 权限列表 on 数据库名. 表名 to '用户名'@'主机名';

# 创立账号并指定权限
grant  权限  on 数据库. 数据表  to '用户名'@'主机名' identified  by‘明码’

例：

grant select,delete,update on db3.* to 'lisi'@'%';
grant all on *.* to 'zhangsan'@'localhost';

3、撤销权限

语法：

revoke 权限列表 on 数据库名. 表名 from '用户名'@'主机名';

例：

revoke update on db3.* from 'lisi'@'%';
revoke all on *.* from 'zhangsan'@'localhost';

4、刷新权限

​ flush：刷新，将以后对用户的权限操作，进行一个刷新，将操作的具体内容同步到对应的表中

flush privileges;    

遗记 root 明码

1、进行 mysql 服务
            cmd----> net stop mysql 

2：应用无验证形式启动 mysql 服务
            mysqld --skip-grant-tables #启动服务器然而跳过权限

3：输出 mysql 命令 间接登陆
            新开窗口，mysql
                
4：批改 root 明码
            update mysql.user set password = password('root') where user = 'root' and host = 'localhost';
            
5：关上工作管理器，手动完结 mysqld.exe 的过程

二十二、读写拆散（主从复制）

从代码实现来说叫读写拆散，从架构角度来说叫主从复制

1、什么是主从复制

主从复制，是一种数据备份的计划

是应用两个或两个以上雷同的数据库，将一个数据库当做 主数据库 ，而另一个数据库当做 从数据库。在 主数据库 中进行相应操作时，从数据库 记录下所有主数据库的操作，使其二者截然不同。

为什么要应用：

1、当主数据库呈现问题时，能够让从数据库代替主数据库，能够防止数据的失落。

2、能够进行读写拆散

1.1 此时主服务器会将更新信息写入到一个特定的二进制文件中，并会保护文件的一个索引用来跟踪日志循环，这个日志能够记录并发送到从服务器的更新中去。

1.2 一台从服务器连贯到主服务器时，从服务器会告诉主服务器从服务器的日志文件中读取最初一次胜利更新的地位。而后从服务器会接管从哪个时刻起产生的任何更新，而后锁住并等到主服务器告诉新的更新。

2、什么是读写拆散

就是基于主从复制架构，一个主库，有多个从库，当 主数据库 进行对数据的增删改也就是写操作时，将查问的工作交给 从数据库。

为什么要应用：

1、防止从数据库进行写操作而导致的主从数据库数据不统一的状况，因为当主从数据库数据不统一时，那么从数据库最次要的备份工作就没有意义了。

2、加重主数据库的压力。因为进行写操作更耗时，所以如果不进行读写拆散的话，写操作将会影响到读操作的效率。

能够说主从复制是实现读写拆散的技术之一

3、主从复制原理

1、Master 将数据扭转的记录到二进制（binary log）中，也就是配置文件 log-bin 指定的文件名

2、slave 通过 i/o 线程读取 master 中 binary log events 内容，并写入到 slave 的中继日志中

3、slave 重做中继日志事件，把中继日志的事件信息一条一条的在本地执行一次，实现数据库的本地存储，从而实现数据的复制操作。

mysql 中有一种日志，叫做 bin 日志（二进制日志），会记录下所有批改过数据库的 sql 语句。

主从复制的原理理论是多台服务器都开启 bin 日志，而后主服务器会把执行过的 sql 语句记录到 bin 日志中，之后从服务器读取该日志，在从服务器再把 bin 日志中记录的 sql 语句同样的执行一遍。

这样从服务器上的数据就和主服务器雷同了。

留神：

（1）主从都要开启 bin 日志

（2）主服务器须要受权用户

（3）具体的配置过程；

4、读写拆散实现办法

1、基于程序代码的外部实现

在代码中依据 select、insert 进行路由分类，这类办法也是目前生产环境中较为罕用的，

长处是性能较好，因为在程序代码中实现，不须要减少额定的设施作为硬件开销；

毛病是须要研发人员来实现，运维人员无从下手。

2、基于两头代理层实现

代理个别位于客户端和服务器之间，代理服务器接管到客户端申请后通过判断后转发到后端数据库。

mysql_proxy：mysql_proxy 是 Mysql 的一个开源我的项目，通过其自带的 lua 脚本进行 sql 判断。

Atlas：是由 Qihoo 360, Web 平台部基础架构团队开发保护的一个基于 MySQL 协定的数据中间层我的项目。它是在 mysql-proxy 0.8.2 版本的根底上，对其进行了优化，减少了一些新的性能个性。360 外部应用 Atlas 运行的 mysql 业务，每天承载的读写申请数达几十亿条。反对事物以及存储过程。

Amoeba：由阿里巴巴团体在职员工陈思儒应用序 java 语言进行开发，阿里巴巴团体将其用户生产环境下，然而他并不反对事物以及存数过程。

5、bin-log 开启操作

开启 bin-log 日志

关上 mysql 的配置文件，

window 上面 my.ini

linux 上面 my.cnf

log-bin=mysql-bin

留神：

批改实现 mysql 的配置后，要重启 mysql 服务

mysql 外面数据表的存储地位，要看配置文件，

开启配置后，产生的二进制日志文件如下；

与 log-bin 日志相干的函数

flush logs;    #执行该命令，就会产生一个新的 log-bin 日志

reset master;    #清空所有的 log-bin 日志，并产生一个新的 log-bin 日志

通过 show master status 命令，能查看到二进制文件外面最初一个 pos 地位。

show master status;    #查看最初（新）的一个 log-bin 日志 

查看 log-bin 日志外面的内容

新建一张表，测试 log-bin 日志是否记录增删改的 sql 语句

MySQL 中二进制文件所在目录：/var/lib/mysql

留神：应用 mysql 装置目录上面的 bin 目录上面 mysqlbinlog 命令，来查看日志内容。

语法：

mysqlbinlog    --no-defaults     二进制日志的名称（全门路）

留神：end_log_pos 的了解，用于记录上一个 sql 语句的完结，下一个 sql 语句 的开始地位

6、主从复制配置步骤

试验布局，须要两台主机

第一台主机：ip 地址 192.168.1.69 配置为 master 服务器

第二台主机：ip 地址 192.168.1.70 配置为 slave 服务器

1、配置主服务器

（1）开启二进制日志。

（2）要设置一个 server-id（作为一个服务器的编号，是惟一）该值不能和从服务器雷同。

留神：

在 my.cnf 配置文件外面，配置的区域在 [mysqld] 与[mysql]之间配置；

配置实现后，要重启 mysql 服务

（3）受权一个账号，让从服务器通过该账号读取 log-bin 日志外面的内容

grant replication slave on *.* to 'xiongda'@'%' identified by '123456'

赋予从库权限账号，容许用户在主库上读取日志，也就是 Slave 机器读取 File 权限，

grant FILE on *.* to 'xiongda'@'%' identified by '123456';

（4）记录主服务器外面的最新的二进制的名称和 pos 地位

留神：此时，就禁止对主服务器执行增删改的操作，始终到从服务器配置胜利。

2、配置从服务器

（1）开启二进制日志。

（2）要设置一个 server-id 该值不能和主服务器的雷同。

留神：配置好配置文件后，要重启 mysql 服务器；

（3）进行从服务器

登录到从服务器后，执行 stop slave 指令即可。

（4）开始配置

语法：

change master to 
master_host="主服务器的 ip 地址",
master_user="受权用户的名称",
master_password="受权用户的明码",
master_log_file="二进制日志文件的名称",
master_log_pos= 记录的 pos 地位;

例：

change master to 
master_host="192.168.1.69",
master_user="xiongda",
master_password="123456",
master_log_file="mysql-bin.000004",
master_log_pos=2603;

（5）开启从服务器

执行 start slave 指令即可。

（6）查看是否配置胜利

执行 show slave status;

Slave_IO_Running:Yes

此过程负责从服务器从主服务器上读取 binlog 日志，并写入从服务器上的中继日志。

Slave_SQL_Running:Yes

此过程负责读取并且执行中继日志中的 binlog 日志，

注：以上两个都为 yes 则表明胜利，只有其中一个过程的状态是 no，则示意复制过程进行，谬误起因能够从”last_error”字段的值中看到。

3、测试主从复制

在主服务器创立一个新库，并增加一张新表，并插入新数据，

在从服务器下面查看是否有该库，该表，该记录。

4、撤销从服务器

在 从服务器 上执行如下两个指令。

（1）stop slave

（2）reset slave all

7、实现读写拆散

1、通过业务逻辑来实现读写拆散

class  mysql
{
    $dbm= 主服务器 
    $dbs1= 从服务器 
    $dbs2= 从服务器 
    public function query()
    {在 query 外面进行语句判断，剖析连贯不同的 mysql 服务器。}
} 

2、TP 框架外面实现读写拆散

1、TP 框架外面配置文件

2、在 TP 外面，测试读写拆散的配置

db()->query('select * from user');
db()->execute('insert into user values(3,"xiaolong")')