Mysql optimization: a deep understanding of the storage engine, index optimization, mysql Index

Source: Internet
Author: User
Tags mysql index

Mysql optimization: a deep understanding of the storage engine, index optimization, mysql Index

Compare the storage methods of the two storage engines in the database:

MyIsam: If you observe carefully, you will find that the database using this engine generally contains less than three files ,**. frm ,**. myi, (INDEX )**. myd (put data), through the index (**. myi file) to locate the row in which the data file is stored, which generates a response. If no rows are returned, that is, index coverage, the return speed is very fast.

InnoDb: A file where the index and data are put together. The disadvantage of clustered index is that the file is too large, and the disk turns and the search is relatively low. In this way, a block file is generated by page.

Create a table: it contains the primary key index and the joint index, respectively using the myisam engine and the innodb engine.

create table smth (id int auto_increment ,ver int(11) default null,content varchar(1000) not null,intro varchar(1000) not null,primary key(id),key idver(id,ver))engine = myisam default charset = utf8;


create table smth1 (id int auto_increment ,ver int(11) default null,content varchar(1000) not null,intro varchar(1000) not null,primary key(id),key idver(id,ver))engine = innodb default charset = utf8;
The file directory analysis is as follows:


Create a stored procedure and insert 10 thousand data records.

Create procedure smthTest () begin declare num int default 1050; while num <10000 doset num: = num + 1; insert into smth values (num, num, 'I am step ', 'Who Am I '); end while; end;


Create procedure smthTest1 () begin declare num int default 0; while num <10000 doset num: = num + 1; insert into smth1 values (num, num, 'I am step ', 'Who Am I '); end while; end;


Set profiling = 1; Show Details

Running result analysis:


Statement analysis result:

Mysql> explain
Select id, ver, content from smth order by id;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
| 1 | SIMPLE | smth | ALL | NULL | 9946 | Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
1 row in set
Mysql> explain
Select id, ver, content from smth order by id, ver;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
| 1 | SIMPLE | smth | ALL | NULL | 9946 | Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
1 row in set
Mysql> explain
Select id, ver, content from smth1 order by id;
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- +
| 1 | SIMPLE | smth1 | index | NULL | PRIMARY | 4 | NULL | 9932 | NULL |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- +
1 row in set
Mysql> explain select id
, Ver, content from smth1 order by id, ver;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +
| 1 | SIMPLE | smth1 | ALL | NULL | 9932 | Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------------- +


Mysql> explain
Select id from smth order by id;
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------------- +
| 1 | SIMPLE | smth | index | NULL | PRIMARY | 4 | NULL | 9946 | Using index |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------------- +
1 row in set
Mysql> explain
Select id from smth order by id, ver;
+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +
| 1 | SIMPLE | smth | index | NULL | idver | 9 | NULL | 9946 | Using index |
+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +
1 row in set
Mysql> explain
Select id from smth1 order by id;
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------------- +
| 1 | SIMPLE | smth1 | index | NULL | PRIMARY | 4 | NULL | 9932 | Using index |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------------- +
1 row in set
Mysql> explain
Select id from smth1 order by id, ver;
+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +
| 1 | SIMPLE | smth1 | index | NULL | idver | 9 | NULL | 9932 | Using index |
+ ---- + ------------- + ------- + --------------- + ------- + --------- + ------ + ------------- +
1 row in set



Related Article

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.