Statistical processing and simulation materialized view of massive data in MySQL

Source: Internet
Author: User

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/>);

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.