mysql高级

发表于 2021-08-30 更新于 2021-09-29 分类于 mysql ， mysql高级

[TOC]

Mysql 高级篇

1、索引（Index）

1、索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

如下面的==示意图==所示：

2、索引优势劣势

索引的优势：

类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引的劣势：

实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3、索引结构

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

MySQL目前提供了以下4种索引：

BTREE 索引 ：最常见的索引类型，大部分索引都支持 B 树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。
其中==聚集索引==、==次要索引==、==覆盖索引==、==复合索引==、==前缀索引==、==唯一索引==默认都是使用 B+tree 索引，统称为索引。

注意：关于 InnoDB引擎支不支持 HASH 索引问题

InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引

InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引，这一层上说，InnoDB又是支持哈希索引的。

1、InnoDB的自调优

那什么是自适应哈希索引(Adaptive Hash Index, AHI)呢?原理又是怎样的呢? 咱们先从一个例子开始。

不妨设有InnoDB数据表：t(id PK, name KEY, sex, flag)

id是主键，name建了普通索引。

假设表中有四条记录：

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

如上图，通过前序知识，容易知道InnoDB在主键id上会建立聚集索引(Clustered Index)，叶子存储记录本身，在name上会建立普通索引(Secondary Index)，叶子存储主键值。

发起主键id查询时，能够通过聚集索引，直接定位到行记录。

1	select * from t where name='ls';

发起普通索引查询时：

会先从普通索引查询出主键(上图右边);
再由主键，从聚集索引上二次遍历定位到记录(上图左边)。

不管聚集索引还是普通索引，记录定位的寻路路径(Search Path)都很长。

在MySQL运行的过程中，如果InnoDB发现，有很多SQL存在这类很长的寻路，并且有很多SQL会命中相同的页面(page)，InnoDB会在自己的内存缓冲区(Buffer)里，开辟一块区域，建立自适应哈希所有AHI，以加速查询。

从这个层面上来说，InnoDB的自使用哈希索引，更像“索引的索引”，毕竟其目的是为了加速索引寻路。

既然是哈希，key是什么，value是什么?

==key是索引键值(或者键值前缀)。==
==value是索引记录页面位置。==

为啥叫“自适应(adaptive)”哈希索引?

系统自己判断“应该可以加速查询”而建立的，不需要用户手动建立，故称“自适应”。

系统会不会判断失误，是不是一定能加速?

不是一定能加速，有时候会误判。当业务场景为下面几种情况时：

很多单行记录查询(例如passport，用户中心等业务)
索引范围查询(此时AHI可以快速定位首行记录)
所有记录内存能放得下

AHI往往是有效的。

任何脱离业务的技术方案，都是耍流氓。

当业务有大量like或者join，AHI的维护反而可能成为负担，降低系统效率，此时可以手动关闭AHI功能。

2、BTREE（B树）结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

树中每个节点最多包含m个孩子。
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点，则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变过程如下：

插入前4个字母 C N G A
插入H，n>4，中间元素G字母向上分裂到新的节点
插入E，K，Q不需要分裂
插入M，中间元素M字母向上分裂到父节点G
插入F，W，L，T不需要分裂
插入Z，中间元素T向上分裂到父节点中
插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂
最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了。

BTREE树和二叉树相比：

查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

3、B+TREE（B+树）结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
所有的非叶子节点都可以看作是key的索引部分。

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

4、MySQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化：在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图：

5、聚簇索引与非聚簇索引（了解）

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

何时使用聚簇索引与非聚簇索引：

聚簇索引具有唯一性

由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引

一个误区：把主键自动设为聚簇索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段，那样的话Clustered index对效率的帮助，完全被浪费了。

刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。

记住我们的最终目的就是在相同结果集情况下，尽可能减少逻辑IO。

结合图再仔细点看

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的优势：

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
辅助索引使用主键作为”指针”而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个”指针”**。也就是说行的位置（实现中通过16K的Page来定位）会随着**数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
聚簇索引适合用在排序的场合，非聚簇索引不适合
取出一定范围数据的时候，使用用聚簇索引
二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势：

维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，

所以建议使用int的auto_increment作为主键

主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）

如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

为什么主键通常建议使用自增id：

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

mysql中聚簇索引的设定：

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

6、full-text全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询：

1	SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

全文索引用match+against方式查询：

1	SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

明显的提高查询效率。

限制：

mysql5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。
5.7以后官方支持中文分词。
随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr,elasticSearch等专门的搜索引擎所替代。

7、Hash索引

Hash索引只有Memory，NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。
NoSql采用此索引结构。

8、R-Tree索引

R-Tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
相对于b-tree，r-tree的优势在于==范围查找==。

4、索引分类

单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引 ：索引列的值必须唯一，但允许有空值
复合索引 ：即一个索引包含多个列
主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
- 随表一起建索引：
  1
  2
  3
  CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id)
  );
  使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。
- 单独建主键索引：
  1
  ALTER TABLE customer add PRIMARY KEY customer(customer_no);
- 删除建主键索引：
  1
  ALTER TABLE customer drop PRIMARY KEY ;
- 修改建主键索引：必须先删除掉(drop)原索引，再新建(add)索引

5、索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

准备环境：

-- 创建数据库
create database demo_01 default charset=utf8mb4;

use demo_01;

-- 创建city表
CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建country表
CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

-- 插入数据
insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

1、创建索引

语法：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

示例：为city表中的city_name字段创建索引；

2、查看索引

语法：

1	show index from table_name;

示例：查看city表中的索引信息（其中加上\G可以将查询到的数据以row的方式展示，方便查看）

3、删除索引

语法：

1	DROP INDEX index_name ON tbl_name;

示例：想要删除city表上的索引idx_city_name，可以操作如下：

4、ALTER命令（添加索引）

-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table tb_name add primary key(column_list);

-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
alter table tb_name add unique index_name(column_list);

-- 添加普通索引， 索引值可以出现多次。
alter table tb_name add index index_name(column_list);

-- 该语句指定了索引为FULLTEXT， 用于全文索引
alter table tb_name add fulltext index_name(column_list);

6、索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

主键自动建立唯一索引
对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高。
查询中与其它表关联的字段，外键关系建立索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- group by 和 order by 后面的字段有索引大大提高效率
查询中统计或者分组字段（分组包含着排序，因为在分组之前会先进行排序（当然也可以设置不排序））
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
在高并发下倾向创建组合索引

利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

-- 创建复合索引
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

  - 就相当于：

    - 对name 创建索引 ;
    - 对name , email 创建了索引 ;
    - 对name , email, status 创建了索引 ;

**哪些情况不要创建索引：**

- 表记录太少（没必要）
- 经常增删改的表
  - 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
  - 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
- Where条件里用不到的字段不创建索引，索引建多了影响 增删改 的效率
- 数据重复且分布平均的表字段，因此应该**只为最经常查询和最经常排序的数据列建立索引**。
  - **注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。**
    - 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每一个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的程序速度
    - 索引的选择性是指索引列中不同值的数目与表中记录数的比。即：如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000 = 0.99。**一个索引的选择性越接近于1，这个索引的效率就越高。**

-----





### 2、视图（View）

#### 1、视图概述

**视图（View）是一种虚拟存在的表**。**视图并不在数据库中实际存在，行和列数据来自定义视图的==查询==中使用的表，并且是在使用视图时动态生成的。**

通俗的讲，**视图就是一条SELECT语句执行后返回的结果集**。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项：

- **简单**：**使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件**，对用户来说已经是过滤好的复合条件的结果集。
- **安全**：**使用视图的用户只能访问他们被允许查询的结果集**，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
- **数据独立**：一旦视图的结构确定了，可以**屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响**。



#### 2、创建或者修改视图

创建视图的语法为：

```sql
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

其中：选项WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。

LOCAL ：只要满足本视图的条件就可以更新。
CASCADED ：必须满足所有针对该视图的所有视图的条件才可以更新。 默认值

示例：创建city_country_view视图，执行如下SQL：

1
2
3

create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

由于视图是一种虚拟的表，所以可以使用操作表的SQL语句对视图进行查询：

查询视图：

当然，由于视图是一种虚拟的表，所以也可以对视图进行修改操作：

1	update city_country_view set city_name = 'GuangDong' where city_id = 1;

此时，修改操作不仅会修改视图上的表的数据，而且也会同步修改底层的表中的数据。

注意：一般不要在视图上进行修改，视图是用来简化我们的==查询==操作，方便我们进行数据查询的。

3、查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看：

4、删除视图

语法：

1	DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT \| CASCADE]

示例，删除视图city_country_view ：

1	DROP VIEW city_country_view ;

3、存储过程（Procedure）和函数（Function）

1、存储过程和函数概述

存储过程和函数是：事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有：

函数（function）：是一个有返回值的过程；
过程（procedure）：是一个没有返回值的函数；

其实存储过程与存储函数的作用并没有太大的区别：

存储函数可以获取返回值
存储过程可以通过OUT也能获取返回值

2、存储过程

1、创建存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
	-- SQL语句
end ;

示例：

delimiter $

create procedure pro_test1()
begin
	select 'Hello Mysql' ;
end$

delimiter ;

知识小贴士

DELIMITER

该关键字用来声明SQL语句的分隔符，告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号;。
在命令行客户端中，如果有一行命令以;结束，那么回车后，mysql将会执行该命令。
如果要创建存储过程的话，在定义里面的sql语句时，使用;会让mysql执行命令，而此时的命令是不完全的，会报错。此时使用DELIMITER将分隔符修改为其他符号，等到创建存储过程之后，在将分隔符改回;就行。

2、调用存储过程

1	call procedure_name() ;

3、查看存储过程

-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';

-- 查询存储过程的状态信息
show procedure status;

-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;

4、删除存储过程

1	DROP PROCEDURE [IF EXISTS] sp_name;

5、在存储过程的sql当中的语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ，来完成比较复杂的功能。

1、变量

DECLARE

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

DECLARE var_name[,...] type [DEFAULT value]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

- 示例：

- ```sql
  delimiter $
  
   create procedure pro_test2() 
   begin 
   	declare num int default 5;
   	select num+ 10; 
   	-- concat('xxx','ooo') 把里面的东西连接成一个字符串
   	-- select concat('num的值为:',num);
   end$
  
   delimiter;

SET

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [, var_name = expr] ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14

- 示例：

- ```sql
    DELIMITER $
    
    CREATE  PROCEDURE pro_test3()
    BEGIN
    	DECLARE NAME VARCHAR(20);
    	SET NAME = 'MYSQL';
    	SELECT NAME ;
    END$
    
    DELIMITER ;

也可以通过select … into 方式进行赋值操作：

DELIMITER $

CREATE  PROCEDURE pro_test5()
BEGIN
    declare  countnum int;
    select count(*) into countnum from city;
    select concat('city表当中的记录数为:',num);
END$

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13

###### 2、if条件判断

语法结构：

```sql
if search_condition then statement_list

	[elseif search_condition then statement_list] ...
	
	[else statement_list]
	
end if;

需求：根据定义的身高变量，判定当前身高的所属的身材类型

180 及以上：身材高挑
170 - 180：标准身材
170 以下：一般身材

示例：

delimiter $

create procedure pro_test6()
begin
  declare  height  int  default  175; 
  declare  description  varchar(50);
  
  if  height >= 180  then
    set description = '身材高挑';
  elseif height >= 170 and height < 180  then
    set description = '标准身材';
  else
    set description = '一般身材';
  end if;
  
  select description ;
  -- select concat('身高:',height,'对应的身材类型:',description)
end$

delimiter ;

调用结果为：

3、传递参数

语法格式：

1	create procedure procedure_name([in/out/inout] 参数名参数类型)

IN ：该参数可以作为输入，也就是需要调用方传入值，默认
OUT：该参数作为输出，也就是该参数可以作为返回值
INOUT：既可以作为输入参数，也可以作为输出参数

IN - 输入

需求：根据定义的身高变量，判定当前身高的所属的身材类型

示例：

delimiter $

create procedure pro_test5(in height int)
begin
    declare description varchar(50) default '';
  if height >= 180 then
    set description='身材高挑';
  elseif height >= 170 and height < 180 then
    set description='标准身材';
  else
    set description='一般身材';
  end if;
  select concat('身高 ', height , '对应的身材类型为:',description);
end$

delimiter ;

OUT-输出

需求：根据传入的身高变量，获取当前身高的所属的身材类型

示例：

create procedure pro_test5(in height int , out description varchar(100))
begin
  if height >= 180 then
    set description='身材高挑';
  elseif height >= 170 and height < 180 then
    set description='标准身材';
  else
    set description='一般身材';
  end if;
end$

调用：

1
2
3

call pro_test5(168, @description)$

select @description$

小知识

@description：这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size：这种在变量前加上 “@@” 符号，叫做 系统变量

4、case结构

语法结构：

-- 方式一
CASE case_value

  WHEN when_value THEN statement_list
  
  [WHEN when_value THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;


-- 方式二

CASE

  WHEN search_condition THEN statement_list
  
  [WHEN search_condition THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

需求：给定一个月份，然后计算出所在的季度

示例：

delimiter $

create procedure pro_test9(month int)
begin
  declare result varchar(20);
  case 
    when month >= 1 and month <=3 then 
      set result = '第一季度';
    when month >= 4 and month <=6 then 
      set result = '第二季度';
    when month >= 7 and month <=9 then 
      set result = '第三季度';
    when month >= 10 and month <=12 then 
      set result = '第四季度';
  end case;
  
  select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
  
end$

delimiter ;

5、while循环

语法结构：

while search_condition do

	statement_list
	
end while;

需求：计算从1加到n的值

示例：

delimiter $

create procedure pro_test8(n int)
begin
  declare total int default 0;
  declare num int default 1;
  while num<=n do
    set total = total + num;
	set num = num + 1;
  end while;
  select total;
end$

delimiter ;

6、repeat结构

有条件的循环控制语句，当满足条件的时候退出循环。

while 是满足条件才执行，repeat 是满足条件就退出循环。

语法结构：

REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

需求：计算从1加到n的值

示例：

delimiter $

create procedure pro_test10(n int)
begin
  declare total int default 0;
  
  repeat 
    set total = total + n;
    set n = n - 1;
    until n=0  
  end repeat;
  
  select total ;
  
end$


delimiter ;

注意：until后面的语句不要加上 ; ，否则语法报错。

7、loop语句

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

[begin_label:] LOOP

  statement_list

END LOOP [end_label]

如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

8、leave语句

用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简单例子，退出循环：

delimiter $

CREATE PROCEDURE pro_test11(n int)
BEGIN
  declare total int default 0;
  
  ins: LOOP
    
    IF n <= 0 then
      leave ins;
    END IF;
    
    set total = total + n;
    set n = n - 1;
  	
  END LOOP ins;
  
  select total;
END$

delimiter ;

9、游标/光标（Cursor）

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。

光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下：

声明光标：

1	DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 光标：

1	OPEN cursor_name ;

FETCH 光标：（每fetch一次，指针往下走一个）

1	FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光标：

1	CLOSE cursor_name ;

示例：

初始化脚本：

create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment '姓名',
  age int(11) comment '年龄',
  salary int(11) comment '薪水',
  primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

查询emp表中数据，并逐行获取进行展示

create procedure pro_test11()
begin
  declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
  declare emp_result cursor for select * from emp;
  
  open emp_result;
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  close emp_result;
end$

结果：前四条数据被成功fetch出来展示，最后一次fetch由于表中已经没有数据，所以会报错：

1	ERROR 1329 (020000): No data - zero rows fetched ,selected, or processed

通过循环结构，获取游标中的数据：

DELIMITER $

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  DECLARE has_data int default 1;
  
  DECLARE emp_result CURSOR FOR select * from emp;
  -- 这里的条件声明必须放在游标声明之后，否则报错
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
  
  open emp_result;
  
  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
    until has_data = 0
  end repeat;
  
  close emp_result;
end$

DELIMITER ;

注意：这里的条件声明必须放在游标声明之后，否则报错

3、存储函数

语法结构：

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
	-- SQL语句
END;

案例：定义一个存储函数，请求满足条件的总记录数

delimiter $

create function count_city(countryId int)
returns int
begin
  declare cnum int ;
  
  select count(*) into cnum from city where country_id = countryId;
  
  return cnum;
end$

delimiter ;

调用：

1
2
3

select count_city(1);

select count_city(2);

4、触发器（Trigger）

1、介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

mysql触发器还只支持行级触发器，不支持语句级触发器。
Oracle数据库既支持行级触发器，又支持语句级触发器。

触发器类型	NEW 和 OLD 的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

2、创建触发器

语法结构：

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器

begin

	trigger_stmt ;

end;

示例：

需求：通过触发器记录 emp 表的数据变更日志，包含增加、修改、删除

首先创建一张日志表：

create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

创建 insert 型触发器，完成插入数据时的日志记录：

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
end $

DELIMITER ;

创建 update 型触发器，完成更新数据时的日志记录：

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

创建delete 行的触发器 , 完成删除数据时的日志记录：

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);

update emp set age = 39 where id = 3;

delete from emp where id = 5;

select * from emp_logs;

3、删除触发器

语法结构：

1	drop trigger [schema_name.]trigger_name

如果没有指定 schema_name，默认为当前数据库。

4、查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

语法结构：

1	show triggers；

5、Mysql的体系结构概览

整个MySQL Server由以下组成

Connection Pool：连接池组件
Management Services & Utilities：管理服务和工具组件
SQL Interface：SQL接口组件
Parser：查询分析器组件
Optimizer：优化器组件
Caches & Buffers：缓冲池组件
Pluggable Storage Engines：存储引擎
File System：文件系统

整个MySQL Server 从上往下可以分为以下四层：

连接层
- 最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。
- 主要完成一些类似于连接处理、授权认证、及相关的安全方案。
- 在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。
- 同样在该层上可以实现基于SSL的安全链接。
- 服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
- 第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。
- 所有跨存储引擎的功能也在这一层实现，如过程、函数等。
- 在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。
- 如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层
- 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。
- 不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
存储层
- 数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

查询流程图：

首先，mysql的查询流程大致是：

mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中(一模一样的sql才能命中)，直接返回结果，否则进行语句解析。也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
然后，mysql默认使用的BTREE索引，并且一个大致方向是：无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

6、存储引擎

1、存储引擎概述

和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。
存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。所以数据库的每一张表都可以使用不同的存储引擎。
Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过指定 show engines ，来查询当前数据库支持的存储引擎：

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

查看Mysql数据库默认的存储引擎，指令：

1	show variables like '%storage_engine%';

2、各种存储引擎特性

下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别，如下表所示：

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	==支持==
锁机制	==行锁(适合高并发)==	==表锁==	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	==支持==（唯一支持外键的存储引擎）

下面我们将重点介绍最长使用的两种存储引擎： ==InnoDB==、==MyISAM== ，另外两种 MEMORY、MERGE ，了解即可。

1、InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点：

事务控制

create table goods_innodb(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

- ```sql
  start transaction;
  
  insert into goods_innodb(id,name)values(null,'Meta20');
  
  commit;

测试，发现在InnoDB中是存在事务的

外键约束

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。

下面两张表中， country_innodb是父表， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id：

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括：
- RESTRICT
- CASCADE
- SET NULL
- NO ACTION
RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新；
- 如 DELETE RESTRICT ：表示在删除父表当中数据的时候，如果该数据有外键关联着子表的数据的话，则删除失败。
CASCADE 表示父表在更新或者删除时，更新或者删除子表对应的记录；
- 如 UPDATE CASCADE ：表示在更新父表数据的时候，如果该数据有外键关联着子表的数据的话，则子表的数据也会跟着一起更新。
SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。
- 如 DELETE SET NULL ：表示在删除数据的时候，如果该数据有外键关联着子表的数据的话，则子表对应的数据会被设置为null
针对上面创建的两个表，子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的：
- ON DELETE RESTRICT：那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，
- ON UPDATE CASCADE：主表在更新记录的时候，如果子表有对应记录，则子表对应更新 。
表中数据如下图所示：
外键信息可以使用如下两种方式查看：
1
show create table city_innodb ;
删除country_id为1 的country数据：(在主表删除记录的时候，如果子表有对应记录，则不允许删除)
1
delete from country_innodb where country_id = 1;

更新主表country表的字段 country_id：

1	update country_innodb set country_id = 100 where country_id = 1;

1556087759615

更新后，子表的数据信息为：(主表在更新记录的时候，如果子表有对应记录，则子表对应更新)

存储方式
- 在Linux环境下，数据库表的数据信息默认存储在 var/lib/mysql 下
- InnoDB 存储表和索引有以下两种方式：
  1. 使用共享表空间存储，这种方式创建的表的表结构保存在==.frm文件==中，数据和索引保存在 ==innodb_data_home_dir== 和 ==innodb_data_file_path== 定义的表空间中，可以是多个文件。
  2. 使用多表空间存储，这种方式创建的表的表结构仍然存在 ==.frm 文件==中，但是每个表的数据和索引单独保存在 ==.ibd== 中。

2、MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。

MyISAM有以下两个比较重要的特点：

不支持事务
1
2
3
4
5
create table goods_myisam(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
- 通过测试，我们发现，就算mysql显示的好像开启了事务，但是在MyISAM存储引擎中，是没有事务控制的，因此就算 start transaction 之后，执行sql在commit之前还是可以执行到mysql数据库当中的。
文件存储方式
- 每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：
  - .frm (存储表定义)
  - .MYD(MYData , 存储数据)
  - .MYI(MYIndex , 存储索引)

3、MEMORY

Memory存储引擎将表的数据存放在内存中。
每个MEMORY表实际对应一个磁盘文件，格式是==.frm== ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。
优点：MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引
缺点：
- 但是对于内存来说，存储空间是很宝贵的，因此MEMORY 类型的表存储的数据量不能很大；
- 而且服务一旦关闭，表中的数据就会丢失。

4、MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。类似于视图（View）

对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值：

使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上
不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

下面是一个创建和使用MERGE表的示例：

创建3个测试表 order_1990，order_1991，order_all，其中order_all是前两个表的MERGE表：

create table order_1990(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;

create table order_1991(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;

create table order_all(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;

分别向两张表中插入记录

insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');

insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');

查询3张表中的数据：
- order_1990中的数据：
- order_1991中的数据：
- order_all中的数据：
往order_all中插入一条记录，由于在MERGE表定义时，INSERT_METHOD 选择的是LAST，那么插入的数据会想最后一张表中插入。
1
insert into order_all values(100,10000.0,'西安')；

3、存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

以下是几种常用的存储引擎的使用环境：

InnoDB：是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

7、优化SQL步骤

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，这里将详细介绍在 MySQL 中优化 SQL 语句的方法。

当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

1、查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。

1	show [session\|global] status;

show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中整个数据库所有统计参数的值：

1 2	-- 七个下划线 show status like 'Com_______';

下面的命令显示了当前Innodb存储引擎的所有统计参数的值：

1	show status like 'Innodb_rows_%';

以上sql语句查询的是当前连接的相关的状态信息，如果想要查看全局的状态信息，即整一个数据库的状态信息，需要在show与status之间加入 global

1
2
3

show global status like 'Com_______';

show global status like 'Innodb_rows_%';

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	含义
==Com_select==	==执行 select 操作的次数，一次查询只累加 1。==
==Com_insert==	==执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。==
==Com_update==	==执行 UPDATE 操作的次数。==
==Com_delete==	==执行 DELETE 操作的次数。==
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

Com_***：这些参数对于==所有存储引擎的表操作==都会进行累计。
Innodb_***：这几个参数==只是针对InnoDB 存储引擎==的，累加的算法也略有不同。

2、定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句：

慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看日志管理的相关部分。
show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

其中几个表头的相关信息：

id：用户登录mysql时，系统分配的”connection_id”**，可以使用函数connection_id()查看**
user：显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host：显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db：显示这个进程目前连接的是哪个数据库
command：显示当前连接的执行的命令，一般取值为
- 休眠（sleep）
- 查询（query）
- 连接（connect）等
time：显示这个状态持续的时间，单位是秒
state：显示使用当前连接的sql语句的状态，很重要的列。
- state描述的是语句执行中的某一个状态。
- 一个sql语句，以查询为例，可能需要经过：
  1. copying to tmp table
  2. sorting result
  3. sending data等状态才可以完成
info：显示这个sql语句，是判断问题语句的一个重要依据

3、explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划：

1	explain select * from tb_item where id = 1;

1	explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白联通3G手机3';

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。（与表结构的执行顺序有关）
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const —–> eq_ref ——> ref ——-> ref_or_null—-> index_merge —> index_subquery —–> range —–> index ——> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

现在对以上字段进行相关说明：

1、环境准备

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');


INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');


INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

2、explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id 情况有三种：

id 相同表示加载表的顺序是从上到下。
1
2
-- 一次性查询多张表
explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;
此例中先执行where 后的第一条语句 r.id = ur.role_id 通过 r.id 关联 ur.role_id 。而 ur.role_id 的结果建立在 u.id = ur.user_id 的基础之上。

id 不同id值越大，优先级越高，越先被执行。

1 2	-- 采用子查询的方式 EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

1556103009534

id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

1 2	-- 既查询了多张表，又进行了子查询 EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

1556103294182

3、explain 之 select_type

表示 SELECT 的类型，有哪些：

常见的取值，如下表所示：

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

1、select_type 之 SIMPLE

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

2、select_type 之 PRIMARY

PRIMARY：查询中若包含任何复杂的子查询，最外层查询标记为该标识

3、select_type 之 SUBQUERY

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

1
2
3

-- t_user表是查询的最外层的表，所以t_user表是PRIMARY
-- user_role表是在WHERE 语句当中的子查询当中出现的表，所以user_role表是SUBQUERY
explain select * from t_user where id = (select id from user_role where role_id = '9') ;

4、select_type 之 DERIVED

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

1
2
3

-- t_user表是在FROM 列表中包含的子查询当中查询的表，所以t_user表是DERIVED，而生成的临时表就是<derived2>(里面的2表示产生这个临时表的DERIVED的id)
-- 结果从临时表<derived2>当中获取，即临时表<derived2>是最外层的表，因此是PRIMARY
explain select a.* from (select * from t_user where id in ('1', '2')) a;

5、select_type 之 UNION

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

6、select_type 之 UNION RESULT

UNION RESULT：从UNION表获取结果的SELECT

-- t_user表是查询的最外层的表，所以t_user表是PRIMARY
-- 在union之后也查询了t_user表，所以在id=2的t_user表是UNION
-- <union1,2>表是连接了id=1与id=2的两张t_user表的结果表，标记为UNION RESULT
explain select * from t_user where id = '1' union select * from t_user where id = '2'

7、其他不常见的类型：select_type 之 DEPENDENT SUBQUERY

DEPENDENT SUBQUERY：在SELECT或WHERE列表中包含了子查询，子查询基于外层

dependent subquery 与 subquery 的区别：

dependent subquery（依赖子查询）： 子查询结果为多值
subquery （子查询）：查询结果为单值

8、其他不常见的类型：select_type 之 UNCACHEABLE SUBQUREY

UNCACHEABLE SUBQUREY：无法被缓存的子查询

@@ 表示查的环境参数。没办法缓存

4、explain 之 table

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

5、explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

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


system > const > eq_ref > ref > range > index > ALL

==一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。==

1、type 之 NULL

NULL：MySQL不访问任何表，索引，直接返回结果

2、type 之 system

system：表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现

3、type 之 const

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

通过比较primary key

通过比较unique 索引

4、type 之 eq_ref

eq_ref：类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描

5、type 之 ref

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）

6、type 之 range

range：只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。

7、type 之 index

index：index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。

查询的是id主键，mysql的主键默认有着主键索引：

8、type 之 all

all：将遍历全表以找到匹配的行

9、补充：type 之 index_merge

index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

10、补充：type 之 ref_or_null

ref_or_null：对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

11、补充：type 之 index_subquery

index_subquery：利用索引来关联子查询，不再全表扫描。

12、补充：type 之 unique_subquery

unique_subquery：该联接类型类似于index_subquery。子查询中的唯一索引

6、explain 之 key

possible_keys：显示==可能==应用在这张表的索引，一个或多个。
key：==实际使用==的索引，如果为NULL，则没有使用索引。
key_len：表示==索引中使用的字节数==，该值为索引字段最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
- 在不损失精确性的前提下，长度越短越好，越短执行效率越高。

1、key_len的长度如何计算

1	EXPLAIN SELECT * FROM emp WHERE emp.deptno=109 AND emp.`ename`='AvDEjl'

如何计算

总结一下：char(30) utf8 –> key_len = 30*3 +1 表示:

utf8 格式需要 *3 (跟数据类型有关)
允许为 NULL +1 ，不允许 +0
动态类型 +2 (动态类型包括 : varchar , detail text() 截取字符窜)

第一组：key_len = deptno(int) + null + ename(varchar(20) * 3 + 动态) = 4 + 1+ 20 * 3 + 2= 67
第二组：key_len = deptno(int) + null = 4 + 1 = 5

7、explain 之 rows

rows列显示MySQL认为它执行查询时必须检查的行数。 越少越好

8、explain 之 extra

其他的额外的执行计划信息，在该列展示。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。
Using where	表明使用了where过滤
using join buffer	使用了连接缓存：
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
distinct	优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

如果出现的是前面两个，就需要考虑优化了，因为前面那两个是非常耗费性能的。如果出现的是最后一个，则需要保持，因为使用到了索引，性能较高。

1、extra 之 using filesort（重要）

using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为 “文件排序”；效率低。

2、extra 之 using temporary（重要）

using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低

3、extra 之 using index（重要）

using index：表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。

如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

**覆盖索引(Covering Index)**：

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。

==一个索引==
==包含了(或覆盖了)[select子句]与查询条件[Where子句]中==
==所有需要的字段就叫做覆盖索引==。

上句理解：

1	select id , name from t_xxx where age=18;

有一个组合索引 idx_id_name_age_xxx 包含了(覆盖了)，id,name,age三个字段。查询时直接将建立了索引的列读取出来了，而不需要去查找所在行的其他数据。所以很高效。

(个人认为：在数据量较大，固定字段查询情况多时可以使用这种方法。)

注意：

*如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select **，
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

4、extra 之 using join buffer（了解）

using join buffer：使用了连接缓存

出现在当两个连接时：

驱动表(被连接的表，left join 左边的表。inner join 中数据少的表) 没有索引的情况下，给驱动表建立索引可解决此问题。且 type 将改变成 ref

5、extra 之 impossible where（了解）

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

4、show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

1 2	-- 开启profiling 开关； set profiling = 1;

通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述命令之后，再执行show profiles 指令，来查看SQL语句执行的耗时：

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

注意：Sending data 状态表示==MySQL线程开始访问数据行并把结果返回给客户端==，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。

例如，选择查看CPU的耗费时间：

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu

5、trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。

打开trace，设置格式为 JSON，并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示。

1 2	SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;

执行SQL语句：

1	select * from tb_item where id < 4;

最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

1	select * from information_schema.optimizer_trace\G;

*************************** 1. row ***************************
QUERY: select * from tb_item where id < 4
TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `tb_item`.`id` AS `id`,`tb_item`.`title` AS `title`,`tb_item`.`price` AS `price`,`tb_item`.`num` AS `num`,`tb_item`.`categoryid` AS `categoryid`,`tb_item`.`status` AS `status`,`tb_item`.`sellerid` AS `sellerid`,`tb_item`.`createtime` AS `createtime`,`tb_item`.`updatetime` AS `updatetime` from `tb_item` where (`tb_item`.`id` < 4)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`tb_item`.`id` < 4)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`tb_item`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`tb_item`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 9816098,
                    "cost": 2.04e6
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indices */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "id < 4"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 1.6154,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 3,
                      "ranges": [
                        "id < 4"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.6154,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`tb_item`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 2.2154,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.2154,
                "rows_for_plan": 3,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`tb_item`.`id` < 4)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`tb_item`",
                  "attached": "(`tb_item`.`id` < 4)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`tb_item`",
                "access_type": "range"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

8、索引的使用

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

1、验证索引提升查询效率

在我们准备的表结构tb_item 中，一共存储了 300 万记录；

1、根据ID查询

1	select * from tb_item where id = 1999\G;

查询速度很快，接近0s ，主要的原因是因为id为主键，有索引；

查看SQL语句的执行计划：

2、根据 title 进行精确查询

1	select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;

查看SQL语句的执行计划：

处理方案，针对title字段，创建索引：

1	create index idx_item_title on tb_item(title);

索引创建完成之后，再次进行查询：

通过explain ，查看执行计划，执行SQL时使用了刚才创建的索引：

2、索引的使用

1、准备环境

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

-- 创建name,status,address的复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

2、避免索引失效

1、全值匹配，对索引中所有列都指定具体值

该情况下，索引生效，执行效率高。

1 2	-- 对索引中所有列(name,status,address)都指定具体值 explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

2、最左前缀法则

如果索引了多列，即复合索引，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

注意：

创建的复合索引为name,status,address的复合索引，在该表中会创建三个索引：
- idx_seller_name：name的索引——对应的索引长度为：403
- idx_seller_name_sta：name与status的索引——对应的索引长度为：410
- idx_seller_name_sta_addr：name、status与address的索引——对应的索引长度为：813

匹配最左前缀法则，走索引：
把name放在最后面，走索引：（与where之后的条件先后顺序没有关系，只与查询条件有无有关）
- and 忽略左右关系。既即使没有没有按顺序由于优化器的存在，会自动优化。
违法最左前缀法则，索引失效：
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

节点比较排序是先比较第一列，第一列相同就比较第二列，接着第三列，以此类推。所以不使用第一列的话，后面就乱序了，走不了索引。

举一个简单的例子：走楼层

对应的三个索引就是上层楼：
- name——第一层
- name&status——第二层
- name$status$address——第三层
匹配最左前缀法则，走索引——成功走到楼顶
违法最左前缀法则，索引失效
- 只是使用了status或者address：未走第一层楼就想走到第二层楼或者第三层楼——失败
- 同时使用了status与address：也是一样，走第一层楼就想走到第二层楼和第三层楼——失败
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效
- 同时使用了name与address：走了第一层，但是没走第二层就想到第三层楼——第一层楼成功，第二第三层楼失败

3、范围查询右边的列，不能使用索引

根据前面的两个字段name ， status 查询是走索引的，但是最后一个条件address 没有用到索引。

4、不要在索引列上进行运算操作，索引将失效

5、字符串不加单引号，造成索引失效

由于在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换（隐式类型转换），造成索引失效（底层对索引进行了运算操作）。

6、尽量使用覆盖索引，避免`select *`

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

注意：

using index：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是索引只是记录当前索引值的数据，需要回表查询其他数据
（回调查询）
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7、用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：

1	explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

8、以%开头的Like模糊查询，索引失效

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案：

通过覆盖索引来解决

9、如果MySQL评估使用索引比全表更慢，则不使用索引

10、is NULL ， is NOT NULL 有时索引失效

mysql底层会判定该字段中的数据大部分是null还是not null：

如果大部分是not null，则使用is NOT NULL 的时候查询的是大部分数据，mysql底层会评估使用索引比全表更慢，则不使用索引，使用全表扫描；同理，如果使用的是is NULL的话，查询的是少量数据，这时候mysql使用索引查询的效率会比全表扫描高，使用mysql会使用索引。
而且因为这里是select * 查询全部数据，如果使用了索引，那么还需要回表查询索引匹配的其他数据，速度上还不如直接全表扫描。

11、in 走索引， not in 索引失效

12、单列索引和复合索引

尽量使用复合索引，而少使用单列索引。

创建复合索引：

create index idx_name_sta_address on tb_seller(name, status, address);

-- 就相当于创建了三个索引 ： 
	-- name
	-- name + status
	-- name + status + address

创建单列索引：

1
2
3

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。
- 最优索引：表中的数据辨识度，辨识度越高，索引越优。（当查询的常量在表中只有一条数据，此时的辨识度是很高的）
3个单列索引对应3个b+tree数据结构，通过索引查找只能以一个b+tree为标准来查，所以就算可能涉及到多个索引，但是只能使用一个索引。

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

索引：

idx_nameAgeJob
idx_name

使用 != 和<>的字段索引失效( != 针对数值类型。针对字符类型 != 针对数值类型)

前提 where and 后的字段在混合索引中的位置比当前字段靠后 where age != 10 and name=’xxx’，这种情况下，mysql自动优化，将 name=’xxx’ 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)

3、一般性建议

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

4、查看索引使用情况

-- 查看当前会话的索引情况
show status like 'Handler_read%';	
-- 查看全局的索引情况
show global status like 'Handler_read%';

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

9、SQL优化

什么时候需要用到SQL优化？

步骤：

查询优化
观察，至少跑1天，看看生产的慢SQL情况。
开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
explain+慢SQL分析
show profile
运维经理or DBA，进行SQL数据库服务器的参数调优。

优化原则：小表驱动大表（原理：RBO）

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。

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。

注意：A表与B表的ID字段应建立索引。

EXISTS
1
SELECT .. FROM table WHERE EXISTS (subquery)
该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。
提示：
1. *EXISTS (subquery)只返回TRUE或FALSE，因此子查询中的SELECT 也可以是SELECT 1或select’X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别
2. EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
3. EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

1、大批量插入数据

环境准备：

-- 两个表示一模一样的，一个用来测试有顺序的插入，一个用来测试没有顺序的插入
CREATE TABLE `tb_user_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT '用户状态',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE `tb_user_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT '用户状态',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

1、主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率（底层使用的是B+树）。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

脚本文件介绍：

sql1.log —-> 主键有序

sql2.log —-> 主键无序

插入ID顺序排列数据：

1
2

-- 记忆：load（加载） data（数据） 到本地文件 + 本地文件地址 into table（到哪一张表） + 表名 fields （属性） 之间的 terminated（分隔符）by 确定的分隔符 + lines（行）之间的 terminated（分隔符）by 确定的分隔符
load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';

插入ID无序排列数据：

2、关闭唯一性校验

如果保证自己数据没问题，在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

3、手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2、优化 insert 语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案：

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

示例，原始方式为：
1
2
3
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
优化后的方案为：
1
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

在事务中进行数据插入。

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

数据有序插入

insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

优化后

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

3、优化 order by 语句

1、环境准备

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

2、两种排序方式

1、filesort 排序

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

2、using index

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

多字段排序

了解了MySQL的排序方式，优化目标就清晰了：

尽量减少额外的排序，通过索引直接返回有序数据。
where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，
并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

3、Filesort 的优化

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。

对于Filesort ， MySQL 有两种排序算法：

两次扫描算法：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果（一次扫描）。完成排序之后，再根据行指针回表读取记录（两次扫描），该操作可能会导致大量随机I/O操作。
- 多路排序需要借助磁盘来进行排序。所以取数据，排好了取数据。两次 io操作。比较慢
一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
- 单路排序，将排好的数据存在内存中，省去了一次 io 操作，所以比较快，但是需要内存空间足够。
- 但是用单路也有它的问题：
  - 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
  - 本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和==Query语句取出的字段总大小==， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

4、优化 group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。如下：

1
2
3

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

优化后：

1	explain select age,count(*) from emp group by age order by null;

从上面的例子可以看出，第一个SQL语句需要进行”filesort”，而第二个SQL由于order by null 不需要进行 “filesort”，而上文提过Filesort往往非常耗费时间。

创建索引：

1	create index idx_emp_age_salary on emp(age,salary)；

一些建议：

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

5、优化嵌套查询

1、使用join替代子查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

示例，查找有角色的所有的用户信息：

1	explain select * from t_user where id in (select user_id from user_role );

执行计划为：

优化后：

1	explain select * from t_user u , user_role ur where u.id = ur.user_id;

连接(Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

2、where 之后使用的是 `用in 还是 exists`

1、实验

1、有索引&大表驱动小表

2、有索引&小表驱动大表

结论：有索引 小驱动大表性能优于大表驱动小表

3、无索引&小表驱动大表

4、无索引&大表驱动小表

3、结论

有索引的情况下用 inner join 是最好的其次是 in ，exists最糟糕
无索引的情况下用：
- 小表驱动大表 因为join 方式需要distinct ，没有索引distinct消耗性能较大
- 所以 exists性能最佳 in其次 join性能最差
无索引的情况下大表驱动小表
- in 和 exists 的性能应该是接近的都比较糟糕 exists稍微好一点超不过5% 但是inner join 优于使用了 join buffer 所以快很多
- 如果left join 则最慢

6、优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且==不能使用到复合索引，要使用单列索引==；如果没有索引，则应该考虑增加索引。

获取 emp 表中的所有的索引：

示例：

1	explain select * from emp where id = 1 or age = 30;

建议使用 union 替换 or：

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项

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

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

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

7、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，因此使用limit进行分页查询的时候，越往后，耗费的时间越长，查询排序的代价就越大。

1、优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

2、优化思路二

该方案适用于==主键自增==的表，可以把Limit 查询转换成某个位置的查询。

一般来说思路二要比思路一要简单而且效率要好，但是思路二有许多的限制：

主键自增
不能出现断层

8、优化单表查询

1、建表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');

SELECT * FROM article;

2、案例

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

1	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
2
3

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

第2次EXPLAIN
1
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
DROP INDEX idx_article_ccv ON article;

第2次新建索引

1 2	-- ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ; create index idx_article_cv on article(category_id,views);

第3次EXPLAIN

1	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 也消失了，结果非常理想。

9、优化关联查询

1、建表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)));
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)));

2、案例

下面开始explain分析

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

结论：type 有All

添加索引优化：

1	ALTER TABLE `book` ADD INDEX Y ( `card`);

第2次explain：

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

结论：可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。

这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定需要建立索引。

3、建议

保证**==被驱动表==的join字段已经被索引**
- 被驱动表 join 后的表为被驱动表 (需要被查询)
left join 时，选择小表作为驱动表，大表作为被驱动表。(原则：小表驱动大表)
- left join 时一定是左边是驱动表，右边是被驱动表
inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
子查询尽量不要放在被驱动表，有可能使用不到索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
select
a.name,bc.name
from
t_emp a
left join
(select
b.id,c.name
from
t_dept b
inner join
t_emp c
on b.ceo = c.id)
on bc.id = a.deptid;
上段查询中用到了子查询，必然 bc 表没有索引。肯定会进行全表扫描

上段查询可以直接使用两个 left join 优化：
1
2
3
select a.name , c.name from t_emp a
left outer join t_dept b on a.deptid = b.id
left outer join t_emp c on b.ceo=c.id
所有条件都可以使用到索引

若必须用到子查询，可将子查询设置为驱动表。
- 因为驱动表的type 肯定是 all，而子查询返回的结果表没有索引，必定也是all

8、使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1、USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去==参考==的索引列表（数据库不一定使用），就可以让MySQL不再考虑其他可用的索引。

1	create index idx_seller_name on tb_seller(name);

2、IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

1	explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

3、FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

1	create index idx_seller_address on tb_seller(address);

10、应用优化

前面章节，我们介绍了很多数据库的优化措施。但是在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。

1、使用连接池

对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有必要建立数据库连接池，以提高访问的性能。

2、减少对MySQL的访问

1、避免对数据进行重复检索

在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求。

比如，需要获取书籍的id 和name字段，则查询如下：

1	select id , name from tb_book;

之后，在业务逻辑中有需要获取到书籍状态信息，则查询如下：

1	select id , status from tb_book;

这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。其实完全可以用一条SQL语句得到想要的结果。

1	select id, name , status from tb_book;

2、增加cache层

在应用中，我们可以在应用中增加缓存层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。

因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储（采用配置文件的方式），或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据 。

3、负载均衡

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果。

1、利用MySQL复制分流查询

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

2、采用分布式数据库架构

分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

11、Mysql中查询缓存优化

1、概述

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。MySQL 5.6(2013)以来，查询缓存已被禁用。

2、操作流程

客户端发送一条查询给服务器；
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回给客户端。

3、查询缓存配置

查看当前的MySQL数据库是否支持查询缓存：
1
SHOW VARIABLES LIKE 'have_query_cache';
![1555249929012](D:\编程\黑马\mysql高级/资料-MySQL高级教程\MySQL 高级 - day-03\文档\assets\1555249929012.png)
查看当前MySQL是否开启了查询缓存：
1
SHOW VARIABLES LIKE 'query_cache_type';
![1555250015377](D:\编程\黑马\mysql高级/资料-MySQL高级教程\MySQL 高级 - day-03\文档\assets\1555250015377.png)
查看查询缓存的占用大小：（单位：字节）默认：1M。如果想要增加的话，建议增加的值为1024的倍数
1
SHOW VARIABLES LIKE 'query_cache_size';
![1555250142451](D:\编程\黑马\mysql高级/资料-MySQL高级教程\MySQL 高级 - day-03\文档\assets\1555250142451.png)

查看查询缓存的状态变量：

1	SHOW STATUS LIKE 'Qcache%';

![1555250443958](D:\编程\黑马\mysql高级/资料-MySQL高级教程\MySQL 高级 - day-03\文档\assets\1555250443958.png)

各个变量的含义如下：

参数	含义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
==Qcache_hits==	查询缓存命中数
==Qcache_inserts==	添加到查询缓存的查询数
Qcache_lowmen_prunes	由于内存不足而从查询缓存中删除的查询数
==Qcache_not_cached==	非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

4、开启查询缓存

MySQL的查询缓存默认是关闭的，需要手动配置参数 query_cache_type ，来开启查询缓存。

query_cache_type 该参数的可取值有三个：

值	含义
OFF 或 0	查询缓存功能关闭
ON 或 1	查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2	查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其它均不予缓存

在 /usr/my.cnf 配置中，增加以下配置：

配置完毕之后，重启服务既可生效；

然后就可以在命令行执行SQL语句进行验证，执行一条比较耗时的SQL语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存。

5、查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项：

SQL_CACHE：如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果。
SQL_NO_CACHE：服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

1 2	SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;

6、查询缓存失效的情况

1、SQL 语句不一致的情况，要想命中查询缓存，查询的SQL语句必须一致

1 2	SQL1 : select count() from tb_item; SQL2 : Select count() from tb_item;

2、当查询语句中有一些不确定的时，则不会缓存

如： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

1
2
3

SQL1 : select * from tb_item where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();

3、不使用任何表查询语句

1	select 'A';

4、查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存

1	select * from information_schema.engines;

5、在存储的函数，触发器或事件的主体内执行的查询

6、如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除

这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。当然，与此同时也会将更改之后的表重新加入查询缓存当中。

12、Mysql内存管理及优化

1、内存优化原则

将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。
MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。
排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

2、MyISAM 内存优化

myisam存储引擎使用 key_buffer 缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

1、key_buffer_size

key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。

在/usr/my.cnf 中做如下配置：（默认值为8388608字节 = 8M）

1	key_buffer_size=512M

2、read_buffer_size

如果需要经常顺序扫描myisam表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

3、read_rnd_buffer_size

对于需要做排序的myisam表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。

3、InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

1、innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。

在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

1	innodb_buffer_pool_size=512M

innodb_buffer_pool_size 的默认大小为134217728字节 = 128M

2、innodb_log_buffer_size

决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

1	innodb_log_buffer_size=10M

13、Mysql并发参数调整

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。

在Mysql中，控制并发连接和线程的主要参数包括：

max_connections
back_log
thread_cache_size
table_open_cahce

1、max_connections

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。

如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定==操作系统平台的线程库的质量==、==内存大小==、==每个连接的负荷==、==CPU的处理速度==，==期望的响应时间==等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

2、back_log

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。

如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。

5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），但最大不超过900。

如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值。

3、table_open_cache

该参数用来控制==所有SQL语句==执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 ：max_connections x N ；默认值为：2000

4、thread_cache_size

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。

默认大小为：9

5、innodb_lock_wait_timeout

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ，可以根据需要进行动态设置。

对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；
对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

14、Mysql锁问题

1、锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

2、锁分类

从对数据操作的粒度分：

表锁：操作时，会锁定整个表。
行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

读锁（共享锁）（S Lock）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）（X Lock）：当前操作没有完成之前，它会阻断其他写锁和读锁。

3、行锁：记录锁(Record Locks)

mysql的行锁是通过索引加载的，即行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描.

记录锁，仅仅锁住索引记录的一行，在单条索引记录上加锁。
record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

4、行锁：间隙锁(Gap Locks)

区间锁，仅仅锁住一个索引区间（开区间，不包括双端端点）。
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，==并不包括该索引记录本==身。比如在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)，
间隙锁可用于防止幻读，保证索引间的不会被插入数据

5、行锁：临键锁(Next-Key Locks)

record lock + gap lock，左开右闭区间。
默认情况下，innodb使用next-key locks来锁定记录。
1
select … for update
但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
Next-Key Lock在不同的场景中会退化：

场景	退化锁类型
使用unique index精确匹配(=)，且记录存在	Record Locks
使用unique index精确匹配(=)，且记录不存在	Gap Locks
使用unique index范围匹配（`<和>`）	Record Locks + Gap Locks，锁上界不锁下界(左开右闭区间)

6、表锁：意向锁

表锁，其实也可以叫意向锁，表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”。

注意：表锁可以是意向锁，但是意向锁不一定是表锁

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突：

事务在请求S锁和X锁前，需要先获得对应的IS、IX锁，
在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

q1：为什么意向锁是表级锁呢？

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

如果意向锁是行锁，则需要遍历每一行数据去确认；
如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

q2：意向锁怎么支持表锁和行锁并存？

首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。
如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如q1的答案中，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能。
1. 共享锁和排他锁，系统在特定的条件下会自动添加共享锁或者排他锁，也可以手动添加共享锁或者排他锁。
2. 意向共享锁和意向排他锁都是系统自动添加和自动释放的，整个过程无需人工干预
3. 共享锁和排他锁都是锁的行记录，意向共享锁和意向排他锁锁定的是表
4. 由于InnoDB存储引擎支持的是行级别的锁，因此意向锁不会阻塞除全表扫描以外的任何请求。
意向共享锁与排他锁冲突，也就是说如果A表中一行被加了排它锁，那么当有select * 这样的全表扫描语句的时候将会加锁失败，因为全表扫描需要对表加意向共享锁，但是表上有排他行锁，于是加锁失败；
意向排他锁与排他锁和共享锁都冲突，同理也就是说如果A表中一行被加了排它锁或者共享锁，那么当有需要加表级的意向排它锁的时候，加锁失败；

3、Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

下表中罗列出了各存储引擎对锁的支持情况：

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
==InnoDB==	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

MySQL这3种锁的特性可大致归纳如下：

锁类型	特点
表级锁	偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁	偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

4、MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

1、如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁；
在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁；
这个过程并不需要用户干预，因此用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

-- 加读锁
lock table table_name read;

-- 加写锁
lock table table_name write；

-- 解锁
unlock tables

2、读锁案例

准备环境

create database demo_03 default charset=utf8mb4;

use demo_03;

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');



CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

客户端一：

1）获得tb_book 表的读锁

1	lock table tb_book read;

2）执行查询操作

1	select * from tb_book;

可以正常执行，查询出数据。

客户端二：

3）执行查询操作

1	select * from tb_book;

客户端一：

4）查询未锁定的表

1	select name from tb_seller;

客户端二：

5）查询未锁定的表

1	select name from tb_seller;

可以正常查询出未锁定的表；

客户端一：

6）执行插入操作

1	insert into tb_book values(null,'Mysql高级','2088-01-01','1');

执行插入，直接报错，由于当前tb_book 获得的是读锁，不能执行更新操作。

客户端二：

7）执行插入操作

1	insert into tb_book values(null,'Mysql高级','2088-01-01','1');

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 inesrt 语句，立即执行；

3、写锁案例

客户端一：

1）获得tb_book 表的写锁

1	lock table tb_book write;

2）执行查询操作

1	select * from tb_book;

查询操作执行成功；

3）执行更新操作

1	update tb_book set name = 'java编程思想（第二版）' where id = 1;

更新操作执行成功；

客户端二：

4）执行查询操作

1	select * from tb_book ;

当在客户端一中释放锁指令 unlock tables 后，客户端二中的 select 语句，立即执行；

4、结论

锁模式的相互兼容性如表中所示：

由上表可见：

对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

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

5、查看锁的争用情况

1	show open tables；

In_user：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
Name_locked：表名称是否被锁定。名称锁定用于==取消表==或==对表进行重命名==等操作。

1	show status like 'Table_locks%';

Table_locks_immediate：指的是能够立即获得表级锁的次数，每立即获取锁，值加1。
Table_locks_waited：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

5、InnoDB 行锁

1、行锁介绍

行锁特点：

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

InnoDB 与 MyISAM 的最大不同有两点：

一是==支持事务==；
二是 ==采用了行级锁==。

2、背景知识

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务==ACID属性==。

ACID属性	含义
原子性（Atomicity）	事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（Consistent）	在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。
持久性（Durable）	事务完成之后，对于数据的修改是永久的。

2、并发事务处理带来的问题

问题	含义
丢失更新（Lost Update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。
脏读（Dirty Reads）	当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读（Non-Repeatable Reads）	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。
幻读（Phantom Reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。

3、事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为：

Read uncommitted
Read committed（Oracle默认）
Repeatable read（Mysql默认）
Serializable

这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

隔离级别	丢失更新	脏读	不可重复读	幻读
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read（默认）	×	×	×	√
Serializable	×	×	×	×

备注： √ 代表可能出现， × 代表不会出现。

Mysql 的数据库的默认隔离级别为 Repeatable read ，查看方式：

1	show variables like 'tx_isolation';

4、解决方案解析

解决脏读——使用读已提交：

首先我们要理解什么是脏读，既然脏读是一个事物读到了另一个事务未提交的数据，那么我们就让它提交后再让别的事物读就好了，读已提交就是改变了释放锁的时机，让事务完成提交后再去释放锁。这样就解决了脏读问题。

解决不可重复读——使用可重复读：

不可重复读是因为读取过程中有其他事务修改数据，导致读取数据不一致。那我们就要保证一个事务读取数据的时候就让他老老实实读那个数据。可重复读是用一个MVCC(多版本并发控制)机制去解决的。

MVCC(多版本并发控制)

MVCC其实就是行级锁的一个升级版。我们都知道数据库中有表锁和行锁，在表锁中读写操作是阻塞的，而MVCC的读写一般是不会阻塞的，这样避免了很多加锁过程。

MVCC具体实现：通过在每行记录后面保存两个隐藏的字段，一个保存的是此行的创建时间，一个保存的是指向旧版本的指针。它们存储的也并不是真的时间，而是系统版本号。就跟我们使用软件都有1.0，2.0这些版本，每个版本有它们自己的特点和数据。MVCC就是在每次开始事务时，都会对应自动递增并保存一个版本号，通过这个版本号去生成对应的一个时间点的数据快照，利用这个快照就可以保证数据读取的一致性。

MVCC把SQL分为两类：一种是快照读，就是普通的select操作，读的就是历史版本的数据。另一种是当前读，比如select … for update，insert，update，delete 读的都是最新的数据，不可重复读就是利用快照保存数据，然后就解决啦！

通俗的说就是：MVCC就是给每次事务操作的数据行都加个字段，代表这次事务的版本，那么如果我现在读取这行数据时，就会通过这个版本生成一个数据快照，那么我这个事务再读的时候，会直接从版本快照中获得数据，相当于帮我们缓存了一份数据，注意喔，我这两次读取都是同一个事务喔！

解决幻读——使用串行化：

上文说到解决不可重复读用MVCC就可以根据版本保证读取的数据一致，那幻读不是也可以用这个去解决吗？

那么这里又要重申幻读和不可重复读的区别，不可重复读是针对某行数据，幻读是特指查询到记录条数，也就是多条数据的查询。

所以我们使用MVCC + Next-key Lock锁去解决幻读问题。

科普一下Innodb的三种行锁的算法：

行锁：就是对单条记录上锁。

间隙锁：锁定一个范围，但是不会包括记录自己，就是如果要查询一个id=10的数据时，就会把它范围外的加上锁防止插入数据操作，这个就是间隙锁。

Next-key Lock：行锁+间隙锁的合体算法，使用它时不仅会把id=10 的范围加上行锁，也会把它间隙加上锁，对于行的查询，使用此法便ok。

具体实现：

当事务执行的是select…for update 时，Next-key Lock对范围加锁，这样事务A执行这个查询当前读语句时，事务B是不能去修改范围内的数据的。

遇到的问题：

我们使用串行化解决幻读会有什么问题产生呢？
我们知道解决幻读使用了间隙锁，那么我们在并发情况下很容易造成死锁！

举个栗子：

事务A、事务B同时执行select * from table where id = 10 for update ，前提是我们没有id=10这条数据，事务A执行时因加上了间隙锁，同时事务B也执行这条语句。这时，事务B如果去添加数据就会因为事务A的间隙锁造成阻塞，事务A再执行添加数据也会因为事务B间隙锁造成阻塞，这样就形成了一个死锁。

所以串行化的并发性不好，那我们实际项目中要合理的选择取舍。

3、InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁：

共享锁（S）：又称为==读锁==，简称==S锁==，**共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是==只能读不能修改==**。
排他锁（X）：又称为==写锁==，简称==X锁==，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁。

-- 共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

-- 排他锁（X)
SELECT * FROM table_name WHERE ... FOR UPDATE

4、案例准备工作

create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

-- 创建两个单列索引
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

5、行锁基本演示

Session-1	Session-2
关闭自动提交功能	关闭自动提交功能
可以正常的查询出全部的数据	可以正常的查询出全部的数据
查询id 为3的数据；	查询id为3的数据；
更新id为3的数据，但是不提交；	更新id为3 的数据，出于等待状态
通过commit，提交事务	解除阻塞，更新正常进行
以上，操作的都是同一行的数据，接下来，演示不同行的数据：
更新id为3数据，正常的获取到行锁，执行更新；	由于与Session-1 操作不是同一行，获取当前行锁，执行更新；

6、无索引行锁升级为表锁

条件不具备索引性质（索引失效或不是索引），则会导致行锁变为表锁

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

查看当前表的索引：

1	show index from test_innodb_lock ;

Session-1	Session-2
关闭事务的自动提交	关闭事务的自动提交
执行更新语句：	执行更新语句，但处于阻塞状态：
提交事务：	解除阻塞，执行更新成功：
	执行提交操作：

由于执行更新时， name字段本来为varchar类型，我们是作为数组类型使用，存在类型转换，索引失效，最终行锁变为表锁。

7、间隙锁危害

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁；对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个 “间隙” 加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

示例：

Session-1	Session-2
关闭事务自动提交	关闭事务自动提交
根据id范围更新数据	此时id=2的数据不存在（间隙）
	插入id为2的记录，出于阻塞状态
提交事务；
	解除阻塞，执行插入操作：
	提交事务：

8、InnoDB 行锁争用情况

1	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：**==系统启动后到现在总共等待的次数==**

当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

9、总结

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

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

优化建议：

尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围
尽可能减少索引条件，及索引范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可使用低级别事务隔离（但是需要业务层面满足需求）

15、Mysql 事务隔离的底层实现

1、MVCC（Multi-Version Concurrency Control）多版本并发控制

1、什么是MVCC？

MVCC（Multi-Version Concurrency Control）多版本并发控制，是数据库控制并发访问的一种手段。

在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

总结：

MVCC只在 读已提交(RC) 和 可重复度（RR） 这两种事务隔离级别下才有效
数据库引擎（InnoDB） 层面实现的，用来处理读写冲突的手段（不用加锁），提高访问性能

2、MVCC的底层原理——版本链和一致性视图

1、版本链

版本链是一条链表，链接的是每条数据曾经的修改记录

那么这个版本链又是如何形成的呢，每条数据又是靠什么链接起来的呢？

其实是这样的，对于InnoDB存储引擎的表来说，它的聚簇索引记录包含两个隐藏字段：

trx_id：==存储修改此数据的事务id，只有这个事务操作了某些表的数据后当更改操作发生的时候（update,delete,insert），才会分配唯一的事务id，并且此事务id是递增的==
roll_pointer：指针，==指向上一次修改的记录==
row_id(非必须)：当有==主键==或者有==不允许为null的unique键==时，不包含此字段

假如说当前数据库有一条这样的数据，假设是事务ID为100的事务插入的这条数据，那么此条数据的结构如下：

后来，事务200，事务300，分别来修改此数据：

时间T	trx_id 200	trx_id 300
T1	开始事务	开始事务
T2	更改名字为A
T3	更改名字为B
T4	提交事务	更改名字为C
T6		提交事务

所以此时的版本链如下：

我们每更改一次数据，就会插入一条undo日志，并且记录的roll_pointer指针会指向上一条记录，如图所示：（注意：插入操作的undo日志没有roll_pointer这个属性，因为它没有老版本）

第一条数据是小杰，事务ID为100
事务ID为200的事务将名称从小杰改为了A
事务ID为200的事务将名称从A又改为了B
事务ID为300的事务将名称从B又改为了C

所以串成的链表就是 C -> B -> A -> 小杰（从最新的数据到最老的数据）

2、一致性视图（ReadView）

需要判断版本链中的哪个版本是是当前事务可见的，因此有了一致性视图的概念。其中有四个属性比较重要：

m_ids：在生成ReadView时，当前活跃的读写事务的事务id列表
- 当前活跃的读写事务就是begin了还未提交的事务
min_trx_id：m_ids的最小值
max_trx_id：m_ids的最大值+1
creator_trx_id：生成该事务的事务id，单纯开启事务是没有事务id的，默认为0，creator_trx_id是0。

版本链中的当前版本是否可以被当前事务可见的要根据这四个属性按照以下几种情况来判断：

当 trx_id = creator_trx_id 时：当前事务可以看见自己所修改的数据，可见，
当 trx_id < min_trx_id 时：生成此数据的事务已经在生成readView前提交了，可见
当 trx_id >= max_trx_id 时：表明生成该数据的事务是在生成ReadView后才开启的， 不可见
当 min_trx_id <= trx_id < max_trx_id 时
- trx_id 在 m_ids 列表里面：生成ReadView时，活跃事务还未提交，不可见
- trx_id 不在 m_ids 列表里面：事务在生成readView前已经提交了，可见

如果某个版本数据对当前事务不可见，那么则要顺着版本链继续向前寻找下个版本，继续这样判断，以此类推。

3、对于RR（可重复读）和RC（读已提交）在生成一致性视图时机的区别

对于事务的隔离级别：RR（可重复读）和RC（读已提交）在生成一致性视图的时机是不一样的：

读提交（read committed RC）是在每一次select的时候生成ReadView的
可重复读（repeatable read RR）是在第一次select的时候生成ReadView的

示例：

多个事务如下执行，我们通过这个例子来分析当数据库隔离级别为RC和RR的情况下，当时读数据的一致性视图和版本链，也就是MVCC，分别是怎么样的。

假设数据库中有一条初始数据姓名是小杰，id是1 （id,姓名,trx_id,roll_point），插入此数据的事务id是1
尤其要指出的是，只有这个事务操作了某些表的数据后当更改操作发生的时候（update,delete,insert），才会分配唯一的事务id,并且此事务id是递增的，单纯开启事务是没有事务id的，默认为0，creator_trx_id是0。
以下例子中的A,B,C的意思是将姓名更改为A,B,C，读也是读取当前时刻的姓名，默认全都开启事务，并且此事务都经历过某些操作产生了事务id

时间	事务100	事务200	事务300	事务400
T1	A
T2	B
T3		C
T4			读
T5	提交
T6			D
T7				读
T8		E
T9		提交
T10			读

1、读已提交（RC）与MVCC

一个事务提交之后，它做的变更才会被其他事务看到
- ==每次读==的时候，ReadView(一致性视图)都会重新生成

当T1时刻时，事务100修改名字为A
当T2时刻时，事务100修改名字为B
当T3时刻时，事务200修改名字为C
当T4时刻时，事务300开始读取名字

此时这条数据的版本链如下：（同颜色代表是同一事务内的操作）

此时T4时刻事务300要读了，究竟会读到什么数据？

当前最近的一条数据是，C，事务200修改的，还记得我们前文说的一致性视图的几个属性和按照什么规则判断这个数据能不能被当前事务读。我们就分析这个例子。

此时（生成一致性视图ReadView）

m_ids 是{100,200}： 当前活跃的读写事务的事务id列表
min_trx_id 是 100： m_ids的最小值
max_trx_id 是 201： m_ids的最大值+1

当前数据的trx_id(事务id)是 200，符合min_trx_id<=trx_id<max_trx_id 此时需要判断：

trx_id 是否在m_ids活跃事务列表里面，
- 一看，活跃事务列表里面是{100，200}，只有两个事务活跃，而此时的trx_id是200，则trx_id在活跃事务列表里面，
活跃事务列表代表还未提交的事务，所以该版本数据不可见，就要根据roll_point指针指向上一个版本，
继续这样的判断，上一个版本事务id是100，数据是B，发现100也在活跃事务列表里面，所以不可见，
继续找到上个版本，事务是100，数据是A，发现是同样的情况，
继续找到上个版本，发现事务是1，数据是小杰，1小于100，trx_id<min_trx_id，代表生成这个数据的事务已经在生成ReadView前提交了，此数据可以被读到。所以读取的数据就是小杰

分析完第一个读，我们继续向下分析：

当T5时刻时，事务100提交
当T6时刻时，事务300将名字改为D
当T7时刻时，事务400读取当前数据

此时这条数据的版本链如下：

此时（重新生成一致性视图ReadView）

m_ids 是{200,300}： 当前活跃的读写事务的事务id列表
min_trx_id 是 200： m_ids的最小值
max_trx_id 是 301： m_ids的最大值+1
当前数据事务id是300，数据为D，符合min_trx_id<=trx_id<max_trx_id
此时需要判断数据是否在活跃事务列表里，300在这里面，所以就是还未提交的事务就是不可见，所以就去查看上个版本的数据，
上个版本事务id是200，数据是C，也在活跃事务列表里面，也不可见，继续向上个版本找，
上个版本事务id是100，数据是B，100小于min_trx_id，就代表，代表生成这个数据的事务已经在生成ReadView前提交了，此数据可见，所以读取出来的数据就是B

分析完第二个读，我们继续向下分析：

当T8时刻时，事务200将名字改为E
当T9时刻时，事务200提交
当T10时刻时，事务300读取当前数据

此时这条数据的版本链如下：

此时（重新生成一致性视图ReadView）

m_ids 是[300]： 当前活跃的读写事务的事务id列表
min_trx_id 是 300： m_ids的最小值
max_trx_id 是 301： m_ids的最大值+1

当前事务id是200，200<min_trx_id ,代表生成这个数据的事务已经在生成ReadView前提交了，此数据可见，所以读出的数据就是E.

总结：当隔离级别是读已提交RC的情况下，每次读都会重新生成一致性视图（ReadView）

T4时刻事务300读取到的数据是小杰
T7时刻事务400读取到的数据是B
T10时刻事务300读取到的数据是E

2、可重复读（RR）与MVCC

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
- 所以对于事务300来讲，它分别在T4和T10的时候，读取数据，但是它的一致性视图，用的永远都是第一次读取时的视图，就是T3时刻产生的一致性视图

==RR和RC的版本链是一样的==，但是判断当前数据可见与否用到的一致性视图不一样

在此可重复读RR隔离级别下：

T4时刻时事务300第一次读时的分析和结果与RC都一样，可以见上文分析与结果
T7时刻时事务400第一次读时的分析和结果与RC都一样，可以见上文分析与结果
T10时刻时事务300第二次读时的一致性视图和第一次读时的一样，所以此时到底读取到什么数据就要重新分析了

此时（用的是第一次读时生成的一致性视图ReadView）

m_ids 是[100,200]： 当前活跃的读写事务的事务id列表
min_trx_id 是 100： m_ids的最小值
max_trx_id 是 201： m_ids的最大值+1

此时的版本链是：

当前数据的事务id是200，数据是E，在当前事务活跃列表里面，所以数据不可见，根据回滚指针找到上个版本，发现事务id是300，当前事务也是300，可见，所以读取的数据是D

总结：当隔离级别是可重复读RR的情况下，每次读都会用第一次读取数据时生成的一致性视图（ReadView）

T4时刻事务300读取到的数据是小杰
T7时刻事务400读取到的数据是B
T10时刻事务300读取到的数据是D

2、关于间隙锁与Next-Key Lock

1、简介

科普一下Innodb的三种行锁的算法：

行锁：就是对单条记录上锁。记录锁（Record Lock）
间隙锁：gap锁，又称为间隙锁。存在的主要目的就是为了防止在可重复读的事务级别下，出现幻读问题。
Next-key Lock：Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁。innodb默认的锁就是Next-Key locks。是行锁和gap锁的组合。
- 在可重复读的事务级别下面，普通的select读的是快照，不存在幻读情况，但是如果加上for update的话，读取是已提交事务数据，Next-key Lock锁保证for update情况下，不出现幻读。

2、那么gap锁到底是如何加锁的呢？

假如是for update级别操作，先看看几条总结的何时加锁的规则：

唯一索引
- 精确等值检索：Next-Key Locks就退化为记录锁，不会加gap锁
- 范围检索：会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上记录锁和 gap 锁（至于区间是多大稍后讨论）。
- 不走索引检索：全表间隙加gap锁、全表记录加记录锁——>升级为表锁
非唯一索引
- 精确等值检索：Next-Key Locks会对间隙加gap锁（至于区间是多大稍后讨论），以及对应检索到的记录加记录锁。
- 范围检索：会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上记录锁和gap 锁（至于区间是多大稍后讨论）。
- 非索引检索：全表间隙gap lock，全表记录record lock

3、相关实例

1、建表

create table gap_table
(
  letter varchar(2) default '' not null
    primary key,
  num    int                   null
);

create index gap_table_num_uindex
  on gap_table (num);
  
INSERT INTO gap_table (letter, num) VALUES ('d', 3);
INSERT INTO gap_table (letter, num) VALUES ('g', 6);
INSERT INTO gap_table (letter, num) VALUES ('j', 8);

表结构：主键简单点是字符，属性列只有一个数字，是非唯一索引。

2、无gap锁

假如没有gap锁，也就是把事务级别调到读已提交，执行以下两个session

session1	session2
select * from gap_table where num=6 for update;结果是一条
	INSERT INTO gap_table (letter, num) VALUES (’’, 6);
select * from gap_table where num=6 for update;结果是二条，出现幻读

3、非唯一索引等值检索gap锁

假如有gap锁，演示一个非唯一索引等值检索gap锁。也就是把事务级别调到可重复读，执行以下两个session

session1	session2
select * from gap_table where num=6 for update;结果是一条。
	INSERT INTO gap_table (letter, num) VALUES (’’, 6);gap锁住间隙，阻塞无法插入数据。
select * from gap_table where num=6 for update;结果是一条。不出现幻读

4、唯一索引（主键）范围检索gap锁

假如有gap锁，演示一个唯一索引范围检索gap锁。也就是把事务级别调到可重复读，执行以下两个session

session1	session2
select * from gap_table where letter>’d’ for update;结果是两条。
	INSERT INTO gap_table (letter, num) VALUES (‘z’, 10);gap锁住间隙，阻塞无法插入数据。
select * from gap_table where letter>’d’ for update;结果是两条。不出现幻读

4、gap锁是如何锁区间？

经过上面的演示可以知道gap锁的基本作用就是保证可重复读的情况下不出现幻读。

那么还有一点就是gap是按照什么原则进行锁的呢？

要了解gap锁的原则，需要先了解innodb中索引树的结构。

下面一张图片描述了在innodb中，索引的数据结构是如何组织的：

从上面的图片可以看出，索引结构分为主索引树和辅助索引树，辅助索引树的叶子节点中包含了主键数据，主键数据影响着叶子节点的排序，gap锁的关键就是锁住索引树的叶子节点之间的间隙，不让新的记录插入到间隙之中，说起来可能拗口，下面画图分析。

1、非唯一索引gap锁原则分析

假如还是使用一开始演示的表结构和数据，那么当前的辅助索引树（数字列）叶子节点的排序结构应该如下。

假如执行以下sql的话：

1	INSERT INTO gap_table (letter, num) VALUES ('k', 6);

辅助索引树的叶子节点结构变为以下图片结构，k大于g，所以(6,k)排在后面，我们先把(6,k)这条数据删除，方便后面演示：

了解了以上的规则，我们进行实际操作演示gap锁区间原则，从而推测锁住哪些区间。

情况1

分别有两个session，session1执行以下语句：

1	select * from gap_table where num=6 for update

session2执行以下sql，执行成功：

1	INSERT INTO gap_table (letter, num) VALUES ('a', 3);

按照排序规则，叶子节点插入结构如下：

情况2

分别有两个session，session1执行以下语句：

1	select * from gap_table where num=6 for update

session2执行以下sql，执行失败：

1	INSERT INTO gap_table (letter, num) VALUES ('e', 3);

按照排序规则，叶子节点应该插入如下地方，但是因为区间被锁插入失败。

情况3

分别有两个session，session1执行以下语句：

1	select * from gap_table where num=6 for update

session2执行以下sql，执行失败：

1	INSERT INTO gap_table (letter, num) VALUES ('h', 6);

按照排序规则，叶子节点应该插入如下地方，但是因为区间被锁插入失败。

情况4

分别有两个session，session1执行以下语句：

1	select * from gap_table where num=6 for update

session2执行以下sql，执行失败：

1	INSERT INTO gap_table (letter, num) VALUES ('h', 7);

按照排序规则，叶子节点应该插入如下地方，但是因为区间被锁插入失败。

情况5

分别有两个session，session1执行以下语句：

1	select * from gap_table where num=6 for update

session2执行以下sql，执行成功：

1	INSERT INTO gap_table (letter, num) VALUES ('h', 9);

按照排序规则,插入在未锁区间就能插入成功。

1、总结

当session1执行以下语句：

1	select * from gap_table where num=6 for update

锁住的区间如图所示。

按照B+索引树排序规则，计算好叶子节点插入位置时，在被gap锁住的区间段内，不能插入任何数据，只有在gap锁释放时才能进行插入。

在上面的各种情况中锁住的区间其实是（3，d）到（6，g）和（6，g）到（8，j），落到这个区间段的叶子节点都是无法插入的。主键也作为一个信息参与到叶子节点的排序规则中。这里面边界都是开区间，插入（3，d），（8，j）的数据会报错主键重复而不是lock等待超时。

2、唯一索引或者非唯一索引范围检索gap锁原则分析

另一种会出现gap锁的情况就是使用索引时，用到范围检索，就会出现gap 锁。

使用以下表结构：

create table gap_tbz
(
  id   int default 0 not null
    primary key,
  name varchar(11)   null
);

INSERT INTO test.gap_tbz (id, name) VALUES (1, 'a');
INSERT INTO test.gap_tbz (id, name) VALUES (5, 'h');
INSERT INTO test.gap_tbz (id, name) VALUES (8, 'm');
INSERT INTO test.gap_tbz (id, name) VALUES (11, 'ds');

情况1

分别有两个session，session1执行以下语句：

1	select * from gap_tbz where id > 5 for update;

session2执行以下sql，执行失败：

1	insert into gap_tbz values(6,'cc');

按照排序规则，这里应该是在主键索引树检索，叶子节点插入结构如下。由于session1执行了范围的for update sql语句，因此范围内添加了gap锁，gap锁的区间是id在（5，+无限）

当执行插入的id范围在5之前，如下sql，能够执行成功。

1	insert into gap_tbz values(4,'cc');

但若此时将session1的sql修改为：

1	select * from gap_tbz where id >= 5 for update;

则此时gap锁的区间为id区间（1,5）和（5，+无限），也就是说以下sql会执行失败：

1	insert into gap_tbz values(4,'cc');

这也同时反映了间隙锁Gap的危害：在数据行2/3/4进行插入操作不会影响session1的sql的执行效果，但是此时数据行2/3/4却因为被加上了间隙锁而导致不能实现插入操作

情况2

分别有两个session，session1执行以下语句：

1	select * from gap_tbz where id > 5 and id < 11 for update;

session2执行以下sql，执行失败：

#以下报错 lock等待超时
insert into gap_tbz values(11,'cc');

#以下报错 主键重复
insert into gap_tbz values(5,'cc');

#从两种报错来看也可以看出gap锁区间是左开右闭

按照排序规则，这里应该是在主键索引树检索，由于session1执行了范围的for update sql语句，因此范围内添加了gap锁，gap锁的区间是id在（5，11]**，唯一索引gap锁区间是左开右闭**。

2、总结

当session1执行以下语句：

1	select * from gap_tbz where id > 5 for update;

此时gap锁的区间是id在（5，+无限）

当session1执行以下语句：

1	select * from gap_tbz where id >= 5 for update;

此时gap锁的区间为id区间（1,5）和（5，+无限）

当session1执行以下语句：

1	select * from gap_tbz where id > 5 and id < 11 for update;

此时gap锁的区间是id在（5，11]，唯一索引gap锁区间是左开右闭

3、思考（在session1执行for update语句）

假如条件是一个非索引列，那么如何处理？

假如是非索引列，那么将会全表间隙加上gap锁。
如果此时整个锁没有数据的话，那么整个表都会加上一个间隙锁

条件是唯一索引等值检索且记录不存在的情况，会使用gap lock？

我们要考虑，gap lock是防止幻读，那么尝试思考，使用唯一索引所谓条件查找数据for update，如果对应的记录不存在的话，是无法使用行锁的。
这时候，会使用gap lock来锁住区间，保证记录不会插入，防止出现幻读。

使用了间隙锁可以在可重复读的隔离级别下解决幻读问题，那么间隙锁Gap在并发情况下很容易造成死锁问题

字段id为不是主键，可以查询出多个值，数据库没有id=10这些数据
事务A、事务B同时执行select * from table where id = 10 for update 语句
事务A执行时SQL语句时加上了间隙锁，同时添加数据一条数据
1
insert into table(id,name) values(10,"zs");
此时事务A对除了（10，”zs”）这条数据以外的数据都设置了间隙锁
事务B 执行select * from table where id = 10 for update 语句查出（10，”zs”）这条数据，并对除了（10，”zs”）这条数据以外的数据都设置了间隙锁
此时事务A如果去添加数据一条其他数据，如（10，”ls”）或者事务B如果去添加数据一条其他数据，如（10，”ww”）
事务A与事务B都会因为对方的间隙锁而导致插入阻塞，从而导致死锁。
使用Next-Key Lock 可以解决以上问题：因为使用Next-Key Lock可以把（10，”zs”）这条数据也加上锁

4、总结

间隙锁的危害

在数据行2/3/4（间隙）进行插入操作不会影响session1的sql的执行效果的情况下，此时数据行2/3/4（间隙）却因为被加上了间隙锁而导致不能实现插入操作
在使用for update的情况下，会有大面积的间隙锁产生，此时其他连接不能操作当前数据
死锁问题

Next-Key Lock的作用

在非唯一索引的精确等值检索时，Next-Key Locks会对间隙加gap锁，以及对应检索到的记录加记录锁。防止出现幻读问题
解决死锁问题
Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁。

补充：关于Next-Key Lock加的记录锁是读锁还是写锁的问题

经过测试得：在Mysql8，存储引擎为InnoDB的情况下

如果使用的select……lock in share mode进行搜索，使用的是共享锁，则记录锁是读锁
如果使用的select……for update进行搜索，使用的是独占锁，则记录锁是写锁

16、常用SQL技巧

1、常见通用的Join查询

1、SQL执行顺序

编写顺序

SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

执行顺序（随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序）

FROM	<left_table>

ON 		<join_condition>

<join_type>		JOIN	<right_table>

WHERE		<where_condition>

GROUP BY 	<group_by_list>

HAVING		<having_condition>

SELECT DISTINCT		<select list>

ORDER BY	<order_by_condition>

LIMIT		<limit_params>

2、Join图

共有与独有(理解)：

共有：满足 a.deptid = b.id 的叫共有
A独有：A 表中所有不满足 a.deptid = b.id 连接关系的数据

同时参考 join 图

1、案例准备

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

2、7种JOIN

A、B两表共有（inner join）

1	select * from t_emp a inner join t_dept b on a.deptId = b.id;

A、B两表共有 + A的独有（a left join b）

1	select * from t_emp a left join t_dept b on a.deptId = b.id;

A、B两表共有 + B的独有（a right join b）

1	select * from t_emp a right join t_dept b on a.deptId = b.id;

A的独有（a left join b + on a.Id = b.id + where b.id is null）

1	select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;

B的独有（a right join b + on a.Id = b.id where a.Id is null）

1	select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;

AB全有（a full outer join b）

-- MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法：
-- left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id

UNION

SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。

A的独有 + B的独有（a full outer join b + on a.Id = b.id + where a.Id is null or b,id = null）

select * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL

UNION

SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

3、子查询与join两者区别

思想上的区别：

子查询理解：
1. 先知道需要查询并将数据拿出来(若from 后的表也是一个子查询结果)。
2. 在去寻找满足判断条件的数据(where,on,having 后的参数等)。而这些查询条件通常是通过子查询获得的。
3. 子查询是一种根据结果找条件的倒推的顺序。比较好理解与判断
join理解：
1. 执行完第一步后的结果为一张新表。
2. 在将新表与 t_emp 进行下一步的 left join 关联。
3. 先推出如何获得条件，再像算数题一样一步一步往下 join。可以交换顺序，但只能是因为条件间不相互关联时才能交换顺序。
4. join 比子查询难一点
5. join 能用到索引，但是子查询出来的表会使索引失效。

2、正则表达式使用

正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。

符号	含义
^	在字符串开始处进行匹配
$	在字符串末尾处进行匹配
.	匹配任意单个字符, 包括换行符
[…]	匹配出括号内的任意字符
[^…]	匹配不出括号内的任意字符
a*	匹配零个或者多个a(包括空串)
a+	匹配一个或者多个a(不包括空串)
a?	匹配零个或者一个a
a1\|a2	匹配a1或a2
a(m)	匹配m个a
a(m,)	至少匹配m个a
a(m,n)	匹配m个a 到 n个a
a(,n)	匹配0到n个a
(…)	将模式元素组成单一元素

select * from emp where name regexp '^T';

select * from emp where name regexp '2$';

select * from emp where name regexp '[uvw]';

3、MySQL 常用函数

数字函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换（下标从1开始）
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

日期函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

17、MySql中常用工具

1、mysql

该mysql不是指mysql服务，而是指mysql的客户端工具。

语法：

1	mysql [options] [database]

1、连接选项

参数[options]：

-u, –user=name：指定用户名
-p, –password[=name]：指定密码
-h, –host=name：指定服务器IP或域名
-P, –port=#：指定连接端口

示例：

1
2
3

mysql -h 127.0.0.1 -P 3306 -u root -p

mysql -h127.0.0.1 -P3306 -uroot -p2143

2、执行选项

1 2	# 执行SQL语句并退出 -e, --execute=name

此选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。

示例：

1	mysql -uroot -p2143 db01 -e "select * from tb_book";

2、mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来==检查服务器的配置和当前状态==、==创建并删除数据库==等。也是对于一些批处理脚本，这种方式尤其方便。

可以通过： mysqladmin --help 指令查看帮助文档

示例：

mysqladmin -uroot -p2143 create 'test01';  

mysqladmin -uroot -p2143 drop 'test01';

mysqladmin -uroot -p2143 version;

3、mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。

语法：

1	mysqlbinlog [options] log-files1 log-files2 ...

选项[options]：

-d, –database=name：指定数据库名称，只列出指定的数据库相关操作。
-o, –offset=#：忽略掉日志中的前n行命令。
-r,–result-file=name：将输出的文本格式日志输出到指定文件。
-s, –short-form：显示简单格式，省略掉一些信息。
–start-datatime=date1 –stop-datetime=date2：指定日期间隔内的所有日志。
–start-position=pos1 –stop-position=pos2：指定位置间隔内的所有日志。

4、mysqldump

mysqldump 客户端工具用来==备份数据库==或==在不同数据库之间进行数据迁移==。

备份内容包含创建表，及插入表的SQL语句。

语法：

mysqldump [options] db_name [tables]

mysqldump [options] --database/-B db1 [db2 db3...]

mysqldump [options] --all-databases/-A

1、连接选项

参数[options]：

-u, –user=name：指定用户名
-p, –password[=name]：指定密码
-h, –host=name：指定服务器IP或域名
-P, –port=#：指定连接端口

2、输出内容选项

参数[options]：

–add-drop-database：在每个数据库创建语句前加上 Drop database 语句（如果数据库存在就删除旧数据库）
–add-drop-table：在每个表创建语句前加上 Drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
-n, –no-create-db：不包含数据库的创建语句
-t, –no-create-info：不包含数据表的创建语句
-d –no-data：不包含数据
-q –quick：加上 -q 后，不会把SELECT出来的结果放在buffer中，而是直接dump到标准输出中，顶多只是buffer当前行结果，正常情况下是不会超过 max_allowed_packet 限制的，它默认情况下是开启的。
-R：备份存储过程等
-T, –tab=name：自动生成两个文件：
- 一个.sql文件，创建表结构的语句；
- 一个.txt文件，数据文件，相当于select into outfile

示例：

1
2
3

mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
	
mysqldump -uroot -p2143 -T /tmp test city

5、mysqlimport/source

mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

语法：

1	mysqlimport [options] db_name textfile1 [textfile2...]

示例：

1	mysqlimport -uroot -p2143 test /tmp/city.txt

如果需要导入sql文件，可以使用mysql中的source 指令 :

1	source /root/tb_book.sql

6、mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：

1	mysqlshow [options] [db_name [table_name [col_name]]]

参数[options]：

–count：显示数据库及表的统计信息（数据库，表均可以不指定）
-i：显示指定数据库或者指定表的状态信息

示例：

#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p2143 --count

#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count

#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

18、Mysql 日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL 也不例外。

在 MySQL 中，有 4 种不同的日志，分别是：

错误日志
二进制日志（BINLOG 日志）
查询日志
慢查询日志

这些日志记录着数据库在不同方面的踪迹。

全局查询日志：

配置启用：

在mysql的my.cnf中，设置如下：

#开启
general_log=1   
# 记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

编码启用：

set global general_log=1;

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

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

1、错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

该日志是默认开启的， 默认存放目录为 mysql 的数据目录（var/lib/mysql）, 默认的日志文件名为 hostname.err（hostname是主机名）。

查看日志位置指令：

1	show variables like 'log_error%';

查看日志内容：

1	tail -f /var/lib/mysql/xaxh-server.err

2、二进制日志

1、概述

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。

此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。

二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。

配置文件位置 : /usr/my.cnf

日志存放位置：配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录（var/lib/mysql）。

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=STATEMENT

2、日志格式

1、STATEMENT

该日志格式在日志文件中记录的都是==SQL语句（statement）==，每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。

主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

2、ROW

该日志格式在日志文件中记录的是==每一行的数据变更==，而不是记录SQL语句。

比如，执行SQL语句： update tb_book set status=’1’：

如果是STATEMENT 日志格式，在日志中会记录一行SQL文件；
如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。

3、MIXED

这是目前MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。

默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。

3、日志读取

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看，语法如下：

1	mysqlbinlog log-file；

1、查看STATEMENT格式日志

执行插入语句：

1	insert into tb_book values(null,'Lucene','2088-05-01','0');

查看日志文件：

mysqlbin.index：该文件是日志索引文件，记录日志的文件名；
mysqlbing.000001：日志文件

查看日志内容：

1	mysqlbinlog mysqlbing.000001；

2、查看ROW格式日志

配置：

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=ROW

插入数据：

1	insert into tb_book values(null,'SpringCloud实战','2088-05-05','0');

如果日志格式是 ROW , 直接查看数据 , 是查看不懂的；可以在mysqlbinlog 后面加上参数 -vv

1	mysqlbinlog -vv mysqlbin.000002

4、日志删除

对于比较繁忙的系统，由于每天生成日志量大，这些日志如果长时间不清楚，将会占用大量的磁盘空间。

下面我们将会讲解几种删除日志的常见方法：

1、方式一：Reset Master

通过 Reset Master 指令删除全部 binlog 日志，删除之后，日志编号，将从 xxxx.000001重新开始。

查询之前，先查询下日志文件：

执行删除日志指令：

1	Reset Master

执行之后，查看日志文件：

2、方式二：purge master logs to ‘mysqlbin.*’

执行指令 purge master logs to 'mysqlbin.******' ，该命令将删除 ****** 编号之前的所有日志。

3、方式三：purge master logs before ‘yyyy-mm-dd hh24:mi:ss’

执行指令 purge master logs before 'yyyy-mm-dd hh24:mi:ss' ，该命令将删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志。

4、方式四：–expire_logs_days=#

设置参数 --expire_logs_days=# ，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将有利于减少DBA 管理日志的工作量。

配置如下：

3、查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。

默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：

#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启 
general_log=1

#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log，默认生成在mysql的数据目录下（var/lib/mysql）
general_log_file=file_name

在 mysql 的配置文件 /usr/my.cnf 中配置如下内容：

配置完毕之后，在数据库执行以下操作：

select * from tb_book;
select * from tb_book where id = 1;
update tb_book set name = 'lucene入门指南' where id = 5;
select * from tb_book where id < 8;

执行完毕之后，再次来查询日志文件：

4、慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。

long_query_time 默认为 10 秒，最小为 0，精度可以到微秒。

1、文件位置和格式

慢查询日志默认是关闭的 。

1	SHOW VARIABLES LIKE '%slow_query_log%';

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，

如果MySQL重启后则会失效。

全局变量设置，对当前连接不影响

对当前连接立刻生效

如果要永久生效，可以通过两个参数来控制慢查询日志：

# 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
# 假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。
long_query_time=10

log_output=FILE

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

2、日志的读取

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。

查询long_query_time 的值。
执行查询操作
1
select id, title,price,num ,status from tb_item where id = 1;
由于该语句执行时间很短，为0s ，所以不会记录在慢查询日志中。
1
select * from tb_item where title like '%阿尔卡特 (OT-927) 炭黑联通3G手机双卡双待165454%' ;
该SQL语句，执行时长为 26.77s ，超过10s ，所以会记录在慢查询日志文件中。

查询当前系统中有多少条慢查询记录：
1
show global status like '%Slow_queries%';
查看慢查询日志文件
- 直接通过cat\tail 指令查询该日志文件：
- 如果慢查询日志内容很多，直接查看文件，比较麻烦，这个时候可以借助于mysql自带的 mysqldumpslow 工具，来对慢查询日志进行分类汇总。

19、Mysql复制

1、复制概述

复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

2、复制原理

MySQL 的主从复制原理如下：

从上层来看，复制分成三步：

Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中。
主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log 。
slave重做中继日志中的事件，将改变反映它自己的数据。

3、复制优势

MySQL 复制的有点主要包含以下三个方面：

主库出现问题，可以快速切换到从库提供服务。
可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。
可以在从库中执行备份，以避免备份期间影响主库的服务。

4、搭建步骤

1、master

在master 的配置文件（/usr/my.cnf）中，配置如下内容：

#mysql 服务ID,保证整个集群环境中唯一
server-id=1

#mysql binlog 日志的存储路径和文件名
log-bin=/var/lib/mysql/mysqlbin

#错误日志,默认已经开启
#log-err

#mysql的安装目录
#basedir

#mysql的临时目录
#tmpdir

#mysql的数据存放目录
#datadir

#是否只读,1 代表只读, 0 代表读写
read-only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01

执行完毕之后，需要重启Mysql：
1
service mysql restart ；

创建同步数据的账户，并且进行授权操作：

-- 创建主节点的账户完成主从复制、指定对所有数据库（如果相关忽略的在mysql配置文件中配置）、主节点账户的名字、从结点的账户IP、主节点账户的密码
grant replication slave on *.* to 'itcast'@'192.168.192.131' identified by 'itcast';

-- 刷新权限
flush privileges;

查看当前master结点状态信息：
1
show master status;
字段含义：
- File：从哪个日志文件开始推送日志文件
- Position：从哪个位置开始推送日志
- Binlog_Ignore_DB：指定不需要同步的数据库

2、slave

在 slave 端配置文件中，配置如下内容：

#mysql服务端ID,唯一
server-id=2

#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin

执行完毕之后，需要重启Mysql：
1
service mysql restart；

执行如下指令：

1
2

-- 指定哪一个主节点、主节点IP地址、主节点账户名称	、主节点的账户密码、主节点二进制日志名称、主节点日志的位置
change master to master_host= '192.168.192.130', master_user='itcast', master_password='itcast', master_log_file='mysqlbin.000001', master_log_pos=413;

指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

开启同步操作：
1
2
3
start slave;

show slave status\G;
停止同步操作：
1
stop slave;

3、验证同步操作

在主库中创建数据库，创建表，并插入数据：

create database db01;

user db01;

create table user(
	id int(11) not null auto_increment,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;

insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');

在从库中查询数据，进行验证：

在从库中，可以查看到刚才创建的数据库：

在该数据库中，查询user表中的数据：

20、综合案例

1、需求分析

在业务系统中，需要记录当前业务系统的访问日志，该访问日志包含：操作人，操作时间，访问类，访问方法，请求参数，请求结果，请求结果类型，请求时长等信息。记录详细的系统访问日志，主要便于对系统中的用户请求进行追踪，并且在系统的管理后台可以查看到用户的访问记录。

记录系统中的日志信息，可以通过Spring 框架的AOP来实现。具体的请求处理流程，如下：

2、搭建案例环境

1、数据库表

-- 创建数据库
CREATE DATABASE mysql_demo DEFAULT CHARACTER SET utf8mb4 ；

-- 品牌表
CREATE TABLE `brand` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '品牌名称',
  `first_char` varchar(1) DEFAULT NULL COMMENT '品牌首字母',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 商品表
CREATE TABLE `item` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `title` varchar(100) NOT NULL COMMENT '商品标题',
  `price` double(10,2) NOT NULL COMMENT '商品价格，单位为：元',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `categoryid` bigint(10) NOT NULL COMMENT '所属类目，叶子类目',
  `status` varchar(1) DEFAULT NULL COMMENT '商品状态，1-正常，2-下架，3-删除',
  `sellerid` varchar(50) DEFAULT NULL COMMENT '商家ID',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  `updatetime` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';

-- 用户表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 操作日志表
CREATE TABLE `operation_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `operate_class` varchar(200) DEFAULT NULL COMMENT '操作类',
  `operate_method` varchar(200) DEFAULT NULL COMMENT '操作方法',
  `return_class` varchar(200) DEFAULT NULL COMMENT '返回值类型',
  `operate_user` varchar(20) DEFAULT NULL COMMENT '操作用户',
  `operate_time` varchar(20) DEFAULT NULL COMMENT '操作时间',
  `param_and_value` varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值',
  `cost_time` bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位 ms',
  `return_value` varchar(200) DEFAULT NULL COMMENT '返回值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

2、pom.xml

<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <maven.compiler.source>1.7</maven.compiler.source>
  <maven.compiler.target>1.7</maven.compiler.target>

  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
  <spring.version>5.0.2.RELEASE</spring.version>
  <slf4j.version>1.6.6</slf4j.version>
  <log4j.version>1.2.12</log4j.version>
  <mybatis.version>3.4.5</mybatis.version>
</properties>

<dependencies> <!-- spring -->
  <dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.6.8</version>
  </dependency>

  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.16</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context-support</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
  </dependency>

  <dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>3.1.0</version>
    <scope>provided</scope>
  </dependency>

  <dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>jsp-api</artifactId>
    <version>2.0</version>
    <scope>provided</scope>
  </dependency>


  <dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>${log4j.version}</version>
  </dependency>

  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${mybatis.version}</version>
  </dependency>

  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.0</version>
  </dependency>

  <dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
  </dependency>

  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.5</version>
  </dependency>

  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-core</artifactId>
    <version>2.9.0</version>
  </dependency>

  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.9.0</version>
  </dependency>

  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-annotations</artifactId>
    <version>2.9.0</version>
  </dependency>
</dependencies>

 <build>
   <plugins>
     <plugin>
       <groupId>org.apache.tomcat.maven</groupId>
       <artifactId>tomcat7-maven-plugin</artifactId>
       <version>2.2</version>
       <configuration>
         <port>8080</port>
         <path>/</path>
         <uriEncoding>utf-8</uriEncoding>
       </configuration>
     </plugin>
   </plugins>
 </build>

3、通过AOP记录操作日志

1、自定义注解

通过自定义注解，来标示方法需不需要进行记录日志，如果该方法在访问时需要记录日志，则在该方法上标示该注解既可。

@Inherited
@Documented
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface OperateLog {
}

2、定义通知类

@Component
@Aspect
public class OperateAdvice {
   
   private static Logger log = Logger.getLogger(OperateAdvice.class);
   
   @Autowired
   private OperationLogService operationLogService;
   

   @Around("execution(* cn.itcast.controller.*.*(..)) && @annotation(operateLog)")
   public Object insertLogAround(ProceedingJoinPoint pjp , OperateLog operateLog) throws Throwable{
      System.out.println(" ************************ 记录日志 [start]  ****************************** ");
      
      OperationLog op = new OperationLog();
      
      DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

      op.setOperateTime(sdf.format(new Date()));
      op.setOperateUser(DataUtils.getRandStr(8));
      
      op.setOperateClass(pjp.getTarget().getClass().getName());
      op.setOperateMethod(pjp.getSignature().getName());
      
      //获取方法调用时传递的参数
      Object[] args = pjp.getArgs();
      op.setParamAndValue(Arrays.toString(args));

      long start_time = System.currentTimeMillis();

      //放行
      Object object = pjp.proceed();

      long end_time = System.currentTimeMillis();
      op.setCostTime(end_time - start_time);

      if(object != null){
         op.setReturnClass(object.getClass().getName());
         op.setReturnValue(object.toString());
      }else{
         op.setReturnClass("java.lang.Object");
         op.setParamAndValue("void");
      }

      log.error(JsonUtils.obj2JsonString(op));

      operationLogService.insert(op);
      
      System.out.println(" ************************** 记录日志 [end]  *************************** ");
      
      return object;
   }
   
}

3、方法上加注解

在需要记录日志的方法上加上注解@OperateLog。

@OperateLog
@RequestMapping("/insert")
public Result insert(@RequestBody Brand brand){
    try {
        brandService.insert(brand);
        return new Result(true,"操作成功");
    } catch (Exception e) {
        e.printStackTrace();
        return new Result(false,"操作失败");
    }
}

4、日志查询后端代码实现

1、Mapper接口

public interface OperationLogMapper {

    public void insert(OperationLog operationLog);

    public List<OperationLog> selectListByCondition(Map dataMap);

    public Long countByCondition(Map dataMap);

}

2、Mapper.xml 映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.itcast.mapper.OperationLogMapper" >

    <insert id="insert" parameterType="operationLog">
        INSERT INTO operation_log(id,return_value,return_class,operate_user,operate_time,param_and_value,
        operate_class,operate_method,cost_time)
      VALUES(NULL,#{returnValue},#{returnClass},#{operateUser},#{operateTime},#{paramAndValue},
        #{operateClass},#{operateMethod},#{costTime})
    </insert>

    <select id="selectListByCondition" parameterType="map" resultType="operationLog">
      select
        id ,
        operate_class as operateClass ,
        operate_method as operateMethod,
        return_class as returnClass,
        operate_user as operateUser,
        operate_time as operateTime,
        param_and_value as paramAndValue,
        cost_time as costTime,
        return_value as returnValue
      from operation_log
      <include refid="oplog_where"/>
      limit #{start},#{size}
    </select>


    <select id="countByCondition" resultType="long" parameterType="map">
        select count(*) from operation_log
        <include refid="oplog_where"/>
    </select>


    <sql id="oplog_where">
        <where>
            <if test="operateClass != null and operateClass != '' ">
                and operate_class = #{operateClass}
            </if>
            <if test="operateMethod != null and operateMethod != '' ">
                and operate_method = #{operateMethod}
            </if>
            <if test="returnClass != null and returnClass != '' ">
                and return_class = #{returnClass}
            </if>
            <if test="costTime != null">
                and cost_time =  #{costTime}
            </if>
        </where>
    </sql>

</mapper>

3、Service

@Service
@Transactional
public class OperationLogService {

    //private static Logger logger = Logger.getLogger(OperationLogService.class);

    @Autowired
    private OperationLogMapper operationLogMapper;

    //插入数据
    public void insert(OperationLog operationLog){
        operationLogMapper.insert(operationLog);
    }

    //根据条件查询
    public PageResult selectListByCondition(Map dataMap, Integer pageNum , Integer pageSize){

       if(paramMap ==null){
            paramMap = new HashMap();
        }
        paramMap.put("start" , (pageNum-1)*rows);
        paramMap.put("rows",rows);

        Object costTime = paramMap.get("costTime");
        if(costTime != null){
            if("".equals(costTime.toString())){
                paramMap.put("costTime",null);
            }else{
                paramMap.put("costTime",new Long(paramMap.get("costTime").toString()));
            }
        }

        System.out.println(dataMap);


        long countStart = System.currentTimeMillis();
        Long count = operationLogMapper.countByCondition(dataMap);
        long countEnd = System.currentTimeMillis();
        System.out.println("Count Cost Time : " + (countEnd-countStart)+" ms");


        List<OperationLog> list = operationLogMapper.selectListByCondition(dataMap);
        long queryEnd = System.currentTimeMillis();
        System.out.println("Query Cost Time : " + (queryEnd-countEnd)+" ms");


        return new PageResult(count,list);

    }

}

4、Controller

@RestController
@RequestMapping("/operationLog")
public class OperationLogController {

    @Autowired
    private OperationLogService operationLogService;

    @RequestMapping("/findList")
    public PageResult findList(@RequestBody Map dataMap, Integer pageNum , Integer pageSize){
        PageResult page = operationLogService.selectListByCondition(dataMap, pageNum, pageSize);
        return page;
    }

}

5、日志查询前端代码实现

前端代码使用 BootStrap + AdminLTE 进行布局，使用Vuejs 进行视图层展示。

1、js

<script>
   var vm = new Vue({
       el: '#app',
       data: {
           dataList:[],
           searchEntity:{
               operateClass:'',
               operateMethod:'',
               returnClass:'',
               costTime:''
           },

           page: 1,  //显示的是哪一页
           pageSize: 10, //每一页显示的数据条数
           total: 150, //记录总数
           maxPage:8  //最大页数
       },
       methods: {
           pageHandler: function (page) {
               this.page = page;
               this.search();
           },

           search: function () {
               var _this = this;
               this.showLoading();
               axios.post('/operationLog/findList.do?pageNum=' + _this.page + "&pageSize=" + _this.pageSize, _this.searchEntity).then(function (response) {
                   if (response) {
                       _this.dataList = response.data.dataList;
                       _this.total = response.data.total;
                       _this.hideLoading();
                   }
               })
           },

           showLoading: function () {
               $('#loadingModal').modal({backdrop: 'static', keyboard: false});
           },

           hideLoading: function () {
               $('#loadingModal').modal('hide');
           },
       },

       created:function(){
           this.pageHandler(1);
       }
   });

</script>

2、列表数据展示

<tr v-for="item in dataList">
    <td><input name="ids" type="checkbox"></td>
    <td>{{item.id}}</td>
    <td>{{item.operateClass}}</td>
    <td>{{item.operateMethod}}</td>
    <td>{{item.returnClass}}</td>
    <td>{{item.returnValue}}</td>
    <td>{{item.operateUser}}</td>
    <td>{{item.operateTime}}</td>
    <td>{{item.costTime}}</td>
    <td class="text-center">
        <button type="button" class="btn bg-olive btn-xs">详情</button>
        <button type="button" class="btn bg-olive btn-xs">删除</button>
    </td>
</tr>

3、分页插件

<div class="wrap" id="wrap">
    <zpagenav v-bind:page="page" v-bind:page-size="pageSize" v-bind:total="total"
              v-bind:max-page="maxPage"  v-on:pagehandler="pageHandler">
    </zpagenav>
</div>

6、联调测试

可以通过postman来访问业务系统，再查看数据库中的日志信息，验证能不能将用户的访问日志记录下来。

7、分析性能问题

系统中用户访问日志的数据量，随着时间的推移，这张表的数据量会越来越大，因此我们需要根据业务需求，来对日志查询模块的性能进行优化。

1、分页查询优化

由于在进行日志查询时，是进行分页查询，那也就意味着，在查看时，至少需要查询两次：

查询符合条件的总记录数。–> count 操作
查询符合条件的列表数据。–> 分页查询 limit 操作

通常来说，count() 都需要扫描大量的行（意味着需要访问大量的数据）才能获得精确的结果，因此是很难对该SQL进行优化操作的。如果需要对count进行优化，可以采用另外一种思路，可以==增加汇总表==，或者==redis缓存来专门记录该表对应的记录数==，这样的话，就可以很轻松的实现汇总数据的查询，而且效率很高。

但是这种统计并不能保证百分之百的准确。对于数据库的操作，“快速、精确、实现简单”，三者永远只能满足其二，必须舍掉其中一个。

2、条件查询优化

针对于条件查询，需要==对查询条件，及排序字段建立索引==。

3、读写分离

通过==主从复制集群，来完成读写分离，使写操作走主节点，而读操作，走从节点==。

4、MySQL服务器优化

5、应用优化

8、性能优化 - 分页

1、优化count

创建一张表用来记录日志表的总数据量：

1
2
3

create table log_counter(
	logcount bigint not null
)engine = innodb default CHARSET = utf8;

在每次插入数据之后，更新该表：

1
2
3

<update id="updateLogCounter" >
    update log_counter set logcount = logcount + 1
</update>

在进行分页查询时，获取总记录数，从该表中查询既可。

1
2
3

<select id="countLogFromCounter" resultType="long">
    select logcount from log_counter limit 1
</select>

2、优化 limit

在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。一个非常常见，而又非常头疼的分页场景就是 “limit 1000000,10” ，此时MySQL需要搜索出前1000010 条记录后，仅仅需要返回第 1000001 到 1000010 条记录，前1000000 记录会被抛弃，查询代价非常大。

当点击比较靠后的页码时，就会出现这个问题，查询效率非常慢。

优化前的SQL：

1	select * from operation_log limit 3000000 , 10;

将上述SQL优化为：（使用子查询的方式进行优化）

1	select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ;

<select id="selectListByCondition" parameterType="map" resultType="operationLog">
  select
    id ,
    operate_class as operateClass ,
    operate_method as operateMethod,
    return_class as returnClass,
    operate_user as operateUser,
    operate_time as operateTime,
    param_and_value as paramAndValue,
    cost_time as costTime,
    return_value as returnValue
  from operation_log t,
    
  (select id from operation_log 
  <where>
    <include refid="oplog_where"/>
  </where>
  order by id limit #{start},#{rows}) b  where t.id = b.id  
</select>

9、性能优化 - 索引

当根据操作人进行查询时，查询的效率很低，耗时比较长。原因就是因为在创建数据库表结构时，并没有针对于操作人字段建立索引。

1	CREATE INDEX idx_user_method_return_cost ON operation_log(operate_user,operate_method,return_class,cost_time);

同上，为了查询效率高，我们也需要对 ==操作方法==、==返回值类型==、==操作耗时== 等字段进行创建索引，以提高查询效率。

CREATE INDEX idx_optlog_method_return_cost ON operation_log(operate_method,return_class,cost_time);

CREATE INDEX idx_optlog_return_cost ON operation_log(return_class,cost_time);

CREATE INDEX idx_optlog_cost ON operation_log(cost_time);

10、性能优化 - 排序

在查询数据时，如果业务需求中需要我们对结果内容进行了排序处理 , 这个时候，我们还需要==对排序的字段建立适当的索引==，来提高排序的效率。

11、性能优化 - 读写分离

1、概述

在Mysql主从复制的基础上，可以使用读写分离来降低单台Mysql节点的压力，从而来提高访问效率，读写分离的架构如下：

对于读写分离的实现，可以通过Spring AOP 来进行动态的切换数据源，进行操作：

2、实现方式

db.properties

jdbc.write.driver=com.mysql.jdbc.Driver
jdbc.write.url=jdbc:mysql://192.168.142.128:3306/mysql_demo
jdbc.write.username=root
jdbc.write.password=itcast

jdbc.read.driver=com.mysql.jdbc.Driver
jdbc.read.url=jdbc:mysql://192.168.142.129:3306/mysql_demo
jdbc.read.username=root
jdbc.read.password=itcast

applicationContext-datasource.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">


    <!-- 配置数据源 - Read -->
    <bean id="readDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"  lazy-init="true">
        <property name="driverClass" value="${jdbc.read.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.read.url}"></property>
        <property name="user" value="${jdbc.read.username}"></property>
        <property name="password" value="${jdbc.read.password}"></property>
    </bean>


    <!-- 配置数据源 - Write -->
    <bean id="writeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  destroy-method="close"  lazy-init="true">
        <property name="driverClass" value="${jdbc.write.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.write.url}"></property>
        <property name="user" value="${jdbc.write.username}"></property>
        <property name="password" value="${jdbc.write.password}"></property>
    </bean>


    <!-- 配置动态分配的读写 数据源 -->
    <bean id="dataSource" class="cn.itcast.aop.datasource.ChooseDataSource" lazy-init="true">
        <property name="targetDataSources">
            <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                <entry key="write" value-ref="writeDataSource"/>
                <entry key="read" value-ref="readDataSource"/>
            </map>
        </property>

        <property name="defaultTargetDataSource" ref="writeDataSource"/>

        <property name="methodType">
            <map key-type="java.lang.String">
                <entry key="read" value=",get,select,count,list,query,find"/>
                <entry key="write" value=",add,create,update,delete,remove,insert"/>
            </map>
        </property>
    </bean>

</beans>

ChooseDataSource

public class ChooseDataSource extends AbstractRoutingDataSource {

    public static Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();

    /**
     * 实现父类中的抽象方法，获取数据源名称
     * @return
     */
    protected Object determineCurrentLookupKey() {
        return DataSourceHandler.getDataSource();
    }

    // 设置方法名前缀对应的数据源
    public void setMethodType(Map<String, String> map) {
        for (String key : map.keySet()) {
            List<String> v = new ArrayList<String>();
            String[] types = map.get(key).split(",");
            for (String type : types) {
                if (!StringUtils.isEmpty(type)) {
                    v.add(type);
                }
            }
            METHOD_TYPE_MAP.put(key, v);
        }
        System.out.println("METHOD_TYPE_MAP : "+METHOD_TYPE_MAP);
    }
}

DataSourceHandler

public class DataSourceHandler {

    // 数据源名称
    public static final ThreadLocal<String> holder = new ThreadLocal<String>();

    /**
     * 在项目启动的时候将配置的读、写数据源加到holder中
     */
    public static void putDataSource(String datasource) {
        holder.set(datasource);
    }

    /**
     * 从holer中获取数据源字符串
     */
    public static String getDataSource() {
        return holder.get();
    }
}

DataSourceAspect

@Aspect
@Component
@Order(-9999)
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DataSourceAspect {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 配置前置通知,使用在方法aspect()上注册的切入点
     */
    @Before("execution(* cn.itcast.service.*.*(..))")
    @Order(-9999)
    public void before(JoinPoint point) {
        
        String className = point.getTarget().getClass().getName();
        String method = point.getSignature().getName();
        logger.info(className + "." + method + "(" + Arrays.asList(point.getArgs())+ ")");

        try {
            for (String key : ChooseDataSource.METHOD_TYPE_MAP.keySet()) {
                for (String type : ChooseDataSource.METHOD_TYPE_MAP.get(key)) {
                    if (method.startsWith(type)) {
                        System.out.println("key : " + key);
                        DataSourceHandler.putDataSource(key);
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

通过 @Order(-9999) 注解来控制事务管理器，与该通知类的加载顺序，需要让通知类，先加载，来判定使用哪个数据源。

3、验证

在主库和从库中，执行如下SQL语句，来查看是否读的时候，从从库中读取；写入操作的时候，是否写入到主库。

1	show status like 'Innodb_rows_%' ;

4、原理

12、性能优化 - 应用优化

1、缓存

可以在业务系统中使用==redis==或者==框架本身的一级二级缓存==来做缓存，缓存一些基础性的数据，来降低关系型数据库的压力，提高访问效率。

2、全文检索

如果业务系统中的数据量比较大（达到千万级别），这个时候，如果再对数据库进行查询，特别是进行分页查询，速度将变得很慢（因为在分页时首先需要count求合计数），为了提高访问效率，这个时候，可以考虑加入==Solr== 或者 ==ElasticSearch==全文检索服务，来提高访问效率。

3、非关系数据库

也可以考虑将非核心（重要）数据，存在 ==MongoDB== 中，这样可以提高插入以及查询的效率。

0、在Linux系统安装Mysql

1、下载Linux 安装包

在官网下载mysql的安装包

2、安装MySQL

-- 卸载 Linux 中预安装的 mysql
rpm -qa | grep -i mysql
rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps

-- 上传 mysql 的安装包
alt + p -- 切换到ftp模式
put  E:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar

-- 解压 mysql 的安装包
mkdir mysql
tar -xvf MySQL-5.6.22-1.el6.i686.rpm-bundle.tar -C /root/mysql

-- 安装依赖包
yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 --setopt=protected_multilib=false
yum  update libstdc++-4.4.7-4.el6.x86_64

-- 安装 mysql-client
rpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm

-- 安装 mysql-server
rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm

3、启动 MySQL 服务

-- 开启mysql服务
service mysql start

-- 停止mysql服务
service mysql stop

-- 查看mysql的状态
service mysql status

-- 重启mysql
service mysql restart

4、登录MySQL

-- mysql 安装完成之后, 会自动生成一个随机的密码, 并且保存在一个密码文件中 : /root/.mysql_secret
cd /root/.mysql_secret
cat

-- 登陆mysql
mysql -u root -p 

-- 登录之后, 修改密码
set password = password('your password');

-- 授权远程访问
grant all privileges on *.* to 'root' @'%' identified by 'your password';
flush privileges;

5、Mysql 的用户与权限管理

1、MySQL的用户管理

1、创建用户

1	create user zhang3 identified by '123123';

表示创建名称为zhang3的用户，密码设为123123；

2、查看用户

1
2
3

select host,user,password,select_priv,insert_priv,drop_priv from mysql.user;

select * from user\G;

将 user 中的数据以行的形式显示出来(针对列很长的表可以采用这个方法 )

host：表示连接类型
- % 表示所有远程通过 TCP方式的连接
- IP 地址如 (192.168.1.2,127.0.0.1) 通过制定ip地址进行的TCP方式的连接
- ::1 IPv6的本地ip地址等同于IPv4的 127.0.0.1
- localhost 本地方式通过命令行方式的连接，比如mysql -u xxx -p 123xxx 方式的连接。
user：表示用户名
- 同一用户通过不同方式链接的权限是不一样的。
password：密码
- 所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为MYSQLSHA1 ，不可逆。
- mysql 5.7 的密码保存到 authentication_string，字段中不再使用password 字段。
select_priv , insert_priv等
- 为该用户所拥有的权限。

3、设置密码

修改当前用户的密码：

1	set password =password('123456')

修改某个用户的密码：

update mysql.user set password=password('123456') where user='li4';

-- 所有通过user表的修改，必须用该命令才能生效。
flush privileges;

4、修改用户

修改用户名：

update mysql.user set user='li4' where user='wang5';

-- 所有通过user表的修改，必须用该命令才能生效。
flush privileges;

5、删除用户

1	drop user li4 ;

不要通过delete from user u where user=’li4’ 进行删除，系统会有残留信息保留。

2、权限管理

1、授予权限

授权命令：

1	grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;

该权限如果发现没有该用户，则会直接新建一个用户。

比如：

-- 给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,drop on atguigudb.* to li4@localhost ;

-- 授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，密码设为123.
grant all privileges on *.* to joe@'%' identified by '123';

就算 all privileges 了所有权限，grant_priv 权限也只有 root 才能拥有。

给 root 赋连接口令 grant all privileges on *.* to root@'%' ;后新建的连接没有密码，需要设置密码才能远程连接。

1	update user set password=password('root') where user='root' and host='%';

2、收回权限

-- 收回权限命令：
revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ;

-- 若赋的全库的表就 收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;

-- 收回mysql库下的所有表的插删改查权限
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost;

对比赋予权限的方法：必须用户重新登录后才能生效

3、查看权限

-- 查看当前用户权限
show grants;

-- 查看某用户的全局权限
select * from user ;

-- 查看某用户的某库的权限
select * from  db;

-- 查看某用户的某个表的权限
select * from tables_priv;

3、通过工具远程访问

先 ping 一下数据库服务器的ip 地址确认网络畅通。
关闭数据库服务的防火墙
1
service iptables stop

确认Mysql中已经有可以通过远程登录的账户

1	select * from mysql.user where user='li4' and host='%';

如果没有用户，先执行如下命令：

1	grant all privileges on . to li4@'%' identified by '123123';

测试连接：

6、修改字符集问题

1、查看字符集

1 2	show variables like 'character%'; show variables like '%char%';

默认的是客户端和服务器都用了latin1，所以会乱码。

2、修改my.cnf

在/usr/share/mysql/ 中找到my.cnf的配置文件，

拷贝其中的my-huge.cnf 到 /etc/ 并命名为my.cnf

mysql 优先选中 /etc/ 下的配置文件

然后修改my.cnf：

[client]

default-character-set=utf8

[mysqld]

character_set_server=utf8

character_set_client=utf8

collation-server=utf8_general_ci

[mysql]

default-character-set=utf8

3、重新启动mysql

但是原库的设定不会发生变化，参数修改之对新建的数据库生效

4、已生成的库表字符集如何变更

-- 修改数据库的字符集
alter database mytest character set 'utf8';

-- 修改数据表的字符集
alter table user convert to  character set 'utf8';

但是原有的数据如果是用非’utf8’编码的话，数据本身不会发生改变。

7、Mysql的一些杂项配置

1、大小写问题

1	SHOW VARIABLES LIKE '%lower_case_table_names%'

windows系统默认大小写不敏感，但是linux系统是大小写敏感的

默认为0，大小写敏感。
设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和DB进行查找。
设置2，创建的表和DB依据语句上格式存放，凡是查找都是转换为小写进行。

设置变量常采用 setlower_case_table_names = 1；的方式，但此变量是只读权限，所以需要在配置文件中改。

当想设置为大小写不敏感时，要在my.cnf这个配置文件 [mysqld] 中加入 lower_case_table_names = 1 ，然后重启服务器。

但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则更改后将找不到数据库名。

在进行数据库参数设置之前，需要掌握这个参数带来的影响，切不可盲目设置。

2、(生产环境)sql_mode

MySQL的sql_mode合理设置

sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

使用 set sql_mode=ONLY_FULL_GROUP_BY; 的方式设置会将之前的设置覆盖掉ONLY_FULL_GROUP_BY; 的方式设置会将之前的设置覆盖掉

同时设置多个限制：

1	set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO';

sql_mode常用值如下：

ONLY_FULL_GROUP_BY：
- 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO：
- 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES：
- 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：
- 在严格模式下，不允许日期和月份为零
NO_ZERO_DATE：
- 设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO：
- 在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：
- 禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION：
- 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT：
- 将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES：
- 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
ORACLE：
- 设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

3、查看当前系统的性能状态

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

type	含义
index_merge	在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中
ref_or_null	对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
index_subquery	利用索引来关联子查询，不再全表扫描。
unique_subquery	该联接类型类似于index_subquery。子查询中的唯一索引

Mysql 高级篇

1、索引（Index）

1、索引概述

2、索引优势劣势

3、索引结构

1、InnoDB的自调优

2、BTREE（B树）结构

3、B+TREE（B+树）结构

4、MySQL中的B+Tree

5、聚簇索引与非聚簇索引（了解）

6、full-text全文索引

7、Hash索引

8、R-Tree索引

4、索引分类

5、索引语法

1、创建索引

2、查看索引

3、删除索引

4、ALTER命令（添加索引）

6、索引设计原则

3、 查看视图

4、删除视图

3、存储过程（Procedure）和函数（Function）

1、存储过程和函数概述

2、存储过程

1、创建存储过程

2、调用存储过程

3、查看存储过程

4、删除存储过程

5、在存储过程的sql当中的语法

1、变量

3、传递参数

4、case结构

5、while循环

6、repeat结构

7、loop语句

8、leave语句

9、游标/光标（Cursor）

3、存储函数

4、触发器（Trigger）

1、介绍

2、创建触发器

3、删除触发器

4、查看触发器

5、Mysql的体系结构概览

6、存储引擎

1、存储引擎概述

2、各种存储引擎特性

1、InnoDB

2、MyISAM

3、MEMORY

4、MERGE

3、存储引擎的选择

7、优化SQL步骤

1、查看SQL执行频率

2、定位低效率执行SQL

3、explain分析执行计划

1、环境准备

2、explain 之 id

3、explain 之 select_type

1、select_type 之 SIMPLE

2、select_type 之 PRIMARY

3、select_type 之 SUBQUERY

4、select_type 之 DERIVED

5、select_type 之 UNION

6、select_type 之 UNION RESULT

7、其他不常见的类型：select_type 之 DEPENDENT SUBQUERY

8、其他不常见的类型：select_type 之 UNCACHEABLE SUBQUREY

4、explain 之 table

5、explain 之 type

1、type 之 NULL

2、type 之 system

3、type 之 const

4、type 之 eq_ref

5、type 之 ref

6、type 之 range

7、type 之 index

8、type 之 all

9、补充：type 之 index_merge

10、补充：type 之 ref_or_null

3、查看视图

12、补充：type 之 unique_subquery

1、全值匹配，对索引中所有列都指定具体值

4、不要在索引列上进行运算操作，索引将失效

6、尽量使用覆盖索引，避免`select *`

7、用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

12、单列索引和复合索引

2、where 之后使用的是 `用in 还是 exists`