Mysql experiment uses explain to analyze the index trend, mysqlexplain

Source: Internet
Author: User
Tags mysql index

Mysql experiment uses explain to analyze the index trend, mysqlexplain

Overview

Index is a required skill for mysql and a means to provide mysql query efficiency. Which of the following experiments can be understood? Mysql index rules can also be constantly optimized for SQL statements

Lab Purpose

This experiment aims to verify the leftmost principle of the composite index.

Description

This experiment is only used to verify the results of the actual use of indexes. Ignore the rationality of the design.

Preparations

1. A user table contains fields such as uid, user_name, real_name, and eamil. For details, see the table creation statement.
2. Add a simple index user_name under the user_name field, and add the index complex_index under the email, mobile, and age fields.
3. Use MyISAM for the table engine and add
4. Prepare 97000 pieces of data (the data volume can be determined based on the actual situation. Here we prepare more than 97000 pieces of data)
5. Experiment tool Navcat

Table creation statement

Drop table if exists 'qz _ users'; create table 'qz _ users' ('uid' int (11) unsigned not null AUTO_INCREMENT COMMENT 'user uid ', 'User _ name' varchar (255) COLLATE utf8mb4_unicode_ci default null comment 'username', 'real _ name' varchar (128) character set utf8 default null comment 'user name ', 'email 'varchar (255) character set utf8 default null comment 'mail', 'mobile' varchar (16) character set utf8 default null comment 'user phone ', 'Password' varchar (32) character set utf8 default null comment 'user password', 'salt' varchar (16) character set utf8 default null comment' user append obfuscation Code ', 'Avatar _ file' varchar (128) character set utf8 default null comment 'profile picture file', 'sex' tinyint (1) default null comment 'gender ', 'birthday' int (10) default null comment' birthday', primary key ('uid'), KEY 'user _ name' (250 )), KEY 'complex _ Index' ('email ', 'mobile', 'sex') ENGINE = MyISAM AUTO_INCREMENT = 1 default charset = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Prepared query statement

explain select * from qz_users where user_name = "ryanhe";explain select * from qz_users where email = "x";explain select * from qz_users where email = "x" and mobile = "x" and sex=1;explain select * from qz_users where email = "x" and mobile = "x";explain select * from qz_users where email = "x" and sex = "x";explain select * from qz_users where sex = "x" and mobile = "x";explain select * from qz_users where mobile = "x" and sex = "0";

Result Analysis

Use user_name Condition

explain select * from qz_users where user_name= "x";

Result

Analysis

Indexed? Index name Number of scan records
Yes User_name 1

Email Condition

explain select * from qz_users where email = "x";

Result

Analysis

Indexed? Index name Number of scan records
Yes Complex_index 7

Email + mobile + sex Conditions

explain select * from qz_users where email = "x" and mobile = "x" and sex=1;

Result

Analysis

Indexed? Index name Number of scan records
Yes Complex_index 1

Email + mobile conditions

explain select * from qz_users where email = "x" and mobile = "x";

Result

Analysis

Indexed? Index name Number of scan records
Yes Complex_index 7

Email + sex Conditions

explain select * from qz_users where email = "x" and sex = "x";

Result

Analysis

] [3] indexing? Index name Number of scan records
Yes Complex_index 7

Sex + mobile conditions

explain select * from qz_users where sex = "x" and mobile = "x";

Result

Analysis

Indexed? Index name Number of scan records
No 97185

Mobile + sex Conditions

explain select * from qz_users where mobile = "18602199680" and sex = "0";

Result

Analysis

Indexed? Index name Number of scan records
No 97185

Conclusion

The preceding results show that after the combined index is set, the query condition order can be reasonably used to avoid slow query of SQL statements.

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.