image.png

如何设计一个关系型数据库

image.png

索引模块

使用索引目的为快速查询数据

主键，唯一键以及普通键适合成为索引

索引的数据结构

1.生成索引，建立二叉树进行二分查找
2.生成索引，建立B-Tree树结构进行查找
3.生成索引，建立B+Tree树结构进行查找 （Mysql）
4.生成索引，建立Hash结构进行查找

二叉查找树

image.png image.png

若删除2，6，则二叉树变为线性，查询由O(logn)变为O(n)
每一层都会产生IO，二叉树很很深，导致性能下降

B-Tree

image.png

定义
1.根节点至少包括两个孩子
2.树种每个节点最多含有m个孩子（m>=2）
//ceil:去上限 ceil(3/2)=2
3.除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子
4.所有叶子节点都位于同一层

image.png

B+Tree

B+树是B树的变体，其定义基本与B树相同，除了
1.非叶子节点的子树指针与关键字个数相同
// 子树大于等于k[i],小于k[i+1]
2.非叶子节点的子树指针P[i],指向关键字值[K[i],K[i+1])的子树
3.非叶子节点仅用来索引，数据都保存在叶子节点中
4.所有叶子节点均有一个链指针指向下一个叶子节点

image.png

B+Tree更适合用来做存储索引
1.B+树的磁盘读写代价更低（节点只存放索引，不存数据，比B树更小，相同内存存放数据更多）
2.B+树的查询效率更加稳定（都要指向最后的叶子节点）
3.B+树更有利于对数据库的扫描（在叶子节点中扫描）

Hash索引

image.png

缺点：
1.仅仅能满足“=”，“IN”，不能满足范围查询
2.无法被用来避免数据的排序操作
3.不用利用部分索引 引键查询
4.不能避免表扫描
5.遇到大量Hash值相等的情况后性能并不一定会比B-Tree索引高

BitMap索引

image.png

mysql不支持，Oracle支持
锁数据，不适合高并发

索引模块

密集索引和稀疏索引的区别

1.密集索引文件中的每个搜索码值都对用一个索引值
2.稀疏索引文件只为索引码的某些值建立索引项

image.png

InnoDB
1.若一个主键被定义，该主键则作为密集索引
2.若没有主键被定义，该表的一个唯一非空索引则作为密集索引
3.若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
4.非主键索引存储相关键位和其对应的主键值，包含两次查找

image.png image.png

InnoDB 索引文件储存在一起，
myisam 索引为.MYI，数据为.MYD

小结

1.索引能使我们避免全表扫描，提升检索效率
2.主键，唯一键，具有一定区分性的键都能成为索引
3.B+树，Hash，位图，mysql 不支持位图，InnoDB和myisam 为B+树

如何定位并优化慢查询Sql

1.根据慢日志定位慢查询sql
2.使用explain等工具分析sql
3.修改sql或尽量让sql走索引

image.png

show variables like '%quer%'

slow_query_log 慢日志开启
slow_query_log_file 慢日志文件
//1s 比较合理，关闭客户端清0
long_query_time 慢日志记录规定时间

//value 为慢查询数量
show status like '%slow_queries%';

set global slow_query_log = on;
set global long_query_time = 1; (重连或更改 ini文件)

image.png

  //构造慢查询
select name from table order by name desc;
//分析慢查询
explain select name from table order by name desc;

image.png

index/all 表示走的全表扫描

image.png

//加索引
alter table person_info_large add index idx_name(name);

image.png

Extra:Using index 使用索引

image.png

//强制使用索引
force index (primary)
mysql 查询优化器决定使用索引