索引原理与表设计.docxVIP

索引原理与表设计 1?索引原理 索引是有效使用数据库的基础，但你的数据量很小的时候，或许通过扫描整表来存取数据的性能 还能接受，但当数据量极大时，当访问量极大时，就一定需要通过索引的辅助才能有效地存収数 据。一般索引建立的好坏是性能好坏的成功关键。 1.1 InnoDb数据与索引存储细节 使用InnoDb作为数据引擎的Mysql和冇聚集索引的SqIServer的数据存储结构冇点类似，虽然在 物理层面，他们都存储在Page上，但在逻辑上谢，我们可以把数据分为三块：数据区域，索引区 域，主键区域，他们通过主键的值作为关联，配合工作。歙认配置下，一个Page的大小为16K。 一个表数据空间中的索引数据区域中有很多索引，每一个索引都是一颗B+Tree,在索引的B+Tree 中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。 在InnoDB中，表数据文件木身就是按B+Tree组织的一个索引结构，这棵树的叶节点数据域保存 了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件木身就是主索引。 可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要 按主键聚集，所以InnoDB要求表必须有主键（MylSAM可以没有），如果没有显式指定，则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL 自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。 农数据都以Row的形式放在一,个一个人小为16K的Page屮，在每个数据Page屮有都页头信息和 一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET, 便于通过主键能迅速找到其对应的数据位置。 2-9?w?eorw15kTil+TREE ：26 3319-23?ww|15 E0] 4950 I7$7ZlB+TREE66-8350-59Hl 2-9 ?w?eorw 15 kTil +TREE ： 26 33 19-23 ?ww |15 E0] 49 50 I 7$7Zl B+TREE 66-83 50-59 Hl 表空间逻辑结构图 1.2索引优化检索的原理 索引是数据库的灵魂，如果没有索引，数拯库也就是一堆文本文件，存在的意义并不大。索引能 让数据库成几何借数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集 索引（也就是主键）和普通索引。上节我们已经讲解了这两种索引的存储结构，现在我们仔细讲 解下索引是如何工作的。 聚集索引和普通索引都有可能由多个字段组成，我们乂称这种索引为复合索引，123将为大家解 析这种索引的性能情况。 1.2.1聚集索引 从上节我们知晓，Innodb的所有数据是按照聚集索引排序的，聚集索引这种存储方式使得按主键 的搜索十分高效，如杲我们SQL语句的选择条件中有聚集索引，数据库会优先使用聚集索引来进 行检索工作。 数据库引擎根据条件中的主键的值，迅速在B+Tree中找到主键对应的叶节点，然后把叶节点所在 的Page数据库读取到内存，返回给用户，如上图绿色线条的流向。下面我们来运行一条SQL,从 数据库的执行情况分析一下： select * from UP_User where userid = # Query_time: 0.000399 Lock_time: 0.000101 Rows_sent: 2 Ro\A/s_examined: 1 Ro\A/s_affected: 0 Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 lnnoDB_trx_id: 1D4D QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No Filesort: No Filesort_on_disk: No Merge_passes: 0 InnoDB 10 r ops: 0 InnoDB 10 r bytes: 0 InnoDB 10 r wait: 0.000000 InnoDB rec lock wait: 0.000000 InnoDB queue wait: 0.000000 lnnoDB_pages_distinct: 2 SET timestamp=1451104535; select * from UP_User where userid = 我们可以看到，数据库读从磁盘取了两个Page就把809Bytes的数据捉取出来返回给客戸端。 下面我们试一下