Mysql知识整理

在这里插入图片描述

MySql的逻辑架构

在这里插入图片描述
  Mysql的逻辑分层分为三层。
插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离
1、最上层为客户端层,并非MySQL所独有,诸如:连接处理、授权认证、安全等功能均在这一层处理。
2、MySQL大多数核心服务均在中间这一层,包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。
3、最下层为存储引擎,其负责MySQL中的数据存储和提取。和Linux下的文件系统类似,每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。不同引擎只会简单的响应上层服务器的请求,而不会相互通信。
在这里插入图片描述

1、连接层

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

2、服务层

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

3、存储引擎层

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

4、存储层

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

SQL语句的执行顺序

设有如下sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>

其具体执行的顺序应为:

1
2
3
4
5
6
7
8
9
10
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>

Mysql存储引擎

Mysql 5.6支持的存储引擎

在这里插入图片描述
我看可以看到comment中InnoDB是支持事务、行锁、以及外键的 且其实默认的存储引擎

InnoDB与MyISAM的区别

对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
行标锁 支持是表锁,即使操作一条纪录也会锁住整张表,不适合高并发场景 支持行锁,操作时只锁定某一行,不对其他行有影响,适合高并发的操作
缓存 只缓存索引、不缓存真实数据 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
表空间
关注点 性能 事务
默认安装
表的具体行数 保存表的具体行数,如执行select count(*) from table。会直接取到行数 没有保存具体行数,执行select count(*) from table会扫描全表。

索引

索引类型

  索引有很多种类型,可以为不同的场景提供更好的性能。在Mysql中,索引是在存储引擎层而不是服务器层实现的。所以并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同。

B-Tree索引

  B-Tree索引的底层是使用B+Tree这个数据结构来储存数据的(即每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历)。大多数的MySQL存储引擎都支持这种索引。
对如下类型的查询有效

  • 全值匹配。(是指和索引中所有的列进行匹配)
  • 匹配最左前缀。(即只使用索引的第一列)
  • 匹配列前缀。(只匹配某一列的值的开头部分。)
  • 匹配范围值。
  • 精确匹配某一列并范围匹配另外一列
  • 只访问索引的查询
      因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的order by操作(按顺序查找)。一般来说,如果B-Tree可以按照某种方式查找到值,那么也可以按照这种方式用于排序。所以,如果order by字句满足前面列出的几种查询类型,则这个索引也可以满足对应的排序需求。
    下面是一些关于B-Tree索引的限制:
    • 如果不是按按照索引的最左列开始查找,则无法使用索引。
    • 不能跳过索引中的列。
    • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

哈希索引

  哈希索引是基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存只想每个数据行的指针。

空间数据索引

  MyISAM表支持空间索引,可以用作地理数据存储。和B-Tree索引不同,这类索引无需前缀查询。空间索引会从所有维度来索引数据。查询时,可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善,所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

全文索引

  全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情,而不是简单的where条件匹配。
  在相同的列上同时创建全文索引和基于值得B-Tree索引不会有冲突,全文索引适用于Match against操作,而不是普通得where条件操作。

其他索引类别

  还有很多第三方的存储引擎使用不同类型得数据结构来存储索引。例如TokuDB使用分形树索引,这是一类较新开发的数据结构,既有B-tree得很多优点,也避免了B-Tree的一些缺点。

基本命令

索引分类

  • 主键索引:自动为主键创建的索引。
  • 普通索引:使用字段创建的索引,包括单列索引和联合索引
  • 唯一索引:字段的数据是唯一的且字段内容不能为null,允许存在多条唯一索引
  • 全文索引:MyISAM支持全文索引

创建索引

  • 单例索引
      方式一:create index 索引名 on 表名(字段名);
      方式二:alter table 表名 add index 索引名(字段名);
  • 唯一索引
      方式一:create unique index 索引名 on 表名(字段名);
      方式二:alter table 表名 add index unique index 索引名(字段名);
  • 组合索引
      方式一:create index 索引名 on 表名(字段名1, 字段名2, 字段名3 .... );
      方式二:alter table 表名 add index 索引名((字段名1, 字段名2, 字段名3 .... );

查看索引

show index from 表名

删除索引

drop index on 表名

索引的优点

  索引可以让服务器快速地定位到表的指定位置。但这并不是索引的唯一作用,最常见的如B-Tree索引,按照顺序存储数据,所以MySQL可以用来做 order bygroup by操作。因为数据是有序的,所以B-Tree也就会将相关的列值都存储在一起,如果待查询的列包含在索引中,则只需要走索引即可得到所查找的数据,无需回表进行查询。

  • 索引大大减少了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和产生临时表
  • 索引可以将随机IO变为顺序IO

索引选取的原则

独立的列

  如果不正确的使用索引,那么可能会造成索引失效。“独立的列”是指索引不能是表达式的一部分,也不能是函数的参数。
例如
select id from actor where id + 1 = 4;
凭肉眼很容易看出where中的表达式等价于id = 4,但是MySQL无法自动解析这个方程式。这完全是用户行为,我们应该养成简化where条件的习惯,始终将索引列单独放在比较符号的一侧。
下面是另一个常见的错误,会导致索引失效:
select ... where to_days(current_date) - to_days(date_col) <= 10;

前缀索引和索引选择性

  索引的选择性是指: 不重复的索引值和数据表的纪录总数的比值,范围从1/n到1。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

多列索引

选择合适的索引列顺序

-------------本文结束,感谢您的阅读!-------------