mysql内部组件架构,索引管理,视图view

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;


视图能插入数据吗?

可以,往视图中插入数据,其实是插入到基表中,所以能否插入成功取决于基表对字段的约束。








本文转自 zhuhc1988 51CTO博客,原文链接:http://blog.51cto.com/changeflyhigh/1886365,如需转载请自行联系原作者
上一篇:区块链应用 | 区块链将永久改变法律行业的七种方式


下一篇:代码生成利器:IDEA 强大的 Live Templates