再一次sql优化中一个select count(*)语句因数据量实在太大,已经无法从简单的索引什么进行优化了,在同事的推荐下考虑到了物化视图
物化视图是相对于视图而言的,但是两者实际上并没有什么关系就如java/javaScript一样
首先mysql的视图不是一种物化视图,他相当于一个虚拟表,本身并不存储数据,当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来,优化查询速度,切操作视图的很多命令和普通标一样,这回导致在业务中无法通过sql区分表和视图,是代码变得复杂。
视图是简化设计,清晰编码的东西,他并不是提高性能的,他的存在只会降低性能(如一个视图7个表关联,另一个视图8个表,程序员不知道,觉得很方便,把两个视图关联再做一个视图,那就惨了),他的存在未了在设计上的方便性
实现视图的方法有两种,分别为合并算法和临时表算法,合并算法是指查询视图是将视图定义的sql合并到查询sql中,比如create view v1 as select * from user where sex = m;当我们要查询视图时,mysql会将select id,name from v1;并合成select id,name fromwhere sex= m; 临时表算法是将视图查出来的数据保存到一个临时表中,查询的时候查这个临时表。不管是合并算法和临时算法,都会给数据库带来额外的开销,切如果使用临时表后会使mysql的优化变得很困难,比如索引,而且视图还引入了一些其他的问题,是的其背后的逻辑非常复杂。
当然,视图在某些情况下还是可以帮助提升性能的,单视图的性能很难预测,且在mysql的优化器中,视图的代码执行路径也完全不同,无法直观预测其性能。
物化视图是是查询结果的预运算,物化视图的结果一般存储于表中。物化视图用于需要对查询立即做出响应,而又需要耗费长时间获得结果。物化视图必须能快速更新。它去介于对更新频率和内容的准确性的要求。一般来说物化视图能够在易订时间内及时更新。
Mysql本来不支持视图的。但是在5.0以上的版本,支持了视图功能,但是可惜的是不提供物化视图,但是这也难不住咱们,自己动手丰衣足食。
1.
实现自己的物化视图
看一个它是如何实现的简单的查询实例:
SELECT COUNT(*) FROM MyISAM_table;
由于计数值存储在表的头部 立即返回结果。接下来的例子会耗费几秒到数分钟。
SELECT COUNT(*) FROM innodb_huge;
对此的可能解决方案是创建一个存储所有 InnoDB 行的表。
CREATE TABLE innodb_row_count ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , schema_name VARCHAR(64) NOT NULL , table_name VARCHAR(64) NOT NULL , row_count INT UNSIGNED NOT NULL );
取决于对该信息结果正确性的需要,该表可以每天更新一次(花费系统资源最少,结果错误最大),一小时一次甚至是极端情况下每次改变都更新(最慢)。
另一种可能就是从信息架构中读取数据。但是信息会有高达20%的错误。
SELECT table_schema, table_name, table_rows
FROM information_schema.tables WHERE table_type = ‘BASE TABLE’;
2.
更新物化视图
物化视图的更新方式有很多种。比如:
l 从不更新(只在开始更新,只用于静态数据)
l 根据需要(比如每天,比如每夜)
l 及时(每次数据修改之后)
一半使用的更新方法:
l 全部更新(速度慢,完全从无到有)
l 延时的(速度快,使用log表)
通过在日志表中存储变更信息,通常会产生简单的“快照”或者延时状况:
l 及时更新
l 完全更新
3.
测试
为了理解这个方法,我们举个例子,详细讲解一下。
CREATE TABLE sales ( sales_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , product_name VARCHAR(128) NOT NULL , product_price DECIMAL(8,2) NOT NULL , product_amount SMALLINT NOT NULL ); INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1), (NULL, 'Apple', 2.40, 2), (NULL, 'Apple', 4.05, 3), (NULL, 'Pear', 6.30, 2), (NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3); SELECT * FROM sales;
我们要知道售价和每种产品获得的利润,就要使用到两次的分组查询,我们晓得在mysql中连接查询和分组排序是会用到临时表和filesort的,这个如果数据量大的话,是十分耗时,如题查询如下:
EXPLAIN SELECT product_name , SUM(product_price) AS price_sum, SUM(product_amount) AS amount_sum , AVG(product_price) AS price_avg, AVG(product_amount) amount_agg , COUNT(*) FROM sales GROUP BY product_name ORDER BY price_sum /G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales type: ALL possible_keys: NULL key: NULL key_len: N