关于数据库索引-3

为什么要建立索引，任何时候都要建立索引么？考虑一个问题，当你的数据库只包含2条record的时候，是否需要建立索引，这显然不需要，sequential的查找会更快， 那到底需要建立什么样的索引，这也是个问题，如果建立索引不当还会使查询效率降低。

postgresql里面有个pg_class表，里面包含整个database的statistic信息，通过它可以查询数据库所占的disk page数目，tuple数目

postgresql里面用explain和explain analysis这两条命令对某个sql的查询效率进行分析。

例如有一个database:

Student (student_id, student_given_name, student_family_name )
Course (course_id, course_name, course_description, course_term)
Assessment (total_points, course_id, assessment_id)
Student_Course(student_id, course_id)
Student_Assessment (student_id, assessment_id, score) 

这样开始我们的数据库效率的优化：

SELECT relname, reltuples, relpages FROM pg_class WHERE relname = 'student'; 

得到数据库student内所有table的统计信息，主要包括下面三种：

relname | reltuples | relpage 分别表示数据表名字，tuples数目，以及存储在多少page上。

接着Explain命令

explain select distinct course_id from course where course_term = 'Fal02'

表示搜索courset_term=“Fal02”的不同course_id个数，得到结果：

Unique  (cost=12223.09..12339.76 rows=4667 width=4)

 

               -> Sort (cost=12223.09..12223.09 rows=46666 width=4)

 

               ->  Seq Scan on course  (cost=0.00..8279.99 rows=46666 width=4)

explain不会真正执行sql语句，只是根据已知的信息预测到搜索可能访问的page数目

要是真正的统计sql语句执行的时间需要用explain analysis select distinct course_id from course where course_term = 'Fal02'，读这个log的方法是从底向上阅读，这里cost后面的数据的单位是disk page. 并且如cost=0.00..8279.99表示执行seq scan on course的start-up cost是 page 0.00, total cost 是8279.99 ，第2行的start-up cost是为12223.09-8279.99。 后续依次类推。

会得到下面结果：

Unique  (cost=12223.09..12339.76 rows=4667 width=4)

 

        (actual time=1643.87..1797.34 rows=41803 loops=1)

 

  ->  Sort  (cost=12223.09..12223.09 rows=46666 width=4)

 

               (actual time=1643.86..1706.05 rows=41803 loops=1)

 

        ->  Seq Scan on course  (cost=0.00..8279.99 rows=46666 width=4)

 

                       (actual time=184.83..1075.11 rows=41803 loops=1)

 

Total runtime: 1899.24 msec

 (actual time=184.83..1075.11 rows=41803 loops=1)中ime=184.83表示允许时间，1075.11表示访问disk page

后续通过这两个命令基本上就能查看数据库的性能。