MySQL架构，MySQL存储引擎，索引

MySQL架构

连接层

负责与客户端和程序建立连接,认证…

服务层

sql接口 解析器 查询优化器 缓存

引擎层

负责与数据文件系统连接,写数据

物理文件层

负责存储表数据,日志文件,就是依赖于日志

MySQL存储引擎

引擎是数据库中具体与文件交互的技术,不同的引擎实现方式是有区别的

数据库引擎,具体实现者

常用的引擎

myisam 不支持事务,适合查询较多,不支持外键,不支持行锁,支持表锁,支持全文索引,存储表的总行数

select count(*) from admin 直接获取到总行数即可,速度快

innodb 支持事务,适合增删改多,支持事务,表锁,行锁,支持缓存,支持主键自增,支持全文索引,不存储表的总行数

select count(*) from admin 自行统计计算 慢

在数据库中输入此语句,查询该数据库支持的引擎

show engines

索引

数组索引 可以通过索引快速的找到某个位置的数据

为什么要有索引呢？

不使用索引的话,查询从第一行开始,逐行向后查询,直到查询到我们所需要的数据,如果数据量非大的情况下,查询的效率比较低

什么是索引

索引是帮助MySQL高效获取数据的数据结构

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

在一个数据结构中将数据维护着,方便查找

索引原理

索引就类似书的目录,通过目录快速的查询到我们的数据,缩小查询范围

索引的优势

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

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

索引的劣势

也需要占用内存空间

对表进行新增修改删除操作时在操作数据的同时,也需要对索引信息作出修改

索引创建的原则

索引虽好,但是不要乱用

什么情况下需要索引

主键自动建立唯一索引,主键索引

作为查询条件的列,适合创建索引

外键建议建立索引

排序,分组字段适合添加索引

什么情况下不建议使用索引

表记录太少

新增,修改,删除频繁的表 分表 将本来一张表拆分,读写分离

不是查询条件

数据重复且分布均匀的 例如:性别

索引分类

主键索引:

设定为主键后数据库会自动建立索引,不能为空,唯一的,一个表只能有一个主键

单值索引:

一个索引包含一个列,一个表可以有多个单值索引

唯一索引:

数据不能重复

组合索引(复合索引)

一个索引中包含多个列

组合索引最左前缀原则

使用组合索引时要出现最左列,否则索引失效

例如表中有 a,b,c 3 列,为 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=’’索引不生效

使用模糊查询 name like %张%; 这样写,会导致name列的索引失效,like的模糊查询不建议使用

mysql8中建议使用全文索引

全文索引

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

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

SELECT 结构 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词') 

查看索引

SHOW INDEX FROM 表名;

索引数据结构

MySQL Innodb 引擎中默认使用B+数作为数据结构存储索引

排好序的,一个节点可以存储多个数据.横向扩展,使得数的高度降低了

非叶子结点不存储数据,只存储索引,可以放更多的索引

数据记录都存放在叶子结点中,找到了索引也就找到了数据

所有叶子结点之间都有一个链指针,非常适合区间查询 如:age>20 age<50

聚簇索引和非聚簇索引

聚簇索引:

找到了索引就找到了数据,那么这个索引就是聚簇索引

主键可以直接找到数据

根据学号值查询学号,可以直接命中学号,此种场景学号就是聚簇的

非聚簇索引:

找到了索引但没有找到数据,需要根据主键再次回表查询

根据学号值查询学号,姓名,虽然学号加了索引但是还需要查询姓名,需要根据学号找到主键,通过主键回表查询,此种场景就是非聚簇的.

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