数据库——知识点总结

索引

1. 什么是索引？

数据库用于提升查找速度的一种手段。

2. 索引分类

• B+树索引
  传统意义上的索引，最常用和最有效的。
• 哈希索引
  一种自适应，数据库根据表的使用情况自动生成。
• 全文索引
  用于实现关键词的搜索，根据空格来分词，不支持没有空格的语言。
• Rtree索引
  MySQL上使用很少，范围查找很快。

索引知识点详细参考

3.MyISAM索引实现与InnoDB的不同点

索引

• 在MyISAM中，索引文件和数据文件分开存放，不管是主键索引还是辅助索引，都属于非聚集索引。其主键索引和辅助索引的区别在于，主键的Key值要是唯一的，而辅助索引可以不唯一。
• 而在InnoDB中，数据和主键索引是放在一起的，辅助索引的数据也中记录的是主键，查辅助索引的时候需要先查到主键，然后通过主键索引查到数据。所以，InnoDB的主键索引占用空间比较大。

锁的粒度

外键

主键

事务

适用场景

• MyISAM适合：
  做很多count 的计算，因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。
  插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；
  没有事务。

• InnoDB适合：
  可靠性要求比较高，或者要求事务；
  表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
  如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；
  DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；
  LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

4.索引的优点

• 可以提高数据查询的速度。
• 通过创建唯一索引，可以保证数据库表中对应数据的唯一性。
• 可以加速表与表之间的连接，在实现数据的参考完整性上有特别的意义。
• 在使用分组和排序子句进行检索的时候，可以减少查询中分组和排序的时间。
• 在使用索引查询的时候，使用优化隐藏器，提高系统性能。

5.索引的缺点

• 创建索引需要耗费时间，数据量越大，开销越大。
• 索引本身需要占据内存空间，如果是聚集索引，占用空间就更大。
• 在表中的数据发生变动的时候就需要维护索引，这样表的数据维护时间也变长。

6.需要索引的情况

• 在经常需要搜索的列上，索引可以加快搜索速度。
• 作为主键的列上，索引确保列数据的唯一性和表中数据的排列结构。
• 在经常用在连接的列上，这些列主要是外键，索引可以加快连接速度。
• 在经常需要范围搜索的列上，因为索引是排序的，所以可以加快速度。
• 在经常需要排序的列上。
• 在经常使用where语句的列上。

7.那些情况不要加索引

• 对于选择性很低的列，就不要加入索引，例如性别。
• 查询中很少使用的或者参考的列。
• 数据量很大的列，例如text，image和bit数据类型。
• 要求修改性能远远大于检索性能时，因为添加索引后会降低修改性能。索引主要是提高了检索应能。
• 没有满足6的条件时。

8.聚集索引和非聚集索引的区别

根本区别：
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

InnoDB上的区别：

• 聚集索引的数据页存放的是完整的记录；也就是说，聚集索引决定了表的物理存储顺序；
• 非聚集索引的数据页只存指向记录的地址信息，它真正的数据已经在聚集索引中存储了。

9.主键索引和唯一索引的区别

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

关于约束的概念

10.为什么数据库用b+树不用b树和红黑树？

1. 红黑树必须存在内存里的，数据库表太大了，存不进去。

2. 即使你找到了把红黑树存进硬盘的方法，红黑树查找一个节点最多要查logN层，每一层都是一个内存页，将一个节点放大磁盘中的一个页就很浪费；B+数的一个节点可以有很多数据，放进一个节点就不浪费，而且B+树一般三到四层就可以了。

3. b树的非叶子节点中也存放数据，都方法内存中就很消耗空间，而B+树的非叶子节点只是索引，没有数据，放到内存中占用的空间不大。

4. b+树的叶子节点有链表相连，适合范围查询，因为相邻页直接读取就好了。但b树做不到这一点。

11.索引失效

1.or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效；
2.组合索引，不是使用第一列索引，索引失效；
3.like以%开头，也就是通配符放在了首位;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）。

12.联合索引

什么联合索引
对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)

原理
以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。

注意问题
创建的时候应该让选择度高的、使用频繁的字段放在左边。

---

事务

1. 什么是事务？
2. 事务的四大特性。

事务知识点参考

关系数据库三大范式

第一范式

• 第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。
• 若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。
• 在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式

• 满足第二范式（2NF）必须先满足第一范式（1NF）。
• 第二范式要求实体中每一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。
• 完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性。
• 若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并且新实体与旧实体间是一对多的关系。

第三范式

• 满足第三范式必须先满足第二范式。
• 第三范式要求：实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。即：属性不依赖于其他非主属性。就是要消除传递依赖，可以看做是“消除冗余”，就是各种信息只在一个地方存储，不出现在多张表中。
• 如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。

锁

1.数据库的乐观锁和悲观锁是什么？

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

综合

1.什么是存储过程？有哪些优缺点？

存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

• 存储过程是一个预编译的代码块，执行效率比较高。
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率。
• 可以一定程度上确保数据安全。

2.SQL注入了解吗？怎么防止？

sql注入是一种将sql代码添加到输入参数中，传递到sql服务器解析并执行的一种攻击手法。

防止SQL注入：

• 严格检查输入变量的类型和格式；
• 过滤和转义特殊字符；
• 利用预编译机制（mysql 和 pdo）；
• 避免将用户提供的输入直接放入SQL语句中；
• 不要将敏感数据保留在纯文本中；
• 避免直接向用户显示数据库错误；
• 定期测试与数据库交互的Web应用程序。

参考博客

SQL

SQL优化

1.语句优化

参考博客
参考文章
参考文章

2.数据库优化

参考文章

SQL语句运行过程

参考文章