怎样分析数据库缓冲池与SQL查询成本

数据库缓冲池

磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

 那么缓冲池如何读取数据呢？

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

查看缓冲池大小

如果使用的是 MyISAM 存储引擎(只缓存索引，不缓存数据)，对应的键缓存参数为 key_buffer_size，可以用它进行查看。

如果使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大小，命令如下：

mysql> show variables like 'innodb_buffer_pool_size';

mysql> set global innodb_buffer_pool_size = 1073741824;

在 InnoDB 存储引擎中，可以同时开启多个缓冲池，查看缓冲池的个数，使用命令：

mysql> show variables like 'innodb_buffer_pool_instances'; 

查看SQL语句的查询成本

 一条 SQL 查询语句在执行前需要确定查询计划，如果存在多种查询计划的话，MySQL 会计算每个查询计划所需要的成本，从中选择成本最小的一个作为最终执行的查询计划。

如果查看某条 SQL 语句的查询成本，可以在执行完这条 SQL 语句之后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句所需要读取的页(page)的数量。

mysql> show status like 'last_query_cost'

example  

mysql> select userid,rating from movierating where userid = 4169;

结果：2313 rows in set (0.05 sec)

mysql> show status like 'last_query_cost'; 

mysql> select userid,rating from movierating where userid between 4168 and 4175; 

结果：2643 rows in set (0.01 sec)

mysql> show status like 'last_query_cost';

你能看到页的数量是刚才的 1.4 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。