Statistical processing and simulation materialized view of massive data in MySQL

A materialized view

Materialized views, which are the result of time-consuming operations that are used to calculate and save table joins or aggregates, so that when you execute a query, you can avoid these time-consuming operations and get results quickly.

Only large databases ORACLE10G,DB2 support this feature, and MySQL5.1 does not yet have this functionality. I simulate materialized views through event scheduling and stored procedures. Let's discuss it together. ^-^

Second Preparation knowledge

1 Stored procedure: The person who has played the database knows what he is ~,~

2 Event Scheduling: A new feature that starts at MySQL5.1. It's a timer, frankly. It's about the same as a timer in Java.

SQL code

Show Processlist; --This command can be to view the thread, and if the time Scheduler is started, then multiple

event_scheduler  
set global Event_scheduler = on;--start time scheduler, shut down I don't have to tell you,

off Can  
show processlist\g;--view threads, is not a user:event_scheduler  
      
-if you want to start a single event, execute the following command  
ALTER event ' MyEvent ' On   
completion PRESERVE   
ENABLE;  
--Closes  
ALTER EVENT ' myevent ' on   
completion PRESERVE   
DISABLE;

MySQL does not start the event by default, so you need to modify your My.ini or my.cnf[mysqld] to add the following as Downlink

Event_scheduler=1

Three simulated materialized views

1) First build a base table.

SQL code

CREATE TABLE ' user ' (  
  ' Id ' int () NOT NULL auto_increment,  
  ' name ' varchar (255) DEFAULT null,  
  ' age ' int (one) DE FAULT NULL,  
  PRIMARY KEY (' Id ')  
);

2) Design view

I'd like to inquire about the number of all 18-year-old employees.

Very simply, select COUNT (*) from the user where age=18;

If it's a traditional concept view, in MySQL, each time you access the view, he creates a temporary table and executes it once. In the case of massive data, this efficiency is very low. Materialized views, he will periodically refresh the temporary table, not when you are in use to refresh. And the materialized view's "temp table" exists all the time. So the efficiency is much higher. Take space to change time ^

3 Create a table of materialized views

SQL code

CREATE TABLE ' User_view ' (
  ' id ' int () NOT NULL auto_increment,
  ' C ' int (one) DEFAULT null,
  PRIMARY KEY (' id ') c10/>);