MySQL体系结构

●连接层

最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

●服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

●引擎层

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

●存储层

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

存储引擎简介

1.在创建表时，指定存储引擎

CREATE TABLE表名(
	字段1字段1类型[ COMMENT字段1注释]，
	……
	字段n字段n类型[COMMENT字段n注释]
)ENGINE = INNODB [ COMMENT 表注释];

2.查看当前数据库支持的存储引擎

SHOW ENGINES ;

存储引擎特点

●lnnoDB

>介绍

InnoDB是一种兼高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。

>特点

DML操作遵循ACID模型，支持事务;

行级锁，提高并发访问性能;

支持外键FOREIGN KEY约束，保证数据的完整性和正确性;

>文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。

参数: innodb_file_per_table

●MylSAM

>介绍

MyISAM是MySQL早期的默认存储引擎。

>特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

●Memory

>介绍

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

>特点

内存存放

hash索引（默认)

>文件

xxx.sdi：存储表结构信息

特点

lnnoDB

MyISAM

Memory

存储限制

64TB

有

有

事务安全

支持

-

-

锁机制

行锁

表锁

表锁

B+tree索引

支持

支持

支持

Hash索引

-

-

支持

全文索引

支持(5.6版本之后)

支持

-

空间使用

高

低

N/A

内存使用

高

低

中等

批量插入速度

低

高

高

支持外键

支持

-

-

索引

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:

索引结构

描述

B+Tree索引

最常见的索引类型，大部分引擎都支持B+树索引

Hash索引

底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询

R-tree(空间索引)

空间索引是MylSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text(全文索引)

是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Solr,ES

索引

lnnoDB

MylSAM

Memory

B+tree索引

支持

支持

支持

Hash索引

不支持

不支持

支持

R-tree索引

不支持

支持

不支持

Full-text

5.6版本之后支持

支持

不支持

●B-Tree(多路平衡查找树)

●B+Tree

相对于B-Tree区别:

① 所有的数据都会出现在叶子节点

② 叶子节点形成一个单向链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

●Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞〉，可以通过链表来解决。

>Hash索引特点

1、Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，<，..)

2、无法利用索引完成排序操作

3、查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

>存储引擎支持

在MySQL中，支持hash索引的是Memory引擎，而innoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

分类

含义

特点

关键字

主键索引

针对于表中主键创建的索引

默认自动创建,只能有一个

PRIMARY

唯一索引

避免同一个表中某数据列中的值重复

可以有多个

UNIQUE

常规索引

快速定位特定数据

可以有多个

全文索引

全文索引查找的是文本中的关键词，而不是比较索引中的值

可以有多个

FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:

分类

含义

聚集索引(Clustered Index)

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

二级索引(Secondary Index)

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

聚集索引选取规则:

1、如果存在主键，主键索引就是聚集索引。

2、如果不存在主键，将使用第一个唯一(UNIQUE）索引作为聚集索引。

3、如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

索引语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... );

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;