数据库基础知识点汇总（事务，索引）

三大范式

一范式：原子性（每一个属性都不能再分）不可分割的意思就按字面理解就是最小单位，不能再分成更小单位了。这个字段只能是一个值，不能被拆分成多个字段，否则的话，它就是可分割的，就不符合一范式。

二范式：依赖性\相关性（属性都要和实体相关，主键所代表的实体）
二范式就是要有主键，要求其他字段都依赖于主键。

三范式：所有属性都要直接和实体相关，不能传递相关性（学号–>学生–>课程就是传递相关性）
三范式就是要消除传递依赖，方便理解，可以看做是“消除冗余”。
消除冗余应该比较好理解一些，就是各种信息只在一个地方存储，不出现在多张表中。

索引

概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针，可以对表中的一系列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。
数据库中的表，数据，和索引之间的关系，类似于书架上的图书，书籍内容和书籍目录的关系。

使用场景

• 数据量较大，且经常对这些列进行条件查询。
• 对该数据库表的插入操作，及对这些列的修改操作频率较低。
• 磁盘空间充足。
  注意：如果非条件查询列，或者经常插入，修改操作，或者磁盘空间不足，不考虑创建索引。

索引的类型有哪些，他们的区别是什么？

依据创建规则：
* 唯一索引：数据列不允许重复，允许为null；一个表允许多个列创建唯一索引；例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

  * 主键索引：在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型，数据列不允许为空，不允许为null;一个表只能有一个主键；

  * 普通索引：就是简单的加速查询，没有唯一性的限制，允许未null;
  * 全文索引：对文本内容进行搜索，解决像 where name like "%word%"这类模糊查询效率比较低的问题；目前只有MyISAM引擎支持；

依据物理地址是否连续

  * 聚簇索引：在聚簇索引中，表中行的物理顺序与索引的物理顺序一致。一个表只能包含一个聚集索引， 即如果存在聚集索引，就不能再指定CLUSTERED 关键字。一个表中只能有一个聚集索引，但表中的每一列都可以有自己的非聚集索引。InnoDb使用的是聚簇索引
  * 非聚集索引：则表中行的物理顺序与键值的逻辑（索引）顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。Myisam使用的是非聚簇索引

依据底层数据结构
索引的类型有：B树索引，位图索引，函数索引等。
B树索引 主键和唯一性约束字段的B树索引，效率几乎和海量数据没有关系。键值重复率低的字段比较适合使用B树索引。
位图索引 键值重复率高的字段比较适合使用位图索引。 count、and、or、in这些特定的操作更适合位图索引。 DML操作比较多的表不适合使用位图索引。
复合索引 在where条件中必须带驱动列，复合索引才会使用。键值重复率低（DISTINCT数量多）的字段放在前面。

        * 一、索引的底层实现原理和优化

B+树，经过优化的B+树，主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引。
B树：层级较高

作用

正向

快速访问数据表中的特定信息，提高检索速度 创建唯一性索引，保证数据库表中每一行数据的唯一性。 加速表和表之间的连接
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

负面

创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

为数据表建立索引的原则有哪些？

• 在最频繁使用的、用以缩小查询范围的字段上建立索引。
• 在频繁使用的、需要排序的字段上建立索引

创建

• 创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。
• 唯一索引： creat unique index 索引名 on 表名（字段名：）
• 主键索引：无需显式创建
• creat index 索引名 on 表名（字段名：）
• 组合索引：creat index 索引名 on 表名（字段名A，字段名B）

查看

show index from 表名；

删除

drop index 索引名 on 表名；

注意事项

1:生产服务器不要直接执行添加索引，添加索引耗时。 操作从库

2:少查询，添加删除多的场景不适应

什么情况下不宜建立索引？

对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

什么情况下索引不会触发？

1:当对一个已经设置索引的列进行复制查询时
2:避免使用like模糊查询时，有可能不触发。以“%”开头的LIKE语句，模糊匹配
3:组合索引时要遵循最左匹配原则，（）
4: OR语句前后没有同时使用索引
5:数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
6:不要使用！=，<>会导致索引失效

事务

概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

特性ACID

1:原子性，要么全部成功，要么全部失败
2:隔离性：并发事务执行时，隔离问题
3:一致性：事务执行前后，保证一致性
4.持久性：事务执行完成之后，结果保留下来

并发事务存在的问题

• 脏读：事务A读取到事务B没提交的数据，事务B回滚了
  不可重复读：事务A使用相同的查询条件，读取到了不同的结果，过程中事务B修改了数据
  • 不可重复读VS幻读
    事务A将数据修改后，事务B新增了数据，结果和逾期不一致
    不可重复读侧重点是修改，幻读侧重点是描述新增或添加
    • 幻读解决方案
      1:mvcc，再读一遍
      2:gap间隙锁，加数据之前等待

作用

假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是 四十大盗的账户上就没有了增加的金额。
解决方案:使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败

使用

(1)开启事务:start transaction;
(2)执行多条SQL语句
(3)回滚或提交:rollback/commit;
rollback即是全部失败，commit即是全部成功。

start transaction;
– 阿里巴巴账户减少2000 update accout set money=money-2000 where name = ‘阿里巴巴’;
– 四十大盗账户增加2000 update accout set money=money+2000 where name = ‘四十大盗’; commit;

事务的隔离级别

1:读未提交: 脏读 不可重复读 幻读

2:读已提交 :不可重复读 幻读

3:可重复读:（默认） 幻读

4:串行化 都可以解决，依次解决，效率不高

为什么mysql默认可重复读

主要原因是语句级的Binlog。可重复读能提供SQL语句的写可串行化，保证了主从一致。

JDBC编程

1:装载驱动
2:建立连接
3:创建语句
4:使用语句

如何优化MySql的性能？

1:查询较多的列上加索引
2:开启慢查询日志，定位比较慢的sql，精准修改
3:尽量使用主键索引
4:分表（垂直分割）分库（水平分割）
5:提高数据库的硬件配置，更换磁盘，更大的内存

如何开启慢查询？

修改配置文件my.confirm,在[mysqld]下方加入
slow_query_log=ON
slow_query_log_file=路径
long_query_time=1;

主键索引 VS 普通索引

1:主键索引不用显式创建
2:主键索引不能删除
3:主键索引查询更快，普通索引要要回表查询