这是我参与8月更文挑战的第11天，活动详情查看：8月更文挑战

🌈往期回顾

    感谢阅读，希望能对你有所帮助，博文若有瑕疵请在评论区留言或在主页个人介绍中添加我私聊我,感谢每一位小伙伴不吝赐教。我是XiaoLin，既会写bug也会唱rap的男人

• 💛任职要求中的熟系Redis，你真的熟悉吗？💛
• ❤Git真的就是Pull和Push吗？❤
• ❤RESTful都不会，你怎么拿20k？❤

一、MySql 的用户和权限管理

1.1、MySQL的用户管理

1.1.1、相关命令

命令	描述	备注
create user xiaolin identified by '123456';	创建名称为xiaolin的用户，密码为123456
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user;	查看用户和权限的相关信息
set password = password('123456')	修改其他用户的密码	所有通过 user 表的修改，必须用 flush privileges;命令才能生效
update mysql.user set user = 'zs' where user = 'xiaolin'	修改用户名	所有通过 user 表的修改，必须用 flush privileges;命令才能生效
drop user xiaolin	删除用户	不要通过delete from user where user = 'xiaolin'进行删除，系统会有残留信息保存

1.1.2、详解

host

    host表示连接类型。

1. %：表示所有远程通过TCP方式连接。
2. 127.0.0.1：通过指定IP进行的TCP方式的连接。
3. 机器名：通过指定网络中的机器名进行的TCP方式的连接。
4. ::1：IPV6的本地IP地址，等同于IPV4的127.0.0.1
5. localhost 本地方式通过命令行方式的连接 ，比如 mysql -u xxx -p 123xxx 方式的连接。

user

    user表示用户名，同一用户通过不同方式连接的权限是不一样的。

password

    这里显示的是明文密码通过哦MYSQLSHA1加密算法加密后得到的密文密码，是不可逆的，mysql 5.7 的密码保存到 authentication_string 字段中不再使用 password 字段。

select_priv , insert_priv等

    表示该用户所拥有的权限。

1.2、MySQL的权限管理

1.2.1、授予权限

    我们可以通过命令来授予用户权限，该权限如果发现没有该用户，则会直接新建一个用户。

    格式为：

grant 权限 1,权限 2,…权限 n on 数据库名称.表名称 to 用户名@用户地址 identified by‘连接口令

    示范：

# 给 xiaolin 用户用本地命令行方式下，授予 user 这个库下的所有表的插删改查的权限。
grant select,insert,delete,drop on user.* to xiaolin@localhost;

# 授予通过网络方式登录的的 xiaolin 用户 ，对所有库所有表的全部权限，密码设为 123.
grant all privileges on *.* to xiaolin@'%' identified by '123';

1.2.2、查看权限

    我们可以使用命令来查看当前用户的权限。

show grants;

1.2.3、收回权限

    我们可以使用命令来收回用户的权限，权限收回后，必须用户重新登录后，才能生效。

revoke
[权限 1,权限 2,…权限 n] on 库名.表名 from 用户名@用户地址 ;

示范：

# 收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;

# 收回 mysql 库下的所有表的插删改查权限
REVOKE select,insert,update,delete ON mysql.* FROM xiaolin@localhost;

二、MySQL逻辑架构

2.1、整体架构图

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

2.2、MySQL分层

MySql大概分为四层：

2.2.1、连接层

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

2.2.2、服务层

    服务层提供各种用户使用的接口，同时提供SQL优化器，对用户传进来的SQL语句进行优化。他有几个组件。

组件名	描述
Management Serveices & Utilities	系统管理和控制工具
SQL Interface	SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from就是调用 SQL Interface
Parser	解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer	查询优化器。SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有where 条件时，优化器来决定先投影还是先过滤。
Cache 和 Buffer	查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等

2.2.3、引擎层

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

2.2.4、存储层

    数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2.3、MySQL的查询流程

    mysql 的查询流程大致是：

1. MySQL客户端通过协议与MySQL服务器建立连接，发送查询语句。
2. 服务器收到了查询语句之后，会先去检查查询缓存，这个缓存存储了SELECT语句以及相应的查询结果集。如果命中（查询结果已经位于缓存中）服务器就不会再对查询进行解析、优化、执行，他做的仅仅是将缓存中的结果直接返回给用户，大大提升了性能。如果没有命中缓存的话，将会进行第三步。
3. 接下来进入的是语法解析器和预处理，首先MySQL通过关键字将SQL进行解析，并生成一颗对应的解析树。MySQL解析器将使用MySQL语法规则验证解析查询，预处理器则根据一些MySQL规则进一步检查解析是否合法。
4. 接着到了查询优化器，解析树由优化器转化为执行计划。一条查询可以有很多种执行方式，但是最后都返回相同的结果，优化器的作用就是找到其中最好的执行计划。
5. 最后交有存储引擎进行执行返回给客户端。

2.4、MySQL的执行顺序

    我们手写一条完整的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_number >

    然而它的执行顺序是这样的.。

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

2.5、MyISAM 和 InnoDB

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整张表，不适合高并发操作	行锁，操作时只锁住某一行，不对其他行有影响，适合高并发操作
缓存	只缓存索引不缓存真实数据	不仅仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

三、索引

3.1、索引的概述

3.1.1、索引是什么

    MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。

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

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

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

3.2、索引的优缺点

3.2.1、优点

1. 提高数据检索的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

3.2.2、缺点

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

3.2、Btree 索引

3.2.1、初始化介绍

    MySQL 使用的是 Btree 索引。一颗B树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。

    磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。

    真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。

    非叶子节点只不存储真实的数据，只是为了作为分隔线，存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。

3.2.2、查找过程

    如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO。

    在内存中用二分查找确定 29在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计。通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO。

    29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

    3层的B树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的。

    如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

3.3、B+tree 索引

    我们可以看到BTree结构图中，每个节点不仅仅包含key值，同时还有data值，每一个页的存储空间是有限的，如果data数据较大时会导致每个节点（即每一个页）能存储的key数量很小，当存储的数据量很大的时候会导致BTree的深度很深，增大了查询时磁盘I/O的次数。

    而在B+Tree中，所有数据记录节点都是按照键值大小顺序放在同一层的叶子节点上，而非叶子节点只存储key信息，这样可以大大增加每个节点存储的key值的数量，降低B+Tree的高度。

    实际情况中每个节点可能不能填满，在数据库中，B+Tree的高度一般在24层之间，MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的（不动用磁盘I/O，直接上内存找），也就是说查找某一键值行记录时最多只需要13次磁盘I/O操作。

3.4、B+Tree 与 B-Tree 的区别

1. B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
2. 在 B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看好像B-树的性能1是要比B+树的性能要高，而在实际应用中确实B+树的性能更好一些。因为B+树的非叶子节点不存放实际的数据，这样每个11节点1可容纳的元素个数比B-树多，但是依次磁盘访问的时间相当于成千上百次内存比较的时间，因此在实际中B+树性能更好，而且B+树的叶子节点1是使用指针连接在一起，方便顺序遍历。

3.5、为什么B+树比 B树更适合实际应用

1. B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。
2. B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3.6、聚簇索引和非聚簇索引

1. 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，聚簇索引默认是主键。
2. 非聚簇索引：也称为辅助索引，将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。非聚簇索引存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。不存储物理位置的原因是当数据发生增删改的时候，物理位置可能会发生改变，万一发生了改变，那么还需要维护非聚簇索引。

• 聚簇索引查找：将主键组织到一个B+Tree树中，而行数据就存储在叶子节点上，若使用'where id = 14'这样的条件查找主键时，则按照B+Tree的检索算法即可查找到对应的叶子节点，从而获得行数据。
• 若对name(辅助索引)列进行条件搜索则需要两个步骤：
  1. 第一步先在辅助索引B+Tree中检索name，到达其叶子节点对应的主键。
  2. 使用主键在主索引B+Tree中再执行一次B+Tree检索操作，最终到达叶子节点即可获取整行数据。

3.6.1、聚簇索引的好处

1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问统一数据页不同记录时，已经把页加载到了buffer（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据时一起载入内存的，找到叶子节点就可以立刻将数据返回了，如果按照主键ID来组织数据的话，获得数据更快。
2. 辅助索引的叶子节点是存储主键的，而不是数据的存放地址。好处是当行数据发生改变时，索引树节点也是需要分裂变化的，另一个好处是因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

3.6.2、聚簇索引的限制

1. 对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。
2. 由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。
3. 为了充分利用聚簇索引的聚簇的特性，**所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如 uuid 这种。**如果id比较大的1话，可以选择雪花算法1得出的id。
4. 建议使用int自增的类型，方便排序且默认会在索引树末尾增加主键值，对索引树结构影响最小。聚簇索引的数据的物理存放顺序是一致的，因为索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的，如果主键不是自增id，那么它会不断地调整物理地址、分页，但如果是自增，那么他只需一页页地写索引结构相对紧凑，磁盘碎片较少，效率高。

3.7、索引的分类

3.7.1、单值索引

3.7.1.1、概述

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

3.7.1.2、语法

# 随着表的建立一起建立
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name) );

# 单独建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name);

3.7.2、唯一索引

3.7.2.1、概述

    唯一索引指的是索引列必须唯一，但是允许有空值，且只能有一个。

3.7.2.2、语法

# 随着表的建立一起建立
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_no) );

# 单独建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

3.7.3、主键索引

3.7.3.1、概述

    设定为主键后数据库会自动建立索引，innodb为聚簇索引，主键索引列值不能为空。

3.7.3.2、语法

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id) );

# 单独建主键索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);

# 删除建主键索引
ALTER TABLE customer drop PRIMARY KEY ;

# 如果需要修改建主键索引，必须先删除掉(drop)原索引，再新建(add)索引

3.7.4、复合索引

    他会符合两个原则：

1. 最左匹配原则。
2. MySQL引擎在查询时为了更好的利用索引，在查询过程中会动态调整查询字段的顺序，以便于更好地利用索引。

3.7.4.1、概述

    复合索引是一个索引包含多个列。

3.7.4.2、语法

# 随表一起建索引
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200), PRIMARY KEY(id), KEY (customer_name), UNIQUE (customer_name), KEY (customer_no,customer_name) );

# 单独建索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

3.8、索引的创建时机

3.8.1、适合创建索引的时机

• 主键自动建立唯一索引。
• 频繁作为查询条件的字段应该创建索引。
• 查询中与其他表关联的字段，外键关系建立索引。
• 单键/组合索引面临选择的适合，一般选择组合索引性价比更高。
• 查询中排序的字段，排序字段若通过1索引去访问将大大提高排序的1速度。
• 查询中统计或者分组字段。

3.8.2、不适合创建索引的情况

• 表记录太少。
• 经常增删改的表或者字段。
• where条件里用不到的字段不创建索引。

3.9、索引的基本操作

3.9.1、主键索引

    主键索引是在建表时自动创建的。

建表，主键自动创建主键索引

create table t_user(id varchar(20) primary key,name varchar(20));

查看索引

show index from t_user;

3.9.2、单列索引

创表的时候一起创建索引

# 在建表的时候字段后面用key(列名)来创建索引，但是我们没办法指定索引名,默认索引名和列名一致。
create table t_user(id varchar(20) primary key , name varchar(20 , key(name)))

创建完表以后加索引

# 语法格式
create index 索引名 on 表名(列名);

# 示范
create index index_name on t_user(name);

删除索引

# 语法格式
drop index 索引名 on 表名;

# 示范
drop index index_name on t_user;

3.9.3、唯一索引

建表的时候创建索引

# 在建表的时候字段后面用unique(列名)来创建索引，但是我们没办法指定索引名,默认索引名和列名一致。
create table t_user(id varchar(20) primary key , name varchar(20) , unique(name) );

创建表之后创建索引

# 格式
create unique index 索引名 on 表名(列名);

# 示范
create unique index index_name on t_user(name);

3.9.4、复合索引

建表的时候创建索引

# 在建表的时候字段后面用key(列名1,列名2...)来创建索引，但是我们没办法指定索引名,默认索引名和列名一致。
create table t_user (id varchar(20) primary key , name varchar(20) , age int , key(name , age) );

建表之后创建

# 语法格式
create index 索引名 on 表名(列名1,列名2...);

# 示范
create index index_name_age on t_user (name , age);