MySQL database Advanced (vi)--index

MySQL database Advanced (vi)--index One, introduction to the index 1, Introduction to the index

Index is the data structure that helps MySQL to get data efficiently.
In MySQL, indexes belong to the concept of storage engine level, and different storage engines implement indexes differently. The MyISAM and InnoDB storage engines support only btree indexes, and the MEMORY/HEAP storage engine supports hash and btree indexes.

2, the advantages of the index

A, improve the efficiency of data retrieval, reduce the IO cost of the database.
B, the index to the data sorting, reduce the cost of data sorting, reduce the CPU consumption.
C, greatly accelerate the data query speed.

3. Disadvantages of the Index

A, it takes time to create indexes and maintain indexes, and increases the time it takes to increase the amount of data
B, the index also needs to occupy space, we know the data table data will also have the largest on-line settings, if we have a large number of indexes, the index file may be faster than the data file to reach the line value
C, when the data in the table to increase, delete, modify, the index also needs dynamic maintenance, reduce the data maintenance speed.

4, the use of the index principle

A, the primary key automatically create A unique index
B. Fields that are frequently used as query criteria should create indexes
C, the fields that are associated with other tables in the query, and the foreign key relationships are indexed
D. Frequently updated fields are not suitable for indexing because each update does not just update the record but also updates the index
E, fields not used in the Where condition do not create an index
F, single-key/combination index selection question, who? (Inclined to create a composite index under high concurrency)
G, the fields sorted in the query, the sorted fields will greatly improve the sorting speed if accessed by the index
H, statistics or grouping fields in queries
Not suitable for use with indexes:
A. Avoid too many indexes on frequently updated tables, and you should create indexes on the fields that are frequently used for queries.
B, the data volume of the table is best not to use the index, because the data is small, may query all the data spend more time than the time to traverse the index, the index may not have an optimization effect.
C, do not set up indexes on columns of different values, for example, in the "Gender" field of the student table, only male, female two different values. A column with a different value can be indexed.

Ii. Index Classification 1, single-column index

Single-column indexes contain only a single row, but there can be multiple single-row indexes in one table.
A, normal index
A normal index allows you to insert duplicate and null values in the column that defines the index.
B, unique index
The values in the indexed column must be unique, but allow null values.
C, PRIMARY key index
A primary key index is a special unique index that does not allow null values.

2. Composite Index

Indexes created on multiple field combinations in a table are used only when the left field of a combination of multiple fields is used in the query criteria, and are followed by the leftmost prefix collection when using a composite index.

3. Full-Text Indexing

Full-text indexing, supported only by the MyISAM storage engine, with full-text indexing only on char, VARCHAR, Text type fields.
Full-text indexes are primarily used to find keywords in text, rather than directly compared to values in the index. When the data volume is large, the data is placed in a table without a full-text index and then created with the CREATE INDEX, which is much faster than establishing a fulltext for a table and then writing the data fulltext.

4. Spatial index

Spatial indexes are indexes on fields of spatial data types, and there are four types of spatial data in MySQL: GEOMETRY, point, LINESTRING, POLYGON.
When creating a spatial index, use the spatial keyword.
The spatial index must use the MyISAM storage engine, and the field for the spatial index must be non-empty.

Iii. operation of the index 1, creation of the index

Syntax for creating indexes when creating tables:

CREATE TABLE table_name[col_name data type][UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[asc|desc]

syntax for creating indexes 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 that represents a unique index, a full-text index, respectively
Index and key are synonyms, both of which have the same effect, and are used to specify index creation
Col_name is the field column for which you want to create an index, and the column must be selected from multiple columns in that definition in the datasheet
Index_name the name of the specified index, as an optional parameter, if not specified, the default col_name is the index value
Length is an optional parameter, which indicates the index size, only a field of type string can specify the index length
ASC or DESC Specifies that an ascending or descending index value is stored
If you do not specify an index name when you create an index, the field name is used by default as the index name.
1, the creation of ordinary 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));

2. Creation of unique indexes

The value of the indexed column must be unique, but a null value is allowed. If it is a composite 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 creating a table

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

3. Creation of primary key index

Modify Table Structure
ALTER TABLE table_name ADD PRIMARY KEY(col_name)
Specify directly when creating a table

CREATE TABLE tablename(col_name1 type,col_name2 type,PRIMARY KEY(col_name));

4. Creation of composite indexes

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 creating a table

CREATE TABLE tablename(col_name1 type,col_name2 type,INDEX index_name(col_name1,col_name2));

5. Creation of full-text indexing

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 creating 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 use the match against operation instead of the generic where statement to add like. There are many limitations to full-text indexing, such as using only the MyISAM storage engine, such as setting up full-text indexing on Char, VARCHAR, and text only. For example, the search keyword by default at least 4 characters, such as the search keyword is too short to be ignored.
SELECT * FROM tablename WHERE MATCH(col_name) AGAINST(‘pattern‘);
Col_name is a full-text indexed column, ' pattern ' is a matching string

6, the deletion of the index

DROP INDEX [indexName] ON tablename; ALTER TABLE tablename DROP INDEX indexname;

7. Viewing of index information

SHOW INDEX FROM table_name;

8. Considerations for Indexing

A, the index does not contain a column with null values
Do not let the default value of the field be null at database design time.
B. Using a short index
C, index column sorting
So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
D, like statement operations
It is generally not recommended to use the like operation if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
E, do not perform calculations on columns
An operation on an indexed column causes the index to fail with a full table scan, for example
SELECT * FROM table_name WHERE YEAR(column_name)<2017;
F, do not use not in and <> operations

Four, index query speed comparison

1. Inserting records

create procedure addStudent(in num int)begindeclare i int;set i=1;delete from TStudent;while num>=i doinsert TStudent values (       LPAD(convert(i,char(10)),10,‘0‘),       CreateName(),       if(ceil(rand()*10)%2=0,‘男‘,‘女‘),       RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,‘0‘),       Concat(convert(ceil(rand()*10)+1980,char(4)),‘-‘,LPAD(convert(ceil(rand()*12),char(2)),2,‘0‘),‘-‘,LPAD(convert(ceil(rand()*28),char(2)),2,‘0‘)),       Concat(PINYIN(sname),‘@hotmail.com‘),       case ceil(rand()*3) when 1 then ‘网络与网站开发‘ when 2 then ‘JAVA‘ ELSE ‘NET‘ END,       NOW());set i=i+1;end while;select * from TStudent;end

Modify the addstudent stored procedure, insert 500,000 records
call addStudent(500000);
SQL statement Query XXX number Cardid students starting with 12345.
select * from TStudent where cardID like ‘12345%‘

Takes 1.27 seconds

2. Add an index to the XXX column

alter table TStudent add index cardidIndex(cardID);
SQL statement Query XXX number Cardid students starting with 12345.
select * from TStudent where cardID like ‘12345%‘
Takes 31 milliseconds.

3. View the disk space occupied by the index

SCHOOLDB the disk space occupied by the database index.

View disk space consumed by 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 query plans for SQL query statements, use an index or a full table scan, and key displays the index used.
explain select * from TStudent where cardid like ‘12345%‘ \G;

The id:select identifier, which is the query sequence number for SELECT, is the first occurrence of a single statement.
Select_type: The type of select query used, simple is represented as a straightforward select, not a practical union or subquery. Other values, PRIMARY: The outermost select has more than two select when it has a subquery. Union:union (two table connections) in the second or subsequent SELECT statement? Subquery: In the subquery, the second select.
Table: The name of the data table. Sorted by the order in which they were read.
Type: Specifies the association between this data table and other data tables, and all records that match the retrieved values in the table are taken out and combined with the records taken from the previous table. Ref is used when the connector uses the leftmost prefix of a key or if the key is not a primary key or a unique index. The values are system, const, EQ_REF, index, and all.
Possible_keys:mysql the various indexes that can be selected when searching for data records
Key: The actual selected index
Key_len: Shows the length of the index used by MySQL (the number of indexes used), and the length of the index is null when the value of the key field is null.
Ref: gives the name of the data column in another data table in the associated relationship.
Rows:mysql the number of rows of data that are expected to be read from the data table when the query is executed.
Extra: Provides information about the associated operation.

V. Overwrite index 1, overwrite index

An index that contains the fields required by the query is called the Overwrite index (covering). MySQL only needs to pass the index to return the data required by the query, without having to go back to the table after the index is found, reduce IO, and provide efficiency.
When you view the execution plan for an SQL statement through explain, the use index hint appears in the extra column of explain, stating that the SQL query uses an overlay index.

2. Using SQL statements that overwrite indexes

Tstudent Table Cardid Column creates an index, the SQL statement looks for a column that is Cardid, the lookup condition is also Cardid, and the Cardid index is used to find the page that does not need to scan the table.
explain select sname from TStudent where sname like ‘刘%‘;
The execution result extra appears using index, which indicates that the overwrite index lookup is used.

3. Using an overlay index to sort order by

The order by in MySQL is implemented in two ways:
A. Get ordered data with an ordered index
B, file sorting
When parsing SQL queries using explain, an ordered index is used to show the using index of the ordered data. And the file sort shows the using Filesort.
explain select email from TStudent order by email;
The e-mail column has no index, and the query plan for the SQL statement can see that extra is a using filesort, stating that the results are sorted in memory and require additional time overhead.
After you add an index to an email column,

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

Once again, you can see that the extra column is using index, which shows that the index is sorted with no additional time overhead.

MySQL database Advanced (vi)--index