InnoDB Count Optimization test

For index optimization really is the door topic, first to study the most common problem, InnoDB engine how to let count (*) faster.

First you need to be clear

　　InnoDB default is to establish a clustered index on the primary key, if there is no primary key, it is a unique and non-null value of the index to replace, if not, InnoDB internally will maintain one such index.

The clustered index is the index + data, and the two-level index is the point of the primary key-clustered index.

　　So through the conjecture above

1. The clustered index in the table if there are varchar,text fields, if the stored data is more, the clustered index will be very large, it will be divided into a large number of data pages. (Here the data page is what, I also understand is not very deep, please own Baidu)

2. It is much faster to go to count (*) According to the level two index.

Do what you want, start practicing

#只有基本的字段Create TableA1 (IDint  not NULLAuto_increment,user_name varchar( -) not NULL, User_pwdChar( +) not NULL, Regtimedatetime  not NULL default Current_timestamp,    Primary Key(ID)) CharSet=UTF8; #有个varchar (255This is the field where big data is storedCreate TableA2 (IDint  not NULLAuto_increment,user_name varchar( -) not NULL, User_pwdChar( +) not NULL, Descriptionvarchar(255) not NULLComment'Description', Img_urlvarchar(255) not NULLComment'Avatar Address', Regtimedatetime  not NULL default Current_timestamp,    Primary Key(ID)) CharSet=UTF8;

#建立存储过程写入50w条数据DELIMITER//Create procedureInsert_a (inchTint)begin    Set @num=1; ifT=2  Then        Set @description = 'The contents of the work-style self-introduction should include three items such as my name, the unit of Service and its department, the duty or the specific work involved. They are called work-style self-introduction content of the three elements, usually indispensable. Among them, the first name, should be a mouthful, must not have a surname nameless, or famous no surname. The second service unit and its department may best be reported, and the specific work department can sometimes not report it. In the case of a third duty or specific work, a job with the best job title, low or no duty, the specific work to be reported. '; Set @img_url = 'https://gss1.bdstatic.com/5eN1dDebRNRTm2_p8IuM_a/res/img/0617jd.jpg'; End if;  while @num<=500000 DoifT=1  Then            Insert  intoA1 (user_name, user_pwd)Values(Concat ('User',@num),'E10ADC3949BA59ABBE56E057F20F883EZC'); Else            Insert  intoA2 (user_name, User_pwd,description,img_url)Values(Concat ('User',@num),'E10ADC3949BA59ABBE56E057F20F883EZC',@description,@img_url); End if; Set @num=@num+1; End  while;End //DELIMITER; Call Insert_a (1); Call Insert_a (2);

The prep work is done.

1. First Test the following 2 statements

1 1. Count (*), A2 in the description deposit a relatively large amount of data 2 Select Count (* from A1;  #平均0.  A sec 3 Select Count (* from A2;  #平均0.  $ sec

2. Next, add the normal index to user_name

1 Alter Table Add Index user_name ('user_name'); 2 Alter Table Add Index user_name ('user_name');

Execute the following statement to verify

 1  select  sql_no_cache "count  (* ) from  A1 where  user_name  >   '   

　　

By testing, the count (*) of the A1 is slowed down, but the A2 count (*) is several times faster.

The above are their own conjecture experiment, perhaps the middle is other reasons cause conjecture test inaccurate, welcome expert guidance.

Reference: http://www.t086.com/article/5083

InnoDB Count Optimization test