Knowledge Points: Mysql Index optimization Combat (3)

Knowledge points: MySQL indexing principle complete manual (1) Knowledge points: MySQL indexing principle complete manual (2) Knowledge points: MySQL index optimization combat (3) Index principle Knowledge Review index performance analysis and optimization

Use EXPLAIN to judge the SQL execution plan and discover SQL that slows down SQL or performance impacting the business

Explain [EXTENDED] SELECT ...

Viewing the execution plan will have the following information:

Id:1Select_type:simpletable:tpossible_keys:primarykey:primarykey_len:4ref:const  Rows:1filtered:100.00extra:using Index

About Key_len length Calculation formula:

varchar (10) Variable length field and allow null:10_ (Character set:utf-8,gbk=2,latin1=1) +1 (NULL) +2(variable-length field) varchar (10) variable-length field and does not allow null:10_ (Character set:utf-8,gbk=2,latin1=1) +2(variable-length field)char(10) Variable-length field and allow null:10_ (Character Set: utf-8,gbk=2,latin1=1) +1(NULL)Char(10) Variable length field and do not allow null:10_ (Character set:utf-8,gbk=2,latin1=1)

The default is null, which takes up bytes and index length. This means that the index Key_len is too large to affect SQL performance.

4.1 Index How to improve SQL efficiency

• Speed up queries with indexes
• Row record Retrieval
• Returning results directly from index Records (Federated index)

Min (), Max () Order by GROUP by distinct

If the column is defined as DEFAULT NULL, the null value is also indexed and stored in the most front-end portion of the index tree. Case 1:

CREATE TABLE ' base_assets ' (  int(one) unsigned not NULL auto_increment,  int (one) default ' 0 ',  int(ten) unsigned default NULL,  int(10) unsigned NOT NULL default ' 0 ',  ' ASSETS3 ' timestamp not null default Current_timestamp on UPDATE current_stamp,
     ' ASSETS4 ' varchar ($) not NULL DEFAULT,  PRIMARY KEY (' ID ')  ' idx_a1 ' (' ASSETS1 '),  KEY ' key_c2 ' (' ASSETS2 ')) ENGINE=innodb auto_increment=512976 DEFAULT Charset=utf8;

Table Description:

• 5 million rows of records, ASSETS11, ASSETS12, ASSETS15 Three column values are exactly the same, but the definitions are different:
• ASSETS1 column defined as not NULL DEFAULT 0, indexed
• ASSETS2 column is defined as DEFAULT NULL, indexed
• ASSETS5 column defined as not NULL DEFAULT 0, no index

= 100000 Limit 1= 100000 limit 1; # Statistical class business: Explain select Max (ASSETS2) from base_assets;# average, indexed, scan index, no full table Scan (avoid back table) explain select AVG (ASSETS1) from Base_assets;

4.2 Using indexes to improve sorting efficiency

> 100000  ORDER by ASSETS5 limit of> 100000  ORDER by ASSETS1limit > 100000  ord Er by ASSETS1 limit 100;

The results can again indicate different execution plan performance gaps. (figure)

4.3 The difference between not NULL and DEFAULT null

Nullnull;

4.4 Using the index merge-using Union

Desc SELECT * Form base_assets where ASSETS1 = 2333 or ASSETS2 = 6666

Case 2:

# Test Index write efficiency create bable base_assets_test (IDintUnsigned notNULLAuto_increment,assets1intNotNULL default' 0 ', Assets2intNotNULL default' 0 ', Assets3intNotNULL default' 0 ', Assets4intNotNULL default' 0 ', assets5 timestampNULL, assets6 varchar () notNULL default‘‘, primary KEY (' ID '), key ' Idx_c2 ' (' assets2 '), key ' idx_c3 ' (' assets3 ')); # test for index contrast write efficiency stored procedures delimiter $$ $create procedure' Insert_test ' (in Row_numint) BEGIN DECLARE Iint default1; whileI <= row_num DoINSERT INTO Base_assets_test (ID,ASSETS1,ASSETS2,ASSETS3,ASSETS4,ASSETS5,ASSETS6) VALUES (I,floor (rand () _row_ num), Floor (rand () _row_num), Floor (rand () _row_num), now (), repeat (' Wubx ', Floor (rand () *) 20)) ; set I= i + 1; end while; end $$$

Client invocation: Call Insert_test (1 000 000);

Insert initialization data:

1	Mode	Time Consuming
InnoDB	No index	110
InnoDB	Only PRIMARY key index	110
InnoDB	All indexes below	110
MyISAM	Without any index	24
MyISAM	Only PRIMARY key index	27
MyISAM	All indexes	31

Summary

• Recommended low-selectivity columns are not indexed, such as gender, name;
• Fields with high selectivity are placed in front, and frequently used fields are in front;
• Fields that need to be sorted frequently, can be added to the index, and the column order is consistent with the most commonly used sort;
• Prefix index, such as index (URL (64)), for a Jianjian index of long character data types
• Create only the required indexes and avoid redundant indexes, such as index (A, a), index (a)

InnoDB table primary KEY, index

• INNODB table Each table must explicitly set the primary key;
• The shorter the primary key, the better it is, preferably a self-incrementing type, or, if you cannot use self-increment, consider constructing a one-way incremental primary key, and prohibit the use of a random type value for the primary key;
• The primary key is best composed of one field, and the combined primary key does not allow more than 3 fields. If the business needs, you can create a self-increment field as the primary key, and then add a unique index;
• The column you select as the primary key must not be modified or rarely modified after insertion, otherwise you should consider using the self-increment column as the primary key;
• If there is more than one (group) unique key on a business, query the most commonly used unique key as the primary key.

Over

Knowledge Points: Mysql Index optimization Combat (3)