MySQL数据库——索引机制及其优化

最新推荐文章于 2023-07-08 09:54:18 发布

至，若春和景明

最新推荐文章于 2023-07-08 09:54:18 发布

阅读量615

点赞数

文章标签： b树数据结构

本文链接：https://blog.csdn.net/weixin_51328633/article/details/127119305

版权

基础知识储备

局部性原理

发现程序和数据的访问都有聚集成群的倾向，在一段时间内，仅使用其中一小部

分（也称空间局部性），或者最近访问过得程序代码和数据，很快又被访问的可

能性很大（也称时间局部性）。

磁盘预读（预读的长度一般为页（page）的整数倍）

页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相

等的块，每个存储块称为一页(在许多操作系统中，页大小通常为4k)，主存和

磁盘以页为单位交换数据。

索引是什么?

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

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

索引存储在文件系统中

索引的文件存储形式与存储引擎有关

索引文件的结构 hash

二叉树

B树

B+树

示例：

mysql数据文件
id	name
1	teacher.MA
2	teacher.ZHOU
3	teacher.ZHANG
4	teacher.LIAN

hash表的索引格式

index（0---7）

缺点:

1.利用hash存储的活需要将所有的数据文件添加到内存，比较耗费内存空间.

2.如果所有的查询都是等值查询，那么hash确实很快，但是在企业或者实际工作环境中范围查找的数据更多，而不是等值查询，因此hash就不太适合了。

二叉树与红黑树的索引格式

缺点: 无论是二叉树还是红黑树，都会因为树的深度过深而造成io次数变多，影响数据读取的效率。

B树的索引格式

B树特点:

1、所有键值分布在整颗树中

2、搜索有可能在非叶子结点结束，在关键字全集内做一次查找性能逼近二分查找

3、每个节点最多拥有m个子树

4、根节点至少有2个子树

5、分支节点至少拥有m/2颗子树(除根节点和叶子节点外都是分支节点)

6、所有叶子节点都在同一层、每个节点最多可以有m-1个key,并且以升序排列

实例图说明: 每个节点占用一一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为16 和34, P1指针指向的子树的数据范围为小于16, P2指针指向的子树的数据范围为16~34，P3指针指向的子树的数据范围为大于34。查找关键字过程:

1、根据根节点找到磁盘块1,读入内存。[磁盘 I/O操作第1次]

2、比较关键字28在区间(16,34) ，找到磁盘块1的指针P2。

3、根据P2指针找到磁盘块3,读入内存。[磁盘 I/O操作第2次]

4、比较关键字28在区间(27,29)，找到磁盘块3的指针P2。

5、根据P2指针找到磁盘块8，读入内存。[磁盘 I/O操作第3次]

6.在磁盘块8中的关键字列表中找到关键字28。

缺点:

1、每个节点都有key,同时也包含data,而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储的key数重变小

2、当存储的数据量很大的时候会导致深度较大，增大查询时磁盘io次数，进而影响查询性能

mysql索引数据结构——B+Tree

B+Tree是在BTree的基础之.上做的一种优化，变化如下:

1、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快

2、非叶子节点存储key,叶子节点存储key和数据

3、叶子节点两两指针相互连接(符合磁盘的预读特性)，顺序查询性能更高

注意:在B+Tree上有两个头指针，一个指向根节点，另一-个指向关键字最小的叶子节点，而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算: 一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

索引的分类

mysq|索引的五种类型:主键索引、唯一索引、普通索引和全文索引、组合索引。通过添加索引可以

提高数据的读取速度，提高项目的并发能力和抗压能力。

主键索引

主键是一种唯一性索引，但它必须指定为PRIMARY KEY,每个表只能有一个主键。

唯一索引

索引列的所有值都只能出现-次，即必须唯- ，值可以为空。

普通索引

基本的索引类型，值可以为空，没有唯-性的限制。

全文索引

全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、 text类型的列上创建

组合索引

多列值组成个索引，专J用于组合搜索

mysql的存储引擎

	MyISAM	InnoDB
索引类型	非聚簇索引	聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	是	是(5.6后支持)
适合操作类型	大量select	大量insert、delete、 update

索引基础知识

1.索引的优点

1、订单减少了服务器的需要扫描的数据量

2、帮助服务器避免排序和临时表

3、将随机IO编程顺序IO

2.索引的用处

1、快速查找匹配where子句的行

2、从consideration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用最少行的索引

3、如果表具有具有多行索引，则优化器可以使用索引的任何最左前缀来查找行

4、当有表连接的时候，从其他表检索行数据

5、查找特定索引列的min或max值

6、如果排序或分组是在可用索引的最左前缀上完成的，则对表进行排序和分组

7、在某些情况下，可以可以优化查询以检索值而无需查询数据行

3.索引的优势

1）提高数据检索效率，降低磁盘IO成本

2）通过对数据的排序，降低排序成本

4. 索引的劣势

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

5. 什么情况下适合建立索引

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

6. 什么情况下不适合建立索引

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

7. 什么时候会出现索引失效

1）like以通配符开头('%abc')会导致索引失效，违反最左前缀法则

最左前缀法则：

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

8. 为什么建议InnoDB必须建主键

对于InnoDB来说，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集

索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。为什么建议InnoDB

必须建主键呢？因为本身数据库的资源就非常宝贵，我们尽量能手动做的就不要麻烦MySQL

去帮我们维护，说白了就是降低数据库开销。

9. 为什么推荐使用整型主键

我们就拿UUID举个例子，一大串十分长但无具体意义的字符串，

回顾上面InnoDB的索引图，是比较两个int型数据快捷呢还是比较

两个字符串快捷呢？想都不用想肯定是比较两个int型更具有优势，

字符串需要逐位的去比较，如果碰巧两个字符串只有最后一位不一

致那不是亏得要死。

10. 为什么推荐使用自增主键

上文B+Tree第三条特性：叶子节点用指针连接，提高区间访问性能。

这样带来了一个好处那就是范围查找，比如一行SQL：select * from table

name where id between 1 and 20，MySQL只需要查到索引等于1的位置，

然后通过链表往后依次找到20的位置，首尾位置之间就是我们需要查找的结果集。

但这样也带来了一个问题，加入我们主键已经插入了1、2、3、4、6、7，这时候我

们插入了5，MySQL在维护索引的时候就会打破原有链表顺序，导致链表节点分裂重排，从而消耗性能。

11. 为什么InnoDB非主键索引存储的是主键值

保持一致性，当数据库表进行DML操作时，同一行记录的页地址会发生改变，

因非主键索引保存的是主键的值，无需进行更改。同时还可以节省存储空间，

因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了，如果普通索引还

继续再保存一份数据，就会导致有多少索引就要存多少份数据。

面试技术名词

回表 ：innodb对普通列建立索引时，最后一行存储的并不是整行数据，放的是主键，那么第一次查询会根据name列查找，但是信息并不在该B+树中，所以查找到主键之后，查找主键的B+树。这就是回表。比如select * from emp where name = 1;先找name列的B+树，找到后再找主键B+树。

覆盖索引：每次查数据时，发现都会先查到主键值，再从主键B+树找数据，有两次B+树，但是如果查询时，包含了所有的字段，不需要回表操作，那么就是覆盖索引。比如 select id from emp where name = 1;发现主键ID已经有了，不需要回表。最左匹配：建索引时，有可能遇到多个列创建索引，比如包含name与age建立的索引，比如写select * from emp where name = ? and age = ?;此时会先匹配name，再匹配age，但是如果select * from emp where age = ?；相当于把那name跨过去了，那么此时不能用这个索引了。必须先有最左边，再有最右边。否则查不了。这就是最左匹配。可以用age + name建立索引，此时可以匹配到age；或者对age再建立一个索引都可以解决。只有组合索引才有最左匹配。谓词下推：比如select t1.name,t2.name from t1 join t2 on t1.id = t2.id;假设t1表有10条，t2有20条。有两种组织形式，一种为将t1与t2按照id进行关联，整体20列再取name。另外一种，将t1.name t2.name与t1.id与t2.id取出来，再按照id关联。那么第二种方式比较好，这是谓词下推。

索引下堆：比如有个组合索引（name，age）。Where name = ‘’ and age = ‘’。第一种方式，在存储引擎先取出所有name等于的值先去出来，再到server层时，再把age过滤。高版本时，第二种方式，在存储引擎对age过滤掉。这就意味着server层读数据时的IO量少了，那么效率高了。

索引采用的数据结构

常见的mysql主要有两种结构：hash索引和B+tree索引，我们使用的是innoDB引擎，默认是B+树

索引的匹配方式

mysql官网下载saklia相关zip;

登录mysql执行

source 命令导入.sql文件

source /root/sakila-schema.sql

source /root/sakila-data.sql

CREATE TABLE table_staffs (
id INT PRIMARY KEY auto_increment,
NAME VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) charset utf8 COMMENT '员工记录表';
CREATE TABLE table_staffs (id INT PRIMARY KEY auto_increment,  NAME VARCHAR (24) NOT NULL DEFAULT '' COMMENT 'name',  age INT NOT NULL DEFAULT 0 COMMENT 'age',  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT 'pos',    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'time') charset utf8 COMMENT 'table';

alter table staffs and index idx_nap(name,age,pos);

1>全值匹配:和索引中的所有列进行匹配;

2>最左前缀匹配:只匹配最左边几个前缀;

3>匹配列前缀:只匹配最左边几个前缀;%通配符最好不要用到最前面;

4>匹配某一个范围值:

5>精确匹配到某一列并范围匹配另外一列:可以查询第一列的全部和第二例的部分　　　　

6>只访问索引的查询:查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引:

出现using index代表出现索引覆盖

回表只有普通索引才存在;

哈希索引

基本介绍: 　　基于hash表的实现，只有精确匹配索引所有列的查询才有效; 　　在mysql中，只有memory的存储引擎显式支持hash索引　　 hash索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让hash索引查找的速度非常快; hash索引的限制:　　　

1>hash索引只包含hash值和行指针，而不存储字段值，索引不能使用索引中的值来进行避免读取行;

　　　　　　　　 hash值->行指针->行记录因为在memory中，所以特别快　　　　2>hash索引数据并不是按照索引值顺序进行存储的，所以无法进行排序; 　　　　3>hash索引不支持部分列的匹配查找,hash索引是使用索引列的全部内容来计算的hash值;(部分列的hash值和存储的hash值不一致) 　　　　4>hash索引支持等值比较查询，不支持任何范围查找; 　　　　　访问hash索引的数据非常快，除非有很多的hash冲突，当出现hash冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，知道找到所有符合条件的行　　　　　　避免hash冲突的方法:编写优秀的hash算法:hashmap中有扰度函数，让高位参与运算，地位不参与运算，为了减少hash冲突的可能性，因为如果hash冲突太多的时候链表非常长，改成链表的方式，时间复杂度比较低。　　　　5>hash冲突比较多的话，维护的代价比较大;

组合索引

组合索引:优化就是考虑组合索引的建立及其顺序

聚簇索引与非聚簇索引

聚簇索引:不是单独的索引类型，是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起.

　　优点:

　　　　1>可以把相关数据保存在一起

　　　　2>数据访问更快，因为索引和数据保存在同一个树中

　　　　3>使用覆盖索引扫描的查询可以直接使用页节点中的主键值

　　　缺点:

　　　　1>聚簇诗句最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势了

　　　　2>插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式->(插入和删除涉及到的拆分合并浪费了很多IO和空间)

　　　　3>更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置

　　　　4>基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题

　　　　5>聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

　　　　索引维护很麻烦，大批数据的迁移的时候，mysql默认给主键和唯一键创建索引，可以先将索引关掉再迁移，然后导完数据再打开，效率比较高一些

覆盖索引

->查询的字段值是不是都在索引中

　　如果一个索引包含所有需要查询字段的值，我们称之为覆盖索引->没有回表的过程

　　不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值

　　不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引

　　使用的优点:

　　　　1>索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少的数据访问量(IO量)

　　　　2>因为索引是按照列值顺序存储的，所以对于IO密集型(需要频繁的IO)的范围查询会比随机从磁盘读取每一行数据的IO要少的多

　　　　3>一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题;

　　　　4>由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用.(ibd是数据和索引存放的)

　　extra:是否显示using index

　　无论数据还是索引都是需要持久化的

优化小细节

1>当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层;

eg:select age from actor where age + 1= 5;尽量不要使用这种带有表达式的;　　　　　　　　　　system>const>ref>range>index>all

2>尽量使用主键查询，而不是其他索引，因此主键查找不会触发回表查询;　　　　　　

3>使用前缀索引->某个列开头的部分字符串，但是可能降低索引的选择性；varchar/blob/text使用的时候必须要使用前缀索引

　　eg:

　　创建数据表: create table citydemo(city varchar(50) not null);

　　插入数据表:insert into citydemo(city) select city from city;(执行多次直到select count(1) from citydemo=19200);

　　更新表中数据:update citydemo set city=(select city from city order by rand() limit 1)'

　　查询:

　　select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

　　select count(*) as cnt,left(city,3)as pref from citydemo group by city order by cnt desc limit 10;

　　查看数量是否和完整索引一致，一致采用left就可以，索引保存的少了;

select count(distinct left(city,3))/count() as sel3,count(distinct left(city,4))/count() as sel4,count(distinct left(city,5))/count() as sel5,count(distinct left(city,6))/count() as sel6, count(distinct left(city,7))/count() as sel7,count(distinct left(city,8))/count() as sel8 from citydemo;　　

　　取前面7个字节和整体的长度就完全一致了;取3的话，重复值太多，索引查询效率太低;

4>使用索引来进行排序

排序操作的时候，如果排序列中是索引的话一定要用，但是如果不是索引，使用文件的话IO量是很大的;设计索引的时候要尽可能满足查询和排序两种条件;

5>union all，in，or都可以使用到索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id =2;

分为两个阶段执行,如果必须用的话，用union all 不要用union，因为union包含disctinct的操作，如果没有重复数据要求的话 explain select * from actor where actor_id=1 or actor_id=2; explain select * from actor where actor_id in (1,2);

数据量大的时候，使用in查询效率高的特点更明显.

6>范围查询的索引列只能使用一个，但是依然建议范围查询使用索引，查询效率还是比较高的;

7>强制类型转换会全表扫描;

8>更新十分频繁，数据区分度不高的字段不宜建索引;更新频繁的情况维护成本太高

9>创建索引的列，不允许为null，可能会得到不符合预期的结果;实际业务需求中大部分还是允许为空的

10>当需要进行表连接的时候，最好不要超过3张表，因为需要join的字段，数据类型必须一致

mysql中join的实现方式；当使用索引的时候，非驱动表上面的列要有索引，可以通过索引来减少比较，加速查询，一般AjoinB还是BjoinA，都是优化器去决定的，除非使用constraint关键字Block nested-loop join:非驱动表没有索引，将驱动表的需要记录的列对应的放到join_buffer中，然后再去匹配非驱动表，匹配上就取出来这条记录，匹配不上跳过下一条记录;小表join大表当使用内连接的时候，and和where效果相同，当使用左外连接(右外连接)的时候，会把左表(右表)的数据全部取出.

11>如果只有一条结果返回，limit 1能够提高效率。

limit 1提高查询效率的原因: limit并不是用来做分页的，不加limit是逐行判断，加上limit就是扫到满足那行为止，能用limit的就尽量用limit.

12>单表索引建议控制在5个以内;

给表创建索引的时候，并不是索引越多越好，因为索引越多，索引文件就越多，维护越困难.

定义了varchar(10),实际存储的数据是null，内存和硬盘是不一样的，越大的话，内存消耗越大.

13>单索引字段数不允许超过5个(组合索引-最左匹配原则，容易浪费空间)

14>创建索引的时候避免使用索引越多越好，过早优化，在不了解系统的时候进行优化