Shulou(Shulou.com)06/01 Report--

This article is about the use of index + explain in MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

I. introduction of the index

In mysql, an index is a data structure that has been sorted by index in a file.

Using the index can speed up our query, but the efficiency of adding, deleting and modifying our data will be reduced.

Because most of a website is a query, we mainly optimize select statements.

II. Classification of indexes in MySQL

General index key

Unique index unique key unique key alias alias can be ignored alias can be ignored

Primary key index primary key (field)

Full-text indexing myisam engine support (English indexing only, mysql version 5.6 also supports), sphinx (Chinese search)

An index consisting of multiple fields. Such as key key_index (title,email)

3. Basic operation of index 1. Add create table t_index (id int not null auto_increment, title varchar (30) not null default'', email varchar (30) not null default'', primary key (id), unique key uni_email (email), key key_title (title)) engine=innodb charset=utf8 to the table

View tabl

Desc tablename

Mysql > desc t_index +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | title | varchar (30) | NO | MUL | | email | varchar (30) | NO | UNI | +- -+ 3 rows in set (0.01sec)

View the creation statement of the table

Show create table tbalename/G

Mysql > show create table tasking indexCompact Gentleman error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'/ G' at line 1mysql > show create table t_index\ G * * 1. Row * * Table: t_indexCreate Table: CREATE TABLE `tindex` (`id` int (11) NOT NULL AUTO_INCREMENT, `title` varchar (30) NOT NULL DEFAULT'', `email` varchar (30) NOT NULL DEFAULT'', PRIMARY KEY (`id`), UNIQUE KEY `uni_ email` (`email`) KEY `key_ title` (`title`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) ERROR: No query specified2, delete index delete primary key index

Alter table table_name drop primary key

Note:

Mysql > alter table t_index drop primary key;ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

The primary key is not necessarily self-growing, but self-growing must be the primary key.

Remove the self-growth of the primary key index before deleting it gradually.

Mysql > alter table t_index modify id int not null;Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0

And then delete the primary key.

Mysql > alter table t_index drop primary key;Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 delete normal and unique indexes

Alter table table_name drop key 'alias for index'

Actual operation

Mysql > alter table t_index drop key uni_email;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table t_index drop key key_title;Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 03, add index alter table t_index add key key_title (title); alter table t_index add key uni_email (email); alter table t_index add primary key (id) 4. Compare create table article with or without index (id int not null auto_increment,no_index int,title varchar (30) not null default'', add_time datetime,primary key (id))

Insert data

Mysql > insert into article (id,title,add_time) values (null,'ddsd1212123d',now ()); mysql > insert into article (title,add_time) select title,now () from article;Query OK, 10 rows affected (0.01sec) Records: 10 Duplicates: 0 Warnings: 0mysql > update article set no_index=id

Comparison of query data with or without index

Mysql > select * from article where no_index=1495298 +-+ | id | no_index | title | add_time | +- -+ | 1495298 | 1495298 | ddsd1123d | 2019-05-15 23:13:56 | +-+ 1 row in set (0.28 sec) mysql > select * from article where id=1495298 +-+ | id | no_index | title | add_time | +- -+ | 1495298 | 1495298 | ddsd1123d | 2019-05-15 23:13:56 | +-+ 1 row in set (0.01 sec)

Table structure

Mysql > show create table article\ G * * 1. Row * * Table: articleCreate Table: CREATE TABLE `roomle` (`id`int (11) NOT NULL AUTO_INCREMENT, `no_ index` int (11) DEFAULT NULL, `title`varchar (30) NOT NULL DEFAULT'', `add_ time` datetime DEFAULT NULL PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1572824 DEFAULT CHARSET=utf81 row in set (0.00 sec) ERROR: No query specified IV, explain analysis

Using explain, you can analyze whether the sql statement uses the index query or not, so as to better optimize it.

We just need to precede the select statement with an explain or desc.

1. Grammar

Explain | desc select * from tablename\ G

2. Analysis

Use the two indexes just now to see if they are indexed.

Mysql > mysql > explain select * from article where no_index=1495298\ G * * 1. Row * * id: 1 select_type: SIMPLE// single table query table: table name of article// query partitions: NULL type: type of ALL// index From good to bad: system > const > range > index > Allpossible_keys: index that NULL// may use key: index actually used by NULL//: length of NULL// index ref: NULL rows: 1307580 NULL// / number of rows that may scan the table filtered: 10.00 Extra: Using where1 row in set, 1 warning (0.00 sec) ERROR: No query specifiedmysql > explain select * from article where id=1495298\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: const// appears constpossible_keys: PRIMARY key: PRIMARY// when performing an equivalent query on the primary key index All primary indexes actually used: key_len: 4 Extra / length of index 4 = int occupies 4 bytes ref: const rows: 1 const rows / the number of rows scanned is only one line filtered: 100.00 Extra: index 1 warning (0.00 sec) ERROR: type term analysis of No query specified3 and explain

Type items are sorted from best to worst:

System: generally speaking, a system table only appears when there is a row of records.

Const: appears when the primary key value is queried for equivalence, such as where id=666666

Range: appears when querying the range of the values of an index, such as where id use mysql;mysql > explain select * from user\ G * * 1. Row * * id: 1 select_type: SIMPLE table: user partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec) mysql > use test Mysql > explain select * from article where id=666666\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Filtered: 100.00 Extra: NULLmysql > explain select * from article where id > 666666\ G Mysql > explain select * from article where id explain select id from article\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 1307580 Filtered: 100.00 Extra: Using index1 row in set 1 warning (0.00 sec) ERROR: No query specified

If the field of the query exists in the index file, it will be queried directly from the index file, which we call an index override query.

The emergence of all, we need to avoid, because of a full scan.

If all appears, you can add a normal index query to this field.

Mysql > alter table article add key key_no_index (no_index); Query OK, 0 rows affected (1.92 sec) Records: 0 Duplicates: 0 Warnings: 0type is ref, should be associated, but ref is constmysql > explain select * from article where no_index=666666\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: refpossible_keys: key_no_index key: key_no_index key_len: 5 ref: const Rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec) Speed leap mysql > select * from article where no_index=666666 +-+ | id | no_index | title | add_time | +- -+ | 666666 | 666666 | ddsd1123d | 0-05-15 23:13:55 | +-+ 1 row in set (2019 sec) 4. Scenarios that use indexing 1. Fields that often appear after where We need to index him 2. The order by statement uses mysql > explain select * from article order by id\ G to optimize the index. * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 1307580 Filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec) ERROR: No query specifiedmysql > explain select * from article where id > 0 order by id\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 653790 Filtered: 100.00 Extra: Using where1 row in set 1 warning (0.01sec) ERROR: No query specified

As you can see, even if the index is used, it is almost a full table scan.

If you add where, you will lose half of it.

3. Optimization of fuzzy query index for like

Where title like'% keyword%' = > full table scan

Where title like 'keyword%' = = > will use the index query

Add an index to title

Mysql > alter table article add key key_index (title); Query OK, 0 rows affected (2.16 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table article\ G * * 1. Row * * Table: articleCreate Table: CREATE TABLE `indele` (`id` int (11) NOT NULL AUTO_INCREMENT, `no_ index` int (11) DEFAULT NULL, `title`varchar (30) NOT NULL DEFAULT'', `add_ time`datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `key_no_ index` (`no_ index`) KEY `key_ index` (`title`) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf81 row in set (0.00 sec)

Because% does not appear at the far left of the like keyword query, you can use the index query

As long as% appears on the left side of like, it is a full table query.

Mysql > explain select * from article where title like'a%'\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: range// range query possible_keys: key_index key: key_index key_len: 92 / Ref: NULL rows: 1 filtered: 100.00 Extra: Using index condition1 row in set 1 warning (0.00 sec) mysql > explain select * from article where title like'% a%'\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: ALL// full table query possible_keys: NULL key: NULL key_len: NULL ref: NULL Rows: 1307580 filtered: 11.11 Extra: Using where1 row in set 1 warning (0.00 sec) 4. Index usage optimization of limit statements

For optimization of the limit statement, we can add the order by index field before it

If the field of order by is an index, it will first look up the data of the specified number of rows in the index file.

Mysql > explain select sql_no_cache * from article limit 9000001 10\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: ALL// full table possible_keys: NULL key: NULL key_len: NULL ref: NULL Rows: 1307580 filtered: 100.00 Extra: NULL1 row in set 2 warnings (0.00 sec) ERROR: No query specifiedmysql > explain select sql_no_cache * from article order by id limit 9000pr 10\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: indexpossible_keys: NULL key: PRIMARY// uses the index key_len: 4 ref: NULL rows: 90010 filtered: 100.00 Extra: NULL1 row in set 2 warnings (0.00 sec) ERROR: No query specified

Another optimization method for limit:

Index override + delayed association

Principle: mainly use the index overlay query to associate the id returned by the overlay index query with the id we want to query the record

Mysql > select sql_no_cache * from article limit 1000000 Jol 10 +-+ | id | no_index | title | add_time | +- -+-+ | 1196579 | 1196579 | ddsd12123123ad | 2019-05-15 23:13:56 | | 1196580 | 1196580 | ddsd121231ad | 2019-05-15 23:13:56 | | 1196581 | 1196581 | ddsd1212123d | 2019-05-15 23:13:56 | | 1196582 | 1196582 | ddsd1123123d | 2019-05-15 23:13:56 | 1196583 | 1196583 | ddsd1123d | 2019-05-15 23:13:56 | 1196584 | 1196584 | ddsd1123d | | 2019-05-15 23:13:56 | | 1196585 | 1196585 | ddsd1123d | 2019-05-15 23:13:56 | | 1196586 | 1196586 | ddsd1123d | 2019-05-15 23:13:56 | | 1196587 | 1196587 | ddsd1123d | 201905-15 23:13:56 | 1196588 | 1196588 | ddsd1123d | 2019-05-15 23:13:56 | +-| -+-+ 10 rows in set 1 warning (0.21 sec) mysql > select t1.* from article as T1 inner join (select id as pid from article limit 10000 sec 10) as T2 on t1.id=t2.pid +-+ | id | no_index | title | add_time | +-- -+ | 13058 | 13058 | ddsd12123123ad | 2019-05-15 23:13:49 | | 13059 | 13059 | ddsd121231ad | 2019-05-15 23:13:49 | | 13060 | ddsd1212123d | 2019-05-15 23:13:49 | | 13061 | ddsd1123123d | 2019-05-15 23:13:49 | 13062 | ddsd1123d | 2019-05-15 23:13:49 | 13063 | 13063 | ddsd1123d | | | 2019-05-15 23:13:49 | | 13064 | 13064 | ddsd1123d | 2019-05-15 23:13:49 | | 13065 | 13065 | ddsd1123d | 2019-05-15 23:13:49 | | 13066 | 13066 | ddsd1123d | 20109-05-15 23:13:49 | | 13067 | 13067 | ddsd1123d | 20109-05-15 23:13:49 | +-| -+-+ 10 rows in set (0.00 sec) 5, Leftmost principle of composite (multi-column) indexes (often asked in interviews)

The index query will be used only when the leftmost field of the composite index appears during the query.

Create a composite index of the no_index and title of the article table:

/ / create a composite index for no_index and title mysql > alter table article add key index_no_index_title (no_index,title); Query OK, 0 rows affected (1.18 sec) Records: 0 Duplicates: 0 Warnings: 0 Warnings / View the created structure mysql > show create table article\ G * * 1. Row * * Table: articleCreate Table: CREATE TABLE `indele` (`id` int (11) NOT NULL AUTO_INCREMENT, `no_ index` int (11) DEFAULT NULL, `title`varchar (30) NOT NULL DEFAULT'', `add_ time`datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `key_no_ index` (`no_ index`) KEY `key_ index` (`title`), KEY `index_no_index_ title` (`no_ index`, `title`) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf81 row in set (0.00 sec) / delete the index mysql > alter table article drop key key_index of no_index and title Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table article drop key key_no_index;Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table article\ G * * 1. Row * * Table: articleCreate Table: CREATE TABLE `roomle` (`id`int (11) NOT NULL AUTO_INCREMENT, `no_ index` int (11) DEFAULT NULL, `title`varchar (30) NOT NULL DEFAULT'', `add_ time` datetime DEFAULT NULL, PRIMARY KEY (`id`) KEY `index` (`no_ index`, `title`) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf81 row in set (0.00 sec) / / Composite index usage mysql > explain select * from article where title='ddsd1123d' and no_index=77777\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: refpossible_keys: index_no_index_title key: index_no_index_title key_len: 97 ref: const Const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec) mysql > explain select * from article where no_index=77777\ G * * 1. Row * * id: 1 select_type: SIMPLE table: article partitions: NULL type: refpossible_keys: index_no_index_title key: index_no_index_title key_len: 5 Ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec) 5. Slow query log 1. Introduction

We can define the maximum execution time of a sql statement. If we find that the execution time of a sql statement exceeds our specified time limit, then the sql will be recorded.

2. Specific operation of slow query

Start slow log query first

View slow log configuration

Mysql > show variables like'% slow_query%' +-- + | Variable_name | Value | +- -+-+ | slow_query_log | OFF | | slow_query_log_file | / usr/local/mysql/data/caredeMacBook-Pro-slow.log | +- -+-+ 2 rows in set (0.00 sec)

Enable slow log query

Mysql > set global slow_query_log=on;Query OK, 0 rows affected (0.00 sec)

Check the slow log configuration again

Mysql > show variables like'% slow_query%' +-- + | Variable_name | Value | +- -+-+ | slow_query_log | ON | | slow_query_log_file | / usr/local/mysql/data/caredeMacBook-Pro-slow.log | +- -+-+ 2 rows in set (0.00 sec)

Go to the mysql configuration file my.ini to specify the boundary time of the sql statement and the path to the slow log file

The name of the slow log, which is saved by default in the data directory under the mysql directory.

Log-slow-queries = 'man.txt'

Set a limit time

Long-query-time=5

Restart

VI. Profile tools 1. Introduction

Using the profile tool to analyze the time consumption of a sql statement

2. Specific operation

Turn on profile

Execute a SQL, (all SQL statements executed after opening will be recorded

To see how much time it takes to execute a sql statement)

Find the specific SQL according to query_id

Example:

/ / View profile settings mysql > show variables like'% profil%' +-+ | Variable_name | Value | +-+-+ | have_profiling | YES | | profiling | OFF | / / unopened status | profiling_history_size | 15 | + -+-+ 3 rows in set (0.00 sec) / / enable operation mysql > set profiling = on Query OK, 0 rows affected, 1 warning (0.00 sec) / / check whether mysql is enabled successfully > show variables like'% profil%' +-+-+ | Variable_name | Value | +-+-+ | have_profiling | YES | | profiling | ON | / / enabled successfully | profiling_history_size | 15 | +- -+-+ 3 rows in set (0.00 sec)

Specific inquiry

Mysql > select * from article where no_index=666666 +-+ | id | no_index | title | add_time | +- -+ | 666666 | 666666 | ddsd1123d | 2019-05-15 23:13:55 | +-+ 1 row in set (0.02 sec) mysql > show profiles +-+ | Query_ID | Duration | Query | +- -- +-+ | 1 | 0.00150700 | show variables like'% profil%' | | 2 | 0.01481100 | select * from article where no_index=666666 | + -- + 2 rows in set 1 warning (0.00 sec) mysql > show profile for query 2 +-- +-+ | Status | Duration | +-+-+ | starting | 0.000291 | | checking permissions | 0.000007 | Opening tables | 0.012663 | / / Open table | init | 0.000050 | | System lock | 0.000009 | optimizing | 0.000053 | | statistics | 0.001566 | preparing | 0.000015 | executing | 0.000002 | Sending data | 0.000091 | / / data sent on disk | end | 0.000004 | | query end | 0.000007 | | closing tables | 0. 000006 | | freeing items | 0.000037 | | cleaning up | 0.000010 | +-- +-- + 15 rows in set 1 warning (0.01sec) Thank you for your reading! On the MySQL index + explain use examples to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.