mysql

一、逻辑架构

1.1 系统架构图

  • Connectors：指的是不同语言中与SQL的交互

  • Connection Pool：管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等

  • Management Serveices & Utilities：系统管理和控制工具

  • SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果

  • Parser：SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。

  • 主要功能：

    • 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的；
    • 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

  • Optimizer
    　　查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。
    　　使用的是“选取-投影-联接”策略进行查询：
    用一个例子就可以理解： select uid,name from user where gender = 1;
    这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。

  • Cache和Buffer：
    　　查询缓存：主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
    　　如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

  • 存储引擎接口
    　　MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
    注意：存储引擎是基于表的，而不是数据库。

1.2 逻辑架构图

  • 首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。
  • 第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
  • 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB例外，它会会解析外键定义，因为MySQL本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

1.2.1 连接管理与安全性

  • 每个客户端连接都会在服务器进程中拥有一个进程，这个连接的查询只会在这个单独的线程中执行，该线程只会轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程(MySQL5.5或者更新的版本提供了一个API，支持线程池插件，可以使用池中少量的线程服务大量的连接)。
  • 当客户端连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字（SSL）的方式连接，还可以使用X.509证书认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

1.2.2 优化与执行

  • MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行优化，包括重写查询、决定表的读写顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示优化器，影响他的决策过程。也可以请求优化器解释（explain）优化过程的各个因素。使用者可以知道服务器是如何进行优化决策的，并提供一个参考标准便于用户重构查询和schema】修改相关配置，使应用尽可能高效运行。
  • 优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。
  • 对于SELECT语句，在解析之前，服务器会先检查查询缓存，如果能在其中找到对应的查询，服务器就不必执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.3 MySQL工作流程

二、SQL

2.1、常用命令

查看数据库版本
select version();
show variables like ‘%version%’;

2.2、常见查询

  • 查询不重复的记录

    查询名字不相同的学生;
select distinct name from students;
查询名字和年龄同时不同的学生
select distinct name,age from students;
1.distinct必须放在最开头
2.distinct只能使用需要去重的字段进行操作。
3.distinct去重多个字段时，含义是：几个字段同时重复时才会被过滤。

  • 条件查询

    查询性别是男，并且年龄大于20岁的人
select * from student where sex='男' and age>20; 
1.where后面的条件可以用>、<、>=、<=、!=等多种比较运算符，多个条件之间可以用or、and等逻辑运算符

  • 排序

    查询学生表并按年龄降序排列
select *from student order by age desc;
1.desc 降序排列，asc 升序排列
2.order by 后面可以跟多个不同的排序字段，每个排序字段都可以有不同的排序顺序。
3.如果排序字段的值一样，则相同的字段按照第二个排序字段进行排序。
4.如果只有一个排序字段，则字段相同的记录将会无序排列。

  • 限制

    取出成绩前五名的学生(省略了起始偏移量，此时默认为0)
select * from student order by mark desc limit 0,5;
1.默认情况下，起始偏移量为0，只写记录行数就可以。
2.从第一条数据开始，查询五条数据。

  • 聚合

    1.聚合函数，常用的有 : sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)。
2.group by 关键字，表示要进行分类聚合的字段。比如要按照部门分类就应该写在group by dept。
3.with rollup 是可选语法，表明是否对分类聚合后的结果进行再汇总
4.having 关键字表示对分类后的结果再进行条件过滤。

2.3、多表查询

  • 笛卡尔积

    select id,name,from student ,teacher;
//会产生笛卡尔积，若student有5条数据，teacher有3条数据，那么结果会有5*3条数据

//解决办法

1.等值连接
select id,name,from student ,teacher where student.id=teacher.id
2.内连接
select id,name,from student join teacher 
on student.id=teacher.id

  • 内连接

    1.join（inner join）

select * from a
join b 
on a.id=b.id;
//得到的是两表内部共有数据，交集

  • 外连接

    1.left join(left outer join)

select a.*,b.* from a 
left join b
on a.id = b.id
//得到a的所有数据，和满足某一条件的b的数据，b中不存在的记为null

================================================
2.left join(left outer join)+where b.column is not null

select a.*,b.* from a 
left join b
on a.id=b.id
where b.id is not null
//去除b中为null的那行数据

================================================
3.right join(right outer join) 与 left join 类似

三、数据类型

3.1、整数类型

整数类型 存储空间 （bit/位） 存储范围
TINYINT 8 -128-127
SMALLINT 16 -32768-32767
MEDIUMINT 24
INT 32
BIGINT 64
TINYINT UNSIGNED 8 0-255
  • 存储范围：-（2的n-1次方）到（2的n-1次方）-1
  • 1字节（byte）=8位（bit）
  • 有符号与无符号类型使用相同的存储空间，并具有相同的性能。

3.2、字符串类型

3.2.1、VARCHAR

  • VARCHAR用于存储可变长字符串，它比定长字符串更节省空间，因为它仅使用需要的空间。但是有一种情况除外，如果MySQL使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储，浪费空间。
  • VARCHAR需要使用1个或2个额外字节记录字符串的长度，如果列的最大长度小于等于255个字节，则需要一个字节表示，否则需要2个字节。但是由于行是变长的，在update时可能会使行变得比原来更长，占用的空间变大，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎处理的方式不一样。MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。
  • 总结一下
    1. mysql记录行的长度是65535字节
    2. utf8编码占3字节，gbk编码占2字节，latin1编码占1字节
    3. 对于变长字段如varchar，mysql会用额外的字节来存储字符长度，255个字符以内用1个字节存，多于255个字符用2字节存
    4. decimal类型的字段长度不固定，整数与小数部分所占字节数总和为总字节数

3.2.2、CHAR

  • CHAR类型是定长的：MySQL总是根据定长的字符串长度分配足够的空间。CHAR非常适合存储密码的md5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型更不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。比如，用CHAR(1)来存储只有Y和N的值。

  • 举个例子：

    create table char_test(char_col CHAR)

create table vachar_test(varchar_col varchar(12))

insert into char_test values('string'),('  string'),('string  ');

insert into vachar_test values('string'),('  string'),('string  ');

select concat("'",char_col,"'") from char_test;

3.2.3、BLOB、TEXT

  • BLOB和TEXT都是为了存储更大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
  • 与其它类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象处理。存储引擎在存储是会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值。
  • BLOB和TEXT之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则和字符集，而TEXT有字符集和排序规则
  • 排序：MySQL对BLOB和TEXT的排序规则不同于其他类型，它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用order by substring(column,length)
  • MySQL不能够BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引来消除排序。

3.3、日期和时间类型

  • MySQL能存储的最小时间粒度为秒（MariaDB支持微妙级别的时间类型），但是也可以使用微秒级粒度进行临时运算。

3.3.1、DATETIME

  • 这个类型能保存很大范围的值，从1001年到9999年，精度为秒。它将日期和时间封装到格式为YYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储空间。
  • 默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME，例如1998-04-03 07:00:00。

3.3.2、TIMESTAMP

  • TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多，只能表示1970年到2038年。
  • TIMESTAMP显示的值依赖时区。
  • 如果插入时没有指定一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间。在插入一行记录时，MySQL默认也会更新TIMESTAMP列的值（除非在update语句中明确指定了值）。
  • TIMESTAMP列默认为not null

四、常用函数

4.1、数学函数

4.2、字符串函数

//计算字符串字符个数
char_length(str)

//返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
concat(s1,s2,...)

//返回多个字符串拼接之后的字符串，每个字符串之间有一个x
concat_ws(x,s1,s2,...)

//前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写
LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

//返回字符串s删除了两边空格之后的字符串
trim(str)

//两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串
substring(s,n,length),mid(s,n,length)

//将字符串s反转
reverse(s)

4.3、日期和时间函数

4.4、条件判断函数

//如果expr是TRUE则返回v1，否则返回v2
IF(expr,v1,v2)

//如果v1不为NULL，则返回v1，否则返回v2
IFNULL(v1,v2)

//如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

4.5、系统信息函数

4.6、加密函数

4.7、其它函数

五、索引

5.1、树

5.1.1、一颗普通的树

  • 树的概念
    • 结点的度：一个结点含有的子结点个数称为该结点的度；
    • 树的度：一棵树中，最大结点的度称为树的度；
    • 父结点：若一个结点含有子结点，则这个结点称为其子结点的父结点；
    • 深度：对于任意结点n,n的深度为从根到n的唯一路径长，根结点的深度为0；
    • 高度：对于任意结点n,n的高度为从n到一片树叶的最长路径长，所有树叶的高度为0；

5.1.2、树的种类

  • 按照有序性

    • 无序树：树中任意节点的子结点之间没有顺序关系
    • 有序树：树中任意节点的子结点之间有顺序关系

  • 按照包含子树个数二叉树

二叉树
  • 二叉树：每个节点最多含有两个子树
  • 二叉查找树：首先它是一颗二叉树，若左子树不空，则左子树上所有结点的值均小于它的根结点的值；若右子树不空，则右子树上所有结点的值均大于它的根结点的值；左、右子树也分别为二叉排序树
  • 满二叉树：叶节点除外的所有节点均含有两个子树的树被称为满二叉树
  • 完全二叉树：如果一颗二叉树除去最后一层节点为满二叉树，且最后一层的结点依次从左到右分布
  • 霍夫曼树：带权路径最短的二叉树
  • 红黑树：红黑树是一颗特殊的二叉查找树，每个节点都是黑色或者红色，根节点、叶子节点是黑色。如果一个节点是红色的，则它的子节点必须是黑色的
  • 平衡二叉树（AVL）：一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树
B树

  • B-树：也成为B树，是一种平衡的的多叉树，它比较适用于对外查找

    • 阶数：一个节点最多有多少个孩子节点。（一般用字母m表示）

    • 关键字：节点上的数值就是关键字

    • 度：一个节点拥有的子节点的数量

    • 根节点至少有两个子女

    • 每个非根节点所包含的关键字个数 j 满足：m/2 - 1 <= j <= m - 1.(表示向上取整)

    • 有k个关键字(关键字按递增次序排列)的非叶结点恰好有k+1个孩子

    • 所有的叶子结点都位于同一层

  • B+树

    • B+树是B-树的变体，也是一颗多路搜索树。一棵m阶的B+树主要有这些特点：

    • 每个结点至多有m个子女

    • 非根节点关键字个数范围：m/2 <= k <= m-1

    • 相邻叶子节点是通过指针连起来的，并且是关键字大小排序的

  • B-树与B+树的区别

    • B-树内部节点是保存数据的;而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据
    • B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的
    • 查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束
    • B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次

https://baijiahao.baidu.com/s?id=1692469218111984631&wfr=spider&for=pc

聚集索引、非聚集索引
  • 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引
  • 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

5.2、索引的概念

  • 索引（Index）是帮助MySQL高效获取数据的数据结构。

    我们知道，数据库查询是数据库的最主要功能之一。但每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

  • 索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。

  • 在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的(例如树结构的选型和查找算法的实现)。

5.3、索引的类型

  • BTree索引

    • B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页值的上限和下限。最终存储引擎要么找到对应的值，要不该记录不存在。
    • 叶子节点他们的指针指向的是被索引的数据，而不是其他节点页。
    • 全值匹配
    • 匹配最左前缀
    • 匹配列前缀
    • 匹配范围值
    • 精准匹配某一列并范围匹配另外一列
    • 只访问索引的查询

  • hash索引

    • 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引对所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
    • 在MySQL中，只有Memory引擎显式支持哈希索引，也是默认索引，同时Memory引擎也支持B-Tree索引。
    • 哈希索引质保函哈希值和指针，而不存储字段值，索引不能使用索引中的值来避免读取行。
    • 哈希索引数据并不是按照索引值顺序排序，所以无法用于排序
    • 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的，例如在数据列（A，B）建立哈希索引，如果查询只有数据列A，则无法使用该索引
    • 哈希索引只支持等值比较查询，包括=、IN()、<=>，不支持任何范围查询
    • 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有不同的哈希值）。当存在哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行比较，直到找到符合条件的行

  • InnoDB有一个特殊的功能叫做”自适应哈希索引“，当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点。

5.3.1、普通索引

  • 创建普通索引，不需要添加 [UNIQUE | FULLTEXT | SPATIAL ] 等任何参数进行约束
  • 普通索引 (由关键字KEY或INDEX定义的索引) 的唯一任务是加快对数据的访问速度。
  • 只为那些最经常出现在‘查询条件’(WHERE column = ...) 或‘排序条件’(ORDER BY column)中的数据列，来创建索引。
  • 只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个int整数类型的数据列)来创建索引。
//查看索引
show index from tableName
//创建表时创建索引
create table test(
id int primary key not null,
name varchar(12),
index `index_id` (id)
)
//直接创建索引
create index index_name on test (name)

//删除索引
drop index index_name on test

5.3.2、唯一索引

  • 创建唯一索引时，使用 UNIQUE 参数对 INDEX | KEY 进行约束

  • 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

  • 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引：

    • 一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；
    • 二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；

  • 事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

  • 主索引：必须为主键字段创建一个索引，这个索引就是所谓的"主索引"。

  • 主索引 与 唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

    create table a(
id int primary key not null,
name varchar(12),
addr varchar(32),
unique key `info` (id)
)

5.3.3、全文索引

  • 全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

  • MySQL从3.23.23版开始支持全文索引和全文检索，fulltext索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

  • 对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

  • 文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

  • 这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用命令添加。

  • 有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：

    create table a(
id int primary key not null,
name varchar(12),
addr varchar(32),
fulltext key `info` (addr)
)

5.3.4、单列索引

  • 创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需要指定单列字段名，即可创建单列索引。

  • 多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

    create table a(
id int primary key not null,
name varchar(12),
addr varchar(32),
index `info` (addr(12))//由于addr是字符串类型，可以指定索引长度
)

5.3.5、多列索引

  • 创建多列索引，即在数据表的多个字段上创建索引。与上述单列索引类似，创建该类型索引不需要引入约束参数。

  • 多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

create table a(
id int primary key not null,
name varchar(12),
addr varchar(32),
index `info` (`name`,`addr`)
)

  • 最左匹配原则：

    触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用。

5.3.6、空间索引

  • 创建空间索引，需要添加 SPATIAL 参数进行约束。

    同样，必须说明的是，只有 MyISAM 类型的表支持该类型 ‘ 空间索引 ’。而且，索引字段必须有非空约束。

5.4、show status

https://blog.csdn.net/qq_29168493/article/details/79149132

5.5、EXPLAIN

https://www.cnblogs.com/tufujie/p/9413852.html

  • id：

    1. id相同时，执行顺序由上向下
    2. 如果是子查询，id的序号会递增；id的值越大优先级越高，越先被执行
    3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

  • select_type：

    • simple：简单查询
    • primary：最外层查询
    • subquery：映射为子查询
    • derived：子查询
    • union：联合
    • union result：使用联合的结果

  • table：

    • 显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

  • type：

    • 对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”
    • 常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
      • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
      • index：Full Index Scan，index与ALL区别为index类型只遍历索引树
      • range：只检索给定范围的行，使用一个索引来选择行
      • ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
      • eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
      • const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
      • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

  • possible key：

    • 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
    • 该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
    • 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

  • key

    • key列显示实际使用的键（索引），必然包含在possible_key中
    • 如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

  • key_len

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

  • ref

    • 列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  • rows

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

  • Extra

    • 该列包含MySQL解决查询的详细信息,有以下几种情况：
    • Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
    • Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
    • Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
    • Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
    • Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）
    • Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
    • No tables used：Query语句中使用from dual 或不含任何from子句

六、SQL安全问题

6.1、SQL注入：

https://www.cnblogs.com/myseries/p/10821372.html

七、优化

7.1、SQL优化

  • 避免使用子查询

  • 避免函数索引

  • 用IN替换OR

  • LIKE

    • LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引，如果查询的是索引列则可以

  • LIMIT

    select id,name 
from table_name limit 866613, 20

    使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

    优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

    select id,name 
from table_name where id> 866612 limit 20

  • 避免数据类型不一致

  • 尽量不使用select *

  • 禁止UNION ALL

    union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。

    另外，如果排序字段没有用到索引，就尽量少排序

  • IN与EXITS

    select * from 表A 
where id in (select id from 表B)

    相当于

    select * from 表A 
where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。

所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

  • GROUP BY

    • 如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会去排序）；
    • 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
    • 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
    • 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。

7.2、索引优化

7.2.1、Join语句优化

  • 当我们执行两个表的Join的时候，就会有一个比较的过程，逐条比较两个表的语句是比较慢的，因此可以把两个表中数据依次读进一个内存块中。

  • 在Mysql中执行：show variables like 'join_buffer_size'，可以看到join在内存中的缓存池大小，其大小将会影响join语句的性能。在执行join的时候，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer。

  • 假设有表如右边： t1与t2表完全一样，a字段有索引，b无索引，t1有100条数据，t2有1000条数据

    若被驱动表有索引，那么其执行算法为：Index Nested-Loop Join（NLJ），示例如下：

    1、执行语句：select * from t1 straight_join t2 on (t1.a=t2.a)；由于被驱动表t2.a是有索引的，其执行逻辑如下：

    从表t1中读入一行数据 R；

    从数据行R中，取出a字段到表t2里去查找；

    取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；

    重复执行步骤1到3，直到表t1的末尾循环结束。

     如果一条join语句的Extra字段什么都没写的话，就表示使用的是NLJ算法

  • 若被驱动表无索引，那么其执行算法为：Block Nested-Loop Join（BLJ）（Block 块，每次都会取一块数据到内存以减少I/O的开销），示例如下：

    2、执行语句：select * from t1 straight_join t2 on (t1.a=t2.b)；由于被驱动表t2.b是没有索引的，其执行逻辑如下：

    把驱动表t1的数据读入线程内存join_buffer（无序数组）中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；

    顺序遍历表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

  • 另外还有一种算法为Simple Nested-Loop Join（SLJ），其逻辑为：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。

性能优化，left join 是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建在右边。当然如果索引是在左边的，我们可以考虑使用右连接

7.2.2、避免索引失效

  • 最左匹配原则
  • 不在索引列上做任何操作：计算、函数、类型转换，会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列：如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。
  • 尽量使用覆盖索引
  • MySQL在使用不等于（!=或<>）的时候无法使用索引而导致全表扫描
  • is null, is not null 也无法使用索引，在实际中尽量不要使用null
  • like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。
  • 字符串不加单引号索引失效
  • 少用or，用它来连接时会索引失效
  • 尽量避免子查询，而用join
  • 在组合索引中，将有区分度的索引放在前面
  • 避免在 where 子句中对字段进行 null 值判断

7.3、表结构优化

数据库结构符合[三大范式]与BCNF

7.4、系统配置优化

7.5、硬件优化

八、主从复制

https://zhuanlan.zhihu.com/p/96212530

8.1、为什么需要主从复制

  1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
  2. 做数据的热备
  3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

8.2、什么是主从复制

  • MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。
  • MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

8.3、复制原理

  1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中
  2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
  3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

  1. 从库会生成两个线程,一个I/O线程,一个SQL线程
  2. I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中
  3. 主库会生成一个log dump线程,用来给从库I/O线程传binlog
  4. SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行
  5. 对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

注意事项：

  1. master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
  2. slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
  3. Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
  4. Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）
  5. master和slave两节点间时间需同步

8.4、具体步骤

  1. 从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave
  2. 从库的IO线程和主库的dump线程建立连接
  3. 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求
  4. 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程
  5. 从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中
  6. 从库SQL线程应用relay-log，并且把应用过的记录到realy_log.info中，默认情况下，已经应用过的relay 会自动被清理purge

8.5、主从形式

8.5.1、一主一从

8.5.2、主主复制

8.5.3、一主多从

8.5.4、多主一从

8.5.5、联级复制

8.6、同步延时

8.6.1、分析

  • mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。
  • slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

8.6.2、解决方案

  1. 业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
  2. 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
  3. 服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
  4. 不同业务的mysql物理上放在不同机器，分散压力。
  5. 使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。

使用更加强劲的硬件设备

九、分库分表

https://zhuanlan.zhihu.com/p/348659067

实践：https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html

9.1、前言

  • 在互联网项目中比较常用到的关系型数据库是MySQL，随着用户和业务的增长，传统的单库单表模式难以满足大量的业务数据存储以及查询，单库单表中大量的数据会使写入、查询效率非常之慢，此时应该采取分库分表策略来解决。

9.2、水平分库分表

9.2.1、RANGE

  • 第一种方法们可以指定一个数据范围来进行分表，例如从1~1000000，1000001-2000000，使用一百万一张表的方式

  • 当然这种方法需要维护表的ID，特别是分布式环境下，这种分布式ID,在不使用第三方分表工具的情况下，建议使用Redis，Redis的incr操作可以轻松的维护分布式的表ID。

  • 优点：扩容简单，提前建好库、表就好

  • 缺点：大部分读和写都会访问新的数据，有IO瓶颈，这样子造成新库压力过大，不建议采用。

9.2.1、HASH取模

HASH取模方法优点： 能保证数据较均匀的分散落在不同的库、表中，减轻了数据库压力

HASH取模方法缺点： 扩容麻烦、迁移数据时每次都需要重新计算hash值分配到不同的库和表

9.2.3、一致性HASH

  • 普通HASH算法

    • 普通哈希算法将任意长度的二进制值映射为较短的固定长度的二进制值，这个小的二进制值称为哈希值。哈希值是一段数据唯一且极其紧凑的数值表示形式。
    • 普通的hash算法在分布式应用中的不足：在分布式的存储系统中，要将数据存储到具体的节点上，如果我们采用普通的hash算法进行路由，将数据映射到具体的节点上，如key%n，key是数据的key，n是机器节点数，如果有一个机器加入或退出集群，则所有的数据映射都无效了，如果是持久化存储则要做数据迁移，如果是分布式缓存，则其他缓存就失效了。

  • 一致性HASH算法

    • 按照常用的hash算法来将对应的key哈希到一个具有2^32次方个节点的空间中，即0~ (2^32)-1的数字空间中。现在我们可以将这些数字头尾相连，想象成一个闭合的环形，

这个圆环首尾相连，那么假设现在有三个数据库服务器节点node1、node2、node3三个节点，每个节点负责自己这部分的用户数据存储，假设有用户user1、user2、user3，我们可以对服务器节点进行HASH运算，假设HASH计算后，user1落在node1上，user2落在node2上，user3落在user3上

假设node3节点失效

user3将会落到node1上，而之前的node1和node2数据不会改变，再假设新增了节点node4

  1. 你会发现user3会落到node4上，你会发现，通过对节点的添加和删除的分析，一致性哈希算法在保持了单调性的同时，还是数据的迁移达到了最小，这样的算法对分布式集群来说是非常合适的，避免了大量数据迁移，减小了服务器的的压力。
  2. 当然还有一个问题还需要解决，那就是平衡性。从图我们可以看出，当服务器节点比较少的时候，会出现一个问题，就是此时必然造成大量数据集中到一个节点上面，极少数数据集中到另外的节点上面。
  3. 为了解决这种数据倾斜问题，一致性哈希算法引入了虚拟节点机制，即对每一个服务节点计算多个哈希，每个计算结果位置都放置一个节点，称为虚拟节点。

一致性HASH方法优点： 通过虚拟节点方式能保证数据较均匀的分散落在不同的库、表中，并且新增、删除节点不影响其他节点的数据，高可用、容灾性强。

一致性取模方法缺点： 嗯，比起以上两种，可以认为没有。

十、备份与恢复

posted @ 2023-09-12 17:13  hasome  阅读(8)  评论(0)  编辑  收藏  举报