一、数据库架构
存储：存储数据
程序实例：组织数据
存储管理：物理数据用逻辑形式表示
缓存机制：提高IO效率
SQL解析：解析SQL语句
日志管理：操作记录
权限划分：多用户管理
容灾机制
索引管理：优化查询效率
锁管理：支持并发
二、索引模块
## 1、为什么要使用索引
数据量很少，不需要索引，但是数据量大，全表索引效率就很低。使用索引，提高查询速度。
2、什么信息能成为索引
主键、唯一键、普通键。
3、索引的数据结构
B+Tree 和 Hash结构。这里先介绍二n叉查找树索引和B树
二叉树
二查查找树的查询复杂度 O（logn），但是二叉树可能会退化成单边树，这样复杂度退化为O(n)
就算是平衡二叉树是平衡的，但是查询时，每遍历一个节点，就要进行一次IO，效率低下。
B树
ceil取上整
约束
B+树
存储更多关键字、更好地实现范围统计
第2点，稳定的原因是，每次查询走的节点一样多
Hash索引
Hash索引的单个查询效率高，O(1)
缺点：
只能满足‘=’，‘IN’这种等值查询，范围查询只能全表扫描
无法用来避免数据的排序操作（无序的）
不能利用部分索引键查询（因为Hash的组合索引，是将组合的键合并之后hash，而不是对每个键都hash，当只有部分索引键查询的时候，无法使用）
不能避免表扫描（hash获取index之后，还是要扫描整个buckets中的值，因为不同值的hash可能相同）
如果大量Hash值相等，性能就会严重降低
BitMap
4、密集索引和稀疏索引的区别
MyISAM存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引
innodb存储引擎：有且只有一个密集索引。密集索引的选取规则如下：
- 若主键被定义，则主键作为密集索引
- 如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引
- 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
- 非主键索引存储相关键位和其对应的主键值，包含两次查找
所以，innodb的索引和数据存在一起/一个文件，MyISAM是分开存储
5、如何定位并优化满查询sql
这个必须要经验，没做过的话只要深挖就会露馅，所以这里只提供大致思路
（1）分析慢日志定位慢查询sql
三个参数：
ong_query_time 设置超时时间，超过这个时间，就会被记录到慢sql日志
slow_query_log 开关 on/off
slow_query_log_file 慢sql日志文件地址
通过下列语句查看参数
show variables like "%query%";
通过下列语句查看(本次会话)慢sql条数
show status like "%slow_queries%";
打开慢查询：
set global slow_query_log = on;
set global long_query_time = 1;
最好在配置文件中设置，这样是永久保存 my.conf
（2）使用explain等工具分析sql
explain 放在select前面，不会真正运行select
type：表示mysql找到需要数据行的方式
all，全表扫描
extra：
无法利用索引完成的排序操作称为“文件排序”
（3）修改sql或者尽量让sql走索引
i.改sql
由于在这个例子厘米，account与name一一对应，并且account有索引，那么可以把name换成account，并且业务上都差不多。
ii.加索引
但是如果就是要name，那么可以对name建索引
alter table person_info add index idx_name(name);
附加：
explain select count(id) from person_info;
首先统计肯定直接走叶子节点的链表，但是索引没有走主键，因为查询优化器会选择一个最优的，可能是主键索引是密集索引，每个叶子节点都存放了所有数据，所以走了account这种辅助索引。
explain select count(id) from person_info force index(primary);
可以像上面那么强制主键索引
6、联合索引的最左匹配原则的成因
建表的时候
key index_name (column1,column2.....),
下面的就会走联合索引
select * from person_info where column1="xxxxx" and column2="xxxx";
select * from person_info where column1="xxxxx" ;
但是下面就不走联合索引了：
select * from person_info where column2="xxxx";
所以下面也是走联合索引：
select * from person_info where column2="xxxx" and column1="xxxx";
因为联合索引首先会对最左边的字段进行排序，然后再依次对后面的排序，所以其他键都不是绝对有序的。如果不是最左匹配，就无法走联合索引
7、索引越多越好吗？----不是
三、锁模块
1、MyISAM与InnoDB关于锁的区别
前者表级锁，后者行锁和表锁都有，默认行锁
读锁/共享锁/S锁 ，都可以读，但是都不能写，可以同时上多个S锁
写锁/排他锁/X锁，只有获取排他锁的事务可以对数据就行读取和修改。
MyISAM
SQL跑完之后自动解锁
MyISAM 读时（select）加读锁，此时更新操作不能进行。
两种读锁方法：
（1）select默认加读锁
（2）显式加读锁
lock tables tableName read;
解锁：unlock tables;
写锁/排他锁：
（1）更新默认加读锁
（2）显式加读锁
lock tables tableName write;
（3）select加排他锁
在末尾加for update
select * from tableName where id between 1 and 20000 for update;
InnoDB
InnoDB是二段锁，事务开始的时候上锁，在commit的时候自动解锁。InnoDB默认为commit自动提交。也就是执行SQL后自动commit
注意非阻塞select,InnoDB对select进行优化，并未上S锁
显式上读锁：
lock in share mode
select * from tableName where id = 3 lock in share mode;
行锁是锁的索引，当某字段没有索引，然后通过这个字段查询或者更新记录，也就是不走索引时，就会用表锁
如果走索引，就是用行锁+gap锁（RR级别）
InnoDB有表级意向锁
IS：共享读锁
IX：排他写锁
锁的粒度也细，开销越大
2、两种引擎的使用场景
MyISAM
- 频繁执行全表count语句（MyISAM保存了表的行数，InnoDB没有）
- 对数据进行增删改的频率不高，查询非常频繁。
- 没有事务
InnoDB
-
增删该查非常频繁
-
可靠性要求高，需要支持事务
3、数据库锁分类
- 按照粒度划分，表级锁，行级锁，页级锁（BDB引擎，介于表和行之间）
- 锁级别划分，S锁，X锁
- 加锁方式，自动锁，显式锁
- 按照操作划分，DML锁（对数据的操作，增删该查）、DDL锁（表结构变更，如alt）
- 使用方式，乐观锁，悲观锁
4、ACID
原子型（Atomic）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
5、事务并发访问引起的问题
获取事务隔离级别。
select @@tx_isolation;
设置当前session的隔离级别：
set session transaction isolation level read uncommitted;
事务：
start transaction;
.......
.......
commit;
或者
start transaction;
.......
.......
rollback;
四种隔离级别：Read uncommitted、Read committed、Repeatable read、Serializable
更新丢失
（主流数据库不会出现这类问题，因为会加锁）
本应该为120，但是被回滚为100，写入的+20丢失了。
脏读
一个事务读到另一个事务未提交的事务。
eg: A事务从1000元取出100元，此时B事务去读钱数，读到了900，然后A事务回滚，B事务+200，最后钱为1100，但是应该为1200。 破坏了一致性。
RC级别以上，可以避免脏读，Oracle默认RC级别
不可重复读
事务A多次读取同一数据，事务B在事务A多次读取的中间修改这一数据并且提交事物，导致多次读取数据不同。
RR级别以上，可以避免不可重复读，InnoDB默认RR级别。
以下情况需要考虑，如果数据为1600，在RR级别下，事务A多次读都是1600，然后事务B+400，那么此时的真实值为2000，那么如果事务A对该数据进行修改，会出错吗？比如对1600-100，那么数据变成了1500，实际应该为1900。
这种情况可以避免
例如在事务A中这样更新：
update tableName set balance = balance - 100 where id =1;
等待事务A提交后，该数据变为1900。
幻读
事务B在事务A的查询范围内新增了一条数据，事务A再次查询发现多了一条数据。
从理论上来讲，只有Serializable避免，但是InnoDB的RR级别解决了这个问题（next-key lock）
只有Serializable级别，所有的sql执行都上锁，其他级别select没上锁
6、next-key lock
行锁（记录锁record lock）+间隙锁（Gap锁）
如果where条件全部命中，则不会用Gap锁，因为select查询的条件一定是唯一的。
如果where条件没有全部命中，或者全部都没有命中，比如范围查询，会加gap锁。
eg：查询部分命中，比如id只有5，9
select * from tb where id in (5,7,9) lock in share mode;
此时另外一个事务不能插入5-9之间。
eg：全部都没有命中，表中id 7,8不存在
此时事务A删除不存在的id 7，此时where全部未命中
delete from tb where id = 7;
事务B此时插入不存在的 id 8，会阻塞。
gap锁会用在走非唯一索引或者不走索引的当前读中
此时(6,9],(9,11]都被锁柱
不走索引：锁住所有gap
非阻塞读(使用MVCC)
Mysql使用了Multi-Version Concurrent Controll多版本并发控制的技术来实现非阻塞式读的。
MySQL InnoDB使用MVCC来实现非阻塞式读，在这个模式下，数据库会为每个数据记录维护多个版本。在可重复读隔离级别下，事务第一次查询记录的时候，会记录下一个时间点，在该事务内如果再次（可是以不同的SELECT）查询相同的数据的话，事务只会取时间点前的记录版本，这样在不需要对数据加锁的情况下就实现了可重复读的隔离级别了，而且并发性能更好。在同一事务内多次查询同一数据，也不是就返回一个固定的记录版本，如果事务先查询了某个记录，随后自己又更新了这个数据，等再查询该数据的话，返回的就是自己更新过后的数据版本了。
135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
