Mysql的索引优化

梦里梦外; 2022-06-18 04:42 109阅读 0赞

一、为什么需要索引（Why is it needed）?  
当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点（或数据块）的指针，不需要连续存储。  
记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（Linear Search）的过程，平均需要访问N/2的数据块，N是表所占据的数据块数目。如果这个字段是一个非主键字段（也就是说，不包含唯一的访问入口），那么需要在N个数据块上搜索整个表格空间。  
但是对于一个有序字段，可以运用二分查找（Binary Search），这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。  
  
二、什么是索引（What is indexing）?  
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。  
副作用是索引需要额外的磁盘空间，对于MyISAM引擎而言，这些索引是被统一保存在一张表中的，这个文件将很快到达底层文件系统所能够支持的大小限制，如果很多字段都建立了索引的话。  
  
三、索引如何工作（How does it work?）  
首先，我们建立一个示范数据库表：  
字段名 数据类型 大小  
id (Primary key) Unsigned INT 4 bytes  
firstName Char(50) 50 bytes  
lastName Char(50) 50 bytes  
emailAddress Char(100) 100 bytes  
注意：使用char是为了指定准确的磁盘占用大小。这个示范数据库包含500万行，而且没有索引。我们将分析一些查询语句的性能，一个是使用主键id（有序）查询，一个是使用firstName（非关键无序字段）。  
  
1、我们的示范数据库有r=5,000,000条记录，每条记录长度R=204字节而且使用MyISAM引擎存储（默认数据块大小为B=1024字节），这张表的块因子（blocking factor）会是bfr = (B/R) = 1024/204 = 5 条记录每磁盘数据块。保存这张表所需要的磁盘块为N = (r/bfr) = 5000000/5 = 1,000,000 blocks。  
在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录假设id字段是查询关键值，不过既然id字段是有序的，可以执行一个二分查询，这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。我们马上就看到了极大的提高。  
现在firstName字段既不是有序的，无法执行二分搜索，数值也不具有唯一性，所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。  
假如索引记录只包含一个索引列以及一个指向原记录数据的指针，那么它显而易见会比原记录（多列）要小。所以索引本身所需要的磁盘块要更少，扫描数目也少。firstName索引表结构如下：  
字段名 数据类型 大小  
firstName Char(50) 50 bytes  
(record pointer) Special 4 bytes  
注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。  
  
2、假设我们的数据库有r = 5,000,000 条记录，建立了一个长R = 54字节的索引，并且使用默认磁盘块大小为1,024字节。那么该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。  
现在使用FirstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找，平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址，这需要进一步的块读取，这样总数达到19 + 1 = 20次数据块访问，这和非索引表的数据块访问次数有天壤之别。  
  
四、什么时候使用索引（When should it be used?）  
鉴于创建索引需要额外的磁盘空间（上面的例子需要额外的277778个磁盘块），以及太多的索引会导致文件系统大小限制所产生的问题，所以对哪些字段建立索引，什么情况下使用索引，需要审慎考虑。

由于索引只是用来加速数据查询，那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间，所以这种情况下应该尽量避免。此外鉴于二分搜索的特性，数据的基数或独立性是很重要的。在基数为2的字段上建立索引，将把数据分割一半，而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序，而且查询优化器可能会在基数小于记录数某个比例时（如30%）的情况下将避免使用索引而直接查询原表，所以这种情况下的索引浪费了空间。