一、如何设计一个关系型数据库
关系型数据库
二、索引相关
1.为什么要使用索引
快速查询数据
2.什么样的信息能成为索引
主键、唯一键及普通键等
3.索引的数据结构
1、生成索引，建立二叉查找树进行二分查找
2、生成索引，建立B-Tree结构进行查找
3、生成索引，建立B+-Tree结构进行查找（mysql常用结构）
生成索引，建立Hash结构进行查找
4.二叉查找树
二叉查找树
优点：使用二分查找法，效率比全表查询快
缺点：当层次越多，发生IO的次数也会越多
5.平衡二叉树
3
/ \
9 20
/ \
15 7
返回 true 。是平衡的
1
/ \
2 2
/ \
3 3
/ \
4 4
返回 false 。不是平衡的
6.B-Tree
1、根节点至少包括两个孩子
2、树中每个节点最多含有m个孩子（m>=2）
3、除根节点和叶节点外，其他每个节点至少有ceil（m/2）个孩子
4、所有叶子节点都位于同一层
5、左关键字永远比左节点下所有子节点关键字大
6、右关键字永远比右节点下所有子节点关键字小
7、其他节点的关键字，在父节点左右关键字之间
优点：每个节点尽可能的多节点，减少树的高度，减少IO树
7.B+-Tree
1、非叶子节点的子树指针与关键字个数相同
2、非叶子节点的子树指针，指向关键字值的子树
3、非叶子节点仅用来索引，数据都保存在叶子节点中
4、所有叶子节点均有一个链指针指向下一个叶子节点
优点：
1、B+树的磁盘读写代价更低
2、B+树的查询效率更加稳定
3、B+树更有利于对数据库的扫描
8.Hash索引
优点：查询效率更高
缺点：
1、仅仅能满足"=","IN"，不能使用范围查询
2、无法被用来避免数据的排序操作
3、不能利用部分索引键查询
4、不能避免表扫描
5、遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
9.密集索引和稀疏索引的区别
1、密集索引文件中的每个搜索码值都对应一个索引值
2、稀疏索引文件只为索引码的某些值建立索引项
10.InnDB（mysql）
1、若一个主键被定义，该主键则作为密集索引
2、若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
4、若不满足以上，innodb内部会生成一个隐藏主键（密集索引）
5、非主键索引存储相关键位和其对应的主键值，包含两次查找
6、一个表只能有一个密集索引
11.索引额外问题之如何调优Sql
如何定位并优化慢查询Sql
根据慢日志定位慢查询sql
1、在navicat中输入show variables like '%quer%' 展示慢日志的配置项，slow_query_log是否开启慢日志记录，slow_query_log_file慢日志记录的位置，long_query_time sql执行多少秒为慢sql。
2、在navicat中输入show status like '%slow_quer%'，得到慢sql的总数。
3、在navicat中输入set global slow_query_log = on开启慢日志记录，set global long_query_time = 1设置sql超过1秒为慢sql。
使用explain等工具分析sql
1、在sql前加explain，就能分析sql为什么慢，explain分析中的关键字段。
type的优先级，前面的更快
extra的解析
修改sql或者尽量让sql走索引
联合索引的最左匹配原则的成因
1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 3 and b = 4 and c > 5 and d = 6，如果建立（a、b、c、d） 顺序的索引，d是用不到索引的，例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。
2、=和in可以乱序，比如a=1 and b=2 and c=3建立(a、b、c)索引可以任意顺序，mysql的查询优化器会帮你优化索引可以识别的形式。
3、创建联合索引时列的选择原则经常用的：
列优先（最左匹配原则）
离散度高的列优先（离散度高原则）
宽度小的列优先（最少空间原则）
列的离散性计算：count（distinct col）/ count(col)
例如：
id列一共9列都不重复 9/9 = 1
性别列一共9列只有（男或者女）两列 2/9 约等于0.2
离散性越高选择性越大（也就是不重复率高）
索引是建立得越多越好吗
1、数据量小的表不需要建立索引，建立会增加额外的索引开销
2、数据变更需要维护索引，因此更多的索引意味着更多的维护成本
3、更多的索引意味着也需要更多的空间
三、锁相关
1.MyISAM与InnoDB关于锁方面的区别的是什么？
1、MyISAM默认用的表级锁，不支持行级锁
2、InnoDB默认用的是行级锁，也支持表级锁
读锁也称为共享锁，多个查询sql可同时查，写锁也称为排他锁，先上了写锁其他操作都要等待。
MyISAM适合的场景
1、频繁执行全表count语句
2、对数据进行增删改的频率不高，查询非常频繁
3、没有事务
InnoDB适合的场景
1、数据增删改查都相当频繁
2、可靠性要求比较高，要求支持事务
数据库锁的分类
1、按锁的粒度划分，可分为表级锁，行级锁，页级锁
2、按锁级别划分，可分为共享锁、排他锁
3、按加锁方式划分，可分为自动锁、显示锁
4、按操作划分，可分为DML锁，DDL锁
5、按使用方式划分，可分为乐观锁，悲观锁
2.数据库事务的四大特性
ACID
1、原子性（Atomic）事务中的所有操作，要么一起成功，要么一起失败
2、一致性（Consistency）如果约定了a+b=10，无论a或b如何加减，结果永远都是a+b=10
3、隔离性（Isolation）一个事务的操作，不会影响另外一个事务的操作
4、持久性（Durability）事务一旦提交，要保证正确存储到相应设备
3.事务隔离级别以及各级别下的并发访问问题
事务并发访问引起的问题以及如何避免
1、更新丢失——mysql所有事务隔离在数据库层面均可避免
2、脏读——READ-COMMITTED事务隔离级别以上可避免（orcale数据库默认隔离级别）
3、不可重复读——REPEATABLE-READ事务隔离级别以上可避免（mysql数据库默认隔离级别，通过gap锁能保证幻读不出现，gap锁就是锁对有可能出现幻读的范围加锁，这个范围跟where条件有关）
4、幻读——SERIALIZABLE事务隔离级别可避免
InnoDB可重复读隔离级别下如何避免幻读
1、行锁
2、Gap锁（间隙锁）
如果where条件全部命中（条件必须是主键或唯一索引），则不会用Gap锁，只会加行级锁。
如果where条件部分命中或全部不命中，则会加Gap锁
Gap锁会用在非唯一索引或者不走索引的当前读中
非唯一索引，innodb中会通过db_row_id唯一标识一行数据，通过row_id来确定gap锁的锁定范围
不走索引或全部都不命中，会锁表，这种效率最低应尽量避免
当前读和快照读
1、当前读：select ... lock in share mode,select .... for update
2、当前读:update insert delete
3、快照读：不加锁的非阻塞读,不加锁的select（SERIALIZABLE隔离级别以下）
当前读就是拿到最新的数据，而且加锁避免其他事务对数据进行修改
网友评论