MySQL InnoDB索引介绍_mysql innodb 主键索引-程序员宅基地

技术标签： MySQL mysql

MySQL InnoDB索引介绍

什么是索引

索引简单来说就是一个目录，能够标记该内容的在书中的位置，启动快速查询内容的目的。而MySQL的索引是一种单独的数据结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

InnoDB索引的特点

聚簇索引

聚簇索引（Clustered Index）是InnoDB特殊的索引，直接存储了行数据。通常情况下聚簇索引与主键索引具有相同的含义。由于，通过聚簇索引可以直接访问包含行数据的数据页，能够减少磁盘的I/O次数。
InnoDB选取聚簇索引的规则：
1. 如果定义了主键索引，则InnoDB会将该主键索引作为聚簇索引；
2. 如果没有定义主键索引，InnoDB则会选择第一个所有列都不能为NULL的唯一索引作为主键索引。
3. 如果即没有主键索引，也没有合适的唯一索引，InnoDB会在包含row ID的列上增加一个名为GEN_CLUST_INDEX的隐藏聚簇索引。其中行 ID 是一个 6 字节字段，随着新行的插入而单调增加，因此，表数据是按行ID排序就是按插入顺序排列的。

辅助索引

在InnoDB中，其他非聚簇索引都称为辅助索引（Secondary Index）。每个表最多包含64个辅助索引。每一个辅助索引记录都是由索引所包含的列数据和主键索引包含的列数据组成，其中主键索引包含的列数据用于查找聚簇索引上的行数据。可以看出主键索引的列被冗余存储，所以其值的长度越小，可以节省辅助索引的存储空间。

B+树

InnDB使用B+树索引。B+树能够支持 =, >, >=, <, <=, BETWEEN 和非%开头的like操作。

MySQL如何使用索引

MySQL通过索引与where条件快速匹配查找数据；
当有多个索引时，MySQL通常选择返回行数最少的索引；
一个索引包含多个列时，MySQL只能使用索引的最左前缀来查找数据，例如：索引包含三列(col1, col2, col3)，则只有该三种情况(col1), (col1, col2)和 (col1, col2, col3)可以使用索引。
在联合查询中，MySQL对于类型和字段大小相同的列能够更有效的使用索引。如果 VARCHAR 和 CHAR 声明为相同的大小，则将它们视为相同。例如：对于VARCHAR(10) 和 CHAR(10) 的大小相同，但 VARCHAR(10) 和 CHAR(15) 的大小不同。
在联合查询中，对于非二进制字符串的列，必须使用相同的字符集才能使用索引，如UTF-8与latin1不能使用索引。
如果查询的值(select中的列值)都包含在整个索引中，此时MySQL直接使用索引中的值，而不会再次获取查询行数据（叫做覆盖索引Covering Index）。
有时 MySQL 不使用索引，即使有可用的索引。因为在一些情况下，优化器评估使用索引将需要 MySQL 访问表中很大比例的行，此时表扫描可能会更快，因为它需要更少的查找。但是，如果查询结果只需要某些列的值，MySQL 无论如何都会使用索引，因为它可以直接使用索引值返回。

查询优化

主键优化

主键由一个或者多个非NULL列组成，并代表了最主要的查询，而且创建时默认会关联创建一个唯一索引。由于非NULL特性和索引能够提供更好的查询效率。如果一个表没有一个或者一组列可作为主键，可以额外创建一个具有自动增量值的单独列作为主键，该自增唯一ID也可以作为连接查询时的外键使用，提高查询效率。

外键优化

一个大表如果有许多列，且会查询许多不同列的组合，则可以将不常用的数据拆分到每个包含少量字段的单独小表中。每个小表可通过复制主表的主键ID与主表进行关联。这样，每个小表都可以通过主键来快速查找其数据，并可以使用联接操作仅查询所需的列集。为了最大限度地提高性能，MySQL会尝试从磁盘读取尽可能少的数据块。而对于小表来说，每个数据块中可以容纳更多行，这样MySQL一次性读取的数据就更多，只需更少的IO操作和占用更少的内存。

索引前缀

对于字符类型的列，可以通过colname(N) 来制定前N个字符作为索引。而BLOB和TEXT类型的列，则必须为索引指定前缀的长度。InnoDB支持最大前缀长度为767字节（innodblarge_prefix打开时最大支持3072字节）。
如果查询条件的长度大于索引前缀长度，MySQL会先通过索引来排出不满足条件的行，然后再对剩余的行进行匹配。

多列索引

多列索引即由多个列组成的索引（只包含一个列的索引，称为单列索引），也可以称为组合索引。MySQL中一个多列索引最多包含16列。查询条件必须满足最左前缀才能使用索引，特别是or关联的查询条件，不用用于左前缀匹配。

索引的使用成本

索引需要占用额外的存储空间；
在插入、删除、更新数据时，都需要更新索引；
Mysql在查询过程中，需要花时间来判断使用哪个索引，索引增多就增加了选择索引的时间成本。

由于使用索引存在成本，索引不是建立的索引越多越好，要在查询效率和索引使用成本之间取得平衡。

更多内容参见
懒蚂蚁 · 技术漫谈

本文链接：https://blog.csdn.net/happyLazyAnt/article/details/132081787

原作者删帖不实内容删帖广告或垃圾文章投诉

智能推荐

微信小程序源码案例大全_微信小程序switch页面demo-程序员宅基地

文章浏览阅读4.3k次，点赞4次，收藏62次。微信小程序demo：足球，赛事分析小程序简易导航小程序demo：办公审批小程序Demo：电魔方小程序demo：借阅伴侣微信小程序demo：投票微信小程序demo：健康生活小程序demo：文章列表demo 微商城（含微信小程序）完整源码+配置指南微信小程序Demo：一个简单的工作系统微信小程序Demo：用于聚会的小程序微信小程序Demo：Growth 是一款..._微信小程序switch页面demo

SLAM学习笔记(Code2)----刚体运动、Eigen库_eigen.determinant-程序员宅基地

文章浏览阅读2.2k次。2.1除了#include<iostream>之外的头文件#include <Eigen/Core>//Core:核心#include <Eigen/Dense>//求矩阵的逆、特征值、行列式等#include <Eigen/Geometry>//Eigen的几何模块，可以利用矩阵完成如旋转、平移/***其他***/#include <ctime>//可用于计时，比较哪个程序更快#include <cmath>//包含a_eigen.determinant

图像梯度-sobel算子-程序员宅基地

文章浏览阅读1w次，点赞12次，收藏61次。（1）理论部分x 水平方向的梯度，其实也就是右边 - 左边，有的权重为1，有的为2 。若是计算出来的值很大说明是一个边界。y 竖直方向的梯度，其实也就是下面减上面，权重1，或2 。若是计算出来的值很大说明是一个边界。图像的梯度为：有时简化为：即：（2）程序部分函数：Sobelddepth 通常取 -1，但是会导致结果溢出，检测不出边缘，故使..._sobel算子

cuda10.1和cudnn7.6.5百度网盘下载链接（Linux版）_cudnn7.6网盘下载-程序员宅基地

文章浏览阅读3.6k次，点赞17次，收藏8次。cuda10.1和cudnn7.6.5百度网盘下载链接（Linux版）在官网下载不仅慢，，，主要是还总失败。。终于下载成功了，这里给出百度网盘下载链接，希望可以帮到别人百度网盘下载链接提取码: vyg5_cudnn7.6网盘下载

Python正则表达式大全-程序员宅基地

文章浏览阅读9.3w次，点赞69次，收藏427次。定义：正则表达式是对字符串（包括普通字符（例如，a 到 z 之间的字母）和特殊字符（称为“元字符”））操作的一种逻辑公式，就是用事先定义好的一些特定字符、及这些特定字符的组合，组成一个“规则字符串”，这个“规则字符串”用来表达对字符串的一种过滤逻辑。正则表达式是一种文本模式，该模式描述在搜索文本时要匹配的一个或多个字符串。上面都是官方的说明，我自己的理解是（仅供参考）：通过事先规定好一些特殊字符的匹配规则，然后利用这些字符进行组合来匹配各种复杂的字符串场景。比如现在的爬虫和数据分析，字符串校验等等都需要用_python正则表达式

Vue之条件渲染_条件渲染的基本概念-程序员宅基地

文章浏览阅读973次。条件渲染就是在指定的条件下，渲染出指定的UI。比如当我们显示主页的时候，应该隐藏掉登录等一系列不相干的UI元素。即UI元素只在特定条件下进行显示。而在VUE3中，这种UI元素的显示和隐藏可以通过两个关键字，`v-if` 和`v-show`来实现。但是虽然实现的功能一样，但他们两者有着一些细微的区别。总结起来这个区别就是：v-show控制UI元素隐藏时只是将UI的显示状态变成了不可见，实际上这个UI是存在的，但是v-if隐藏UI元素时则是直接干掉了这个UI元素，使其不显示_条件渲染的基本概念

随便推点

NILM（非侵入式电力负荷监测）学习笔记 —— 准备工作（一）配置环境NILMTK Toolkit_nilmtk学习-程序员宅基地

文章浏览阅读1.9w次，点赞27次，收藏122次。安装Anaconda，Python，pycharm我另一篇文章里面有介绍https://blog.csdn.net/wwb1990/article/details/103883775安装NILMTK有了上面的环境，接下来进入正题。NILMTK官网：http://nilmtk.github.io/因为官方安装流程是基于linux的（官方安装流程），我这里提供windows..._nilmtk学习

k8s-pod 控制器-程序员宅基地

文章浏览阅读826次，点赞20次，收藏28次。如果实际 Pod 数量比指定的多那就结束掉多余的，如果实际数量比指定的少就新启动一些Pod，当 Pod 失败、被删除或者挂掉后，RC 都会去自动创建新的 Pod 来保证副本数量，所以即使只有一个 Pod，我们也应该使用 RC 来管理我们的 Pod。label 与 selector 配合，可以实现对象的“关联”，“Pod 控制器” 与 Pod 是相关联的 —— “Pod 控制器”依赖于 Pod，可以给 Pod 设置 label，然后给“控制器”设置对应的 selector，这就实现了对象的关联。