数据库性能优化（一） 索引

当前主流关系型数据库RDBMS都是将平衡树（B树、B+树）作为默认索引的数据结构。

主键与索引

A. 表不加主键，会以无序的形式一行一行的存放在磁盘上。

B. 表增加主键后，转变为树状结构，整个表变成一个聚集索引。

索引的优缺点

优点：索引可提升表的查询速度；为用来排序或分组的字段添加索引，可加快分组和排序。

缺点：降低写入速度。索引平衡树的结构需要时刻保持正确的状态，插入或更新数据，会改变节点中索引数据的内容，平衡树需要重新梳理。时间成本随数据量的增大而增大，对数级复杂度。同时索引需要复制表的数据，增加空间成本。随着数据的插入和修改，索引的空间消耗越来越大，需定期重新build以整理索引结构，减小消耗。

聚簇索引 及 非聚簇索引

聚簇索引：也叫簇类索引，对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。

create clustered index INDEX_NAME on TABLE_NAME(COLUMN_NAME1, COLUMN_NAME2, ...)

非聚簇索引：非聚簇索引的数据行不按索引键值排序和存储，且非聚簇索引的叶子节点不包含数据页，非聚簇索引只是复制索引键的值组成索引结构。

每张表只能建一个聚簇索引，且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。每创建一个非聚簇索引就会出现1个独立的索引结构。增加表的体积，占用空间。

create nonclustered index INDEX_NAME on TABLE_NAME(COLUMN_NAME1, COLUMN_NAME2, ...)

表查找时，通过聚簇索引，可直接查找到数据行；通过非聚簇索引，只能查到索引列的值和主键值，如果查找的列不在索引已有数据范围内，则需要根据主键回表查询。（若select的列是非聚簇索引中包含的列，不必回表查询时，该索引又称“覆盖索引”）

索引结构

图

索引的创建

示例：

create index INDEX_BIRTHDAY on user_info(birthday) 

create index IDX_BDAY_UNAME on user_info(birthday, username)  ——联合索引、可能是覆盖索引

数据量大，查询频繁的表需要加索引。创建原则：

1. 数据类型越小越好，越简单越好

2. 索引列应避免NULL值

3. 大量重复值的列不加索引

4. 多表联表查询的约束条件列，应建立索引

5. 用于排序的字段可以增加索引

6. 用于分组的字段，应视情况增加索引

查询条件过多的表，没有必要加索引，几乎等同于全表查询。

联合索引触发条件（需再确认）

1. 查询条件中的列存在于联合索引中

2. 索引最左侧的列的单条件查询

SQL SERVER的执行计划

USE TSQL2012

GO

select orderId from Sales.Orders

select * from Sales.Orders

执行后，会显示SQL的开销对比

索引类型

普通单列索引

唯一索引（字段唯一，效率高，简化管理）

主键索引（主键、聚簇索引）

外键索引（外键、非聚簇索引）

复合索引（联合索引、联合覆盖）

全文索引

SQLServer、MySql、Oracle的索引区别

SQLServer会将主键默认设置为聚簇索引，若该表是联合主键，如（term, name），则先按term排序，若term相同，按name排序。外键会默认设置为非聚簇索引。