MySQL optimization (2): Index Brief

first, the basic grammar

the indexes are sorted before they are created. affect the efficiency of where and order by.

The index is also a table that holds the primary key and index fields and records that point to the entity table, and is the space that is stored in the disk as an index file.

When adding or deleting data, index points also need to be changed, so it will be slow, the fields that need frequent deletion do not fit the index.

The establishment of indexes also needs constant optimization and adjustment.　

1, create, two ways of writingCREATE [UNIQUE] INDEXIndex name ontable (column 1, column 2); Primary KEY index:ALTER TABLETableADD PRIMARY KEY(column); #添加一个主键, the index value must be unique and cannot be a null unique index:ALTER TABLETableADD UNIQUEIndex name (column 1, column 2); #索引列的值必须唯一, you can have multiple single-valued indexes for Null,null: One index contains only a single column, and one table can have multiple single-column indexed composite indexes:ALTER TABLETableADD INDEXIndex name (column 1, column 2); #普通索引, index values can appear multiple times*Full-text index:ALTER TABLETableADDFulltext index name (column);2, deleteDROP INDEXIndex name on TABLE;3, view showINDEX  fromTable

TwoIndex Structure

1, Btree
2. Hash index
3. Full-text Full-text index
4. R-tree Index

"Btree Retrieval Principle"

The height of the tree indicates the maximum number of times to find, the wider the breadth, the smaller the depth, the less the number of lookups

B+tree All index data is on the leaf node.

　　

　　

Three, the index optimization idea

1, turn on slow query log, set more than a few seconds for slow SQL, crawl slow SQL

2, through explain to slow SQL analysis (focus)

3,show profile Query SQL in the MySQL server execution details and life cycle situation (focus)

4. Tuning the parameters of the database server

　　

These four steps are summarized in the next few chapters.

FourIndex Usage Scenarios

What are the best things to build:

① Primary key automatically creates a unique index

② fields that are associated with other tables in a query, and foreign key relationships are indexed

③ fields that are frequently used as query criteria should create an index

④ the order of the fields after querying or sorting is best and the index is the same

⑤ Single-value/Combined index selection problem, inclined to create composite index under high concurrency

⑥ Statistics or grouping fields in a query

　What is best not to build:

① table records too little

② often delete and change the table, because when updating the table, not only to save the data, but also to save the index file.

③ If a data column contains many duplicate content , indexing it does not have much practical effect, indexing only the most frequently queried and most frequently sorted data.

a field with too much space for ④ data should not be indexed and occupy more space, resulting in greater depth of the tree

(The number of IO depends on the height of the B + tree H, assuming that the current data table data is n, the number of data items per disk block is M = size of the disk block/data item size, disk block size is fixed, if the data items occupy less space, the more data items per disk block, that is, each layer can hold more data, The lower the height of the tree. )

do not create an index in a field that is not available in the ⑤where condition

⑥ frequently updated fields are not suitable for indexing because each update does not just update the record but also updates the index

Reference Link: Https://www.cnblogs.com/ManyQian/p/9076247.html#_label2

MySQL optimization (2): Index Brief