索引原理与表设计

《索引原理与表设计》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、索引原理与表设计1•索引原理索引是有效使用数据库的基础，但你的数据量很小的时候，或许通过扫描整表来存取数据的性能还能接受，但当数据量极大时，当访问量极大时，就一定需要通过索引的辅助才能有效地存収数据。一般索引建立的好坏是性能好坏的成功关键。1.1InnoDb数据与索引存储细节使用InnoDb作为数据引擎的Mysql和冇聚集索引的SqIServer的数据存储结构冇点类似，虽然在物理层面，他们都存储在Page上，但在逻辑上谢，我们可以把数据分为三块：数据区域，索引区域，主键区域，他们通过主键的值作为关联，配合工作。歙认配置下，一个Page的大小为

2、16K。一个表数据空间中的索引数据区域中有很多索引，每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。在InnoDB中，表数据文件木身就是按B+Tree组织的一个索引结构，这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件木身就是主索引。可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MylSAM可以没有），如果没有显式指定，则MyS

3、QL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。农数据都以Row的形式放在一,个一个人小为16K的Page屮，在每个数据Page屮有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。2-9«w»eorw15kTil+TREE：263319-23«ww

4、15E0]4950I7$7ZlB+TREE66-8350-59Hl表空间逻辑结构图

5、1.2索引优化检索的原理索引是数据库的灵魂，如果没有索引，数拯库也就是一堆文本文件，存在的意义并不大。索引能让数据库成几何借数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引（也就是主键）和普通索引。上节我们已经讲解了这两种索引的存储结构，现在我们仔细讲解下索引是如何工作的。聚集索引和普通索引都有可能由多个字段组成，我们乂称这种索引为复合索引，123将为大家解析这种索引的性能情况。1.2.1聚集索引从上节我们知晓，Innodb的所有数据是按照聚集索引排序的，聚集索引这种存储方式使得按主键的搜索十分高效，如杲我们

6、SQL语句的选择条件中有聚集索引，数据库会优先使用聚集索引来进行检索工作。数据库引擎根据条件中的主键的值，迅速在B+Tree中找到主键对应的叶节点，然后把叶节点所在的Page数据库读取到内存，返回给用户，如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下：select*fromUP_Userwhereuserid=10000094;#Query_time:0.000399Lock_time:0.000101Rows_sent:2RoA/s_examined:1RoA/s_affected:0#Bytes_sent:

7、803Tmp_tables:0Tmp_disk_tables:0Tmp_table_sizes:0#lnnoDB_trx_id:1D4D#QC_Hit:NoFull_scan:NoFull_join:NoTmp_table:NoTmp_table_on_disk:No#Filesort:NoFilesort_on_disk:NoMerge_passes:0#InnoDB10rops:0InnoDB10rbytes:0InnoDB10rwait:0.000000#InnoDBreclockwait:0.000000InnoDBqueuewait

8、:0.000000#lnnoDB_pages_distinct:2SETtimestamp=1451104535;select*fromUP_Userwhereuserid=10000094;我们可以看到，数据库读从磁盘取了两个Page就把809Bytes的数据捉取出来返回给客戸端。下面我们试一下，如杲试一下选取条件没有包含主键和索引的情况：select*fromUP_User'wherebigPortrait='5F29E883BFA8903B';ttQuery_time:0.002869Lock_time:0.000094Rows_sen

9、t:2RoA/s_examined:1816Rows_affected:0#Bytes_sent:792Tmp_tables:0Tmp_disk_tables:0