mssql 索引 MySQL核心知识【持续更新中】

MySQL介绍

数据库种类：

MySQL属于关系型数据库。其区别与其他关系型数据库最大的特点是，它是一个支持插件式的存储引擎，支持如InnoDB，MyISAM，Memory等。

MySQL是C/S模型，即有mysql client，mysql server

MySQL的服务器模型采用的是IO复用和可伸缩的线程池。其中IO复用采用的是select，而不是epoll。主要是因为数据库的磁盘IO比较慢，所以网络IO要比磁盘IO快得多，因此不需要太快的网络IO，只需要两个IO速度相适配即可。

MySQL存储引擎

MyISAM和InnoDB存储引擎有什么区别？

MyISAM不支持事务，行级锁，不支持外键。索引采用的是非聚簇索引。其优势就是访问速度比较快。如果对事务完整性没有要求，并且查询数据和存储数据比较频繁的场景可以使用该存储引擎进行数据持久化。MyISAM在磁盘上会存储成三个文件：.frm（存储表的定义，即表结构）， .MYD（数据），.MYI（索引）。

InnoDB存储引擎提供了具有提交，回滚，崩溃恢复能力的事务安全，支持行级锁，支持外键，支持自动增长列等功能。索引采用的是聚簇索引，索引和数据存储都是存储在同一个文件中的（InnnoDB的数据不能脱离索引）。InnoDB在磁盘上的存储会生成两个文件：.frm（表结构），.idb（数据+索引）

补充：Memory存储引擎是一种在内存级别存储数据的引擎。每一个memory表都对应一个磁盘文件。其优势是速度访问极快，因为它的数据都是存储在内存中的。其使用的索引结构是HASH索引（不适合做范围查询）。缺点是一旦服务关闭，之前的数据就会全部消失。

为什么使用MyISAM的时候，只有在主动加主键的时候才会加，而InooDB存储下，默认就会添加主键？

因为MyISAM中有.MYI文件专门存储索引，所以如果没有主动加索引的话，该文件就是空的。而InnoDB的数据和索引是在一起的，所以数据不能脱离索引而存在，因此如果没有主动加主键索引的话，默认就是加一个整型的id作为表的索引。

MySQL索引

索引的核心就是加速查找数据，提高查询的数据的速度。

由于索引也是要被存储成索引文件的，因此对索引的使用也会涉及到磁盘IO。如果创建的索引过多，返回会因为进行一些无用的磁盘IO，从而降低了SQL的查询效率。

索引的类型

索引在物理上被分成了

索引在逻辑上分成了

索引的创建和删除

创建表的时候添加索引

create table indexTable(
	id int,
	name varchar(20),
	index(id) # 使用index()创建索引
);

在已创建的表上添加索引

create index 索引名称 on 表名(属性名(长度));
# 例如
create index nameIdx on indexTable(name(10));

删除索引

drop index 索引名 on 表名;
# 例如
drop index nameIdx on indexTable;

查看索引

# 查看当前表中所有的索引
show indexes from 表名;

索引的底层实现原理

数据是存储在磁盘上的，而用户如果想要读取数据，就一定要将数据读取到内存中。其中最大的成本就在于磁盘I/O。磁盘是以块为单位进行数据管理的，一个数据块一般是16K。而内存是以页为单位进行内存管理的，一个页是4k。所以进行一个磁盘I/O对应需要4个内存页进行管理。

正因为查询数据的性能瓶颈在磁盘的I/O上，因此索引的底层数据结构的目的就是要尽量地减少磁盘I/O。

一般来说查找数据比较快的数据结构就是AVL树，红黑树等二叉平衡树。假设有2000w的数据使用AVL树进行存储，那么每一次读取数据至少要使用2000w的对数次，大约是25次，也就是进行一次数据读取就需要进行25次磁盘IO。

一般数据库中都是使用B-树（注意不是B减树，B-是B树，B+树，B-树的统称），也就是一个多叉平衡树，B-树的阶数通常取300~500之间，具体情况按照具体的业务去设置。假设阶数为400，那么最多只需要3次磁盘IO就可以找到数据，这样就大大减少了磁盘I/O的次数（核心）。并且因为B树节点中的数据都是有序的，所以也可以通过二分搜索查询数据，因此查询时间复杂度还是在O(logN)。

B树的特点：多叉，平衡，层数少。所以在索数据的时间复杂度不变的同时大大减少了磁盘IO的次数，这就是选用B树作为索引底层实现的最大的优势。

具体过程：判断查询的字段是否有索引（假设有索引） -> 通知存储引擎 -> 通知操作系统 -> 磁盘I/O -> 读取到内存中 -> 在内存中构建索引树进行加速查询

注意：

实际上B树并不是MySQL采用的索引底层实现，MySQL中的MyIASM和InnoDB使用的采用B+树进行构建索引树。

为什么MySQL采用B+树而不是B树作为索引底层实现呢？

主要基于一下三点：

在B树中索引和数据是分散在节点中存储的，所以离根节点近的节点就可以更快地被搜索到。反之mssql 索引，搜索时间就更长。所以磁盘I/O次数很随机，搜索次数也很随机。每一个非叶子节点中同时要存储索引和数据，这样比只存储索引的节点能存放的索引值少的多，所以B树相对B+树层数会更高一点，搜索效率也会更低一点。B树不方便进行范围查询。并且进行全表查询的时候，搜索B树节点也比较不方便。

B+树特点

简单说，B+树比B树多出的特点就是所有非叶子节点只存储索引值，所有叶子结点存储索引值和数据的映射。并使用双向链表串联成一个有序链表。

总结

当使用select * from user where id = 123;的时候，首先判断id是否有索引，如果没有就进行全表搜索。如果有就让存储引擎进行磁盘IO将索引文件读取到内存中，并在内存中构建出一个B+树。B+树的非叶子节点中只存储索引值，叶子节点存储索引值和其对应的数据，并用双向链表将其串联形成一个有序链表。相对于B树或者其他的平衡树，它的层数最少，也就是磁盘IO次数最少。而且支持区间的范围查询。

主键索引和辅助索引

假设有这样一张表（存储引擎是InnoDB）

create table user(
	id int primary key,
	name varchar(20),
	age int,
	index(name)
);

其中id是主键索引，name是二级索引/辅助索引。从上文可知，主键索引的B+树的叶子节点存储的是数据。而辅助索引的叶子节点存储的索引所在行的主键，而不是实际数据。所以当要通过辅助索引找非索引字段就需要进行回表查询。

回表查询的意思就是在辅助索引树中获得记录的主键，然后拿着这个主键再去主键索引树中找到对应的数据。

但是也有一些例外：

# 不用回表查询
select name from user where name = 'zhangsan';
# 不用回表查询
select id, name from user where name = 'zhangsan';
# 需要回表查询
select age from user where name = 'zhangsan';

前两个SQL执行的时候，不用回表查询。因为id和name在辅助索引上就有，所以直接返回就可以了。但是age在辅助所以上没有，因此就需要进行拿着主键在主键索引的叶子节点中找。

如果在某个业务常见下，需要通过name索引快速返回其对应的age。这时就不能让辅助索引进行回表查询了，而可以考虑建立一个name和age的联合索引，这样age就可以辅助索引上，而不用回表查询了。

tyle=none&taskId=uefb1c1ae-4901-4f68-a372-fbe74d2dca2&title=&width=1055)

还有一种使用联合索引的常见，就是如果要执行`select * from user where name =‘zhangsan’ order age’的时候，如果只对name建索引，那么就会name筛选出来的值进行外部排序。

如果建立了index(name,age)的联合索引，那么**name在前，age在后，所以就会先按name排序。name相同，再按age排序。**当搜索出name='zhangsan’的值之后，这些值就是按照age进行排序的，此时就不会出现外部排序。

总结

聚簇索引和非聚簇索引

辅助索引中叶子节点存储的是记录的主键，所以需要进行回表查询辅助辅助树中没有的数据，其底层的存储引擎是InnoDB。这种将索引和数据放在一起存储的索引也被成为聚簇索引。

如果存储引擎是MyIASM的话，那么主键索引树的叶子节点中存储是完整数据记录的地址。而辅助索引的叶子节点中存储的也是完整数据记录的地址。这种将索引和数据分开存储在两个文件的索引也成为非聚簇索引。可以看出来非聚簇索引的辅助索引是可以不用回表查询的。

哈希索引

MySQL中的memory存储引擎使用的就是哈希索引，其底层是一个链式哈希表。即发生哈希冲突的时候，采用的是拉链法处理哈希冲突。

哈希表的查询效率是O(1)，看起来要比B树效率还要高。但是实际上哈希索引的缺点也很明显。哈希索引底层的哈希表是无序的，所以不能使用order by进行查询。并且哈希表不能支持范围搜索和模糊匹配。除非哈希索引将所有数据进行搜索，那样的话，磁盘IO的次数就太多了，效率就会大大降低。

一般基于kv（哈希表）存储的数据库都用于内存中的数据搜索，例如Redis。

总结

InnoDB自适应哈希

前面说了memory存储引擎是在内存中使用的哈希索引进行数据构建，但是由于磁盘IO的次数太多，并且查询方式的有限，所以一般不使用。

而InnoDB中也有哈希索引，但是开启这个哈希索引有一定的条件。当InnoDB检测到某一个二级索引树上相同的二级索引值被重复的查询，此时InnoDB会根据这个二级索引值在内存中构建一个哈希索引一次来加速查询数据。

如图：

原本需要二级索引查询+回表查询两次查询操作，现在只需要一次哈希定位就可以了。

但是自适应哈希索引本身也是需要被维护在内存中的，也是需要消耗一定的性能。并不是任何场景下都是适用。

# 查看是否开启自适应哈希：默认开启
show variables like 'innodb_adaptive_hash_index';
# 查看自适应哈希分区数量：每一个自适应哈希索引分区都有一把单独的锁
show variables like 'innodb_adaptive_hash_index_parts';
# 查看InnoDB存储引擎使用情况
show engine innodb status;

自适应哈希默认启动开启，一般使用show engine innodb status;查看InnoDB的使用情况，然后判断是否要关闭自适应哈希。

分区的锁竞争情况。如果一个分区上等待的的线程数量过多，那么锁竞争会导致效率降低。自适应哈希索引搜索频率和二级索引树搜索频率。如果自适应哈希索引的使用频率比较低，那么就没有必要在内存中维护自适应哈希了。 优化索引的常见方式

经常要作为where中的过滤字段可以考虑添加索引。并且选择的字段的区分度要高。当区分度不高的情况下，索引会失效而被优化成为全表扫描或者其他的方式。

查询的字段会影响到是否要回表查询，所以要精确定位需要的字段。

一张表在一次查询时只会使用一个索引，在一张表有多个索引的情况下，优先使用过滤数据量最少的索引。如果一定要使用某一个索引进行数据查询的话，可以使用select * from 表名 force index(索引名)的方式。

字符串字段创建索引时，尽量规定索引的长度，不至于让索引的长度过长。

同时出现where和order by的时候，可以考虑建立联合索引。

需要注意索引字段在涉及类型强转（字段是字符串类型，但是查询的时候使用的是整数，MySQL就会自动进行类型强转），函数调用，表达式字段时不能使用索引。

使用like进行前缀的模糊查询（例如like ‘%zhang%’），某些场景下使用or，not in都会被优化成全表查询。

not in会被优化成为范围搜索。or会被优化成union all;

进行联合查询时，先比较每一张表执行查询条件的开销，哪一个执行查询条件的开销小，哪一张表就是小表。然后用小表筛选出的数据在大表中使用索引加速查找。所以小表决定循环次数，大表决定每一次循环的时间。

慢查询日志-slow_query_log

对于索引优化的方式，一般都是通过explain进行分析，是否使用到索引？是不是索引建立的有问题？是否因为表数据量太大导致花费的时间很长？

在实际项目中会设计到很多的sql，此时就需要找到最消耗时间的sql，从这个性能瓶颈出发使用explain做进一步的分析和优化。但是如何找到耗时的sql呢？这就需要使用到慢查询日志，即slow_query_log。

slow_query_log是MySQL中四大日志之一，顾名思义就知道它记录的就是查询比较慢的sql等详细信息。

慢查询信息查看命令

show variables like 'slow_query%';

# 开启慢查询
# slow_query_log是全局变量，所以需要使用global
set global slow_query_log = ON;

long_query_time

系统中通过long_query_time变量来判断哪一个SQL执行的时间比较慢，消耗时间比该变量大的SQL就被称为慢查询。

在不同的场景下可以进行设置慢查询的标准。

# 查看long_query_time
show variables like 'long_query_time';
# 设置long_query_time
set long_query_time = 1;

MySQL事务 什么是事务

事务就是将一条或者多条SQL打包成一组，形成一个最小的执行单元。这个执行单元在执行的时候具有原子性，要么全部执行成功，要么全部执行失败。

一般事务设计到三个操作：开启事务，提交事务（结束事务），回滚事务

事务处理的命令

# 查看是否为自动提交事务（默认是自动提交，即一句SQL就是一个事务）
select @@autocommit;
# 设置是否手动提交事务
set autocommit=0;
# 开启事务
begin; 
# 随时可以回滚，相当于事务中的SQL全部执行失败，恢复当执行事务前的状态
rollback
# 提交事务
commit; 
# 设置一个名字叫point的保存点
savepoint point;
# 事务回滚到名字家偶偶point的保存点
rollback to savepoint;
# 设置事务的隔离级别
set tx_isolation='';
# 查看事务的隔离级别
select @@tx_isolation；

为什么会有事务

事务的本质就是为了服务上层应用。当上层的应用程序在访问数据的时候，事务简化了编程模型。因为不用在考虑网络异常，服务器宕机，并发访问等问题，因为事务要么回滚，要么提交。其中的潜在错误和并发问题已经被绕开了。

所以事务并不是数据库系统天生自带的，而是因为更好地服务上层，从而产生的。

ACID特性

MySQL中的每一个事务都有这样四种特性：

一致性 隔离性 持久性

补充：

事务并发存在的问题

如果事务不经过隔离，在并发执行事务的时候通常会出现一下的问题：

不可重复读 幻读

其中脏读大多数情况下都是有问题的，因为它的不确定因素太大了。

但是不可重复读和幻读根据业务场景的不同，可能是问题也可能不是。

事务的隔离级别

在并发的过程中，为了保证数据的安全，所以就引入了事务时间的隔离问题。而在不同的业务场景下，事务之间的隔离程度是不同的。所以在MySQL中就划分成了4中不同的隔离级别。

READ-UNCOMMITTED，读未提交：在提交前，另一个事务就可以看到修改的数据。READ-COMMITTED，读提交：只能读到其他事务提交过的数据。REPEATABLE-READ，可重复读：保证事务前后两次的查询操作数据是相同的。SERIALIZABEL，串行化：线程是串行执行的。

下图展示，设置对应的隔离级别能够解决和还没解决的问题：

补充：

（编辑：广州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!