Optimize the paging of millions of mysql data entries

Many program friends are writing paging, especially mysql has limitn and m. Paging is simple, but we do not know that there is no problem with paging tens of thousands of data, but it cannot be used in the case of millions or tens of millions. Today we will introduce the two paging optimization methods.

Many program friends are writing paging, especially mysql has limit n and m. Paging is simple, but we do not know that there is no problem with paging tens of thousands of data, but it cannot be used in the case of millions or tens of millions. Today we will introduce the two paging optimization methods.

When writing a function in PHP, you only need to use MySQL in two steps.

1. calculate the total number of pages. The SQL statement is

The Code is as follows:
SELECT count (*) FROM tablename;

2. obtain data based on the specified page number. The corresponding SQL statements are the same on the Internet:

SELECT f1, f2 FROM table LIMIT offset, length

Instance Paging

The Code is as follows:

/*************************************** ****** Class Name: PageSupport Function: displays MySQL Data by page. **************************************** *******/ Class PageSupport { // Attributes Var $ SQL; // SQL query statement of the data to be displayed Var $ page_size; // maximum number of lines per page Var $ start_index; // the sequence number of the first row of the record to be displayed Var $ total_records; // The total number of records. Var $ current_records; // number of records read on the current page Var $ result; // read the result Var $ total_pages; // total number of pages Var $ current_page; // current page number Var $ display_count = 30; // The first and last pages displayed Var $ arr_page_query; // array, which contains the parameters to be passed for pagination Var $ first; Var $ prev; Var $ next; Var $ last; // Method /*************************************** ****** Construct () Input parameters: $ Ppage_size: Maximum number of lines per page **************************************** *******/ Function PageSupport ($ ppage_size) { $ This-> page_size = $ ppage_size; $ This-> start_index = 0; } /*************************************** ****** Constructor :__ destruct () Input parameters: **************************************** *******/ Function _ destruct () { } /*************************************** ****** Get function :__ get () **************************************** *******/ Function _ get ($ property_name) { If (isset ($ this-> $ property_name )) { Return ($ this-> $ property_name ); } Else { Return (NULL ); } } /*************************************** ****** Set function :__ set () **************************************** *******/ Function _ set ($ property_name, $ value) { $ This-> $ property_name = $ value; } /*************************************** ****** Function Name: read_data Function: reads records from a table based on SQL query statements. Returned value: Two-Dimensional attribute array result [Record Number] [field name] **************************************** *******/ Function read_data () { $ Ql = $ this-> SQL; // Query data, database links, and other information should be implemented outside class calls $ Result = _ query ($ psql) or die (mysql_error ()); $ This-> total_records = mysql_num_rows ($ result ); // Use the LIMIT keyword to obtain the record to be displayed on this page If ($ this-> total_records> 0) { $ This-> start_index = ($ this-> current_page-1) * $ this-> page_size; $ Psql = $ psql. "LIMIT". $ this-> start_index. ",". $ this-> page_size; $ Result = mysql_query ($ psql) or die (mysql_error ()); $ This-> current_records = mysql_num_rows ($ result ); // Put the query result in the result Array $ I = 0; While ($ row = mysql_fetch_Array ($ result )) { $ This-> result [$ I] = $ row; $ I ++; } } // Obtain the total number of pages and current page information $ This-> total_pages = ceil ($ this-> total_records/$ this-> page_size ); $ This-> first = 1; $ This-> prev = $ this-> current_page-1; $ This-> next = $ this-> current_page + 1; $ This-> last = $ this-> total_pages; } /*************************************** ****** Function Name: standard_navigate () Function: displays the home page, next page, last page, and not pages. **************************************** *******/ Function standard_navigate () { Echo" "; Echo ""; Echo" "; } /*************************************** ****** Function Name: full_navigate () Function: displays the home page, next page, last page, and not pages. Generate navigation links such as 1 2 3... 10 11 **************************************** *******/ Function full_navigate () { Echo" "; Echo ""; Echo" "; } } ?> Call: Include_once ("../config_jj/sys_conf.inc "); Include_once ("../PageSupportClass. php"); // paging class Include_once ('../smarty_jsnhclass.php '); $ Smarty = new Smarty_Jsnh (); Include_once ("../include/Smarty_changed_dir.php "); $ Smarty-> assign ('title', "Smarty news paging test "); $ PageSupport = new PageSupport ($ PAGE_SIZE); // instantiate the PageSupport object $ Current_page = $ _ GET ["current_page"]; // the current page number. If (isset ($ current_page )){ $ PageSupport->__ set ("current_page", $ current_page ); } Else { $ PageSupport->__ set ("current_page", 1 ); } ?> $ PageSupport->__ set ("SQL", "* from news "); $ PageSupport-> read_data (); // read data If ($ pageSupport-> current_records> 0) // if the data is not empty, assemble the data { For ($ I = 0; $ I <$ pageSupport-> current_records; $ I ++) { $ Title = $ pageSupport-> result [$ I] ["title"]; $ Id = $ pageSupport-> result [$ I] ["id"]; $ News_arr [$ I] = array ('News' => array ('id' => $ id, 'title' => $ title )); } } // Close the database Mysql_close ($ db ); $ Pageinfo_arr = array ( 'Total _ records '=> $ pageSupport-> total_records, 'Current _ page' => $ pageSupport-> current_page, 'Total _ pages' => $ pageSupport-> total_pages, 'First' => $ pageSupport-> first, 'Prev' => $ pageSupport-> prev, 'Next' => $ pageSupport-> next, 'Last' => $ pageSupport-> last ); $ Smarty-> assign ('results', $ news_arr ); $ Smarty-> assign ('pagesupport ', $ pageinfo_arr ); $ Smarty-> display ('news/list. tpl '); ?> Template list. tpl {* I am a Smarty comment, I don't exist in the compiled output *} {* {$ PageSupport. total_records} {$ PageSupport. current_page} {$ PageSupport. total_pages} {$ PageSupport. first} {$ PageSupport. prev} {$ PageSupport. next} {$ PageSupport. last} *} {$ Title} {Foreach item = o from = $ results} {$ O. news. id} {$ o. news. title} {Foreachelse} No data you want to view! {/Foreach} {If ($ pageSupport. total_records> 0 )} {/If}

The syntax is not explained. When the data volume is small, it is okay to write it like this.

What if the data volume is large? It's not generally big. It's millions.

Run the following command:

The Code is as follows:
SELECT id FROM users LIMIT 00,10

On my computer, the first run is shown as follows:

10 rows in set (9.38 sec)

Then run the command, as shown below:

10 rows in set (0.38 sec)

This is not surprising. MySQL caches the SQL statements that have been run and can quickly extract the previous data.

In any case, I cannot accept the first nine seconds or more.

In another way:

The Code is as follows:
SELECT id FROM users WHERE id> 1000000 LIMIT 10;

Display: 10 rows in set (0.00 sec)

As a matter of fact, you can use phpMyAdmin to see "display rows 0-9 (10 in total, the query takes 0.0011 seconds)" and then run the command again, which is about 0.0003 seconds.

Millions of Optimizations

To optimize the query, try to avoid full table scanning. First, consider creating an index on the columns involved in where and order.

2. Try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform full table scanning, for example:

The Code is as follows:
Select id from t where num is null

You can set the default value 0 on num to make sure that the num column in the table does not have a null value, and then query it like this:

The Code is as follows:
Select id from t where num = 0

3. Try to avoid using it in the where clause! = Or <> operator. Otherwise, the engine will discard the index for full table scanning.

4. Try to avoid using or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scanning, for example:

The Code is as follows:
Select id from t where num = 10 or num = 20

You can query it as follows:

The Code is as follows:
Select id from t where num = 10 Union all Select id from t where num = 20

5. Use in and not in with caution. Otherwise, a full table scan may occur, for example:

　

The Code is as follows:
Select id from t where num in (1, 2, 3)

For continuous values, you can use between instead of in:

　　

The Code is as follows:
Select id from t where num between 1 and 3

6. The following query will also cause a full table scan:

　

The Code is as follows:
Select id from t where name like '% abc %'

Classification functions

The Code is as follows:
$ Db = dblink (); $ Db-> pagesize = 20; $ SQL = "select id from collect where vtype = $ vtype "; $ Db-> execute ($ SQL ); $ Strpage = $ db-> strpage (); // Save the paging string in a temporary variable to facilitate output While ($ rs = $ db-> fetch_array ()){ $ Strid. = $ rs ['id']. ','; } $ Strid = substr ($ strid, 0, strlen ($ strid)-1); // construct the id string $ Db-> pagesize = 0; // It is critical to clear the page without canceling the class. In this way, you only need to connect to the database once and do not need to open it again; $ Db-> execute ("select id, title, url, sTime, gTime, vtype, tag from collect where id in ($ strid )"); Fetch_array ():?>
					"Target =" _ blank ">

Echo $ strpage;