杭州网站建设推荐q479185700上墙,wordpress get_tax_meta,1分钟视频制作报价明细,网站空间已过期目录 第一章、快速了解索引1.1#xff09;索引是什么1.2#xff09;为什么使用索引1.3#xff09;操作索引示例 第二章、索引分类2.1#xff09;按数据结构分类2.1.1#xff09;树型数据结构索引二叉树B树B 树 2.1.2#xff09;Hash数据结构索引2.1.3#xff09; 其他数… 目录 第一章、快速了解索引1.1索引是什么1.2为什么使用索引1.3操作索引示例 第二章、索引分类2.1按数据结构分类2.1.1树型数据结构索引二叉树B树B 树 2.1.2Hash数据结构索引2.1.3 其他数据结构索引 2.2按物理存储方式分类2.2.1聚簇索引聚集索引2.2.2非聚簇索引非聚集索引 2.3按索引字段的特性分类2.3.1普通索引2.3.2唯一索引2.3.3主键索引2.3.4多列索引又称组合索引复合索引多字段索引联合索引2.3.5前缀索引后缀索引2.3.6全文索引FULLTEXT 2.4按索引的字段个数分类2.4.1单列索引2.4.2重复索引2.4.3多列索引又称组合索引复合索引多字段索引联合索引2.4.4冗余索引 第三章、索引的使用场景3.1索引的优势和劣势3.1.1优势3.1.2劣势 3.2创建索引的场景3.2.1应创建索引的字段3.2.2不应创建索引的字段 3.3索引优化3.3.1索引失效3.3.2索引优化3.3.3使用索引优化排序 第四章、参考自以下文章 友情提醒: 先看文章目录大致了解文章知识点结构点击文章目录可直接跳转到文章指定位置。这是一篇学习博客很多内容都是摘抄的并非原创。文章末尾有列出参考学习的博客 第一章、快速了解索引
1.1索引是什么
索引介绍 ①数据结构是计算机存储、组织数据的方式。 ②索引索引是一种单独的、物理的对数据库表中一列或多列的值进行排序通过方便有效的数据结构来排序的存储结构。它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 ③类比生活中字典有目录通常会先查找汉字的偏旁目录或字母目录找到同音或同偏旁的汉字后再进行查找。 数据库的数据表好比是一本字典数据库索引好比被预先排序的目录数据库会通过索引快速定位查找信息加快查询速度。
索引原理 不同类型的索引有着不同的数据结构但是提高查询速度的思想是一致的利用数据结构的有序性避免进行全表扫描来获取需要的数据。
该列的值如果越互不相同索引效率越高。反之列存在大量相同的值例如性别存储的不是男就是女创建索引就没有意义。使用主键索引的效率是最高的因为主键会保证绝对唯一唯一性带来的索引的充分散列。 键和索引的关系 键和索引都是基于字段的但是键是逻辑结构不另外存储 而索引是物理结构需要另外占用空间存储。
1.2为什么使用索引
主要目的是加快检索数据。不用索引时需要在表中遍历所有的行直到找到想要的数据使用索引后就不必扫描整个表而是快速定位到符合条件的数据。加快数据库的查询效率和提升程序性能。
举例select * from tableName where id10000 这个查询语句中
如果没有索引必须遍历整个表直到ID等于10000的这一行被找到为止
有了经过算法优化过的索引可以快速定位查询效率就很高1.3操作索引示例
创建索引 创建一个普通的索引作为示例 ①普通索引 最基本的索引类型允许定义索引的列中插入重复值和空值 注这里的name是数据表中一列的列名10是索引的长度长度可以不写,但是创建索引时如果是blob 和 text 类型必须指定length
//CREATE INDEX indexName ON tableName(columnName(columnLength))
create INDEX index_name ON tableName(name(10))②其他的索引创建
1.PRIMARY KEY 主键索引
MySQL alter table table_name add primary key ( column )
2.UNIQUE 或 UNIQUE KEY (唯一索引)
mysql alter table table_name add unique (column)
3.FULLTEXT (全文索引)
mysql alter table table_name add fulltext (column )
4.INDEX (普通索引)
mysql alter table table_name add index index_name ( column )
5.多列索引 mysql alter table table_name add index index_name ( column1, column2, column3 )
删除和查看索引 ①查看索引
show INDEX from tableName; ②删除索引
DROP INDEX index_name ON tableName第二章、索引分类
索引的分类方式很多名称叫法也不同。这里根据不同的分类方式进行介绍
2.1按数据结构分类
数据结构是计算机存储、组织数据的方式。根据数据结构分类主要可以分为B树结构Hash结构等
2.1.1树型数据结构索引
二叉树
接下来我将通过最简单的树形结构二叉树引出一些诸如根节点节点树B树之类的概念。 二叉树Binary tree如图树形结构许多实际问题抽象出来的数据结构往往是二叉树形式二叉树特点是每个节点最多只能有两棵子树且有左右之分 。 ①节点包含一个数据元素及若干指向子树分支的信息 ②节点的度一个节点拥有子树的数目称为节点的度 ③叶子节点也称为终端节点没有子树的节点或者度为零的节点 ④分支节点也称为非终端节点度不为零的节点称为非终端节点 ⑤树的度树中所有节点的度的最大值 ⑥节点的层次从根节点开始假设根节点为第1层根节点的子节点为第2层 ⑦树的深度也称为树的高度树中所有节点的层次最大值称为树的深度 ⑧有序树如果树中各棵子树的次序是有先后次序则称该树为有序树 ⑨无序树如果树中各棵子树的次序没有先后次序则称该树为无序树 ⑩森林由mm≥0棵互不相交的树构成一片森林。如果把一棵非空的树的根节点删除则该树就变成了一片森林森林中的树由原来根节点的各棵子树构成
B树
索引会用到更复杂的树形数据结构如B-tree 1、B-tree也就是B树允许每个节点有更多的子节点树的节点增多后树的层级比原来的二叉树少了且能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作都在对数时间内完成。与自平衡二叉查找树不同B树为系统大块数据的读写操作做了优化。B树减少定位记录时所经历的中间过程从而加快存取速度。
2、 B 树应用到数据库中的时候充分利用了磁盘块的原理磁盘数据存储是采用块的形式存储的每个块的大小为 4K每次 I/O 进行数据读取时同一个磁盘块的数据可以一次性读取出来把节点大小限制在磁盘块大小范围。 B树查找数据过程 一个节点上存有主键关键字和多个指针p指针存储的是子节点所在磁盘块的地址。 模拟查找关键字29的过程 1、根据根节点找到磁盘块1读入内存。【磁盘I/O操作第1次】 2、比较关键字29在区间17,35找到指针P2。 3、根据P2指针找到磁盘块3读入内存。【磁盘I/O操作第2次】 4、比较关键字29在区间26,30找到磁盘块3的指针P2。 5、根据P2指针找到磁盘块8读入内存。【磁盘I/O操作第3次】 6、在磁盘块8中的关键字列表中找到关键字29。 7、分析上面过程发现需要3次磁盘I/O操作和3次内存查找操作。由于内存中的关键字是一个有序表结构可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。
注意B树的非叶子节点也存储数据每个节点中有key也有data。而B树只在叶子节点存储数据 而每个节点的存储空间是有限的如果data数据较大时将会导致每个节点即一个页能存储的key的数量很小。当存储的数据量很大时同样会导致B-Tree的深度较大增大查询时的磁盘I/O次数进而影响查询效率。
B 树
B 树是B 树的升级版InnoDB存储引擎就是用BTree实现MySQL索引结构。有同学又会问了什么是存储引擎啊存储引擎就是数据库底层软件组件, 数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。其实这不是重点重点是我们知道MySQL里有个存储引擎这个存储引擎会使用Btree的数据结构来存储、组织数据表里的数据而使用Btree数据结构组织排序以后查询效率大大提升。
1、B树在叶子节点存储key和数据非叶子节点只存储key不存储数据所以非叶子节点可以包含更多的节点这样做可以降低树的高度。还能将数据范围变为多个区间区间越多数据检索越快。以 InnoDB 的一个整数字段索引为例每个节点大概有 1200各分叉。当树高为 4 时大概可以存储17亿条数据【1200^3 ≈ 17亿】。考虑到树根的数据块总是在内存中的从17亿条数据中查找一个值最多只用访问3次磁盘而树的第二层也有很大概率在内存中那么访问磁盘的平均次数会更少。
2、B树叶节点两两相连可大大增加区间访问性可使用在范围查询等 B树更适合外部存储存储磁盘数据。由于内节点无data域每个节点能索引的范围更大更精确。
3、B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找可能还没有到达叶子节点检索就结束了。而 B树的检索效率就很稳定了任何查找都是从根节点到叶子节点的过程叶子节点的顺序检索很明显。B树全节点遍历更快B 树遍历整棵树只需要遍历所有的叶子节点即可而不需要像 B 树一样需要对每一层进行遍历这有利于数据库做全表扫描。
4、B 树叶子节点的关键字从小到大有序排列所有节点关键字是按递增次序排列并遵循左小右大原则就是说如果有重复元素父节点 20子节点 20那么子节点应当位于右侧即子节点父节点≤右子节点
5、B树所有的叶子节点数据构成了一个有序链表左边结尾数据都会保存右边节点开始数据的指针。这个特性在范围查找区间访问时非常有用数据紧密性很高缓存的命中率也会比 B 树高。。
2.1.2Hash数据结构索引
1、Hash表是一种key-value的存储结构通过key计算出下标然后将value添加到该下标对应的链表中去。哈希表这种结构适用于只有等值查询的场景比如 Memcached 及其他一些 NoSQL 引擎不适合区间查询另外 Hash 表还存在 Hash 函数选择和 Hash 值冲突等问题。
2、哈希索引是一种基于哈希表实现的索引它支持全值匹配但不支持范围查询和前缀匹配。在MySQL中哈希索引主要应用于MEMORY存储引擎。也是 Memory 引擎表的默认索引类型Memory 引擎同时也支持 B-Tree 索引。
3、哈希索引数据并不是按照索引值顺序存储的所以也就无法用于排序。
4、哈希索引不支持部分索引列匹配查找因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
5、哈希索引只支持等值比较查询包括 、IN()、不支持任何范围查询例如 WHERE price100。
6、访问哈希索引的数据非常快除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候存储引擎必须遍历链表中所有的行指针逐行进行比较直到找到所有符合条件的行。
7、如果哈希冲突很多的话一些索引维护操作的代价也会很高。例如如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引那么当从表中删除一行时存储引擎需要遍历对应哈希值的链表中的每一行找到并删除对应行的引用冲突越多代价越大。
注意事项基于 Hash 索引的表还是能够支持范围查找只不过其通过遍历实现不能利用索引进行查询优化。
2.1.3 其他数据结构索引
LSM树【Log-Structured Merge-Trees】 在B树中每次有数据写入时可能要插入到不同的节点即写入不同的数据块。大量的磁盘随机写操作会影响写入速度。因此B树不适合写入量大的场景。针对这个问题HBase引入了LSM树的概念将随机写转变为顺序写大幅提升了写入速度。和B树相比LSM牺牲了部分读性能用来大幅提高写性能。目前HBase/LevelDB/RocksDB/ClickHouse这些NoSQL存储都是采用的LSM树。 LSM树的结构是横跨内存和磁盘的包含memtable、immutable memtable、SSTable等多个部分。
2.2按物理存储方式分类
根据物理存储分类可以分为聚簇索引聚集索引和非聚簇索引非聚集索引 聚集索引InnoDB引擎 主键索引属于聚簇索引的叶子节点会存储指针的值和数据行也就是说数据和索引是在一起这就是聚簇索引InnoDB中也只有主键索引才能是聚簇索引。
非聚集索引MyISAM引擎 二级索引辅助索引属于非聚簇索引叶子节点只会存储数据行的指针简单来说数据和索引不在一起就是非聚聚簇索引
注意主键索引和二级索引都会存储指针的值。
2.2.1聚簇索引聚集索引
聚簇索引并不是一种单独的索引类型而是一种数据存储方式InnoDB引擎使用的是聚簇索引MySQL 的主键索引默认就使用到了聚簇索引。 1、MySQL 自动为主键字段创建一个索引PRIMARY按照每张表的主键构造一颗B树同时叶子节点中存放有行数据所以说主键索引默认就使用到了聚簇索引。如果没有主键则选择非空唯一的索引作为聚簇索引都没有则隐式创建一个索引作为聚簇索引
2、当表有聚簇索引时它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方所以一个表只能有一个聚簇索引如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引必须先删除主键然后添加我们想要的聚簇索引最后恢复设置主键即可。
主键索引查询: 3、实际上索引顺序与物理存储顺序一致。因此通过主键进行查找时的性能最好。InnoDB引擎使用的是聚簇索引将主键组织到一棵B树中主键被作为聚簇索引而行数据就储存在叶子节点上id为主键我们使用“where id 14”这样的条件查找数据则按照B树的检索算法即可查找到对应的叶节点可以直接通过主键定位到数据并返回。
2.2.2非聚簇索引非聚集索引
非聚簇索引也只是一种存储方式MyIsam使用的是非聚簇索引以 MyISAM 存储引擎存储的表不存在聚簇索引。二级索引(又称辅助索引)就属于非聚簇索引
1、当我们自己通过CREATE INDEX语句或者相应的ALTER TABLE ADD INDEX语句创建其他索引就是二级索引索引顺序与物理存储顺序不一致二级索引包括普通索引唯一索引前缀索引等 二级索引属于非聚簇索引但是我们不能说非聚簇索引就是二级索引你可以说狗是动物但是动物是狗就不对。
2、二级索引出现的原因很多业务场景中我们需要根据非主键列进行数据的检索此时又不能一行一行全表查一遍因为这样性能极差 因此数据库的研发者想出了一种办法多建立几棵B树其中只有一颗B树是用主键索引建立的这颗树的叶子节点包含了主键索引和完整的数据记录。其他B树使用二级索引建立的叶子节点存放的是对应的非主键列主键值没有存储数据行
辅助(二级)索引查询回表的概念 3、辅助索引中除了包含了索引对应字段值还有主键值当我们不通过主键来搜索而通过二级索引搜索时则需要两个步骤 第一步在辅助索引B树中检索Name二级索引列到达其叶子节点获取对应的主键。 第二步二级索引在查询需要多扫描一棵索引树也就是回表使用主键在主索引B树中再执行一次B树检索操作最终到达叶子节点即可获取整行数据。
通过覆盖索引避免回表 4、查询的字段中既有索引字段,又有非索引字段就会发生回表。那么把非索引字段建立索引使你查的所有字段都是索引字段即索引字段覆盖了需要查的字段这就是覆盖索引
比如当前的二级索引字段只有name
# 索引中只有name但是却查询了所有字段此时就会回表再通过主键获取其他字段的值
select * from table where name a; 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值而哈希索引、空间索引和全文索引等都不存储索引列的值所以 MySQL 只能使用 B-Tree 索引做覆盖索引。另外不同的存储引擎实现覆盖索引的方式也不同而且不是所有的引擎都支持覆盖索引。
通过索引下推(ICP)减少回表 5、索引下推简称ICP在Mysql5.6的版本上推出用于优化查询 在使用ICP的情况下如果存在某些被索引的列的判断条件时MySQL服务器将这一部分判断条件传递给存储引擎然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。 索引下推默认是开启的关闭可以使用下面的语句
set optimizer_switchindex_condition_pushdownoff;索引下推使用条件
1、只能用于range、 ref、 eq_ref、ref_or_null访问方法
2、只能用于InnoDB和 MyISAM存储引擎及其分区表
3、对InnoDB存储引擎来说索引下推只适用于二级索引非主键索引;
4、引用了子查询的条件不能下推
5、引用了存储函数的条件不能下推因为存储引擎无法调用存储函数。
具体可以看这篇Mysql性能优化什么是索引下推
不同存储引擎下的非聚簇索引实现有所不同 MyISAM 1、MyISAM 存储引擎存储的表不存在聚簇索引MyISAM 的非聚簇索引实现的叶子节点仍然是索引节点MyISAM中无论是主键索引还是二级索引叶子节点存放的都是主键和指向对应数据块的指针叶子节点是不存储表数据的。保证可以通过索引查找到对应的数据行只需要对索引进行一遍查找。但这样会存在页分裂问题。
2、在MyISAM 中一个数据表table的存储文件它是由table.frm、table.myd以及table.myi组成。table.myd记录了数据数据行table.myi记录了索引的数据。MyISAM 引擎的索引文件和数据文件是独立分开的正好符合非聚簇索引的架构。
innodb innodb的非聚簇索引实现叶子节点存储的是主键值不是主键地址通过主键值去聚簇索引进行索引操作找到叶子节点数据在该叶子节点上这个就是前面说的回表。 InnoDB 的策略好处是当出现行移动或者数据页分裂时二级索引的维护工作不需要维护变动的指针坏处是使用主键值当作指针会让二级索引占用更多的空间。
2.3按索引字段的特性分类
2.3.1普通索引
MySQL中最基本的索引类型没有任何限制允许定义索引的列中插入重复值和空值
create index index_name on TableName(name(10))alter table table_name add index index_name ( column )2.3.2唯一索引
UNIQUE 表示唯一索引与普通索引类似用于实现唯一约束索引列中的值必须是唯一的但是允许空值如果是组合索引则列值的组合必须唯一创建方法和普通索引类似。
CREATE UNIQUE INDEX indexName ON tableName(columnName(columnLength))alter table table_name add unique (column)2.3.3主键索引
主键和索引的关系 1主键索引是创建主键的时候系统自动创建的索引 2主键要求不重复不为空 3主键属于索引是索引的一种 主键索引属于聚簇索引但是我们不能说聚簇索引就是主键索引聚簇索引会将索引和数据保存在同一个 B-Tree 中所以通过主键索引可以直接查到需要查找的数据 通过非聚集索引可以查到记录对应的主键值 再使用主键的值通过聚集索引查找到需要的数据。因此从主键索引中获取数据通常比在二级中查找要快。
alter table table_name add primary key ( column )2.3.4多列索引又称组合索引复合索引多字段索引联合索引
这里就不细说了在后文2.4.3会细说 在表中的多个字段组合上创建的索引遵循最左前缀原则。 最左前缀原则使用组合索引时在查询条件中使用了这些字段的左边字段时索引才会被使用。 注意事项在一次查询中MySQL 只能使用一个索引。多列索引也被看作一个索引。 create index index_name on table_name(column1, column2, column3 ) alter table table_name add index index_name ( column1, column2, column3 )2.3.5前缀索引后缀索引
MySQL 允许基于 CHAR、VARCHAR、BINARY 以及 VARBINARY 字段的最前面一部分内容创建索引同时对于 BLOB 和 TEXT 字段必须指定索引使用的字段长度。这一特性被称为前缀索引prefix index。 前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个 bytes 建立的索引而不是在整个字段上建索引。 例如可以对 persons 表中的 name(varchar(16))字段 中 name 的前 5 个字符建立索引。 create index index_name on persons (name(5)) comment 前缀索引;前缀索引是一种能使索引更小、更快的有效办法但另一方面也有其缺点MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY也无法使用前缀索引做覆盖扫描。
后缀索引(suffix index)也有其用途( 例如找到某个域名的所有电子邮件地址)。MySQL 原生并不支持反向索引但是可以把字符串反转后存储并基于此建立前缀索引。可以通过触发器来维护这种索引。
2.3.6全文索引FULLTEXT
全文索引是一种用于文本数据模糊查询的特殊索引它基于倒排索引实现。在MySQL中全文索引主要应用于MyISAM和InnoDB存储引擎。全文索引主要是为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了全文索引。 只能在char、varchar、text 类型字段上才能使用。 对于大容量的数据表生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
CREATE FULLTEXT INDEX indexName ON tableName(columnName(columnLength))alter table table_name add fulltext (column )2.4按索引的字段个数分类
2.4.1单列索引
建立在单个列上的索引为单列索引单列索引有普通索引唯一索引主键索引
注意多个单列索引与单个多列索引的查询效率不同因为执行查询时MySQL只能使用一个索引会从多个索引中选择一个限制最为严格的索引。 测试具体的效率呢
两种不同的建立索引方式case 1对c1,c2,c3三列按此顺序添加一个多列索引case 2 对c1,c2,c3分别建立三个单列索引问题1按c1搜索时哪种索引效率快答单列索引case2快问题2按C2搜索时哪种索引效率快答单列索引case2快并且case1的索引无效问题3按C1C2搜索时哪种效率快答不知道问题4按C1,C2,C3搜索哪种效率快答多列索引case1快问题5按C2,C3,C1搜索时哪种效率快答单列索引case2快 因为没有按“最左前缀” 规则实现给三个列加上索引不管是单列索引还是组合索引查询时间都是相差不大。 但是如果组合索引没有按照 “最左前缀” 规则实现查询时间会比单列索引时间要长。
2.4.2重复索引
重复索引是指在相同的列上按照相同的顺序创建了相同类型的索引。应该避免这样创建重复索引发现以后也应该立即移除
CREATE TABLE test(ID INT NOT NULL PRIMARY KEY,A INT NOT NULL,B INT NOT NULL,UNIQUE(ID),INDEX(ID)
)ENGINE InnoDB;一个经验不足的用户可能是想创建一个主键先加上唯一限制然后再加上索引以供查询使用。事实上MySQL 的唯一限制和主键限制都是通过索引实现的因此上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做除非是在同一列上创建不同类型的索引来满足不同的查询需求。
2.4.3多列索引又称组合索引复合索引多字段索引联合索引
多列索引介绍 多列索引一个索引包含多个列一个联合索引是一个有序元组a1, a2, …, an其中各个元素均为数据表的一列多列索引最多可以16列。 平时用的SQL查询语句一般都有多个的限制条件如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引形成索引覆盖可以提高查询的效率。
建立多列索引 ①MySQL组合索引“最左前缀”简单的理解就是只从最左面的开始组合。 演示建立一个普通多列索引索引从左到右顺序为title,time,age
ALTER TABLE table_name ADD INDEX index_title_time_age (title(50),time(10),age(5))。
建立这样的组合索引其实是相当于从左边开始组合分别建立了下面几个索引
—title,timeage
–title,time
–title②演示建表时设置多列索引 create index index_id_name on table_name (id,name) comment 组合索引;组合索引的非叶子节点保存了两个字段的值作为 Btree 的 key 值当 Btree 上插入数据时先按字段 id 比较在 id 相同的情况下按 name 字段比较。 ③多列唯一索引
ALTER TABLE table_name ADD unique index_name( userid, username );④多列主键索引,主键一般都是自增长的ID要先删除主键后再创建多列主键索引不然会报错的ERROR 1068 (42000): Multiple primary key defined
ALTER TABLE table_name ADD primary key index_name( userid, username );最左前缀原则 ①最左前缀原则使用组合索引时在查询条件中使用了这些字段的左边字段时索引才会被使用。
比如建立了一个 (name,age,sex) 的联合索引当 (张三,20,男)1 这样的数据来检索的时候name是索引字段的最左边所以会使用name来确定下一步的所搜方向如果 name 相同再依次比较 age 和 sex最后得到检索的数据当 (20,男) 这样查询时没有使用name这个最左边的索引数据时就不会使用索引。当 (张三,男) 这样的数据来检索时先用 name 来指定搜索方向但下一个字段 age 的缺失所以只能把名字等于张三的数据通过索引都找到后面的age索引无法使用这个即索引的最左匹配特性。②最左前缀匹配原则指的是 MySQL 会一直向右匹配直到遇到范围查询 (、、between、like) 就停止匹配比如 a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d) 顺序的索引d 是用不到索引的如果建立(a,b,d,c) 的索引则都可以用到。
③当不需要考虑排序和分组时将选择性最高的列放到索引最前列这时候索引的作用只是用于优化 WHERE 条件的查找。但是有时可能需要根据那些运行频率最高的查询来调整索引列的顺序让这种情况下索引的选择性最高。
2.4.4冗余索引
冗余索引和重复索引有一些不同。如果创建了索引 (A, B)再创建索引 (A) 就是冗余索引因为这只是前一个索引的最左前缀列。因此索引 (A, B) 也可以当作索引 (A) 来使用(这种冗余只是对 B-Tree 索引来说的)。但是如果再创建索引 (B, A)则不是冗余索引索引 (B) 也不是因为 B 不是索引 (A,B) 的最左前缀列。另外其他不同类型的索引(例如哈希索引或者全文索引)也不会是 B-Tree 索引的冗余索引而无论覆盖的索引列是什么。
冗余索引通常发生在为表添加新索引的时候。例如有人可能会增加一个新的索引 (AB)而不是扩展已有的索引 (A)。还有一种情况是将一个索引扩展为 (A, ID)其中 ID 是主键对于 InnoDB 来说主键列已经包含在二级索引中了所以这也是冗余的。
大多数情况下都不需要冗余索引应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引因为扩展已有的索引会导致其变得太大从而影响其他使用该索引的查询的性能。
第三章、索引的使用场景
3.1索引的优势和劣势
3.1.1优势
1、通过创建索引可以再查询的过程中提高系统的性能 2、通过创建唯一性索引可以保持数据库表中每一行数据的唯一性 3、在使用分组和排序子句进行数据检索时可以减少查询中分组和排序的时间
3.1.2劣势
1、创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加缺点是在插入、更新和删除记录时需要同时修改索引因此索引越多插入、更新和删除记录的速度就越慢。 2、索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大如果表中数据量巨大索引数量多索引文件可能比数据本身的文件会更快到达硬件限制。 3、聚簇数据最大限度地提高了 I/O 密集型应用的性能但如果数据全部都放在内存中则访问的顺序就没那么重要了聚簇索引也就没什么优势了。
3.2创建索引的场景
3.2.1应创建索引的字段
1、作为主键必须有索引主键 2、经常用在连接的列上主要是一些外键可以加快连接的速度外键 3、经常需要搜索的列上加快搜索的速度 需搜索 4、经常需要排序的列上创建索引利用索引已经排序加快排序查询时间需排序 5、 经常需要根据范围搜索的列上因为索引已经排序其指定的范围是连续的范围 6、经常使用在WHERE子句中的列上加快条件的判断速度。where 7、如果复合索引中包含的字段经常单独出现在Where子句中则分解为多个单字段索引
3.2.2不应创建索引的字段
1、查询中很少用到的列有索引并不能提高查询速度。 2、取值很少的列比如数据表中的性别列 3、text, image和bit数据类型的列这些列的数据量要么相当大要么取值很少。 4、被频繁更新的列会提高搜索性能但是会降低修改性能
3.3索引优化
3.3.1索引失效
①索引列作为表达式的一部分或者函数参数那么索引会失效。例如下面这个查询无法使用 actor_ id 列的索引
SELECT actor_id FROM sakila.actor WHERE actor_id 1 5;只有将上述语句修改为如下等价形式才能够享受到 MySQL 的索引查询优化
SELECT actor_id FROM sakila.actor WHERE actor_id 4;②不符合最左匹配原则例如定义了 (a,b,c) 联合索引相当于构造了 (a)、(a,b)、(a,b,c) 索引。如果要使 c 索引实际工作那么必须在 WHERE 中同时加入 a、b 字段的条件顺序无所谓。对于 b则必须加入 a。而 a 索引可以单独出现并工作。
③查询中有某个列的范围查询则其右边所有列都无法使用索引优化查找。例如有查询 WHERE last_name‘Smith’ AND first_ name LIKE ‘%JJ’ AND dob ’ 1976-12-23’这个查询只能使用索引的前两列因为这里 LIKE 是一个范围条件。 其他常见的范围查找有通过 ,, ,\ ,\ between!,或者 操作符做比较。它们都是导致索引失效。 注意事项IN() 不是范围匹配而是多个等值匹配因此并不会导致索引失效。 ④WHERE 子句的查询条件里使用了比较操作符 LIKE 和 REGEXP第一个字符不是通配符的情况下才能使用索引。比如说如果查询条件是 LIKE ‘abc%’MySQL 将使用索引虽然最终会导致后续索引失效如果条件是 LIKE ‘%abc’MYSQL 将不使用索引。
⑤ 如果 WHERE 子句带有 or 且有字段不属于索引那么即使其中带索引的字段也不会使用。
⑥使用了select*大概率会查询非索引列的数据就无法使用覆盖索引了
⑦对查询结果排序时使用order by不符合索引结构的顺序。
⑧索引列值为null致COUNT(*)不能走索引 查询诸如SELECT COUNT(*) FROM Table 的时候因为HASHSET中不能存储空值的所以优化器不会走索引。 ⑨not in 和 notexists使所有失效 而IN() 和exists不是范围匹配而是多个等值匹配因此并不会导致索引失效。 ⑩字段类型不同 比如你的索引字段是varchar型但是你搜索条件却是 userid333那这样索引不生效。
3.3.2索引优化
①不要让字段的默认值为NULL 只要列中包含有NULL值都将不会被包含在索引中复合索引中只要有一列含有NULL值那么这一列对于此复合索引就是无效的。 ②注意避免冗余索引尽可能的考虑建立联合索引而不是单列索引 而理论上每张表里面最多可创建16个索引不过除非是数据量真的很多否则过多的使用索引也不是那么好玩的。 ③使用短索引 如果在前10个或20个字符内多数值是惟一的那么就不要对整个列进行索引考虑在字符串类型的字段上使用前缀索引代替普通索引 ④索引列排序 MySQL查询只使用一个索引因此如果where子句中已经使用了索引的话那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作如果需要最好给这些列创建复合索引。 ⑤like语句操作 一般情况下不鼓励使用like操作如果非使用不可要注意like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 ⑥不要在列上进行运算 例如select * from users where YEAR(adddate)2007将在每个行上进行运算这将导致索引失效而进行全表扫描因此我们可以改成select * from users where adddate’2007-01-01′。
⑦注意范围查询无法使用索引 如,, ,\ ,\ between!,或者 以及某些时候的like(以通配符%或_开头的情形)。
3.3.3使用索引优化排序
如果有排序的需求我们只需要order by底层实现起来整体上来说有两种不同的思路
filesort排序介绍 有时候我们也将之称为文件排序数据库在执行过程中数据量比较小的时候先将满足条件的数据全部读出来放入内存sort_buffer中执行快排如果临时数据量比内存sort_buffer 大 就要把数据放入临时文件然后做外部排序这种操作就是 filesort。只有当在内存中无法完成排序的时候才会用到磁盘文件。
索引排序 给需要排序的字段加上索引由于 InnoDB 中的索引是按照 BTree 的形式将数据组织在一起的BTree 中数据本身就是有序的所以如果能够利用好索引排序的事情就会事半功倍。
索引排序原理 B 树叶子节点的关键字从小到大有序排列所有节点关键字是按递增次序排列并遵循左小右大原则就是说如果有重复元素父节点 20子节点 20那么子节点应当位于右侧即子节点父节点≤右子节点 只有当索引的顺序和 order by 子句的顺序完全一致并且所有列的排序方向也都一致的情况下MySQL 才能通过索引来对结果进行排序同时如果是联合索引order by 子句也需要满足最左匹配原则。
符合索引结构的情况会进行索引排序 InnoDB 索引树以任意一个叶节点为起始点可以向左或向右遍历如果语句需要的 order by 顺序刚好可以利用索引树的单向遍历就可以避免排序操作。
为了便于说明创建一个简单的表这个表里除了主键索引 id 外还有一个联合索引 ab。 ①单字段倒序排序将这个表的数据按照 a 的大小倒序返回。因为这次排序符合索引结构的存储情况所以会进行索引排序而不是filesort排序
SELECT * FROM t WHERE a 0 ORDER BY a DESC;语句的执行流程 1、从索引 ab 上取最右的一个记录取出主键值 ID_Z 2、根据 ID_Z 到主键索引上取整行记录作为结果集的第一行 3、在索引 ab 上取上一个记录的左边相邻的记录 4、每次取到主键 id 值再到主键索引上取到整行记录添加到结果集的下一行 重复步骤 3、4直到遍历完整个索引。
②组合字段倒序排序按照 a 值倒序当 a 的值相同时按照 b 值倒序。因为这次排序符合索引结构的存储情况所以会进行索引排序同样不需要filesort排序
SELECT * FROM t WHERE a 1 AND b 1 ORDER BY a desc,b desc;③组合字段正序排序显然这个语句也是不需要排序的理由和上面一样只需要先取 ab 索引树最左边的节点然后向右遍历即可。
SELECT * FROM t WHERE a 1 AND b 1 ORDER BY a,b;不符合索引结构的情况会进行filesort排序 按照a值正序对于相同的a值b值需要倒序排序。 与索引的存储情况不同因此只能选择使用filesort排序操作。
select * from t order by a, b desc;解决方式既然不符合索引结构会进行filesort排序那么我们改变索引存储结构不就好了嘛 在8.0版本中支持了这个功能官方名称是Descending Indexes。在8.0版本中我们可以把索引ab的定义做个修改。这样从左到右遍历这个索引的时候就刚好满足a正序然后b逆序的要求。 Descending Indexes可以避免这种情况下的排序操作语句的执行性能自然就提升了。
create table t (id int(11) NOT NULL,a int(11) NOT NULL,b int(11) NOT NULL,c int(11) NOT NULL,primary key (id),key ab (a, b desc)
) engine InnoDB;ORDER BY 语句也使用索引最左前缀原则 比如 (a,b) 联合索引下ORDER BY a 、ORDER BY a,b 都是符合要求的但是如果 ORDER BY b 或者 ORDER BY b,a 这种情况都是不符合要求的。
第四章、参考自以下文章
MySQL索引详解聚集索引与非聚集索引以及数据结构
【mysql】聚簇索引(聚集索引)和非聚簇索引(二级索引、辅助索引)的区别
浅谈MySQL索引的分类
窥探 MySQL 索引
数据库索引详细介绍
数据库的索引简介
数据库索引
mysql 多列索引 详细说明
MySQL 入门教程》第 30 篇 数据库索引
剖析Mysql的InnoDB索引
Mysql性能优化什么是索引下推
主键和聚集索引单列索引和多列索引
单个索引和组合索引联合索引谁效率高
mysql数据库索引类型和原理
MySQL的二级索引
关于在 MySQL 排序中使用索引这件事
