mysql内部组件架构,索引管理,视图view
——以下内容摘自马哥教育课堂
===
单进程多线程模型
每个用户连接都使用一个线程
mysql使用线程池来管理各个线程
mysql内部组件架构
connection
--management service & unities(管理服务单元,如备份恢复,集群,合并,迁移工具,复制工具);
connection pool(认证,线程重用,连接限制,内存检查,缓存);
--SQL接口(DML,DDL,存储过程,视图,触发器);
分析器parser(查询翻译成二进制指令,访问权限);
优化器optimizer(文件系统的访问路径,统计数据);
缓存cache和缓冲buffer;
--存储引擎myisam,innodb,...
--文件系统,文件和日志(数据,索引,二进制日志,重做日志,撤销日志,错误日志,中继日志 )
mysql的某些存储引擎,可以把热点数据直接装入内存中,加速数据的访问,周期性同步到磁盘上(innodb可以实现)
mysql内部的工作流程(简化)
用户--[连接管理器--线程管理器]--用户模块--命令派发器--查询缓存(sql-value),记录日志;分析器--[优化器(sql语句),表修改模块,表维护模块,复制模块,状态报告模块]--访问控制模块--表管理器--存储引擎接口--myisam,innodb
mysql逻辑架构(简化版)
客户端--连接/线程处理--查询缓存,分析器--优化器--存储引擎--文件系统
===
索引管理
索引:按照特定数据结构存储的索引,特别适合某些算法快速执行。索引构建于内存中并排好序,可利用搜索算法快速定位某项或若干项,定位后,通过存储引擎到磁盘上加载索引项指向数据行到内存中即可。
没有索引就只能表扫描,把整张表数据从磁盘加载到内存中,再一行行比对操作,这显然费时费力。
索引构建原理
抽取表中某个字段做排序后单独存放为整字段索引中,然后,在此基础之上构建一个稀疏索引,比如分为四段索引A-H,I-M,O-T,U-Z分别映射到整字段索引中,如果要查找苹果,则会把O-T的字段加载到内存中查找即可。
B+树索引--多级稀疏格式索引来构建
索引类型:
聚集索引,非聚集索引:数据是否与索引存储在一起
主键索引,辅助索引:主键索引可实现一对一查询,辅助索引通常用于辅助其他查询操作
稠密索引,稀疏索引:索引是否索引了每个数据项
B+树索引,HASH索引(一对一查找),R树索引,全文索引:不同的索引数据结构
单键索引,组合索引:索引由一个字段还是多个字段来构建
mysql使用左前缀索引:LIKE 'ABC%'
弊端:LIKE '%abc00'就不能使用索引了
覆盖索引:例如在组合索引的场景中,在索引的基础上就找到需要查询的结果了,而无需再加载表数据的方式称作覆盖索引。
如何管理和使用索引
创建索引:创建表时指定,也可以单独创建
HELP CREATE TABLE
HELP CREATE INDEX;HELP DROP INDEX
索引没必要修改,只有创建/删除
在一张大表中不要随意创建/删除索引,非常耗费资源
操作演示
# mysql < hellodb.sql
> USE hellodb
> SHOW TABLES;
> SELECT * FROM classes;
> HELP SHOW INDEX
> SHOW INDEXES FROM students;
结果显示在StuID上建立了索引
> SELECT * FROM students WHERE StuID=3;
> EXPLAIN SELECT * FROM students WHERE StuID=3\G
结果显示,使用了索引,只查询了1行即命中结果
> EXPLAIN SELECT * FROM students WHERE Age=9\G
结果显示,没有使用索引,需要查询25行
> HELP ALTER TABLE
> ALTER TABLE students ADD INDEX(Age);
此时修改表,添加索引创建在Age字段上
> SHOW INDEXES FROM students;
结果显示在StuID,Age上建立了索引
> EXPLAIN SELECT * FROM students WHERE Age=9\G
结果显示,使用索引,需要查询1行
> CREATE INDEX name ON students (Name);
> SHOW INDEXES FROM students;
此时,该表有3个索引,都是BTREE索引
> EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%'\G
结果显示,过滤了6行,使用了索引
> EXPLAIN SELECT * FROM students WHERE Name LIKE '%X'\G
结果显示,没有使用索引,全表扫描
===
视图view
视图是虚表,是存储下来的SELECT语句
创建视图 HELP CREATE VIEW;
mysql的视图不完善?
雾化视图:把select语句的执行结果永久保存下来
> CREATE VIEW test AS SELECT StuID,Name,Age, FROM students;
> SHOW TABLES;
可以看到test表
> SHOW TABLES STATUS LIKE 'test'\G;
大部分提示NULL,comment提示VIEW
> SELECT * FROM test;
> SELECT * FROM test WHERE Age=22;
> EXPLAIN SELECT * FROM test WHERE Age=22;
同样可以使用索引Age
删除视图 HELP DROP VIEW;
视图能插入数据吗?
可以,往视图中插入数据,其实是插入到基表中,所以能否插入成功取决于基表对字段的约束。