Mysql索引

最新推荐文章于 2024-06-17 15:31:11 发布

zero__007

最新推荐文章于 2024-06-17 15:31:11 发布

阅读量160

点赞数

分类专栏： # 【MySQL】

【MySQL】专栏收录该内容

23 篇文章 0 订阅

订阅专栏

转载自：https://blog.csdn.net/elricboa/article/details/78650281
https://blog.csdn.net/broadview2006/article/details/78060821
https://blog.csdn.net/liangkaiping0525/article/details/82284236

索引的优缺点

优点：

大大加快数据的检索速度，这也是创建索引的最主要的原因；
可以创建唯一性索引，保证数据库表中每一行数据的唯一性；
可以将表的外键制作为索引，加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
将随机I/O变为顺序I/O，帮助服务器避免排序和临时表，在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间；
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大；
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，就是整个表就变成了一个索引。

索引结构

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。
为什么使用B-Tree／B+Tree？一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储到磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。因此类似红黑树就不会被考虑。
为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。B-Tree／B+Tree恰好有这个特性。
MySQL使用B+Tree做索引。为啥不是B-Tree呢，可以参看https://blog.csdn.net/zero__007/article/details/82468577。

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。
MyISAM索引实现：MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的物理地址。MyISAM中主索引与辅助索引形式是一样的，主索引要求key不能重复，辅助索引key可以重复。这种索引方式叫非聚集索引。

InnoDB索引实现：虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
InnoDB的数据和索引聚集在同一个文件中。而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，InnoDB的索引方式叫做“聚集索引”。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，最好使用一个与业务无关的自增字段作为主键。
InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。

索引树结构中各节点的值来自于表中的索引字段，假如给user表的name字段加上索引，那么索引就是由name字段中的值构成，在数据改变时，需要一直维护索引结构的正确性。如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。
然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，称之为覆盖索引查询，也就是平时所说的复合索引或者多字段索引查询。当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

索引的最左匹配特性（即从左往右匹配）：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

下面说下索引建好了该怎么用？毕竟大家花了不少时间和空间代价建了索引，至少得回本吧？
如果出现一些不好的SQL语句，那么索引就白建了。下面通过一些具体的例子来看索引的正确用法。
　　①语句一：select name from商品表。不会用到索引，因为没有where语句。
　　②语句二：select * from商品表where name=‘Java书’，会用到索引，如果项目里经常用到name来查询，且商品表的数据量很大，而name值的重复率又不高，那么建议建索引。
　　③语句三：select * from商品表where name like‘Java%’这是个模糊查询，会用到索引。用like进行模糊查询时，如果第一个就是模糊的匹配符，比如where name like‘%java’，那么在查询时不会走索引。在其他情况下，不论用了多少个%，也不论%的位置，只要不出现在第一个位置，那么都能用到索引。

学生成绩表里有两个字段：姓名和成绩。现在对成绩这个整数类型的字段建索引。
　　①第一种情况，当数字型字段遇到非等值操作符时，无法用到索引。
　　比如： select name from学生成绩表where成绩>95,一旦出现大于符号，就不能用到索引，为了用到索引，应该改一下SQL语句里的where从句：where成绩in(96,97,98,99,100)
　　②第二种情况，如果对索引字段进行了某种左值操作，那么无法用到索引。
　　能用到索引的写法：select name from学生成绩表where成绩=60
　　不能用到索引的写法：select name from学生成绩表where成绩+40=100

③第三种情况，如果对索引字段进行了函数操作，那么无法用到索引。
比如SQL语句：select * from商品表where substr(name)=‘J’，希望查询商品名首字母是J的记录，可一旦针对name使用函数，即使name字段上有索引，也无法用到。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。
唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

主键索引，数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引，在聚集索引中，表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

局部性原理与磁盘预读
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页(在许多操作系统中，页得大小通常为4k)，主存和磁盘以页为单位交换数据。

当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

跟性能相关的最重要的区别就是MyISAM和InnoDB实现的锁机制不一样.MyISAM 使用的是表锁，而InnoDB实现的是行锁。

MyISAM为表级锁
由于MyISAM写进程优先获得锁，使得读锁请求靠后等待队列。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。
如果在大量更新操作的情况下，使得很难获得读锁。从而造成阻塞。
所以MyIsam不适合做大量更新操作。
2 )INNODB的行锁是基于索引实现，如果不通过索引访问数据，Innodb会使用表锁。
表级锁更适合以查询为主，只有少量按索引条件更新数据的应用。
行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又并发查询。因为只锁定要操作的行，所以可以多个线程同时操作不同的行（只要不操作其他线程已经锁定的行）。