1、常见的数据库优化手段

• 对查询进行优化，要尽量避免全表扫描，考虑在where及order by涉及的列上建立索引。
• 避免在where子句中进行null值判断，避免使用!=或者<>，or,in,not in,like操作符，这些操作符会导致全表扫描。
• 对于大的delete或者insert语句，最好批量提交SQL语句，因为这两个操作会锁表，别的操作无法进来，表锁上一段时间累积的进程/线程会让你的服务器崩溃。
• 能用inner join连接尽量使用inner join连接。
• 子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
• 使用JOIN时候，应该用小的结果驱动打的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query(多个表查询效率低，容易锁表和阻塞)。
• limit千万级分页的时候优化。Select * from A where id>=(Select id from a limit 10000000,1) limit 10或者Select * from A where id between 10000000 and 10000010。
• 使用limit 1来终止数据库引擎继续扫描整个表或者索引。

• 尽量少OR，使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。

  
  image.png

2、 索引

2.1 什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储机构，它是某个表中一列或若干列值得集合和相应得指向表中物理标识这些值得数据页的逻辑指针清单。索引的作用相当于图书的目录，它可以根据目录中的页码快速找到所需的内容。

2.2 索引的优点

建立索引的列可以保证行的唯一性，生成唯一的rowid;建立索引可以有效缩短数据的检索时间；建立索引可以加快表与表之间的连接；为用来排序或者是分组的字段添加索引可以加快分组和排序顺序。索引的缺点：创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大；创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大；创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大；会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长。

2.3 什么样的表要建立索引

总的来说就是数据量大，经常进行查询操作的表要建立索引；表中字段建立索引应该遵循几个原则：越小的数据类型通常更好，简单的数据类型更好，尽量避免NULL，对唯一的字段；表与表连接用于多表联合查询的约束条件的字段应当建立索引；用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引；添加多列索引的时候，对应的多条件查询可以触发该索引的同时，索引最左侧的列的单条件查询也可以触发；如果有些表注定只会进行查询所有，也就没有必要添加索引，因为查询全部只能进行全量搜索即扫描全表。

2.4 索引种类

• 普通索引：仅加速查询
• 唯一索引：加速查询+列值唯一（可以有null）
• 全文索引：对文本的内容进行分词，进行搜索
• 主键索引：加速查询+列值唯一（不可以有null）+表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

2.5 索引的类型

• FULLTEXT全文索引，目前只有MyISAM引擎支持（MYSQL5.6的InnoDB支持全文索引），目前只有在CHAR,VARCHAR,TEXT列上可以创建全文索引。
• HASH索引，由于HASH的唯一及类似键值对的形式，很适合作为索引，HASH索引可以一次定位，不需要像树形索引那样逐层查找，所以在“=”和“in”条件下高效，对于范围查询、排序及组合索引效率不高。
• BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，每次查询都是从树的入口root开始，依次遍历node获取leaf。这是MySQL里默认和最常用的索引类型。RTREE索引的优势在于范围查找。

2.6 B+索引和hash索引的区别

Hash索引，等值查询效率高，不能排序，不能进行范围查询。
B+索引，数据有序，范围有序。
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。
哈希索引就是采用一定的哈希算法，把键值换成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立即定位到相应位置。

• 如果是等值查询，那么哈希索引有绝对优势，因为只需经过一次算法即可找到相应的键值。
• 如果是范围查询索引，这时候哈希索引就毫无用武之地了，因为原先有序的键值，经过哈希算法后有可能变成不连续的了，就没办法再利用索引完成范围查询。
• 同理哈希索引也没办法利用索引完成排序，以及like’xxx%’这样的部分模糊查询。
• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大。
• B+树索引结构适用于绝大多数场景，因为在大多数范围查询、排序、分组等。

2.7 最左匹配原则

最左匹配原则是针对索引的。
创建联合索引的原则：经常用的列优先，散列高的优先，宽度小的优先。
两个字段（name,age）建立联合索引，如果where age=12这样的话没有利用到索引。如果是where name=’xxx’ and age=’xxx’就利用到索引了，如果name和age调换位置也会利用到索引，因为优化器会进行优化。如果查询的字段恰好是索引不用回表（覆盖索引）。

image
例：
（a,b,c）索引相当于建立了(a),(a,b),(a,b,c)三个索引，b+树会先检索a，a相同在检索b,c；
（a,b,c,d）的联合索引，a = 1 and b = 2 and c > 3 and d = 4，只会检索到c，如果是(a,b,d,c)都会命因为索引器会优化顺序，检索时遇到比较值会停止匹配;

2.8 索引为什么用B+树

• B树：每个节点都存储Key和data，所有的节点组成这棵树，并且叶子节点指针为null.
• B+树：只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针，后来加上了顺序访问指针。

不同于B树只适合随机检索，B+树同时支持随机检索和顺序检索；
B+树的磁盘读写代价更低；
B+树的查询效率更加稳定；
数据库索引采用B+树的主要原因是B树没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。

3、 数据库连接池

数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”，预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从缓冲池中取一个，使用完毕后再放回去。优势：资源重用，由于数据库连接得到重用，避免了频繁创建、释放连接引起的大量性能开销，也增进了系统运行的平稳性；更快的系统相应速度；新的资源分配手段；统一的连接管理，避免数据库连接泄露。

4、事务：

我们在实际业务场景中，经常会遇到数据频繁修改读取的问题在同一时刻，不同的业务逻辑对同一个表数据进行修改，这种冲突很可能造成数据不可挽回的错乱，所以我们需要对数据进行管理。事务必须服从ACID原则。ACID指的是原子性、一致性、隔离性、持久性。事务其实就是一系列指令的集合。
原子性、稳定性和持久性是通过redo 和 undo 日志文件实现的，undo记录了数据在事务开始之前的值，当事务执行失败或者ROLLBACK时可以通过undo记录的值来恢复数据。redo日志记录数据修改后的值，可以避免数据在事务提交之前必须写入到磁盘的需求，减少I/O。

• 原子性：操作这些指令时，要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。
• 一致性：事务的执行使数据从一个一致性状态转换为另一个一致性状态，但是对于整个数据的完整性保持稳定。
• 隔离性：在该事务执行的过程中，无论发生的任何数据的改变都应该指存在于该事务中，对外界不存在任何影响。只有在事务确定提交后，才会显示该事务对数据的改变。其他事务才能获取到这些改变后的数据。
• 持久性：当事务正确完成后，它对于数据的改变是永久性的。

事务的隔离性：
当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作以保证各个线程获取数据的准确性。数据库事务的隔离级别：

• Read uncommitted,读未提交，就是一个事务可以读取另一个未提交事务的数据。当一个事务读取另一个未提交事务的数据时会发生脏读。通过Read committed读提交能解决脏读问题。
• Read committed读提交，就是一个事务要等另一个事务提交后才能读取数据。可以解决脏读问题。但是在一个事务范围内两次查询操作之间如果另一个事务进行UPDATE操作，这就会导致两次相同的查询操作的结果不一样（比如卡里面看着有钱，但当扣款再次查询时发现没钱了已经），这就是不可重复读。Repeatable read可以解决不可重复读问题。
• Repeatable read重复读，就是在开始读取操作时，不再允许修改操作。重复读可以解决不可重复读问题，不可重复读对应的是修改，即UPDATE操作，但是可能还会有幻读问题，因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。幻读：当某事务打印记录时，另一事务进行了INSERT操作，使打印出来的事务与看到的不一样。Serializable序列化可以解决幻读问题。
• Serializable序列化是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
  大多数数据库默认的事务隔离级别是Read committed，比如Sql Server，Oracle。Mysql的默认隔离级别是Repeatable read。

5、MySQL的两种查询引擎区别，各自适用的场景

image image

MYISAM不支持事务，不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描。
INNODB支持事务，支持外键，行锁，查表总行数时，全表扫描。
MyISAM里data存的是数据地址，索引是索引，数据是数据。
INNODB里data存的是数据本身，索引也是数据。
区别：
一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

• MyISAM是非事务安全的，而InnoDB是事务安全的；
• MyISAM锁的粒度是表级的，而InnoDB支持行级锁；
• MyISAM支持全文类型索引，而InnoDB不支持全文索引；
• MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM；
• MyISAM表保存成文件形式，跨平台使用更加方便；
• MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择；
• InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。

非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。
聚集索引就是指B+Tree的叶子节点上的data，是数据本身。

6、Explain命令：

我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还需要知道该SQL的执行计划，比如时全表扫描还是索引扫描，这些都需要通过Explain去完成。
EXPLAIN列的解释：

• table：显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
• key： 实际使用的索引。如果为NULL，则没有使用索引。
• key_len：使用的索引的长度。
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
  -rows：MYSQL认为必须检查的用来返回请求数据的行数
• Extra：关于MYSQL如何解析查询的额外信息。这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

7、 Varchar和Char的使用场景：

用来存储字符，varchar适用于字符长度经常变的，char适用字符长度固定的。
Varchar(20)中的20表示的是varchar数据的数据长度最大是20，超过则数据库不会存储。
Int类型数据的字节大小是固定的4字节，int(11)表示在开启（填充0）情况下，存储的数字长度是小于11则会在不足位数的前面补充0，但是如果大于11位的话，则按实际存储的显示（数据大小在int类型的4字节即可），也就是说int(M)不代表数据的长度。

8、 三大范式

• 1NF：字段不可分，原子性；
• 2NF：有主键，非主键字段依赖主键，唯一性，一个表只说明一个事务；
• 3NF：非主键字段不能相互依赖。每列都与主键有直接关系，不存在传递依赖。

9、数据库中的连接

• Left join（左连接）返回左表中的所有记录和右表中联结字段相等的记录。
• Right join（右连接）返回右表中的所有记录和左表中联结字段相等的记录。
• Inner join（等值连接）只返回两个连接表中连结字段相等的行。
• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

10、锁

并发控制一般采用三种方法，分别是乐观锁，悲观锁和时间戳。

• 乐观锁就是认为一个用户读取数据的时候，别人不会去写自己所读的数据；
• 悲观锁相反，觉得自己读数据的时候，别人可能刚好在写自己刚读的数据，是基于数据库机制实现的，比如在使用select子句的时候加上for update那么直到改子句的事务结束为止，任何应用都无法修改select出来的数据；
• 时间戳就是不加锁，通过时间戳来控制并发出现的问题。在数据库中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候把该字段也读出来，当写回去的时候把该字段加1，提交之前，跟数据库的该字段比较一次，如果比数据库的值大的话就允许保存，负责不允许保存。

悲观锁分为排他锁（写锁）和共享锁（读锁）。

行级锁是一种排他锁，防止其他事务修改此行。使用insert,update,delete,select语句会自动应用行级锁，使用commit,rollback语句会释放锁。
表级锁分为：行共享（禁止排他锁定表）、行排他（禁止使用排他锁和共享锁）、共享锁（锁定表，对记录只读不写，多个用户可以同时在同一表上应用此锁）、共享行排他（比共享锁更多的限制，禁止使用共享锁及更高的锁）、排他（限制最高的表锁，仅允许其他用户查询该表的行，禁止修改和锁定表）。