数据库:
关系型数据库:
1.架构
2.索引 *
3.锁 *
4.语法 *
5.理论范式
1.如何设计一个关系型数据库
程序实例:存储管理,缓存机制,sql解析,日志管理
权限划分,异常机制,索引管理,锁模块
存储:文件系统
索引模块:
1.为什么要使用索引
避免全表扫描,快速扫描,提升速率
2.什么样的信息能成为索引 (mysql 主键默认为索引)
主键,唯一键以及普通键
3.索引的数据结构
A.建立二叉查找树进行二分查找
B.建立B-Tree结构进行查找
C.建立B+-Tree结构进行查找 (mysql 主要采用这个)
非叶子节点存储的是 数据的索引,数据存储在叶子节点
这样可以存储更多的关键字
B+树的磁盘读写代价更低,因为非叶子节点只存数据索引
B+树的查询效率更加稳定 所有关键字查询都是从根节点到叶子
B+树更有利于对于数据库的扫描,只要遍历叶子节点就行了
D.建立Hash结构进行查找
比B+-Tree速度快,但缺点是 只能 “=”,“IN”,
不能使用范围查询
4.密集索引与稀疏索引的区别
密集索引文件中的每个搜索码值都对应一个索引值
稀疏索引文件中只为索引码的某些值建立索引项
InnoDB 有且仅有一个密集索引
若一个主键被定义,该主键则作为密集索引
若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
若不满足以上条件,Innodb内部会生成一个隐藏主键(密集索引)
非主键索引存储相关键位和其对应的主键值,需要两次查找 (稀疏索引)
innodb 索引与数据存在一个文件中
5.如何定位并优化慢查询Sql
A.根据慢日志定位慢查询SQL
show variable lilke '%quer%' 查看一些mysql系统遍历
show status like '%slow_queries%' 慢查询sql的条数
set global slow_query_log = on; 打开慢查询
set global long_query_time = 1; 设置慢查询超过1s,则为慢查询;该变量设置完后,需要重新连接数据库
(重启数据库客户端会还原,永久的话需要改配置文件)
slow_query_log_file 是存放慢sql日志的地址
B.使用explain 等工具分析 SQL
explain sql语句
字段属性: ID : 越大越先执行
type : 找到需要数据行的方式
....>index > all 出现这两个,就有可能需要优化了
extra :
using filesort 表示MySql 会对结果使用一个外部索引排序,
而不是从表里按索引次序读到相关内容。可能在
内存或者磁盘上进行排序。MySql中无法利用索引
完成的排序操作成为:“文件排序”
using temporary 表示MySql在对查询结果排序时使用临时表。常见于
排序order by 和 分组查询 group by
显示成这个两个字段,则需要进行sql 优化了
key: 表示用了哪个键的索引
C.修改sql 或者 尽量让 sql 走索引
加索引:
alter table 表名 add index 索引名称(字段名)
select xx from xx force index(xxx)强制走哪个索引
6.联合索引的最左匹配原则的成因
最左前缀匹配原则: mysql会一直向右匹配直到遇到
范围查询(>,<,bwtween,like)就停止匹配,比如
a=3 and b=4 and c>5 and d=6;如果建立(a,b,c,d)
顺序的索引,d是用不到索引的。如果建立的(a,b,d,c)
的索引则都可以用到。
= 和 in 可以乱序,比如 a=1 and b=2 and c=3建立
(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你
优化成索引可以识别的形式。
7.索引是建立得越多越好吗
不,
数据量小的表不需要建立索引,建立会增加额外的索引开销
数据变更需要维护索引,因此更多的索引意味着更多的
维护成本,
更多的索引意味着也需要更多的空间
锁:
1.MyISAM与InnoDB关于锁方面的区别是什么
MyISAM默认用的是表级锁,不支持行级锁
在 select 的时候会被加上读锁,这时再执行
insert,update,delete等写的操作会被阻塞,等
读的查询完成后,读锁释放。才能加写锁,再进行
写的操作。
lock tables 表名 read; // 加读锁 读锁是共享的
lock tables 表名 write; //写锁
unlock tables; //释放锁
selet 语句 for update //排他锁
InnoDB默认用的是行级锁,也支持表级锁
采用二段锁,mysql默认是自动提交的
show variables like '%autocommit%';
sql语句 lock in share mode //加共享锁,为行锁
当不走所索引的时候,用的是表级锁
当走索引的时候,用的是行级锁
MyISAM 适合的场景
频繁执行全表count语句
对数据进行增删改的频率不高,查询频繁的
没有事务
InnoDB适合的场景
数据增删改查都相当频繁
可靠性要求比较,要求事务
2.数据库事务的四大特性
原子性(Atomic)
一致性(Consistency)--- a 与 b 互相转账,无论次数,两人总金额不变
隔离性(isolation)
持久性(Durability)
3.事务隔离级别以及各级别下的并发访问问题
select @@tx_isolation 查看事务隔离级别
set session transaction ioslation level ___设置隔离级别
事务并发引起的问题:
更新丢失--Mysql所有事务隔离级别在数据库层面上均可避免
脏读 -- READ-COMMITTED事务隔壁级别以上可以避免
不可重复读 -- REPEATABLE-READ(InnoDB默认的事务隔离级别) 事务隔离级别以上可避免
幻读 -- SERIALIZABLE事务隔离级别可避免
隔离性越高 性能越低
4.InnoDB可重复读隔离级别下如何避免幻读
表象:快照读(非阻塞读)--伪MVCC
内在:next-key锁(行锁+gap锁)
当前读一般读到的是最新版本
快照读有可能读到最新版本,也有可能读到久版本
5.RC、RR级别下的InnoDB的非阻塞读如何实现
数据行里的 DB_TRX_ID 事务ID
DB_ROLL_PTR 回滚指针
DB_ROW_ID 行号
undo日志
read view
sql关键语法:
group by :(分组统计)
1.满足“SELECT子句中的列名必须为分组列或列函数”
2.列函数对于group by子句,定义的每个组返回一个结果
having:
通常与GROUP BY 子句一起使用 用来过滤的组的
where > group by > having
统计相关: count,sum,max,min,avg
三大范式
热门工具 换一换