Mysql database 4 --- database index

Prodigal son Tang Shao 2022-02-13 07:29:05 阅读数:868

mysql database database index

mysql database 3— Database index

1. The concept of index

1. Concept

An index is a structure that sorts the values of a column or columns in a database table , Can quickly query data . Similar to the catalogue of books in real life , You don't need to query the whole book to find the data you want

2. Advantages and disadvantages

advantage :

1. Improve query speed

2. Unique index can also keep the uniqueness of data

3. Reduce the amount of data that the database needs to scan

shortcoming :

1. Take up a certain amount of disk space

2. Will lower the insertion 、 Delete 、 Update table speed , Because when performing these operations , Also manipulate the index .

2. Types of indexes

1. Add index

(1) General index (Normal)

The most basic index , It has no restrictions

grammar :ALTER TABLE Table name ADD INDEX Index name ( Field name )

 There are several ways to create
-- 1. Create index directly 
CREATE INDEX Index name ON Table name ( Field name )
-- 2. Add indexes by modifying the table structure 
ALTER TABLE Table name ADD INDEX Index name ( Field name )
-- 3. When creating tables, create indexes at the same time 
CREATE TABLE ` Table name ` (
`id` int(11) auto_increment primary key not null,
`title` VARCHAR(25) NOT NULL,
`time` DATE,
INDEX Index name ( Field name )
);
-- 4. Delete index 
DROP INDEX Index name ON Table name

2. primary key

Special unique index , A table can only have one primary key , No null values are allowed . It is common to create a primary key index while building a table

grammar :ALTER TABLE Table name ADD PRIMARY KEY ( Field name )

-- 1. Modify the table structure to create 
ALTER TABLE ` Table name ` ADD PRIMARY KEY ( Field name )
-- 2. Create... When creating a table 
CREATE TABLE ` Table name ` (
`id` int(11) auto_increment NOT NULL,
`title` VARCHAR(25) NOT NULL,
`time` DATE,
PRIMARY KEY ( Field name )
);

3. unique index (Unique)

The value of the index column must be unique , But you can have an empty value .

grammar :ALTER TABLE Table name ADD UNIQUE ( Field name )

 There are several ways to create
-- 1. Create unique index 
CREATE UNIQUE INDEX Index name ON Table name ( Field name )
-- 2. Add indexes by modifying the table structure 
ALTER TABLE Table name ADD UNIQUE Index name ( Field name )
-- 3. When creating tables, create indexes at the same time 
CREATE TABLE ` Table name ` (
`id` int(11) auto_increment primary key not null,
`title` VARCHAR(25) NOT NULL,
`time` DATE,
UNIQUE INDEX Index name ( Field name )
);

4. Full-text index (FullText)

-- 1. How to modify the table structure 
ALTER TABLE ` Table name ` ADD FULLTEXT (` Field name `)

5. Joint index

An index created on multiple fields , Only the first field that created the index is used in the query criteria , Indexes are used . When using composite index, follow the leftmost matching principle

-- 1. How to modify the table structure 
ALTER TABLE ` Table name ` ADD INDEX Index name ( ` Field name 1`, ` Field name 2`, ` Field name 3`)

3. Left most matching principle

1. What is the leftmost matching principle

seeing the name of a thing one thinks of its function : Top left first , Any consecutive index starting from the leftmost can match up . Also encountered range query (>、<、between、like) Will stop matching .
for example :

1. If set up (a,b) Index of order ,where b = 2 It doesn't match (a,b) Indexed ; But if the query condition is where a = 1 and b = 2 perhaps where a=1( Or is where b = 2 and a = 1) Can , Because the optimizer will automatically adjust a,b The order of .

2. If set up (a,b,c) Index of order , Inquire about where b = 2 and a = 1 Is useful to index , but where c = 2 and a = 1 There is no , Because there is less b Left connection of .

2. If set up (a,b,c,d) Index of order ,where a = 1 and b = 2 and c > 3 and d = 4 ,d There is no index , because c Field is a range query , The fields after it will stop matching .

2. Failure of an index due to violation of the leftmost principle

The following is a joint index (a,b,c) Explain in sequence

1、 In the query conditions , Missing highest priority index “a”
When where b = 65 and c = ‘ Kobayashi ’ This doesn't mean a When searching for conditions , Will go to the full table scan ( I don't want to go through the index ).

2、 In the query conditions , Missing index with middle priority “b”
When where a =20 and c =“ Monkey D Luffy ” When such data is retrieved ; Match from left , Because the next field b The lack of , therefore c No index , but a There are still people who take the index , Such query statements are much better than direct full table scanning

3. Range query encountered (>、<、between、like) Will stop matching , such as select * from LOL where a = 2 and b > 1000 and c=‘ dragonball ’; because b Used ’>’, So behind c You can't use the index . Consider changing the index order to (a,c,b), In this way, all three fields can be used to index

4. Query optimizer

1、 If the index order is (a, b). And the query statement is where b = 1 AND a = ‘ dragonball ’; Why can we still use the index ? Because MySQL The query optimizer for will automatically adjust where Clause to use the appropriate index , The interior helps you adjust to where a = ‘ dragonball ’ AND b = 1 ; therefore MySQL non-existent where The order of clauses leads to index invalidation .

summary :

1、 Leftmost prefix matching principle , Very important principle ,mysql It will keep matching to the right until it encounters a range query (>、<、between、like) Just stop matching , such as a = 1 and b = 2 and c > 3 and d = 4 If set up (a,b,c,d) Index of order ,d There is no index , If set up (a,b,d,c) The index of can be used ,a,b,d The order of can be adjusted at will .

2、= and in You can order , such as a = 1 and b = 2 and c = 3 establish (a,b,c) Indexes can be in any order ,mysql The query optimizer will help you optimize it into a form that the index can recognize .

5. The time to index

stay WHERE and JOIN The columns that appear in need to be indexed , But not exactly :

  • MySQL Only right <,<=,=,>,>=,BETWEEN,IN,AND Use index .
  • sometime LIKE You can also use indexes , For example, and like “aaa%” You can use index .
  • stay LIKE Use wildcards % and _ At the beginning of the query ,MySQL No index .

6.explain Look at the index , Conduct sql Optimize

1. brief introduction

explain Shows mysql How to use index to handle select Statement and connection table . It can help you choose better indexes and write more optimized query statements . adopt explain We can get the following information :

  • Read order of tables
  • Operation type of data read operation
  • Which indexes can be used
  • Which indexes are actually used
  • References between tables
  • How many rows per table are queried by the optimizer

2. How do you use it? ?

 grammar :explain + SQL
Example :explain select * from User;

3. Parameter Introduction

(1)id

 Express SQL Identification of the order of execution , The following rules :
1.id identical , The order of execution is from top to bottom
2.id Different , If it's a subquery ,id The serial number of will increase ,id The higher the value, the higher the priority , The first to be executed sql

(2)select_type

 Type of query , It is mainly used to distinguish ordinary queries 、 The joint query 、 Complex queries such as subqueries , The following rules :
1.SIMPLE( Simple SELECT, Don't use UNION Or subquery, etc )
2.PRIMARY( If the query contains any complex sub parts , The outermost select Marked as PRIMARY)
3.UNION(UNION The second or later of SELECT sentence )
4. DEPENDENT UNION(UNION The second or later of SELECT sentence , Depends on external queries )
5. UNION RESULT(UNION Result )
6. SUBQUERY( First in subquery SELECT)
7.DEPENDENT SUBQUERY( First in subquery SELECT, Depends on external queries )
8.DERIVED( Derived from table SELECT, FROM A subquery of a clause )
9. UNCACHEABLE SUBQUERY( The result of a subquery cannot be cached , The first line of the outer link must be reevaluated )

(3)table

 Show which table this row's data is about

(4)type

 Express MySQL How to find the desired row in the table , also called “ Access type ”.
Common types are : ALL, index, range, ref, eq_ref, const, system, NULL( From left to right , Performance from poor to good )
ALLFull Table Scan, MySQL Will traverse the entire table to find the matching rows
index: Full Index Scan,index And ALL The difference for index Type only traverses the index tree
range: Retrieve only rows in the given range , Use an index to select rows
ref: Indicates the join matching condition of the above table , That is, which columns or constants are used to find values on index columns
eq_ref: similar ref, The difference is that the index used is the only index , For each index key value , Only one record in the table matches , Simply speaking , It is used in multi table connection primary key perhaps unique key As a condition of Association
const、system: When MySQL Optimize some part of the query , And convert to a constant , Use these types to access . For example, place the primary key in where In the list ,MySQL You can convert the query to a constant ,system yes const Special case of type , When the query table has only one row , Use system
NULL: MySQL Decompose statements during optimization , Execute without even accessing tables or indexes , For example, selecting the minimum value from an index column can be done through a separate index search .

(5)possible_keys

 Show the indexes that may be applied to this table , One or more . If there is an index on the field involved in the query , Then the index will be listed , But it doesn't have to be actually used by the query

(6)key

 Actual index used , If NULL, No index is used .

(7)key_len

 Represents the number of bytes used in the index , You can use this column to calculate the length of the index used in the query (key_len The displayed value is the maximum possible length of the index field , It's not the actual length , namely key_len It is calculated according to the table definition , It is not retrieved from the table )

(8)ref

 Indicates the join matching condition of the above table , That is, which columns or constants are used to find values on index columns

(9)rows

 Express MySQL According to table statistics and index selection , Estimated number of rows to read to find the required record

(10)Extra

 This column contains MySQL Solve query details , There are several situations :
Using where: Column data is returned from a table that uses only the information in the index and does not read the actual action , This happens when all the request columns of the table are part of the same index , Express mysql The server will filter the rows after the storage engine retrieves them
Using temporary: Express MySQL You need to use temporary tables to store result sets , Common in sorting and grouping queries
Using filesort:MySQL The sort operations that can't be done with indexes in are called “ File sorting ”
( If there are more than two kinds of red Using temporary and Using filesort Low efficiency )
Using join buffer: The change emphasizes that no index is used to get the connection conditions , And you need to connect buffers to store intermediate results . If this value appears , That should pay attention to , Depending on the specific situation of the query, you may need to add indexes to improve the performance .
Impossible where: This value emphasizes where Statement will result in no eligible rows .
Select tables optimized away: This value means only by using the index , The optimizer may only return one line from the aggregate function result
( When a composite index is reused , Try to consider the query , Common sorting direction and field combination order )

7. matters needing attention

When using indexes , Here are some tips and precautions :

1. The index will not contain null Columns of values
As long as the column contains null Values will not be included in the index , Only one column in a composite index contains null value , So this column is invalid for this composite index . So we should not let the default value of the field be null.

2. The query uses only one index , So if where If index has been used in clause , that order by The columns in will not use indexes . So the database default sorting can meet the requirements of the case do not use sorting operations ; Try not to include sorting of multiple columns , If you need to create a composite index for these columns .

3.like Statement operation
In general, it is not recommended to use like operation , If necessary , How to use it is also a problem .like “%aaa%” Index will not be used and like “aaa%” You can use index .

4. Don't operate on Columns
This will cause the index to fail and perform a full table scan , for example

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

5. Don't use not in and <> operation

copyright:author[Prodigal son Tang Shao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130729034864.html