一、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.5sset  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、创立索引

-- 该语句增加一个主键，这意味着索引值必须是惟一的，且不能为nullalter 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 keyalter 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 表名\Gshow 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 nulllselect 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)

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

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

过程1过程2
增加锁wating期待
id =100wating期待
id=100-1wating期待
id=99wating期待
开释锁id =100
id=100-1
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 utf8partition 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 utf8partition 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 utf8partition 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 utf8partition 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/hashalter 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 utf8partition 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 utf8partition 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 utf8partition 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     代码3end 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 intbegin     return num1+num2;end$

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

create function nhe(n int)  returns intbegin    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)beginselect * 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 rowbegin     #sql语句：（触发的语句一句或多句）end

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

剖析：

监督地点：it_order表

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

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

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

create  trigger  t1after   insert  on  it_orderfor each rowbeginupdate it_goods set goods_number=goods_number-1 where id=1;end$

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

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

特地留神：

create  trigger  t1after   insert  on it_orderfor each rowbeginupdate 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  t2after   delete  on  it_orderfor each rowbeginupdate 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  t3after   update  on  it_orderfor each rowbeginupdate 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  t4before  insert  on  it_orderfor each rowbegin    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")')