Mysql进阶（索引）

一、Mysql架构

下图为mysql完整架构图

连接层：

处理客户端连接请求，主要完成一些类似于连接处理、授权认证、及相关的安全方案。

服务层：

用户写的SQL语句会到服务层进行解析，生成语法树。优化SQL语句，生成执行计划。

主要完成大多数的核心服务功能，如SQL接口，并完成缓存查询，SQL的分析和优化及部分内置函数的执行。所f有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。

引擎层：

存储引擎层，存储的引擎真正负责Mysql中数据的存储和提取，服务器通过API与存储引擎（文件层）进行通信。可有不同的引擎选择。

物理文件层：

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

注：

        在MySQL中，API通常指的是应用程序接口（Application Programming Interface），它是一组定义了不同组件之间交互的规范和协议。在MySQL中，API可以用来与存储引擎进行通信，允许应用程序通过API调用来执行数据库操作，比如查询、插入、更新和删除数据等。不同的存储引擎可能会提供不同的API，而MySQL服务器作为中间层，通过这些API与存储引擎进行通信，从而实现对数据库的操作。常见的MySQL存储引擎包括InnoDB、MyISAM、Memory等，它们都提供了与服务器通信的API，以便应用程序可以与数据库进行交互。

二、引擎层

引擎指的是mysql在数据操作时，所使用的技术（事物），mysal中提供了多种引擎技术，不同的引擎特征不同。

查看支持的引擎：

SHOW ENGINES;

查看表引擎：

SHOW TABLE STATUS LIKE '表名'

修改引擎：

方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.
方式 2:建表时指定 CREATE TABLE 表名(...)ENGINE=MYISAM;
方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;

InnoDB：

默认的存储引擎。InnoDB是一个事务性的存储引擎，有行级锁和外键约束，支持全文检索（全文

索引），它的设计目标是处理大容量数据库系统，Mysal运行时，InnoDB会在内存中建立缓存池，用于缓冲数据和索引；支持主键自增，不支持存储表的总行数，需要sql运行统计。

Myisam：

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

所以我们在不同的数据操作时，选择合适的引擎操作，提高效率。

 三、索引

索引：

索引是排好序的数据结构（例如二叉树），我们借助索引，索引类似一个目录，通过目录可以快速定位到数据位置，拿到物理地址。用的是B+树。

优势：

提高查询的效率，降低IO次数。（在硬盘上查询次数）。

通过索引进行条件查询，排序，分组，提高效率，降低了CPU的消耗。

劣势：

索引需要占用内存空间（书的目录占用书的空间）。

数据进行增，删，改时索引树也是需要更新，需要时间。

索引创建原则：

1.建议添加索引的情况：

主键；外键；查询条件where后面的列；排序，分组使用的列

2.不建议添加索引的情况：

表数据太少的；增删改频繁的表，因为更改数据，需要更新索引树；不用作查询条件；数据重复率高的（性别非男即女）。

索引分类：

1.主键索引：

设置某列为主键，为主键自动添加主键索引。

CREATE TABLE test(
id INT,
NAME VARCHAR(20),
age INT
)

-- 主键索引
-- 设定为主键后数据库会自动建立索引
ALTER TABLE test ADD PRIMARY KEY test(id);
-- 删除建主键索引：
ALTER TABLE test DROP PRIMARY KEY ;

SHOW TABLE STATUS LIKE 'test'

2.单值索引

给某一个列添加索引，一个索引对应一个列，一个表中可以有多个单值索引。

-- 单值索引
CREATE INDEX name_index ON test(NAME);

DROP INDEX name_index ON test;

3.唯一索引

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

-- 唯一索引
CREATE UNIQUE INDEX  id_index ON test(id);

DROP INDEX id_index ON test;

4.组合索引（复合索引）

一个索引包含多个列，降低了空间开销，当表的行数远大于索引列的数目时可以使用复合索引。

-- 组合索引（复合索引）

CREATE INDEX A_B_index ON test(A,B);

DROP INDEX 索引名 ON 表名;

在刚刚的表上增减三界列，分别为A,B,C三列，为varchar类型

组合索引最左前缀原则：

在使用组合索引的列作为查询条件时，必须要出现最左侧列为条件，否则组合索引不生效。

例如表中有A,B,C三列，为A,B两列创建组合索引。

-- 组合索引最左前缀原则
EXPLAIN SELECT * FROM test WHERE a='1' AND b='1' -- 生效
EXPLAIN SELECT * FROM test WHERE b='1' AND a='1' -- 生效
EXPLAIN SELECT * FROM test WHERE a='1' AND c='1' -- 生效
EXPLAIN SELECT * FROM test WHERE b='1' AND b='1' -- 不生效

前面三个：

最后一个：

5.全文索引

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

向表中的name列中添加一条“我是张三”的数据

CREATE FULLTEXT INDEX name_index ON test(NAME) WITH PARSER ngram;

SELECT * FROM test WHERE MATCH(NAME) AGAINST('张三')

-- CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;
-- SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

索引数据结构：

Mysql（InnoDB）存储引擎就是用B+树实现其索引的。即一个节点中可以存储更多的数据，而且数据都存储在叶子节点，这样非叶子节点中就可以存储更多的索引数据。叶子之间还有指针指向，便于范围查询。

Mysql使用自增主键，二叉树：像链表一样单边增长。红黑树：虽然会旋转平衡，但是也是单边增长，树的高度大。B树：多路平衡树，叶子节点之间没有联系。B+树：多路平衡树，叶子之间有联系。

聚簇索引和非聚簇索引：

聚簇索引：

找到了索引就找到了数据。

例如innodb中的主键索引(一级索引)就是聚簇索引  。

非聚簇索引：

找到了索引没有找到数据,需要再次的查询

myisam:  

由于myisam引擎中表结构,数据,索引都是在不同的文件中,所以索引都是 非聚簇索引.
innodb:

例如name也有索引(二级索引),但是不是聚簇索引,为需要先通过name索引树,找到主键索引树,然后才能找到数据

回表查询 ：

 评判标准就是看查询是否直接能够找到想要的数据。
 例如以下三种情况：

主键,一级索引,可以直接找到数据, 不需要回表查询
二级索引, 如果通过索引列还查询了其他列,需要回表查询；如果只需要查询索引列本身,不需要回表查询。