数据库索引、事务

索引

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

使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点:

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

使用

1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引

DROP INDEX index_name ON table

2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

实现原理

索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引.

B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

B+ Tree(B- Tree变种)

• 非叶子节点不存储data,只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

data：

1. 磁盘文件地址指针
2. 存储相应记录主键的值

聚集索引：索引和数据放在一个文件里

非聚集索引：索引文件和数据文件是分离的

InnoDB索引实现(聚集)

1. 表数据文件本身就是按B + Tree组织的一个索引结构文件
2. 聚集索引-叶节点包含了完整的数据记录
3. 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键?
   • 如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引
   • 整型：节约空间。
   • 自增：每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页
4. 为什么非主键索引结构叶子节点存储的是主键值? (一致性和节省存储空间)
   

MyISAM索引文件和数据文件是分离的(非聚集)

Hash索引不支持范围查找

事务

定义：

1. 数据库事务是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)
2. 事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

组成：一个数据库事务通常包含对数据库进行读或写的一个操作序列

事务并发异常

1. 回滚丢失：此种更新丢失是因为回滚的原因，
2. 覆盖丢失：因为更新被其他书屋给覆盖了

• 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
• 不可重复读: 这种异常是一个事务对同一行数据执行了两次或更多次查询,但是却得到
  了不同的结果
• 幻读: A事务读取了一个范围的内容（10条数据）,而同时B事务在此期间插入了一条数据（11条）.造成"幻觉".（读取数据量不一致）

事务隔离级别

1. 读未提交：该隔高级别指即使一个事务的更新语句没有提交，但是别的事务可以读到这个改变
2. 读已提交：该隔离级别指一个事务只能看到其他事务的已经提交的更新,看不到未提交的更新。消除了脏读和第一类丢失更新，.这是大多数数据库的默认隔离级别,如Oracle, Sqlserver
3. 可重复读：该隔高级别指一个事务中进行两次或多次同样的对于数据内容的查询。得到的结果是一样的,但不保证对于数据条数的查询是一样的,只要存在读改行数据就禁止写,消除了不可重复读和第二类更新丢失 ,这是Mysql数据库的默认隔离级别
4. 串行化：事务执行的时候不允许别的事务并发执行完全串行化的读,只要存在读就禁止写，但可以同时读,消除了幻读。这是事务隔离的最高级别虽然最安全最省心,但是效率太低，一般不会用

各种隔离级别能解决哪些异常