原创

    MySQL 数据库高级教程(纯干货!)

    第一章:Linux 系统安装 MySQL

    一、下载 MySQL

    要安装 MySQL,我们首先就要下载 MySQL。 下载地址:点击跳转下载页面 。进入后选择自己的操作系统,下载相应的 MySQL 安装包即可。注意:CentOS 系统选择 RedHat 选项。 如下图:

    二、安装 MySQL

    下载好 MySQL 安装包之后,我们就可以进行安装了。 具体步骤如下:

    -- 上传 MySQL 安装包并解压
    rpm -qa | grep -i mysql              -- 查找 MySQL 安装包
    rpm -e mysql-xxx --nodeps            -- 卸载 MySQL 安装包
    put D:/mysql-xxx                     -- 上传 MySQL 的安装包到 Linux 系统【使用 SecureCRT 工具】
    mkdir mysql                          -- 创建 mysql 目录
    tar -xvf mysql-xxx -C mysql          -- 解压 mysql-xxx 压缩包到 mysql 目录下
    cd mysql                             -- 进入到 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 客户端和服务端
    rpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm     -- 安装客户端
    rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm     -- 安装服务端
    -- 安装过程中会在 /root/.mysql_secret 生成 MySQL 的随机密码,
    -- 可以使用 cat 命令查看。我的密码是:uubZDL8IxUByPS2o
    

    三、启动 MySQL 服务

    如果是 CentOS7 之前的系统,使用 service 命令来启动和关闭服务。在 CentOS7 之后,使用 systemctl 命令来启动和关闭服务。 具体命令如下:

    -- MySQL 的启动、关闭和状态查看
    service mysql start                 -- 启动
    service mysql stop                  -- 关闭
    service mysql status                -- 查看状态
    service mysql restart               -- 重新启动
    
    -- 如果是 CentOS7 及以上版本,那就使用 systemctl 命令进行相关的操作
    systemctl start mysql               -- 启动
    systemctl stop mysql                -- 关闭
    systemctl status mysql              -- 查看状态
    systemctl restart mysql             -- 重新启动
    

    TIPS: Linux 系统下的 MySQL 的安装到此结束。本网站提供的另一种安装方式:Linux 下安装 MySQL8,我讲明白了吗?

    四、登录 MySQL

    把 MySQL 服务开启之后,我们就可以登录 MySQL 了。 命令如下:

    -- 登录 MySQL ,密码是安装服务端的时候生成的随机密码,用户名是 root
    mysql -uroot -puubZDL8IxUByPS2o
    set password = password('root');    -- 修改 root 用户的密码
    exit                                -- 然后退出
    mysql -uroot -proot                 -- 再次登录,使用的密码就是 root 了
    
    -- 授权远程登录,这样便可在可视化工具下登录或其他主机上登录
    grant all privileges on *.* to 'root' @'%' identified by 'root';  -- by 后面的 root 指的是 root 用户的密码
    flush privileges;
    
    -- 执行到上述命令,如果发现无法远程连接到 MySQL,那么可能就是防火墙的问题了
    service iptables status             -- 查看防火墙状态
    service iptables stop               -- 关闭防火墙
    service iptables start              -- 开启防火墙
    service iptables restart            -- 重启防火墙
    -- 如果防火墙是开启的,那么关闭即可。关闭防火墙之后就可以远程连接数据库了
    

    五、环境准备

    在 MySQL 高级部分【即本篇博客】,我们需要使用到许多表,所以先在这个地方创建好。 具体如下:

    -- 创建 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;
    
    -- 向 city 表中插入数据
    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);
    
    -- 创建 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;
    
    -- 向 country 表中插入数据
    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');
    
    -- 创建 emp 表
    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;
    
    -- 向 emp 表中插入数据
    insert into `emp` (`id`, `name`, `age`, `salary`) values (null, '金毛狮王', 55, 3800);
    insert into `emp` (`id`, `name`, `age`, `salary`) values (null, '白眉鹰王', 60, 4000);
    insert into `emp` (`id`, `name`, `age`, `salary`) values (null, '青翼蝠王', 38, 2800);
    insert into `emp` (`id`, `name`, `age`, `salary`) values (null, '紫衫龙王', 42, 1800);
    
    create table `emp_logs` (    -- 创建 emp 表的日志记录表 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;
    

    第二章:MySQL 的索引

    一、索引概述

    MySQL 官方对索引的定义为:索引(index) 是帮助 MySQL 高效获取数据的数据结构(有序)。 在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。 如下图所示:

    左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找快速获取到相应数据。一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

    二、索引的优缺点

    建立索引既有优势也有劣势。优劣对比如下:

    索引的优势:
    1、索引类似于书籍的目录,能够提高数据检索的效率,降低数据库的 IO 成本。
    2、通过索引列对数据进行排序,降低数据排序的成本,降低 CPU 的消耗。

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

    三、索引的结构

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

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

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

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

    注意: 我们平常所说的索引,如果没有特别指明,都是指B+树(多路搜索树,并不一定是二叉的)结构组织的索引。 其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引,统称为索引。

    BTree 结构

    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 时,中间节点分裂到父节点,两边节点进行分裂
    

    BTree 结构演示,以插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。演变过程如下:

    TIPS: 到此,该 BTREE 树就已经构建完成。BTREE 树和二叉树相比,查询数据的效率更高,因为对于相同的数据量来说,BTREE 的层级结构比二叉树小,因此搜索速度快。

    B+TREE 结构

    MySQL 中的 B+Tree

    四、索引的分类

    索引有许多分类,具体介绍如下:

    # 单值索引
    即一个索引只包含单个列,一个表可以有多个单列索引
    
    # 唯一索引
    索引列的值必须唯一,但允许有空值3)
    
    # 复合索引
    即一个索引包含多个列
    

    五、索引的语法

    前面已经了解了索引,那么现在就来了解一下索引的语法。 如下:

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
    [USINGindex_type] ON tbl_name(index_col_name,...)
    
    index_col_name:column_name[(length)][ASC|DESC]
    
    -- 索引是一种数据结构,作用就是高效的查询数据
    -- 没有建立索引之前,我们查询数据是需要全表扫描的
    -- 在 MySQL 中,主键默认是有索引的
    
    -- 创建索引的语法
    -- CREATE INDEX  索引的名字  ON 表名(需要创建索引的字段)
    CREATE INDEX idx_city_name ON city(city_name);           -- 创建索引
    SHOW INDEX FROM city;                                    -- 查看索引
    SHOW INDEX FROM city\G;                                  -- 在命令行中使用,更方便查看
    DROP INDEX idx_city_name ON city;                        -- 删除索引
    
    -- 创建主键索引
    ALTER TABLE city ADD PRIMARY idx_city_id(city_id);
    SHOW INDEX FROM city;
    -- 创建唯一索引
    ALTER TABLE city ADD UNIQUE idx_city_name(city_name);
    SHOW INDEX FROM city;
    

    -- 创建各种索引的命令
    
    -- 主键索引:该语句添加一个主键,这意味着索引值必须是唯一的,且不能为 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);
    

    六、索引的设计原则

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


    1、对查询频次较高,且数据量比较大的表建立索引

    2、索引字段的选择,最佳候选列应当从 where 子句的条件中提取。 如果 where 子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合

    3、使用唯一索引,区分度越高,使用索引的效率越高

    4、索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。 对于插入、更新、删除等 DML 操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低 DML 操作的效率,增加相应操作的时间消耗。另外索引过多的话,MySQL 也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价

    5、使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。 假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。

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


    # 创建复合索引
    CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
    
    # 就相当于
      对 name 创建索引;
      对 name,email 创建了索引;
      对 name,email,status 创建了索引;
    

    第三章:视图和触发器

    一、视图的概述

    视图(View) 是一种虚拟存在的表。 视图并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。通俗的讲,视图就是一条 SELECT 语句执行后返回的结果集。 所以我们在创建视图的时候,主要的工作就落在创建这条 SQL 查询语句上。视图相对于普通的表的优势主要包括以下几项:

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

    二、视图的 CRUD 操作

    -- 创建视图语法模板
    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:必须满足所有针对该视图的所有视图的条件才可以更新。这个是默认值
    
    -- 删除视图语法模板
    DROP VIEW [IF EXISTS] view_name [,view_name]... [RESTRICT | CASCADE]
    
    -- 视图相关的命令,视图是一张虚拟的表,怎么操作表,就怎么操作视图
    select c.* , t.country_name from city c , country t where c.country_id = t.country_id;  -- 多表查询
    
    -- 创建名字为 view_city_country 的视图。as 关键字之后写查询语句
    create view view_city_country 
    as select c.* , t.country_name from city c , country t where c.country_id = t.country_id;
    
    -- 查看视图
    select * from view_city_country;
    
    -- 更新视图,更新的是 city 表的数据,而不是视图。虽然视图可以更新,但是不建议更新
    update view_city_country set city_name = '西安市' where city_id = 1;
    select * from view_city_country;
    select * from city;
    
    -- 从 MySQL5.1 版本开始,使用 SHOW TABLES 命令不仅显示表的名字,同时也会显示视图的名字,而不存在 SHOW VIEWS 命令
    -- 查看视图
    show tables;
    
    -- 同样,在使用 SHOW TABLE STATUS 命令的时候,不但可以显示表的信息,同时也可以显示视图的信息
    show table status\G;
    show table status like 'view_city_country'\G;
    
    -- 查看创建视图的语句,即查看视图的定义
    show create view view_city_country;
    
    -- 修改视图
    alter view view_city_country 
    as select c.* from city c , country t where c.country_id = t.country_id;
    
    -- 删除视图
    drop view if exists view_city_country;
    

    三、触发器的介绍

    触发器 是与表有关的数据库对象,用于在 insert/update/delete 之前或之后,触发并执行触发器中定义的 SQL 语句集合。 触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

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

    四、触发器的 CRUD 操作

    -- 触发器相关的命令,与 js 中的事件差不多,需要达到某个条件才触发
    -- NEW 和 OLD 两个行记录变量需要去了解一下
    -- 创建触发器,MySQL 只支持行级触发器,不支持语句级触发器
    -- 通过触发器记录 emp 表的数据变更日志,包含增加、修改、删除等记录
    
    -- 插入型的触发器
    create trigger emp_insert_trigger
    after insert    -- 表示在 insert 之后触发
    on emp          -- 对哪一张表设置触发器
    for each row    -- 表示行级触发器,MySQL 不支持语句级触发器
    
    -- 在 emp 表插入一条数据之后需要执行的操作
    begin
      insert into emp_logs(id, operation, operate_time, operate_id, operate_params) values 
      (null, 'insert', now(), new.id, concat("Id is ", new.id));   -- new.id 指的是当前插入到 emp 表的数据的 id
    end$
    
    -- 修改型的触发器
    create trigger emp_update_trigger
    after update    -- 表示在 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("before update (id=", old.id, ',name=', old.name , 
      ',age=', old.age , ',salary=', old.salary , ') ==> after update (id=', new.id , ',name=', 
      new.name , ',age=', new.age , ',salary=', new.salary , ')'));
    end$
    
    -- 删除型的触发器
    create trigger emp_delete_trigger
    after delete    -- 表示在 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("delete (id=", old.id, ',name=', old.name , 
      ',age=', old.age , ',salary=', old.salary, ')'));
    end$
    
    -- 然后执行下列相关操作
    -- insert 语句用于测试插入型触发器
    -- update 语句用于测试修改型触发器
    -- delete 语句用于测试删除型触发器
    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$               -- 最后查看 emp_logs 表,每一次的操作记录都有
    show triggers\G$                      -- 查看触发器
    drop trigger emp_update_trigger$      -- 删除触发器
    

    第四章:存储过程和存储函数

    一、存储过程和函数的概述

    存储过程和函数 是事先经过编译并存储在数据库中的一段 SQL 语句的集合,调用存储过程和函数其实就是执行那段 SQL 代码。 这样做的目的可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。存储过程和存储函数的区别在于:存储函数必须有返回值,而存储过程没有。通俗一点说:存储函数是一个有返回值的过程;而存储过程是一个没有返回值的函数。

    二、创建和调用存储过程

    -- 创建存储过程语法模板
    delimiter $
    CREATE PROCEDURE procedure_name([proc_parameter[,...]]) 
    begin 
        --SQL语句 
    end$
    
    -- 调用存储过程语法模板
    call procedure_name();
    
    -- 存储过程就是一段 SQL 语句的集合,有什么用呢?它能减少数据在数据库和服务器之间的传输的次数,提高了数据处理的效率
    -- 创建存储过程
    delimiter $                       -- 切换分隔符, 把 ;[分号] 切换为 $[美元符]
    create procedure pro_test1() 
    begin 
      select 'Hello , MySQL!';        -- 分号不能忘记,否则会报错
    end$ 
    
    -- 调用存储过程
    call pro_test1()$
    

    三、查询和删除存储过程

    -- 查询存储过程的信息
    --查询 mysql_advanced 数据库中的所有的存储过程。存储过程的信息都存在 mysql 数据库的 proc 表中
    select name from mysql.proc where db='mysql_advanced' $
    show procedure status\G $    -- 查询存储过程的信息
    
    -- 查询存储过程的定义
    show create procedure pro_test1\G $
    
    -- 删除存储过程
    drop procedure if exists pro_test1 $
    

    四、存储过程的语法

    1、变量

    存储过程是可以编程的,意味着可以使用变量、表达式、流程控制结构等来完成比较复杂的功能。 我们可以使用 DECLARE 来定义一个局部变量,该变量的作用范围只能在 BEGIN...END 中。具体代码如下:

    -- 存储过程之变量定义的语法模板
    DECLARE var_name[,...] type [DEFAULT value]  -- type 是数据类型,value 是定义的默认值
    
    -- 存储过程之变量赋值的语法模板
    SET var_name=expr [,var_name=expr]...        -- expr 是表达式的意思
    
    -- 存储过程语法之变量的定义
    delimiter $
    create procedure pro_test1()
    begin
      declare num int default 10;    -- 声明一个变量 num ,int 类型的,默认值是 10
      select concat('The value of num is : ' , num);
    end $
    call pro_test1 $
    
    -- 存储过程语法之变量的赋值,使用 set 关键字
    create procedure pro_test2()
    begin
      declare num int default 10;    -- 声明一个变量 num ,int 类型的,默认值是 10
      set num = num + 10;            -- 使用 set 关键字为 num 赋值
      select concat('The value of num is : ' , num);
    end $
    
    -- 存储过程语法之变量的赋值,使用 into 关键字
    create procedure pro_test3()
    begin
      declare num int;    -- 此时没有给定默认值
      select count(*) into num from city;  -- 将查询的结果赋值给 num 变量
      select concat('The value of num is : ' , num);
    end $
    

    2、if 条件判断

    -- if 条件判断语法模板
    if search_condition then statement_list 
        [elseif search_condition then statement_list] ... 
        [else statement_list] 
    end if;
    
    -- 存储过程语法之 if 条件判断举例
    delimiter $
    create procedure pro_test4()
    begin
      declare height int default 175;
      declare description varchar(50) default '';
      if height >= 180 then
        set description='tall';
      elseif height >= 170 and height < 180 then
        set description='middle';
      else
        set description='normal';
      end if;
      select concat('The height is : ' , height , ' ,The description of height is : ' , description);
    end $
    

    3、传递参数

    在上一小节中,我们发现,参数是定义死的,不够灵活。所以,在这一小结学习传递参数。 语法如下:

    -- 传递参数语法模板
    create procedure procedure_name([in/out/inout] 参数名 参数类型)
    ... 
    
    -- IN: 该参数可以作为输入,也就是需要调用方传入值,默认就是 IN
    -- OUT: 该参数作为输出,也就是该参数可以作为返回值
    -- INOUT: 既可以作为输入参数,也可以作为输出参数
    
    -- 存储过程语法之输入参数。根据传递的身高变量,判定当前身高的所属的身材类型
    -- in 代表输入参数,out 代表输出参数,inout 既可以是输入参数,也可以是输出参数
    delimiter $
    create procedure pro_test5(in height int) 
    begin
      declare description varchar(50) default '';
      if height >= 180 then
        set description='tall';
      elseif height >= 170 and height < 180 then
        set description='middle';
      else
        set description='normal';
      end if;
      select concat('The height is : ' , height , ' ,The description of height is : ' , description);
    end $
    
    -- 存储过程语法之输出参数。根据传入的身高变量,获取当前身高的所属的身材类型,并以返回值的方式返回
    create procedure pro_test6(in height int, out description varchar(50)) 
    begin
      if height >= 180 then
        set description='tall';
      elseif height >= 170 and height < 180 then
        set description='middle';
      else
        set description='normal';
      end if;
    end $
    
    -- @ 变量【用户变量】和 @@ 变量【系统变量】
    -- @description 这种在变量前加上“@”符号,叫做用户会话变量,代表整个会话过程它都是有作用的,类似于全局变量
    -- @@global.sort_buffer_size 这种在变量前加上 "@@" 符号, 叫做系统变量
    call pro_test6(175, @description)$    -- 使用 @description 变量来保存输出的内容
    select @description$                  -- 查看 @description 变量保存的内容
    
    -- 用户变量
    set @name = 'Java' $                  -- 设置用户变量
    select @name$                         -- 查看用户变量
    

    4、case 结构

    -- 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;
    
    -- 存储过程语法之 case 结构。给定一个月份,计算出所在的季节
    delimiter $
    create procedure pro_test7(mon int)
    begin
    declare result varchar(30);
      case
        when mon >= 1 and mon <= 3 then
          set result = 'Spring';
        when mon >= 4 and mon <= 6 then
          set result = 'Summer';
        when mon >= 7 and mon <= 9 then
          set result = 'Autumn';
        else
          set result = 'Winter';
      end case;
      select concat("The month is : " , mon , " , The season of month is : " , result) as content;
    end $
    

    5、while 循环

    -- while 循环语法模板
    while search_condition do 
      statement_list 
    end while;
    
    -- 存储过程语法之 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 $
    

    6、repeat 结构

    -- repeat 结构语法模板。while 是满足条件就一直执行,而 repeat 是满足条件就退出循环
    REPEAT 
      statement_list 
      UNTIL search_condition 
    END REPEAT;
    
    -- 存储过程语法之 repeat 结构。计算从 1 加到 n
    create procedure pro_test9(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 $
    

    7、loop 和 leave 语句

    -- loop 语句语法模板
    -- LOOP 实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用 LEAVE 语句实现
    [begin_label:] LOOP 
      statement_list 
    END LOOP [end_label]
    
    -- 存储过程语法之loop。计算从 1 加到 n
    create procedure pro_testx(n int)
    begin
      declare total int default 0;
      c:loop
        set total = total + n;
        set n = n - 1;
        if n <= 0 then
          leave c;  -- 这个是退出 loop 循环
        end if;
      end loop c;
      select total;
    end $
    
    -- LEAVE 语句是用来从标注的流程构造中退出,通常和 BEGIN ... END 或者循环一起使用。示例如下:
    create procedure pro_testx(n int)
    begin
      declare total int default 0;
      c:loop
        set total = total + n;
        set n = n - 1;
        if n <= 0 then
          leave c;  -- 这个是退出 loop 循环
        end if;
      end loop c;
      select total;
    end $
    

    8、游标和光标

    游标 也叫光标,是用来存储查询结果集的数据类型, 在存储过程和存储函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明 DECLAREOPENFETCHCLOSE 等操作。 其具体语法如下:

    -- 声明光标
    DECLARE cursor_name CURSOR FOR select_statement;
    
    -- OPEN光标
    OPEN cursor_name;
    
    -- FETCH光标
    FETCH cursor_name INTO var_name [,var_name] ...
    
    -- CLOSE光标
    CLOSE cursor_name;
    
    -- 存储过程语法之游标,查询 city 表中的数据,并逐行获取进行展示
    create procedure pro_test_cursor1()
    begin
      declare e_id int(11);
      declare e_name varchar(50) character set utf8;
      declare e_age int(11);
      declare e_salary int(11);
      declare e_result cursor for select * from emp;        -- 定义一个 e_result 游标变量,把查询到的结果封装到游标
      open e_result;    -- 开启游标
      fetch e_result into e_id, e_name, e_age, e_salary;    -- into 后面的变量的顺序需和数据库字段顺序一致
      select concat('e_id=', e_id , ' <===> e_name=', e_name , ' <===> e_age=' , e_age , ' <===> e_salary=' , e_salary);
      close e_result;   -- 关闭游标
    end $
    
    -- 游标使用的改进方案
    create procedure pro_test_cursor2()
    begin
      declare e_id int(11);
      declare e_name varchar(50) character set utf8;
      declare e_age int(11);
      declare e_salary int(11);
      declare has_data int default 1;  -- 定义一个退出变量 has_data
      
      declare e_result cursor for select * from emp;        -- 定义一个游标 e_result ,把查询到的结果封装到游标
      declare exit handler for not found set has_data = 0;  -- 当获取不到数据的时候,我们把 has_data 置零,并且退出当前程序
      open e_result;    -- 开启游标
      
      -- 此处使用循环遍历数据,可以把所有在游标中的数据都遍历出来。如果 has_data=0,则表示游标已指向表尾的下一个位置
      repeat
        fetch e_result into e_id, e_name, e_age, e_salary;  -- into 后面的变量的顺序需和数据库字段顺序一致
        select concat('e_id=', e_id , ' <===> e_name=', e_name , ' <===> e_age=' , e_age , ' <===> e_salary=' , e_salary);
        until has_data = 0
      end repeat;
      
      close e_result;   -- 关闭游标
    end $
    

    五、存储函数

    存储函数 是一个有返回值的过程。 具体使用如下:

    -- 存储函数语法
    CREATE FUNCTION function_name([paramtype...]) 
    RETURNS type 
    BEGIN 
      ... 
    END;
    
    -- 定义一个存储函数,获取满足条件的 city 表的总记录条数
    create function getCount(countryId int)
    returns int
    begin
      declare cnum int;
      select count(*) into cnum from city where country_id = countryId;
      return cnum;
    end$
    
    -- 存储函数的调用
    select getCount(1)$
    select getCount(2)$
    
    -- 存储函数的删除
    drop function getCount$
    

    第五章:MySQL 的存储引擎

    一、MySQL 的体系结构概述

    整个 MySQL Server 由以下部分组成, 预览图如下:

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

    连接层

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

    服务层

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

    引擎层

    存储引擎层,存储引擎真正的负责了 MySQL 中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。

    存储层

    数据存储层,主要是将数据存储在文件系统之上,并完成与存储引擎的交互。

    总结

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

    二、存储引擎介绍

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

    -- 查看存储引擎
    show engines;                                    -- 查询 MySQL 数据库支持的存储引擎
    show variables like '%storage_engine%';          -- 查看 MySQL 数据库默认的存储引擎
    
    -- 创建新表时如果不指定存储引擎,那么系统就会使用默认的存储引擎
    -- MySQL5.5 之前的默认存储引擎是 MyISAM 引擎,5.5 之后就改为了 InnoDB 引擎
    

    三、各种存储引擎特性

    下面是几种常用的存储引擎,并对比各个存储引擎之间的区别。重点关注 InnoDB 和 MyISAM 。 如下表所示:

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

    TIPS: 重点学习 InnoDB、MyISAM 引擎,其他的引擎了解即可。

    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;
    
    startt ransaction;     -- 在一个窗口开启事务,然后插入数据
    
    -- 插入数据之后,别着急提交事务。打开第二个窗口进行查询数据
    -- 如果查不到数据,则代表此引擎具有事务
    insert into goods_innodb(id,name) values (null,'Meta20');
    commit;     -- 最后提交事务,在第二个窗口再次查询,发现是可以查到数据的,从而验证了此引擎具有事务
    

    外键约束

    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 相同,是指限制在子表有关联记录的情况下,父表不能更新
    • CASCADE 表示父表在更新或者删除时,更新或者删除子表对应的记录
    • SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被 SET NULL

    针对上面创建的两个表,子表的外键指定是 ON DELETE RESTRICT ON UPDATE CASCADE 方式的,那么在主表删除记录的时候,如果子表有对应记录,则不允许删除,主表在更新记录的时候,如果子表有对应记录,则子表对应更新。

    -- 外键信息可以使用如下两种方式查看
    show create table city_innodb;
    show create table city_innodb\G;
    
    -- 删除 country_id=1 的 country 数据。由于存在外键,删除失败
    delete from country_innodb where country_id=1;
    
    -- 更新主表 country 表的字段 country_id。由于外键存在,子表的外键数据会被级联更新
    update country_innodb set country_id=100 where country_id=1;
    

    存储方式

    InnoDB 存储表和索引有以下两种方式:

    • 使用共享表空间存储: 这种方式创建的表的表结构保存在 .frm 文件中,数据和索引保存在 innodb_data_home_dirinnodb_data_file_path 定义的表空间中,可以是多个文件。
    • 使用多表空间存储: 这种方式创建的表的表结构仍然存在 .frm 文件中,但是每个表的数据和索引单独保存在 .ibd 中。

    TIPS: 查看 MySQL 数据存储的位置,通过命令 cd /var/lib/mysql 即可查看到各种数据存储文件。

    2、MyISAM 引擎

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

    不支持事务

    -- 创建 goods_myisam 表,使用 myisam 引擎
    create table goods_myisam(
      id int NOT NULL AUTO_INCREMENT,
      name varchar(20) NOT NULL,
      primary key(id)
    )ENGINE=myisam DEFAULT CHARSET=utf8;
    
    -- 通过测试,我们发现在 MyISAM 存储引擎中,是没有事务控制的
    

    文件存储方式

    每个 MyISAM 在磁盘上存储成 3 个文件,其文件名都和表名相同。 但扩展名分别是:

    • .frm (存储表定义)
    • .MYD (MYData , 存储数据)
    • .MYI (MYIndex , 存储索引)

    TIPS: 查看 MySQL 数据存储的位置,通过命令 cd /var/lib/mysql 即可查看到各种数据存储文件。

    3、MEMORY 引擎

    Memory 存储引擎将表的数据存放在内存中。每个 MEMORY 表实际对应一个磁盘文件,格式是 .frm ,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。 MEMORY 类型的表访问非常地快,因为他的数据是存放在内存中的,并且默认使用 HASH 索引, 但是服务一旦关闭,表中的数据就会丢失。

    4、MERGE 引擎

    MERGE 存储引擎是一组 MyISAM 表的组合,这些 MyISAM 表必须结构完全相同,MERGE 表本身并没有存储数据,对 MERGE 类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的 MyISAM 表进行的。 对于 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张表中的数据
    select * from order_1990;
    select * from order_1991;
    select * from order_all;
    
    -- 往 order_all 中插入一条记录,由于在 MERGE 表定义时
    -- INSERT_METHOD 选择的是 LAST,那么插入的数据会在最后一张表中插入
    insert into order_all values (100,10000.0,'西安');
    

    四、存储引擎的选择

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

    InnoDB 是 MySQL 的默认存储引擎,用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询意外,还包含很多的更新、删除操作,那么 InnoDB 存储引擎是比较合适的选择。 InnoDB 存储引擎除了有效的降低由于删除和更新导致的锁定,还可以确保事务的完整提交和回滚,对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB 是最合适的选择。

    MyISAM: 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。

    MEMORY: 将所有数据保存在 RAM 中,在需要快速定位记录和其他类似数据环境下,可以提供几块的访问。MEMORY 的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表,用以快速得到访问结果。

    MERGE 用于将一系列等同的 MyISAM 表以逻辑方式组合在一起,并作为一个对象引用他们。 MERGE 表的优点在于可以突破对单个 MyISAM 表的大小限制,并且通过将不同的表分布在多个磁盘上,可以有效的改善 MERGE 表的访问效率。这对于存储诸如数据仓储等 VLDB 环境十分合适。

    第六章:SQL 性能分析

    在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越大。 此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,本章将详细介绍在 MySQL 中优化 SQL 语句的方法。当面对一个有 SQL 性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题 SQL 并尽快解决问题。

    一、查看 SQL 执行频率

    当 MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。show[session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级(当前连接)的计算结果和 global 级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是“session”。具体命令如下:

    -- 以下三行将查询出 341 行记录
    show status;
    show session status;
    show global status;
    
    -- 显示当前 session 中所有统计参数的值
    show status like 'Com_______';         -- 横线是七根下划线
    show status like 'Innodb_rows_%';      -- 查询 InnoDB 引擎相关的执行记录
    

    Com_xxx 表示每个 xxx 语句执行的次数,我们通常比较关心的是以下几个统计参数。 具体介绍如下:

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

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

    二、定位低效率执行的 SQL

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

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

    三、explain 分析执行计划

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

    -- 查询 SQL 语句的执行计划。可使用 explain 命令或者 desc 命令,结果都是一样的
    explain select * from city;
    explain select * from city where city_id='1';
    
    desc select * from city;
    desc select * from city where city_id='1';
    
    字段含义
    idselect 查询的序列号,是一组数字,表示的是查询中执行 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、环境准备

    先创建三张表,表之间的关系和 SQL 语句如下:

    -- 创建 t_role 表
    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;
    
    -- 创建 t_user 表
    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;
    
    -- 创建 user_role 表
    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','mtk3wxp6','超级管理员');
    insert into `t_user`(`id`,`username`,`password`,`name`) values ('2','admin','2MknsKRe','系统管理员');
    insert into `t_user`(`id`,`username`,`password`,`name`) values ('3','root','F8ZCrW0Cui','test02');
    insert into `t_user`(`id`,`username`,`password`,`name`) values ('4','stu1','pL3tt2Za','学生1');
    insert into `t_user`(`id`,`username`,`password`,`name`) values ('5','stu2','Kn3Hv9qm','学生2');
    insert into` t_user`(`id`,`username`,`password`,`name`) values ('6','t1','nstqKRe','老师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');
    INSERT INTO user_role(id,user_id,role_id) VALUES (NULL,'1','7');
    INSERT INTO user_role(id,user_id,role_id) VALUES (NULL,'2','8');
    INSERT INTO user_role(id,user_id,role_id) VALUES (NULL,'3','9');
    INSERT INTO user_role(id,user_id,role_id) VALUES (NULL,'4','8');
    INSERT INTO user_role(id,user_id,role_id) VALUES (NULL,'5','10');
    

    把上述代码执行之后,结果如下:

    2、explain 之 id

    id 字段 是 select 查询的序列号,是一组数字,表示的是查询中执行 select 子句或者是操作表的执行顺序。id 情况有三种,接下来一一分析。 具体情况和结果如下:

    -- 第一种情况: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;
    
    -- 查询结果:id 的值都为 1 ,那么表的加载顺序就是从上到下
    +----+-------------+-------+------+-----------------------------+---------------+---------+---------------------+------+----------------------------------------------------+
    | id | select_type | table | type | possible_keys               | key           | key_len | ref                 | rows | Extra                                              |
    +----+-------------+-------+------+-----------------------------+---------------+---------+---------------------+------+----------------------------------------------------+
    |  1 | SIMPLE      | u     | ALL  | PRIMARY                     | NULL          | NULL    | NULL                |    5 | NULL                                               |
    |  1 | SIMPLE      | ur    | ref  | fk_ur_user_id,fk_ur_role_id | fk_ur_user_id | 99      | mysql_advanced.u.id |    1 | NULL                                               |
    |  1 | SIMPLE      | r     | ALL  | PRIMARY                     | NULL          | NULL    | NULL                |    5 | Using where; Using join buffer (Block Nested Loop) |
    +----+-------------+-------+------+-----------------------------+---------------+---------+---------------------+------+----------------------------------------------------+
    3 rows in set (0.00 sec)
    
    -- 第二种情况: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'));
    
    -- 查询结果:id 都不同。id 值越大,优先级越高,越先被执行
    +----+-------------+-----------+-------+----------------------+----------------------+---------+-------+------+-------------+
    | id | select_type | table     | type  | possible_keys        | key                  | key_len | ref   | rows | Extra       |
    +----+-------------+-----------+-------+----------------------+----------------------+---------+-------+------+-------------+
    |  1 | PRIMARY     | t_role    | const | PRIMARY              | PRIMARY              | 98      | const |    1 | NULL        |
    |  2 | SUBQUERY    | user_role | ref   | fk_ur_user_id        | fk_ur_user_id        | 99      | const |    1 | Using where |
    |  3 | SUBQUERY    | t_user    | const | unique_user_username | unique_user_username | 137     | const |    1 | Using index |
    +----+-------------+-----------+-------+----------------------+----------------------+---------+-------+------+-------------+
    3 rows in set (0.00 sec)
    
    -- 第三种情况: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;
    
    -- 查询结果:id 有相同的,也有不同的。
    -- <derived2> 中的 derived 指的是衍生的表,2 表示由 id=2 衍生出来的表
    +----+-------------+------------+--------+---------------+---------------+---------+-----------+------+-----------------------+
    | id | select_type | table      | type   | possible_keys | key           | key_len | ref       | rows | Extra                 |
    +----+-------------+------------+--------+---------------+---------------+---------+-----------+------+-----------------------+
    |  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL          | NULL    | NULL      |    2 | Using where           |
    |  1 | PRIMARY     | r          | eq_ref | PRIMARY       | PRIMARY       | 98      | a.role_id |    1 | NULL                  |
    |  2 | DERIVED     | ur         | ref    | fk_ur_user_id | fk_ur_user_id | 99      | const     |    1 | Using index condition |
    +----+-------------+------------+--------+---------------+---------------+---------+-----------+------+-----------------------+
    3 rows in set (0.00 sec)
    

    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
    -- SIMPLE
    explain select * from t_user;
    
    -- PRIMARY && SUBQUERY
    explain select * from t_user where id=(select id from user_role where role_id='9');
    
    -- DERIVED
    explain select * from (select * from t_user where id in ('1','2')) a;
    
    --UNION && UNION RESULT
    explain select * from t_user where id='1' union select * from t_user where id='2';
    

    4、explain 之 table 和 type

    explain 命令中的 table 字段表示这一行的数据是来源于哪一张表的。 比较好理解。重点来说一下 type 字段。type 显示的是访问类型,是较为重要的一个指标。通过这个指标我们就能够大概的知道当前 SQL 的耗时情况或执行效率情况。 type 的具体可取值如下:

    type含义
    NULLMySQL 不访问任何表、索引,直接返回结果
    system表只有一行记录(等于系统表),这是const类型的特例,一般不会出现
    const表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快。如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常亮。const 于将"主键" 或 "唯一" 索引的所有部分与常量值进行比较
    eq_ref类似 ref,区别在于使用的是唯一索引,使用主键的关联查询,关联查询出的记录只有一条。常见于主键或唯一索引扫描
    ref非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个)
    range只检索给定返回的行,使用一个索引来选择行。 where 之后出现 between , < , > , in 等操作
    indexindex 与 ALL 的区别为 index 类型只是遍历了索引树,通常比 ALL 快, ALL 是遍历数据文件
    all将遍历全表以找到匹配的行
    -- NULL
    explain select now();
    
    -- system
    explain select * from (select * from t_user where id='1') t;
    
    -- const
    explain select * from t_user where id='1';
    
    -- eq_ref
    explain select * from t_user u , t_role r where u.id = r.id;
    
    -- ref
    show index from t_user;
    create index idx_name on t_user(name);
    explain select * from t_user where name = 'a';
    
    -- range
    暂无SQL语句,以后补充
    
    -- index
    explain select id from t_user;
    
    -- all
    explain select * from t_user;
    

    5、explain 之 key、rows、extra

    -- explain 之 key
    possible_keys:显示可能应用在这张表的索引,一个或多个
    key:实际使用的索引,如果为 NULL,则没有使用索引
    key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好
    
    -- explain 之 rows
    rows 指的是扫描行的数量
    
    -- explain 之 extra
    其他的额外的执行计划信息,具体介绍如下表。
    如果出现 using filesort 或 using temporary 的情况,那么说明没有走索引。此时就要去进行 SQL 优化了
    
    extra含义
    using filesort说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,称为“文件排序”, 效率低
    using temporary使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于 order by 和group by;效率低
    using index表示相应的 select 操作使用了覆盖索引,避免访问表的数据行,效率不错
    -- using filesort
    explain select * from t_user order by password;
    
    -- using temporary
    explain select name from t_user group by password;
    
    -- using index
    explain select name from t_user order by name;
    

    四、show profile 分析 SQL

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

    -- 查看当前 MySQL 是否支持 profile 操作
    SELECT @@have_profiling;
    
    -- 查看 profiling 状态。0 表示未开启,1 表示开启
    SELECT @@profiling;
    
    -- 默认 profiling 是关闭的,可以通过 set 语句在 Session 级别开启 profiling 操作
    SET profiling = 1;
    
    -- 再次查看 profiling 状态,此时为 1 ,属于开启状态
    SELECT @@profiling;
    
    -- 先进行各种操作,然后使用 show profiles 进行查看分析
    select * from t_user;
    select * from t_role;
    select * from user_role;
    
    -- 使用 show profiles 进行查看分析
    show profiles;
    
    -- show profiles; 命令的结果。Duration 表示耗时,Query 表示执行的 SQL 语句
    +----------+------------+-------------------------+
    | Query_ID | Duration   | Query                   |
    +----------+------------+-------------------------+
    |        1 | 0.00007575 | SELECT @@PROFILING      |
    |        2 | 0.00013775 | select * from t_user    |
    |        3 | 0.00017750 | select * from t_role    |
    |        4 | 0.00007200 | select * from user_role |
    |        5 | 0.00015425 | select * from user_role |
    +----------+------------+-------------------------+
    5 rows in set, 1 warning (0.00 sec)
    
    -- 通过 show profile for query Query_ID 语句可以查看到该 SQL 执行过程中每个线程的状态和消耗的时间
    show profile for query 3;  -- 拿到 Query_ID=3 的 SQL 语句进行分析,查看它的耗时情况
    
    -- 查看结果
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000036 |
    | checking permissions | 0.000004 |
    | Opening tables       | 0.000011 |
    | init                 | 0.000011 |
    | System lock          | 0.000042 |
    | optimizing           | 0.000005 |
    | statistics           | 0.000008 |
    | preparing            | 0.000006 |
    | executing            | 0.000001 |
    | Sending data         | 0.000029 |
    | end                  | 0.000003 |
    | query end            | 0.000003 |
    | closing tables       | 0.000006 |
    | freeing items        | 0.000007 |
    | cleaning up          | 0.000007 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)
    

    TIPS: Sending data 耗时指的是 MySQL 从线程开始访问数据行并把结果返回给客户端这段时间,而不仅仅是返回给客户端的所用时间。由于在 Sending data 状态下,MySQL 线程往往需要做大量的磁盘读取操作,所以经常是整各查询中耗时最长的状态。

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

    -- 选择查看 CPU 的耗费时间
    show profile cpu for query 3;
    
    -- 其他示例
    show profile all for query 3;
    show profile block io for query 3;
    show profile context switch for query 3;
    show profile page faults for query 3;
    
    字段含义
    Statussql 语句执行的状态
    Durationsql 执行过程中每一个步骤的耗时
    CPU_user当前用户占有的 cpu
    CPU_system系统占有的 cpu

    五、trace 分析优化器

    MySQL5.6 提供了对 SQL 的跟踪 trace, 通过 trace 文件能够进一步了解为什么优化器选择 A 计划, 而不是选择 B 计划。打开 trace ,设置格式为 JSON,并设置 trace 最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示。 具体如下:

    SET optimizer_trace="enabled=on",end_markers_in_json=on;    -- 打开 trace ,并设置为 JSON 格式
    set optimizer_trace_max_mem_size=1000000;                   -- 设置最大使用内存
    select * from t_user;                                       -- 执行一条 SQL 语句
    
    -- 最后,检查 information_schema.optimizer_trace 就可以知道 MySQL 是如何执行 SQL 的
    select * from information_schema.optimizer_trace\G;
    
    -- 最终返回的结果
    *************************** 1. row ***************************
                                QUERY: select * from t_user
                                TRACE: {
      "steps": [
        {
          "join_preparation": {
            "select#": 1,
            "steps": [
              {
                "expanded_query": "/* select#1 */ select `t_user`.`id` AS `id`,`t_user`.`username` AS `username`,`t_user`.`password` AS `password`,`t_user`.`name` AS `name` from `t_user`"
              }
            ] /* steps */
          } /* join_preparation */
        },
        {
          "join_optimization": {
            "select#": 1,
            "steps": [
              {
                "table_dependencies": [
                  {
                    "table": "`t_user`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ] /* depends_on_map_bits */
                  }
                ] /* table_dependencies */
              },
              {
                "rows_estimation": [
                  {
                    "table": "`t_user`",
                    "table_scan": {
                      "rows": 5,
                      "cost": 1
                    } /* table_scan */
                  }
                ] /* rows_estimation */
              },
              {
                "considered_execution_plans": [
                  {
                    "plan_prefix": [
                    ] /* plan_prefix */,
                    "table": "`t_user`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "rows": 5,
                          "cost": 2,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 2,
                    "rows_for_plan": 5,
                    "chosen": true
                  }
                ] /* considered_execution_plans */
              },
              {
                "attaching_conditions_to_tables": {
                  "original_condition": null,
                  "attached_conditions_computation": [
                  ] /* attached_conditions_computation */,
                  "attached_conditions_summary": [
                    {
                      "table": "`t_user`",
                      "attached": null
                    }
                  ] /* attached_conditions_summary */
                } /* attaching_conditions_to_tables */
              },
              {
                "refine_plan": [
                  {
                    "table": "`t_user`",
                    "access_type": "table_scan"
                  }
                ] /* refine_plan */
              }
            ] /* steps */
          } /* join_optimization */
        },
        {
          "join_execution": {
            "select#": 1,
            "steps": [
            ] /* steps */
          } /* join_execution */
        }
      ] /* steps */
    }
    MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
              INSUFFICIENT_PRIVILEGES: 0
    1 row in set (0.01 sec)
    

    第七章:SQL 性能优化

    一、索引的使用

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

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

    -- 要验证索引提升查询效率,前提要有一张数据量很大的表。例如有 100 玩万条数据
    -- 如果没有大数据量的表,那就看一下步骤即可。假设 tb_item 表有 100 万数据
    select * from tb_item where id=1999\G;       -- 因为 id 是主键,有主键索引,所以查询很快
    select * from tb_item title='iphoneX 移动3G 32G941'\G;  -- title 没有索引,查询特别慢
    
    -- 解决 title 字段查询慢的问题:针对 title 字段,创建索引
    create index idx_item_title on tb_item(title);
    
    -- 索引创建完成之后,再次进行查询。这次就非常的快了
    select * from tb_item title='iphoneX 移动3G 32G941'\G;
    
    -- 通过 explain 查看执行计划,执行 SQL 时使用了刚才创建的索引
    explain select * from tb_item title='iphoneX 移动3G 32G941'\G;
    

    2、索引环境准备

    -- 创建 tb_seller 表
    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);
    

    3、避免索引失效

    第一种情况:全值匹配

    全值匹配: 对索引中所有列都指定具体值。该情况下,索引生效,执行效率高。

    -- 全值匹配
    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;
    
    -- 执行结果。通过 key 和 Extra 可以看出:全值匹配是走索引的
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-----------------------+
    | id | select_type | table     | type | possible_keys            | key                      | key_len | ref               | rows | Extra                 |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 | Using index condition |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-----------------------+
    1 row in set (0.00 sec)
    

    第二种情况:最左前缀法则

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

    -- 匹配最左前缀法则,走索引
    explain select * from tb_seller where name='小米科技';
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    | id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra                 |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using index condition |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    
    explain select * from tb_seller where name='小米科技' and status='1';
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-----------------------+
    | id | select_type | table     | type | possible_keys            | key                      | key_len | ref         | rows | Extra                 |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 | Using index condition |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-----------------------+
    
    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-----------------------+
    | id | select_type | table     | type | possible_keys            | key                      | key_len | ref               | rows | Extra                 |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 | Using index condition |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-----------------------+
    
    -- 违法最左前缀法则,索引失效
    explain select * from tb_seller where status='1';
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tb_seller | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    
    explain select * from tb_seller where status='1' and address='北京市';
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tb_seller | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
    
    -- 如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效
    explain select * from tb_seller where name='小米科技' and address='北京市';
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    | id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra                 |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using index condition |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    
    -- 如果是范围查询,那么右边的列不能使用索引,即索引失效
    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';   -- 走索引
    explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';   -- 右边列不走索引
    
    -- 根据前面的两个字段 name、status 查询是走索引的,但是最后一个条件 address 没有用到索引。
    
    -- 不要在索引列上进行运算操作,否则索引将失效
    
    -- substring(name,3,2) 表示从第 3 个字符开始截取,截取 2 个字符
    select * from tb_seller where substring(name,3,2)='科技';
    explain select * from tb_seller where substring(name,3,2)='科技';    -- 有运算操作,索引失效
    
    -- 字符串不加单引号,造成索引失效
    explain select * from tb_seller where name='小米科技' and status='1';   -- 走索引
    
    -- status 的值没有加单引号,索引失效。【注意:status 是 varchar 类型的】
    explain select * from tb_seller where name='小米科技' and status='1';   -- 不走索引
    
    -- 原因:在查询时没有对字符串加单引号,MySQL 的查询优化器会自动进行类型转换,造成索引失效
    
    -- 尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少使用 select * 
    -- using index:使用覆盖索引的时候就会出现
    -- using where:在查找使用索引的情况下,需要回表去查询所需的数据
    -- using index condition:查找使用了索引,但是需要回表查询数据
    -- using index;using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
    
    explain select * from tb_seller where name='小米科技';    -- 走索引,需回表查询
    explain select name from tb_seller where name='小米科技'; -- 走索引,不需要回表查询
    explain select name,status,address from tb_seller where name='小米科技';  -- 走索引,不需要回表查询
    explain select name,status,password from tb_seller where name='小米科技'; -- 走索引,需回表查询
    
    -- 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
    -- 示例,name 字段是索引列,而 nickname 不是索引列,中间是 or 进行连接。因此是不走索引的
    explain select * from tb_seller where name='小米科技' or nickname='小米科技旗舰店';
    
    -- 如果把 or 改成 and,那是会走索引的
    explain select * from tb_seller where name='小米科技' and nickname='小米科技旗舰店';
    
    -- 以 % 开头的 Like 模糊查询,索引失效
    -- 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效
    explain select * from tb_seller where name='%科技';  -- 索引失效
    explain select * from tb_seller where name='科技%';  -- 走索引
    explain select * from tb_seller where name='%科技%'; -- 索引失效
    
    -- 解决方案:通过覆盖索引来解决模糊查询导致的索引失效
    explain select name from tb_seller where name='%科技%';  -- 走索引
    explain select name,status from tb_seller where name='%科技%';  -- 走索引
    explain select name,status,address from tb_seller where name='%科技%';  -- 走索引
    -- 索引失效,password 列没有创建索引
    explain select name,status,address,password from tb_seller where name='%科技%';
    
    -- 如果 MySQL 评估使用索引比全表更慢,则不使用索引
    explain select * from tb_seller where address='北京市';   -- 不走索引,address 还没有索引
    create index indx_address on tb_seller(address);
    explain select * from tb_seller where address='北京市';   -- 虽创建了索引,但是仍然不走索引【数据太多,占比特大】
    explain select * from tb_seller where address='西安市';   -- 走索引,因为数据只有一条
    
    -- is NULL、is NOT NULL 有时索引失效
    -- 如果字段存在索引,那么如果 is null 的结果占比特别大,那就不走索引,而是进行全表扫描
    -- 如果 is null 的结果占比特别小,数据量小,那就走索引。is not null 同理
    
    explain select * from tb_seller where address is null;
    explain select * from tb_seller where address is not null;
    
    -- in 走索引,not in 索引失效
    explain select * from tb_seller where sellerid in ('oppo','xiaomi','sina');      -- 走索引
    explain select * from tb_seller where sellerid not in ('oppo','xiaomi','sina');  -- 不走索引
    
    -- 单列索引和复合索引:尽量使用复合索引,而少使用单列索引
    -- 创建复合索引。就相当于创建了三个索引:name、name+status、name+status+address
    create index idx_name_sta_address on tb_seller(name,status,address);
    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';  -- 走索引
    drop index idx_name_sta_address on tb_seller;
    
    -- 创建单列索引。数据库会选择一个最优的索引(辨识度最高索引)来使用,并不会使用全部索引
    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);
    
    -- 以下语句走索引,但只用到了 name 的索引。说明 name 索引是最优的索引
    -- 为什么 name 的所有是最优的?因为它的辨识度最高,只有一个值,而 status=1 address='北京市' 有多个值
    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
    

    4、查看所有的使用情况

    -- 查看当前会话的索引的使用情况
    show status like 'Handler_read%';
    
    -- 查看全局的索引的使用情况
    show global status like 'Handler_read%';
    
    -- 全局索引的使用情况结果
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 28    |
    | Handler_read_key      | 68    |
    | Handler_read_last     | 0     |
    | Handler_read_next     | 15    |
    | Handler_read_prev     | 0     |
    | Handler_read_rnd      | 15    |
    | Handler_read_rnd_next | 7884  |
    +-----------------------+-------+
    
    # Handler_read_first
    索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)
    
    # Handler_read_key
    如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用(这个值越高越好)
    
    # Handler_read_next
    按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加
    
    # Handler_read_prev
    按照键顺序读前一行的请求数。该读方法主要用于优化 ORDERBY...DESC
    
    # Handler_read_rnd
    根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。
    你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救
    
    # Handler_read_rnd_next
    在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引
    

    二、大批量插入数据

    -- 创建两张表:tb_user_1 和 tb_user_2。这两张表只是名字不同,其他的都相同
    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;
    
    -- 当使用 load 命令导入数据的时候,适当的设置可以提高导入的效率
    -- 脚本文件介绍: sql1.log --> 主键有序;sql2.log --> 主键无序
    
    -- 导入 sql1.log ,因为这个文件是有序的,所以导入比较快,耗时 18.47sec
    load data local infile '/root/sql1.log' into table `tb_user_1` fields terminated by ',' lines terminated by '\n';
    
    -- 导入 sql2.log ,因为这个文件是乱序的,所以导入比较快,耗时 1 min 51.42 sec
    load data local infile '/root/sql1.log' into table `tb_user_2` fields terminated by ',' lines terminated by '\n';
    
    # 对于 InnoDB 类型的表,有以下几种方式可以提高导入的效率:
    # 1、主键顺序插入
    因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,
    可以有效的提高导入数据的效率。如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,
    所以如果可以给表创建一个主键,将可以利用这点,来提高导入数据的效率。
    
    # 2、关闭唯一性校验
    在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,
    在导入结束后执行 SET UNIQUE_CHECKS=1,恢复唯一性校验,这样可以提高导入的效率
    
    # 3、手动提交事务
    如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,
    关闭自动提交,导入结束后再执行 SETAUTOCOMMIT=1,打开自动提交,这样也可以提高导入的效率
    

    三、优化 insert 语句

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

    -- 一、如果需要同时对一张表插入很多行数据时,应该尽量使用多个值表的 insert 语句,
    -- 一、这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert 语句快
    
    -- 原始方式插入数据:
    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 (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');
    

    四、优化 order by 语句

    -- 两种排序方式
    -- 第一种是通过对返回数据进行排序,也就是通常说的 filesort 排序,所有不是通过索引而直接返回排序结果的排序都叫 FileSort 排序
    create index idx_age on emp(age);
    explain select * from emp order by age desc;
    explain select * from emp order by age asc;
    
    -- 第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高
    explain select id from emp order by age asc;
    explain select id,age from emp order by age asc;
    
    -- 多字段排序
    explain select id from emp order by age,salary;
    explain select id from emp order by age asc,salary asc;
    explain select id from emp order by salary desc,age desc;
    explain select id from emp order by age desc,salary desc;
    explain select id from emp order by age asc,salary desc;
    
    -- 了解了 MySQL 的排序方式,优化目标就清晰了:尽量减少额外的排序,通过索引直接返回有序数据。
    -- where 条件和 Order by 使用相同的索引,并且 Order By 的顺序和索引顺序相同,
    -- 并且 Order by 的字段都是升序,或者都是降序。否则肯定需要额外的操作,这样就会出现 Using FileSort
    
    -- 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 系统变量,来增大排序区的大小,提高排序的效率。
    
    show variables like 'max_length_for_sort_data';
    show variables like 'sort_buffer_size';
    

    五、优化 group by 语句

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

    -- age 没有索引,优化 Using filesort
    explain select age,count(*) from emp group by age;  -- Using temporary; Using filesort
    explain select age,count(*) from emp group by age order by null;  -- Using temporary
    -- 从上面的例子可以看出,第一个 SQL 语句需要进行 "filesort",
    -- 而第二个 SQL 由于 order by null 不需要进行 "filesort",而上文提过 Filesort 往往非常耗费时间
    -- 所以通过 order by null 来优化 Using filesort
    
    -- 现在给 age 字段加索引,优化 Using temporary
    create index idx_age on emp(age);
    explain select age,count(*) from emp group by age order by null;  -- Using index
    

    六、优化子查询

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

    -- 查找有角色的所有的用户信息【优化前】
    select * from t_user where id in (select user_id from user_role);
    explain select * from t_user where id in (select user_id from user_role);
    
    -- 查找有角色的所有的用户信息【优化后】
    select u.* from t_user u,user_role ur where u.id=ur.user_id;
    explain select u.* from t_user u,user_role ur where u.id=ur.user_id;
    
    -- 连接(Join)查询之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作
    

    七、优化 or 条件

    对于包含 OR 的查询子句,如果要利用索引,则 OR 之间的每个条件列都必须用到索引,而且不能使用到复合索引;如果没有索引,则应该考虑增加索引。 示例代码如下:

    -- 获取 emp 表中的所有的索引
    show index from emp;
    explain select * from emp where id=1 or age=30;   -- 未走索引
    explain select * from emp where id=1 or id=2;     -- 走索引
    
    -- 建议使用 union 替换 or 来对 SQL 进行优化
    select * from emp where id=1 union select * from emp where age=30;
    
    -- 我们来比较下重要指标,发现主要差别是 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
    

    八、优化分页查询

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

    -- 原始查询第 20 万页的 10 条数据
    select * from tb_user_1 limit 20000,10;     -- 耗时 0.04 sec
    select * from tb_user_1 limit 200000,10;    -- 耗时 0.29 sec
    explain select * from tb_user_1 limit 200000,10;
    
    -- 优化思路一:在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容
    -- 优化后查询第 20 万页的 10 条数据
    select id from tb_user_1 order by id limit 200000,10;    -- 先对 id 排序。因为 id 是主键,有索引,所以很快
    select * from tb_user_1 t,(select id from tb_user_1 order by id limit 200000,10) a where t.id=a.id;  -- 耗时 0.05 sec
    explain select * from tb_user_1 t,(select id from tb_user_1 order by id limit 200000,10) a where t.id=a.id;
    
    -- 优化思路二:该方案适用于主键自增的表,可以把 Limit 查询转换成某个位置的查询
    select * from tb_user_1 where id > 200000 limit 10;   -- 耗时 0.00 sec【非常快】
    -- 优化思路二的局限性:主键要自增,而且不能出现断层【出现断层的话,查询的结果是不准确的】
    

    九、使用 SQL 提示

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

    -- 先查看索引,结果有:PRIMARY、idx_seller_name、idx_seller_status、idx_seller_address 四个索引
    show index from tb_seller;
    
    -- 创建复合索引
    create index idx_seller_name_sta_addr on tb_seller(name,status,address);
    
    -- SQL 提示的第一种方式:use index() 。通过它来选择自己指定的索引
    -- 在查询语句中表名的后面,添加 use index 来提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引
    -- 通过以下 SQL 语句,发现可能的索引有两个,但是数据库默认选择了 idx_seller_name 这个索引
    explain select * from tb_seller where name='小米科技';
    +----+-------------+-----------+------+------------------------------------------+-----------------+---------+-------+------+-----------------------+
    | id | select_type | table     | type | possible_keys                            | key             | key_len | ref   | rows | Extra                 |
    +----+-------------+-----------+------+------------------------------------------+-----------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name,idx_seller_name_sta_addr | idx_seller_name | 403     | const |    1 | Using index condition |
    +----+-------------+-----------+------+------------------------------------------+-----------------+---------+-------+------+-----------------------+
    
    -- 那如果我要指定具体的索引,那该如何设置?通过 use index() 来选择自己指定的索引。如下:
    explain select * from tb_seller use index(idx_seller_name_sta_addr) where name='小米科技';
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    | id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra                 |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using index condition |
    +----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
    
    -- SQL 提示的第二种方式:ignore index() 。通过它来选择自己不想要【忽略】的索引
    -- 如果用户只是单纯的想让 MySQL 忽略一个或者多个索引,则可以使用 ignore index 作为 hint
    explain select * from tb_seller ignore index(idx_seller_name_sta_addr) where name='小米科技';
    +----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+-----------------------+
    | id | select_type | table     | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
    +----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_name | idx_seller_name | 403     | const |    1 | Using index condition |
    +----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+-----------------------+
    
    -- 由于忽略了 idx_seller_name_sta_addr 这个复合索引,那么数据库就走了 idx_seller_name 这个单列索引
    
    -- SQL 提示的第三种方式:force index() 。强制 MySQL 使用自己指定的索引
    -- 为强制 MySQL 使用一个特定的索引,可在查询中使用 force index 作为 hint
    explain select * from tb_seller where address='北京市';  -- 由于通过条件查询的结果特别多,所以默认全表扫描,不走索引
    +----+-------------+-----------+------+--------------------+------+---------+------+------+-------------+
    | id | select_type | table     | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-----------+------+--------------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tb_seller | ALL  | idx_seller_address | NULL | NULL    | NULL |   12 | Using where |
    +----+-------------+-----------+------+--------------------+------+---------+------+------+-------------+
    
    -- 通过 use index() 让其走索引,发现数据库并没有走索引
    -- 原因是 use index() 只是给数据库提供参考【在下列 SQL 中,数据库认为走全表扫描更快,所以不走指定的索引】
    explain select * from tb_seller use index(idx_seller_address) where address='北京市';     
    +----+-------------+-----------+------+--------------------+------+---------+------+------+-------------+
    | id | select_type | table     | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
    +----+-------------+-----------+------+--------------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tb_seller | ALL  | idx_seller_address | NULL | NULL    | NULL |   12 | Using where |
    +----+-------------+-----------+------+--------------------+------+---------+------+------+-------------+
    
    -- 强制让其走索引
    explain select * from tb_seller force index(idx_seller_address) where address='北京市';  
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+-----------------------+
    | id | select_type | table     | type | possible_keys      | key                | key_len | ref   | rows | Extra                 |
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tb_seller | ref  | idx_seller_address | idx_seller_address | 403     | const |   11 | Using index condition |
    +----+-------------+-----------+------+--------------------+--------------------+---------+-------+------+-----------------------+
    

    第八章:应用优化

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

    一、应用优化

    1、使用连接池

    对于访问数据库来说,建立连接的代价是比较昂贵的,因为需要频繁的创建和关闭连接,这是比较耗费资源的。因此,我们有必要建立数据库连接池,以提高访问的性能。常见的连接池有:Druid,C3P0、DBCP、Hikari 等。

    2、减少对 MySQL 的访问

    -- 第一种方式:避免对数据进行重复检索
    -- 在编写应用代码时,需要能够理清对数据库的访问逻辑。
    -- 能够一次连接就获取到结果的,就不用两次连接,这样可以大大减少对数据库无用的重复请求。
    
    -- 比如,需要获取员工的 id 和 name 字段,则查询如下
    select id,name from emp;
    
    -- 之后,在业务逻辑中有需要获取到书籍状态信息,则查询如下
    select id,status from emp;
    
    -- 这样,就需要向数据库提交两次请求,数据库就要做两次查询操作。其实完全可以用一条 SQL 语句得到想要的结果
    select id,name,status from emp;
    
    -- 第二种方式:增加 cache 层
    
    -- 在应用中,我们可以在应用中增加缓存层来达到减轻数据库负担的目的。
    -- 缓存层有很多种,也有很多实现方式,只要能达到降低数据库的负担又能满足应用需求就可以。
    -- 因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储,
    -- 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存,或者使用 redis 数据库来缓存数据。
    

    3、负载均衡

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

    一、利用 MySQL 复制分流查询

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

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

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

    二、MySQL 中查询缓存优化

    1、查询缓存优化概述

    开启 MySQL 的查询缓存,当执行 完全相同的 SQL 语句 的时候,服务器就会直接从缓存中读取结果。 当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存。完全相同的 SQL 语句 指的是两条 SQL 语句一模一样,不能够忽略大小写。例如:select 和 Select 是不同的。

    2、缓存执行流程

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

    3、查询缓存配置

    -- 1、查看当前的 MySQL 数据库是否支持查询缓存
    SHOW VARIABLES LIKE 'have_query_cache';
    
    -- 2、查看当前 MySQL 是否开启了查询缓存
    SHOW VARIABLES LIKE 'query_cache_type';
    
    -- 3、查看查询缓存的占用大小
    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查询缓存中的块总数

    4、开启查询缓存

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

    含义
    OFF 或 0查询缓存功能关闭
    ON 或 1查询缓存功能打开,SELECT 的结果符合缓存条件即会缓存,否则,不予缓存,显式指定 SQL_NO_CACHE,不予缓存
    DEMAND 或 2查询缓存功能按需进行,显式指定 SQL_CACHE 的 SELECT 语句才会缓存;其它均不予缓存
    # 开启查询缓存
    vim /usr/my.cnf
    
    # 添加如下配置
    query_cache_type=1
    
    # 保存退出
    :wq
    
    # 重启 MySQL 服务器,使配置生效
    service mysql restart
    
    # 然后就可以在命令行执行 SQL 语句进行验证,执行一条比较耗时的 SQL 语句,然后再多执行几次,查看后面几次的执行时间;
    # 获取通过查看查询缓存的缓存命中数,来判定是否走查询缓存
    

    5、查询缓存 SELECT 选项

    -- 可以在 SELECT 语句中指定两个与查询缓存相关的选项:SQL_CACHE 和 SQL_NO_CACHE
    -- SQL_CACHE    => 如果查询结果是可缓存的,并且 query_cache_type 系统变量的值为 ON 或 DEMAND ,则缓存查询结果
    -- SQL_NO_CACHE => 服务器不使用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果
    show status like 'Qcache%';      -- 查看命中情况
    select SQL_CACHE id from emp;
    select id from emp;
    show status like 'Qcache%';      -- 查看命中情况
    
    select SQL_NO_CACHE id from emp;
    show status like 'Qcache%';      -- 查看命中情况
    

    6、查询缓存失效的情况

    -- 1、SQL 语句不一致的情况,要想命中查询缓存,查询的 SQL 语句必须完全一致
    select count(*) from emp;
    Select count(*) from emp;
    -- 结果:无法命中缓存。因为 select 和 Select 不一样,后者是大写 S
    
    -- 2、当查询语句中有一些不确定的值时,则不会进行缓存。
    -- 例如:now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database()
    select database();
    select uuid();
    
    -- 3、不使用任何表查询语句
    select 'Hello World';
    
    -- 4、查询 mysql、information_schema 或 performance_schema 数据库中的表时,不会走查询缓存
    select * from information_schema.engines;
    
    -- 5、在存储的函数,触发器或事件的主体内执行的查询
    
    -- 6、如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除
    -- 6、这包括使用 MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句
    -- 6、如:INSERT、UPDATE、DELETE、TRUNCATETABLE、ALTER TABLE、DROP TABLE 或 DROP DATABASE
    

    三、MySQL 内存管理及优化

    1、内存优化原则

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

    2、MyISAM 内存优化

    MyISAM 存储引擎使用 key_buffer 缓存索引块,加速 MyISAM 索引的读写速度。对于 MyISAM 表的数据块,MySQL 没有特别的缓存机制,完全依赖于操作系统的 IO 缓存。 具体的优化操作以及参数如下:

    -- 优化参数一:key_buffer_size
    -- 参数一决定 MyISAM 索引块缓存区的大小,直接影响到 MyISAM 表的存取效率。
    -- 可以在 MySQL 参数文件中设置参数一的值,对于一般 MyISAM 数据库,建议至少将 1/4 可用内存分配给参数一
    -- 在 /usr/my.cnf 中做如下配置
    key_buffer_size=512M
    
    -- 优化参数二:read_buffer_size
    -- 如果需要经常顺序扫描 MyISAM 表,可以通过增大参数二的值来改善性能。
    -- 但需要注意的是:参数二是每个 session 独占的,如果默认值设置太大,就会造成内存浪费
    -- 在 /usr/my.cnf 中做如下配置,具体多大视情况而定
    read_buffer_size=xxx
    
    -- 优化参数三:read_rnd_buffer_size
    -- 对于需要做排序的 MyISAM 表的查询,如带有 order by 子句的 SQL,适当增加参数三的值,可以改善此类的 SQL 性能
    -- 但需要注意的是:参数三是每个 session 独占的,如果默认值设置太大,就会造成内存浪费
    -- 在 /usr/my.cnf 中做如下配置,具体多大视情况而定
    read_rnd_buffer_size=xxx
    
    -- 查看各个优化参数的值,其单位是字节
    show variables like 'key_buffer_size';        -- 默认是:8388608 Byte
    show variables like 'read_buffer_size';       -- 默认是:131072 Byte
    show variables like 'read_rnd_buffer_size';   -- 默认是:262144 Byte
    

    3、InnoDB 内存优化

    InnoDB 用一块内存区做 IO 缓存池,该缓存池不仅用来缓存 InnoDB 的索引块,而且也用来缓存 InnoDB 的数据块。 具体的优化操作以及参数如下:

    -- 优化参数一:innodb_buffer_pool_size
    -- 该变量决定了 InnoDB 存储引擎表数据和索引数据的最大缓存区大小。
    -- 在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size 的值越大,
    -- 缓存命中率越高,访问 InnoDB 表需要的磁盘 I/O 就越少,性能也就越高。
    -- 在 /usr/my.cnf 中做如下配置,具体多大视情况而定
    innodb_buffer_pool_size=xxx
    
    -- 优化参数二:innodb_log_buffer_size
    -- 决定了 InnoDB 重做日志缓存的大小,对于可能产生大量更新记录的大事务,
    -- 增加参数二的大小,可以避免 InnoDB 在事务提交前就执行不必要的日志写入磁盘操作。
    -- 在 /usr/my.cnf 中做如下配置,具体多大视情况而定
    innodb_log_buffer_size=xxx
    
    -- 查看各个优化参数的值,其单位是字节
    show variables like 'innodb_buffer_pool_size';    -- 默认是:134217728 Byte
    show variables like 'innodb_log_buffer_size';     -- 默认是:8388608 Byte
    

    四、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 参数控制 MySQ L监听 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 。

    4、thread_cache_size

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

    5、innodb_lock_wait_timeout

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

    6、查看各种参数默认值

    -- 如果需要配置,使用 vim /usr/my.cnf 去进行配置
    -- 查看各种参数的默认值
    
    show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
    
     show variables like 'back_log';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | back_log      | 80    |
    +---------------+-------+
    
    show variables like 'table_open_cache';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | table_open_cache | 2000  |
    +------------------+-------+
    
    show variables like 'thread_cache_size';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | thread_cache_size | 9     |
    +-------------------+-------+
    
    show variables like 'innodb_lock_wait_timeout';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_lock_wait_timeout | 50    |
    +--------------------------+-------+
    

    第九章:MySQL 锁的问题

    一、锁的概述及分类

    1、锁的概述

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

    2、锁的分类

    # 从对数据操作的粒度分:
    表锁:操作时,会锁定整个表
    行锁:操作时,会锁定当前操作行
    
    # 从对数据操作的类型分:
    读锁(共享锁):针对同一份数据,【多个读操作】可以同时进行而不会互相影响
    写锁(排它锁):当前操作没有完成之前,【它会阻断其他写锁和读锁】
    

    3、MySQL 对锁的支持

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

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

    MySQL 这三种锁的特性可大致归纳如下:

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

    从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适! 仅从锁的角度来说:表级锁更适合于以查询为主, 只有少量按索引条件更新数据的应用,如Web 应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并查询的应用,如一些在线事务处理(OLTP)系统。

    二、MyISAM 的表锁

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

    1、如何加表锁

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

    -- 显示加表锁语法
    lock table table_name read;       -- 加读锁
    lock table table_name write;      -- 加写锁
    

    2、读锁案例

    先进行环境准备,创建两张表,并插入数据。 代码如下:

    -- 创建 tb_book 表并插入数据
    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');
    
    -- 创建 tb_user 表并插入数据
    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 表加读锁
    lock table tb_book read;
    
    -- 2、【客户端一】执行查询 tb_book 表的操作:可以正常执行,查询出数据
    select * from tb_book;
    
    -- 3、【客户端二】执行查询 tb_book 表的操作:也可以正常执行,并查询出数据
    select * from tb_book;
    
    -- 4、【客户端一】执行查询 tb_user 表的操作:无法查询,因为没有释放锁
    -- 查询未锁定的表
    select * from tb_user;
    
    -- 5、【客户端二】执行查询 tb_user 表的操作:可以正常执行并查询出数据
    select * from tb_user;
    
    -- 6、【客户端一】执行更新 tb_book 表的操作:无法更新,因为有读锁存在
    -- 执行插入,直接报错,由于当前 tb_book 获得的是读锁,不能执行更新操作
    update tb_book set name='Python' where id=2;
    
    -- 7、【客户端二】执行更新 tb_book 表的操作:无法更新,需要客户端一释放读锁,否则处于阻塞状态
    -- 当在客户端一中释放锁指令 unlock tables 后,客户端二中的 update 语句,立即执行
    update tb_book set name='Python' where id=2;
    

    上述的各种操作配图如下:

    小提示: 当给表加了读锁之后,它不会阻塞其他线程的读操作,但是会阻塞其他线程的写操作。

    3、写锁案例

    -- 1、【客户端一】给 tb_book 加写锁【写锁是排它锁,会阻塞其他客户端进行读和写的操作】
    -- 2、那么【客户端一】可以对 tb_book 进行读和写的操作
    -- 3、但是【客户端二】对 tb_book 表既不能读,也不能写
    
    -- 【客户端一】的操作:对 tb_book 表进行读和写都没问题
    lock table tb_book write;
    select * from tb_book;
    update tb_book set name='java编程思想(第二版)' where id=1;
    
    -- 【客户端二】的操作:无法对 tb_book 表进行读和写
    select * from tb_book;   -- 处于阻塞中,一直在等【客户端一】中释放锁指令 unlock tables
    -- 当在【客户端一】中释放锁指令 unlock tables 后,【客户端二】中的 select 语句,立即执行;
    

    4、结论

    # 结论如下
    1、对 MyISAM 表加读锁,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求
    2、对 MyISAM 表加写锁,则会阻塞其他用户对同一表的读和写操作
    
    此外,MyISAM 的读写锁调度是【写优先】,这也是 MyISAM 不适合做写为主的表的存储引擎的原因。
    因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。
    

    最简结论: 简而言之,读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。

    5、查看锁的争用情况

    -- 查看锁的争用情况的命令如下
    show open tables;
    -- 【In_user】 表示当前哪些表加锁了
    -- 【Name_locked】表示名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作
    
    show status like 'Table_locks%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Table_locks_immediate | 87    |
    | Table_locks_waited    | 1     |
    +-----------------------+-------+
    
    -- 【Table_locks_immediate】指的是能够立即获得表级锁的次数,每立即获取锁,值加 1
    -- 【Table_locks_waited】指的是不能立即获取表级锁而需要等待的次数,每等待一次,该值加 1。此值高说明存在着较为严重的表级锁争用情况
    

    三、InnoDB 的行锁

    1、行锁介绍与背景知识

    行锁介绍

    # InnoDB 存储引擎既支持表锁,也支持行锁,但默认使用的是行锁
    
    # 行锁的特点
    1、偏向 InnoDB 存储引擎
    2、开销大,加锁慢
    3、会出现死锁
    4、锁定粒度最小,发生锁冲突的概率最低,并发度也最高
    
    # InnoDB 与 MyISAM 的最大不同有两点:
    1、支持事务
    2、采用了行级锁
    

    背景知识:事务及其 ACID 属性

    事务 是由一组 SQL 语句组成的逻辑处理单元。事务具有四个特性,简称为 ACID 具体介绍如下表:

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

    背景知识:并发事务带来的问题

    问题含义
    丢失更新(LostUpdate)当两个或多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖
    脏读(DirtyReads)一个事务读取到了其他事务中未提交的数据
    不可重复读(Non-RepeatableReads)一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现和以前读出的数据不一致
    幻读(PhantomReads)一个事务按照相同的查询条件重新读取以前查询过的数据,却发现其他事务插入了满足其查询条件的新数据

    背景知识:事务的隔离级别

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

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

    备注: √ 代表可能出现, × 代表不会出现。

    -- MySQL 的默认隔离级别为:REPEATABLE-READ
    
    show variables like 'tx_isolation';    -- 隔离级别查看方式
    select @@tx_isolation;                 -- 隔离级别查看方式
    

    2、InnoDB 的行锁模式

    # InnoDB 实现了以下两种类型的行锁
    
    # 共享锁(S)
    又称为读锁,简称 S 锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
    
    # 排他锁(X)
    又称为写锁,简称 X 锁,排他锁就是不能与其他锁并存。
    如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁。
    这包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
    
    # 总结
    对于 UPDATE、DELETE 和 INSERT 语句,InnoDB 会自动给涉及数据集加排他锁(X);
    对于普通 SELECT 语句,InnoDB 不会加任何锁。以通过以下语句显式地给记录集加共享锁或排他锁。
    
    
    -- 可以通过以下语句显式地给记录集加共享锁或排他锁
    
    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE  -- 共享锁(S)
    SELECT * FROM table_name WHERE ... FOR UPDATE          -- 排他锁(X)
    

    3、行锁的基本演示

    先进行环境准备,创建两张表,并插入数据。 代码如下:

    --创建 test_innodb_lock 表并插入数据
    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);
    

    然后我们开启两个客户端,左边是客户端一,右边是客户端二,然后进行相应操作。 具体操作如下:

    -- 开启两个客户端,按顺序执行以下命令
    
    -- 1、【客户端一】:关闭自动提交
    set autocommit=0;
    
    -- 2、【客户端二】:关闭自动提交
    set autocommit=0;
    
    -- 3、【客户端一】执行查询,可查询出结果
    select * from test_innodb_lock;
    
    -- 4、【客户端二】执行查询,可查询出结果
    select * from test_innodb_lock;
    
    -- 5、【客户端一】执行更新操作,那么 innodb 会对此表加行锁
    update test_innodb_lock set name='300' where id=3;
    
    -- 6、【客户端二】执行更新操作,和客户端操作同一行,这时【客户端二】出现阻塞情况
    update test_innodb_lock set name='30' where id=3;
    
    -- 7、【客户端一】提交事务,那么【客户端二】执行了更新操作
    commit;   -- 提交事务之后,释放排它锁,【客户端二】就可以执行操作了
    select * from test_innodb_lock;  -- id=3 的那么 name 字段是 300 ,不是 30 ,因为【客户端二】还未提交事务
    
    -- 8、【客户端二】提交事务
    commit;
    select * from test_innodb_lock;  -- id=3 的 name 字段是 30
    
    -- 9、客户端一】再次查询,发现 id=3 的记录的 name 字段还是 300
    select * from test_innodb_lock;    -- 发现 id=3 的记录的 name 字段还是 300
    -- 这个因为数据库的隔离级别的原因。因此,【客户端一】还需再次提交一次事务即可查到正确结果
    commit;
    select * from test_innodb_lock;    -- 发现 id=3 的记录的 name 字段还是 30
    
    -- 注意:操作同一行才会出现上述情况,如果操作不同行,那就不会出现任何问题
    

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

    如果不通过索引条件检索数据,那么 InnoDB 将对表中的所有记录加锁,实际效果跟表锁一样。这将导致 InnoDB 存储引擎的处理能力下降。 那么接下来就介绍什么情况下会导致 行锁升级为表锁

    -- 查看当前表的索引:有两个,都是单列索引
    show index from test_innodb_lock;
    
    -- 开启两个客户端,按顺序执行以下命令
    -- 1、【客户端一】:关闭自动提交
    set autocommit=0;
    
    -- 2、【客户端二】:关闭自动提交
    set autocommit=0;
    
    -- 3、【客户端一】:执行更新操作,操作的是 name=900 的那一行
    update test_innodb_lock set sex='2' where name=900;
    select * from test_innodb_lock;   -- 结果没有问题,但是行锁已经升级为表锁
    
    -- 4、【客户端二】:执行更新操作,操作的是 name=800 的那一行
    update test_innodb_lock set sex='2' where name=800;
    -- 这个时候发现,【客户端二】处于了阻塞状态。可操作的是不同行,为什么出现了阻塞呢?原因是 name 的索引失效了
    -- 由于执行更新时,name 字段本来为 varchar 类型,我们是作为数组类型使用,存在类型转换,索引失效,最终行锁变为表锁
    
    -- 总结:如果 where 条件后面的字段没有索引或者是索引失效,那么行锁就会升级成表锁
    -- 如果【客户端二】还需要执行操作,就必须等【客户端一】commit 提交事务了
    

    5、间隙锁的危害

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

    -- 开启两个客户端,按顺序执行以下命令
    -- 1、【客户端一】:关闭自动提交
    set autocommit=0;
    
    -- 2、【客户端二】:关闭自动提交
    set autocommit=0;
    
    -- 3、【客户端一】:对符合条件的数据进行更新。此时 id=1,2,3,4 的记录都会被加锁
    -- 但是 id=2 这项记录不存在,但是也会被加锁,这就叫间隙锁
    -- 因此,当【】想要插入一条 id=2 的数据时,便会进入到阻塞状态
    update test_innodb_lock set sex='0' where id < 4;
    
    -- 3、【客户端二】:插入数据
    -- 由于间隙锁的存在,【客户端二】只能处于阻塞状态
    insert into test_innodb_lock values (2,'200','1');
    

    6、行锁争用情况

    -- 查看行锁的争用情况
    show status like 'innodb_row_lock%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | Innodb_row_lock_current_waits | 0     |
    | Innodb_row_lock_time          | 4714  |
    | Innodb_row_lock_time_avg      | 4714  |
    | Innodb_row_lock_time_max      | 4714  |
    | Innodb_row_lock_waits         | 1     |
    +-------------------------------+-------+
    
    -- 各项指标的介绍
    -- Innodb_row_lock_current_waits ==> 当前正在等待锁定的数量
    -- Innodb_row_lock_time          ==> 从系统启动到现在锁定总时间长度
    -- Innodb_row_lock_time_avg      ==> 每次等待所花平均时长【重点关注】
    -- Innodb_row_lock_time_max      ==> 从系统启动到现在等待最长的一次所花的时间
    -- Innodb_row_lock_waits         ==> 系统启动后到现在总共等待的次数【重点关注】
    
    -- 如果【重点关注】的指标时间过长,那就需要考虑进行优化了。
    -- 当等待的次数很高,而且每次等待的时长也不小的时候,
    -- 我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手制定优化计划。
    

    7、总结

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

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

    第十章:SQL 技巧与常用工具

    一、SQL 技巧

    1、SQL 执行顺序

    -- 编写 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>
    
    -- 执行 SQL 的顺序
    
    FROM    <left_table>
    ON      <join_condition>
    <join_type>    JOIN    <right_table>
    WHERE       <where_condition>
    GROUP BY    <group_by_list>
    HAVING      <having_condition>
    SELECT DISTINCT <selectlist>
    ORDER BY    <order_by_condition>
    LIMIT       <limit_params>
    

    2、正则表达式

    正则表达式(Regular Expression) 是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。 具体的 SQL 使用以及具体的详细介绍如下:

    -- 正则表达式在 SQL 中的使用
    
    select * from emp where name regexp '^T';
    select * from emp where name regexp '2$';
    select * from emp where name regexp '[uvw]';
    
    符号含义符号含义
    ^在字符串开始处进行匹配a?匹配零个或者一个a
    $在字符串末尾处进行匹配a1a2
    .匹配任意单个字符, 包括换行符a(m)匹配m个a
    [...]匹配出括号内的任意字符a(m,)至少匹配m个a
    [^...]匹配不出括号内的任意字符a(m,n)匹配m个a 到 n个a
    a*匹配零个或者多个a(包括空串)a(,n)匹配0到n个a
    a+匹配一个或者多个a(不包括空串)(...)将模式元素组成单一元素

    3、数字函数

    -- 数字函数的使用示例
    
    select abs(-10);            -- 求绝对值
    select ceil(8.1);           -- 向上取整
    select floor(9.1);          -- 向下取整
    select floor(9.9);          -- 向下取整
    select round(4.5);          -- 四舍五入
    select round(4.4);          -- 四舍五入
    select rand();              -- 生成 [0-1) 的随机数
    
    -- 更多数字函数请参考和官方文档
    
    函数名称作用
    ABS求绝对值
    SQRT求二次方根
    MOD求余数
    CEIL 和 CEILING两个函数功能相同,都是返回不小于参数的最小整数,即向上取整
    FLOOR向下取整,返回值转化为一个BIGINT
    RAND生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列
    ROUND对所传参数进行四舍五入
    SIGN返回参数的符号
    POW 和 POWER两个函数的功能相同,都是所传参数的次方的结果值
    SIN求正弦值
    ASIN求反正弦值,与函数 SIN 互为反函数
    COS求余弦值
    ACOS求反余弦值,与函数 COS 互为反函数
    TAN求正切值
    ATAN求反正切值,与函数 TAN 互为反函数
    COT求余切值

    4、字符串函数

    -- 字符串函数的使用示例
    
    select length('Hello World');             -- 求字符串长度
    select concat('Hello',' World');          -- 合并字符串
    select concat('Hello',' World',' !');     -- 合并字符串
    select lower('AAA');                      -- 将字符串中的字母转换为小写
    select left('javascript',4);              -- 从左侧字截取符串,返回字符串左边的若干个字符
    select right('javascript',6);             -- 从右侧字截取符串,返回字符串右边的若干个字符
    select substring('javascript',1,4);       -- 截取字符串,索引起始位置是 1 而不是 0
    
    -- 更多字符串函数请参考和官方文档
    
    函数名称作用
    LENGTH计算字符串长度函数,返回字符串的字节长度
    CONCAT合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个
    INSERT替换字符串函数
    LOWER将字符串中的字母转换为小写
    UPPER将字符串中的字母转换为大写
    LEFT从左侧字截取符串,返回字符串左边的若干个字符
    RIGHT从右侧字截取符串,返回字符串右边的若干个字符
    TRIM删除字符串左右两侧的空格
    REPLACE字符串替换函数,返回替换后的新字符串
    SUBSTRING截取字符串,返回从指定位置开始的指定长度的字符换
    REVERSE字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串

    5、日期函数

    函数名称作用
    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获取指定日期在一周内的对应的工作日索引

    6、聚合函数

    函数名称作用
    MAX查询指定列的最大值
    MIN查询指定列的最小值
    COUNT统计查询结果的行数
    SUM求和,返回指定列的总和
    AVG求平均值,返回指定列数据的平均值

    二、MySQL 的常用工具

    1、mysql

    该 mysql 不是指 MySQL 服务,而是指 mysql 的客户端工具。 具体用法如下:

    -- 语法模板
    mysql [options] [database]
    
    -- 参数选项(options):
    -u, --user=name          指定用户名
    -p, --password[=name]    指定密码
    -h, --host=name          指定服务器IP或域名
    -P, --port=xxxx          指定连接端口
    -e, --execute=name       执行SQL语句并退出
    
    -- -u/p/h/P 都是连接选项,-e 是执行选项。此选项可以在Mysql客户端执行SQL语句,
    -- 而不用连接到MySQL数据库再执行,对于一些批处理脚本,这种方式尤其方便。
    
    -- 具体使用示例
    mysql -h127.0.0.1 -P3306 -uroot -proot
    mysql -h 127.0.0.1 -P 3306 -u root -p root
    mysql --host=127.0.0.1 --port=3306 --user=root --password=root
    mysql -uroot -proot mysql_advanced -e "select * from emp";
    

    2、mysqladmin

    mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。 具体使用如下:

    -- 可以通过 mysqladmin --help 指令查看帮助文档
    -- 具体使用示例
    
    mysqladmin --help                           -- 查看帮助,同时也可以看到数据库版本信息
    mysqladmin -uroot -proot create 'test01';   -- 直接创建 test01 数据库
    mysqladmin -uroot -proot drop 'test01';     -- 直接删除 test01 数据库
    mysqladmin -uroot -proot version;           -- 查看数据库版本
    mysqladmin -V                               -- 查看数据库版本
    mysqladmin --version                        -- 查看数据库版本
    

    3、mysqlbinlog

    由于服务器生成的二进制日志文件以二进制格式保存,所以如果想要检查这些文本的文本格式,就会使用到 mysqlbinlog 日志管理工具。

    -- 查看二进制日志信息
    mysqlbinlog [options] log-files1 log-files2 ...
    
    -- 选项:
    -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[db2db3...]
    
    mysqldump [options] --all-databases/-A
    
    # 连接选项参数(options):
        -u, --user=name          指定用户名
        -p, --password[=name]    指定密码
        -h, --host=name          指定服务器IP或域名
        -P, --port=xxxx          指定连接端口
    
    # 输出内容参数(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           不包含数据
        -T,--tab=name          自动生成两个文件:一个 .sql 文件,创建表结构的语句;
                               一个 .txt 文件,数据文件,相当于 select into outfile
    
    -- 具体使用示例
    
    mysqldump -uroot -proot test > test01.sql        -- 备份 test 数据库
    mysqldump -uroot -proot test emp > test01.sql    -- 备份 test 数据库下的 emp 表
    mysqldump -uroot -proot test --skip-add-drop-table > test01.sql # 备份 test 数据库,不需要 drop table 的语句
    
    -- 对 test 数据库下的 emp 表进行备份, -T 选项表示将表结构和数据文件分开存放
    -- /tmp 表示把生成的 .sql 表结构文件和 .txt 数据文件存放到 tmp 目录下
    mysqldump -uroot -proot -T /tmp test emp
    

    5、mysqlimport/source

    mysqlimport 是客户端数据导入工具,用来导入 mysqldump 加 -T 参数 后导出的文本文件。

    -- 语法模板
    mysqlimport [options] db_name textfile1 [textfile2...]
    
    -- 具体使用示例
    delete from emp;
    mysqlimport -uroot -proot test /tmp/emp.txt;
    
    -- 如果需要导入 sql 文件,可以使用 mysql 中的 source 指令
    source /root/emp.sql
    

    6、mysqlshow

    mysqlshow 客户端对象查找工具,用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

    # 参数选项
    
    --count   显示数据库及表的统计信息(数据库,表均可以不指定)
    -i        显示指定数据库或者指定表的状态信息
    
    # 语法模板
    mysqlshow [options] [db_name [table_name [col_name]]]
    
    # 使用示例:1、查询每个数据库的表的数量及表中记录的数量
    mysqlshow -uroot -proot --count
    +--------------------+--------+--------------+
    |     Databases      | Tables |  Total Rows  |
    +--------------------+--------+--------------+
    | information_schema |     59 |        20420 |
    | mysql              |     28 |         2317 |
    | mysql_advanced     |     12 |      1000064 |
    | performance_schema |     52 |        17867 |
    | test               |      0 |            0 |
    +--------------------+--------+--------------+
    
    # 使用示例:2、查询 test 库中每个表中的字段书,及行数
    mysqlshow -uroot -proot mysql_advanced --count
    +------------------+----------+------------+
    |      Tables      | Columns  | Total Rows |
    +------------------+----------+------------+
    | city             |        3 |          4 |
    | country          |        2 |          4 |
    | emp              |        4 |          4 |
    | emp_logs         |        5 |         11 |
    | t_role           |        4 |          5 |
    | t_user           |        4 |          5 |
    | tb_book          |        4 |          2 |
    | tb_seller        |        7 |         12 |
    | tb_user          |        2 |          2 |
    | tb_user_1        |       12 |    1000000 |
    | test_innodb_lock |        3 |          9 |
    | user_role        |        3 |          6 |
    +------------------+----------+------------+
    
    # 查询 mysql_advanced 库中 emp 表的详细情况
    mysqlshow -uroot -proot mysql_advanced emp --count
    +--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
    | Field  | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
    +--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
    | id     | int(11)     |                 | NO   | PRI |         | auto_increment | select,insert,update,references |         |
    | name   | varchar(50) | utf8_general_ci | NO   |     |         |                | select,insert,update,references | 姓名    |
    | age    | int(11)     |                 | YES  | MUL |         |                | select,insert,update,references | 年龄    |
    | salary | int(11)     |                 | YES  |     |         |                | select,insert,update,references | 薪水    |
    +--------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
    
    # 显示 mysql_advanced 数据库下 emp 表的状态信息
    mysqlshow -uroot -proot mysql_advanced emp -i
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
    | emp  | InnoDB | 10      | Compact    | 4    | 4096           | 16384       | 0               | 16384        | 0         | 5              | 2021-04-04 19:34:24 |             |            | utf8_general_ci |          |                |         |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
    

    三、日志相关

    在任何一种数据库中,都会有各种各样的日志,记录着数据库工作的方方面面,以帮助数据库管理员追踪数据库曾经发生过的各种事件。 MySQL 也不例外,在 MySQL 中,有四种不同的日志,分别是 错误日志、二进制日志(BINLOG 日志)、查询日志和慢查询日志, 这些日志记录着数据库在不同方面的踪迹。

    1、错误日志

    错误日志 是 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。 当数据库出现任何故障导致无法正常使用时,可以首先查看此日志。该日志是默认开启的, 默认存放目录为 mysql 的数据目录(var/lib/mysql), 默认的日志文件名为hostname.err(hostname 是主机名)。

    -- 查看日志文件存放位置的指令
    showvariableslike'log_error%';
    
    -- 查看日志内容
    cat  /var/lib/mysql/47.113.85.135.localdomain.err
    tail -f /var/lib/mysql/47.113.85.135.localdomain.err
    

    2、二进制日志

    二进制日志概述

    二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制,就是通过该 binlog 日志实现的。 二进制日志在默认情况下是没有开启的,需要到 MySQL 的配置文件中开启,并配置 MySQL 日志的格式。配置文件位置:/usr/my.cnf 是日志存放位置。配置时,给定了文件名但是没有指定路径,日志默认写入 MySQL 的数据目录。

    二进制日志格式

    # 日志格式
    
    # STATEMENT
    该日志格式在日志文件中记录的都是 SQL 语句(statement),每一条对数据进行修改的 SQL 都会记录在日志文件中。
    通过 MySQL 提供的 mysqlbinlog 工具,可以清晰的查看到每条语句的文本。
    主从复制的时候,从库(slave)会将日志解析为原文本,并在从库重新执行一次。
    
    # ROW
    该日志格式在日志文件中记录的是每一行的数据变更,而不是记录 SQL 语句。
    比如,执行 SQL 语句:update emp set age=1 , 如果是 STATEMENT 日志格式,在日志中会记录一行 SQL 文件;
    如果是 ROW ,由于是对全表进行更新,也就是每一行记录都会发生变更,ROW 格式的日志中会记录每一行的数据变更。
    
    # MIXED
    这是目前 MySQL 默认的日志格式,即混合了 STATEMENT 和 ROW 两种格式。
    默认情况下采用 STATEMENT,但是在一些特殊情况下采用 ROW 来进行记录。
    MIXED 格式能尽量利用两种模式的优点,而避开他们的缺点。
    

    二进制日志之 STATEMENT 格式的使用

    # 配置开启 binlog 日志,日志的文件前缀【mysqlbin】。生成的文件名如:mysqlbin.000001、mysqlbin.000002
    # mysqlbin.index 该文件是日志索引文件,记录日志的文件名;
    # mysqlbing.000001 才是日志文件
    
    # 1、配置 binlog 步骤
    vim /usr/my.cnf
    
    # 2、加入如下两行配置
    log_bin=mysqlbin           # 开启 binlog 日志
    binlog_format=STATEMENT    # 配置二进制日志的格式为 STATEMENT
    :wq                        # 保存退出
    service mysql restart      # 重启 MySQL 服务器
    
    # 3、登录 MySQL 执行插入操作
    mysql -uroot -proot
    use mysql_advanced;
    insert into emp values (null,'张无忌',31,2823);
    exit;
    
    # 4、查看日志文件信息
    cd /var/lib/mysql            # 进入到日志保存目录
    ll -lh                       # 会发现生成了两个文件:mysqlbin.000001、mysqlbin.index
    mysqlbinlog mysqlbin.000001  # 查看日志文件,发现有一条我们的插入数据的记录
    

    二进制日志之 ROW 格式的使用

    # 配置开启 binlog 日志,日志的文件前缀【mysqlbin】。生成的文件名如:mysqlbin.000001、mysqlbin.000002
    
    # 1、配置 binlog 步骤
    vim /usr/my.cnf
    
    # 2、加入如下两行配置
    log_bin=mysqlbin           # 开启 binlog 日志
    binlog_format=ROW          # 配置二进制日志的格式为 ROW
    :wq                        # 保存退出
    service mysql restart      # 重启 MySQL 服务器
    
    # 3、登录 MySQL 执行插入操作
    mysql -uroot -proot
    use mysql_advanced;
    insert into emp values (null,'赵敏'281,6823);
    exit;
    
    # 4、查看日志文件信息
    cd /var/lib/mysql            # 进入到日志保存目录
    ll -lh                       # 会发现生成了 mysqlbin.000002 文件
    mysqlbinlog mysqlbin.000002  # 查看日志文件,发现并没有什么信息
    
    # 注意:如果日志格式是 ROW ,直接查看数据,是查看不懂的。可以在 mysqlbinlog 后面加上参数 -vv
    mysqlbinlog -vv mysqlbin.000002
    

    二进制日志的删除操作

    对于比较繁忙的系统,由于每天生成日志量大,这些日志如果长时间不清楚,将会占用大量的磁盘空间。 下面将介绍几种删除日志的常见方法,具体使用如下:

    -- 方式一:通过 Reset Master 指令删除全部 binlog 日志。
    -- 删除之后,日志编号,将从 xxxx.000001 重新开始。
    -- 执行删除日志指令
    mysql -uroot -proot;
    Reset Master;
    
    -- 方式二:行指令 purge master logs to 'mysqlbin.******',该命令将删除 ****** 编号之前的所有日志
    mysql -uroot -proot;
    purge master logs to 'mysqlbin.000005';
    
    -- 方式三:执行指令 purge master logs before 'yyyy-mm-dd hh:mm:ss',
    -- 该命令将删除日志为 "yyyy-mm-dd hh:mm:ss" 之前产生的所有日志
    mysql -uroot -proot;
    purge master logs beore '2021-04-07 11:47:00';
    
    -- 方式四:设置参数 --expire_logs_days=# ,此参数的含义是设置日志的过期天数,
    -- 过了指定的天数后日志将会被自动删除,这样将有利于减少 DBA 管理日志的工作量
    -- 具体配置如下
    vim /usr/my.cnf
    log_bin=mysqlbin           # 开启 binlog 日志
    binlog_format=ROW          # 配置二进制日志的格式为 ROW
    --expore_logs_days=3       # 配置日志过期天数
    :wq                        # 保存退出
    service mysql restart      # 重启 MySQL 服务器即可生效
    

    3、查询日志

    查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的 SQL 语句。默认情况下,查询日志是未开启的。 如果需要开启查询日志,可以设置以下配置:

    # 配置查询日志
    
    vim /usr/my.cnf                    # 编辑配置文件
    general_log=1                      # 开启查询日志,可选值:0 或者 1。0 代表关闭,1 代表开启
    general_log_file=mysql_query.log   # 设置日志的文件名,如果没有指定,默认的文件名为 host_name.log
    :wq
    service mysql restart
    
    # 配置完毕之后,在数据库执行以下操作
    mysql -uroot -proot
    use mysql_advanced;
    select * from emp;
    select * from emp where id=1;
    update emp set name="猴子" where id=2;
    select * from emp where id<3;
    
    # 执行完毕之后,再次来查询日志文件
    cd /var/lib/mysql           # 这时能够看到 mysql_query.log 文件
    cat mysql_query.log         # 查看日志信息
    

    4、慢查询日志

    慢查询日志 记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的 SQL 语句的日志。long_query_time 默认为 10 秒,最小为 0,精度可以到微秒。 慢查询日志默认是关闭的,我们需要手动开启。可以通过两个参数来控制慢查询日志:

    # 编辑配置文件
    vim /usr/my.cnf
    
    # 该参数用来控制慢查询日志是否开启,可取值:1 和 0。1 代表开启,0 代表关闭
    slow_query_log=1
    
    # 该参数用来指定慢查询日志的文件名
    slow_query_log_file=slow_query.log
    
    # 该选项用来配置查询的时间限制,超过这个时间将认为值慢查询,将需要进行日志记录,默认10s
    long_query_time=10
    
    # 保存退出
    :wq
    
    # 重启 MySQL 服务器
    service mysql restart
    
    -- 和错误日志、查询日志一样,慢查询日志记录的格式也是纯文本,可以被直接读取
    -- 登录 MySQL 服务器
    mysql -uroot -proot
    
    --查询 long_query_time 的值
    show variables like 'long%';
    
    -- 执行查询操作
    select * from tb_user_1 where id=1;  -- 由于该语句执行时间很短,为 0s ,所以不会记录在慢查询日志中
    
    -- 该 SQL 语句,执行时长为 26.77s ,超过 3s ,所以会记录在慢查询日志文件中
    select * from tb_user_1 where title like '%阿尔卡特(OT-927)炭黑联通3G手机双卡双待%';
    
    -- 查看慢查询日志文件
    cd /var/lib/mysql
    cat slow_query.log
    
    -- 如果慢查询日志内容很多,直接查看文件,比较麻烦
    -- 这个时候可以借助于 MySQL 自带的 mysqldumpslow 工具,来对慢查询日志进行分类汇总
    mysqldumpslow slow_query.log
    

    四、主从复制

    1、主从复制概述

    主从复制 是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。 MySQL 支持一台主库同时向多台从库进行复制,从库同时也可以作为其他从服务器的主库,实现链状复制。

    2、主从复制原理

    主从复制原理图如下:

    # 从上层来看,复制分成三步
    
    1、Master 主库在事务提交时,会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中
    2、主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log
    3、slave 重做中继日志中的事件,将改变反映它自己的数据
    

    3、主从复制优势

    # MySQL 主从复制的优点主要包含以下三个方面:
    
    1、主库出现问题,可以快速切换到从库提供服务
    2、可以在从库上执行查询操作,从主库中更新,实现读写分离,降低主库的访问压力
    3、可以在从库中执行备份,以避免备份期间影响主库的服务
    

    4、集群搭建

    主服务器配置

    # 1、关闭主从服务器的防火墙
    service iptables stop
    
    # 2、在 master 的配置文件(/usr/my.cnf)中,配置如下内容
    vim /usr/my.cnf
    server-id=1          # mysql 服务 ID,保证整个集群环境中唯一
    log-bin=/var/lib/mysql/mysqlbin   # mysqlbinlog 日志的存储路径和文件名
    #log-err             # 错误日志,默认已经开启
    #basedir             # mysql 的安装目录
    #tmpdir              # mysql 的临时目录
    #datadir             # mysql 的数据存放目录
    read-only=0          # 是否只读,1 代表只读,0 代表读写
    binlog-ignore-db=mysql   # 忽略的数据,指不需要同步的数据库
    #binlog-do-db=db01       # 指定同步的数据库
    
    :wq                      # 保存退出
    service mysql restart    # 重启 MySQL 服务器
    
    # 3、创建同步数据的账户,并且进行授权操作
    mysql -uroot -proot
    grant replication slave on *.* to'root'@'192.168.157.132' identified by 'root';
    flush privileges;
    
    # 4、查看 master 状态
    show master status;
    
    # 5、结果字段含义
    File                 从哪个日志文件开始推送日志文件
    Position             从哪个位置开始推送日志
    Binlog_Ignore_DB     指定不需要同步的数据库
    

    从服务器配置

    # 1、关闭主从服务器的防火墙
    service iptables stop
    
    # 2、在 slave 服务器端配置文件中(/usr/my.cnf)中,配置如下内容
    vim /usr/my.cnf
    server-id=2        # mysql 服务端 ID,唯一
    # 指定 binlog 日志
    log-bin=/var/lib/mysql/mysqlbin
    :wq                      # 保存退出
    service mysql restart    # 重启 MySQL 服务器
    
    # 3、然后执行如下指令
    mysql -uroot -proot
    
    # 指定当前从库对应的主库的 IP 地址,用户名,密码,从哪个日志文件开始的那个位置开始同步推送日志
    change master to master_host='192.168.157.136', master_user='root', 
    master_password='root', master_log_file='mysqlbin.000001', master_log_pos=413;
    
    # 4、开启同步操作
    start slave;
    show slave status;     # 只有 Slave_IO_Running 和 Slave_SQL_Running 为 YES 即算成功
    
    # 5、停止同步操作
    stop slave;
    
    -- 验证同步操作
    -- 1、在主库中创建数据库,创建表,并插入数据
    create database db01;
    use 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');
    
    -- 2、在从库中查询数据,进行验证
    show databases;         # 在从库中,可以查看到刚才创建的数据库
    use db01;
    select * from user;     # 在该数据库中,查询 user 表中的数据
    
    MySQL
    • 文章作者:GuoShiZhan
    • 创建时间:2021-08-02 20:36:00
    • 更新时间:2021-08-02 20:36:00
    • 版权声明:本文为博主原创文章,未经博主允许不得转载!
    请 在 评 论 区 留 言 哦 ~~~
    1024