MySQL----(index, slow query)

Index one, introduction to the index

A data structure in the database that is designed to help users quickly find data. Similar to a directory in a dictionary, when looking up a dictionary, you can find the location of the data based on the directory, and then get it directly.

Second, the role of the index

Constraints and accelerated lookups

Iii. several common indexes:

Normal index, unique index, primary key index (these are all single-column)

Federated indexes (multiple columns), such as: Federated primary Key index, Federated Unique index, Federated Common Index

Non-indexed and indexed differences and the purpose of indexing

No index: From one query to the next
Indexed: The essence of creating an index is to create additional files (some format storage, query, first go to the extra file to find, set the location, and then go to the original table directly query. However, the more indexes you create, the more you will lose on your hard disk.

Purpose of Indexing:

A. Additional files to save special data structures
B. Query fast, but insert update delete is still slow
C. After an index is created, the index must be hit to be valid

Types of indexes

Hash index and Btree index
(1) Hash type index: query single fast, range query slow
(2) Index of btree type: B + Tree, the more layers, the amount of data exponential growth (we use it because InnoDB supports it by default)

3.1 General Index

Function: Only one accelerated search

CREATE TABLE + Normal index

CREATE TABLE UserInfo (   nid int not NULL Auto_increment primary key,   name varchar (+) NOT NULL,   email varchar (6 4) Not NULL,   index Ix_name (name)      # # #这里是索引);

Normal index

Create index name on table name (column name)

Delete Index

Drop index index name on table name

View Index

Show index from table name

3.2 Unique Indexes

Unique index has two functions: Accelerated Find and UNIQUE constraint (can contain null)

CREATE TABLE + Unique index

CREATE TABLE userinfo (   ID int not NULL auto_increment primary key,   name varchar (+) NOT NULL,   email varchar (64 ) not NULL,   unique  index  ix_name (name)     # # #这里是索引);

Unique index

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

To delete a unique index

Drop unique index index name on table name

3.3 Primary Key index

Primary key index has two functions: Accelerated Lookup and UNIQUE constraints (without null)

CREATE TABLE + PRIMARY key index

CREATE TABLE userinfo (    ID int not NULL auto_increment primary key,    name varchar (+) NOT NULL,    email varchar (64 ) not NULL,    unique  index  ix_name (name)), or CREATE TABLE userinfo (    ID int not NULL auto_increment,    Name varchar (+) NOT NULL,    email varchar (+) NOT NULL,    primary KEY (NID),    unique  index  ix_name (name));

Primary key Index

ALTER TABLE name add primary key (column name);

Delete primary key index

ALTER TABLE name drop primary key;alter table name  modify  column name int, drop primary key;

3.4 Combined Index

A composite index is the combination of n columns into one index

Its application scenario is: Frequent use of n-columns to query, such as: where name = ' Alex ' and email = ' [email protected] '.

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

Primary key Index

ALTER TABLE name add primary key (column name);

Delete primary key index

ALTER TABLE name drop primary key;alter table name  modify  column name int, drop primary key;

3.4 Combined Index

A composite index is the combination of n columns into one index

Its application scenario is: Frequent use of n-columns to query, such as: where name = ' Alex ' and email = ' [email protected] '.

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

Four, the index noun

　

#覆盖索引: Get data directly in the index file for example: select name from userinfo where name = ' alex50000 '; #索引合并: Merge multiple single-column indexes into use for example: SELECT * from  userinfo where name = ' alex13131 ' and id = 13131;

V. Correct use of the index

When you add an index to a database table, it does make the query take off, but the premise must be the correct query using the index, and if used in an incorrect way, even indexing will not work.

Using the index, we must know:

(1) Create an index

(2) hit index

(3) Correct use of index

Get ready:

#1. Prepare tables CREATE TABLE UserInfo (ID int,name varchar, gender char (6), email varchar), #2. Create a stored procedure that implements a bulk insert record delimiter $$ #声明存储过程的结束符号为 $ $create Procedure Auto_insert1 () BEGIN    declare i int default 1;    while (i<3000000) does        insert into userinfo values (I,concat (' Alex ', I), ' Male ', concat (' Egon ', I, ' @oldboy '));        Set i=i+1;    End while; end$$ #$$ end delimiter; #重新声明分号为结束符号. View stored procedures Show CREATE PROCEDURE Auto_insert1\g #4. Calling the stored procedure call Auto_insert1 (); Preparing 300w data

-Like '%xx ' select * from userinfo where name like '%al ';-Use function select * from userinfo where reverse (name) = ' Alex3    ';-or select * from userinfo where id = 1 or email = ' [email protected] ';            Special: When the OR condition has an unindexed columns invalidation, the following will go through the index select * from userinfo where id = 1 or name = ' alex1222 '; SELECT * from userinfo where id = 1 or email = ' [email protected] ' and name = ' alex112 '-type inconsistent if the column is a string type, the incoming condition must be Number, otherwise ... select * from userinfo WHERE name = 999;-! = Select COUNT (*) from userinfo where name! = ' Alex ' Special: if    Is the primary key, then the index select COUNT (*) from userinfo where id! = 123-> select * from userinfo where name > ' Alex ' Special: If the primary key or index is an integer type, then the index select * from userinfo where ID > 123 SELECT * from UserInfo where num > 12    3-order by Select e-mail from UserInfo order by name Desc; When sorting by index, the selected mappings are not indexed if they are not indexes: if the primary key is sorted, then the index: SELECT * from UserInfo ORDER by nid desc; -The combined index is the leftmost prefix if the combined index is: (Name,email) name and email--Use index name--use index email--Do not use index 

　

What is the leftmost prefix?

Leftmost prefix match: CREATE index ix_name_email on UserInfo (name,email), select * from userinfo where name = ' Alex ', select * from Userinf o WHERE name = ' Alex ' and email= ' [email protected] '; select * from UserInfo where  email= ' [email protected] '; If you use a composite index like above, After the name and email combination index, query (1) name and email---use index (2) name        ---use index (3) Email       ---Non-applicable index the performance of a composite index is better than multiple single-column indexes when searching for n conditions simultaneously * * * * * * Performance of composite index > Index Merge *********

Vi. Considerations for Indexing

(1) Avoid using SELECT * (2) count (1) or count (column) instead of Count (*) (3) To create a table using char instead of varchar (4) Table field order fixed Length field precedence (5) Combining indexes instead of multiple single-column indexes (when multiple conditional queries are used frequently) (6) Try to use a short index (CREATE index ix_title on TB (title (16)); Special data type text type) (7) Use Join to replace subquery (8) Note that the condition type must be consistent (9) the index hash (less repetition) does not apply to indexing, for example: gender inappropriate

　　

Vii. Plan of Implementation

　　

　　

　　

　

　　

MySQL----(index, slow query)