架构
- 存储(文件系统) 物理存储
- 存储管理 -> 管理数据存储
- 缓存管理 -> 优化执行效率
- SQL解析 -> 解析SQL语句
- 日志管理 -> 记录操作日志
- 权限划分 -> 管理多用户的增删改查权限
- 容灾机制 -> 数据库异常恢复
- 索引管理 -> 优化数据查询效率
- 锁管理 -> 支持数据库并发操作
索引
为什么要使用索引?
当数据表数据量很少的时候,我们可以不用索引,但是当数据量大到影响查询效率,这时候就要用到索引了,避免全表扫描,加索引会加快查询的效率。
索引的数据结构
建立二叉查找树进行二分查找 O(logn)
每个节点最多只能两个子节点,且左子树小于父节点,右子树大于父节点。
缺点:
当数据量很大时,查询效率也会降低,插入效率也会降低。
当关键数递增形成线性二叉树,查询时间变成0(n),虽然可以通过树的旋转变成平衡二叉树。
随着数据量的增多,二叉树会越来越深,底层的数据会越来越多,查找发生的IO也会越来多。
建立B-Tree结构进行查找
让每个树节点尽可能的存储更多数据,让树的高度更矮减少IO次数
在二叉树的基础上,每个节点保存的数据更多,子树也更多,叶节点都在同一层,同时比二叉树矮,查找的IO也很少,大大提高查询的效率。节点保存的数据量要小于子树的数量
缺点:
磁盘单元为磁盘块4K,InnoDB的存储单元为页16K,每个节点存储量为一页16K,存储键值、对应的数据、指向孩子节点的指针,当里面的数据很大时,相应存储的键值就少了,每个节点存储的行数也就少了,数据量骤增时树的深度会越来越深,因此查询效率也会随之下降。
建立B+-Tree结构进行查找
在B-Tree结构的基础上,将数据保存至叶子节点,非叶子节点只保存索引。
优点:
因为非叶子节点只存储索引,所以可以存储更多的索引,树的高度也比B-Tree矮很多。
所有叶子节点均有一个链指针指向下一个叶子节点,且每个叶子节点保存的数据按升序排列, 在范围查询时,不用重新从根节点开始查找,可以横向跨子树进行查询,且支持数据的排序。
查询效率更加稳定,每次查询都必须从根节点到叶子节点,三阶B+tree能够存储10亿条数据(索引)
建立Hash以及BitMap结构进行查找
Hash: 根据hash函数的运算,只需一次就能定位到查询数据所在的地址。所以理论上Hash索引的查询效率会高于B+Tree。
缺点:仅满足精准定位的查询,“=”,“IN” ,不支持范围查询,数据排序,部分索引(组合索引)查询,。遇到大量Hash值相等的情况后性能不一定比B+Tree索引高,不能避免表扫描,因为当Hash值相等的时候需要比较。
BitMap:Oracle支持,其他数据库还不支持。01010101
密集索引和稀疏索引
密集索引文件中每个搜索码值都对应一个索引值
稀疏索引文件只为索引码的某些值建立索引项
mysam存储引擎,不管是主键索引,唯一键索引还是普通索引都是稀疏索引
innodb存储引擎:有且只有一个密集索引。
如何定位并优化慢查询SQL?
根据慢日志定位慢查询SQL
show variables like '%query%'
# 是否开启慢日志查询
slow_query_log ON/OFF
set global show_query_log = ON;
# 查询时间超过n秒的会被记录下来
long_query_time n
set global long_query_time = 1;
# 记录慢查询的日志路径
slow_query_log_file
# 永久保存配置需要在my.cnf里设置
# 慢查询SQL的条数(当前会话)
show status like '%slow_queries%'
使用explain工具分析SQL
# explain关键字段
type
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
index和all 表示全表扫描
extra
Using filesort
表示mysql会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。
mysql中无法利用索引完成的排序操作称为“文件排序”
Using temporary
表示mysql在对查询结果排序时使用临时表。常见于排序order by 和 分组查询 group by。
# 添加索引
alter table xxx add index idx_field_name(field_name);
# 分析如下语句
explain select count(id) from person_info_large;
type = index; key = account;
MySQL查询优化器并没有选择主键索引作为索引;而是选择了account字段的普通索引;
原因是选择主键索引为密集索引,叶子节点上带有除主键外的其他数据,查询效率会比account字段的索引要低。
# 联合索引的最左前缀匹配原则
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,
比如a=3 and b=4 and c>5 and d=6;
如果建立(a,b,c,d)顺序的索引,d是用不到索引的,
如果建立(a,b,d,c)顺序的索引则a b d都可以用到,
=、in可以乱序,查询优化器会帮助我们优化条件语句的顺序如下,
a=3 and b=4 and d=6 and c>5
修改SQL或者尽量让SQL走索引
索引是建立得越多越好吗?
数据量小的表不需要建立索引,建立会增加额外的索引开销
数据变更需要维护索引,因此更多的索引意味着更多的维护成本
更多的索引意味着也需要更多的空间
锁模块
数据库锁的分类
按锁的粒度划分,可分为表级锁、行级锁、页级锁
按锁级别划分,可分为共享锁、排它锁
按加锁方式划分,可分为自动锁、显式锁
按操作划分,可分为DML锁、DDL锁
按使用方式划分,可分为乐观锁、悲观锁
MyISAM和InnoDB关于锁方面的区别
MyISAM默认用的是表级锁,不支持行级锁
InnoDB默认用的是行级锁,也支持表级锁