数据库
2019-03-07 10:49:53 0 举报AI智能生成
数据库相关，根据多人博客，面经和相关书籍总结
作者其他创作
大纲/内容
索引
数据结构
B+树
聚集索引只有叶子节点存放数据
非聚集索引存放：非聚集索引的key+主键值
强烈建议表都要有主键，且以数字型为主键
哈希索引
基于哈希表实现，每列计算一个哈希码，精确匹配才会表现出高效性
哈希索引只存储哈希值和行指针
哈希索引场景
Mysql中只有在memory引擎显示支持哈希索引
当Innodb注意到某些索引值使用非常频繁时，innodb会
在内存中基于B-tree索引之上再建立一个哈希索引
在内存中基于B-tree索引之上再建立一个哈希索引
这是完全自动、内部的行为，用户无法配置或者设置，可以关闭此功能。
优点
共同优点
避免全表扫描
B+树按照顺序存储数据，避免排序，适合order by和group by操作
索引存储存储了实际值，有效利用覆盖索引
聚集索引优点：避免数据插入操作集中发生于表的最后一个数据页
非聚集索引优点：有些查询可以不用访问数据项
缺点
非聚集索引缺点
空间成本：索引占用空间，表越大，索引占用空间越大
更新成本：对于insert、update、delete操作需要同步更新索引，性能损失
高性能索引策略
聚集索引：一种数据存储方式
有主键：主键为聚集索引
无主键：第一个不允许为null的唯一索引
无主键，无满足非空约束的唯一索引：使用innodb内置的rowid
非聚集索引
单列索引：使用表中某一字段作为索引
多列索引：根据多个列创建索引，索引列的顺序非常重要，索引只能高效使用第一个列
前缀索引：选取某一个字段的前面部分前缀创建索引
覆盖索引：如果一个索引包含（或者说覆盖）所以需要查询的字段的值
使用索引扫描来做排序
mysql死锁
引擎对比
InnoDB中，锁是逐步获得的，就有可能造成死锁
MyISAM不会产生死锁：对于某条sql语句涉及的资源，myisam要么一次性获取全部锁，要么全不获取
进程线程中，各进程会握住其它进程想要的部分资源
进程线程中，各进程会握住其它进程想要的部分资源
死锁原因
系统资源不足
资源分配不当
进程推进顺序非法
减少死锁的策略
按同一顺序访问对象
保持事务简短并在一个批处理中
避免事务中的用户交互
使用低隔离级别
死锁及解决
情况1
场景：用户1锁住表A后企图访问表B；用户2锁住表B后企图访问表A
解决：数据库的多表操作，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源
情况2
场景：用户1查询一条记录后修改这条记录，共享锁企图上升为独占锁；
用户2企图修改该记录，在用户1升级前先一步等待独占锁；
比如：用户多次快速点击同一按钮，很容易引起这种情况的死锁
用户2企图修改该记录，在用户1升级前先一步等待独占锁；
比如：用户多次快速点击同一按钮，很容易引起这种情况的死锁
解决
对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作
使用乐观锁——为数据表增加version字段
情况3
场景：当表中的数据量非常庞大而索引建的过少或不合适时，全表扫描经常发生，
应用系统会越来越慢，最终发生阻塞或死锁；
执行一条不满足条件的update语句，会进行全表扫描，行锁升级为表锁，多个这
样的事务发生时可能会造成死锁和阻塞。
应用系统会越来越慢，最终发生阻塞或死锁；
执行一条不满足条件的update语句，会进行全表扫描，行锁升级为表锁，多个这
样的事务发生时可能会造成死锁和阻塞。
解决
SQL语句中不要使用太复杂的关联多表的查询
使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化
redis持久化
持久化：将数据放到断电后数据不会丢失的设备中，即硬盘
主动
client 使用save或bgsave命令通知redis做一次快照持久化；
由于redis是用一个主线程来处理所有 client的请求，save操作是在主线程中保存快照；
这种方式会阻塞所有client请求，不推荐使用
由于redis是用一个主线程来处理所有 client的请求，save操作是在主线程中保存快照；
这种方式会阻塞所有client请求，不推荐使用
自动
RDB持久化
（半持久化模式）
（半持久化模式）
总结：通过异步方式保存到磁盘上，快照持久化
策略
①指定时间间隔内将内存中的数据集快照写入磁盘
②n秒内如果超过m个key被修改就自动做快照
过程
①redis调用fork（内存的瞬时快照），产生子进程；
②os的写时复制（copy on write）机制：
父进程继续处理client请求，在内存中“待修改页面的副本”上修改；
子进程将fork时刻的内存快照写入到临时文件，写入完毕后替换原来的RDB文件，子进程退出
父进程继续处理client请求，在内存中“待修改页面的副本”上修改；
子进程将fork时刻的内存快照写入到临时文件，写入完毕后替换原来的RDB文件，子进程退出
AOF持久化
（全持久化模式）
（全持久化模式）
总结：把每一次数据变化都写入到一个append only file(aof)中
过程
①redis会将每一个收到的写命令都通过write函数追加到文件中(默认是 appendonly.aof)
②当redis重启时会通过重新执行文件中保存的写命令来在内存中重建整个数据库的内容
sql优化
查询性能的优化
不要使用select *
不要查询多余的行，需要多少查询多少，使用limit限制行数
切分查询，对于大规模的delete、insert、update分片执行，避免长时间加锁
分解关联查询，改成多个查询语句，高效利用查询缓存
尽可能使用索引覆盖
利用IN(...)来代替OR语句，因为IN语句时间复杂度是O(logn)，但是OR语句是O(n)
尽可能得到索引条件，否则就会回表查询
特定查询语句的优化
Count的优化
关联查询优化：确保ON语句上的列有索引，尽可能group by和order by上只涉及一个有索引的列
尽量少使用子查询、因为会产生临时表
group by和distinct查询优化----使用索隐列
limit分页的时候限定查询记录并利用最好使用索引覆盖，避免回表查询
char vs varchar
char
长度
1~255个字符(不同编码的最大可用字节数不同)的定长串
创建时指定长度，如果不够，用空格补全；未指定长度，mysql默认char(1)
特点：存储效率高，用空间换时间
使用场景
存储很短的信息
固定长度，且频繁更新的列
varchar
长度
4.0版本以下，varchar(20)，指的是20字节
5.0版本以上，varchar(20)，指的是20字符
varchar字段，衍生
NULL标识位：N个变长字段，m=floor((N+7)/8)个bytes存储null标识位
长度标识位
某个varchar字段<=255bytes，需要1byte标识
某个varchar字段>255bytes，需要2bytes标识
所有字段(列)的长度+所有varchar字段的长度标识+NULL标识位<=65535bytes
特点：只占用必要的存储空间
使用场景
更新较少的列
减少空间占用和减少磁盘I/O
职业：硕士
0 条评论
下一页
