MySQL高级笔记

参照黑马程序员MySQL高级部分视频做的笔记

 

MySQL高级

一、索引

索引是什么？

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引的优势和劣势

优势：

可以提高数据检索的效率，降低数据库的**IO成本**，类似于书的目录。

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

被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。 劣势：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

索引类型

主键索引 索引列中的值必须是唯一的，不允许有空值。

普通索引 MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引 索引列中的值必须是唯一的，但是允许为空值。

全文索引 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引 MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引 在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

其他（按照索引列数量分类） 单列索引

组合索引

组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

索引的数据结构

Hash表

二叉查找树

平衡二叉树

B树：改造二叉树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：

B树的节点中存储着多个元素，每个内节点有多个分叉。

节点中的元素包含键值和数据，节点中的键值从**大到小**排列。也就是说，在所有的节点都储存数据。

父节点当中的元素不会出现在子节点中。

所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

B+树：改造B树

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

B树：非叶子节点和叶子节点都会存储数据。 B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

Mysql的索引实现

介绍完了索引数据结构，那肯定是要带入到Mysql里面看看真实的使用场景的，所以这里分析Mysql的两种存储引擎的索引实现：MyISAM索引和InnoDB索引

MyIsam索引

以一个简单的user表为例。user表存在两个索引，id列为主键索引，age列为普通索引

CREATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;
​

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

主键索引

表user的索引存储在索引文件user.MYI中，数据文件存储在数据文件 user.MYD中。

简单分析下查询时的磁盘IO情况：

根据主键等值查询数据：

select * from user where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项。（1次磁盘IO）

4. 从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）

5. 将记录返给客户端。

   磁盘IO次数：3次索引检索+记录数据检索。

根据主键范围查询数据：

select * from user where id between 28 and 47;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历比较16<28，18<28，28=28<47。查找到值等于28的索引项。

   根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）

   我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28<47=47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）

5. 最后得到两条符合筛选条件，将查询结果集返给客户端。

   磁盘IO次数：4次索引检索+记录数据检索。

备注：以上分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。

辅助索引 在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

InnoDB索引

主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

1.在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
2.如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
3.如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

 

InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

等值查询数据：

select * from user_innodb where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO数量：3次。

辅助索引 除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

以表user_innodb的age列为例，age索引的索引结果如下图。

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

画图分析等值查询的情况：

select * from t_user_innodb where age=19;

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

磁盘IO数：辅助索引3次+获取记录回表3次

组合索引 还是以自己创建的一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

组合索引的数据结构：

组合索引的查询过程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

最左匹配原则：

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

总结

看到这里，你是不是对于自己的sql语句里面的索引的有了更多优化想法呢。比如：

避免回表

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）

如果在一个场景下，select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。

这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。

联合索引的使用

联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引。

1. 联合索引的使用

2. 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

二、视图

1、视图概述

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

1.1 视图的概念

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图是存储在数据库中的查询的SQL语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。

1.2 视图的作用

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图的作用归纳为如下几点。

1、简单性

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2、安全性

视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行的方法，如下：

（1）在表中增加一个标志用户名的列。

（2）建立视图，使用户只能看到标有自己用户名的行。

（3）把视图授权给其他用户。

3、逻辑数据独立性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立。

（1）如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

（2）如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。

（3）如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序可以不动。

（4）如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而使数据库可以不动。

2、创建视图

创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

2.1 查看创建视图的权限

创建视图需要具有CREATE VIEW的权限。同时应该具有查询涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息。查询语法如下：

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';

参数说明：

（1）Select_priv：属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。

（2）Create_view_priv：属性表示用户是否具有CREATE VIEW权限；

（3）mysql.user：表示MySQL数据库下面的user表。

（4）用户名：参数表示要查询是否拥有权限的用户，该参数需要用单引号引起来。

示例：查询MySQL中root用户是否具有创建视图的权限。

SELECT * FROM mysql.user WHERE user='root';

2.2 创建视图

MySQL中，创建视图是通过CREATE VIEW语句实现的。其语法如下：

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

VIEW 视图名[(属性清单)] AS SELECT语句

[WITH [CASCADED|LOCAL] CHECK OPTION];

参数说明：

（1）ALGORITHM：可选项，表示视图选择的算法。

（2）视图名：表示要创建的视图名称。

（3）属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。

（4）SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。

（5）WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

示例：创建视图。

CREATE OR REPLACE VIEW view_user
AS
	SELECT id,name FROM tb_user;

示例：创建视图同时，指定属性清单。

CREATE OR REPLACE VIEW view_user (a_id,a_name)
AS
	SELECT id,name FROM tb_user;

 

3、修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

示例：修改视图

ALTER VIEW view_user
AS
	SELECT id,name FROM tb_user where id  in (select id from tb_user);

说明：ALTER VIEW语句改变了视图的定义，该语句与CREATE OR REPLACE VIEW语句有着同样的限制，如果删除并重新创建一个视图，就必须重新为它分配权限。

4、删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。

示例：删除视图。

DROP VIEW IF EXISTS view_user;

三、存储过程

在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 ;

1、存储过程的概念

存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;

2、存储过程的优缺点

优点 :

1. 由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;

2. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;

3. 由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;

4. 可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;

5. 代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;

6. 增强安全性 : 1.通过向用户授予对存储过程 (而不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ; 2.提高代码安全 , 防止 SQL注入 (但未彻底解决 , 例如将数据操作语言 DML 附加到输入参数) ;

   3.SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一 失 , 还是应该传递至数据库前得到附加验证) ;

缺点 :

1. 如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数 , 等等

2. 可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

3、编写简单存储过程

//创建一个存储过程
create procedure GetUsers()
begin 
select * from user; 
end;

//调用存储过程
call GetUsers();

//删除存储过程
drop procedure if exists GetUsers;

4、带参数的存储过程

MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;

下面的存储过程接受三个参数 , 分别用于获取用户表的最小 , 平均 , 最大分数 , 每个参数必须具有指定的类型 , 这里使用十进制值(decimal(8,2)) , 关键字 OUT 指出相应的参数用来从存储过程传出

create procedure GetScores(
	out minScore decimal(8,2),
	out avgScore decimal(8,2),
	out maxScore decimal(8,2)
)
begin
	select min(score) into minScore from user;
	select avg(score) into avgScore from user;
	select max(score) into maxScore from user;
end;

调用此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所示 :

call GetScores(@minScore, @avgScore, @maxScore);

该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量 @minScore, @avgScore, @maxScore , 然后即可调用显示该变量的值 :

select @minScore, @avgScore, @maxScore;

使用 IN 参数 , 输入一个用户 id , 返回该用户的名字 :

create procedure GetNameByID(
	in userID int,
	out userName varchar(200)
)
begin
	select name from user
	where id = userID
	into userName;
end;

调用存储过程 :

call GetNameByID(1, @userName);
select @userName;

5、复杂一点示例

根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :

create procedure GetPriceByID(
	in prodID int,
	in isDisc boolean,
	out prodPrice decimal(8,2)
) 
begin
	declare tmpPrice decimal(8,2);
	declare prodDiscRate decimal(8,2);
	set prodDiscRate = 0.88;

	select price from products
	where id = prodID
	into tmpPrice;
	
	if isDisc then
		select tmpPrice*prodDiscRate into tmpPrice;
	end if;
	
	select tmpPrice into prodPrice;

end;

该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调用如下 :

call GetPriceByID(1, true, @prodPrice);
select @prodPrice;

6、DELIMITER

MySql 的命令行客户机的语句分隔符默认为分号 ; ，而实用程序也是用 ; 作为分隔符，这会使得存储过程的 SQL 出现语法错误，使用 DELIMITER $$ 告诉命令行实用程序将 $$ 作为新的语句结束分隔符，最后再使用 DELIMITER ; 改回来，如下所示：

DELIMITER $$ 
create procedure getUsers() begin select * from user; 
end $$ 
DELIMITER ;


DELIMITER $$
CREATE PROCEDURE getcount()
BEGIN
SELECT
*
FROM
contract_user;
END $$
DELIMITER ;

CALL getcount();

---

一、IF语句

语法格式：

IF expr_condition THEN statement_list 
    [ELSEIF expr_condition THEN statement_list] 
    [ELSE statement_list] 
END IF;

参数：

Expr_condition：表示判断条件 
Statement_list：表示SQL语句列表，它可以包括一个或多个语句
注意事项：
if以end if结尾
如果，expr_condition求值为TRUE，相应的SQL语句列表就会被执行，如果，没有expr_condition匹配，则ELSE子句李的语句列表被执行
注意，MySQL中还有一个IF()函数，不同于这里的IF语句

演示案例

IF val IS NULL 
  THEN SELECT ‘val is NULL’; 
  ELSE SELECT ‘val is not NULL’; 
END IF; 

二、CASE语句

另一个进行条件判断的语句，该语句有两种语句格式 格式一：

CASE case_expr 
    WHEN when_value THEN statement_list 
    [WHEN when_value THEN statement_list]…… 
    [ELSE statement_list] 
END CASE

参数：
Case_expr，表示条件判断的表达式，决定了哪一个WHEN自己会被执行 
When_value，表示表达式可能的值，如果，某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement中的语句 
Statement_list，表示不同when_value值的执行语句

演示案例

CASE val 
   WHEN 1 THEN SELECT ‘val is 1’; 
   WHEN 2 THEN SELECT ‘val is 2’; 
   ELSE SELECT ‘val is not 1 or 2’; 
END CASE;

格式二：

CASE 
     WHEN expr_condition THEN statement_list 
     [WHEN expr_condition THEN statement_list] 
     [ELSE statement_list] 
END CASE;

注意，这里介绍的用在存储过程中的CASE语句，与控制流程函数中的SQL CASE表达式中的CASE是不同的 。存储过程中，CASE语句不能有ELSE NULL子句，并且，用END CASE代替END来终止 演示案例

CASE 
   when val is null then select 'val is null';
   when val<0 then select 'val is less then 0';
   when val>0 then select 'val is greater than 0';
   else select 'val is 0';
end case;

三、LOOP语句

LOOP循环语句，用来重复执行某些语句 与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断 LOOP内的语句一直重复执行，直到跳出循环语句（使用leave离开LOOP） 语法格式：

[loop_label:] LOOP 
   Statement_list 
END LOOP [loop_label]

参数： Loop_label，表示LOOP语句的标注名称，该参数可以省略 Statement_list，表示需要循环执行的语句 演示案例

Add_loop：LOOP循环名，自定义 
DECLARE id INT DEFAULT 0; 
add_loo:LOOP 
SET id=id+1; 
  IF id>=10 THEN LEAVE add_loop; 
  END IF; 
END LOOP add_loop;

四、ITERATE语句

用于将执行顺序转到语句段的开头处 iterate只能用于LOOP、REPEAT、WHILE语句内 iterate类似于C语言的continue，leave类似于C语言的break 语法格式： ITERATE lable Lable：表示循环的标志 演示案例

P1的初始值为0，如果，p1的值小于10时，重复执行p1加1的操作，当p1大于或等于10，并且小于20时，打印消息p1 is between 10 and 20，当p1大于20时，退出循环
CREATE PROCEDURE doiterate() 
BEGIN 
DECLARE p1 INT DEFAULT 0; 
my_loop:LOOP 
   SET p1=p1+1; 
   IF p1<10 THEN ITERATE my_loop; 
   ELSEIF p1>20 THEN LEAVE my_loop; 
   END IF; 
   SELECT ‘p1 is between 10 and 20’; 
END LOOP my_loop; 
END

五、REPEAT语句

用于创建一个带有条件判断的循环过程 每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束，否则，重复执行循环中的语句 语法格式：

[repeat_lable:] REPEAT 
   Statement_list 
UNTIL expr_condition 
END REPEAT [repeat_lable]

参数： Repeat_lable：为REPEAT语句的标注名称，该参数是可选的 REPEAT语句内的语句，或语句群被重复，直至expr_condition为真 演示案例

UNTIL：循环条件。后面不能有分号 
DECLARE id INT DEFAULT 0; 

REPEAT 
SET id=id+1; 
UNTIL id>=10; 
END REPEAT;

六、WHILE语句

创建一个带条件判断的循环过程 与REPEAT不同的是，WHILE在语句执行时，先对指定的条件进行判断，如果为真，则执行循环内的语句，否则退出循环 语法格式：

[while_lable:] WHILE expr_condition DO 
Statement_list 
END WHILE [while_lable]

参数： While_lable：为WHILE语句的标注名称 Expr_condition：为进行判断的表达式，如果表达式为真，WHILE语句内的语句，或语句群就被执行，直至expr_condition为假，退出循环 演示案例

DECLARE i INT DEFAULT 0; 
WHILE i<10 DO 
SET i=i+1; 
END WHILE;

四、触发器

一、基本概念

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

二、作用

可在写入数据前，强制检验或者转换数据(保证护数据安全) 触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚

三、创建触发器

语法结构：

create trigger trigger_name

before/after insert/update/delete

on tbl_name

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

begin

trigger_stmt

end;

create trigger demo_insert_trigger
after insert //执行时机
on city //对哪张表建立触发器
for each row
begin
insert into demo_logs(id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.city_id,concat('插入后（id：',new.city_id,',name:',new.city_name,'age:',new.city_id,',salary:',new.city_name));
end

四、查看触发器

show triggers;

五、删除触发器

drop trigger demo_insert_trigger；

五、存储引擎

1.MySQL体系结构

连接层：负责接收客户端发送过来的请求，开启一份线程，进行相关的认证授权操作，然后进行接下来的请求处理；

服务层：备份、恢复、SQL语句的封装、解析、优化、缓存等操作；

存储引擎层：选择合适的存储引擎，对数据进行相关的处理；

系统文件层：将数据最终落在文件系统（磁盘上）；

 

由图，可以看出MySQL最上层是连接组件。下面服务器是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。

连接池：由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。 管理工具和服务：系统管理和控制工具，例如备份恢复、Mysql复制、集群等 SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface 解析器: SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本， 主要功能： a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的 优化器：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。 用一个例子就可以理解： select uid,name from user where gender = 1; 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤 这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤 将这两个查询条件联接起来生成最终查询结果 缓存器： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。 通过LRU算法将数据的冷端溢出，未来得及时刷新到磁盘的数据页，叫脏页。 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

2.存储引擎概述

存储引擎就是存储数据，简历索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

MySQL5.5版本以后默认就是InnoDB，之前是MyISAM。

//查看全部存储引擎
show engines;
//查看默认的存储引擎
show variables like'%storage_engine%';

3.各种存储引擎的特性

只需掌握前两种：

InnoDB支持事务，支持行锁（适合高并发），是唯一一个支持外键的存储引擎；

MyISAM不支持事务，支持表锁，不支持外键；

3.1InnoDB

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;

 

1.支持事务（唯一）

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

2.支持外键（唯一） MySQL 支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包RESTRICT、CASCADE、SET NULL 和NO ACTION。其中RESTRICT 和NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

 on delete restrict ------>删除主表数据时，如果有关联记录，不删除；

 on update cascade  ------>更新主表时如果子表有关联记录，更新子表记录；

3.文件存储

cd /var/lib/mysql/   //存储对应数据库表结构的信息

表结构存储在.frm文件中，每个表的数据和索引单独保存在.ibd中

 

3.2MyISAM

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

不支持外键；

存储方式：表的结构定义存储在.ifm文件中；表的索引存储在索引文件.MYI中，数据文件存储在数据文件 .MYD中。

CREATE TABLE goods_myisam(
	id int not null auto_increment,
	name varchar(20) not null,
	primary key(id)
	)engine=myisam default charset=utf8;

 

六、优化SQL

 

针对整个数据库的：

show status like 'com_______';
show status global status like 'com_______';

 

针对InnoDB的指令：

show global status like 'Innodb_rows_%';

定位低效率执行SQL：

两种方式：

• 慢查询日志：通过慢查询日志定位哪些执行效率较低的SQL语句。

• show processlist:查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时查看SQL的执行情况。同时对一些锁表操作进行优化。

  create table tb_item(
  	item_id int NOT NULL AUTO_INCREMENT,
  	item_name varchar(100) not null,
  	item_price int not null,
  	primary key(item_id)
  )engine=InnoDB default charset=utf8;
   
  delimiter $
   
  create procedure pro_item_insert()
  begin
     declare num int(11) default 1000000;
     ins:loop
     if num<=0 then
     leave ins;
     end if;
     insert into tb_item values(null,'小米mix',num);
     set num = num -1;
     end loop ins;
  end$
   
  delimiter ;

   

explain分析执行计划

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

查询SQL语句的执行计划 ：

explain select * from tb_item where item_id = 1;

explain select * from tb_item where item_price = 987378;

环境准备

创建三张表，建表语句如下：

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

explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种： 1） id 相同表示加载表的顺序是从上到下。**

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

 

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

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id= (SELECT id FROM t_user WHERE username = 'stu1'));

 

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

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` ='2') a WHERE r.id = a.role_id ;

explain 之 select_type

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

 

explain 之 table

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

explain 之 type

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

explain 之 key

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 key ： 实际使用的索引， 如果为NULL， 则没有使用索引。 key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

explain 之 rows

扫描行的数量。

explain 之 extra

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

 

show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。 通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

select @@have_profiling;

 

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

select @@profiling;

set profiling=1; //开启profiling 开关；

通过profile，我们能够更清楚地了解SQL执行的过程。 首先，我们可以执行一系列的操作，如下图所示：

show databases;
use demo_02;
show tables;
select * from tb_item where item_id < 5;
select count(*) from tb_item;

 

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

 

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

show profile for query 5;

 

注意：Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

show profile cpu for query 5;

trace分析优化器执行计划

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

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

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

执行SQL语句 ：

select * from tb_item where item_id<4;

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

select * from information_schema.optimizer_trace\G;

mysql> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: select * from tb_item where item_id<4
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `tb_item`.`item_id` AS `item_id`,`tb_item`.`item_name` AS `item_name`,`tb_item`.`item_price` AS `item_price` from `tb_item` where (`tb_item`.`item_id` < 4)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`tb_item`.`item_id` < 4)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`tb_item`.`item_id` < 4)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`tb_item`.`item_id` < 4)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`tb_item`.`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": 998187,
                    "cost": 201916
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "item_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": [
                          "item_id < 4"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 1.6126,
                        "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": [
                        "item_id < 4"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.6126,
                    "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.2126,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.2126,
                "rows_for_plan": 3,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`tb_item`.`item_id` < 4)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`tb_item`",
                  "attached": "(`tb_item`.`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 */
}

七、索引的使用

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

4.1 验证索引提升查询效率 在我们准备的表结构tb_item 中， 一共存储了 100 万记录；

建立一个表并利用存储过程向其中插入100万行数据：

use test;
drop table if exists t;
create table t (id int not null,name varchar(30));

#创建存储过程，输入记录数，插入t表行数据
delimiter $$
create procedure proc1(cnt int)
begin
  declare i int default 1;
  start transaction;
  repeat
	insert into test.t (id,name) values (i,concat('a',i));	
	set i = i + 1;  
  until i > cnt end repeat;
  commit;
  end$$
delimiter ;

#调用存储过程proc1，1百万条记录
call proc1(1000000);
#查看记录数
select count(*) from t;
#查看执行时间
select * from t where id=1500;
#t表id列建立索引
create index idx_id on t(id);
show keys from t;
#查看执行时间
select * from t where id=1500;

A. 根据ID查询

select * from tb_item where item_id = 1999\G;

通过建立索引大大提升了查询速度。

create index idx_item_price on tb_item(item_price);

 

4.2 索引的使用

4.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');
 
--创建复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

4.2.2 避免索引失效

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

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

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

 

2).最左前缀法则

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

匹配最左前缀法则，走索引：

 

全值索引：走索引，走不走索引跟字段的顺序没有关系；

name的索引长度为403

 

只查询最左的字段：走索引

 

按照索引顺序查询：第二个status查询也是走索引的，status的索引长度就是7

 

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

 

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

范围查询字段后的索引将失效：

 

4). 不要在索引列上进行运算操作， 否则索引将失效。

explain select * from tb_seller where substring(name,3,2)='科技';

 

5). 字符串不加单引号，造成索引失效。

我们的建表语句中，status定义的类型是varchar字符串类型，但是当我们根据status查询的时候，就算不加单引号，我们也能准备的查询到数据，这是因为MySQL的查询优化器，会自动的进行类型转换，但是这就相当于是在索引列作了运算，所以就会造成索引失效。

6). 尽量使用覆盖索引，避免select *

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

知识点： using index ：使用覆盖索引的时候就会出现 using where：在查找使用索引的情况下，需要回表去查询所需的数据 using index condition：查找使用了索引，但是需要回表查询数据 using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7). 用or分割开的条件

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

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

 

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

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

解决方案：使用覆盖索引来解决

 

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

我们先给address列创建索引：

create index idx_address on tb_seller(address);

接下来，我们执行根据address进行查询，不过address的值是北京市，如下：

可以看到，表中一共是12条数据，address列中，北京市是11行，西安市是1行，当我们的查询条件是北京市的时候，Mysql底层根据表中数据分析发现，走索引还不如直接全表扫描速度快，所以就不走索引，直接进行全表扫描。而当查询的条件是西安市的时候，走索引的速度会更快，于是就选择了使用索引。

10). is NULL ， is NOT NULL 有时索引失效。

总结如下：当该列中的数据大部分都是null的时候，根据条件is null查询，索引失效，根据条件is not null查询，索引生效；当该列中的数据大部分都不是null的时候，根据条件is null查询，索引生效，根据条件is not null查询，索引失效。

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

尽量要使用in，尽量不使用not in。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 ！！！ 创建复合索引：

create index idx_name_sta_address on tb_seller(name, status, address);
就相当于创建了三个索引 ：
name
name + status
name + status + address

创建单列索引

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

单列索引的时候，如果根据这三个条件去查询，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引

 

4.3 查看索引的使用情况

 

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

八、SQL优化

5.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 data local infile '/root/sql1.log' into table `tb_user_1` fields terminated by ',' lines terminated by '\n';

 

可以看到sql1.log里面的数据主键是自增有序的，而sql2.log文件中的数据主键是乱序的。这两个文件中的数据的顺序是和我们之前创建的表的字段顺序对应的。

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

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

1） 主键顺序插入

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

插入ID顺序排列数据sql1.log：

 

2） 关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0；，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1；，恢复唯一性校验，可以提高导入的效率。（唯一索引：索引列中的值必须是唯一的，但是允许为空值。）

3） 手动提交事务

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

 

5.2 优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。 1、如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。 示例， 原始方式为：

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

优化后的方案为 ：合并多条insert语句为一条，一次性插入多条数据。

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

2、在事务中进行数据插入，事务的提交方式改为手动提交。

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

3、数据有序插入，按照主键顺序进行排序。

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

5.3 优化order by语句

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

5.3.2 两种排序方式

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

(效率低)

这里是filesort排序的主要原因是：我们查询的字段是*，包括了非索引列的数据，所以是先查询到数据，然后再对返回的数据进行排序，效率比较低。

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

多字段排序：根据age和salary两个字段排序：

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

5.3.3 Filesort 的优化

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

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

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

可以查询一下这两个变量的值是多少：

可以通过命令来设置上面的值的大小：

set @@max_length_for_sort_data=2048;

 

5.4 优化group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。 ​ 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;

优化后：

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

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

创建索引 ：

create index idx_emp_age_salary on emp(age,salary);

这个时候就会是using index了。

5.5 优化嵌套查询

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

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

使用连接查询优化后 :

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

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

 

 

5.6 优化OR条件

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

获取 emp 表中的所有的索引 ：
show index from emp;

执行计划:
explain select * from emp where id = 1 or age = 30;

虽然id主键和age都是由索引的，但是age上面的是复合索引，使用or的时候，复合索引就失效了。

当查询条件都是主键id的时候，执行or的时候，执行计划如下：

建议使用 union 替换 or 后的执行计划如下：

explain select * from emp where id = 1 union select * from emp where id = 10;

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项 type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

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 。

5.7 优化分页查询

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

所以，分页查询越到后面，查询时间越长。那么要怎么进行优化呢？针对不同的情况，有下面两种方式：

5.7.1 优化思路一

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

mysql> select * from tb_user_1 t,(select id from tb_user_1 order by id limit 800000,10) a where t.id = a.id;

5.7.2 优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。如果主键自增，但是主键数据有断层的话，也是不合适的。

速度快的原因是走主键索引的，但是必须满足主键自增并且没断层。

5.8 使用SQL提示

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

5.8.1 USE INDEX

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

准备工作，给tb_seller表的name字段添加单列索引，

create index idx_seller_name on tb_seller(name);

（之前已经创建了复合索引，里面页有name字段），所以这个时候在name字段就有两个索引存在，我们接下来看根据name查询的时候会使用哪个索引：

根据name查询的执行计划：

 explain select * from tb_seller where name = '小米科技';

可以看到，最终使用的索引是idx_seller_name_sta_addr；我们可以使用use index来使用idx_seller_name

5.8.2 IGNORE INDEX

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

5.8.3 FORCE INDEX

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

我们之前已经给tb_seller表创建过一个address列的索引：idx_address

数据库认为走全表扫描比走索引要快，所以不适用索引。

这里使用了use，只是建议使用某一索引，数据库没有采纳，还是使用了全表扫描。

使用force命令，强制使用指定索引，即使使用其效率慢，依旧使用。

 

九、应用优化

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

1.1 使用数据库连接池

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

1.2 减少对MySQL的访问

1.2.1 避免对数据进行重复检索

在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求。 比如 ，需要获取书籍的id 和name字段 ， 则查询如下：

select id , name from tb_book;

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

select id , status from tb_book;

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

select id, name , status from tb_book;

1.2.2 增加cache层

在应用中，我们可以在应用中增加 缓存层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。 ​ 因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据 。

1.3 负载均衡

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

1.3.1 利用MySQL复制分流查询

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

 

1.3.2 采用分布式数据库架构

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

十、Mysql中查询缓存优化

2.1 概述

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

2.2 操作流程

1. 客户端发送一条查询给服务器；

2. 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

5. 将结果返回给客户端。

2.3 查询缓存配置

1. 查看当前的MySQL数据库是否支持查询缓存：

show variables like 'have_query_cache';

2. 查看当前MySQL是否开启了查询缓存 ：

show variables like 'query_cache_type';

3. 查看查询缓存的占用大小 ：1048576单位是字节，占用1M=1024k空间。

show variables like 'query_cache_size';

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

show status like 'Qcache%';

 

各个变量的含义如下：

参数 含义 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 查询缓存中的块总数

2.4 开启查询缓存

MySQL的查询缓存默认是关闭的, 需要手动配置参数query_cache_type , 来开启查询缓存。query_cache_type该参数的可取值有三个 ：

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

#开启MySQL的查询缓存
query_cache_type=1

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

 

2.5 查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项 ： SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果 。 SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：SQL_CACHE的使用

SQL_NO_CACHE 的使用：

2.6 查询缓存失效的情况

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

我们之前已经执行过：select count() from tb_item; 这条语句的结果已经进入了缓存的，下面我们执行 Select count() from tb_item; 这条语句和之前的只有S是大写，其他的完全一样，接下来看是否走缓存：

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

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

3） 不使用任何表查询语句。

select 'A';

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

select * from information_schema.engines;

5） 在存储函数，存储过程，触发器或事件的主体内执行的查询，是不会走查询缓存的。

 

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

这包括使用MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATETABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

 

十一、Mysql内存管理及优化

3.1 内存优化原则

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

3.2 MyISAM 内存优化

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

key_buffer_size

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

在/usr/my.cnf 中做如下配置：

key_buffer_size=512M
read_buffer_size

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

 

read_rnd_buffer_size

 

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

 

3.3 InnoDB 内存优化

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

innodb_buffer_pool_size：该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

也是在MySQL的参数文件（/usr/my.cnf）中进行设置：

innodb_buffer_pool_size=512M

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

innodb_log_buffer_size=10M

十二、Mysql并发参数调整

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。

4.1 max_connections

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。 Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

查看和设置参数方式：

--查看参数的值
show variables like 'max_connections';
--修改参数的值，修改/usr/my.cnf文件，添加如下的内容：
max_connections=168

下面的几个参数的值设置和查看方式也是这样的。

4.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 的值。

4.3 table_open_cache

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

max_connections x N ；默认值是2000，如下所示：

mysql> show variables like 'table_open_cache';

4.4 thread_cache_size

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

mysql> show variables like 'thread_cache_size';

4.5 innodb_lock_wait_timeout

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ， 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说，可以将行锁的等待时间调大， 以避免发生大的回滚操作。

 

Mysql锁问题

5.1 锁概述

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

5.2 锁分类

从对数据操作的粒度分 ： 1） 表锁：操作时，会锁定整个表。 2） 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分： 1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

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

5.3 Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

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

5.4 MyISAM 表锁

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

5.4.1 如何加表锁

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

显示加表锁语法：

加读锁 ： lock table table_name read;
加写锁 ： lock table table_name write；

5.4.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,'田伯光');

所以得出结论，如果我们对某一张表加了读操作，不会阻塞其他其他的线程的读操作，但是会阻塞其他线程的写操作。

5.4.3 写锁案例

结论如下：如果一个表被加了写锁，那么会阻塞其他线程的读和写操作，会导致其他线程对该表的读写操作进入阻塞状态；

5.4.4 结论

由上面的操作可以得出如下总结： 1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求； 2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作； 简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。 此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

 

5.4.5 查看锁的使用情况

show open tables;

 

 

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

 

show status like 'Table_locks%';

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

5.5 InnoDB 行锁

5.5.1 行锁介绍

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

5.5.2 背景知识

事务及其ACID属性 事务是由一组SQL语句组成的逻辑处理单元。 事务具有以下4个特性，简称为事务ACID属性。

 

并发事务处理带来的问题

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。 数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

 

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

show variables like 'tx_isolation';

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

5.5.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.5.5 行锁基本演示

这个时候就能看出来行锁的作用了，当客户端一执行更新操作的时候，会自动给改行数据加上锁，在还没有执行commit提交事务的时候，如果其他线程也要对该条数据进行操作（增删改），那么就会进入阻塞状态。只有客户端一执行了commit之后，才会释放行锁，其他线程才能进行操作。

 

5.5.6 无索引行锁升级为表锁

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。 查看当前表的索引 ： show index from test_innodb_lock ;

我们在id和name列都是创建了索引的。

name字段明明是有索引的，那么更新条件是name=400，为什么会造成表锁呢？需要注意的是我们的name字段是varchar类型，而我们的where条件中，name=400没有加单引号，这样就会导致数据库底层做数据转换，进行了运算，这样就不会走索引了。所以就造成了表锁，就算我们其他线程操作的不是同一行数据，也会进入阻塞。

 

5.5.7 间隙锁危害

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

5.5.8 InnoDB 行锁争用情况

show status like 'innodb_row_lock%';

 

 

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

 

5.3.9 总结

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

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

 

常用SQL技巧

6.1 SQL执行顺序

编写顺序：

SELECT DISTINCT

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>

执行顺序：

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>

 

6.2 正则表达式使用

正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。

select * from emp where name regexp '^T';
select * from emp where name regexp '2$';
select * from emp where name regexp '[uvw]';

 

6.3 MySQL 常用函数

6.3.1 数字函数

字符串函数：

日期函数：

聚合函数：