Knowledge Points: Mysql Index optimization Combat (3)

Source: Internet
Author: User
Tags mysql index

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


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

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
    • 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.


Knowledge Points: Mysql Index optimization Combat (3)

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: 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.