2.数据库优化及索引

一、事务的特性及隔离级别

1.1 并发操作引发的问题

当对数据库进行并发操作的时候，可能会产生脏读，幻读，不可重复读。

• 脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。

• 不可重复读：指一个线程中的事务读取到了另外一个线程中提交的update的数据。

  不可重复读：是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

• 幻读（虚读）：指一个线程中的事务读取到了另外一个线程中提交的insert的数据。

  幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。简单来说，幻读是由插入或者删除引起的。

1.2 事务的隔离级别(4种)

isolation:隔离.事务有4种隔离级别

READ-UNCOMMITTED:读未提交，最低的隔离级别，一般不采用；

READ-COMMITTED:读已提交，解决了脏读问题，Oracle的默认隔离级别;

REPEATABLE_READ:可重复读，解决了脏读，不可重复读的问题，mysql的默认隔离级别;

SERIALIZABLE:串行化，解决了脏读，幻读和不可重复读(虚读)，一般开发中也 不采用该隔离级别。

1.2 事务的传播特性/行为

propagation:Spring的传播行为/特性,7个属性.表示在Spring中是否支持事务及对事务的操作策略

REQUIRED:默认值,表示该方法支持事务,如果程序员自己手动开启事务了,则使用自己开启的事务;如果没有开启,则使用Spring自带的事务;

REQUIRES_NEW:支持事务操作,Spring都会自动创建一个新的事务;

NEVER:不支持事务,如果有则会抛出异常

SUPPORTS:支持事务,可以没有事务.

NOT_SUPPORTED:不支持事务;

MANDATORY:手动的,需要自己手动开启事务.

NESTED:内嵌的,支持事务嵌套.

二、数据库索引及分类

索引是帮助MySQL高效获取数据的排好序的数据结构

**注意：**索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

1、索引我们分为四类来讲单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

1.1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

1.1.1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

1.1.2、**唯一索引：**索引列中的值必须是唯一的，但是允许为空值，

1.1.3、主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）

1.2、**组合索引：**在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

1.3、**全文索引：**全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。

三、数据库的优化实践

3.1普通查询

• 1.如果明确知道查询只有一条结果返回，limit 1 能够提高效率

• 2.把计算放到业务层而不是数据库层，除了节省数据的CPU , 还有意想不到的查询缓存优化效果。

• 3.不要使用 select * 进行数据库的查询，查什么具体的字段就直接具体指明，这样能够大大的节省数据传输量，从而提高数据库查询的效率。

• 4.强制类型转换会导致全表扫描( phone 为 varchar 类型)

  select from user where	phone=13800001234
  

• 5.如果在查询的时候涉及到了数据的计算，把数据的相关计算放到业务层而不是数据库层，一方面节省了数据的CPU,还会有比较好的查询缓存优化效果。

• 6.表的关联查询时遵循 小表驱动大表 原则

• 7.在根据指定条件判断表中记录是否存在时，使用 select 1 进行查询

  SQL不再使用count，而是改用LIMIT 1，让数据库查询时遇到一条就返回，不要再继续查找还有多少条了业务代码中直接判断是否非空即可。

  使用count
  	SELECT count(*) FROM table WHERE a = 1 AND b = 2;
  替换为select 1
  	SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1 ; 
  

  int nums = xxDao.countXxxxByXxx(params);
  if ( nums > 0 ) {
    //当存在时，执行这里的代码
  } else {
    //当不存在时，执行这里的代码
  }
  替换为
  Integer exist = xxDao.existXxxxByXxx(params);
  if ( exist != NULL ) {
    //当存在时，执行这里的代码
  } else {
    //当不存在时，执行这里的代码
  }
  

3.2索引查询

• 1.负向条件查询不能使用索引

  select from order where status!=0 and status!=1
  no in/not exists
  

  可以将其优化为 in 查询

  select from order where status in(2,3)
  

• 2.前导模糊查询不能使用索引

  select from order where desc like '%XX'
  

• 3.非前导模糊查询可以命中索引

  select from order where desc like 'XX%'
  

• 4.数据库区分度不大的字段不宜使用索引

  select from user where	sex=1
  

• 5.能过滤大量数据时就可以使用索引

• 6.在属性上进行计算时无法命中索引

  select from order where YEAR(date)	<	=	'2017'
  

• 7.如果业务大部分是单挑查询，使用 Hash 索引性能更好，例如用户中心

  select from user where	uid=?
  select from user where	login_name=?
  

  原因 : B-Tree 索引的时间复杂度为 O(log(n)) ; Hash 索引的时间复杂度是 O(1)

• 8.允许 null 值的字段，在查询时会有潜在的大坑

• 9.单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null ,可能会得到"不符合预期"的结果集

  select from user where name	!=	'shenjian'
  

• 10.组合索引的最左前缀，并不是 SQL语句的 where 顺序要和组合索引顺序一致

• 11.可以使用 ENUM 取代字符串，保存 TINYINT 类型

四、使用索引的注意事项

4.1 建立索引的注意点

• 1.索引要建立在经常 select 操作的字段上
• 2.索引要建立在值比较唯一的字段上
• 3.对 text、image 和 bit 数据类型的字段不应该建立索引；(因为这些列的数据量要么很大，要么则很少进行取值)
• 4.在 where 和 join 中出现的列需要建立索引
• 5.在 join 操作中，应该在保证主键和外键数据类型相同时才去建立索引
• 6.针对于不同的字段应该建立不同的索引，主键索引，唯一索引等。
• 7.索引不宜过多，尽量不要超过 10 个
• 8.对每张表都需要设立主键(索引),提高/加速查询的效率

4.2 查询时注意点

• 1.索引不会包含有 null 的列

• 2.使用短索引进行查询(提高效率)

• 3.索引列排序

• 4.不要在列上进行计算

• 5.不要使用 not , ! , > < 等操作(转换为 in 查询)

五、索引的失效场景

1. 对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引

​ alter table student add index my_index(name, age) // name左边的列， age 右边的列

​ select * from student where name = ‘aaa’ // 会用到索引

​ select * from student where age = 18 // 不会使用索引

2. 对于使用 like 查询， 查询如果是 ‘%aaa’ 不会使用索引，而 ‘aaa%’ 会使用到索引。

select * from student where name like ‘aaa%’ // 会用到索引

​ select * from student where name like ‘%aaa’ 或者 ‘_aaa’ // 不会使用索引

3. 如果条件中有 or， 有条件没有使用索引，即使其中有条件带索引也不会使用，换言之， 就是要求使用的所有字段，都必须单独使用时能使用索引。

4. 如果mysql认为全表扫描要比使用索引快，则不使用索引。

如：表里只有一条数据。

​ 5.组合索引要遵循从左到右原则，否则索引会失效，如：select * from table

​ where name = “张三” and age = 18 ,那么该组合索引是 (name,age)

六、回表、索引下推及索引覆盖

5.1 聚集索引

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了主键，则PK就是聚集索引；
（2）如果表没有定义主键，则第一个非空唯一索引（not NULL unique）列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

5.2 回表

假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

为什么会回表?

（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

​ 如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索

引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列

中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

5.3 索引覆盖

• 所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了

• MySQL 中只能使用 B-Tree 索引做覆盖索引，因为哈希索引等都不存储索引的列的值，覆盖索引对于 MyISAM 和 InnoDB 都非常有效，可以减少系统调用和数据拷贝等时间

• Tips：减少 select * 操作

注： 如何知晓自己是否进行了索引覆盖?

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

5.4 索引下推

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

重要：

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

七、数据库的三大范式

第一范式，又称1NF，它指的是在一个应用中的数据都可以组织成由行和列的表格形式，且表格的任意一个行列交叉点即单元格，都不可再划分为行和列的形式，实际上任意一张表格都满足1NF；

第二范式，又称2NF，它指的是在满足1NF的基础上，一张数据表中的任何非主键字段都全部依赖于主键字段，没有任何非主键字段只依赖于主键字段的一部分。即，可以由主键字段来唯一的确定一条记录。比如学号+课程号的联合主键，可以唯一的确定某个成绩是哪个学员的哪门课的成绩，缺少学号或者缺少课程号，都不能确定成绩的意义。

第三范式，又称3NF，它是指在满足2NF的基础上，数据表的任何非主键字段之间都不产生函数依赖，即非主键字段之间没有依赖关系，全部只依赖于主键字段。例如将学员姓名和所属班级名称放在同一张表中是不科学的，因为学员依赖于班级，可将学员信息和班级信息单独存放，以满足3NF。

又称2NF**，它指的是在满足1NF的基础上，一张数据表中的任何非主键字段都全部依赖于主键字段，没有任何非主键字段只依赖于主键字段的一部分。即，可以由主键字段来唯一的确定一条记录。比如学号+课程号的联合主键，可以唯一的确定某个成绩是哪个学员的哪门课的成绩，缺少学号或者缺少课程号，都不能确定成绩的意义。

第三范式，又称3NF，它是指在满足2NF的基础上，数据表的任何非主键字段之间都不产生函数依赖，即非主键字段之间没有依赖关系，全部只依赖于主键字段。例如将学员姓名和所属班级名称放在同一张表中是不科学的，因为学员依赖于班级，可将学员信息和班级信息单独存放，以满足3NF。