1. Performance degradation Cause of SLOW SQL query

(1) Query statement performance is low;

② Index failure single value/composite index;

③ Too many databases associated with too many joins will also lead to slow queries (design defects or business requirements)

(4) Server tuning each parameter setting problem (buffer/thread pool, etc.);

2. Common common Join query

2.1 SQL execution sequence

① The incoming SQL statement

SELECT DISTINCT
<select_list>
FROM
<left_table>
<join_type> JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_conditon>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
Copy the code

② Optimized SQL statement

With the iteration upgrade of MySQL version, the optimizer is also constantly upgraded. The optimizer will analyze the performance consumption caused by different execution order and dynamically adjust the execution order. The following is the common query order:

FROM
<left_table>
<join_type> JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_conditon>
SELECT DISTINCT
<select_list>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
Copy the code

SQL Parsing Flowchart

MySQL server executes SQL statements FROM FROM;

2.2 SQL JOIN

2.2.1 Creating a Database table

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null.PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('hua'.'hua');
INSERT INTO t_dept(deptName,address) VALUES('gai'.'the luoyang');
INSERT INTO t_dept(deptName,address) VALUES('emei'.'Mount Emei');
INSERT INTO t_dept(deptName,address) VALUES('wudang'.'Wudang Mountain');
INSERT INTO t_dept(deptName,address) VALUES('zoroastrianism'.'Bright Top');
INSERT INTO t_dept(deptName,address) VALUES('the shaolin'.'Shaolin Temple');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('The wind is clear'.90.1.100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(Yue Buqun.50.1.100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(Linghu Chong.24.1.100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Hong Qi Gong'.70.2.100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('look crazy'.35.2.100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Extinct master tai'.70.3.100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhou Zhiruo'.20.3.100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhang SAN Feng'.100.4.100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhang Wuji'.25.5.100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES(Trinket.18.null.100010);
Copy the code

Table structure after creation:

mysql> show tables;
+---------------------+
| Tables_in_base_crud |
+---------------------+
| tbl_dept            |
| tbl_emp             |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from tbl_emp;+----+--------------+------+--------+--------+ | id | name | age | deptId | empno | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 90 | | 100001 | 1 the breeze pure Yang | 2 | yue not the cluster 50 | 1 | | 100002 | | 3 | | linghu chong 24 | 1 | 100003 | | | 4 hong seven | 70 | | 100004 | | | 5 xiao feng 35 | 2 | | 100005 | | 6 extinction teacher | | 70 | 3 | 100006 | | | 7 week zhi if | 20 | 3 | 100007 | | 8 zhang sanfeng | | 100 | | 100008 | | | 9 zhang mowgli 25 5 | 100009 | | | | | 10 weixiabao, 18 | NULL | | 100010 | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 10 rows in the set (0.00 SEC)
mysql> select * from tbl_dept;+ - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | | id the deptName | address | + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | huashan | huashan | | 2 | gai | | luoyang | 3 | emei mount emei | | | | 4 wudang wudang mountain | | | | 5 zoroastrianism light roof | | | | 6 shaolin temple of shaolin | | + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 6 rows in the set (0.00) sec)Copy the code

2.2.2 Internal Connection:

The INNER JOIN keyword returns a row if there is at least one match in the table. If rows in the “TableA” table do not match in the “TableB” table, those rows will not be listed, which is the common part of TableA and TableB.

SELECT
<select_list>
FROM
TableA A
INNER JOIN TableB B ON A.key = B.key
Copy the code

Example:

mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;+----+--------------+------+--------+--------+----+----------+-----------+ | id | name | age | deptId | empno | id | The deptName | address | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 90 | 1 | the breeze pure Yang 100001 | 1 | huashan | huashan | | 2 | yue not the cluster 50 | 1 | | 100002 | 1 | huashan | huashan | | 3 | make fox blunt 24 | 1 | | 100003 | 1 | huashan | huashan | | | 4 hong seven Mr | 70 | | 100004 | 2 | gai luoyang | | | | 5 xiao feng 35 | 2 | | 100005 | 2 | gai luoyang | | | | destroy teacher 100006 | | 70 | 3 | 3 | emei | Emei mountain | | | 7 week zhi if 20 100007 | | 3 | 3 | | emei mount emei | | | | 8 zhang sanfeng | 100 | | 100008 | | 4 wudang wudang mountain | | | | 9 zhang mowgli 25 | | | 5 100009 | | zoroastrianism | | top light + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 9 rows in the set (0.00) sec)Copy the code

2.2.3 Left Connection:

The LEFT JOIN keyword returns all rows from the LEFT table, even if there is no match in the right table. If there is no match in the right table, the result is NULL

SELECT
<select_list>
FROM
TableA A
LEFT JOIN TableB B ON A.key = B.key
Copy the code

Contains only the left table:

TableA is unique to TableB

SELECT
<select_list>
FROM
TableA A
INNER JOIN TableB B ON A.key = B.key
WHERE
B.key IS NULL
Copy the code

Example:

#Left connection
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;+----+--------------+------+--------+--------+------+----------+-----------+ | id | name | age | deptId | empno | id | The deptName | address | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 90 | 1 | the breeze pure Yang 100001 | 1 | huashan | huashan | | 2 | yue not the cluster 50 | 1 | | 100002 | 1 | huashan | huashan | | 3 | make fox blunt 24 | 1 | | 100003 | 1 | huashan | huashan | | | 4 hong seven Mr | 70 | | 100004 | 2 | gai luoyang | | | | 5 xiao feng 35 | 2 | | 100005 | 2 | gai luoyang | | | | destroy teacher 100006 | | 70 | 3 | 3 | emei | Emei mountain | | | 7 week zhi if 20 100007 | | 3 | 3 | | emei mount emei | | | | 8 zhang sanfeng | 100 | | 100008 | | 4 wudang wudang mountain | | | | 9 zhang mowgli 25 | | | 5 100009 | | zoroastrianism light roof | | | | 10 weixiabao, 18 | NULL | | 100010 | NULL | NULL | NULL | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 10 rows in the set (0.00 SEC)#Contains only the left side
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;+----+-----------+------+--------+--------+------+----------+---------+ | id | name | age | deptId | empno | id | The deptName | address | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | | 10 weixiabao, 18 | NULL | | 100010 | NULL | NULL | NULL | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01)  sec)Copy the code

2.2.4 Right Connection:

The RIGHT JOIN keyword returns all rows from the RIGHT table, even if there is no match in the left table. If there is no match in the left table, the result is NULL.

SELECT
<select_list>
FROM
TableA A
RIGHT JOIN TableB B ON A.key = B.key
Copy the code

Contains only the right table:

TableB is unique to TableA

SELECT
<select_list>
FROM
TableA A
INNER JOIN TableB B ON A.key = B.key
WHERE
A.key IS NULL
Copy the code

Example:

#The right connection
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;+------+--------------+------+--------+--------+----+----------+-----------+ | id | name | age | deptId | empno | id | The deptName | address | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 90 | 1 | the breeze pure Yang 100001 | 1 | huashan | huashan | | 2 | yue not the cluster 50 | 1 | | 100002 | 1 | huashan | huashan | | 3 | make fox blunt 24 | 1 | | 100003 | 1 | huashan | huashan | | | 4 hong seven Mr | 70 | | 100004 | 2 | gai luoyang | | | | 5 xiao feng 35 | 2 | | 100005 | 2 | gai luoyang | | | | destroy teacher 100006 | | 70 | 3 | 3 | emei | Emei mountain | | | 7 week zhi if 20 100007 | | 3 | 3 | | emei mount emei | | | | 8 zhang sanfeng | 100 | | 100008 | | 4 wudang wudang mountain | | | | 9 zhang mowgli 25 | | | 5 100009 | | zoroastrianism light roof | | | NULL | NULL | NULL | NULL | NULL shaolin temple of shaolin | | | | 6 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 10 rows in the set (0.00 SEC)#Contains only the right table
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;+------+------+------+--------+-------+----+----------+-----------+ | id | name | age | deptId | empno | id | deptName |  address | +------+------+------+--------+-------+----+----------+-----------+ | NULL | NULL | NULL | NULL | NULL | 6 | Shaolin temple of shaolin | | + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

2.2.5 Full Connection:

FULL OUTER JOIN keywords as long as the left table (table1) and right table (table2) match, one of the tables of the return line. The FULL OUTER JOIN keyword combines the results of the LEFT JOIN and RIGHT JOIN.

SELECT
<select_list>
FROM
TableA A
FULL OUTER JOIN TableB B ON A.key = B.key
Copy the code

Remove common parts:

SELECT
<select_list>
FROM
TableA A
FULL OUTER JOIN TableB B ON A.key = B.key
WHERE
A.key IS NULL
OR
B.key IS NULL
Copy the code

Example:

#All connection
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id-> union -> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id; +------+--------------+------+--------+--------+------+----------+-----------+ | id | name | age | deptId | empno | id | The deptName | address | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | | | 90 | the breeze pure Yang 1 | 100001 | 1 | huashan | huashan | | 2 | yue not the cluster 50 | 1 | | 100002 | 1 | huashan | huashan | | 3 | make fox blunt 24 | 1 | | 100003 | 1 | huashan | huashan | | | 4 Hong seven | 70 | | 100004 | 2 | gai luoyang | | | | 5 xiao feng 35 | 2 | | 100005 | 2 | gai luoyang | | | | destroy teacher 100006 | | 70 | 3 | 3 | emei | Emei mountain | | | 7 week zhi if 20 100007 | | 3 | 3 | | emei mount emei | | | | 8 zhang sanfeng | 100 | | 100008 | | 4 wudang wudang mountain | | | | 9 zhang mowgli 25 | | | 5 100009 | | zoroastrianism light roof | | | | 10 weixiabao, 18 | NULL | | 100010 | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | | 6 Shaolin temple of shaolin | | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 11 rows in the set (0.00 SEC)#Get rid of the common parts
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null-> union -> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null; +------+-----------+------+--------+--------+------+----------+-----------+ | id | name | age | deptId | empno | id | The deptName | address | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | | 10 weixiabao, 18 | | NULL 100010 | | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | NULL shaolin temple of shaolin | | | | 6 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

MySQL does not support FULL OUTER JOIN, so use UNION to associate left JOIN and right JOIN to find all operations.

3. Index introduction

3.1 an overview of the

An Index is a data structure that helps MySQL obtain data efficiently. The essence of an index is a data structure, which can be understood as a quickly ordered lookup data structure.

Indexes affect lookups after WHERE and sorts after ORDER BY

In addition to the data itself, the database also maintains a data structure that meets the specific search algorithm. These data structures point to the data in a certain way, so that high-level search methods can be realized on the basis of these data structures. This data structure is the index, that is, the BTREE index.

Indexes themselves are generally too large to be stored in memory, so index files are usually stored on disk as index files, myI files.

It is better not to delete the data in the database, set the mark position for logical deletion, deleted data may be used for data analysis, more importantly, frequent add and delete operations will cause index failure;

** If not specified, we usually refer to the index structure BTREE (search tree, not necessarily binary tree). ** Where clustered index, secondary index, compound index, prefix index, unique index are used by default B+ tree index, collectively called index. In addition to the B+ tree index structure, there are also hash indexes.

Advantages of indexes:

  • Improve the retrieval efficiency of data and reduce the IO file of database;
  • Sorting data by index column reduces data sorting cost and CPU consumption.

Disadvantages of indexes:

  • In fact, the index is also a table. The table holds the primary key and index fields, and points to the records of the entity table, so the index column also takes up space.
  • Although the index greatly improves the query speed, it will reduce the efficiency of updating tables, such as the tableINSERT,UPDATE,DELETEOperation. When updating a table, MySQL needs to save not only the data, but also the index file. Each update of a field to which an index column is added adjusts the index information after the key value changes as a result of the update.
  • Index is only one factor to improve efficiency. If MySQL has large data volume tables, create good indexes or optimize query statements.

3.2 Classification of indexes

3.2.1 Single-valued index

An index contains only a single column, and a table can have multiple single-column indexes.

CREATE command: CREATE INDEX idx_ table name_field name ON Table name (field name);

3.2.2 Unique Index

The value of the indexed column must be unique, but null values are allowed. Such as: bank card number, ID number and so on.

CREATE a UNIQUE INDEX idx_ table name _ table name ON table name (table name);

3.3.3 Composite indexes

An index contains multiple columns

CREATE command: CREATE INDEX idx_ table name_field 1_ field 2_ field n ON Table name (field 1, field 2,… N, field);

(1) Creating compound indexes is better than creating single-value indexes, and creating single-value indexes for frequently used fields;

② A table should have no more than 5 indexes;

Only one index is used at a time;

3.3.4 Primary key Index

When set as primary key, the database automatically creates indexes. InnoDB is a clustered index.

ALTER TABLE table_name ADD PRIMARY KEY; ALTER TABLE table_name ADD PRIMARY KEY;

ALTER TABLE table_name DROP PRIMARY KEY;

Modifying a primary key index: Deletes the primary key index and creates a new primary key index.

3.3.5 Basic syntax of indexes

3.3.5.1 create

CEREATE [UNIQUE] INDEX indexName ON tableName(columnName(length))

ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (columnName(length))

If the field type is CHAR or VARCHAR, length can be smaller than the actual length. If the type is BLOb or text, length must be specified

IndexName is usually named idx_tableName_columnName

3.3.5.2 delete

DROP INDEX [indexName] ON tableName

3.3.5.3 view

SHOW INDEX FROM tableName

3.3.5.4 useALTERThe command

Adding a primary key means that the index value is unique and cannot be NULL

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Create a unique index. The index value must be unique, but can be NULL or multiple times

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

Add a normal index. The index value may appear more than once

ALTER TABLE table_name INDEX index_name (column_list)

Specifies the index as FULLTEXT for full-text indexing

ALTER TABLE table_name ADD FULLTEXT index_name (column_list)

3.3 Index Structure

3.3.1 B Tree Index

Illustration:

  • Light blue – disk block; Dark blue — data item; Yellow — pointer;
  • For example, disk block 1 contains item 17 and 35 and Pointers P1, P2, and P3.
  • P1 indicates the disk block smaller than 17. P2 indicates disk blocks that are larger than 17 and smaller than 35. P3 indicates disk blocks larger than 35.
  • Non-leaf nodes do not store real data, but only the data items that guide the search direction. For example, data items 17 and 35 do not exist in the data table
  • The real data is stored in leaf nodes, between disks 5 and 11.

Search process: for example, find data 60

(1) First load disk 1 into memory, generate an IO operation, use binary search in memory to determine that 60 is greater than 35 (the comparison time in memory is so fast that it can be ignored), lock P3 pointer;

(2) Use P3 to find disk 4, load disk 4 into memory, perform a second DISK I/O, use binary search in memory to determine that 60 is less than 65, and lock P1 pointer;

③ Use P1 to find disk block 9 and perform I/O for the third time. The query is complete.

In actual MySQL queries, there are usually millions of pieces of data. If traversal is performed sequentially, a large number of disk I/OS will be performed, which seriously affects performance. However, data items can be found after three disk I/OS, which significantly improves performance.

3.3.2 B+ Tree Index

Differences between B+ Tree and B-tree:

(1) The keywords and records of b-tree are put together, and the leaf node can be regarded as an external node, which does not contain any information; The non-leaf node of B+ Tree contains only the keyword and the index pointing to the next node, and the record is placed only on the leaf node.

② In b-tree, the closer the root node is, the faster the record search time is. The existence of the record can be determined as long as the keyword is found. However, the search time of each record in B+ Tree is basically the same, which needs to go from the root node to the leaf node, and the key word in the leaf node needs to be compared. From this perspective, the performance of b-tree seems to be better than that of B+ Tree, but in practical applications, THE performance of B+ Tree is better. The non-leaf nodes of a B+ Tree do not store actual data. Therefore, each node can contain more elements and the Tree height is smaller than that of a B-tree. In this way, disk access times are reduced. Although B+Tree takes more time to find a record than B+Tree, disk I/O time is equivalent to hundreds of memory comparisons, so the actual performance of B+Tree may be better, and the leaf nodes of B+Tree are linked together using Pointers. It is easy to traverse (for example, to view all files in a directory, all records in a table, etc.), and many databases and file systems use B+ Tree.

B+ Tree is more suitable than B-tree for file and database indexes in operating systems.

① The disk cost of B+ Tree is lower

The internal node of B+ Tree does not have a pointer to the specific information of the keyword. Therefore, its internal nodes are smaller than those of b-tree. If the keywords of the same internal node are stored in the same disk block, the more keywords that the disk block can contain, the more keywords that need to be searched in the memory at a time, and the relative DISK I/O times are reduced.

② The query efficiency of B+ Tree is more stable

Because non-endpoints are not the nodes that ultimately point to the contents of the file, they are just indexes of the keywords in the leaf nodes. So any keyword lookup must go from the root node to the leaf node. The length of all keyword query paths is the same, resulting in the same query efficiency for each data.

3.3.3 Clustered and non-clustered indexes

Clustered indexes are not a separate type of index, but a way of storing data. Clustered data rows are stored with adjacent clusters of key values. In the figure below, the index on the left is a clustered index because the rows are arranged on disk in the same way as the index sort.

Benefits of clustered indexes:

When a certain range of data is displayed in a clustered index sequence, the database does not need to extract data from multiple data blocks because the data is closely connected, saving a lot of I/O operations.

Limitations of clustered indexes:

Since MySQL database currently only InnoDB data storage engine supports clustered indexes, Myisam does not support clustered indexes.

Data can be stored in only one way. Therefore, each MySQL table can have only one cluster index, which is generally the primary key of the table.

To take full advantage of clustered indexes, InnoDB tables use sequential I’s for primary key columns rather than unordered ids such as UUID.

Other index types:

  • A Hash index
  • Full-text Full-text index
  • R – Tree indexes

3.4 Index creation time

3.4.1 Indexes Need to be created

① The primary key automatically creates a unique index;

② The fields frequently used as query conditions should be indexed;

③ Query the sorted field, if the sorted field through the index access will greatly improve the speed of sorting;

④ Query statistics or grouped fields;

Create a single-column index or a composite index? Composite indexes tend to be created in high concurrency scenarios

3.4.2 No index creation is required

MySQL is capable of supporting 3 million data volumes.

Select * from ‘where’; select * from ‘where’;

③ Tables that are frequently added or deleted or fields that are frequently updated are not suitable for creating indexes.

(4) Repeated and evenly distributed table fields;

Updating a field not only updates the record but also updates the index, which increases the IO burden.

Note: If a data column contains a lot of duplicate content, creating an index for it doesn’t have much practical effect.

Index selectivity: the ratio of the number of different values in the index column to the number of records in the table. For example, if a table has 2000 records and the index of the table has 1980 different values, the selectivity of the index is 1980/2000 = 0.99. The closer the index selectivity is to 1, the more efficient the index will be.