目录
链接一下目录方便查找
数据库
1.MySQL的索引
索引就是帮助MySQL高效获取数据排好序的数据结构
索引数据结构：
- 二叉树
- 红黑树
- hash表
- B树
1、单指索引一个索引只包含单个列，一个表可以有多个单列索引
2、唯一索引索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
3、主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
4、组合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
5、全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
存储引擎级别是表
1.什么情况要创建索引?
主键自动建立唯一索引
频繁作查询条件的字段要创建索引
查询中与其他表关联的字段,外键关系建立索引
查询中排序的字段 order by
查询中统计或者分组的段 group by
2.什么情况不创建索引
频繁更新的字段不适给创建索引
where条件里用不到的字段不创建索引
表记录太少
数据重复且分布平均的表字段不适合创建索引
3.单值索引和组合索引的选择问题?
高并发建议组合索引(过滤性最好的字段排在前面)
4.避免索引失效
最佳左前缀法则
不要在索引上做任何操作,否则会失效
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只查询索引列),减少使用select *
!= <>无法使用索引会导致全表扫描
is null ,is not null也无法使用索引
like ‘%abc…’ 索引失效会导致全表扫描
字符串不加单引号索引失效
用or,索引失效，如果or前后都是索引列不会失效
5.查询优化
①小表驱动大表
②order by子句尽量避免FileSort ,order by子句使用索引最左前列,或者where子句与order by子句条件列组合满足索引最左前列
③group by:先排序后分组，遵照最佳左前缀，where高于having, 尽在where中限定条件
2.存储引擎
MyISAM和InnoDB区别
myisam:表锁，只缓存索引
innoDB:支持主外键，支持事务，行锁，缓存索引和真实数据
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。
大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。
两者的对比：
- 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
- 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
- 是否支持外键： MyISAM不支持，而InnoDB支持。
- 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在
READ COMMITTED和REPEATABLE READ两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。
3.索引
MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。
-
MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
-
InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
通俗来讲,就是如果用UUID,就会导致使用辅助索引的时候,底部的data是uuid,比较浪费磁盘空间;并且,如果不用自增的id作为主键的话,会频繁的分裂数
4.为什么InnoDB底层使用B树而不使用Hash？
hash是直接存地址值，在某种程度上来说查找速度比B树还要快。但是如果查范围，例如id>20，就不行了，而B+树只要找到20，然后往后拿数据就行
5.为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?
在InnoDB中，他的底层是用B+实现的，必须要有查找索引，如果没有，底层会自动帮你从左往右进行每列查找，将没有重复的列自动作为索引，如果没有不重复的列，就要自动创建一列。首先，推荐整形的主键是因为在索引查找中，能够实现较快的查找，然后就是因为B+树的结构了，本来按顺序自增，就是在后面加，很少发生树结构的改变，但是如果直接插入，会导致树结构改变，开销很大，影响insert语句。id用来做聚簇索引，插入的时候可以顺序插入，如果随机数，会导致也分裂
6.联合索引的底层存储结构
底层使用B+树，排序使用从左往右排序
如果联合索引’a’,‘b’,‘c’，就要只能查带’a’的
从底层数据结构原理来解释，只有有第一个字段在，才能查找叶子结点的数据，不然在宏观上来看，是没有排序过的
7.事务
事务的ACID
A原子性:事务是逻辑上的一组操作，要么都执行，要么都不执行。
C一致性:针对数据一致性来说,一组sql执行之前数据必须准确,执行之后也必须准确.
I隔离性:多个事务在执行的时候不能相互干扰
D持久性.
8.sql执行过长的时间，如何优化
9.创建索引原则
10.事务隔离级别有哪些?MySQL的默认隔离级别是?
SQL 标准定义了四个隔离级别：
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）
总结：
1.读未提交：脏读、不可重复读、幻读
2.读已提交：不可重复度、幻读
3.可重复读：幻读
4.串行化：隔离级别最高
脏读：脏读是读到了bai别的事务回滚前的脏数据。比如事务B执行过程中修zhi改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。
也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。
不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。
也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。
幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读
11.谈谈你对MVCC的理解
MVCC就是多版本并发控制，通过行级锁的变种，避免很多不必要情况下发生加锁，减少开销。其基本思想是为每次事务生成一个新版本的数据，在读数据时，选择不同版本的数据即可实现对事务结果的完整性读取。同时MVCC只支持读取已提交和可重复提交的隔离级别。
a.是什么? MVCC即眵个不同版本的数据实现并发控制的技术，基本思想是为每次务生成一个新版本的数据， 在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取。
b.作用?提高并发的读写性能
操作的时候会生成事务id
①每条记录都会保存两个隐藏列: trx_ id (事务id)和roll pointer(回滚指针)2个字段
②每次操作都会生成一条undo log日志,回滚指针指向前一条记录
查询的时候会读取出ReadView:[未提交的事务id]数组+最大事务id,并根据readview从undo log日志中最新的记录依次往下找
<1>从最新记录开始找:
如果当前记录:事务id<未提交事务的最小id，则可读
如果当前记录:最小id< =事务id< =事务的最大id,则判断事务id是否在未提交事务id的数组中，若在则不可读(只有自己可读)
如果当前记录:事务id>事务的最大id,则不可读
<2>可重复读返回的read-view是第一记录的，记提交每次查询都返回新的read-view
MVCC只针对读E提交和可重复读,如果读未提交,每次查询都取最新的记录即可。
innodb存储引擎，会在每行数据的最后加两个隐藏列，一个保存行的创建时间，一个保存行的删除时间，但是这儿存放的不是时间，而是事务id，事务id是mysql自己维护的自增的，全局唯—。
事务闻，在mysql内部是全局唯一递增的，事务 id=1，事务如=2，事务id=3
| id | name | 创建事务id | 删除事务id |
|---|---|---|---|
| 1 | 张三 | 120 | 122 |
| 2 | 李四 | 119 | 空 |
| 2 | 小李四 | 122 | 空 |
事务d=121的事务，查询 id=1的这一行的时候，一定会找到创建事务id<=当前事务d的那一行，select * from table where id=1，就可以查到上面那一行
事务=122的事务，将id=1的这一行给删除了,此时就会将id=1的行的册删除事务d设置成122
事务d=121的事务，再次查询id=1的那一行，能查到吗?能查到，要求创建事务id<=当前事务id,当前事务id<删除事务id
事务id=121的事务查询id=2的哪一行,查到name=李四的id
事务id=122的事务,将id=2那一行的name改成name=小李四
事务id=121的事务再次查询id=2的那一行,查出name是等于李四的,是因为保存了事务快照,创建事务id<=当前事务id,当前事务id<删除事务id
12.MySQL为什么使用B+树做索引
1.全部遍历，时间复杂度O(N)
2.hash，优点：增删改查快，O（1），缺点，不能进行范围查询
3.二叉树：O(log2N)深度为N的结点，如果数据极端，会形成一条链表
4.平衡二叉树（AVL）：数据越大树越高，IO收到影响
5.B树：每个结点可以有多个子节点，可以控制书高，IO次数比平衡二叉树少，但是数据存储在索引上，无法加载大量数据
6.B+树：每个结点只存储索引，数据都放在叶子结点，叶子结点链表相连。最好使用整形递增索引，保证连接顺序
13.分表、分库、分区
MySQL数据量过大，考虑按业务进行垂直拆分
如果单表容量超过500W就要水平拆分
分库，通常都是一主多从，如果并发量大，就需要多个主库，进行MySQL集群访问
分区：分区查询使用 SHOW VARIABLES LIKE ‘%partition%’
range 分区，list分区
14.行表锁
myisam用的少了,他在读的时候回自动加共享表锁,在写的时候回自动加独占表锁别人不能读也不能写
1.查询锁
mysql>show open tables
行锁分为共享锁和排它锁
2.读锁
共享锁，lock table 表名 read
多个书屋都可以加共享锁度同一行数据,大那是别的事务不能写这行数据
3.写锁
独占锁，lock table 表名 write
也叫排它锁,就是一个事务可以写这行数据,但是别的事务只能读不能写
insert,update,delete的时候innodb会自动给那一行加上行级排它锁
4.表锁偏读，行锁偏写
5.表锁分析
表锁有意向共享锁和意向排它锁,就是在家共享行锁和排他行锁的时候innodb会自动加上
①读锁示例: session1加表1的读锁
session2可读,不能改(排队等待)
session1不可改，不可读其他表
②写锁示例: session1加表1的写锁
session1可读写操作
session2不能读(排队等待)
③如何分析表锁锭
sq|: show status like ‘table%’;
table_ locks_ immediate:产生表级锁定的次数
table_ locks_waited:出现表级争用而发生等待的次数
总结:读锁会阻塞写但不会阻塞读,写锁会把读写都阻塞
15.MySQL的主从复制
slave会从ma ster读取binlog来进行数据同步，slave将master的binlog拷贝到它的中继日志。 mysql的复制是异步且串行化的。
16.索引的最左匹配原则
create index(a,b,c)
1)全匹配
就是完全使用联合索引
2)最左部分匹配
用的从左往右的索引,用的最左边的几个索引
3)最左前缀中间某个值没匹配
比如a,c作为索引,a,c匹配了,b没有匹配,那么只有a用了索引
不要在索引上做任何操作,否则会失效
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只查询索引列),减少使用select *
!= <>无法使用索引会导致全表扫描
is null ,is not null也无法使用索引
like ‘%abc…’ 索引失效会导致全表扫描
字符串不加单引号索引失效
用or,索引失效，如果or前后都是索引列不会失效
sql调优
explain select * from table，就ok了
table / type ] possible_keys| koey | key _len ] ref |rows| Extra
table:哪个表
type:这个很重要，是说类型,all《全表扫描)， const《读常量，最多一条记录匹配》, eg_ref《走主键,一般就最多一条记录匹配》，index《扫描全部索引)，range (扫描部分索引>possible_keys:显示可能使用的素引
key:实际使用的索引
key_len:使用索引的长度ref:联合素引的那一歹惟防用了rows:一共扫描和返回了多少行
extra: using filesort《需要额外进行排序)，using temporary ( mysql构建了临时表，比如排序的时候>，using vhere 《就是对索引出来的数据再次根据 where来过滤出了结果》
17.B+树比B树的优点
1.单一结点不需要存储数据,能够存储更多的结点,让树更加矮胖
2.查询效率稳定,都是查询到叶子结点,而B树最好是根节点最坏是叶子结点
3.叶子结点形成有序的链表,便于范围查询
18.数据库设计的三大范式和反范式
数据库的三大范式
1.列不可分,即保持列的原子性,比如要常常访问一个地址中的城市,就要将这个地址进行拆分
2.属性依赖于主键,针对联合主键而言,表中的每一列都要依赖主键,而不是部分依赖主键
3.属性和主键不能间接相关,数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
反范式设计
就是在第三范式的情况下,传递依赖,用冗余数据来换来搜索效率
19.左连接、右连接和内连接的区别
左连接：以左表为主
select a.,b. from a left join b on a.b_id = b.id;
右连接：以右表为主
select a.,b. from a right join b on a.b_id = b.id;
内连接：只列出两张表关联查询符合条件的记录
select a.,b. from a inner join b on a.b_id = b.id;
20.如何解决SQL注入
SQL注入有statement注入和mybatis注入
配合or 1=1 和注释 – 会注入
解决方法：使用PreparedStatement进行预编译；检查字符串，过滤非法字符；正则表达式过滤参数
mybatis 中使用${}符号会造成sql注入,有时候是行名或者动态表名,这时候就需要进行过滤来进行防止sql注入
21.事务的特点
ACID
原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持久性（ Durability ）
原子性：
事务是数据库的逻辑工作单位，事务中包含的各操作要么都完成，要么都不完成
(要么一起成功，要么一起失败)
一致性：
事务一致性是指数据库中的数据在事务操作前后都必须满足业务规则约束。
比如A转账给B，那么转账前后，AB的账户总金额应该是一致的。
隔离性：
一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
(设置不同的隔离级别，互相干扰的程度会不同)
持久性：
事务一旦提交，结果便是永久性的。即使发生宕机，仍然可以依靠事务日志完成数据的持久化。
日志包括回滚日志（undo）和重做日志(redo)，当我们通过事务修改数据时，首先会将数据库变化的信息记录到重做日志中，然后再对数据库中的数据进行修改。这样即使数据库系统发生奔溃，我们还可以通过重做日志进行数据恢复。
22.回滚日志,重做日志和二进制日志的区别
回滚日志(undo log):
记录事务发生之前上一个版本的状态,可应用于回滚,也可提供多版本并发控制下的读(MVCC),即非锁定读
事务开始之前将当前版本数据生成undolog
重做日志(redo log):
用于确保事务的持久性,防止在故障发生的时间点有脏页未写入磁盘.
在事务开始的时候就开始写入
二进制日志(bin log):
用于主从复制,利用主库的binlog进行主从同步和数据库基于时间点的还原
事务提交的时候才写入,写入的是具体的sql语句和其反sql语句(例如delete和insert)
23.谈谈你对MVCC的理解
MVCC就是多版本并发控制，通过行级锁的变种，避免很多不必要情况下发生加锁，减少开销。其基本思想是为每次事务生成一个新版本的数据，在读数据时，选择不同版本的数据即可实现对事务结果的完整性读取。同时MVCC只支持读取已提交和可重复提交的隔离级别。
a.是什么? MVCC即眵个不同版本的数据实现并发控制的技术，基本思想是为每次务生成一个新版本的数据， 在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取。
b.作用?提高并发的读写性能
操作的时候会生成事务id
①每条记录都会保存两个隐藏列: trx_ id (事务id)和roll pointer(回滚指针)2个字段
②每次操作都会生成一条undo log日志,回滚指针指向前一条记录
查询的时候会读取出ReadView:[未提交的事务id]数组+最大事务id,并根据readview从undo log日志中最新的记录依次往下找
<1>从最新记录开始找:
如果当前记录:事务id<未提交事务的最小id，则可读
如果当前记录:最小id< =事务id< =事务的最大id,则判断事务id是否在未提交事务id的数组中，若在则不可读(只有自己可读)
如果当前记录:事务id>事务的最大id,则不可读
<2>可重复读返回的read-view是第一记录的，记提交每次查询都返回新的read-view
MVCC只针对读E提交和可重复读,如果读未提交,每次查询都取最新的记录即可。
908
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
