当前位置： 首页 > news >正文

柳市网站托管,山东省住房和城乡建设厅网站定额站,中建国际建设有限公司网站,在阿里巴巴网站上怎么做贸易什么是索引#xff1f; 官方上面说索引是帮助MySQL高效获取数据的数据结构#xff0c;通俗点的说#xff0c;数据库索引好比是一本书的目录#xff0c;可以直接根据页码找到对应的内容#xff0c;目的就是为了加快数据库的查询速度。 索引是对数据库表中一列或多列的值进…什么是索引 官方上面说索引是帮助MySQL高效获取数据的数据结构通俗点的说数据库索引好比是一本书的目录可以直接根据页码找到对应的内容目的就是为了加快数据库的查询速度。 索引是对数据库表中一列或多列的值进行排序的一种结构使用索引可快速访问数据库表中的特定信息。 如果没有加索引我们会对数据库中的数据逐行比对效率极低所以我们可以根据索引快速定位到我们需要查询的数据拿到定位后的信息再去查找。 索引的原理 那么问题来了索引肯定要高效如果只是用一个普通数组存下来那不依然是遍历一遍效率很低吗所以索引一定是一个非常高效的数据结构 索引的存储原理可以概括为一句话以空间换时间 一般来说索引本身也很大不可能全部存储在内存中因此索引往往是存储在磁盘上的文件中的可能存储在单独的索引文件中也可能和数据一起存储在数据文件中。 数据库在未添加索引进行查询的时候默认是进行全文搜索也就是说有多少数据就进行多少次查询然后找到相应的数据就把它们放到结果集中直到全文扫描完毕。 索引有哪些 主键索引 设定为主键后数据库自动建立索引InnoDB为聚簇索引主键索引列值不能为空Null

(1) 创建表添加主键索引

CREATE TABLE table_name ([…] ,PRIMARY KEY (col_name), )

(2) 添加主键索引

ALTER TABLE table_name ADD PRIMARY KEY (col_name); 普通索引单列索引 普通索引单列索引单列索引是最基本的索引它没有任何限制。

(1) 直接创建索引

CREATE INDEX index_name ON table_name(col_name);

(2) 修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

(3) 创建表的时候同时创建索引

CREATE TABLE table_name ([…] ,PRIMARY KEY (id),INDEX index_name (col_name) )

(4) 删除索引

DROP INDEX index_name ON table_name;
alter table 表名 drop index 索引名; 复合索引组合索引 复合索引复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则即在查询条件中使用了复合索引的第一个字段索引才会被使用。因此在复合索引中索引列的顺序至关重要。

1创建一个复合索引

create index index_name on table_name(col_name1,col_name2,…);

2修改表结构的方式添加索引

alter table table_name add index index_name(col_name1,col_name2,…); 唯一索引 唯一索引唯一索引和普通索引类似主要的区别在于唯一索引限制列的值必须唯一但允许存在空值只允许存在一条空值。 如果在已经有数据的表上添加唯一性索引的话 如果添加索引的列的值存在两个或者两个以上的空值则不能创建唯一性索引会失败。一般在创建表的时候要对自动设置唯一性索引需要在字段上加上 not null如果添加索引的列的值存在两个或者两个以上的null值还是可以创建唯一性索引只是后面创建的数据不能再插入null值 并且严格意义上此列并不是唯一的因为存在多个null值。对于多个字段创建唯一索引规定列值的组合必须唯一 – 1创建唯一索引

创建单个索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);# 创建多个索引 CREATE UNIQUE INDEX index_name on table_name(col_name,…);– 2修改表结构

单个

ALTER TABLE table_name ADD UNIQUE index index_name(col_name);

多个

ALTER TABLE table_name ADD UNIQUE index index_name(col_name,…);– 3创建表的时候直接指定索引 CREATE TABLE table_name ([…] ,PRIMARY KEY (id),UNIQUE index_name_unique(col_name) )“空值” 和”NULL”的概念 1空值是不占用空间的 .

2: MySQL中的NULL其实是占用空间的.长度验证注意空值的之间是没有空格的。 select length(),length(null),length( );

| length() | length(null) | length( ) |

| 0 | NULL | 1 | ————————————— 全文索引 Full Text类型索引FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB而之前的版本只支持 MyISAM 表。全文索引主要用来查找文本中的关键字而不是直接与索引中的值相比较目前只有char、varchartext 列上可以创建全文索引。 – 1创建表的适合添加全文索引 CREATE TABLE table_name ([…] ,PRIMARY KEY (id),FULLTEXT (col_name) )– 2修改表结构添加全文索引 ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name)– 3直接创建索引 CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name) 注意 默认 MySQL 不支持中文全文检索MySQL 全文搜索只是一个临时方案对于全文搜索场景更专业的做法是使用全文搜索引擎例如 ElasticSearch 或 Solr。 索引的查询和删除命令 #查看: show indexes from 表名; #或 show keys from 表名;#删除 alter table 表名 drop index 索引名; 索引的优缺点 优点 大大提高数据查询速度。可以提高数据检索的效率降低数据库的IO成本类似于书的目录。通过索引列对数据进行排序降低数据的排序成本降低了CPU的消耗。被索引的列会自动进行排序包括【单例索引】和【组合索引】只是组合索引的排序需要复杂一些。如果按照索引列的顺序进行排序对order 不用语句来说效率就会提高很多。 缺点 索引会占据磁盘空间。索引虽然会提高查询效率但是会降低更新表的效率。比如每次对表进行增删改查操作MySQL不仅要保存数据还有保存或者更新对应的索引文件。维护索引需要消耗数据库资源。 所以综合索引的优缺点 数据库表中不是索引越多越好而是仅为那些常用的搜索字段建立索引效果最佳!也即where后面常用到的字段建立索引效果最佳 索引的数据结构 前面我们说到索引就类似于目录索引也是一个数据结构存放一些字段对应的磁盘地址索引的特点就是要高效快速定位查找等不然就简简单单用一个数组查询还是一样的全表扫描遍历。 所以索引一定是用高效查找的数据结构来实现的 我们来依次看看有哪些数据结构用来做索引以及各种数据结构的优缺点和为什么InnoDB默认采用的B树对应的面试题为什么MySQL存储引擎InnoDB索引的底层数据结构采用B树 Hash Hash表在Java中的HashMapTreeMap就是Hash表结构以键值对的形式存储数据。我们使用hash表存储表数据结构Key可以存储索引列Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高时间复杂度为O(1)但是不支持范围快速查找因为Hash表是无序的呀范围查找时只能通过扫描全表的方式筛选出符合条件的数据。 显然这种方式不适合我们经常需要查找和范围查找的数据库索引使用。 二叉树 上面这个图就是我们常说的二叉树每个节点最多有两个分叉节点左子树和右子树数据按顺序左小右大。 二叉树的特点就是为了保证每次查找都可以进行折半查找从而减少IO次数。 但是二叉树不是一直保持二叉平衡因为二叉树很考验根节点的取值因为很容易在某个节点下不分叉了这样的话二叉树就不平衡了也就没有了所谓的能进行折半查找了如下图 对于MySQL每次从磁盘加载的大小为16kb如果刚好一个节点就是16kb那么这里查找5号节点就要做5次磁盘IO 显然这种不稳定的情况我们在选择存储数据结构的时候就会尽量避免这种的情况发生。 另外二叉树也不适合范围查询进行范围查询每次都只能从根节点往后找只找一个 平衡二叉树AVL树 为了解决二叉树在极端情况下会退化成链表的问题考虑使用平衡二叉树其特点就是每个节点的左子树和右子树高度差不能超过1所以他会通过旋转来维护二叉树的平衡性所以就不会像普通二叉树一样出现“歪脖子”问题 平衡二叉树采用的是二分法思维平衡二叉查找树除了具备二叉树的特点最主要的特征是树的左右两个子树的层级最多差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡不会出现左子树很高、右子树很矮的情况。 使用平衡二叉查找树查询的性能接近与二分查找时间复杂度为O(log2n)查询id6只需要两次IO。 就上述平衡二叉树的特点来看其实是我们理想的状态下然而其实内部还是存在一些问题 时间复杂度和树的高度有关。树有多高就需要检索多少次每个节点的读取都对应一次磁盘的IO操作。树的高度就等于每次查询数据时磁盘IO操作的次数。磁盘每次寻道的时间为10ms在数据量大时查询性能会很差。1百万的数据量log2n约等于20次磁盘IO读写时间消耗约等于20*100.2S。平衡二叉树不支持范围查询快速查找范围查询需要从根节点多次遍历查询效率不高。 B树 AVL树、红黑树都是常见的平衡二叉树不管是哪个其一个节点最多只能有两个分支都会随着插入的元素增多而导致树的高度变高就意味着磁盘IO的次数变多会影响整体数据的查询效率 那么就有一个需求了在树的高度不那么高的情况下如何能满足存储大数据量 答多叉平衡树 MySQL的数据是存储在磁盘文件中的查询处理数据时需要先把磁盘中的数据加载到内存中磁盘IO操作非常耗时所以我们优化的重点就是尽量减少磁盘的IO操作。访问二叉树的每个节点都会发生一次IO如果想要减少磁盘IO操作就需要尽量降低树的高度。 那如何降低树的高度呢 假如key为bigint8字节每个节点有两个指针每个指针为4个字节一个节点占用的空间为84*216。 因为在MySQL的InnoDB引擎的一次IO操作会读取一页的数据量默认一页大小为16K而二叉树一次IO操作的有效数据量只有16字节空间利用率极低。为了最大化的利用一次IO操作空间一个解决方法就是在一个节点处存储多个元素在每个节点尽可能多的存储数据。每个节点可以存储1000个索引16k/161000这样就将二叉树改造成了多叉树通过增加树的分叉树将树的体型从高瘦变成了矮胖。构建1百万条数据树的高度需要2层就可以1000*10001百万也就是说只需要两次磁盘IO操作就可以查询到数据磁盘IO操作次数变少了查询数据的效率整体也就提高了。 这种数据结构我们称之为B树B树是一种多叉平衡查找树如下图主要特点 B树的节点中存储这多个元素每个内节点有多个分叉。节点中的元素包含键值和数据节点中的键值从小到大排列。也就是说在所有的节点中都存储数据。父节点当中的元素不会出现在子节点中。所有的叶子节点都位于同一层叶子节点具有相同的深度叶子节点之间没有指针连接。 举个简单的例子在B树中查询数据的情况 假如我们要查询key等于10对应的数据data根据上图我们可知在磁盘中的查询路径是磁盘块1-磁盘块2-磁盘块6 第一次磁盘IO将磁盘块1加载到内存中在内存中从头遍历比较1015走左子树到磁盘中寻址到磁盘块2。第二次磁盘IO将磁盘块2加载到内存中在内存中从头遍历比较107走右子树到磁盘中寻址到磁盘块6。第三次磁盘IO将磁盘块6加载到内存中在内存中从头遍历比较1010找到key10的位置取出对应的数据data如果data存储的是行记录直接取出数据查询结束如果data存储的是行磁盘地址还需要根据磁盘地址到对应的磁盘中取出数据查询结束。 相比较二叉平衡查找树在整个查找过程中 虽然数据的比较次数并没有明显减少但是对于磁盘IO的次数会大大减少 同时由于我们是在内存中进行的数据比较所以比较数据所消耗的时间可以忽略不计。 B树的高度一般2至3层就能满足大部分的应用场景所以使用B树构建索引可以很好的提升查询的效率。 看到上面的情况觉得B树已经很理想了但是其中还是存在可以优化的地方 B树不支持范围查询的快速查找例如仍然根据上图我们想要查询10到35之间的数据查找到10之后需要回到根节点重新遍历查找需要从根节点进行多次遍历查询效率有待提高。如果data存储的是行记录行的大小随着列数的增加所占空间会变大这时一页中可存储的数据量就会减少树相应就会变高磁盘IO次数就会随之增加有待优化。 B树 B树作为B树的升级版MySQL在B树的基础上继续进行改造使用B树构建索引。B树和B树最主要的区别在于非叶子节点是否存储数据的问题。 B树叶子节点和非叶子节点都会存储数据。B树只有叶子节点才会存储数据非叶子节点只存储键值key叶子节点之间使用双向指针连接最底层的叶子节点形成了一个双向有序链表。 B树的最底层叶子节点包含了所有的索引项。从图上可以看到B树在查找数据的时候由于数据都存放在最底层的叶子节点上所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系但是从另一方面来说由于数据都被放到了叶子节点所以放索引的磁盘块锁存放的索引数量是会跟这增加的所以相对于B树来说B树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况在索引中数据满足了当前查询语句所需要的全部数据此时只需要找到索引即可立刻返回不需要检索到最底层的叶子节点。举例等值查询 假如我们查询值等于9的数据。查询路径磁盘块1-磁盘块2-磁盘块6。 第一次磁盘IO将磁盘块1加载到内存中在内存中从头遍历比较915走左路到磁盘寻址磁盘块2。第二次磁盘IO将磁盘块2加载到内存中在内存中从头遍历比较7912到磁盘中寻址定位到磁盘块6。第三次磁盘IO将磁盘块6加载到内存中在内存中从头遍历比较在第三个索引中找到9取出data如果data存储的行记录取出data查询结束。如果存储的是磁盘地址还需要根据磁盘地址到磁盘中取出数据查询终止。这里需要区分的是在InnoDB中Data存储的为行数据而MyIsam中存储的是磁盘地址。 举例范围查询 假如我们想要查找9和26之间的数据查找路径为磁盘块1-磁盘块2-磁盘块6-磁盘块7 前三次磁盘IO首先查找到键值为9对应的数据定位到磁盘块6然后缓存大结果集中。这一步和前面等值查询流程一样发生了三次磁盘IO。继续查询查找到节点15之后底层的所有叶子节点是一个有序列表我们从磁盘块6中的键值9开始向后遍历筛选出所有符合条件的数据。第四次磁盘IO根据磁盘块6的后继指针到磁盘中寻址定位到磁盘块7将磁盘块7加载到内存中在内存中从头遍历比较9252692626将数据data缓存到结果集中。逐渐具备唯一性后面不会再有26的数据不需要再向后查找查询结束将结果集返回给用户。 可以看到B树可以保证等值和范围查询的快速查找MySQL的索引就采用了B树的数据结构。 MySQL是如何实现索引的 介绍完了索引数据结构那肯定是要带入到Mysql里面看看真实的使用场景的所以这里分析Mysql的两种存储引擎的索引实现MyISAM索引和InnoDB索引 InnoDB索引 以下介绍其实在“什么是索引#索引有哪些”下面的介绍会做一些补充 聚簇索引主键索引 每个InnoDB表都有一个聚簇索引 聚簇索引使用B树构建叶子节点存储的数据是整行记录。一般情况下聚簇索引等同于主键索引当一个表没有创建主键索引时InnoDB会自动创建一个ROWID字段来构建聚簇索引。 InnoDB创建索引的具体规则如下 在表上定义主键PRIMARY KEYInnoDB将主键索引用作聚簇索引。如果表没有定义主键InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。如果以上两个都没有InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。 除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB辅助索引中的叶子节点存储的数据是该行的主键值。 在检索时InnoDB使用此主键值在聚簇索引中搜索行记录。 这里以user_innodb为例user_innodb的id列为主键age列为普通索引。 CREATE TABLE user_innodb (id int(11) NOT NULL AUTO_INCREMENT,username varchar(20) DEFAULT NULL,age int(11) DEFAULT NULL,PRIMARY KEY (id) USING BTREE,KEY idx_age (age) USING BTREE ) ENGINE InnoDB; InnoDB的数据和索引存储在t_user_innodb.ibd文件中InnoDB的数据组织方式是聚簇索引。主键索引的叶子节点会存储数据行辅助索引的叶子节点只会存储主键值。 等值查询数据 select * from user_innodb where id 28; 先在主键树中从根节点开始检索将根节点加载到内存比较2875走左路。1次磁盘IO将左子树节点加载到内存中比较162847向下检索。1次磁盘IO检索到叶节点将节点加载到内存中遍历比较162818282828。查找到值等于28的索引项直接可以获取整行数据。将改记录返回给客户端。1次磁盘IO 磁盘IO数量3次。 辅助索引普通索引 除聚簇索引之外的所有索引都称为辅助索引InnoDB的辅助索引只会存储主键值而非磁盘地址也非行记录。 以表user_innodb的age列为例age索引的索引结果如下图。 辅助索引的底层叶子节点是按照ageid的顺序排序先按照age列从小到大排序age相同时按照id列从小到大排序。使用辅助索引需要检索两遍索引首先检索辅助索引获得主键然后根据主键到主键索引中检索获得数据记录。 辅助索引等值查询的情况 select * from t_user_innodb where age19; 根据在辅助索引树中获取的主键id到主键索引树检索数据的过程称为回表查询。 磁盘IO数辅助索引3次获取记录回表3次 请记住这里产生了回表显然回表是消耗很多性能的使查询效率大大降低磁盘IO次数显而易见的增多所以我们有覆盖索引来优化他 联合索引 刚才介绍的辅助索引其实就是对单一字段做一个普通索引罢了其实联合索引就是创建普通索引的时候多索引几个字段

1创建一个复合索引

create index index_name on table_name(col_name1,col_name2,…);

2修改表结构的方式添加索引

alter table table_name add index index_name(col_name1,col_name2,…); 最左匹配原则 它指出查询条件必须从联合索引的最左边的列开始匹配且必须连续匹配。只有当查询条件包含联合索引的最左边的列时数据库引擎才会使用该索引。 使用联合索引时存在‘最左匹配原则’要求where后面的查询条件需要包含构建联合索引的第一个字段 最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。 上面的col_name1/col_name2/colname3我在下面就称为a/b/c列 在组合索引树中最底层的叶子节点按照第一列a列从左到右递增排序但是b列和c列是无序的b列只有在a列值相等的情况下小范围内有序递增而c列只能在a和b两列值相等的情况下小范围内有序递增。 就像上面的查询B 树会先比较a列来确定下一步应该检索的方向往左还是往右。如果a列相同再比较b列但是如果查询条件中没有a列B树就不知道第一步应该从那个节点开始查起。 可以说创建的idx(abc)索引相当于创建了(a)、(ab)、(abc)三个索引。 接下来通过几条sql来看看最左匹配原则的匹配过程以及不遵循最左匹配原则导致的索引失效问题 CREATE TABLE abc_innodb (id int(11) NOT NULL AUTO_INCREMENT,a int(11) DEFAULT NULL,b int(11) DEFAULT NULL,c varchar(10) DEFAULT NULL,d varchar(10) DEFAULT NULL,PRIMARY KEY (id) USING BTREE,KEY idx_abc (a, b, c) ) ENGINE InnoDB;show index from abc_innodb– 是否会匹配索引 – 1 explain select * from abc_innodb where a 1 and b 2 explain select * from abc_innodb where b 2 and a 1– (2) explain select * from abc_innodb where b 2– (3) explain select * from abc_innodb where a 1 and b2 and c3 哪几条SQL会使用索引索引的匹配过程 explain select * from abc_innodb where a 1 and b 2 这一条肯定是匹配的完全满足最左匹配原则先按照a来查询索引a相同的时候再按照b来找 explain select * from abc_innodb where b 2 and a 1 这一条也是匹配的但是这里是先按照b来找啊应该不满足最左匹配原则了啊 因为这里MySQL的优化器对我们的sql进行了优化即只要where后面包含了联合索引的第一个字段我就能匹配到索引 explain select * from abc_innodb where b 2 这个是无法匹配索引的因为这里只按照b来进行索引查找而b只是局部有序只有a才是全局有序 explain select * from abc_innodb where a 1 and b2 and c3 这个是可以匹配的但是只能部分匹配a1肯定是匹配的但是遇到了b2b只是在a的小范围有序 其他a范围的b可能也包含2的数据搜易到这里肯定不能继续使用索引了 注使用组合索引查询时mysql会一直向右匹配直至遇到范围查询(、、between、like)等就会停止匹配。 总结如何用好联合索引 除了在写sql的时候满足最左匹配原则之外还应该把频繁使用的列、区分度高的列放在前面频繁使用代表索引利用率高区分度高代表筛选粒度大这些都是在索引创建时候需要考虑的优化场景 覆盖索引原则 如何避免回表 覆盖索引并不是一种索引结构覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候我们只可以拿到相应的主键值想要获取最终的数据记录还需要根据主键通过主键索引再去检索最终获取到符合条件的数据记录。 那么覆盖索引是指一个索引包含了查询所需的全部列。当查询只需要这些列时数据库引擎可以直接从索引中读取数据而不需要访问表中的数据行。这种优化可以减少磁盘 I/O 和提高查询速度。 显然覆盖索引会增加索引的大小因为索引中包含更多的列。因此需要权衡索引的大小和查询性能。 如何使用覆盖索引 CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_date DATE,total_amount DECIMAL(10, 2),status VARCHAR(50) ); 如果你经常执行以下查询 SELECT order_id, customer_id, order_date FROM orders WHERE status Completed; 你可以用辅助索引把他们三个字段都圈起来创建多个字段的联合索引这样就能避免单一的辅助索引致使回表问题 这其实是一种数据库索引调优方案 CREATE INDEX idx_covering ON orders (status, order_id, customer_id, order_date); 在这个索引中status 是查询条件列order_id、customer_id 和 order_date 是查询结果列。这样索引包含了查询所需的全部列可以作为覆盖索引使用。 一旦创建了覆盖索引数据库引擎会自动选择使用它来优化查询。你可以通过执行计划来验证是否使用了覆盖索引。例如在 MySQL 中可以使用 EXPLAIN 命令来查看查询计划 EXPLAIN SELECT order_id, customer_id, order_date FROM orders WHERE status Completed; 在查询计划中如果看到 Using index则表示使用了覆盖索引 MyIsam索引 对于MyIsam索引我们了解一下就行主要是了解与InnoDB的区别 以一个简单的user表为例。user表存在两个索引id列为主键索引age列为普通索引 CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,username varchar(20) DEFAULT NULL,age int(11) DEFAULT NULL,PRIMARY KEY (id) USING BTREE,KEY idx_age (age) USING BTREE ) ENGINE MyISAMAUTO_INCREMENT 1DEFAULT CHARSET utf8; MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B树构建索引树时叶子节点中存储的键值为索引列的值数据为索引所在行的磁盘地址。 在磁盘IO次数上MyIsam要比InnoDB多一次根据地址去磁盘拿数据的操作 在MyISAM存储引擎中辅助索引和主键索引的结构是一样的没有任何区别叶子节点中data阈存储的都是行记录的磁盘地址。 主键列索引的键值是唯一的而辅助索引的键值是可以重复的。