MySQL高级学习笔记(二)

一、MySQL逻辑架构简介

1.1 连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.2 服务层

Management Serveices & Utilities：

系统管理和控制工具  

SQL Interface: SQL接口

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

Parser: 解析器

SQL命令传递到解析器的时候会被解析器验证和解析。 

Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。 
用一个例子就可以理解： select uid,name from user where  gender= 1;
优化器来决定先投影还是先过滤。

Cache和Buffer： 查询缓存。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
缓存是负责读，缓冲负责写。

1.3 引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

1.4 存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

二、查询流程

mysql客户端通过协议与mysql服务器建连接,发送查询语句,先检查查询缓存,如果命中(一模一样的sql才能命中),直接返回结果,否则进行语句解析,也就是说,在解析查询之前,服务器会先访问查询缓存--它存储select语句以及相应的查询结果集,如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析优化执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的'解析树'。mysql解析器将使用mysql语法规则验证和解析查询;预处理器根据一些mysql规则进一步检查解析树是否合法。
查询优化器:当解析树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回了相同的结果。优化器的作用就是找到最好的执行计划。

三、Mysql存储引擎

3.1 查看命令

mysql> show engines;
mysql> show variables like '%storage_engine%';

3.2各个引擎简介

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况

MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

CSV引擎

CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis)

Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

3.3 MyISAM和InnoDB

MyISAM	InnoDB
不支持主外键	支持主外键
不支持事务	支持事务
支持表锁	支持行锁[适合高并发]
只缓存索引	还缓存真实数据
表空间小	表空间大
关注点是性能	关注点是事务

四、索引

4.1 机器执行SQL语句顺序

4.2 索引是什么

索引是一种数据结构

可以简单理解为“排好序的快速查找数据结构”。

索引的目的在于提高查询效率,可以类比字典,如果要查'mysql'这个单词,先定位到m字母,然后从上往下找到y字母,再找到剩下的sql;
如果没有索引,那么你可能需要从a--z进行查找;
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

索引本身也很大,不可能全部存储在内容中,因为索引往往以索引文件形式存储在磁盘上;
我们平常说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引、次要索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引。统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引等。

4.3 索引优势

1. 提高数据检索的效率,降低数据库的IO成本
2. 通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗

4.4 索引劣势

1. 索引也是一张表,也占内存
2. 对数据进行更新时候,也需要维护索引

4.5 索引分类

 1. 单指索引
 2. 唯一索引
 3. 复合索引
 4. 基本语法

#创建索引
create [unique] index indexName on mytable(columnname);
alter mytable add [unique] index indexName on (columnname) 
#删除索引
drop index indexName on mytable;
#查看索引
show index from table_name\G

#有四种方式来添加数据表的索引
alter table table_name add primary key(column_name);#mysql会自动为主键字段添加索引
alter table table_name add unique index_name(column_name);#创建唯一索引
alter table table_name add index index_name(column_name);#创建普通索引,索引值可出现多次
alter table table_name add fulltsxt index_name(column_name);

4.6 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型的。

1. BTree索引
   

   【初始化介绍】 
   一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
   非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
   
    【查找过程】
   如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
   
    真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
   

2. Hash索引

3. full-text全文索引

4. R-Tree索引

4.7 哪些情况需要建索引

 1. 主键自动建立唯一索引
 2. 频繁作为查询条件的字段应该创建索引
 3. 查询中与其他表关联的字段,外键关系建立索引
 4. 频繁更新的字段不适合创建索引(每次更新还需要更新索引)
 5. where条件里用不到的字段不创建索引
 6. 单键/组合索引  (在高并发情况下倾向创建组合索引)
 7. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
 8. 查询中统计或者分组的字段

4.8 哪些情况不用建索引

 1. 表记录太少
 2. 经常增删改的表(不仅仅需要维护表,还需要维护索引)
 3. 数据重复且分布平均的表字段

五、Explain性能分析

5.1 MySql常见瓶颈

 1. CPU:CPU在饱和时的时候一般发生在数据装入内存或从磁盘上读取数据时候
 2. IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
 3. 服务器硬件的性能瓶颈:top,free,lostat和vmstat来查看系统的性能瓶颈

5.2 Explain

5.2.1 是什么

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道mysql是如何处理你的sql语句的,分析你的sql语句或者是表结构的性能瓶颈

5.2.1 能干吗

 1. 表的读取顺序
 2. 哪些索引可以使用
 3. 数据读取操作的操作类型
 4. 哪些索引被实际使用
 5. 表之间的引用
 6. 每张表有多少行被优化器查询

5.2.2 怎么玩

explain+sql语句

5.2.3 各字段解释

1. id
表的读取顺序
select查询的序列号,包含一组顺序,表事查询中执行select子句或操作表的顺序
id相同,执行顺序从上到下
如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

2. select_type
数据读取操作的操作类型
取值有simple、primary、subquery、derived、union、union result
查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
simple:简单的select查询,查询中不包含子查询或者union
primary:查询中若包含任何复杂的子部分,最外层查询被标记为primary
subquery:查询中若包含任何复杂的子部分,子查询被标记为subquery
derived:在from列表中包含的子查询表标记为derived,MYSQL会递归执行这些子查询,把结果放在临时表里
union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select被标记为deriver
union result:从union表获取结果的select

3. table
显示这一行的数据是关于那张表的

4. type
显示查询使用了哪种类型
取值有ALL、Index、range、ref、eq_ref、const,system、null等
从最好到最差的依次是:system>const>eq_ref>ref>range>index>all
一般来说,得保证查询到达range级别,最好是ref
system:表只有一行记录,这是const类型的特例,平时不会出现
const:表示通过索引一次就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快;如将主键置于where列表中,MYSQL就能将该查询转换为一个常量
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
ref:非唯一性索引扫描,返回匹配某个单独值的所有行;本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
range:只检索给定范围的行,使用一个索引来选择行;key 列显示使用了哪个索引,一般就是在你的where语句中出现了between and等的查询,这种范围扫描索引比全表扫描要好,因为只扫描部分索引,不用扫描全部
index:只遍历索引树(虽然all和Index都是读全表,但是Index是从索引中读取,而all是从硬盘中读取的)
all:全表扫描

5. possible_keys
显示可能应用在这张表中的索引,一个或多个
查询涉及到的字段上若存在索引,则该索引被列出,但不一定被查询实际使用

6. key
实际使用的索引,如果为null,则没有使用索引
查询中若使用了覆盖索引,则该索引仅仅出现在key列表中

7. key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下越小越好
值为索引字段的最大可能长度,并非实际使用长度,既是根据表定义计算而得,不是通过表内检索出的

8. ref
显示索引的哪一列被使用了,如果可能的话,是一个常数,那些列或常量用于查找索引列上的值

9. rows
每张表有多少行被优化器查询 
根据表统计信息及索引选用情况,大致估算出找到所需记录所需要读取的行数,越小越好

10. extra
10.1 using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。
mysql中无法利用索引完成的排序操作称为"文件排序";
10.2 using temporary
使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序和分组查询。
10.3 using index
表示相应的selec操作中使用了覆盖索引,避免访问了表的数据行,效率不错
如果同时出现了using where ,表面索引被用来执行索引键值的查找
如果没有同时出现using where,表面索引只是用来读取数据而非利用索引来执行查找
10.4 using where 
表明使用了where过滤
10.5 using join buffer
使用了连接缓存
10.6 impossible where
where子句的值总是false
10.7 select tables optimized away
在没有groupby子句的情况下,基于索引优化min/max操作或者对于myisan存储殷勤优化count(*)操作,不必等到执行阶段在进行计算,查询执行阶段即完成优化。

六、索引优化

6.1 索引分析

#总结 
#1.where后面的字段建立索引
#2.尽量建立复合索引
#3.对复合索引的 不是第一个字段 的其余字段进行了范围查找 会使后面字段索引失效
#4.对左外连接的表建立索引,应该对右边的表的外键字段建立索引
#5.保证被驱动表的join字段以及建立索引(join后的表为被驱动表)
#6.left join时,选择小表作为驱动表,大表作为被驱动表
#7.inner join时,mysql会自动帮你把小结果集的表作为驱动表
#8.子查询尽量不要放在被驱动表,可能会不走索引

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

SELECT * FROM article;

#1.查询 category_id 为1 且  comments 大于 1 的情况下,views 最多的 article_id。 
 
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

#结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
 
#开始优化：
# 1.1 新建索引+删除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);
DROP INDEX idx_article_ccv ON article
  
# 1.2 第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;
 
#结论：
#type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
#但是我们已经建立了索引,为啥没用呢?
#这是因为按照 BTree 索引的工作原理,
# 先排序 category_id,
# 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
#当 comments 字段在联合索引里处于中间位置时,
#因comments > 1 条件是一个范围值(所谓 range),
#MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
 
# 1.3 删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;
 
# 1.4 第2次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);
 
# 1.5 第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
DROP INDEX idx_article_cv ON article;

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);


INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));


INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

# 下面开始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#结论：type 有All
 
# 添加索引优化
ALTER TABLE `book` ADD INDEX Y ( `card`);
 
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
 
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

6.2 索引失效(应该避免)

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' ,
  age INT NOT NULL DEFAULT 0 ,
  pos VARCHAR (20) NOT NULL DEFAULT '' ,
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
) CHARSET utf8 ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

1. 全值匹配你最爱
2. 最左前缀索引法则:指的是查询从索引的最左前列开始并且不跳过索引中的列(and忽略左右关系)
3. 不在索引列上做任何操作
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用select 索引字段,减少select *
6. mysql在使用不等于的时候无法使用索引会导致全表扫描
7. is not null无法使用索引,但是is null可以
8. like以通配符开头 索引会失效,如果想要两边都%,就使用覆盖索引(建的索引和查的字段顺序和个数最好一致)
9. 字符串不加单引号索引失效
10.少用or,用它来连接会导致索引失效 

6.3 面试题分析

create table test03(
    ->  id int primary key not null auto_increment,
    ->  c1 char(10),
    ->  c2 char(10),
    ->  c3 char(10),
    ->  c4 char(10),
    ->  c5 char(10)
    -> );

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

#1.全值匹配你最爱
explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
#1.1四个索引全用到
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
#1.2用到c1 c2 c3 (我以为用到c1 c2 ,但是范围之后全失效)
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
#1.3用到c1 c2 c3 c4
 explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
#1.4用到c1 c2 
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
#c3也用到了,但是它的作用是在排序而不是查找
#1.5用到c1 c2 
explain select * from test03 where c1='a1' and c2='a2' order by c3;
#c3也用到了,但是它的作用是在排序而不是查找
#1.6用到c1 c2 
explain select * from test03 where c1='a1' and c2='a2' order by c4; 
#using filesort
#1.7用到了c1 但是c2 c3用于排序,无filesort
 explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; 
 #1.8用到了c1 ,有filesort
  explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
 #1.9用到了c1 c2 无filesort
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
#1.10
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3; #用到了c1 c2 无filesort
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2; #用到了c1 c2 无filesort
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;  #用到了c1 ,有filesort
#1.11 用到了c1 无filesort
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
#1.12  用到了c1 有filesort,还有了temporary
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;          

定值、范围还是排序,一般order by 是给个范围
group by 基本上都需要进行排序,会有临时表产生

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引
2. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
3. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
4. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

6.4 小总结

where语句	是否使用索引
where a=3	是,使用a
where a=3,b=3	是,使用a b
where a=3,b=3	是,使用a b c
where b=3或者where b=3 and c=4	否
where a=3 and c=5	是 ,使用a
where a=3 and b>4 and c=5	是,使用a b,b断了,c不能用在范围之后
where a=3 and b like ‘kk%’ and c=4	是,使用到a b c
where a=3 and b like ‘%kk’ and c=4	是 只用到a
where a=3 and b like ‘%kk%’ and c=4	是,只用到a
where a=3 and b like ‘k%kk%’ and c=4	是,使用到a b c

6.5 口诀

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用

七、查询截取分析

7.1 查询优化

小表驱动大表

for(int i=5;...){
	for(int j=1000)
	{
	
	}
}
*****************************
for(int i=1000;...){
	for(int j=5
	{
	
	}
}
******************************
尽量使用上面的,在计算看来这两个结果一样,但是当使用到数据库时,使用上面的减少了连接的建立

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id=B.id
当B表的数据集小于A表的数据集时,用in优于exists    //使用in的话,外面的第一个for循环是B

select * from A where exists (select 1 from B where B.id=A.id)
等价于
for select * from A
for select * from B where B.id=A.id
当A表的数据集小于B表的数据集时,用exists优于in    //使用exists的话,外面的第一个for循环是A
//exists可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果来决定主查询的数据结果是否得以保留

order by 优化

1.order by 子句,尽量使用index 避免产生filesort

order by 两情况,会使用index排序:
1.order by 语句使用索引最左前列原则
2.使用where子句与order by子句条件列组合满足索引最左前列
3.where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL,
  name varchar(200)
);
 
INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');
 
CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);
 
SELECT * FROM tblA; 

2.尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀

3.如果不在索引列上完成排序操作,filesort有两种算法:mysql就要启动双路排序和单路排序

双路排序:
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序:
取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题:
由于单路是后出的，总体而言好过双路
但是用单路有问题
在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……
从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

4.优化策略

增大sort_buffer_size参数的设置:用于单路排序的内存大小
增大max_length_for_sort_data参数的设置:单次排序字段大小。(单次排序请求)
去掉select 后面不需要的字段:select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的

Why:
提高Order By的速度
1. Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：
1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

2. 尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

3. 尝试提高 max_length_for_sort_data
提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 

5.小总结

为排序使用索引:
mysql两种排序方式:文件排序或扫描有序索引排序
mysql能为排序和查询使用相同的索引

key a_b_c(a,b,c)
order by 使用索引最左前缀:
order by a
order by a,b
order by a,b,c
order by a desc,b desc, c desc

如果where使用索引的最左前缀定义为常量,则order by 能使用索引:
where a=const order by b,c
where a=const and b=const order by c
where a=const and b>const order by b,c 

不能使用索引进行排序:
order by a asc,b desc,c desc /*排序不一致*/
where g=const order by b,c /*丢失a*/
where a=const order by c /*丢失b索引*/
where a=const order by a,d /*d不是索引的一部分*/
where a in (...) order by a,c /*对于排序来说,多个想等条件也是范围查询*/

group by 优化

1.group by实质是先排序后进行分组，遵照索引建的最佳左前缀
2.当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
3.where高于having，能写在where限定的条件就不要去having限定了。

7.2 慢查询日志

是什么

1.MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

2.具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

3.由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

怎么玩

1.说明
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
2.查看是否开启以及如何开启
show variables like '%slow_query_log%';
set global slow_query_log=1;
3.那么开启了之后,什么样的sql语句会被记录到日志里呢
show variables like 'long_query_time%';查看默认值;
可以使用命令修改值的大小 set global long_query_time=3;
假如运行时间恰好等于,也不会被记录,记录的是大于这个运行时间的sql语句
4.查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%'

7.3 批量数据脚本

1.建表

# 新建库
create database bigData;
use bigData;

#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;  
 
 
#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
hiredate DATE NOT NULL,/*入职时间*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*红利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ; 

2.设置参数log_bin_trust_function_creators

set global log_bin_trust_function_creators=1;

3.创建函数,保证每条数据不同

#随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    ##方法开始
 DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 ##声明一个 字符窜长度为 100 的变量 chars_str ,默认值 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
##循环开始
 WHILE i < n DO  
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
##concat 连接函数  ，substring(a,index,length) 从index处开始截取
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$
 
#假如要删除
#drop function rand_string;

#随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
 
 
#假如要删除
#drop function rand_num;

4.创建存储过程

#创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  ；提高执行效率
 SET autocommit = 0;    
 REPEAT  ##重复
 SET i = i + 1;  
 INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一个循环
 END REPEAT;  ##满足条件后结束循环
 COMMIT;   ##执行完成后一起提交
 END $$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

#创建往dept表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$ 
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

5.调用存储过程

#dept
DELIMITER ;
CALL insert_dept(100,10); 

#执行存储过程，往emp表添加50万条数据
DELIMITER ;    #将 结束标志换回 ;
CALL insert_emp(100001,500000); 
CALL insert_emp10000(100001,10000); 

7.4 show profile

是什么

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
官网地址:http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤

1.查看当前mysql版本是否支持:
Show  variables like 'profiling';

2.开启功能 默认关闭  
set profiling=1;

3.运行sql 
select * from emp group by id%10 limit 150000;
select * from emp group by id%20  order by 5

4.查看结果 show profiles;

5.诊断sql,show profile cpu,block io for query 上一步前面地问题sql数字号码
type:  
 | ALL                        --显示所有的开销信息  
 | BLOCK IO                --显示块IO相关开销  
 | CONTEXT SWITCHES --上下文切换相关开销  
 | CPU              --显示CPU相关开销信息  
 | IPC              --显示发送和接收相关开销信息  
 | MEMORY           --显示内存相关开销信息  
 | PAGE FAULTS      --显示页面错误相关开销信息  
 | SOURCE           --显示和Source_function，Source_file，Source_line相关的开销信息  
 | SWAPS            --显示交换次数相关开销的信息
 
6.日常开发需要注意的结论
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table 创建临时表
	拷贝数据到临时表
	用完再删除
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
locked

7.5 全局查询日志

永远不要在生产环境中开启这个功能

命令
set global general_log=1;

#全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中
set global log_output='TABLE';

此后 ，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;

八、MySQL的锁机制

8.1 概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，
那么如何解决是你买到还是另一个人买到的问题？
这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，
然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类
从对数据操作的类型（读\写）分
	读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。可以读自己,不能修改自己,不能读别的表;别人可以读,别人修改时发生阻塞;
	写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的粒度分
	表锁
	行锁

8.2 三锁

表锁(偏读)

1.特点:偏向myisan存储引擎,开销小,加锁快;无死锁;锁定力度大,发生锁冲突的概率最高,并发度最低;
2.案例分析:

【表级锁分析--建表SQL】
 
create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;
 
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
 
select * from mylock;
 
【手动增加表锁】
 #lock table 表名字1 read(write)，表名字2 read(write)，其它;
 lock table mylock read,dept write;
【查看表上加过的锁】
  show open tables;
【释放表锁】
unlock tables;

#1.lock table mylock read
当前session可以读,其它session也可以读
当前session不能查询其它没有锁定的表,其它session可以查询或者更新未锁定的表
当前session中插入或者更新 锁定的表 都会提示错误,其它session插入或更新锁定表会一直等待获得锁
#2.lock table mylock write
当前session可以读,其它session阻塞等待锁释放
当前session不能查询其它没有锁定的表,其它session可以查询或者更新未锁定的表
当前session可以更新 锁定的表,其它session插入或更新锁定表会一直等待获得锁

3.案例结论:
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。 
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）锁类型他人可读他人可写读锁是否写锁否否

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

结论：
结合上表，所以对MyISAM表进行操作，会有以下情况： 
  1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。 
  2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

行锁(偏写)

1.特点
偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

2.行锁支持事务
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。 
l 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 
l 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。 
l 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。 
l 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。 

并发事务处理带来的问题:
更新丢失(Lost Update):
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。 

脏读(Dirty Reads):一句话：
事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读(Non-Repeatable Reads):
在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
一句话：一个事务范围内两个相同的查询却返回了不同数据。
	
幻读(Phantom Reads):
  一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
  一句话：事务A 读取到了事务B提交的新增数据，不符合隔离性。
   
事务隔离级别:
脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。 
常看当前数据库的事务隔离级别：show variables like 'tx_isolation';

3.案例分析

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;

insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

select * from test_innodb_lock;
#关闭自动提交
set autocommit=0;


#1.如何锁定一行
select xxx ... for update锁定某一行后,其它的操作会被阻塞,直到锁定行的会话提交commit;
#2.间隙锁
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。即使session2操作的是另外一行不存在的数据,也会阻塞;
#3.索引失效行锁变成表锁
本来各自锁定各自的行，互相不影响，如果没有正常使用，会导致行锁变表锁
比如没加单引号导致索引失效，行锁变表锁被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新

4.案例结论
 Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

5.行锁分析
show status like 'innodb_row_lock%';
对各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是
Innodb_row_lock_time_avg（等待平均时长），
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
最后可以通过SELECT * FROM information_schema.INNODB_TRX\G;来查询正在被锁阻塞的sql语句。
	
6.优化建议
6.1尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
6.2尽可能较少检索条件，避免间隙锁
6.3尽量控制事务大小，减少锁定资源量和时间长度
6.4锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
6.5涉及相同表的事务，对于调用表的顺序尽量保持一致。
6.6在业务环境允许的情况下,尽可能低级别事务隔离

页锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

九、主从复制

9.1复制的基本原理

slave会从master读取binlog来进行数据同步
MySQL复制过程分成三步：
1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2 slave将master的binary log events拷贝到它的中继日志（relay log）；
3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

9.2复制的基本原则

复制的基本原则:
每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve

9.3复制的最大问题

延时

9.4一主一从常见配置

1.mysql版本一致且后台以服务运行
2.主从都配置在[mysqld]结点下，都是小写
3.主机修改my.ini配置文件
	[必须]主服务器唯一ID
		server-id=1
	[必须]启用二进制日志
		log-bin=自己本地的路径/data/mysqlbin
		log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
	[可选]启用错误日志
		log-err=自己本地的路径/data/mysqlerr
		log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
	[可选]根目录
		basedir="自己本地路径"
		basedir="D:/devSoft/MySQLServer5.5/"
	[可选]临时目录
		tmpdir="自己本地路径"
		tmpdir="D:/devSoft/MySQLServer5.5/"
	[可选]数据目录
		datadir="自己本地路径/Data/"
		datadir="D:/devSoft/MySQLServer5.5/Data/"
	read-only=0
		主机，读写都可以
	[可选]设置不要复制的数据库
		binlog-ignore-db=mysql
	[可选]设置需要复制的数据库
		binlog-do-db=需要复制的主数据库名字
4.从机修改my.cnf配置文件
	[必须]从服务器唯一ID
	[可选]启用二进制日志
5.因修改过配置文件，请主机+从机都重启后台mysql服务
6.主机从机都关闭防火墙
	windows手动关闭
	关闭虚拟机linux防火墙    service iptables stop
7.在Windows主机上建立帐户并授权slave
	GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456';
	flush privileges;
	查询master的状态
		show master status;
		记录下File和Position的值
	执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
8.在Linux从机上配置需要复制的主机
	CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='zhangsan',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;
	启动从服务器复制功能
		start slave;
	show slave status\G
		下面两个参数都是Yes，则说明主从配置成功！
		Slave_IO_Running: Yes
		Slave_SQL_Running: Yes
9.主机新建库、新建表、insert记录，从机复制
10.如何停止从服务复制功能
	stop slave;