https://blog.csdn.net/moakun/article/details/79927830 B树
https://blog.csdn.net/qq_26222859/article/details/80631121 B+数
1、当我们利用索引查询的时候，能把整个索引加载到内存吗？显然不能，能做的只有逐一加载每一个磁盘页，这里的磁盘对应着索引树的节点。所以二叉树的IO次数是树高。
2、相比磁盘IO的速度，内存中的比较耗时几乎可以忽略不计。所以二叉树的深度问题导致他的耗时。B数变深度为宽度，IO次数减少
3、mySql基于B+数和hash
4、B+数所有数据（卫星数据）保存在叶子节点，中间节点只用来索引，这就导致相同大小的磁盘片可以容纳更多的节点数据，这样，B+数会更矮胖。B数的元素不是都在叶子节点，导致查询的效率不稳定。子节点数据左闭右开。每一个叶子节点都带有指向下一个节点的指针，形成有序链表。需要补充的是，在数据库的聚集索引（Clustered Index）（密集索引）中，叶子节点直接包含卫星 数据。在非聚集索引（NonClustered Index）（稀疏）中，叶子节点带有指向卫星数据的指针。
综上：B+相对于B的优势有
A：IO更少
B:查询性能稳定
C:范围查询更快（B+基于链表结构,B则需要反复遍历）
B+和B自平衡
5、哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。但是只能查"=“和"in”
6、Bitmap位图索引只适用于值为固定数据的，比如（性别）
7、对MyISAM 来说, 主键索引和其他索引没有任何区别, 都是稀疏索引 , 表数据存储在独立的地方, 表数据和索引的分开的, 索引用地址指向表数据； innodb包含（必须有）一个密集索引和辅助索引（稀疏索引），数据存放在密集索引的子节点中。辅助索引存放的是相关键位和其对应的主键值，所以辅助索引查找包含两次：先找到主键，再根据主键查找具体数据。密集索引的选取规则为如果有主键，该主键则作为密集索引；如没有主键，该表的第一个唯一非空索引；如上面两个都不成立，innodb内部会生成一个隐藏主键作为密集索引
8、外键的不一定是主键，但要保证唯一性。作用是，当其所属的主键数据被修改或删除后，对应的外键的值也会被删除或修改。
慢查询大概思路
- 根据日志定位慢查询sql
show variables like "%query%"结果中几个说明：
slow_query_log：是否开启慢日志记录
slow_query_log_file：慢查询日志位置
long_query_time：超过多久的时间算慢查询
修改上面的值用set,例如set GLOBAL slow_query_log=ON，有些设置需要重连客户端。这样的设置在数据库服务器重启后会失效，如果想要永久生效，修改配置文件。 - 使用explain等工具分析sql
explain sql查询结果中字段的解释：
type:index/all是全表扫描，需要优化
extra:出现using fileSort/using temporary，需要优化 - 修改sql或者尽量让sql走索引
10、索引是建立的越多越好吗
数据量小的表不需要建立索引，建立会增加人外的索引开销
数据变更需要维护索引，因此更多的索引意味着更多的维护成本
更多的索引意味着也需要更多的空间
联合索引的最左匹配原则的成因
假设联合索引为A、B、C
最左匹配原则：https://www.cnblogs.com/lanqi/p/10282279.html
为什么会这样？
mysql会先对联合索引的第一个字段（a）进行排序，再在此基础上对第二个元素排序。类似于order by a,b…,所以直接查b并不是有序的
MyIsam与 InnoDB关于锁的一些区别
| 级别 | 加共享锁方式 | 加排它锁方式 | 锁释放方式 | 是否支持事物 | 事物与锁的关系 | |
|---|---|---|---|---|---|---|
| MyISAM | 只支持表级锁 | 1、select * from table； | delete;update;add;select for update | 执行完自动释放 | 否 | × |
| InnoDB | 默认支持行级锁，也支持表级锁（不走索引的时候） | SELECT * from tablelock in share mode; | delete;update;add;select for update | 二段锁，对事物中的一批加锁，在commit后统一解锁 | 是 | 同一事物中不存在锁住的概念，锁只在不同事物体现 |
其他一些语句，如下
lock TABLES person_info_myisam read;--加读锁
UNLOCK TABLES;--解除锁
show VARIABLES like 'autocommit'--查看是否自动提交
set autocommit=0--关闭自动提交，仅针对当前session
commit;--提交
begin TRANSACTION--开启事物，当然只对InnoDB有作用
共享锁和排它锁的关系如下
| 共享锁 | 排它锁 | |
|---|---|---|
| 共享锁 | √ | × |
| 排它锁 | × | × |
数据库锁的分类
| 按锁的粒度 | 按锁的级别 | 按加锁的方式 | 按操作方式 | 按使用方式 |
|---|---|---|---|---|
| 表级、行级、页级 | 共享、排他 | 自动、显示（lock in share这种） | DML(增删改查)、DDL（表结构) | 乐观锁、悲观锁 |
悲观锁与乐观锁
- 悲观锁：每次在拿数据的时候都会上锁，例如前面的selec * from table lock in share;
- 乐观锁：先操作，然后跟标示位比较看有没有变， 比如
select version from test_innodb where id=2;–假设结果为0，这里version就是标识位，一般也可以用时间戳作为标识位
update test_innodb set money=123,version=0+1 where version=0 and id=2;加入在select之后有别的session对此条数据进行了修改，那么update就会不成功因为id=2的version已经被另一个update修改为了1，不存在id=2,version=0的数据
数据库事务
ACID
| 原子性(Atomic) | 一致性(Consistency) | 隔离性(Isolation) | 持久性(Durability) |
|---|---|---|---|
| 事物包含的所有操作要么全部成功，要么全部失败 | 数据完整性，例如转账A和B的钱总数为2000，那么不管他们之间怎么转账，总数永远是2000 | 一个事物的执行不影响别的事物的执行 | 事物一旦被提交，要保证更新不被丢失。 |
事物并发引起的问题及解决方案
navicat验证用到的一些语句
select @@transaction_isolation;--查询事物隔离级别(高版本mysql）
select @@tx_isolation;--查询事物隔离级别（低版本mysql)
set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED--设置事物隔离级别
| 更新丢失 | 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|---|
| 问题 | AB同时开启事务，A取出数据后值为100，B对这条数据更新为120并提交，此时A也更新了数据值变为90，此时对B来说更新丢失了，理论上A的更新应该基于B的120。事实上Mysql数据库会在事务里面默认添加写锁，上面的现象是没法重现的 | 一个事物读取到另一个事物未提交的更新数据。比如A事务读取到B事务未提交的 账户余额为900，但是之后B事务回滚了，账户余额变成了开始的1000，但是A还是以900在操作，假设这是一个转账功能，A存入200，数据库为900+200=1000；然而实际上B并没有取100，账户余额应该为1200才对 | 事务A多次读取同一数据，事务B在事务A多次读取的过程中更新并提交，导 致事务A多次读取的数据不一致。例如A事物查出余额为1600，B事物将余额改为2000，A又读了一次余额，变成了2000，很难确保当前的2000不会再变 | 事物A对全表上锁，事物B能新增一条数据，提交后，会影响事物A发现多出一条数据（不可重复读体现的是对同一条数据，是可以对操作的那条数据上锁的） |
| 解决方案 | mySql所有事务隔离级别在数据库层面上均可避免 | RC(READ-COMMITTED,oracle默认级别)及以上隔离级别可避免,set session TRANSACTION ISOLATION LEVEL READ COMMITTED | RR(REPEATABLE-READ,mySql 默认级别,事务隔离级别及以上可避免)。set session TRANSACTION ISOLATION LEVEL REPEATABLE READ。解决后A读取到账户余额1600，B在1600的基础上 加400并提交余额变为2000，此时A再读一次，还是1600，并且此时balance=balance+200后的值依然是在B的2000的基础上操作的，也就是会变成2200。而RC读取到的一直在变，虽然update是基于B提交后的 | 设置为最高隔离级别，串行，set session TRANSACTION ISOLATION LEVEL SERIALIZABLE。此级别下所有的操作都会加锁，即便是innoDb的select。解决后体现为A select * from table后会整个上锁，事物B无法再插入数据 |
| 记忆方式 | 提交了才读 | 提交了也不读 | 整个上锁 |
| 事物隔离级别 | 更新丢失 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读 | 避免 | 发生 | 发生 | 发生 |
| 已提交读 | 避免 | 避免 | 发生 | 发生 |
| 可重复读 | 避免 | 避免 | 避免 | 发生(InnoDb RR级别下的当前读，即加了锁的增删改查操作，具体原因下面有分析) |
| 串行化 | 避免 | 避免 | 避免 | 避免 |
当前读和快照读
| 当前读 | 快照读 |
|---|---|
| select … lock in share mode; select … for update; insert; update ;delete; 即加锁。update、insert、delete实际上都是根据条件逐条查出符合条件的数据，并加锁，然后再做相应的增删改，所以增删改也是当前读。顾名思义，永远读数据库当前最新的数据 | 不加锁的非阻塞读（注意串行隔离级别下的select是加锁的），select。顾名思义，从已经读过的快照中读取，这也是RR级别下实现可重复读的原因。读取的是什么快照，取决于快照读的时机，第一次肯定是从库里读的，读了之后放在read view文件里，下次从该文件读。而低于RR的级别，快照读都是从库里读，所以没法实现可重复读 |
InnoDB可重复读(RR)隔离级别下避免幻读的原因
事物对数据加了next-key锁，next-key锁=行锁+GAP锁，gap锁只有在RR及串行锁下有
GAP锁区间的定义：GAP锁区间为对已经有的数据，按左开右闭分为多个GAP,比如某列有6，9,11三个数，则GAP锁区间为（负无穷，6],(6,9],(9,11],(11,正无穷),如果此时进行当前读select * from ta where id=9 lock in share mode,则(6,9],(9,11]区间的值被上锁
| where条件走主键或唯一索引 | where条件走普通索引 | where条件不走索引 | |
|---|---|---|---|
| where条件被全部命中 | 不会用GAP锁 ,只会加行锁 | 按锁区间 加GAP锁 | 全表加锁，锁区间为（正无穷，负无穷） |
| where条件未被全部命中 | 按锁区间 加GAP锁 | 按锁区间 加GAP锁 | 全表加锁，锁区间为（正无穷，负无穷） |
sql优化问题
| 服务层面 | 系统层面 | 数据库层面 | 代码层面 | 其他 | |
|---|---|---|---|---|---|
| 配置mysql性能优化参数 | 优化数据表结构、字段类型、字段索引、分表，分库、读写分离等等 | 优化SQL语句，合理使用字段索引 | 使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。 | A、减少数据库操作次数，尽量使用数据库访问驱动的批处理方法；B、不常使用的数据迁移备份，避免每次都在海量数据中去检索；C、提升数据库服务器硬件配置，或者搭建数据库集群；D、编程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查询；正则表达式过滤（非法字符串过滤）； | |
| A、范式优化： 比如消除冗余（节省空间。。）；B、反范式优化：比如适当加冗余等（减少join）；C、拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。 | A、避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；B、避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null；可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0；C、用 exists 代替 in 是一个好的选择；D、用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤；E、当只要一行数据时使用 LIMIT 1，查到就返回了；F、%apple%不走索引；F、尽量不要用复杂的join和子查询，非要用的话，用来 Join 的字段，应该是相同的类型的，并建立索引；G、UNION ALL 要比 UNION 快很多;H、有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性， |
那在设计数据库时就去掉外键
sql优化利器
| Explain | Profiling |
|---|
数据库三范式是什么
| 第一范式（1NF） | 第二范式（2NF） | 第三范式（3NF） |
|---|---|---|
| 每一列只有一个值 | 要求数据库表中的每个实例或行必须可以被惟一地区分 | 每一个表都不包含其他表已经包含的非主关键字信息 |
14、事务的传播与隔离
- https://blog.csdn.net/zjj972326230/article/details/79003318
- spring事务本质上使用数据库事务，而数据库事务本质上使用数据库锁，所以spring事务本质上使用数据库锁，开启spring事务意味着使用数据库锁；
- spring事务只有在方法执行过程中出现异常才会回滚，并且只回滚数据库相关的操作；
- Nested嵌套事务回滚或提交不会导致外部事务回滚或提交，但外部事务回滚将导致嵌套事务回滚，而 RequiresNew由于都是全新的事务，所以之间是无关联的；
- 只要被catch了的异常，不管有没有rollbackfor，当前事务都不会回滚，注意是当前事务，针对事务传播需要区分好是不是当前事务。
17、mysql一般的机械硬盘300qps,固态硬盘700qps左右
18、spring的事务支持是通过ThreadLocal存放某个线程对应的connection,所以对于不同的数据库或者是事务方法中有多线程的，事务不起作用
扫一扫
359
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
