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

The following is about the knowledge of MySQL database indexing. The secret of the text lies in being close to the topic. So, no gossip, let's read the following directly, I believe you will benefit from reading this article on MySQL database indexing knowledge.

MySQL Database-Index I. introduction to Index A data structure in a database designed to help users find data quickly. Similar to the directory in the dictionary, when looking up the contents of the dictionary, can you find the location of the data according to the directory, and then get it directly. Second, the function of the index to constrain and accelerate the search. Third, several common indexes:

General index, unique index, primary key index (these are all single columns)

Federated index (multi-column), such as federated primary key index, federated unique index, federated general index

The difference between indexed and indexed and the purpose of indexing

No index: from going to the next query

Indexing: the essence of creating an index is to create additional files (stored in a certain format, when querying, first go to extra files, locate them, and then query directly in the original table. But the more indexes are created, there will be wear and tear on the hard drive.

The purpose of indexing:

a. Extra files hold special data structures

b. Query is fast, but insert update and delete is still slow

c. After the index is created, the index must be hit to be valid

Types of indexes

Hash index and BTree index

(1) hash index: query a single item quickly, but query the range slowly.

(2) Index of btree type: B + tree. The more layers there are, the more data will grow exponentially (we will use it because innodb supports it by default).

3.1 General Index

Purpose: there is only one accelerated search

Create table userinfo (nid int not null auto_increment primary key, name varchar (32) not null, email varchar (64) not null, index ix_name (name) # here is the index)

Create table + general index

Name of create index index on table name (column name) general index

Delete index

The name of the drop index index on table name

View Index

Show index from Table name 3.2 unique Index

A unique index has two functions: accelerated lookup and unique constraints (can include null)

Create table + unique index

Create table userinfo (id int not null auto_increment primary key, name varchar (32) not null, email varchar (64) not null, unique index ix_name (name) # here is the index)

Create unique index index name on table name (column name) unique index

Delete unique index

Drop unique index Index name on Table name 3.3 Primary key Index

Primary key indexes have two functions: accelerated lookup and unique constraints (excluding null)

Create table userinfo (id int not null auto_increment primary key, name varchar (32) not null, email varchar (64) not null, unique index ix_name (name); or create table userinfo (id int not null auto_increment, name varchar (32) not null, email varchar (64) not null, primary key (nid), unique index ix_name (name))

Create table + primary key index

Alter table table name add primary key (column name); primary key index

Delete primary key index

Alter table table name drop primary key;alter table table name modify column name int, drop primary key;3.4 composite index

A combined index is a combination of n columns into a single index.

The application scenario is that n columns are frequently used for query, such as where name = 'John' and email =' John@qq.com'.

Create index index name on table name (column name 1, column name 2)

Primary key index

Alter table table name add primary key (column name)

Delete primary key index

Alter table table name drop primary key;alter table table name modify column name int, drop primary key;3.4 composite index

A combined index is a combination of n columns into a single index.

The application scenario is that n columns are frequently used for query, such as where name = 'john' and email =' john@qq.com'.

Create index index name on table name (column name 1, column name 2). Index noun # override index: get data directly in the index file for example: select name from userinfo where name = 'john50000'; # index merge: merge multiple single-column indexes into use for example: select * from userinfo where name =' john13131' and id = 13131 Adding an index to a database table does make the query take off, but only if the index is used correctly, and even indexing will not work if it is used in the wrong way. V. the correct use of the index

To use an index, we must know:

(1) create an index

(2) hit index

(3) use the index correctly

Prepare:

# 1. Prepare table create table userinfo (id int, name varchar (20), gender char (6), email varchar (50)); # 2. Create a stored procedure to insert records in batch delimiter $$# declare that the ending symbol of the stored procedure is $$create procedure auto_insert1 () BEGIN declare i int default 1; while (I select * from userinfo where name > 'john' is special: if the primary key or index is an integer type, it will still index select * from userinfo where id > 123 select * from userinfo where num > 123-order by select email from userinfo order by name desc When sorting by index, if the selected mapping is not an index, then the index is not special: if the primary key is sorted, then the index is still select * from userinfo order by nid desc -the leftmost prefix of a combined index if the combined index is: (name,email) name and email-- using index name-- using index email-- what is the leftmost prefix without using index? Leftmost prefix match: create index ix_name_email on userinfo (name,email); select * from userinfo where name = 'john'; select * from userinfo where name =' john' and email='john@john.com'; select * from userinfo where email='john@john.com' If you use a composite index as above, after name and email combine the index, query (1) name and email-- use the index (2) name-- use the index (3) email-do not apply the index for searching n conditions at the same time, the performance of the combined index is better than that of multiple single-column indexes * the performance of the combined index > the performance of the index merge *

6. Notes for indexing (1) avoid using select * (2) count (1) or count (column) instead of count (*) (3) when creating tables, try to use char instead of varchar (4) Field order fixed-length fields first (5) Composite indexes instead of multiple single-column indexes (when multiple conditional queries are often used) (6) try to use short indexes (create index ix_title on tb (title (16)) Special data type text type) (7) use join (join) to replace subquery (8) when joining tables, pay attention to the condition type should be consistent (9) Index hash (less repetition) is not suitable for indexing, for example: gender is not appropriate

VII. Implementation of the plan

Explain + query SQL-used to display SQL execution information parameters, which can be optimized for SQL according to reference information

Mysql > explain select * from userinfo +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | Extra | +-- + | 1 | SIMPLE | userinfo | ALL | NULL | NULL | NULL | NULL | 2973016 | NULL | +-- + mysql > explain select * from (select id Name from userinfo where id > = < symbol INDEX_MERGE: merge indexes Search for select * from userinfo where name = 'john' or nid in using multiple single-column indexes (11, 22, 22, 33) REF: find one or more values according to the index select * from userinfo where name = 'john112'; EQ_REF: join using primary key or unique type select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id; CONST: constant tables have at most one matching row, because there is only one row, and the column values in this row can be considered constant by the rest of the optimizer, and the const table is fast because they are only read once. Select id from userinfo where id = 2; SYSTEM: the system table has only one row (= system table). This is a special case of the const join type. Select * from (select id from userinfo where id = 1) as A; possible_keys: possible index key: real used key_len: index byte length rows: mysql is used in MySQL to estimate the number of rows to be read in order to find the desired rows-only the estimated extra: this column contains MySQL query details "Using index" this value indicates that mysql will use the override index to avoid accessing the table. Don't confuse the override index with the index access type. "Using where" means that the mysql CVM will filter after the rows are retrieved by the storage engine. Many where conditions involve columns in the index, and when (and if) it reads the index, it can be verified by the storage engine, so not all queries with where clauses will display "Using where". Sometimes the emergence of "Using where" is a hint that queries can benefit from different indexes. "Using temporary" this means that mysql uses a temporary table when sorting query results. "Using filesort" this means that mysql uses an external index to sort the results, rather than reading rows from the table in index order. Mysql has two file sorting algorithms, both of which can be done in memory or on disk. Explain won't tell you which file sorting mysql will use, nor will it tell you whether the sorting will be done in memory or on disk. "Range checked for each record (index map: n)" this means that there is no useful index, the new index will be re-evaluated on each row of the join, N is the bitmap of the index displayed in the possible_keys column and is redundant

8. Slow logging

Parameter description:

By enabling the slow query log, you can let MySQL record the statements that query for more than a specified time. By locating the bottleneck of analyzing performance, you can better optimize the performance of the database system.

(1) enter MySql to query whether slow query show variables like 'slow_query%' is enabled. Parameter explanation: slow_query_log slow query enabled status OFF is not enabled ON to enable slow_query_log_file slow query log storage location (this directory requires writable permission of the running account of MySQL, generally set to MySQL data storage directory) (2) View slow query timeout show variables like 'long%' How many seconds does the ong_query_time query take to record the default 10 seconds (3) enable slow log (1) (whether to enable slow log, 1 means to enable it, 0 means to disable it. Set global slow_query_log=1; (4) View show variables like'% slow_query_log%' again (5) enable slow log (2): (recommended) find [mysqld] in the my.cnf file and add: slow_query_log = 1 slow_query_log_file=C:\ mysql-5.6.40-winx64\ data\ localhost-slow.log # linux here the path needs to be changed to long_query_time = 1 parameter description: slow_query_log slow query enabled status 1 to enable the location where the slow_query_log_file slow query log is stored, how many seconds does the long_query_time query take to record by default 10 seconds to 1 second?

For the above MySQL database indexing knowledge-related content, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

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.