mysql索引

本文主要是介绍mysql索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一.什么是索引？

一般的应用系统，读写比例在10:1左右，

而且插入操作和一般的更新操作很少出现性能问题，
在生产环境中，我们遇到最多的，也是最容易出问题的，
还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。
说起加速查询，就不得不提到索引了。
比如下面这个数据表，如果 Mysql 没有实现索引算法
，那么查找 id=7 这个数据，那么只能采取暴力顺序遍历查找，
找到 id=7 这个数据需要比较 7 次，如果这个表存储的是 1000W 个数据，
查找 id=1000W 这个数据那就要比较 1000W 次，这种速度是不能接受的。

索引的本质

索引的本质：索引是帮助Mysql高效获取数据的排好序的数据结构。

MySQL若不建立索引，查找某条数据时则会逐行扫描，每扫描一行数据就会做一次磁盘IO。

二、Innodb(按的no逼病) 引擎和 Myisam （米少母）引擎的实现和原理

Mysql 底层数据引擎以插件形式设计，最常见的是 Innodb (按的no逼病)
引擎和 Myisam （米少母）引擎，用户可以根据个人需求选择不同的
引擎作为 Mysql 数据表的底层引擎。我们刚分析了，
B+树作为 Mysql 的索引的数据结构非常合适，
但是数据和索引到底怎么组织起来也是需要一番设计，
设计理念的不同也导致了 Innodb(按的no逼病) 和 Myisam（米少母） 的出现，各自呈现独特的性能。

MyISAM （米少母）虽然数据查找性能极佳，但是不支持事务处理。
Innodb(按的no逼病) 最大的特色就是支持了 ACID 兼容的事务功能，
而且他支持行级锁。

知识点代入

事务的四大特性（ACID）

1.原子性（A）：是指事务要么都成功，要么都失败。成功就影响数据库，失败就对数据库不影响，保持原样。

2.一致性（C）：是指应用层系统从一种正确的状态，在事务成功后，达成另一种正确的状态。比如：A、B账面共计100W，A向B转账，加上事务控制，转成功后，他们账户总额应还是100W，事务应保持这种应用逻辑正确一致。还有，转账（事务成功）前后，数据库内部的数据结构--比如账户表的主键、外键、列必须大于0、Btree、双向链表等约束需要是正确的，和原来一致的。

3.隔离性（I）：隔离是指当多个事务提交时，让它们按顺序串行提交，每个时刻只有一个事务提交。但隔离处理并发事务，效率很差。所以SQL标准制作者妥协了，提出了4种事务隔离等级（1，read-uncommited 未提交就读，可能产生脏读  2，read-commited 提交后读  可能产生不可重复读  3，repeatable-read 可重复读  可能产生幻读    4，serializable 序列化，最高级别，按顺序串行提交） 事务的隔离性实现详见https://zhuanlan.zhihu.com/p/27035174

4.持久性（D）：是指事务一旦提交后，对数据库中的数据改变是永久性的。

以上AID是数据库的特性，C是应用层特性，AID是为C服务的，目的就是要保证逻辑能正确的执行。

行级锁

每次锁定的是一行数据的锁机制就是行级别锁定(row-level)。
行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的

1. 优点

由于锁粒度小，争用率低，并发高。
2. 缺点

实现复杂，开销大。
加锁慢、容易出现死锁

行级锁定实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。其他注意事项：

在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
隐式加锁：

InnoDB自动加意向锁。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；
查看行级锁争用情况
show status like 'InnoDB_row_lock%';

mysql锁在4种事务隔离级别里的应用

事务的四种隔离级别有：

 读未提交(Read Uncommitted)

此时select语句不加任何锁。此时并发最高，但会产生脏读。

 读提交(Read Committed, RC)

普通select语句是快照读

update语句、delete语句、显示加锁的select语句（select … in share mode 或者 select … for update） 等，除了在外键约束检查和重复键检查时会封锁区间，其他情况都只使用记录锁；

 可重复读(Repeated Read, RR)

普通select语句也是快照读

update语句、delete语句、显示加锁的select语句（select … in share mode 或者 select … for update）则要分情况：

在唯一索引上使用唯一的查询条件，则使用记录锁。如: select * from user where id = 1;其中id建立了唯一索引。

在唯一索引上使用 范围查询条件，则使用间隙锁与临键锁。如: select * from user where id >20;

 串行化(Serializable)

此时所有select语句都会被隐式加锁：select … in share mode

快照读、当前读
MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。
快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
什么是多版本并发控制？
MVCC定义：多版并发控制系统。可认为是行级锁的一个变种，它能够避免更多情况下的加锁操作。

作用：避免一些加锁操作，提升并发性能。

实现：通过在每行记录的后面保存行的创建时间和过期时间或删除时间（它们是隐藏的），这两个时间实际都是系统的版本号。每开始一个新的事务，版本号都会自动增加。

具体原理
 select：innoBD(按的no逼病)查询时会检查以下两个条件：一个是数据行的版本号早于当前事务的版本号；另一个是行的删除版本号，要么没有，要么大于当前事务的版本号。

insert/delete：innoDB(按的no逼病)将当前的系统版本号作为新插入(删除)的数据行的版本号。

update：先新插入一行数据，并将当前系统版本号作为行的版本号，同时将当前系统版本号作为原来行的删除版本号。更新主键时，聚集索引和普通索引都会产生两个版本；而更新非主键时，只要普通索引会产生两个版本
快 照 读 是 哪 些
一个正常的select…语句就是快照读。

快照读，使得在RR（repeatable read）级别下一个普通select...语句也能做到可重复读。即前面MVCC里提到的利用可见版本来保证数据的一致性。

当 前 读 是 哪 些
insert语句、update语句、delete语句、显示加锁的select语句（select… LOCK IN SHARE MODE、select… FOR UPDATE）是当前读。

为什么insert、update、delete语句都属于当前读？

这是因为这些语句在执行时，都会执行一个读取当前数据最新版本的过程。

当前读的SQL语句，InnoDB(按的no逼病)是逐条与MySQL Server（mysql色我）交互的。即先对一条满足条件的记录加锁后，再返回给MySQL Server（mysql色我），当MySQL Server（mysql色我）做完DML操作后，再对下一条数据加锁并处理。

Innodb (按的no逼病)创建表和Myisam （米少母）创建表不同

Innodb(按的no逼病) 创建表和后生成的文件有：

frm:创建表的语句
idb:表里面的数据+索引文件
Myisam（米少母） 创建表后生成的文件有

frm:创建表的语句
MYD:表里面的数据文件（myisam data）
MYI:表里面的索引文件（myisam index）
从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM（米少母） 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb (按的no逼病)引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。

非聚集索引方式

MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。
我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了

索引的查询数据的流程

添加索引---->生成对应字段的索引树---->该字段的索引树的叶子节点同样是记录了对应数据的物理地址------->物理地址去数据文件里定位到具体的数据记录

聚集索引方式

InnoDB (按的no逼病)是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB(按的no逼病) 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB(按的no逼病) 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。
原理是
建表的时候 InnoDB(按的no逼病) 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB(按的no逼病) 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

流程

自动建立好主键 ID 索引树---->加索引--->建立 索引 B+树(节点里存的是 这个 KEY，叶子节点存储的数据的是主键 KEY)----->拿到主键 KEY 后，InnoDB(按的no逼病) 才会去主键索引树里根据刚在 创建 索引树找到的主键 KEY 查找到对应的数据

面试问题

为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，
InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，
那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。
从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，
通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

InnoDB 和 MyISAM 特点对比？

MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：
MyISAM 直接找到物理地址后就可以直接定位到数据记录，
但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，
才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，
那当然 MyISAM 查询性能更高。

三.B+树 二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。

树是由节点和边构成，储存元素的集合。节点分根节点、父节点和子节点的概念。

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次 插入的动画 Java代码的实现：

https://blog.csdn.net/qq_40005658/article/details/91344583
基本操作
https://my.oschina.net/indestiny/blog/208297

b+树的查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，
此时发生一次IO，在内存中用二分查找确定29在17和35之间，
锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）
可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，
发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，
通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，
结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，
如果上百万的数据查找只需要三次IO，性能提高将是巨大的，
如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，
假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，
当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，
磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，
数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，
比如int占4字节，要比bigint8字节少一半。
这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，
一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.索引的最左匹配特性（即从左往右匹配）：
当b+树的数据项是复合的数据结构，
比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，
比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，
最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，
b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，
必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，
但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，
然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

为什么B-tree好，为什么？

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，
I/O存取的消耗要高几个数量级，
所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

为了达到这个目的，磁盘按需读取，
要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
  并把B-tree中的m值设的非常大，就会让树的高度降低，有利于一次完全载入

四。索引的分类

1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

索引的查询类型

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样
InnoDB(按的no逼病) 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM（米少母）  不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

索引的使用和注意

查看索引  show index from 数据库表名
alter table 数据库add index 索引名称(数据库字段名称)
PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `col·）


多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )


CREATE TABLE `order` (
  `id` int(255) NOT NULL AUTO_INCREMENT COMMENT '订单号',
  `name` varchar(255) DEFAULT NULL COMMENT '商品的名称',
  `price` double(10,2) DEFAULT NULL COMMENT '商品的价格',
  `starttime` datetime(3) DEFAULT NULL COMMENT '订单开始的时间',
  `endtime` datetime(3) DEFAULT NULL COMMENT '订单结束的时间',
  `status` int(255) DEFAULT NULL COMMENT '订单的状态',
  `shop` varchar(255) NOT NULL COMMENT '订单的店铺的名称',
  `number` int(255) NOT NULL COMMENT '订单的数目',
  PRIMARY KEY (`id`),
  UNIQUE KEY `orderid` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

//order的主键是id  自增  不能null  主键一个表一般只有一个主键  
ALTER table `order` drop   PRIMARY  key;  //删除主键
//创建唯一索引  数据不能重复  唯一
CREATE  UNIQUE  index orderid on `order`(id)
//删除唯一索引  删除不了因为id自增
drop  UNIQUE  index orderid on `order`
//联合唯一索引  商品id和订单id唯一联合索引  一般连接表  2个不会再次出现  1  1情况
CREATE  UNIQUE  index orderid on `order`(id,name)

商品id     订单id

          1

//2个唯一索引 的时候联合的数据不能重复
//创建索引  提高查询速度
CREATE index  shopp on `order`(number)
// 删除索引
drop    index shopp  on `order`
命中索引
类型不一样  name-----字符串类型
SELECT * FROM `order` WHERE name=123
使用不等于
SELECT * FROM `order` WHERE `name`!=123
SELECT * FROM `order` WHERE `name` not  in("123")
主键可以
//name有索引  但是id没有  使用or不能命中索引
SELECT * FROM `order` WHERE name="123" or id=1
or后面有and  命中
SELECT * FROM `order` WHERE name="123" or id=1 and  status=1
这个字段是索引 但是现在对他进行排序 就命中不了索引
SELECT * FROM `order` WHERE name="123" order by `name` asc
//主键可以
使用函数
假设  使用函数 时间格式化  starttime 这个字段假设是加上索引
SELECT * FROM  big  WHERE DATE_FORMAT(starttime,format)="2021-10-10"
//下面就可以命中 对查询使用函数
SELECT * FROM  big  WHERE starttime=DATE_FORMAT("2021-10-10",format)
返回  sum 和count  返回当前字段是索引也是不会命中的
最左原则

CREATE  UNIQUE  index orderid on `order`(id,name)
已id进行查询  连表的化已主表索引为主 id  or name  不能命中索引 

索引失效情况
模：模糊查询LIKE以%开头
型：数据类型错误
数：对索引字段使用内部函数
空：索引列是NULL
运：索引列进行四则运算
最：复合索引不按索引列最左开始查找
快：全表查找预计比索引更快​

面试问题

索引的好处和作用和不同

索引：相当于书的目录，所以索引是一个单独的，物理的数据结构，
它与目录一样，也是单独存在的。但索引的建立依赖于表的建立。
作用：1.加快表之间的连接速度 2.加快数据检索速度 3.保证数据记录的唯一性，唯一性索引 4.减少查询的时间 索引的类型： 不重复的，称为：唯一索引 如果索引不唯一，称为 ：单列索引 多列组合的索引，
称为：复合索引 根据索引的顺序与数据表的物理顺序是否形同，
索引分为两种类型， 聚集索引和非聚集索引 聚集索引：根据行的键值，在表或视图中排序和存储数据行，每个表只能有一个聚集索引。
非聚集索引：一个表可以创建多个非聚集索引，
最多可以有249个非聚集索引。
9.1索引的使用 考虑创建索引的列
1.主键列上 2.经常用在连接的列上 3.经常需要范围查询的列上 4.经常要排序的列上 不考虑创建索引的列 1.从来不查询的列 2.选择性低（重复值多）的列 
3.小表（记录少的） 4.更新比较频繁的

B树与B+树的性能比较

查询过程看上去跟B树差不多，但还是有两点不同的，
首先，B+树中间节点没有数据，只存索引数据，所以同样大小的磁盘页可以容纳更多的节点元素，这就意味着，
数据量相同的情况下B+树比B树更加的”矮胖“，相应会减小IO次数。
其次，B+树的查询必须最终查找到叶子节点，
而B树只要找到匹配元素即可，无论匹配元素处于中间节点还是叶子节点。

这篇关于mysql索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！