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

MySQL Database Advanced (6)-- Index 1, Index introduction 1, Index introduction

Index is a data structure that helps MySQL obtain data efficiently.

In MySQL, the index belongs to the concept of storage engine level, and different storage engines implement the index in different ways. The MyISAM and InnoDB storage engines support only BTREE indexes, while the MEMORY/HEAP storage engines support HASH and BTREE indexes.

2. The advantages of index

A. improve the efficiency of data retrieval and reduce the IO cost of the database.

B. sort the data through the index to reduce the cost of data sorting and reduce the consumption of CPU.

C, greatly speed up the query speed of data.

3. Shortcomings of the index.

A. it takes time to create and maintain the index, and the time spent increases as the amount of data increases.

B, the index also needs to take up space. We know that the data in the data table will also have the maximum online setting. If we have a large number of indexes, the index file may reach the online value faster than the data file.

C. when the data in the table is added, deleted and modified, the index also needs dynamic maintenance, which reduces the maintenance speed of the data.

4. The principle of using the index

A. the primary key automatically sets up a unique index

B. fields that are frequently used as query criteria should be indexed.

C. the fields associated with other tables in the query are indexed by foreign key relationships.

D. frequently updated fields are not suitable for indexing, because each update will update not only the record but also the index.

Fields not used in E and WHERE conditions will not be indexed.

F, the selection of single key / combined index, who? (tends to create composite indexes under high concurrency)

G, the sorted fields in the query. If the sorted fields are accessed through the index, the sorting speed will be greatly improved.

H, statistics or grouping fields in the query

Situations where indexing is not suitable:

Avoid too many indexes on tables that are updated frequently, and create indexes on fields that are often used for queries.

B, it is best not to use an index for a table with a small amount of data. Because there is less data, it may take less time to query all the data than to traverse the index, and the index may not produce optimization results.

C. do not build an index on columns with fewer values. For example, in the "gender" field of the student table, there are only two different values, male and female. An index can be established in a column with more different values.

2. Classification of indexes 1. Single-column index

A single-column index contains only a single column, but there can be multiple single-column indexes in a table.

A, general index

A normal index allows you to insert duplicate and null values in the columns that define the index.

B, unique index

The value in the index column must be unique, but null values are allowed.

C, primary key index

The primary key index is a special unique index, and null values are not allowed.

2. Composite index

For an index created on a combination of multiple fields in a table, the index is used only if the left field of the combined field is used in the query condition, and the composite index follows the leftmost prefix set.

3. Full-text index

Full-text indexing, which is only supported by the MyISAM storage engine, can only be used on CHAR, VARCHAR, and TEXT type fields.

The full-text index is mainly used to find keywords in the text, rather than directly comparing with the values in the index. When the amount of data is large, it is much faster to put the data into a table without a full-text index, and then to create a fulltext index with CREATE index than to create a fulltext for a table and then write the data.

4. Spatial index

Spatial index is an index of fields of spatial data types. There are four spatial data types in MySQL: GEOMETRY, POINT, LINESTRING, and POLYGON.

Use the SPATIAL keyword when creating a spatial index.

The spatial index must use the MyISAM storage engine, and the fields of the spatial index must be non-empty.

Operation of the index 1. Creation of the index

The syntax for creating an index when creating a table:

CREATE TABLE table_ name [col _ name data type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_ name [length]) [asc | desc]

The syntax for creating an index on a table:

ALTER TABLE tablename ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [indexname] (col_name) [ASC | DESC]; CREATE [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] indexname ON tablename (col_ name [length]) [ASC | DESC]

Unique | fulltext is an optional parameter, indicating unique index and full-text index, respectively.

Index and key are synonyms for the same purpose and are used to specify the creation of an index

Col_name is the field column that needs to be indexed, which must be selected from multiple columns defined in the data table

Index_name specifies the name of the index, which is an optional parameter. If not specified, the default col_name is the index value.

Length is an optional parameter, indicating the length of the index. Only fields of string type can specify the length of the index.

Asc or desc specifies index value storage for ascending or descending order

If you do not specify an index name when creating an index, the field name is used as the index name by default.

2. Creation of general index

Create an index directly

CREATE INDEX index_name ON tablename (column (length))

Modify table structure

ALTER TABLE table_name ADD INDEX index_name ON (column (length))

Specify an index when creating a table

CREATE TABLE tablename (col_name1 type,col_name2 type,INDEX index_name (col_name)); 3. Creation of unique index

The value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique.

Create a unique index directly

CREATE UNIQUE INDEX indexName ON tablename (column (length))

Modify table structure

ALTER TABLE table_name ADD UNIQUE indexName ON (column (length))

Specify directly when you create a table

CREATE TABLE tablename (col_name1 type,col_name2 type,UNIQUE INDEX index_name (col_name)); 4. Creation of primary key index

Modify table structure

ALTER TABLE table_name ADD PRIMARY KEY (col_name)

Specify directly when you create a table

CREATE TABLE tablename (col_name1 type,col_name2 type,PRIMARY KEY (col_name)); 5. Creation of composite index

Create a composite index directly

CREATE INDEX indexName ON tablename (col_name1,col_name2)

Modify table structure

ALTER TABLE table_name ADD INDEX indexName (col_name1,col_name2)

Specify directly when you create a table

CREATE TABLE tablename (col_name1 type,col_name2 type,INDEX index_name (col_name1,col_name2)); 6. Creation of full-text index

Create a full-text index directly

CREATE FULLTEXT INDEX indexName ON tablename (col_name)

Modify table structure

ALTER TABLE table_name ADD FULLTEXT INDEX indexName (col_name)

Specify directly when you create a table

CREATE TABLE tablename (col_name1 type,col_name2 type,FULLTEXT INDEX index_name (col_name))

When using full-text indexing, you need to rely on MATCH AGAINST operations, rather than normal WHERE statements plus LIKE. There are many restrictions on full-text indexing, for example, you can only use the MyISAM storage engine, for example, you can only set full-text indexing on CHAR, VARCHAR, and TEXT. For example, the search keyword should be at least 4 characters by default. For example, if the search keyword is too short, it will be ignored.

SELECT * FROM tablename WHERE MATCH (col_name) AGAINST ('pattern')

Col_name is a full-text indexed column, and 'pattern' is a matching string

7. Delete DROP INDEX [indexName] ON tablename; ALTER TABLE tablename DROP INDEX indexname;8, view index information

SHOW INDEX FROM table_name

9. Matters needing attention in the index

A, the index will not contain columns with null values

Do not let the default value of the field be null when designing the database.

B. Use short index

C, index column sort

Therefore, do not use the sort operation when the database default sorting can meet the requirements; try not to include multiple column sorting, and it is best to create a composite index on these columns if necessary.

D, like statement operation

The like operation is not recommended in general, if it must be used. Like "% aaa%" does not use an index while like "aaa%" can use an index.

E. do not operate on the column

Operating on an index column will cause the index to fail and perform a full table scan, such as

SELECT * FROM table_name WHERE YEAR (column_name) = i doinsert TStudent values (LPAD (convert (iGramchar (10)), 10memorials 0'), CreateName (), if (ceil (rand () * 10)% 2pm (male), 'female'), RPAD (convert (ceil (rand () * 1000000000000000000), char (18)), 18pence0'), Concat (convert (ceil (rand () * 10) + 1980Jing char (4),'-') LPAD (convert (ceil (rand () * 12), char (2)), 2jime (0'),'-', LPAD (ceil (rand () * 28), char (2)), 2mai (0')), Concat (PINYIN (sname),'@ hotmail.com'), case ceil (rand () * 3) when 1 then 'network and website development' when 2 then 'JAVA' ELSE' NET' END, NOW ()) Set iTunes 1. End while;select * from TStudent;end

Modify the addStudent stored procedure to insert 500000 records

Call addStudent (500000)

The SQL statement queries students whose cardID starts with 12345.

Select * from TStudent where cardID like '12345%'

It takes 1.27 seconds.

2. Add an index to the column

Alter table TStudent add index cardidIndex (cardID)

The SQL statement queries students whose cardID starts with 12345.

Select * from TStudent where cardID like '12345%'

It takes 31 milliseconds.

3. Check the disk space occupied by the index

The disk space occupied by the schoolDB database index.

SELECT CONCAT (ROUND (SUM (index_length) / (1024 / 1024), 2), 'MB') AS' Total Index Size' FROM information_schema.TABLESWHERE table_schema LIKE 'schoolDB'

View the disk space consumed by the schoolDB database data.

SELECT CONCAT (ROUND (SUM (data_length) / (1024 / 1024), 2), 'MB') AS' Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'schoolDB';4, view the QL statement execution plan

EXPLAIN can view the query plan of the SQL query statement, using an index or a full table scan, and key displays the index used.

Explain select * from TStudent where cardid like '12345%'\ G

Id: the SELECT identifier, that is, the query sequence number of SELECT, the number of times select appears in a statement.

Select_type: the type of SELECT query used, represented by SIMPLE as a simple SELECT, not practical UNION or subquery. Other values, PRIMARY: the outermost SELECT will have more than two SELECT when it has a subquery. The second or subsequent select statement SUBQUERY in UNION:union (two table joins): in the subquery, the second SELECT.

Table: the name of the datasheet. Arranged in the order in which they were read.

Type: specifies the association between this data table and other data tables. All records in the table that match the retrieval values are taken out and joined with the records taken from the previous table. Ref is used when the linker uses the leftmost prefix of the key or where the key is not a primary key or unique index. Values include system, const, eq_ref, index and All.

Each index that possible_keys:MySQL can choose when searching for data records

Key: the actual selected index

Key_len: displays the length of the index used by MySQL (the number of indexes used). When the value of the key field is null, the length of the index is null.

Ref: gives the name of the data column in another data table in the association relationship.

The number of rows that rows:MySQL expects to read from the data table when executing the query.

Extra: provides information about the associated operation.

Overlay Index 1. Overlay Index

An index that contains the fields required by the query is called an override index (covering index). MySQL only needs to return the data needed by the query through the index, without having to go back to the table after finding the index, which reduces IO and provides efficiency.

When you view the execution plan of the SQL statement through EXPLAIN, when the Using Index prompt appears in the extra column of EXPLAIN, it indicates that the SQL query uses an override index.

2. Use SQL statements that override the index

The cardID column of the Tstudent table creates the index, and the SQL statement looks for the column cardID, and the search condition is also cardID, so it uses the cardID index to find it, and there is no need to scan the page of the table.

Explain select sname from TStudent where sname like 'Liu%'

Using index appears in the execution result Extra, indicating that the overlay index lookup is used.

3. Use overlay index to implement order by sorting.

There are two ways to sort ORDER BY in MySQL:

A. using ordered index to obtain ordered data

B, file sorting

When using EXPLAIN to analyze the SQL query, the ordered index is used to obtain the ordered data to display Using index. File sorting shows Using filesort.

Explain select email from TStudent order by email

The email column does not have an index, and the query plan of the SQL statement shows that Extra is using filesort, indicating that the results are sorted in memory, requiring additional time overhead.

After adding an index to the Email column

Alter table TStudent add index emailIndex (email); explain select email from TStudent order by email

When executed again, you can see that the extra column is Using index, indicating that index sorting is used with no extra time overhead.

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.