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

This article mainly introduces "Mysql index detailed introduction". In daily operation, I believe many people have doubts about Mysql index detailed introduction. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts of "Mysql index detailed introduction"! Next, please follow the small series to learn together!

1 What is index? 1. defined

An index is a data structure that helps mysql retrieve data efficiently.

In addition to the data itself, the database also maintains a data structure that satisfies a specific lookup algorithm, and these data structures point to the data in a way that allows advanced lookup algorithms to be implemented on top of these data structures.

Index Data Correspondence Diagram ## 1. create an index

mysql> create index idx_employee_username on employee(username);

Query OK, 0 rows affected (1.71 sec)

Records: 0 Duplicates: 0 Warnings: 0

## 2. Query index. From the query results, the type of index is B-tree

mysql> show index from employee;

+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| employee | 1 | idx_employee_username | 1 | username | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |

+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1 row in set (1.73 sec)

Note: the index itself is also very large, it is impossible to store all in memory, so the index index is often stored in the form of an index file to disk; if there is no specific description, the data structure corresponding to the index is B-tree.

2. Introduction to B-tree

B-tree: Its design idea is to concentrate related data together as much as possible in order to read multiple data at once and reduce the number of hard disk operations. It is an improvement of binary search tree. It has the following three characteristics:

A node can hold multiple values. New layers are not added unless the data is already full. The values in the child nodes have a strict size correspondence with the values in the parent node. In general, if the parent node has a value, then there are a+1 child nodes. For example, in the figure above, the parent node has two values (7 and 16), which corresponds to three child nodes. The first child node has values less than 7, the last child node has values greater than 16, and the middle child node has values between 7 and 16. Is an ordered fast lookup data structure. 2. Classification and use of index 1. Classified single-valued index: An index contains only one column (a table can contain multiple single-valued indexes). Composite index: An index contains multiple columns. Unique index: The index column value must be unique and can be null. 2. Basic syntax creation: CREATE [UNIQUE] INDEX indexName ON tableName(columnName); and ALERT tableName ADD [UNIQUE] INDEX indexName ON (columnName); delete: DROP INDEX [indexName] ON tableName; view: SHOW INDEX FROM tableName; iii. advantages and disadvantages of index? 1. Benefits == Improve data indexing efficiency and reduce database io costs.== BTREE index data structure, this data structure is very conducive to reducing the number of hard disk reads. Assuming that a node can hold 100 values, then a 3-level B-tree can hold 1 million data! Assuming that the operating system reads one node at a time and the root node remains in memory, B-tree finds the target value in 1 million data and only needs to read the hard disk twice. Sorting data through index columns reduces the cost of data sorting and CPU consumption. 2. An index itself is also a table that holds the primary key and index fields and points to records in the entity table, and the index also takes up space. Although it greatly increases the query speed, it will slow down the update speed of the table (update, delete and insert), because updating the table not only saves the data, but also updates the index accordingly. Indexing is only one factor in improving efficiency, and if you have a large number of tables, it takes time to build the best index. 4. Use of index? Not suitable for use in scenarios? 1. Applicable scenarios (large amount of table data) Primary keys automatically establish unique indexes Fields frequently used as query criteria should create index queries Fields associated with other tables, foreign key relationships establish index queries Sort fields should create index queries Statistics or grouping fields 2. Not applicable Scenarios Frequently updated fields or tables are not suitable for indexing (reduce update efficiency). Fields not used in the where condition are not indexed (wasted space). The table has a small amount of data and is not suitable for indexing. Data columns with high repetition rates are not suitable for indexing, such as gender: male, female. With only these two fields, it is not meaningful to establish an index. V. Performance analysis 1. Mysql Query Optimizer

mysql has an optimizer specifically responsible for optimizing SELECT statements. Its main function is to improve the default optimal execution plan for the Query requested by the client by calculating the statistical information collected in the analysis system (which may violate what we think is optimal, which takes the longest time).

2. Common performance bottlenecks CPU: CPU when saturated data generally occurs in memory or read data from disk IO: disk I/O bottleneck occurs when the load data is much larger than the memory capacity Server hardware performance bottlenecks: top,free,iostat and vmstat to view positioning 3. SQL statement issues

Explain Execution can view sql running status, syntax is as follows: EXPLAIN sql statement

mysql> explain select * from employee where username='jhon';

+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | employee | NULL | ref | idx_employee_username | idx_employee_username | 83 | const | 1 | 100.00 | NULL |

+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

At this point, the study of "Mysql Index Details" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.