数据库索引是什么,数据库索引是什么怎么构建

当数据库中数据量比较少的时候，哪怕全部检索也可以很快，但如果数据量达到了百万，千万，上亿的时候，还是全表扫描，那么数据查询的速度会慢的让人无法忍受。

索引的作用，就是为了加快数据查询，类似于我们查不认识的字时，使用字典的目录一样，在字典里面快速查询出不认识的字。字典可以根据读音的首字母，偏旁部首，笔画来查询。同样的，索引也有hash索引，b-tree索引，GIN索引等不同索引类型，根据查询的场景不同，可以选择创建对应的索引类型。

Postgresql支持丰富的索引类型，并且根据索引框架支持用户开发自定义的索引，下面列举下常用的索引类型及适用范围

索引类型实现方法适用范围b-tree使用b-tree数据结构来存储索引数据等值查询或范围查询，以及in、between、is null、order by等，默认索引类型hash基于hash表实现等值查询，尤其索引列值非常长的情况gist使用一种平衡的树形结构访问方法多维数据类型和集合数据类型gin通用倒排索引，存储的是键值与倒排表数组、jsonb、全文检索、模糊查询等brin块范围索引索引列的值与物理存储相关性很强，比如时序数据

mysql的索引类型和数据库引擎相关性较强，不过最常用的B树索引是支持的

索引类型MyISAMInnoDBb-treeyesyeshashnonoR-TreeyesnoFull-Text(类似gin)yesno

InnoDB 默认创建的主键索引是聚族索引（Clustered Index），其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚族索引。

create index i1 on t2 (c1); create index i2 on t2 (c1,c2);

pg的多列(联合)索引仅支持b-tree、gist、gin、brin类型，其中b-tree的多列索引，仅在索引的第一个字段出现在查询条件中才有效(最左匹配原则)，而其他类型的多列索引可以支持任意字段查询 对于多字段查询，多列索引要比单列索引的查询速度快，可以避免回表查询，但对于单字段查询，多列索引就要比单列索引查询速度慢了，这里需要根据表的实际查询sql类型、频率，综合考虑是否需要使用多列索引。

部分索引是指支持在指定条件的记录上创建索引，通过where条件指定这部分记录，比如：

postgres=# create table test(id int, c1 varchar(10)); CREATE TABLE postgres=# insert into test select generate_series(100001,100100),'invalid'; INSERT 0 100 postgres=# create index i1 on test (c1) where c1 = 'invalid'; CREATE INDEX postgres=# explain analyze select * from test where c1 = 'invalid'; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Index Scan using i1 on t3 (cost=0.14..4.16 rows=1 width=11) (actual time=0.035..0.079 rows=100 loops=1) Planning Time: 0.485 ms Execution Time: 0.135 ms (3 rows)

实际上对于数据分布不均的字段，创建正常的索引，在查询占比较小值时也是可以走索引的，查询占比较大值时无法走索引，如下所示，部分索引的优势在于索引体积小，维护代价也比较小

函数索引指可以使用一个函数或者表达式的结果作为索引的字段，比如：

postgres=# create index i1 on test ((lower(c1))); CREATE INDEX postgres=# explain analyze select * from test where lower(c1) = 'xxx'; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=20.29..783.05 rows=500 width=37) (actual time=0.063..0.063 rows=0 loops=1) Recheck Cond: (lower(c1) = 'xxx'::text) -> Bitmap Index Scan on i1 (cost=0.00..20.17 rows=500 width=0) (actual time=0.060..0.060 rows=0 loops=1) Index Cond: (lower(c1) = 'xxx'::text) Planning Time: 0.406 ms ExecutIOn Time: 0.095 ms (6 rows) postgres=# explain analyze select * from test where c1 = 'xxx'; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..2084.00 rows=1 width=37) (actual time=19.016..19.016 rows=0 loops=1) Filter: (c1 = 'xxx'::text) Rows Removed by Filter: 100000 Planning Time: 0.121 ms Execution Time: 19.048 ms (5 rows)

此时如果直接使用c1字段作为查询条件是无法走索引的，同理如果创建的是普通索引，在查询时对字段加上了函数或者表达式，都不会走索引，我们应始终避免出现这样的问题

在涉及order by操作的sql时，b-tree索引返回的结果是有序的，可以直接返回，而其他索引类型，需要对索引返回结果再进行一次排序。b-tree索引的默认排序为升序，空值放在最后，创建索引时可以指定排序方式，如按倒序排序时，空值默认是放在最前的，但往往我们的查询并不想展示空值的结果，此时可以在创建索引时指定排序desc nulls last以达到和查询sql切合的目的。

索引需要占用额外的物理空间，如果表中的数据变化，也需要同步维护索引中的数据，对数据库的性能会有一定影响。考虑到索引的维护代价、空间占用和查询时回表的代价，不能认为索引越多越好。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。不能认为建了索引就一定有效，对于后缀的匹配查询、查询中不包含联合索引的第一列、查询条件涉及函数计算等情况无法使用索引。此外，即使SQL本身符合索引的使用条件，MySQL也会通过评估各种查询方式的代价，来决定是否走索引，以及走哪个索引。

查询数据可以直接在聚簇索引上进行全表扫描，也可以走二级索引扫描后到聚簇索引回表。那么PostgreSQL/MySQL到底是怎么确定走哪种方案的呢。在满足能走索引的条件下，最终是否走索引由计划器生成的执行计划决定，PostgreSQL/MySQL中执行计划是完全基于代价估计的，如果估算的代价为全表扫描最优，则不会使用索引扫描

这里的代价，包括IO成本和CPU成本：

IO成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的IO成本常数是1（也就是读取1个页成本是1）。

CPU成本，是检测数据是否满足条件和排序等CPU操作的成本。默认情况下，检测记录的成本是0.2。基于此，我们分析下全表扫描的成本。

全表扫描，就是把聚簇索引中的记录依次和给定的搜索条件做比较，把符合搜索条件的记录加入结果集的过程。要计算全表扫描的代价需要两个信息：

1.聚簇索引占用的页面数，用来计算读取数据的IO成本；

2.表中的记录数，用来计算搜索的CPU成本。

有时会因为统计信息的不准确或成本估算的问题，实际开销会和MySQL统计出来的差距较大，导致MySQL选择错误的索引或是直接选择走全表扫描，这个时候就需要人工干预，使用强制索引了。

• 对于 Hash 索引实现的列，如果使用到范围查询，那么该索引将无法被优化器使用到。Hash 索引只有在“=”的查询条件下，索引才会生效。如果涉及范围查询则应建立b-tree索引
• 以 % 开头的 LIKE 查询将无法利用节点查询数据，这种情况下需要考虑gin索引或者es这种全文检索的方式
• 使用复合索引时，需要使用索引中的最左边的列进行查询，才能使用到复合索引。

例如我们在 order 表中建立一个复合索引 idx_user_order_status(order_no, status, user_id)，如果我们使用 order_no、order_no status、order_no status user_id 以及 order_no user_id 组合查询，则能利用到索引；而如果我们用 status、status user_id 查询，将无法使用到索引，这也是我们经常听过的最左匹配原则。

• 如果查询条件中使用 or，且 or 的前后条件中有一个列没有索引，那么涉及的索引都不会被使用到。

• 没有创建索引，建表的时候一定不要忘记建立可能的索引，创建索引需要按照ESR原则进行
• 索引失效的情况，如查询字段上使用表达式导致索引失效比如在c1字段上存在一个b-tree索引，where c1 1 > 10000这个查询条件不会走索引，而where c1 > 10000-1可以走索引。
• 查询列表数据不分页，对于列表展现数据，在数据量特别大的情况，一次性返回所有数据一般不具有实际的业务意义，此时应通过limit offset进行分页，这样有机会利用到索引扫描和排序，降低全表扫描的影响，同时也能减小返回数据包过大的负担。
• count (*) 时order by做无用排序由于列表展现与列表查数经常成对儿出现，有可能在复用列表展现的sql时在查数时也加入了排序操作，此时无论是否加上排序操作，得到的最终结果是一致的，但加上排序时大大增加了得到目标结果的代价。
• 跨表进行分组、排序，当涉及到跨表分组、排序时，需要把两个表的结果集汇总到一起进行排序、分组，这里的消耗是非常大的，此时可以考虑去冗余部分字段，使分组、排序操作在一个表中完成，这样能够利用到索引，起到优化效果。
• 慢sql对数据库cpu消耗极大，严重时甚至会宕机