一 Mysql简介

概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

M/SQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql是开源的，所以你不需要支付额外的费用。

Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。

Mysql可以允许于多个系统上，并且支持多种语言。这些编程语詈包括C、C++、Python、Java、Perl、PHP、Eifel、Ruby和Tcl等。Mysq|对PHP有很好的支持，PHP是目前最流行的Web开发语言。

MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

高级MySQL

mysql内核
sql优化攻城狮
mysql服务器的优化
各种参教常量设定
查询语句优化
主从复制
软硬件升级
容灾备份
sql编程

完整的mysql优化需要很深的功底,大公司甚至有专门的DBA写上述

MySql存储引擎

如何用命令查看

#看你的mysql现在已提供什么存储引擎:

mysql> show engines;

#看你的mysql当前默认的存储引擎:

mysql> show variables like ‘%storage_engine%’;

MyISAM和InnoDB区别

阿里巴巴、淘宝用哪个

Percona为MySQL数据库服务器进行了改进，在功能和性能上较MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能、为DBA提供一些非常有用的性能诊断工具;另外有更多的参数和命令来控制服务器行为。

该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好，

阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
AliSql+AliRedis

二 索引优化分析

性能下降SQL慢,执行时间长,等待时间长 的原因

1 查询语句写的烂

2 索引失效

单值
idx_user_name 就是索引名称 给字段name创建索引

复合
idx_user_nameEmail 就是索引名称 给name和email创建复合索引

3 关联查询太多join（设计缺陷或不得已的需求)
太多多表关联查询也会导致查询慢

4 服务器调优及各个参数设置（缓冲、线程数等)

常见通用的Join查询

SQL执行顺序

手写

机读

总结

Join图 7种JOIN

7种

索引简介

索引是什么

MySQL官方对索引的定义为:索引(Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。

索引的目的在于提高查询效率，可以类比字典，
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要a—Z，如果我想找到Java开头的单词呢﹖或者Oracle开头的单词呢?
是不是觉得如果没有索引，这个事情根本无法完成?

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

详解(重要)
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
下图就是一种可能的索引方式示例:

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

结论
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引,复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hashindex)等。

索引优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引.

mysql索引分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，但允许有空值

复合索引

即一个索引包含多个列

基本语法

ALTER命令的使用

mysql索引结构

BTree索引(针对java开发需要重点了解)

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指针，内存时间因为非常短（相比磁盘的I0)可以忽略不计扌通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次I0，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次lO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

Hash索引

full-text全文索引

R-Tree索引

哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3.查询中与其它表关联的字段，外键关系建立索引

4.频繁更新的字段不适合创建索引 – 因为每次更新不单单是更新了记录还会更新索引表

5.Where条件里用不到的字段不创建索引

6.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

8.查询中统计或者分组字段

哪些情况不要创建索引

1.表记录太少

2.经常增删改的表:
Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析

MySql Query Optimizer

1 Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)

2 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的Hint信息(如果有)﹐看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

IO:磁盘I/o瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈: top,free, iostat和vmstat来查看系统的性能状态

Explain(重点) 解释

是什么(查看执行计划)

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

官网介绍
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

能干嘛

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

怎么玩

Explain + SQL语句

执行计划包含的信息

各字段解释

id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况

一: id相同，执行顺序由上至下

二: id不同，如果是子查询 ,id的序号会递增，id值越大优先级越高，越先被执行

三: id相同不同，同时存在

select_type

有哪些

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

1.SIMPLE 简单的select查询,查询中不包含子查询或者UNION

2.PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为

3.SUBQUERY 在SELECT或WHERE列表中包含了子查询

4.DERIVED
在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询,把结果放在临时表里。

5.UNION
若第二个SELECT出现在UNION之后，则被标记为UNION;
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED

6.UNION RESULT 从UNION表获取结果的SELECT

table

显示这一行的数据是关于哪张表的

type

访问类型排列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是:

system > const > eq_ref > ref > fultext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

常见的 从最好到最差依次是: system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

显示查询使用了何种类型 从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL

system 表只有一行记录（等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计

const 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询 ,这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)

All Full Table Scan，将遍历全表以找到匹配的行

possible_keys

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

key

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中
虽然possible_keys 理论上没有使用索引,但是建立的索引与查询的字段相符,这样就使用了索引

key_len

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

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’
查询中与其它表关联的字段，外键关系建立索引

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra 包含不适合在其他列中显示但十分重要的额外信息

1.Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySOL中无法利用索引完成的排序操作称为"文件排序”
发现第一次查询出现Using filesort,如果能够优化不出现,就想第二次执行的语句一样最好,不出现效率更高

2.**Using temporary**使了用临时表保存中间结果,MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.
group by 若建立了索引,那么就都要在group by后使用这个索引,或者复合索引都要用到,才会是sql效率高,不会出现Using filesort和Using temporary

3.USING index 效率不错!
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行
效率不错!
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
覆盖索引(Covering Index) : 建立的索引刚好是select 字段 from 中查询的字段
一搬说为索引覆盖。
理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引赵回select列表中的子段，川不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
注意:
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

4.Using where 表明使用了where过滤

5.using join buffer 使用了连接缓存:

6.impossible where where子句的值总是false，不能用来获取任何元组

7.select tables optimized away 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8.distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

索引优化

索引分析

单表

建表SQL

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');

案例

1.查询category_id为1且comments大于1的情况下,views最多的article_id。

上面的分析sql结论:很显然,type是ALL,即最坏的情况。Extra里还出现了Using filesort,也是最坏的情况。优化是必须的。

发现此表根本没有索引

开始优化:
#1.1新建索引+删除索引
建立索引的两种sql语句

ALTER TABLE 'article’ADD INDEX idx_article_ccv ( ‘category_id’, 'comments '; ‘views’);create index idx_article_ccv on article(category_id,comments,views);

create index idx _article_ccv on article(category_id,comments,views);

再次查看索引:show index from artic1e;

再次分析sql

虽然使用到了索引,但是还是存在using filesort

结论:范围以后索引失效,但是是需求是范围查询,所以当前建立索引不适合当前业务需求

删除索引后再次分析

DROP INDEX idx_article_ccv ON article
删除后= 与 > 都出现文件排序using filesort

#1.2第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM article"WHEREcategory_id=1 AND comments >10RDERBY views DESC LIMIT1;EXPLAIN SELECT id,author_id FROM article 'WHEREcategory_id=1AND comments =3 ORDER BY views DESCLIMIT1
#结论:
#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次新建索引
#ALTERTABLE 'article’ADD INDEX idx_article_cv ( ‘category_id’ , 'views` ) ;create index idx_article_cv on article(category_id,views:

#结论:可以看到,type变为了ref,Extra中的Using filesort也消失了,结果非常理想。DROP INDEX idx_article_cv ON article;

两表

建表SQL

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)));

关联查询

案例

下面开始explain分析
EXPLAIN SELECT* FROM class LEFT JOIN book ON class.card = book.card;

#结论: type有All

我们需要添加索引优化,但是不知道加在左表还是右表啊,那么就试一试,看看那个效率高,在得出正确的添加索引结论

添加索引优化
ALTER TABLE book ADD INDEXY ( card);
左连接将索引加右表分析

删除刚加的索引

左连接将索引加在左表分析

结论: 两表,左右连接索引相反加,sql执行效率高
EXPLAIN SELECT*FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的type变为了ref,rows也变成了优化比较明显。
#这是由左连接特性决定的。LEFTJOIN条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。

三表

建表SQL

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)));CREATE TABLE IF NOT EXISTS`phone`(
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`phoneid`)
)ENGINE = INNODB;INSERT INTO phone(card) VALUES(FLOOR(1+(RAND( * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20);
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND()* 20));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND()* 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND()* 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND()* 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND()* 20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 +(RAND() * 20)));

查看

案例

SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;

EXPLAIN 分析一下sql

ALTER TABLEphoneADD INDEX z( card );

ALTER TABLE'bookADD INDEX Y ( card);#上一个case建过一个同样的

再次EXPLAIN

后⒉行的type都是ref且总rows优化很好,效果不错。因此索引最好设置在需要经常查询的字段中。

【结论】Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数:“永远用小结果集驱动大的结果集”
优先优化NestedLoop的内层循环;
保证Join语句中被驱动表上Join条件字段已经被索引;
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置;

索引失效(应该避免)

建表SQL

CREATE TABLE staffs (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(24)NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20)NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间')CHARSET utf8 COMMENT'员工记录表';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());SELECT *FROM staffs;
# 建立复合索引
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);

案例(索引失效)

查看索引

1.全值匹配我最爱
EXPLAIN SELECT FROM staffs WHERE NAME =July;

EXPLAIN SELECT * FROM staffs WHERE NAME = July AND age = 25;
EXPLAIN SELECTFROM staffs WHERE NAME = July’AND age = 25 AND pos = dev;

2.**最佳左前缀法则**
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
只查询age与pos

只查询pos

只查询name

结论:最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
俗话说带头大哥不能死,复合索引的第一个不能不使用
================================================
那么只查询name与pos 不要中间的age是否会索引失效呢?
答案是不会

但是由于没有查询age,所以子索引age没有用到,那么就不会用到pos索引,只会用到带头的name,因为age没有,所以就找不到pos

3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
范围之后全失效

EXPLAIN一下

4.存储引擎不能使用索引中范围条件右边的列

将age=25变成age>25 type由ref变成range,效率下降了

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
按需取所要字段
不使用select * 但是age有范围查询,效率会高,相比上面第4条案列解释的sql比较查看

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

7.is null ,is not null也无法使用索引

8.like以通配符开头('%abc...' )mysql索引失效会变成全表扫描的操作'

使用 %字符串%
使用 %字符串
使用 字符串%

问题:解决like '%字符串%'时索引不被使用的方法??

建表sql

CREATE TABLE`tbl_user`(
`id` INT(11)NOT NULL AUTO_INCREMENT,
`name`VARCHAR(20)DEFAULT NULL,
`age`INT(11)DEFAULT NULL,
email VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;#drop table tbl_userINSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email)VALUES('2aa2',222, 'a@163.com');
INSERT INTO tbl_user(NAME,age,email)VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');

业务分析

创建索引

CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

注意:未建立上方复合索引前,使用%字符串% 会全表扫描,索引失效

注意:查询字段为* 的时候,会导致索引失效

9.字符串不加单引号索引失效

10.少用or,用它来连接时会索引失效口

11.小总结

三 查询截取分析

查询优化

永远小表驱动大表 in exists

类似嵌套循环Nested Loop 嵌套 for循环

exists

.EXISTS

SELECT … FROM table WHERE EXISTS(subquery)
该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE)来决定主查询的数据结果是否得以保留。

·提示

1 EXSTS (subquery)只返回TRUE或FALSE，因此子查询中的SELECT*也可以是SELECT 1或 ‘X’，官方说法是实际执行时会忽略SELECT猜单，因此没有区别。
2 EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
3 EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

结论：

当B表的数据集必须小于A表的数据集时，用in优于exists。
当A表的数据集系小于B表的数据集时，用exists优于in。
小表驱动大表

使用之前的部门员工表演示 in exists 查询

ORDER BY关键字优化

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

建表SQL

CREATE TABLE tblA(
#id int primary key not null auto_increment,
age lNT,
birth TIMESTAMP NOT NULLINSERT INTO tblA(age,birth) VALUES(22,NOW());
INSERT INTO tblA(age,birth) VALUES(23,NOW());
INSERT INTO tblA(age,birth) VALUES(24,NOW());# 创建age birth的复合索引
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);

案例
查询出来

1 只order by age 使用到了索引，未产生FileSort

2 order by age，birth 使用到了索引，未产生FileSort

3 order by birth 使用到了索引 产生了FileSort

4 order by birth，age 使用到了索引 产生了FileSort ， 因为创建复合索引排序为 age，birth，你反着也会导排序产生FileSort

5 不where 直接order by birth 使用到索引，产生FileSort

6 直接order by age ASC ，birth DESC 会产生FileSort ，因为默认是ASC 生序的，而案例的birth使用将序排列那么就会导致mysql从新排一次序，那么就会产生FileSort，导致效率变慢

MySQL支持二种方式的排序，FileSort和Index，Index效率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序:

ORDER BY语句使用索引最左前列
使用Where子句与Order BY子句条件列组合满足索引最左前列

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

如果不在索引列上，FileSort有两种算法:

mysql就要启动双路排序和单路排序

双路排序

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

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql 4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机I0变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O.
本来想省一次I/O操作，反而导致了大量的lO操作，反而得不偿失。

优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

Why 如何提高Order By的速度

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

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

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

小总结

GROUP BY关键字优化

与order by一样方式优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having，能写在where限定的条件就不要去having限定了。

慢查询日志

是什么

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

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

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

怎么玩

说明

默认情况下 MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

查看是否开启及如何开启

默认 SHow VARIABLES LIKE ‘%slow_query_log%’;
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的,
可以通过设置slow auery loa的值来开启

开启 set global slow_query_log=1;
使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

如果要永久生效，就必须修改配置文件my.cnf(其它系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件
slow_query_log =1
slow_query_log_file=/var/lib/mysql/fs-slow.log
关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log (如果没有指定参数slow_query_log_file的话)
I
一般 不建议 修改my.cnf永久生效，因为会对mysql的性能有影响

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢?

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令:SHOW VARIABLES LIKE ‘long_query_time%’;

可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

案例

查看当前多少秒算慢
设置慢的阙值时间
SHOW VARIABLES LIKE ‘long_query_time%’;
修改为阙值到3秒钟的就是慢sql

为什么设置后看不出变化?
需要重新连接或新开一个会话才能看到修改值。
SHOW VARIABLES LIKE ‘long_query_time%’;
show global variables like ‘long_query_time’;

记录慢SQL并后续分析
se1ect sleep(4 );
sleep(4 )命令就是查询的时候睡4秒，因为设置的值为3秒就为慢sql，那么这条sql会被mysql认为慢sql

查询当前系统中有多少条慢查询记录
show global status like ‘%Slow_queries%’;

配置版

【mysqld】下配置:
slow_query_log=1,
slow query_log_file=/var/lib/mysql/fs-slow.log
long_query_time=3;
log_output=FILE

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息

mysqldumpslow –help

s:是表示按照何种方式排序;
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据;
g:后边搭配一个正则匹配模式，大小写不敏感的;

工作常用参考

从fs.log中
得到返回记录集最多的10个SQL
mysqldumpslow -s r-t 10 /var/lib/mysql/fs-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/fs-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/fs-slow.log
另外建议在使用这些命令时结合|和more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/fs-slow.log | more

批量数据脚本

往表里插入1000W数据

1.建表sql

#新建库
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=GBK ;#2建表emp
CREATE TABLE emp
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
lename 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=GBK ;

2.设置参数log_bin_trust_function_creators

创建函数，假如报错:This function has none of DETERMINISTIC…
#由于开启过慢查询日志，因为我们开启了bin-log,我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators";

set global log_bin_trust_function_creators=1;

#这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法:

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下letc/my.cnf 下my.cnf[mysqld]加上log_bin_trust_function_creators=1

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

随机产生字符串

# 返回随机字符串的函数
DELIMITER $$
CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100)DEFAULT'abcdefghijklmnopqarstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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));
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表中插入数据的存储过程

# 创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =O把autocommit设置成0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, lename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i)
,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

创建往dept表中插入数据的存储过程

#创建往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 $$

5.调用存储过程

dep

# 调用  DELIMITER的意思就是之前创建函数与存储过程的时候,指定为$$ 开头结尾,但是我们已经创建完成了,就恢复为 ; 结尾SELECT * FROM `dept`DELIMITER;
CALL insert_dept(100,10);

emp

执行存储过程 ，往emp表添加50万条数据

Show Profile

是什么:

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

分析步骤

1.是否支持，看看当前的mysql版本是否支持
Show variables like ‘profiling’;
默认是关闭，使用前需要开启

或者:Show variables like ‘profiling%’;

2.开启功能，默认是关闭，使用前需要开启
SET profiling=ON;

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 上一步前面的问题SOL数字号码:
查询上面show profile中id为3号的 查参数字段为 cpu ,block io 这两个参数最常用
show profile cpu ,block io for query 3;

参数备注
type:
|ALL –显示所有的开销信息
| BLOCK IO–显示块lO相关开销
| CONTEXT SWITCHES –上下文切换相关开销| CPU–显示CPU相关开销信息
| lPC–显示发送和接收相关开销信息
|MEMORY–显示内存相关开销信息
|PAGE FAULTS–显示页面错误相关开销信息
| SOURCE–显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS–显示交换次数相关开销的信息

6.日常开发需要注意的结论
出现以下四种,就需要优化sql了
一 converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了。
二 Creating tmp table创建临时表
———————————————–拷贝数据到临时表
———————————————–用完再删除
三 Copying to tmp table on disk把内存中临时表复制到磁盘，危险!
四 locked

查看一下上面2.7秒多的8号sql
show profile all for query 8;

可以看到这个sql是在copying to tmp 2.5秒,所以sql是有问题的

全局查询日志

配置启用
在mysql的my.cnf中，设置如下:#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

编码启用
命令
set global general_log=1;
set global log_output=‘TABLE’;

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

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

四 MySql锁机制

概述

定义

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

生活购物 打比方 秒杀

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题?

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

从对数据操作的类型（读\写)分
读锁(共享锁):针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁(排它锁):当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分
表锁
行锁

三锁

表锁(偏读）

特点

偏向MyISAM存储引擎，开销小，加锁快;无死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低。

案例分析

建表sql

#【表级锁分析-建表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 表名字 read(write)，表名字2 read(write)，其它;

【查看表上加过的锁】
show open tab1es;

发现表中都没用锁

给mylock表加读锁给book表加写锁
lock table mylock read , book write ;

解锁
unlock tables ;

加读锁 案例演示

1.会话1 mylock加读锁,会话1,2分别查询mylock表

2.会话1 mylock加读锁,会话1更新mylock表

3.会话1 mylock加读锁, 会话1 查询其他表

4.会话1 mylock加读锁, 会话2 更新mylock表

会话1 unlock 解锁

加写锁 案例演示

首先 unlock tables;

会话1 给mylock 加写锁

会话1 unlock tables;

案例结论

MyISAM在执行查诲语句(SELECT)前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。MySQL的表级锁有两种模式:

表共享读锁(Table Read Lock)

表独占写锁(Table Write Lock)

结论:

结合上表，所以对MyISAM表进行操作，会有以下情况:

1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

2、对MylSAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作,只有当写锁释放后，才会执行其它进程的读写操作

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

表锁分析

【看看哪些表被加锁了】
mysql>show open tables;
【如何分析表锁定】
可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定:SQL:
show status like ‘table%’;

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下:

Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1;

Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况;

此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

行锁(偏写)

特点:

InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB与MyISAM的最大不同有两点:
一是支持事务（TRANSACTION);
二是采用了行级锁

由于行锁支持事务，事务知识点概念

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

并发事务处理带来的问题
更新丢失(Lost Update)
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题――最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。
脏读(Dirty Reads)
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态;这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话:事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
不可重复读(Non-Repeatable Reads)
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
一句话:事务A读取到了事务B已经提交的修改数据，不符合隔离性
幻读(Phantom Reads)
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读
一句话:事务A读取到了事务B提交的新增数据，不符合隔离性。
多说一句:幻读和脏读有点类似，
脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

事务隔离级别
脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;

案例分析

建表SQL

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);

行锁定基本演示

先 set autocommit = 0 ;关闭事物的自动提交

会话1 更新数据,会话1 查看 会话2 查看

结论:读己之所写(只能读自己更新的数据)
会话1 2 都commit一下后,就能查看到更新的数据了
会话1 更新a=4这一行 未commit 会话2也来更新a=4这一行

会话1 2 commit;


会话1 更新a=4 会话2 更新a=9 会不会阻塞?

补充：
因为mysql的默认机制是 可重复读 ，会话1 2 都取消自动提交了，当会话1 更新后并且comment，会话2是查看不到会话1更新后的数据，因为关闭了自动提交，而且是重复读，但是：若新建立一个会话3 ，会话3 默认为自动提交，那么会话1 更新后并且comment，会话3能够立马查看到会话1的更新数据

无索引行锁升级为表锁

会话 1 更新语句索引失效 ，未comment ，会话2再次更新

会话1 comment

间隙锁危害

【什么是间隙锁】
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)
【危害】
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

会话1 更新1<a<6 范围内的会产生间隙锁

面试题:常考如何锁定一行

当查询某一行的时候，在 sql 尾部加上for update；就给这一行上行锁了

案列结论

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyYISAM高，甚至可能会更差。

行锁分析

【如何分析行锁定】
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
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个状态变量，比较重要的主要是

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

合理设计索引，尽量缩小锁的范围

尽可能较少检索条件，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度

尽可能低级别事务隔离

页锁

了解一下即可
开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

这三种锁 开销、加锁速度、死锁、粒度、并发性能只能就具体应用的特点来说哪种锁更合适

五 主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

三步骤＋原理图

MySQL复制过程分成三步:
1 master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件，binary log events;

2 slave将master的binary log events拷贝到它的中继日志(relay log) ;

3 slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

复制的基本原则

每个slave只有一个master

每个slave只能有一个唯一的服务器ID

每个master可以有多个salve

复制的最大问题

延时

一主一从常见配置

mysql版本一致且后台以服务运行

主从都配置在[mysqld]结点下，都是小写

主机修改my.ini配置文件

从机修改my.cnf配置文件

[必须]从服务器唯一ID
[可选]启用二进制日志

因修改过配置文件，请主机+从机都重启后台mysql服务

主机从机都关闭防火墙
windows手动关闭
关闭虚拟机linux防火墙service iptables stop

在Windows主机上建立帐户并授权slave
在Linux从机上配置需要复制的主机

主机新建库、新建表、insert记录，从机复制

如何停止从服务复制功能
stop slave;