mysql翻转索引 mysql倒排索引实现原理

MySQL

什么是索引？

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

索引的原理：就是把无需的数据编程有序的查询

1、把创建了索引的列的内容进行排序

2、把排序结构生成倒排表

3、在倒排表内容上拼上数据地址链

4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

索引设计的原则?

查询更快、占用空间更小 ( 适合建立索引 )1、适合索引的列是出现在where子句中的列，或者连接子句中指定的列

2、基数较小的表，索引效果较差，没有必要在此列建立索引

3、使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

4、不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引例列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

5、定义有外键的数据列一定要建立索引。

不适和建立索引

6、更新频繁字段不适合创建索引

7、若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

8、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

9、对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

10、对于定义为text、image和bit的数据类型的列不要建立索引。

聚簇索引（B+树的数据结构）

聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的

非聚簇索引:叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似—本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

慢查询

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

所以优化也是针对这三个方向来的：

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

事务的基本特性和隔离级别

事务基本特性ACID分别是:

原子性指的是一个事务中的操作要么全部成功，要么全部失败。

一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设A只有90块，支付之前我们数据库里的数据都是符合约束的但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证

隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。

持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有4个隔离级别，分别是:

• read uncommit读未提交，可能会读到其他事务未提交的数据，也叫做脏读。用户本来应该读取到ID=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。
• read commit读已提交，两次读取结果不—致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。
• repeatable read 可重复复读，这是MySQL的默认级别，就是每次读取结果都一样，但是有可能产生幻读。
• serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

ACID靠什么保证的？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C一致性由其他三大特性保证、程序代码要保证业务上的一致性

I隔离性由MVCC来保证

D持久性由内存+redo log来保证，MySQL修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

InnoDB redo log 写盘，InnoDB事务进入 prepare状态。
如果前面 prepare 成功，bin1og写盘，再继续将事务日志持久化到 bin1og，如果持久化成功，那么、工nnoDB事务则进入commit 状态(在 redo log 里面写一个commit记录)

redo log的刷盘会在系统空闲时进行

什么是MVCC？

MVCC (Multi-version Concurrency Control，多版本并发控制)指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SIELCT操作时访问记录的版本键的过程。可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READCOMMITTD、REPEATABLEREAD这两个隔离级别的一个很大不同就是:生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个readView，而REPEATABLEREAD只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

 

简述MyISAM和InnoDB的区别

MylSAM:

• 不支持事务，但是每次查询都是原子的;
• 支持表级锁，即每次操作是对整个表加锁;
• 存储表的总行数;
• 一个MYISAM表有三个文件:索引文件、表结构文件、数据文件;
• 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

lnnoDB:

• 支持ACID的事务，支持事务的四种隔离级别;
• 支持行级锁及外键约束:因此可以支持写并发;
• 不存储总行数;
• 一个InnoDB引擎存储在一个文件空间(共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里)，也有可能为多个（(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制;
• 主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值;因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引;最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

 

如何实现分库分表

将原本存储于单个数据库上的数据拆分到多个数据库，把原来存储在单张数据表的数据拆分到多张数据表中，实现数据切分，从而提升数据库操作性能。分库分表的实现可以分为两种方式:垂直切分和水平切分。

水平:将数据分散到多张表，涉及分区键：

• 分库：每个库结构一样，数据不一样，没有交集。库多了可以缓解io和cpu压力
• 分表：每个表结构一样，数据不一样，没有交集。表数量减少可以提高sql执行效率、减轻cpu压力

垂直：将字段拆分为多张表，需要一定的重构：

• 分库：每个库结构、数据都不—样，所有库的并集为全量数据
• 分表：每个表结构、数据不一样，至少有一列交集，用于关联数据，所有表的并集为全量数据

 

MySQL主从同步原理

MySQL主从同步的过程:

MySQL的主从复制中主要有三个线程: master (bin1og dump thread) . s1ave (I/O thread . SQL thread> , Master—条线程和Slave中的两条线程。

• 主节点bin log，主从复制的基础是主库记录数据库的所有变更记录到 bin log。bin log 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
• 主节点 log dump线程，当bin log有变动时，log dump线程读取其内容并发送给从节点。
• 从节点I/O线程接收bin log内容，并将其写入到 relay log文件中。
• 从节点的SQL线程读取relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 bin log文件+ position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从position 的位置发起同步。

 

由于MySQL默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入bin log后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

 

MySQL中索引类型及对数据库的性能影响

普通索引：允许被索引的数据列包含重复的值。唯—索引:可以保证数据记录的唯—性。

主键：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。

联合索引：索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。

全文索引：通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

索引可以极大的提高数据的查询速度。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

 

Explain语句中结果中的字段分别表示什么？

explain概念：

使用explain可以模拟优化器执行SQL查询语句，从而知道MySQL怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈。

列名	描述
id	查询语句中出现一个select关键字，MySQL就会给它分配一个唯一的ID值，某些子查询会被优化为join查询，那么出现的ID会一样
select_type	select关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的查询方式（全表扫描、索引）
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际上使用到的索引长度
ref	当使用索引列等值查询时，与索引进行等值匹配的对象信息
rows	预估的需要读取的记录行数
filterred	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息，比如排序等

索引覆盖是什么

索引覆盖就是一个SQL在执行时，可以利用索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了，所需要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了

 

最左前缀原则是什么

当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a、b、c三作字段建立了一个联合素引那么在写一个so时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a、b、c三个字段的联合索引时，底层的B+树是按照a、b、c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则

 

lnnoDB是如何实现事务的

InnoDB通过Buffer Pool，LogBuffer，Redo Log,Undo Log来实现事务，以一个update语句为例:

1.Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中

2．执行update语句，修改Buffer Pool中的数据，也就是内存中的数据

3．针对update语句生成一个RedoLog对象，并存入LogBuffer中

4．针对update语句生成undolog日志，用于事务回滚

5，如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer Pool中所修改的数据页持久化到磁盘中

6.如果事务回滚，则利用undolog日志进行回滚

 

B树和B+树的区别，为什么MySQL要是使用B+树

B树的特点:

1、节点排序

2、一个节点了可以存多个元素，多个元素也排序了

B+树的特点

1、拥有B树罗点

2、叶子节点之间有指针

3、非叶子节点上的元素在叶子节点上都亢余了，也就是叶子节点中存储了所有的元素，并且排好顺序

MySQL索引使用的是B+树，因为索引是用来加快查询的，而B +树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得B+树的高度不会太高，在MySQL中一个InnoDB页就是一个B+树节点，一个InnoDB页默认16kb，所以一般情况下一颗两层的B+树可以存2000万行左右的数据，然后通过利用B+树叶子节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等SQL语句。

 

MySQL锁有哪些，如何理解

按锁粒度分类：

1、行锁：锁某行数据、锁粒度最小，并发度高

2、表锁：锁整张表、锁粒度最大，并发度低

3、间隙锁：锁的是一个区间

还可以分为：

1、共享锁：也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写

2、排它锁：也就是写锁，一个事务给某行数据加了写锁，其他事务也能读，但是不能加读锁，也不能写。比如：执行一个update，是不允许加读锁的。

还可以分为：

1、乐观锁：并不会真正的去锁某行记录，而是通过一个版本好来实现的。需要修改的时候会判断当前的版本号跟数据的版本号进行对比，如果不一样则不预约修改。

2、悲观锁：行锁、表锁等都是悲观锁

在事务的隔离级别实现中，就需要利用锁来解决幻读

 

MySQL慢查询该如何优化？

1、检查是否走了索引，如果没有则优化SQL利用索引

2、检查所利用的索引，是否烹最优索引

3、检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据

4、检查表中数据是否过多，是否应该进行分库分表了

5、检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

Hash表

hash索引的概念：（MySQL可选择此结构）

基于哈希表实现，只有匹配所有列的查询才有效。对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希码是一个较小的值，不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时保存指向每个数据行的指针。

对于索引key进行一次hash计算就可以定位出数据存储的位置，有时候B+Tree索引更加高效，仅能满足 '='，'in'，不支持范围查询会出现hash冲突问题

hash值：是一个十进制的整数，有系统随机给出（对象的地址值，是一个逻辑地址，是模拟出来得到地址，不是数据实际存储的物理地址）

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

 

 

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值;前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据;

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索;

哈希索引也没办法利用索引完成排序，以及like 'xxx%'这样的部分模糊查询(这种部分模糊查询，其实本质上也是范围查询)﹔

哈希索引也不支持多列联合索引的最左匹配规则;

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

总结：哈希索引限制多，只适用于一定的场合。而一旦适合哈希索引，它带来的性能提升将非常显著。

二叉树

定义：每个不超过2的有序树，是每个节点最多有两个子树的树结构。顶上的叫根结点，两边被称作“左子树”和“右子树”。

 

 

缺点：像如上图二叉树结构图，插入多条记录，右边会单边增加根节点，树的高度增加，查询减慢，不使用，比如主键

二叉树：每个节点最多含有两个子树的树称为二叉树;

二叉查找树：首先它是一颗二叉树，若左子树不空，则左子树上所有结点的值均小于它的根结点的值;若右子树不空，则右子树上所有结点的值均大于它的根结点的值;左、右子树也分别为二叉排序树;

满二叉树：叶节点除外的所有节点均含有两个子树的树被称为满二叉树;

完全二叉树：如果一颗二叉树除去最后一层节点为满二叉树，且最后一层的结点依次从左到右分布

红黑树

红黑树他不允许一边节点比另一边高的太多，他会自动旋转平衡

红黑树：红黑树是一颗特殊的二叉查找树，每个节点都是黑色或者红色，根节点、叶子节点是黑色。如果一个节点是红色的，则它的子节点必须是黑色的。

平衡二叉树(AVL)：一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

定义：本身就是一种特殊的二叉树，每个节点上都有存储位表示节点的颜色，可以是red或black

约束：每个节点是黑色或者红色，根节点为黑色，叶子节点(特指空节点)是黑色，每个红色节点的子节点都是黑色的，任何一个节点到其每一个叶子节点的所有路径上黑色节点数相同

特点：速度特别快，趋近平衡树，查找叶子元素最少和最多次数不多余二倍

缺点：当MySQL数据量很大时，增加一条记录，数据需要平衡一次，非常消耗性能

B-Tree

叶节点的指针可以为空，所有索引元素不重复，节点中的数据索引从左到右递增排列

为什么mysql选择B+tree而不选择B-tree？

B树每个节点都存放了真实的数据，MySQL一个根节点数据存储为16KB，会导致每一个节点存储的数据量变小，所以B树的高度会变高，维护的代价大，查询修改性能会越来越低

B+Tree(MySQL默认)

B+Tree特点：非叶子节点不存储data，只存储索引（冗余），可有存放更多的节点，叶子节点包含了所有索引字段，所有的数据都存放在叶子节点上，叶子节点使用指针访问，提升区间访问性能，从左至右递增。双向指针

 

 

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。

 

数据结构在线演示：Data Structure Visualization (usfca.edu)

 

联合索引底层结构

联合索引根据建表的先后顺序先后建立索引排序顺序，比较相等时，先比较第一列的值，如果相等，再继续比较第二列，以此类推。

联合索引的索引字段中有一个值为null，则将其放在叶子节点的最前面；可以认为null值是最小的。

使用联合索引时，索引列的定义顺序将会影响到最终查询时索引的使用情况。例如联合索引（a,b,c），MySQL会从最左边的列优先匹配，如果最左边的带头大哥没有使用到，在未使用覆盖索引的情况下，就只能全表扫描，如果遇到 > 、<、 between等这样的范围查询，那B+树中也就无法对下一列进行等值匹配了，注意字符串也必须使用单引号做判断，否则也无法作比较