网站空间根目录,昆明网络营销公司哪家好,网站制作进度表,潜江资讯网二手车MySQL官方对索引的定义为: 索引 (Index) 是帮助MySQL高效获取数据的数据结构。
提取句子主干#xff0c;就可以得到索引的本质:索引是数据结构。
1. 什么是索引#xff0c;索引的作用
索引是一种用于快速查询和检索数据的数据结构#xff0c;帮助mysql提高查询效率的数据…MySQL官方对索引的定义为: 索引 (Index) 是帮助MySQL高效获取数据的数据结构。
提取句子主干就可以得到索引的本质:索引是数据结构。
1. 什么是索引索引的作用
索引是一种用于快速查询和检索数据的数据结构帮助mysql提高查询效率的数据结构而且是排好序的数据结构存储在磁盘文件里。
索引的作用是在不读取整个表的情况下使得数据库应用程序可以更快地查找数据用户无法看到索引只能被用来加速检索或查询。
优点
使用索引可以大大加快数据的检索速度大大减少检索的数据量, 这也是创建索引的最主要的原因。通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。
缺点
创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候如果数据有索引那么索引也需要动态的修改会降低 SQL 执行效率。索引需要使用物理文件存储也会耗费一定空间。
但是使用索引一定能提高查询性能吗?
大多数情况下索引查询都是比全表扫描要快的。但是如果数据库的数据量不大那么使用索引也不一定能够带来很大提升。
索引建立的原则
在最频繁使用的、用以缩小查询范围的字段上建立索引。在最频繁使用的、需要排序的字段上建立索引。对于查询中很少涉及的列或者重复值比较多的列不宜建立索引。
2. 索引的类型
1. 主键索引(Primary Key)
数据表的主键列使用的就是主键索引。唯一标识主键不可重复只能有一个列作为主键
一张数据表有只能有一个主键并且主键不能为 null不能重复。可以理解为一种特殊的唯一索引
在 MySQL 的 InnoDB 的表中当没有显示的指定表的主键时InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段如果有则选择该字段为默认的主键否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
2. 二级索引(辅助索引
二级索引又称为辅助索引是因为二级索引的叶子节点存储的数据是主键。也就是说通过二级索引可以定位主键的位置。
唯一索引普通索引前缀索引等索引属于二级索引。
唯一索引(Unique Key) 唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据但是允许数据为 NULL一张表允许创建多个唯一索引。普通索引(Normal Index) 也叫单列索引给表中的某一个列创建索引即一个索引只包含单个列一个表可以有多个单列索引。普通索引的唯一作用就是为了快速查询数据一张表允许创建多个普通索引并允许数据重复和 NULL。前缀索引(Prefix) 前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引相比普通索引建立的数据更小 因为只取前几个字符。全文索引(Full Text) 全文索引主要是为了检索大文本数据中的关键字的信息是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了全文索引。 3. 索引的底层数据结构
1. B 树 B树
B 树也称 B-树,全称为 多路平衡查找树 B 树是 B 树的一种变体。B 树和 B树中的 B 是 Balanced 平衡的意思。
目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 BTree 作为索引结构。
B树是一种平衡多路查找树满足平衡二叉树的规律同时可以拥有多个子树子树的数量取决于关键字的数量关键字数量1因此从这个特征来看在存储同样数据量B树会更低一些。B树是在B树的基础上做了增强。主要有两点 B树的数据存储在每个节点上而B树中的数据存储在叶子节点上并且通过双向链表的方式对叶子节点的数据进行了连接。B树的子树数量等于关键词数量加1B树的子树数量等于关键字的数量。
2. 为什么选择B/B树为什么要用 B树为什么不用二叉树
B/BTree更适合文件系统的索引/更适合硬盘上查询的数据结构。
1.高度低--io次数少2.顺序io只需一次扫描数据 随机io--性能高3.多路子树数据量大不能一次性全部加载到内存时会每次加载树的一个节点由于多路子树的节点数量多于普通树的节点所以每次加载的数量更多--速度更快
为什么不是一般二叉树
二者存储数据的结构可以看出二叉树随着数据的增加树的高度会越来越高而B树是越来越胖。
树的高度越来越高增加了I/O次数导致查询效率减低。而B树随着数据量的增加树的宽度越来越大这样空间利用率更高可减少I/O次数查询效率较快。
我们知道在内存比在磁盘的数据查询效率快得多。如果树这种数据结构作为索引那我们每查找一次数据就需要从磁盘中读取一个节点也就是我们说的一个磁盘块但是平衡二叉树可是每个节点只存储一个键值和数据的如果是B树可以存储更多的节点数据每个节点包含多个数据树的高度也会降低因此读取磁盘的次数就降下来啦查询效率就快啦。
为什么不是B树而是B树呢
B树的层级更少。相较于B树B树每个非叶子节点存储的关键字数更多树的层级更少所以查询数据更快。B树查询效率更高。B树使用双向链表串连所有叶子节点而且数据是按照顺序排列的区间查询效率更高因为所有数据都在B树的叶子节点扫描数据库只需扫一遍叶子结点就行了但是B树则需要通过中序遍历才能完成查询范围的查找。B树查询效率更稳定。B树只有叶子结点存放数据的data值非叶子节点上只存储key值信息B树每次都必须查询到叶子节点才能找到数据而B树查询的数据可能不在叶子节点也可能在这样就会造成查询的效率的不稳定。B树的磁盘读写代价更小。B树的内部节点只存储key值信息这样可以大大加大每个节点存储的key值数量降低BTree的高度因此通常B树矮更胖查询产生的I/O更少。B树节点中不仅存了数据的key值还有data值而每一个页的存储空间是有限的(16KB)如果data数据较大时将会导致一个页能存储的key的数量很小当存储的数据量很大时同样会导致B-Tree的深度较大增大查询时的磁盘I/O次数进而影响查询效率。
3. 不同引擎对于B树的实现
BTree在两种存储引擎的实现方式是不同的。
MylSAMBTree叶节点的「data域存放的是数据记录的地址」
在索引检索的时候首先按照BTree搜索算法搜索索引如果指定的key存在则取出其data域的值然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”
InnoDB其数据文件「本身就是索引文件」。
相比MyISAM索引文件和数据文件是分离的其表数据文件本身就是按BTree组织的一个索引结构「树的叶节点data域保存了完整的数据记录」。
这个索引的key是数据表的主键因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引或聚集索引”。而其余的索引都作为辅助索引辅助索引的data域存储相应记录主键的值而不是地址这也是和MyISAM不同的地方。
4. B Tree索引和Hash索引区别
B树可以进行范围搜索hash索引只支持的操作符等值查询。B树支持order by进行排序hash索引没办法利用索引完成排序。B树支持多列联合索引的最左匹配规则。hash索引不支持如果有大量重复健值得情况下hash索引的效率会很低因为哈希冲突问题。B树使用like进行模糊查询的时候like后面%开头可以起到优化作用Hash索引不行。
Hash索引的缺点
查询性能受hash冲突率影响性能不稳定只能通过等值匹配的方式查询不能范围查询结构存储上没有顺序查询时排序无法支持
InnoDB引擎有一个特殊额功能叫做“自适应哈希索引”当 InnoDB注意到某些索引值被使用得非常频繁时它会在内存中基于B-Tree索引之上再创建一个哈希索引这样就让B-Tree索引页具有哈希索引的一些优点比如快速的哈希查找。
4. 聚集索引和非聚集索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
聚集索引是依据主键创建的索引除了主键以外的其他索引都是非聚集索引。
不是单独的索引类型是一种数据存储方式。
1. 介绍聚集索引和非聚集索引
在InnoDB引擎里一张表的数据对应的物理文件本身是按照B树来组织的而聚集索引就是按照每张表的一个主键来构建这样一个B树叶子节点里面存储了表里面的每一行数据记录基于这样一个特征聚集索引不仅仅是一种索引类型还是一种数据存储方式。
同时意味着每张表里必须有主键没有主键的话innodb会默认添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况建议使用自增id作为主键因为id本身具有连续性对应数据也会按照顺序去存储到磁盘上写入和检索性能都很高。
innodb里只能存在一个聚集索引如果有多个那意味着这个表会有多个副本这不仅会造成空间浪费还会导致数据的维护困难。
innodb主键索引存储了一个表的完整数据主键和行记录放在同一个叶节点找到了主键也就找到了行记录。所以如果是基于非聚集索引去查找一条数据最终还是需要访问主键索引来进行检索。
跟MyISAM引擎的非聚集索引不同的是MyISAM叶节点保存的是地址而InnoDB是主键InnoDB非聚集索引的索引文件和数据文件分开存储索引文件的叶节点只保存主键在查找时要先找到叶节点中的主键再根据主键去主索引文件查找详细行记录 2. 回表查询
上述InnoDB引擎中非主键索引查找数据时需要先找到主键再根据主键查找具体行数据这种现象叫回表查询。
如何解决覆盖索引即将查询sql中的字段添加到联合索引里面只要保证查询语句里面的字段都在索引文件中就无需进行回表查询让索引范围覆盖住我们select 的范围就不会发生回表查询。
比方说有个用户表有id、name、age、addr四个字段其中id为主键主键自带主键索引无需创建
值11、小张、18、成都
值22、小黄、20、北京
这种查询就必须先在索引文件中找到name为小张的索引节点很明显这个节点里面只有id因为这张表只有主键索引再根据id去数据文件查找具体数据。
如果把name、age、addr建立到联合索引在找到name为小张的索引节点时发现里面已经有了我们所需要的age、addr就无需再到数据文件查找
当然实际开发中不可能把所有字段建立到联合索引应根据实际业务场景把经常需要查询的字段建立到联合索引即可。
3. 索引下推
索引下推是 MySQL 5.6 版本中提供的一项索引优化功能可以在非聚簇索引遍历过程中对索引中包含的字段先做判断过滤掉不符合条件的记录减少回表次数。
不使用索引条件下推优化时的查询过程 获取下一行首先读取索引信息然后根据索引将整行数据读取出来。 然后通过where条件判断当前数据是否符合条件符合返回数据。 使用索引条件下推优化时的查询过程 获取下一行的索引信息。 检查索引中存储的列信息是否符合索引条件如果符合将整行数据读取出来如果不符合跳过读取下一行。 用剩余的判断条件判断此行数据是否符合要求符合要求返回数据。 5. 联合索引和覆盖索引
1. 覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中如果不是主键索引叶子节点存储的是主键列值。最终还是要“回表”也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的不做回表操作
覆盖索引即需要查询的字段正好是索引的字段那么直接根据该索引就可以查到数据了而无需回表查询。
2. 联合索引
使用表中的多个字段创建索引就是 联合索引也叫 组合索引 或 复合索引。
覆盖索引是通过联合索引来实现的。
3. 最左前缀匹配原则
最左前缀匹配原则指的是在使用联合索引时MySQL 会根据联合索引中的字段顺序从左到右依次到查询条件中去匹配如果查询条件中存在与联合索引中最左侧字段相匹配的字段则就会使用该字段过滤一批数据直至联合索引中全部字段匹配完成或者在执行过程中遇到范围查询如 、、between 和 以%开头的like查询 等条件才会停止匹配。
如有索引 (a,b,c,d)查询条件 a1 and b2 and c3 and d4则会在每个节点依次命中a、b、c无法命中d。(c已经是范围查询了d肯定是排不了序了)
因此列的排列顺序决定了可命中索引的列数。
6. 创建索引及索引的优化
1.选择合适的字段创建索引
不为 NULL 的字段 被频繁查询的字段 被作为条件查询的字段 频繁需要排序的字段被经常频繁用于连接的字段
2.被频繁更新的字段应该慎重建立索引。
3.尽可能的考虑建立联合索引而不是单列索引。
4.注意避免冗余索引 。
5.考虑在字符串类型的字段上使用前缀索引代替普通索引。
7. MySQL 为表字段添加索引
// 主键索引
ALTER TABLE table_name ADD PRIMARY KEY ( column )//唯一索引
ALTER TABLE table_name ADD UNIQUE ( column )//普通索引
ALTER TABLE table_name ADD INDEX index_name ( column )//全文索引
ALTER TABLE table_name ADD FULLTEXT ( column)//联合索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
8、索引原则
索引不是越多越好。不要对进程变动数据加索引。小数据量的表不需要加索引。索引一般加在常用来查询的字段上!
索引的数据结构 Hash 类型的索引 Btree : InnoDB 的默认数据结构~
