MYSQL

目录

一、MySQL 架构

连接层：

服务层：

引擎层：

物理文件层：

二、引擎

一、概述

????????查看支持的引擎

????????查看表引擎

????????修改引擎

二、InnoDB

三、MyISAM

区别：

三、索引

一、什么是索引

二、索引原理

三、索引优势

四、索引劣势

五、索引创建原则

? ? ? ? 一、哪些情况需要创建索引

????????二、哪些情况不要创建索引

六、索引分类

????????主键索引：? ? ??

????????单值索引：

????????唯一索引:

????????组合索引(复合索引)：

????????全文索引：

????????查看索引：

七、索引数据结构

八、聚簇索引和非聚簇索引

聚簇索引

非聚簇索引

九、回表查询

---

一、MySQL 架构

连接层：

客户端链接请求，对用户进行认证。

服务层：

可以接收sql，调用存储过程，优化sql，缓存数据

引擎层：

负责实际与文件层进行交互操作的，可以由不同的引擎选择。

物理文件层：

存储表数据以及各种日志文件。

二、引擎

一、概述

????????数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控 制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程 序的要求。

? ? ? ? 引擎是指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;

二、InnoDB

????????默认的存储引擎。支持事务，行级锁（并发量大），外键约束，容量大，支持索引，支持缓存，支持主键自增，不支持村拿出表总行数，需要sql逐行统计。

三、MyISAM

不支持事务，不支持行级锁，支持表锁并发量小，不支持外键，支持全文检索，MyISAM存储了表的总行数，统计速度快。

区别：

三、索引

????????对于少量的数据，查询 的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100 万条数据逐 页查询的时间是无法被用户接受的。

一、什么是索引

????????排好序的快速查找的数据结构。

????????数据库在存储数据本身之外，还维护着一个满足特定查找算法的数据结构， 这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高 级查找算法，这种数据结构就是索引。

二、索引原理

????????索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就 能够快速地找到所需的内容。快速地找到所需要的数据位置拿到物理地址。

三、索引优势

????????提高数据检索的效率,降低数据库的 IO 成本;

????????通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;

四、索引劣势

? ? ? ? 索引也要占用空间，数据进行增删改索引也要更新时间。

五、索引创建原则

? ? ? ? 一、哪些情况需要创建索引

? ? ? ? 主键，外键，查询条件（where后边的列，例如新闻标题），排序分组使用的列。

????????二、哪些情况不要创建索引

? ? ? ? 表数据太少，增删改频繁的表（更改数据也要更改索引），Where 条件里用不到的字段不创建索引，数据重复且分布平均的表字段（例如：性别）。

六、索引分类

????????主键索引：? ? ??

????????设定为主键后数据库会自动建立索引

????????ALTER TABLE表名 add PRIMARY KEY表名(列名);

????????删除建主键索引：

????????ALTER TABLE表名 drop PRIMARY KEY ;

????????单值索引：

????????即一个索引只包含单个列，一个表可以有多个单列索引

????????创建单值索引

????????CREATE INDEX索引名 ON表名(列名);

????????删除索引：

????????DROP INDEX索引名;

????????唯一索引:

????????索引列的值必须唯一，允许为 null

????????CREATE UNIQUE INDEX索引名 ON表名(列名);

????????删除索引

????????DROP INDEX索引名 ON表名;????????

????????组合索引(复合索引)：

????????多个列打包,创建一个索引,降低了空间开销，
?? ? ? ?例如，几个查询条件经常组合使用。

????????创建复合索引
?? ??? ?CREATE INDEX 索引名 ON 表名(列 1,列 2...);
?? ??? ?删除索引：
?? ??? ?DROP INDEX 索引名 ON 表名;

?组合索引最左前缀原则：
?? ? ? ?在使用组合索引的列作为条件时,必须要出现最左侧列为条件,
?? ??? ?否则组合索引不生效

????????全文索引：

????????需要模糊查询时，一般索引无效，这时候就可以使用全文索引了。

????????CREATE FULLTEXT INDEX索引名 ON表名(字段名) WITH PARSER ngram;

????????SELECT结果 FROM表名 WHERE MATCH(列名) AGAINST(‘搜索词')

????????查看索引：

????????SHOW INDEX FROM表名;

七、索引数据结构

????????mysql使用自增主键。

树状有：
?? ? ? 二叉树：像链表一样单边增长。无效作用。
?? ? ? 红黑树：虽然会旋转平衡,但是也是一边的增长,树的高度大。
?? ? ? B树：是多路平衡树，叶子节点之间没有联系。
?? ? ? B+树：多路平衡树，叶子节点之间有联系。
?? ? ??
? ? ? ?所以mysql中最终选择了B+树。
?? ? ? B+树一个节点中可以存储更多的数据,而且数据都存储在叶子节点,这样非叶子节点中就可以存储更多索引数据，叶子节点之间还有指针指向,便于范围查询。

八、聚簇索引和非聚簇索引

聚簇索引

????????找到了索引就找到了需要的数据。

????????innodb中的主键就是聚簇索引。

非聚簇索引

????????索引的存储和数据的存储分离，找到了索引但没找到数据，要根据索引上的值(主键)再次回表查询就是非聚簇索引。

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

九、回表查询

?????????评判标准就是看查询是否能直接找到想要的数据。

????????主键,一级索引,可以直接找到数据, 不需要回表查询。

????????二级索引, 如果通过索引列还查询了其他列,需要回表查询。

????????如果只需要查询索引列本身,不需要回表查询。