MySQL(2)

MYSQL架构:

MySQL完整架构层:

连接层

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端 /服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权 认证、及相关的安全方案。

服务层

可以接收SQL,负责调用函数,存储过程,触发器,对SQL执行顺序排序和优化,
如果是查询操作,还可以从内部缓存中先查询数据,提高性能.

引擎层

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

物理文件存储层

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

MySQL引擎:

MySQL 中的数据用各种不同的技术存储在文件中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平、并且最终提供广泛的不同的功能和能力.这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作 表类型)。MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL 服务器中启用.根据不同的需求,选择不同的引擎,使得效率最佳.

查看支持的引擎:

SHOW ENGINES;

查看表引擎:

SHOW TABLE STATUS LIKE ‘表名’

修改引擎

方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.

方式 2:建表时指定 CREATE TABLE 表名(…)ENGINE=MYISAM;

方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;

存储引擎主要有：

1.MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam.

我们主要分析使用 MyIsam 和 InnoDB

引擎功能对比:

MySQL常用两个的存储引擎:

InnoDB:

默认的存储引擎;

InnoDB 是一个事务型的存储引擎，有行级锁和外键约束,支持全文检索(全文索 引)，它的设计目标是处理大容量数据库系统,MySQL 运行时 Innodb 会在内存 中建立缓冲池，用于缓冲数据和索引；支持主键自增.不存储表的总行数.

支持事务,支持行锁,支持外键,支持缓存,不存储表的总行数(增删改快).

MyIsam :

MyISAM 也是 MySQL 的引擎，但是它没有提供对数据库事务的支持，也 不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作 需要锁定整个表，效率便会低一些;支持全文检索;存储表的总行数.

不支持事务,不支持行锁,只支持表锁,不支持外键,不支持缓存,存储表的总行数.(查询快)

索引

一.什么是索引?

索引是帮助 MySQL 高效获取数据的数据结构 : B+树.排好序的快速查找的数据结构.

主键是默认添加索引的. 会将主键维护在一个B+树中,保存这条记录的物理地址.

二.索引原理:

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等.

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果， 同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据,借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。

三.索引优势:

提高数据检索的效率(有B+树排序,把索引数据加载到内存中),降低数据库的 IO 成本;

通过索引列对数据进行排序( 索引使用B+树结构,是有序的),降低数据排序的成本,降低了 CPU 的消耗;

四.索引劣势:

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录, 所以索引列也是要占用磁盘空间的。索引也是需要空间来存储维护的.

执行新增,修改,删除操作时,需要对索引结构进行更新.效率也会受到影响.

五.索引创建原则:

哪些情况需要创建索引:

主键自动建立唯一索引 ;

频繁作为查询条件的字段应该创建索引(where 后面的语句);

查询中与其它表关联的字段，外键关系建立索引 ;

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 ;

分组中的字段.

哪些情况不要创建索引 :

表记录太少(例如系统参数设置表) ;

经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE,因为更新表时，MySQL 不仅要保存数据， 还要保存一下索引文件 ;

不作为查询的列(Where 条件里用不到的字段不创建索引) ;

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果.

六.索引分类:

主键索引:
设定为主键后数据库会自动建立索引.
增加主键索引:
ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
删除主键索引：
ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引(单列索引):
即一个索引只包含单个列，一个表可以有多个单列索引.
创建单值索引 CREATE INDEX 索引名 ON 表名(列名);
删除单值索引： DROP INDEX 索引名 ON 表名;

唯一索引:
索引列的值必须唯一，允许为 null
创建唯一索引:CREATE UNIQUE INDEX 索引名 ON 表名(列名);
删除唯一索引 DROP INDEX 索引名 ON 表名;

组合索引(复合索引):
即一个索引包含多个列,在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引.
创建复合索引: CREATE INDEX 索引名 ON 表名(列 1,列 2…);
删除索引: DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则:

最左侧索引原则 : 在使用组合索引时,最左侧的列必须被使用到,否则索引失效

例如表中有a,b,c3列, a,b列创建了组合索引, 在查询时,必须要满足最左侧索引原则,否则索引失效.

列如:
select * from table where a=’ ’ and b=’ ’ 索引生效
select * from table where b=’ ’ and a=’ ’ 索引生效
select * from table where a=’ ’ and c=’ ’ 索引生效
select * from table where b=’ ’ and c=’ ’ 索引不生效

全文索引:(MySQL8之后innodb引擎开始支持全文索引)
需要模糊查询时，一般索引无效，这时候就可以使用全文索引了。
使用全文索引可以代替 like 实现模糊查询, 索引不会失效

创建全文索引:CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

使用全文索引的模糊查询语法:
SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词’)

查看索引：
SHOW INDEX FROM 表名;

七.索引数据结构:

B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树 （B-Tree），B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构， InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

B+树特点:

1.排好序的,一个节点可以存储多个数据.

2.非叶子节点不存储数据,只存储索引,这样一个节点可以存放更多的索引.

3.数据记录都存放在叶子节点中.

4.所有叶子节点之间都有一个链指针(双向链表).

Mysql 索引使用的是 B+树，因为索引是用来加快查询的，而 B+树通过对 数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元 素，从而可以使得 B+树的高度不会太高.并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。

八.聚簇索引和非聚簇索引:

聚簇索引:
找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就 是聚簇索引。

例如innoDB引擎,索引和数据在同一个文件中,找到了索引就找到了数据

聚簇:使用主键作为查询条件;使用其他的列查询,查询结果只有自己

非聚簇: 使用其他列作为查询条件,查询结果除了本列还有其他列内容;这种情况需要该列先找到主键,再通过主键再次回表查询数据.

非聚簇索引:
索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需 要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

(找到了索引还需要回表查询,例如myisam引擎,索引和数据在两个不同的文件中,找到了索引,还需要去存储数据的文件中去查找)

一个例子
下面我们创建了一个学生表，做三种查询，来说明什么情况下是聚簇索引，什么情况下不是。

CREATE TABLE student(
id BIGINT,
NO VARCHAR(20),
NAME VARCHAR(20),
PRIMARY KEY('id'),
UNIQUE KEY 'idx_no'('no')
)
1
2
3
4
5
6
7

第一种
直接根据主键查询获取所有字段数据，此时主键是聚簇索引，因为主 键对应的索引叶子节点存储了 id=1 的所有字段的值。

SELECT * FROM student WHERE id = 1
1

第二种
根据编号查询编号和名称，编号本身是一个唯一索引，但查询的列包 含了学生编号和学生名称，当命中编号索引时，该索引的节点的数据存储的是主 键 ID，需要根据主键 ID 重新查询一次，所以这种查询下 no 不是聚簇索引 .

SELECT NO,NAME FROM student WHERE NO = 123
1

第三种
我们根据编号查询编号(有人会问知道编号了还要查询？要，你可能 需要验证该编号在数据库中是否存在)，这种查询命中编号索引时，直接返回编 号，因为所需要的数据就是该索引，不需要回表查询，这种场景下 no 是聚簇索引。

SELECT NO FROM student WHERE NO = 123
1

MySQL 中 InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计.

而 MyISAM 引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中.