- 存储引擎
- 索引
- 锁机制
- 中间件
存储引擎
Mysql 体系结构
Mysql 体系结构.png
- 客户端连接层
支持接口: 支持的客户端链接,列如: C,Java,PHP 等语言来链接Mysql数据库- 第一层: 网络连接层
连接池: 管理,缓冲用户的连接,线程处理等需要缓存的需求- 第二层: 核心处理层
管理服务和工具: 系统的管理和控制工具,列如备份恢复,复制,集群等
SQL接口: 接受SQL命令,并返回查询结构
查询解析器: 验证和解析SQL命令,列如过滤条件,语法结构等
查询优化器: 在执行查询之前,使用默认的一套优化机制进行优化SQL语句
缓存: 如果缓存当中有想查询的数据,则直接将缓存中的数据返回,没有的话在重新查询- 第三层: 存储引擎层
插件式存储引擎: 管理和操作数据的一种机制,包括(存储数据,如更新,查询数据等)- 第四层: 系统文件层
文件系统: 配置文件,数据文件,日志文件,错误文件,二进制文件等等的保存
存储引擎介绍- MySQL数据库使用不同的机制存储表文件,包括存储方式,索引技巧,锁定水平等不同的功能,这些不同的技术已经配套的功能称之为存储引擎
- Oracle,SqlServer等数据库只支持一种存储引擎,而Mysql针对不同的需求,配置不同的存储引擎,就会让数据库采取不同处理数据的方式和扩展功能.
- MySQL支持的存储存储引擎有很多,常用的有三种: InnoDB,MyISAM,MEMORY
- 特性对别
MyISAM存储引擎: 访问块,不支持事务和外键操作
InnoDB存储引擎: 支持事务和外键操作,支持并发控制,占用磁盘空间大(Mysql5.5版本后默认引擎)
MEMORY存储引擎: 内存存储,速度快,不安全,适合销量快速访问的数据
引擎对比图
存储引擎的操作
- 查询数据库支持的存储引擎
SHOW ENGINES;- 查询某个数据库中所有数据表的存储引擎
SHOW TABLE STATUS FROM 数据库名称;- 查询某个数据库中某个数据表的存储引擎
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';- 创建数据表,指定存储引擎
CREATE TABLE 表名(列名 数据类型,...) ENGINE = 引擎名称 ;- 修改数据表的存储引擎
ALTER TABLE 表名 ENGINE = 引擎名称;
存储引擎的选择
- Myisam
特点: 不支持事务和外键操作,读取速度快,节约资源
使用场景: 以查询操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不是很高- InnoDB
特点: MySQL默认的存储引擎,支持事务和外键操作
使用场景: 对事务的完整性有较高的要求,在并发条件下要求数据的一致性,读写频繁的操作!- MeMory
特点: 将所有数据保存到内存中,在需要快速定位记录和其他类似数据环境下,可以提供更快的访问
使用场景: 通常用于更新不太频繁的小表,用来快速得到访问的结果
Mysql索引
索引介绍
- MySQL索引: 是帮助MySQL搞高效获取数据的一种数据结构,所以,索引的本质就是数据结构
- 在表数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引.
索引分类
- 按照功能分类
普通索引: 最基本的索引,没有任何限制
唯一索引: 索引列的值必须唯一,但允许有空值,如果是组合索引,则列值组合必须唯一
主键索引: 一种特殊的唯一索引,不允许有空值,在建表时有主键列同时创建主键索引
联合索引: 就是将单列索引进行组合
外键索引: 只有InnoBD引擎支持外键索引,用来保证数据的一致性,完整性和实现级联操作
全文索引: 快速匹配全部文档的方式,InnoDB引擎5.6版本后才支持全文索引,MEMORY引擎不支持- 按照结构分类
BTree索引: MySQL使用最频繁的一个索引数据结构,时InnoDB和myisam 存储引擎默认的索引类型,底层基于B+Tree数据结构
Hash索引:MySQL中memory引擎默认支持的索引类型
索引的操作
- 创建索引
CREATE [ UNIQUE | FULLTEXT] INDEX 索引名称 [ USING 索引类型 ] --默认时BTree ON 表名(列名...);- 查询索引
SHOE INDEX FROM 表名;- 添加索引
普通索引:ALTER TABLE 表名 ADD INDEX 索引名称(列名);
组合索引:ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
主键索引:ALTER TABLE 表名 ADD PRIMARY KEY 索引名称(列名);
外键索引:ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
唯一索引:ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
全文索引:ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);- 删除索引
DROP INDEX 索引名称 ON 表名;
索引的原理
- 索引是在存储引起中实现的,不同的存储引擎所支持的索引也不一样,这里我们主要介绍InnoDB引擎的BTree索引
- BTree 索引类型是基于B+Tree数据结构的,而B+Tree数据结构又是BTree 数据结构的变种,通常使用在数据库和操作文件中的文件系统,特点是能够保持数据稳定有序.
- 需要理解
磁盘存储
BTree
B+Tree
索引的原理-磁盘存储
- 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的
- 位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么读取什么
- InnoDB 存储引擎中有(Page)的概念,页是磁盘管理的最小单位,在InnoDB存储引擎中默认每个小页的大小为16KB
- InnoDB 引擎将若干个地址连接磁盘块,以此来到达也的大小16KB,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率
索引的原理-BTree
BTree原理
- BTree数据结构
每个节点中不仅包含key值,还有数据,会增加查询数据时磁盘的IO次数
注意: BTree的特点时,只要查询的数据涉及到的磁盘块,磁盘块里的所有数据都会加载出来,增加了IO压力
索引的原理-B+Tree
B+Tree原理
- B+Tree数据结构
非叶子节点只存储key值
所有数据存储在叶子节点
所有叶子节点之间都有连接指针- B+Tree 优点
提高查询速度
减少磁盘的IO次数
树形结构较小
索引设计原则
- 创建索引遵循的原则
1.对查询频次较高,且数据比较大的表建立索引
2.使用唯一索引,区分度越高,使用索引的效率越高
3.索引字段的选择,最佳候选列应从where 子句的条件中提取
4.索引虽然可以有效的提高查询数据库的效率,但并不是越多越好
- 最左匹配原则(使用组合索引)
1.例如:为user表中的name, address, phone列添加组合索引
ALTER TABLE user ADD INDEX idx_three(name,address,phone);
2.此时,组合索引idx_three实际建立了(name)、(name,address),(name,address,phone)三个索引
3.下面的三个SQL语句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三";
4.这三条SQL语句在检索时分别会使用以下索引进行数据匹配(name,address,phone)
(name,address)
(name)
5.索引字段出现的顺序可以是任意的,MySQL优化器会帮我们自动的调整where条件中的顺序
6.如果组合索引中最左边的列不在查询条件中,则不会命中索引
SELECT * FROM user WHERE address = '北京';
