数据库面试知识点总结--既可以做目录也可以做资料

前言

本文介绍的知识点主要为以下几个方面，这里列出了几点，读者可以根据这几点回忆一下自己大脑中存在的印象，然后再看正文，这样效果最好！

索引： 单个索引、联合索引、主键索引

事务： 四个粒度, 分别是数据库级、表级、记录级 (行级) 和属性级(字段级)

锁：行锁，表锁；乐观锁，悲观锁

mysql优化： 分表，sql优化

生产数据库都要求数据库引擎设置为innodb，因为考虑到并发的优势！

正文

0：数据库引擎介绍

myisam是mysql数据库的默认引擎，不支持事务，使用的锁是表级别锁， 一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁，由于锁的粒度比较大，所以并发能力一般！

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

在InnoDB的事务管理和锁定机制中，容易发生死锁，所以有一套专门检测死锁存在的机制，当两个事务发生死锁后，会终断小的事务，让大的事务运行（大小，依照事务涉及数据量来评估）

1： 索引

分类：

普通索引(单列索引)：最基本的索引，没有任何限制 @hxx 常加在where列 和join 列上

1. Select people.age, ##不使用索引   
2. town.name ##不使用索引   
3. FROM people LEFT JOIN town ON people.townid=town.townid ##考虑使用索引   
4. Where firstname='Mike' ##考虑使用索引   
5. AND lastname='Sullivan' ##考虑使用索引 

唯一索引：与 "普通索引" 类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它 是一种特殊的唯一索引，不允许有空值。 @hxx就是一种唯一性索引

全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高 mysql 效率可建立组合索引，遵循” 最左前缀 “原则。

最左侧原则：例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .

建立多个单列索引和组合索引的区别?

比如： Select  peopleid  

1. FROM people   
2. Where firstname='Mike'   
3.       AND lastname='Sullivan' AND age=17;  

将三个列都建立独立的单列索引，和建立 ADD   INDEX  fname_lname_age (firstname,lastname,age); 

区别在哪？

三个单索引，相当于查三次索引，每次范围都缩小一次，就是row的结果集依次减少，最后得出结果，但是row的减少并不是最优的！

而建立组合索引，相当于只有一个索引块了，利用b+树的特点，只需要查一个索引就能快速跳转得到结果！

创建索引的sql：

create （ unique  ，）index on Tablename(列的列表) 

alter table TableName add index (列的列表) 

原理：

b+树： 每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级，这就是b+树的优势。

哪些sql语句会用到索引？

列做比较的表达式，如=, >, >=, <, <=及between和 in 操作

like语句的条件是 不以通配符开头的常量串 @hxx LIKE 'Mich%'   这个会使用索引

col_name的列建了索引，则形如"col_name is null"的SQL会用到索引

where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围

优化：

a：列的区分度比较高的，加上索引会比较好！ @hxx 无脑的做法是为常用的where 语句的字段都加上索引，如果此字段识别度比较低（比如sex字段），那么查询效率不会有太大的提升，并且还会影响更新和插入的性能

b：

@hxx 参见美团这篇文章 https://tech.meituan.com/mysql-index.html

----------------

2：事务

捋一下思路

事务的隔离性 造成的影响： 脏读，不可重复读，幻影读

事务一致性造成的影响： 不要用并发来回答这个问题，一致性指的是从一个状态符合预期的变成另一个状态，acid其余三个属性都是为了一致性服务的！ 并发只的是多线程引起的，而一个事务就是一个线程，所以并发应该算到隔离性中。

事务是如何实现的？ @hxx 总觉得redo log 和undo log只要一个即可！

redo log：保存执行的每一条sql log， 当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘 @hxx 1s刷一次

undo log：undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。 @hxx 回退，注意是拷贝数据

rollback segment：可以认为undo log和回滚段是同一意思， @hxx 是undo log的物理存储形式

完整的文章在公众号上，读者可以扫描前往，这样更加方便，谢谢大家对原创的支持