mysql数据库note7:常见面试题相关理论

以下会用到的知识理论：
B+Tree:
	按照建立二叉查找树大致相同的方式建立M叉查找树
		二叉查找树：二叉树的每个节点下的左孩子都小于它，右孩子节点都大于它（要求所有的项都能排序）
	
	阶：一个节点的子节点数目的最大值
	
	关键字：k-v。一个数据的索引和这个数据的统称
	
	阶为M的B+树是一颗具有这些特性的树：
		1.数据项存储在叶子节点上
		2.非叶节点存储直到M-1个关键字以指示搜索的方向；关键字i代表子树i+1中的最小的关键字
		3.树的根/树叶，其儿子树在2-M之间
		4.除根外，所有非树叶节点的儿子树在M/2取上界-M之间
		5.所有的树叶都在相同的深度上并有L/2取上界和L之间个数据项。
		
计算机种常见的锁：
	乐观锁与悲观锁：一种设计思路
	
	乐观锁 (Optimistic Lock)：
		乐观的认为业务并不会产生冲突，自己对数据操作结束后才去拿锁更新。
		数据库不自带乐观锁，想实现一般在操作后跟上锁的时间戳或者版本号。
		
	悲观锁 (Pessimistic Lock)
		认为每次获取锁都有可能失败,先获取锁再进行业务操作.
		
	公平锁与非公平锁
		先到的线程先获得锁

	分布式锁
		利用锁的技术控制在分布式模型下同一份数据的修改进程数

	可重入锁与不可重入锁
		线程可以进入它已经拥有的锁的同步代码块/不能进入。

	互斥锁与共享锁
		同时可以有一个线程/多个线程获得锁。

	CAS
		乐观锁实现方式，当多个线程使用CAS操作数据时，竞争失败者会继续竞争。

	ABA问题以及解决方法：
		数据库种不可重复读的概念：事务A访问了数据C后，事务B对C进行了更改，事务A又访问了数据C，得到的C数据不相同。
	
		ABA问题：事务B虽然对C进行了修改，但是它又把C改回到当初的值，A事务得到的值还是一样。
			
		解决：加上版本号
		
	数据库常用：表锁/行锁,共享锁(读锁)/排他锁(写锁)
	
数据库锁：
	表级锁定，行级锁定和页级锁定。
	表锁：
		适用于以查询为主，少量更新的应用。
		锁定粒度大，锁冲突概率高、并发度低
		不会出现死锁、开销小、获取锁和释放锁的速度很快；
	行锁：
		并发度高
		缺点是开销大、加锁慢，行级锁容易发生死锁；
	页锁：
		介于行级锁和表级锁之间
		会发生死锁
	事务会自动加锁,锁在事务提交或回滚时会释放。
		
	Inoodb引擎行锁类型：
		共享锁，排他锁，意向锁。
		共享锁：只读不写锁。
		排他锁：写锁
		意向锁：INNODB自动加的，可同时和和前两种存在
		
	使用：
		共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
		排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE
		
MVCC:多版本并发控制
	数据库默认隔离级别：RR（Repeatable Read，可重复读），MVCC主要适用于Mysql的RC,RR隔离级别
	基本原理:
		通过保存数据在某个时间点的快照来实现的。








mysql数据库
数据库三大范式：
	第一范式：每个列都不可再分割
	第二范式：所有非主键列完全依赖于所有主键
	第三范式：所有非主键列只依赖于主键列
	
mysql数据库系统表：
	1.information_schema	2.mysql		3.performance_schema		4.sys
	1.information_schema:
		存放数据库的所有元数据（所有其他数据库/表/索引/视图等的信息）
	2.performance_schema
		默认是关闭的。需要设置参数： performance_schema 才可以启动该功能，这个参数是静态参数，只能写在my.cnf 中 不能动态修改。主要做一些监控相关的事情
	3.mysql:
		有mysql_install_db脚本初始化权限表，存储权限的表。
		user:用户列，权限列，安全列，资源控制列 authentication_string是密码 user是账号
		db:用户列，权限列。
	4.sys系统库：只有一个表sys_config
	
常用的数据库引擎：
INNODB，MyIasm,MeMory引擎
	Innodb引擎：
		支持事务
		支持行级锁和表锁
		支持外键约束
		索引是索引组织表
			实现是B+Tree索引
			支持hash索引，不支持全文索引
			是聚簇索引
			主键索引的叶子节点存储着行数据
			非主键索引的叶子节点存储的是主键和其他带索引的列数据
		以更新频率高为主
		
		innoDB四大特性：
			插入缓冲
			二次写
			自适应哈希索引
			预读

	MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
	
	MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

索引有哪些类型？
	主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
	唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
		创建唯一索引：ALTER TABLE table_name ADD UNIQUE (column); 
		创建唯一组合索引：ALTER TABLE table_name ADD UNIQUE (column1,column2); 
	普通索引：
		创建普通索引:ALTER TABLE table_name ADD INDEX index_name (column);
		创建组合索引:ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
	全文索引：搜索引擎使用的一种关键技术
		创建全文索引：ALTER TABLE table_name ADD FULLTEXT (column);
		
创建索引的原则：
	最左匹配原则：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
		组合索引非常重要的原则。
		mysql会一直向右匹配直到遇到范围查询（>,<,between,like）
	查询较为频繁。
	更新频繁不适合创建索引
	尽量的扩展索引，不要新建索引
	定义有外键的数据列一定要建立索引
	
聚簇索引和非聚簇索引
	聚簇：将数据存储与索引放到了一块，找到索引也就找到了数据
		InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
			1.如果表定义了PK，则PK就是聚集索引；
			2.如果表没有定义PK，则第一个not NULL unique列是聚集索引；
			3.否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
	非聚簇：分开存储，索引结构的叶子节点指向了数据的对应行（B+树）。myisam把索引先放到内存缓存中，然后找到索引后再寻找数据。
	
普通索引查询，一般需要扫码两遍索引树。

回表查询：定位主键值，再定位行记录。

索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
	实现：将单列索引(name)升级为联合索引(name, sex)，即可避免回表。
		  把被查询的字段，建立到联合索引中。和主键绑定。

联合索引：
	同时建立多个列索引，命中索引，需要按照顺序依次命中。
	
游标：相当于一个结果集指针。

存储过程和函数：数据库编程

触发器：事件发生时，自动触发
	有六种：
		Before Insert
		After Insert
		Before Update
		After Update
		Before Delete
		After Delete
	
超键：唯一标识数据的集合。包括候选键，主键。
	候选键：最小无冗余的超键
	
大表数据查询，怎么优化
	1.优化shema、sql语句+索引
	2.加缓存。	memcached, redis
	3.主从复制，读写分离
	4.垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
	5.水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

数据库优化：
	1.sql语句优化,使用join连接代替子查询
	2.使用索引
	3.使用视图
	4.存储过程和函数
	5.数据库连接池
	
主从复制：
	将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上。
	然后将这些日志重新执行（重做），从而使得从数据库的数据与主数据库保持一致。

读写分离：
	基于主从复制实现的比较多。