mysql的架构图

MySQL由哪两部分组成_数据

mysql的基础架构图

从图中可以看出来。mysql是有着严格的分层结构：1：连接层、2：数据服务层、3：数据驱动层、4：数据落地（数据写道磁盘中）

  1.  第一层连接层

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

  1.  第二层服务层

：SQL接口，缓存查询，SQL的分析和优化及部分内置函数执行，所有跨存储引擎工鞥呢也在这一层，如过程，函数等。在该层服务器会解析查询并创建响应的  内部解析树，并对其完成相应的优化如确定查询表顺序。是否否利用索引等。最后生成相应执行操作。如果select语句，服务器还会查询内部的缓存，如果缓存空间足够大。这样在解决大量读操作环境中能够很好的提升系统性能。

   Connection Pool连接池 Management Services & Utillties 包括备份，容灾恢复，安全，复制，集群 SQL Interface

      SQL接口 Parser 解析 通过select,insert命令解析相应操作

      Optimizer sql优化器，mysql自己认为最优的优化。因为做sql验证，至少执行2次。在极端情况下，把此步操作省略，可加快速度。

      Caches & Buffers 缓存和缓冲

  1.  第三层：可拔插组件的引擎。 绝大部分公司使用MyISAM,InnoDB 存储引擎层，存储引擎真正的负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同，这样我们可以根据自己的实际需要进行选取。 
  2. 第四层：文件存储层 数据存储，主要将数据存储在运行于裸设备的文件系统智商，并完成与存储引擎的交互
  3. 总结： 插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务选择相应存储引擎。

      查看mysql的引擎

      show engines;

MySQL由哪两部分组成_MySQL由哪两部分组成_02

 查看mysql默认引擎

show variables like "%storage_engine%";

MySQL由哪两部分组成_MySQL由哪两部分组成_03

InnoDB与MyISAM引擎区别 

MySQL由哪两部分组成_字段_04

mysql的7种join

MySQL由哪两部分组成_MySQL由哪两部分组成_05

 建表语句

create table dept (
id int PRIMARY key auto_increment,
dept_name varchar(50)
)engine=INNODB auto_increment = 1 default charset utf8;
create table employee(
emp_id int PRIMARY key auto_increment,
emp_name varchar(50),
dept_id int
)engine=INNODB auto_increment = 1 default charset utf8;

insert into dept (dept_name) values ('开发部'),('运营部'),('销售部'),('人事部'),('财务部'),('总经理办公室'),('后勤部'),('市场部');
insert into employee (emp_name,dept_id) values ('方祖阳','1'),('张信杰','1'),('周思蒙','1'),('卜奕炜','1'),('焦宇阳','1'),
('杨仁翔','2'),('赵睿','2'),('杨志康','2'),('陈智轩','2'),('汪豪','3'),('阚志伟','3'),('朱畅',13),('刘海笛','4'),('吴迪','5');

   1.内连接查询（查询的是两表的共同数据）inner join

    

select * from employee a inner join dept b on a.dept_id = b.id ;

   

MySQL由哪两部分组成_mysql_06

  2.左连接（左边的表为主表）left join

select * from employee a left join dept b on a.dept_id = b.id;

MySQL由哪两部分组成_MySQL由哪两部分组成_07

3.在左连接的基础上，只获取A表的独有数据（left join where b.id is null）

select * from employee a left join dept b on a.dept_id = b.id where b.id is null;

MySQL由哪两部分组成_数据_08

 4.右连接（右边的表为主表）right join

select * from employee a right join dept b on a.dept_id = b.id ;

MySQL由哪两部分组成_MySQL由哪两部分组成_09

5. 在右连接的基础上保留B表的数据（right join where a.id is null）

select * from employee a right join dept b on a.dept_id = b.id where a.dept_id is null;

MySQL由哪两部分组成_字段_10

6.满查询 （union）将A表和B表数据都查询出来

select * from employee a left join dept b on a.dept_id = b.id 
 union
 select * from employee a right join dept b on a.dept_id = b.id

MySQL由哪两部分组成_mysql_11

7.在满连接的基础上获取A表独有数据和B表独有的数据 where a.id is null unino where b.id is null 

select * from employee a left join dept b on a.dept_id = b.id where b.id is NULL
UNION 
select * from employee a right join dept b on a.dept_id = b.id where a.dept_id is null;

MySQL由哪两部分组成_字段_12

3. 什么是索引

数据结构。

  1.  可以得到索引的本质，索引是数据结构。索引可以提高查询效率，可以类比字典。就是排好序的快速查找数据结构。
  2.   影响：影响where条件后面的查询条件和order by排序的因素。
  3. 平常所说的索引，如果没有特别指明都是B树结构组织索引，其中聚集索引，次要索引，符合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然，除了B+树这种类型的索引之外，还有哈希索引等。
  4. 影响索引：对表进行了频繁的 insert、delete、update操作。这些操作都会导致索引失效。优点就是查询的效率高、查询快

 

3.1索引的类型

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

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

复合索引： 即一个索引包含多个列

3.2添加表索引的几种方法 

1.添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

3.3索引的数据结构 

  1. BTree索引【真实情况是，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO,性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常高。】
  2. Hash索引
  3. full-text全文索引
  4. R-Tree索引

3.4适合建立索引

  • 主键自动建立唯一索引。
  • 频繁做为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
  • Where条件里用不到的字段不创建索引。
  • 单键、组合索引的选择问题。在高并发下倾向创建组合索引
  • 查询中排序的字段，排序字段若通过索引取访问将大大提高排序速度
  • 查询中统计或者分组字段。

3.5不适合建立索引

  • 表记录太少。300W以上数据SQL性能会下降，官方500-800W
  • 经常增删改
  • 数据重复且分布平均的表字段

e.g.:假如一个表有10万行记录，有一个字段A中有T和F两种值，且每个值的分布概率大概为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度 索引的选择性是指索引列中不同值的数目与表中记录数的比，如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99，一个索引的选择性越接近1，这个索引效率就越高。

3.6查找索引的方法

  1. show keys from dept [tableName];
  2. show index from dept [tableName];

MySQL由哪两部分组成_mysql_13

4.explain各字段含义(是sql分析的重要的凭证)

 实现的方法：通过explain + SQL语句：explain select * from dept;

MySQL由哪两部分组成_MySQL由哪两部分组成_14

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

1.id

 1.1 通过id值可以知道sql语句执行顺序：

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

          id有相同有不同：

id相同：执行顺序是由上到下

MySQL由哪两部分组成_数据_15

如果是子查询，id的序号会递增，id值越大优先级越高，越被执行。所以下例id=2先被执行，type=ALL做的是一个全表扫描

MySQL由哪两部分组成_字段_16

总结：【id 如果相同，可以认为是一组，从上至下顺序执行 在所有组中，id值越大，优先级越高，越先执行】

2.select_type

根据此字段可以看到当前查询类型，select_type常用值：

SIMPLE 简单的select查询，查询中不包含子查询或者

UNION PRIMARY 查询中若包含任何复杂的子部分，最外层查询标记

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

DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表

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

UNION RESULT 从UNION表获取结果的SELECT

3.table

  •     显示这一行数据是关于哪一个表查询。

 derived 临时表

 4.type

  • 表示访问类型，访问类型理论上如下表，排序越靠前性能越好：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  • 但我们工作中常接触到：ALL,index,rang,ref,eq_ref,const,system,NULL

显示查询使用类型从最好到最差依次： system>const>eq_ref>ref>range>index>ALL

  • 一般来说，你的SQL语句至少得保证达到range级别，最好达到ref。
  • 各类型解释：

system :表只有一行（等于系统表）这是const类型的特列，平时不会出现，这个也可以忽略不计 const: 表示通过索引一次就找到了，

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

eq_ref : 唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配，常见于主键或唯一索引的扫描(CEO的那个例子，一个公司就一个CEO)

ref : 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问。它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。(工作中能达到这个就很牛逼了) (一个公司的java开发人员的列子，会返回多行数据)

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

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

all : Full Table Scan,将遍历全表以找到匹配的行。

 5.possible_keys 和 key

  • 用于判断是否使用索引，是否索引失效，你的sql是使用哪个索引。

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

key 实际使用的索引，如果为NULL 则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中,优先使用普通索引，然后使用主键索引

  • 当然一般情况下都是出现理论索引，再看有没有实际用到索引，但个别情况下也会出现理论上没有索引，实际上使用覆盖索引：

MySQL由哪两部分组成_数据_17

索引覆盖：是指在查询中所需要的列的数据都在索引中，所以在索引中就完成了查询操作，没有真正的走列查询。

6.key_len

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

MySQL由哪两部分组成_字段_18

  • 同样查询结果，精度越小，key_len越小，查询效果越好。

7.ref

  • 显示索引的哪一列被使用了，如果可能的话，是一个常量，哪些列被用于查找索引列上的值。

MySQL由哪两部分组成_数据_19

8.rows 

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

MySQL由哪两部分组成_MySQL由哪两部分组成_20

上图，可以看到建立索引和没有建立索引所扫描行数区别。

9.Extra

  • 额外信息，它的显示十分重要。
  • Using filesort （九死一生）使用了文件排序的方式

MySQL由哪两部分组成_数据_21

改进后的方法：按照创建的索引的顺序进行排序如下：

MySQL由哪两部分组成_数据_22

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作成为“文件排序”。(出现这个就代表SQL不好)

  • Using temporay (十死无生)

      新建了内部临时表，保存中间结果，MySQL在对查询结果排序使用临时表，常见于order by 和分组查询 group by，因创建临时表大大降低了性能。

MySQL由哪两部分组成_mysql_23

优化改进

MySQL由哪两部分组成_数据_24

  • Using index
  • 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where,表明索引被用来执行索引键值查找。如果没有同时出现using where ，表示索引用来读取数据而非执行查找动作。

MySQL由哪两部分组成_字段_25

  • Using where

        使用了where过滤

  • Using join buffer
  • 使用了连接缓存
  • impossible where
  • where子句值总是false,不能用来获取任何元组
  • select查询错乱了。

     当你执行name即等于July又等于z3导致SQL混乱： explain select * from staffs where name='July' and name='z3';

  • select tables optimized away
  • 在没有GROUPBY 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • dictinct
  • 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

覆盖索引

覆盖索引：可以简单认为查询列被所建的索引覆盖。 也就是select数据列只从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。

  • 请根据下面执行计划说出执行顺序：

MySQL由哪两部分组成_字段_26

MySQL由哪两部分组成_mysql_27

mysql索引优化的案例

       首先要创建表和插入数据：

 1.单表分析

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

MySQL由哪两部分组成_MySQL由哪两部分组成_28

    问题1：查询category_id =1并且comments大于1，被看过最多那条记录的id，看看没有加索引的情况下的执行计划：

MySQL由哪两部分组成_MySQL由哪两部分组成_29

在诊断的sql的时候：type=all、extra 出现了Using filesort 并且还没有索引。 出现了Using filesort 说明了这条sql查询效率很差

思考：在where使用了3个字段来查询，使用尝试一下在这3个索引上建立索引？

create index idx_article_ccv on article(`category_id`,`comments`,`views`);

  创建索引后：

MySQL由哪两部分组成_MySQL由哪两部分组成_30

type=range、使用了我们创建的索引。但是还是出现了filesourt 说明创建的索引不是最好的效果；

 原因：这是因为BTree索引工作原理，先排序category_id.如果遇到相同的category_id则再排序comments,如果遇到comments再排序views,当comments字段联合索引处于中间位置时，因为comments>1条件为范围值，这样MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

   删除索引

drop index idx_article_ccv on article;

 更换索引给category_id,views创建索引

create index idx_article_cv on article(category_id,views);

再查看执行计划：

MySQL由哪两部分组成_mysql_31

结论： 可以看到type=ref，索引类型更好了。Extra没有filesort，category_id为覆盖索引第一个它功能用于查找，第二个索引views在order by 后面用于排序。不会像上例中第二个索引用于排序后造成第三个索引失效，从而导致sql内部使用filesort进行排序。

2.双表分析

   创建表 和 数据

 

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 book(card) values(floor(1+(rand()*20)));
（左连接查询）
explain select * from class left join book on book.card = class.card;


MySQL由哪两部分组成_字段_32

 显然这样很糟糕，2个查询type都为ALL，这样导致全表扫描。

当然创建索引并不是一下子就创建成功的，我们需要不断的调换寻求最佳方法，所以首先给右表book的card添加索引：

alter table `book` add index Y (`card`);

MySQL由哪两部分组成_数据_33

结论：左连接(left join)将索引加在右表中，type=ref非唯一性索引扫描，并且rows也由原来39+35变成39+1

那么我们尝试给左表class的card添加索引效果会如何呢？

//删除之前创建索引
drop index Y on book;
//给左表class的card添加索引
alter table `class` add index Y (`card`);

执行查询计划：

explain select * from class left join book on book.card = class.card;

MySQL由哪两部分组成_mysql_34

 结论：可以type=index,当然不如上面创建索引ref好，并且rows为20+21，也不如上面创建的索引好。 这么看来左连接把索引加在右表上会比较好

 那么右连接(left join)会怎样呢？道理是相同的

总结：左连接，索引创建右表，右连接索引建在左表上。 # 当然你也可以通过调换2个表位置，也是可以的

3.三张表分析

    原有基础上再增加一张表，再添加一些数据

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

再没有添加索引情况下，先看看执行计划：

explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

MySQL由哪两部分组成_字段_35

结论：type=ALL全表扫描，key都为NULL没有用到索引。

原因：因查询方向是class-->book--->phone,连接方式为left join,那么从双表分析得到一些诀窍，给book和phone添加索引

alter table `phone` add index z (`card`); alter table `book` add index Y (`card`);

再次执行查询计划：

explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

MySQL由哪两部分组成_数据_36

结论：执行计划后两行的type都成为ref,有人会问为什么第一行type还是ALL，当然第一个执行语句需要全表扫描来驱动整个sql语句。而且看rows时候39+1+1 当然好过 39+35+33.读取记录的行数也少了不少。

小总结：

             - 尽可能减少join语句的NestedLoop循环总次数，永远用小的结果集驱动大的结果集.

             - 优先优化NestedLoop的内层循环 （鸡蛋黄，鸡蛋清，鸡蛋壳道理）

             - 保证Join语句中被驱动表上Join条件字段已经被索引。

             - 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

MySQL常见索引失效

索引失效的原因有很多种，这里列举一些常见失效案例。

首先要创建表和插入数据：

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('tom',23,'dev',now());

 添加索引：

// 添加复合索引 name,age,pos 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 select * from staffs where name='July' and age=25 and pos='dev'; //三个条件

MySQL由哪两部分组成_字段_37

结论：伴随where后面查询精度越大，key_len越大，ref中const越多，代价也会越大。 （按照正常的方式来查询的时候，不会发生索引失效问题）

2.未遵循最左前缀原则

explain select * from staffs where age=25;

MySQL由哪两部分组成_数据_38

结论：因为创建索引的时候是按照：name-->age-->pos的顺序。但是这个查询没有了开头的查询（name）所以导致了索引失效。

explain select * from staffs where name='July' and pos ='dep';

MySQL由哪两部分组成_MySQL由哪两部分组成_39

 结论：创建索引的顺序的第一个索引是一定要作为查询条件的，并且ref就一个常量，违背了最左前缀的中间部分不能省，导致只右部分索引起效

3.不要在索引列上做任何操作

  • 所说的操作指的是计算，函数，自动或手动的类型转换，这样会导致索引失效，从而转换全表扫描

//对索引字段进行操作 explain select * from staffs where left(name,4)='July';

MySQL由哪两部分组成_MySQL由哪两部分组成_40

使用left函数，导致索引失效

4.范围检索也会让索引失效

//在索引字段上进行了范围检索 explain select * from staffs where name='July' and age>25 and pos='dev';

MySQL由哪两部分组成_MySQL由哪两部分组成_41

因为第二个字段使用范围查找，导致第二个索引用途不是检索而是排序，这样导致第三个索引失效。

5.尽量使用覆盖索引（常用范围查找有：in/like/between/</>）

//没有使用覆盖索引
explain select * from staffs where name='July' and age>25 and pos='dev';
//使用了覆盖索引
explain select name,age,pos from staffs where name='July' and age=25 and pos='dev';
//虽然有范围查询。但是使用了覆盖索引
explain select name,age,pos from staffs where name='July' and age>25 and pos='dev';
//虽然中间兄弟断了。但是也使用了覆盖索引
explain select name,age,pos from staffs where name='July'  and pos='dev';

MySQL由哪两部分组成_mysql_42

总结：1和2执行计划对比，可以看第二个Extra使用Using where,Using index,这样表示用索引检索并查询。

          而3，4对比可以看出虽然第三个执行计划的第二个索引字段使用age>25的范围排序，最起码第一个索引是生效的。 这都是得益于select 索引字段，而不是select *

6.使用不等于(!= or <>) 无法使用索引

explain select * from staffs where name!='July';

MySQL由哪两部分组成_字段_43

可以看到type=ALL为全表扫描 

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

explain select * from staffs where name!='July'; explain select * from staffs where name is not null;

MySQL由哪两部分组成_MySQL由哪两部分组成_44

为了避免此类事情发生最好设置default值

8. like 以通配符开头"%abc" 会造成全表扫描

explain select * from staffs where name like 'July%';

explain select * from staffs where name like '%July';

MySQL由哪两部分组成_字段_45

  • 当然如果你执意像要通配符形式为%abc%形式让索引不失效也是可以的：
  • 创建表和数据，示例：
create table `tbl_user`(
`id` int(10) 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;

insert into tbl_user(name,age,email)values('1aa1',21,'aaa@163.com');
insert into tbl_user(name,age,email)values('2aa2',22222,'bbb@163.com');
insert into tbl_user(name,age,email)values('3aa3',33333,'ccc@163.com');
  • 创建覆盖索引：

create index idx_user_nameAge on tbl_user(name,age);

  • 可以看到给name,age创建覆盖索引
explain select name,age from tbl_user where name like '%aa%'

explain select age from tbl_user where name like '%aa%';

explain select name,age,email from tbl_user where name like '%aa%';

MySQL由哪两部分组成_MySQL由哪两部分组成_46

 但是如果是带有没有创建索引的字段查询时会发生什么呢？

MySQL由哪两部分组成_MySQL由哪两部分组成_47

 在没有覆盖索引的情况下，使用了 like并且开头不是常量的话，索引失效 这个email就是最终的罪魁祸首

总结：所以通过：select [覆盖索引or 主键索引 or 覆盖索引沾边的字段] from staffs where name like '%July%';可以不会让索引失效，通常type为index

9.字段不是整形不加单引号

  • 字符串不加单引号索引失效 name为varchar类型 ，必须使用单引号。 否则会失效。

explain select * from staffs where name='2000'; explain select * from staffs where name=2000;

MySQL由哪两部分组成_数据_48

小结：因为加了了单引号查询会mysql不会发生类型转换，而不加 mysql会默认的将数据转换成int类型。索引上面发生了类型转换，才是导致索引失效的最终问题所在 

10.少用or

  • 用它连接也会导致索引失效。

explain select * from staffs where name='2000' or name='July'; explain select * from staffs where name='2000';

MySQL由哪两部分组成_数据_49

Mysql索引一些常用案例

       创建表和一些数据：

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_text03_c1234 on test03(c1,c2,c3,c4);

MySQL由哪两部分组成_mysql_50

order by

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3; //用到c1,c2索引进行查找，c3也用到了索引只不过是用来排序，并c3没有统计到执行计划中，c4没有用到索引

MySQL由哪两部分组成_字段_51

explain select * from test03 where c1='a1' and c2='a2' order by c4; // c1,c2使用索引进行查找，但是中间跳过c3直接使用c4进行排序，导致sql内部使用filesort进行排序。

MySQL由哪两部分组成_MySQL由哪两部分组成_52

explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; // 因为c5没有创建索引，所以c5没有用到索引。而c1使用索引进行查找，c2,c3使用索引进行排序

 

MySQL由哪两部分组成_MySQL由哪两部分组成_53

 

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2; // 与上例不同Extra出现filesort ，c3,c2顺序颠倒，sql内部无法识别，所以sql内部使用filesort进行排序

MySQL由哪两部分组成_数据_54

explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
// c1,c2用到索引查询。c2,c3用来排序。

 


 


MySQL由哪两部分组成_字段_55


Mysql查询优化order by /exists/group by

  • exists

  

select ... from table where exists (subquery)// 该语法可以理解为： 将主查询的数据，放到子查询中做条件验证，根据验证结果(True or False),来决定主查询的数据结果是否得以保证。

1.exists(subquery)只返回True or False ,因此子查询中select * 也可以是select 1或是 select 'X' .官方说法是实际执行时会忽略select清单，因此没有区别。

2.exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题

3.exists子查询往往也可以用条件表达式，其他子查询或者 join来代替，何种最优需要具体问题具体分析。

 

 

order by 子句

  • order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序。
create table tblA(
id int primary key not null auto_increment,
age int,
birth timestamp not null
);

insert into tblA(age,birth)values(22,now());
insert into tblA(age,birth)values(23,now());
insert into tblA(age,birth)values(24,now());
...

// 创建索引
create index idx_A_ageBirth on tblA(age,birth);
  • 为了方便演示，先创建一个表并插入数据数据
  • 首先我们看几个order by执行计划

MySQL由哪两部分组成_数据_56

不会产生filesort

MySQL由哪两部分组成_数据_57

不会产生filesort,因为order by 排序是按照创建索引顺序排列的且覆盖全索引

MySQL由哪两部分组成_数据_58

# 会产生 原因是age 为范围排序，导致后面索引birth失效。最后使用filesort

MySQL由哪两部分组成_MySQL由哪两部分组成_59

会使用到filesort   order by 后面索引字段颠倒顺序

MySQL由哪两部分组成_mysql_60

order by 后面没有遵照最左前缀

MySQL由哪两部分组成_MySQL由哪两部分组成_61

会使用filesort，与上例一样。order by 后面没有遵照最左前缀

MySQL由哪两部分组成_mysql_62

不会使用filesort

MySQL由哪两部分组成_数据_63

会使用filesort,又升序又降序，导致MySQL无法理解
  • MySQL支持两种方式排序，FileSort和Index,当然Index效率高，它指MySQL扫描索引本身完成的排序，而FileSort方式效率比较低。
    由上面例子，不难看出规律， order by 满足一下2种情况，会使用Index方式排序：
order by 语句也遵循最左前缀原则
使用where子句与order by子句条件列组合满足索引最左前列
  • 索引我们有必要尽可能在索引列上完成排序操作，遵照索引建在最左前缀。如果不在索引列上，则会使用filesort，而filesort又有2种算法，mysql就要启动双路排序和单路排序、
  • 双路排序？单路排序？又是什么鬼？
1.双路排序：
	MySQL4.1之前使用双路排序，字面意思就是两次扫描硬盘，最终得到数据，
	读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，
	按照列表中的值重新从列表中读取对应的数据。

简单理解从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。这样从磁盘取出一批数据，要对磁盘进行2次扫描，众所周知，IO是很耗时的，所以再mysql4.1之后，出现第二种改进算法，就是单路排序。

2.单路排序：
	从磁盘读取查询需要的所有列，按照order by列再buffer对它们进行排序，
	然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，
	并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存
	在内存中。
  • 但是单路排序又存在着问题：
在sort_buffer中，单路操作要比双路操作占用更多空间，因为单路操作是把所有字段取出，
所以有可能取出的数据总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排序... 从而多次I/O操作

本想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
  • 那么如何解决或是如何优化？
1. SQL数据库配置文件优化：
	- 增大sort_buffer_size 参数的设置:不管哪种算法，提高这个参数都会提高效率，
	当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
	- 增大max_length_for_sort_data 参数的设置：提高这个参数，会增加用改进算法
	的概率，但是如果设置太高，数据总容量超出sort_buffer_size的概率就增大，明显
	症状是高的磁盘I/O活动和低的处理器使用率。
2.不要用 select *
	- 当Query 字段大小总和小于max_length_for_sort_data 而且排序字段不是TEXT或BLOB类型，会用改进后的算法-		--单路排序，否则用老算法---双路排序
	- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合	并排序，导致多次I/O，但		是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size
  • order by 小总结：
当我们索引  key a_b_c(a,b,c)
	Mysql两种排序方式：文件排序Using filesort/有序索引Using index

order by 能使用索引最左前缀情况：
	order by a
	order by a,b
	order by a,b,c
	order by a DESC,b DESC,c DESC
如果有where如果where使用索引的最左前缀定义为常量，则order by 能使用索引：
	where a=const order b,c
	where a=const and b=const order by c
	where a=const order by b,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 b,c  对于排序来说，多个相等条件也是范围查询

group by 分组

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

MySQL慢日志分析慢SQL

慢日志

  • MySQL的慢查询日志是MySQL提供一种日志记录，它用来记录MySQL中响应时间超过阈值的语句具体指运行时间超过long_query_time值的SQL，则会被记录到慢日志中
    具体指运行时间超过long_query_time值得SQL，则会被记录到慢查询日志中，long_query_time值默认为10，意思是运行10秒以上的语句。
  • 由他来查看哪些SQL超出了我们最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒sql，结合之前explain进行全面分析。
  • 默认情况下，MySQL没有开启慢日志，需要手动设置这个参数。当然如果不是调优需要，一般不建议启动参数，因为开启慢日志会或多或少带来一定性能影响，慢查询日志支持将日志记录写入文件。
  • 查看是否开启慢日志：默认情况下slow_query_log关闭的
show variables like "%slow_query_log%";

MySQL由哪两部分组成_字段_64

  • 开启慢日志：
1.终端开启：
	set global slow_query_log=1;
	
但是这样开启，只是只对当前数据库生效，重启数据库MySQL 则会失效

2.永久启动慢日志：
	修改配置文件：my.cnf
    [mysqld]下增加或修改参数：
    slow_query_log=1
    slow_query_log_file=/var/lib/mysql/{host_name}-slow.log
  • 查看慢日志记录的阈值：默认long_query_time =10秒
show variables like 'long_query_time%';

MySQL由哪两部分组成_MySQL由哪两部分组成_65

  • 修改阀值：
1.终端修改：
	# 更改阀值为3秒
	set global long_query_time=3;
需要注意的是，设置完慢sql的阀值，需要关闭窗口，重新进入窗口，输入下面命令查看是否成功设置：
	show global variables like "long_query_time";
2.永久配置慢日志阈值：
	slow_query_log=1;
    slow_query_log_file=路径
    long_query_time=3;
    log_output=FILE
  • 慢日志文件记录：
  • 先模拟一条查询的慢SQL语句：
select sleep(4);
  • 进入慢日志查看：
Query_time 执行时间
use 当前库
SET timestamp 当前时间戳
导致问题sql语句
  • 查看慢日志条数：
show global status like 'Slow_queries%';

MySQL由哪两部分组成_mysql_66

mysqldumpslow 日志分析工具

  • 在生产环境中，如果手工分析日志，查找，分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
  • 这里在lInux系统操作：我们先看看mysqldumpslow给我们提供了什么方法
[root@VM_0_2_centos ~]# mysqldumpslow --help
Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, ae, c, l, r, e, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
  • 常用方法：
s：表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
t: 即为返回前面多少条数据
g: 后边搭配一个正则匹配模式，大小写不敏感的
  • 案例：
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop-slow.log
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop-slow.log
# 另外建议在使用这些命令时结合 | 和 more  使用，否则有可能出现爆屏现象
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop-slow.log

MySQL用存储过程与函数批量插入数据

20.存储过程与函数的区别：

函数(FUNCTION)和存储过程(PROCEDURE),最大区别在于函数有返回值，存储过程没有返回值。

   批量创建数据案例：

  •  
// 创库
create database bigData;
use bigData;

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

// 建表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(20) 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;
  • 由于进行大批量数据插入，mysql会报一个错误。需要设置参数log_bin_trust_function_creators，它功效用于开启二进制模块，否则会报错：This function has none of DETERMINISTIC...
  • 查看log_bin_trust_function_creators是否开启：默认关闭
show variables like 'log_bin_trust_function_creators';

 

  • 开启
1.终端开启：
	set global log_bin_trust_function_creators=1;
# 这样添加参数以后，如果mysqld重启，上述参数会消失
2.永久方式开启：
	windows 下my.ini [mysqld]添加：log_bin_trust_function_creators=1
	linux下 /etc/my.cnf [mysqld]加上 log_bin_trust_function_creators=1
  • 创建函数，函数功能随机生成字符串。保证每条数据都不同：
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 
 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  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 $$
解释：
	DELIMITER $$
	# 默认DELIMITER 为 ';',但是我们在编辑我们创建函数使用';'' 会终端我们编辑函数，这样，我们通过 更给DELIMITER为$$，这样就得到解决。
	CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
	# 创建函数 rand_string 传入n为int类型， 返回值 为varchar(255)
	DECLARE 变量名称  类型  DEFAULT 默认值
	# while循环
	WHILE 条件 DO
	END WHILE;
	# FLOOR函数：
		FLOOR(RAND()*2)    # 表示0~2 随机生成一个数
     # SUBSTRING
     	SUBSTRING('HELLO WORLD',1,5) # 表示截取'HELLO WORLD' 1-5位也就是'HELLO'
     # CONCAT 用于拼接
     	SELECT CONCAT('NO.',2);
  • 函数：所及产生部门编号
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 函数名；
  • 创建存储过程，往emp表中插入数据的存储过程：
DELIMITER $$
CREATE PROCEDURE insert_emp(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 emp (empno,ename,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 $$
解释：
	# SET autocommit=0; 每提交一条数据就会在终端打印，造成爆屏，所以可以关闭autocommit,最后我们再commit
	# REPEAT ...UNTIL ... END REPEAT; 重复操作,知道UNTIL条件满足就END REPEAT
	# CURDATE 年月日
  • 创建存储过程：往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 ; 成默认
  • 调用存储过程插入10条数据 到dept
CALL insert_dept(100,10);
  • 调用存储过程插入500000条数据 到emp
CALL insert_emp(100001,500000);

show profile查看SQL执行生命周期

MySQL中show profile

  1. 正常情况下：收到爆栈，通过开启慢日志找到SQL，然后执行explain查看SQL，是可以解决大部分问题的。但是仍然找不到问题，可以通过show profile让检测的粒度更细化，比如在传输，网络连接，死锁等现象问题排查。如果再不行就需要配合DBA，进行数据库配置文件优化。
  2. show profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量。官网
  3. 分析步骤：
show variables like 'profiling';

MySQL由哪两部分组成_数据_67

  1. 查看当前MySQL版本是否支持：默认条件关闭状态，并保存最近15次运行结果。
  2. 开启功能，默认是关闭，使用前需要开启。
set profiling=on;
  1. 运行sql：
select * from emp group by id%10;
select * from emp group by id%20 order by 5;
...
  1. 通过show profiles查看执行sql时间的结果:

MySQL由哪两部分组成_MySQL由哪两部分组成_68

Query_ID 查询的ID
Duration  执行时间
Query     当前执行SQL语句

诊断SQL

show profile cpu,block io for query [Query_ID]
show profile cpu,block io for query 3

MySQL由哪两部分组成_MySQL由哪两部分组成_69

  1. 这里只列出了cpu和 block io 当然 诊断类型不止这些：
ALL 显示所有的开销信息
BLOCK IO 显示块IO相关开销
CONTEXT SWITCHES 上下文切换相关开销
CPU  显示CPU相关开销信息
IPC  显示发送和接收相关开销信息
MEMORY 显示内存相关开销信息
PAGE FAULTS 显示页面错误相关开销
SOURCE  显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS   显示交换次数相关开销的信息
  • 象用什么类型只需往后加就行，常用的cpu和block io
  1. 虽然show profile让我们粒度更细的去分析整个sql生命周期，那么如何区分哪条SQL有问题呢？
日常开发注意结论，出现以下4个就会出现很大问题：
	converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬
	Creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
	Copy to tmp table on disk  把内存中临时表赋值到磁盘，很危险
	locked   存在锁

查看id=19语句生命周期：它的时间很长

show profile cpu,block io for query 3;
  • 象用什么类型只需往后加就行，常用的cpu和block io
  1. 虽然show profile让我们粒度更细的去分析整个sql生命周期，那么如何区分哪条SQL有问题呢？
日常开发注意结论，出现以下4个就会出现很大问题：
	converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬
	Creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
	Copy to tmp table on disk  把内存中临时表赋值到磁盘，很危险
	locked   存在锁

查看id=19语句生命周期：它的时间很长

show profile cpu,block io for query 19;

MySQL由哪两部分组成_mysql_70

Creating tmp table 
Copy to tmp table
removing tmp table
当然会慢了

全局查询日志：

  • 只允许在测试环境上使用，永远不要在生产环境开启此功能
  • 启动
1.终端启动：
	set global general_log=1;
	set global log_output='TABLE';
	# 以后，你所编写的sql语句，将会记录到mysql库里的general_log表中，可以用下面命令查看
	select * from mysql.general_log;
2.配置文件启动：
	mysql的my.cnf 设置
	general_log=1
	general_log_file=/path/logfile # 记录日志文件的路径
	log_output=FILE  #输出格式

Mysql锁的机制

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

1.锁的分类

  •        数据中的锁可以分为读锁和写锁操作。      

 读锁(共享锁)： 针对同一份数据，多个读操作可以同时进行而互相不影响

写锁（排它锁）： 当前写锁没有完成前，它会阻断其他写锁和读锁

  • (表锁or行锁or页锁)

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

2.表锁--->偏向读

        2.1表锁之读锁

               

  • 数据表创建，并插入数据：
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');
insert into mylock(name) values('f');
insert into mylock(name) values('g');
  • 常见锁的命令：
# 手动增加表锁，给dept表上写锁，给mylock上读锁
mysql> lock table mylock read,dept write;
# 解锁
unlock tables;
//查看有锁的表  查看表是否带锁   In_use为0表示没有锁
show OPEN TABLES where In_use > 0;

MySQL由哪两部分组成_MySQL由哪两部分组成_71

案例：

当我们在终端1中把mylock表添加上读锁：
lock table mylock read

# 终端1只能读自己的表，读其他表会报错，终端1无法修改该表的字段。
# 终端2可以读mylock表也可以读其他表。终端2如果修改mylock表会造成阻塞

MySQL由哪两部分组成_MySQL由哪两部分组成_72

  2.2表锁之写锁

  • 终端1,添加mylock表写锁；

终端1,添加mylock表写锁；

mysql> lock table mylock write;
# 终端1可以查看mylock表，也可以更改mylock表，但不能查看其他表
# 终端2可以查看其他表，终端2查看mylock表会造成阻塞，更改mylock表也不行。

MySQL由哪两部分组成_mysql_73

总结：在myIsam的引擎下创建

 读锁：自己会话查询只能查询自己的表不能查询其他的表，其他表能查询，但是操作表数据就会发生会话阻塞

               写锁：自己会能修改自己的表，不能修改其他表，查询和操作表都会发生阻塞，直到会话将锁释放就可以操作表。

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

3.行锁

  • 行锁更偏向于Innodb存储引擎，开销、会出现死锁、锁的颗粒度最小、加锁慢、发生锁冲突的几率最低、并发度最高。
  • InoonDB与MyISAM最大不同有两点：一是支持事务(TRANSACTION),二是采用行级锁。
  • 事务及其ACID属性：原子性，一致性，隔离性，持久性

  3.1事务

    并发事务带来的问题：

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

                          脏读是事务B里面修改数据，幻读是事务B里面新增了数据。

MySQL由哪两部分组成_字段_74

查看事务隔离级别：
	show variables like 'tx_isolation';

 

     

MySQL由哪两部分组成_MySQL由哪两部分组成_75

   

 

3.2.行锁

  • 行锁案例分析：
# 创表
create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;
# 插入数据
insert into test_innodb_lock values(1,'a1');
insert into test_innodb_lock values(2,'b1');
insert into test_innodb_lock values(3,'c1');
insert into test_innodb_lock values(4,'d1');
insert into test_innodb_lock values(5,'e1');
insert into test_innodb_lock values(6,'f1');
insert into test_innodb_lock values(7,'g1');
#创建索引
create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_b_ind on test_innodb_lock(b);
  • 为了演示关闭自动提交,终端1和终端2执行下面语句：
set autocommit=0；
  • 终端1执行更新操作，然后查看数据，数据更新了：
mysql> update test_innodb_lock set b='4040' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | a1   |
|    2 | b1   |
|    3 | c1   |
|    4 | 4040 |
|    5 | e1   |
|    6 | f1   |
|    7 | g1   |
+------+------+
7 rows in set (0.00 sec)
  • 此时终端2查询没有更新：没有出现脏读。
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | a1   |
|    2 | b1   |
|    3 | c1   |
|    4 | d1   |
|    5 | e1   |
|    6 | f1   |
|    7 | g1   |
+------+------+
7 rows in set (0.00 sec)
  • 只有当终端1和终端2都执行commit命令提交事务，提交commit 解除阻塞，其他终端的更新操作才会正常进行。
然后终端2查询，数据就更新出来了
  • 当然终端1与终端2操作不同的行记录，不会互相影响。

3.3.索引失效行锁 导致表锁

  • a为int类型，b为varchar类型
# 终端1执行sql:b为varchar类型，但是条件是整型筛选，sql内部会做字符串转换
update test_innodb_lock set a=666 where b=4040;
# 终端2执行sql:
update test_innodb_lock set b='9999' where a=7;
# 当终端b执行update时候会造成阻塞，因为b为varchar类型，但是我们用整型筛选，导致索引失效，从而造成终端2阻塞

# 所以varchar类型必须要用单引号，否则会造成表级锁

 

3.4.间隙锁的危害

  • 当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在记录，叫做"(间隙GAP)"。
  • InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）
  • 危害：因为Query执行过程中通过范围查找的化，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命弱点，就是当锁定一个范围键值后，即使某些不存在的键值也会被无辜锁定。而造成在锁定时候无法插入锁定键值范围内任何数据，在某些场景下这可能对性能造成很大危害。
# 当前表如下：可以看到当前表没有a=2的数据
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | a1   |
|  666 | b1   |
|    3 | c1   |
|    4 | 4040 |
|    5 | e1   |
|    6 | f1   |
|    7 | g1   |
+------+------+
7 rows in set (0.00 sec)
mysql> commit;

# 当终端1去执行更新操作：更新1<a<6的数据行b为'0000'
update test_innodb_lock set b='0000' where a>1 and b<6;

# 当终端2去插入a=2的数据：就会造成阻塞，虽然数据表没有a=2数据，但是在没有commit之前，终端1一直占用着这块地
insert into test_innodb_lock values(2,'2222');

# 只有在终端1执行commit; 终端2才消除阻塞

3.5.给某一行上锁

  • select ... for update 锁定某一行后，其他操作会被阻塞，知道锁定的行进行会话提交commit;
begin;
select * from test_innodb_lock where a=2 for update;
commit;

3.6.InnoDB对比MyISAM：

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

3.7行锁分析

  • 通过检查InnoDB_row_lock
show status like 'innodb_row_lock%'

MySQL由哪两部分组成_mysql_76

  • 对于状态量说明：
Innodb_row_lock_current_waits：当前正在等待锁定的数量
*Innodb_row_lock_time：从系统启动到现在锁定总时长
*Innodb_row_lock_time_avg:每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花费时间
*Innodb_row_lock_waits：系统启动后到现在总共等待的次数

# 带 * 比较重要

8.行锁优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量空设置事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

4.页锁

  • 开销和加锁时间接语表锁和行锁之间，会出现死锁，锁定粒度接语表锁和行锁之间，并发一般。

MySQL主从复制配置

MySQL主从复制

  • slave 会从master读取binlog来进行数据同步
  • 原理：
1.master将改变记录到二进制日志(binary log),这些记录过程角坐二进制日志事件（binary log events）;
2.slave将master的binary log events拷贝到它的中继日志（relay log）
3.slave重做中继日志事件，将改变应用到自己数据库中，MySQL复制时异步的且串行化的
  • 复制基本原理：
每个slave只有一个master
每个slave只能由一个唯一的服务器ID
每个master 可以有多个slave
  • 复制的最大问题是网络上有延迟

一主一从常见配置：

  • 这里演示主机windows ，从机linux
1.mysql版本一致且后台以服务运行
2.同一网段可以ping通（双向都能ping通）
3.修改主机my.ini配置 windows，配置在[mysqld]
    # 主服务器唯一 必须填写
    server-id=1	
    #启动二进制日志 必须填写
    log-bin=自己本地路径/mysqlbin 
    # 启动错误日志 可选
    log-err=自己本地路径/mysqlerr
    # 根目录  可选
    basedir=自己本地路径
    basedir="D:/devSoft/MySQLServer5.5/"
    # 临时目录 可选
    tmpdir="自己本地路径"
    tmpdir="D:/devSoft/MySQLServer5.5"
    # 数据目录 可选
    datadir="D:/devSoft/MySQLServer5.5/Data"
    # 主机，读写都可以
    read-only=0
    # 设置忽略复制的数据库
    binlog-ignore-db=mysql
    # 设置需要复制数据库
    binlog-do-db=数据库名
4.从机Linux上配置需要配置my.conf
	# 从服务器id 必选
	server-id=从机id
5.因修改过配置文件，主机+从机都重启后台mysql服务
	servive mysql stop
	service mysql start
6.主机和从机都要关闭防火墙：
	windows手动关闭
	linux:service iptables stop
7.在windows主机上建立账户并授权slave
	mysql > GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机数据库IP' IDENTIFIED BY '123456';
	#GRANT 授权，REPLICATION拷贝，授权给用户zhangsan的从机IP有复制权限
	flush privileges; # 刷新授权配置
	show master status; # 查询master状态
	#File binlog日志文件名
	#Position 磁道位置
	#Binlog_Do_DB 要求复制库的名字
	#Binlog_Ignore_DB 忽略复制的数据库
	#假如：File=mysqlbin.000035 ,Position=341表示从文件mysqlbin.000035的341行开始插入数据。
	# 记录File 和 Position
8.在linux从机上配置需要复制的主机
	# 从机执行sql命令
	CHANGE MASTER TO MASTER_HOST='主机id',
	MASTER_USER='主机名字',
	MASTER_PASSWORD='主机密码',
	MASTER_LOG_FILE='windows记录的File',
	MASTER_LOG_POS='windows记录的Position';
9.启动从服务器复制功能：
	mysql > start slave;
	mysql > show slave status;  #查看从服务器状态
		# Slave_IO_Running:Yes
		# SLAVE_SQL_Running:Yes
		# 这2个值为yes表示，主从复制配置打通了
10.停掉从机：
	stop slave；

如果两台mysql都在linux上也是可以按照这个步骤

在/etc的目录下没有my.con文件就到/usr/share/mysql/my-medium.cnf 复制并且改名为my.con就可以改配置主从

查看一个表是否包含一个字段的语句

SELECT
	column_name, TABLE_NAME
FROM
	 information_schema.COLUMNS   
WHERE
	column_name = 'provinceId'
and  table_schema = 'zsb'