An overview of the

Mysql > select * from B+ tree; Mysql > select * from B+ tree; Mysql > select * from B+ tree

Cover index

Create a secondary index and use index overwrites if you can get data directly from the secondary index file without accessing the clustered index (increment primary key index) file. This is extremely efficient.

select a from table xxx where b = 2Copy the code

As in the above statement, the index can be used to execute the SQL if it is only indexed for column B, but since the data for column A is not in the B index, the index needs to access the clustered index file again. If you create a joint index like (b,a), the joint index file will contain the values of columns A and B, which can be overridden by the index.

Joint index

A federated index is a multi-column index that exists to improve query performance.


CREATE TABLE `xxxx` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'on the',
  `code` int(10),
  `age` int(10),
  PRIMARY KEY (`id`),
  KEY `code` (`code`)
) ENGINE=InnoDBCopy the code

Some students will think that the use of single-column index can be directly, why must use the union index. In fact, as you can see from the example I gave above, if you just use a single column index, even though you also use the index, you often have to backtrack to the clustered index, which is still a performance penalty, especially if you have to do things like sort, which is even slower. Here’s another example, paging queries

select id,code from xxxx order by age limit50000, 10;Copy the code

This SQL query is not efficient if it is indexed only on the AGE column, but how to create a federated index (age,code) can be done without backtracking to the clustered index.

Do not index columns with simple enumerated values

A secondary index for a column with only 0 and 1 values is unnecessary because there is no distinction between them. For example, if you look at 0, you can find a lot of data from B+ numbers, which is poor performance.

Indexed columns do not participate in the calculation

If you want to query, you need to calculate the data in the tree first and then compare, which is too expensive and extremely slow. Therefore, the index column uses a function, which cannot use the index at all. MySql does not support this.

Expand indexes if you can, and try not to create new ones

If the database has an index (a,b) and now needs an index (B), create (a,b). Because MySql is optimized for queries like b=11 or b in (11,22), it can use indexes, so it can be used safely.

The original link

Mysql > select * from ‘Mysql’ where ‘Mysql’ is