How to create a MySql index

1. B-tree index

As the name suggests, an index whose structure is a B-tree is a B-tree index. Under normal circumstances, the conventional indexes created in the InnoDb engine are all B-tree structures.

B tree indexes are the following.

1.1. Clustered index/clustered index

When defining the primary key, the index automatically appended to the primary key is the clustered index, also known as the clustered index.

In Mysql, components are used to build a B-tree structure. As shown in the figure, each leaf node corresponds to a primary key and other related data.

If we do not define a primary key when we create the table, Mysql will automatically create a primary key and corresponding index. The primary key name is rowId

1.2. Auxiliary index/secondary index

Auxiliary index, also called secondary index, refers to the index created for non-primary key columns. Similarly, Mysql will create a B-tree for this index. In addition to the column value, the leaf nodes of the tree only contain the primary key value of the row where the column is located. In this way, the leaf nodes can be found through the column index. Then the primary key information in the leaf node is searched from the primary key index, and finally a whole row of data is obtained.

The act of finding the primary key through the secondary index and then getting a whole row of data from the primary key is called table return.

1.3. Joint index/composite index

1.3.1. What is a composite index

Aggregation index can be said to be a secondary index a special situation. Generally, secondary indexes only add indexes to one non-primary key column, while aggregate indexes add indexes to multiple columns at once.

General secondary indexes are created with this statement:

CREATE INDEX  order_name_index on t_order(order_name);

Compound indexes are created with this statement:

CREATE INDEX  order_name_and_order_type_index on t_order(order_name, order_type);

For compound indexes, MySQL will also create a B-tree, but because it is an index on multiple columns, the sorting rules of the B-tree are special and follow the leftmost principle. What is the leftmost principle will be discussed below.

After that, the leaf nodes contain multiple pieces of information. One is the value of each column used as the index, and the other is the value of the primary key.

1.3.2. The leftmost principle

The so-called leftmost principle is that the sorting rule of the B-tree is to sort the column names in the defined statement from left to right when defining the index. .

For example, the definition statement is as follows:

CREATE INDEX  joint_index on t_order(order_name, order_type, submit_time);

The sorting rule is to sort order_name first, and if order_name is the same, then sort order_type, the last row is submit_time.

Then when we query, according to the order of the columns from left to right according to the definition, the where clause or order by and other clauses should try to start with ## Start with #order_name, and so on.

For example, we have defined a composite index composed of the above three columns. When querying or sorting, try to

order_name first, then order_type, and finallysubmit_time.

select * from t_order where order_name = 'order1'
and order_type = 1
and submit_time = str_to_date('2022-08-02 00:52:26', '%Y-%m-%d %T')

The reason is very simple, because the sorting rule of the joint index is to sort

order_name first, if order_name is the same, then order_type, and finally submit_time. So only if this rule is followed when query sorting, can we use the index.

If we do not fully comply with the leftmost principle, for example, the query sort only arranges two columns, ignoring the middle

order by order_name, submit_time. At this time, Mysql will have intelligent processing, and it will judge whether it is faster to use the index or not to use the index.

1.3.3. Query optimization of joint index

Try to use the columns that make up the joint index and ensure the order. The order of columns can be viewed by querying the index. Check the fields returned by the query sql_in_index

show index from t_order;

. Try to only return the columns and primary keys that make up the joint index. Do not return other columns to avoid causing table back.

This should be easy to understand, because the leaf nodes of the B-tree of the joint index only contain the primary key and the values ​​of the columns that make up the joint index. If the returned fields are only these columns, then the query in a B-tree is complete. If you want to return other columns, you have to search in the index of the primary key and perform a table return operation.

2. Hash index

General databases use B-tree indexes to query data, but when the database is used for a period of time, InnoDB will record some hot data that is used more frequently, and then Hot data establishes an index of hash structure, which is the application scenario of hash index.

This index is enabled by default starting from Mysql 5.7.

2.1. View the hit rate and other information of the hash index

Use the statement:

show engine innodb status;

## among which

status

There is a lot of information, including the situation of hash index. Let's copy the information into the editor and view it. This section is the hash index information. <pre class="brush:sql;">------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 5 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) -- 哈希索引的命中率，可根据这个来决定是否使用哈希索引 0.00 hash searches/s, 0.00 non-hash searches/s ---</pre><h3>3、索引的创建策略</h3> <h4>3.1、 单列索引的策略</h4> <h5>3.1.1、列的类型占用的空间越小，越适合作为索引</h5> <p>因为B+树也是占用空间的，所以在固定空间中，如果列的类型占用的空间越小，那我们一次就能读取更多的B+树节点，这样自然就加快了效率。</p> <h5>3.1.2、根据列的值的离散性</h5> <p>离散性是指数据的值重复的程度高不高，假如有N条数据的话，那离散性就可以用数值表示，范围是1/N 到 1。</p> <p>比如说某个列在数据库中有下面几条数据（1, 2, 3, 4, 5, 5, 3），其中5和3都有重复，去重后应该是（1, 2, 3, 4, 5）。我们将去重后的条数除以总条数就得到离散性。这里是5/7。列中重复数据较多时，对应的数值较小，而重复数据较少时，数值相应较大。</p> <p>如果一个列的数据的重复性越低，那么这个列就越适合加索引。</p> <p>因为索引是需要起到筛选的作用。比如我们有个<code>where条件是where id = 1，如果数据重复性较高，那可能根据索引会返回100条数据，然后我们在根据其他where条件在100条数据中再筛选。

如果数据重复性较低，那可能就只返回1条数据，那之后的运算量明显小得多。

所以一个列的数据离散性越高，那这个列越适合添加索引。

我们可以用下面的语句得到某个列的离散性程度。

select count(distinct id)/count(*) form t_table;

3.1.3、前缀索引

前缀索引和后缀索引：

有些列的值比较长，比如一些备注日志信息也会记录在数据库当中，这类信息的长度往往比较长，如果我们需要对这类列加索引，那索引并不是索引字符串的全部长度。这时候我们就可以建立前缀索引，即对字符串的前面几位建立索引。

所以前缀索引就是建立范围更小索引，选择一个好前缀位数就能有一个更好的查询效率。

不过有一些缺点，就是这类索引无法应用到order by和group语句上。

Mysql没有后缀索引，如果非要实现后缀索引，那在数据存储时我们应该将数据反转，这样就能用前缀索引达到后缀索引的效果。后缀索引的一个经典应用就是邮箱，快速查询某种类型的邮箱。

选择前缀索引的位数：

这里的逻辑和列的离散性类似，我们需要看看字符串的前面几位的子字符串的离散性如何。比如对于下面的表，内容是电影票的相关信息，我们需要对order_note建立前缀索引。

来比较一下各个位的子字符串的离散性。

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

可以看出，前面几位的子字符串的离散程度较低，后面sel13开始就比较高，那我们可以根据实际情况，建立13~15位的前缀索引。

建立前缀索引SQL语句：

alter table order_exp add key(order_note(13));

3.1.2、只为搜索、排序和分组的列建索引

这个理由很简单，不解释了。

3.2、 多列索引的策略

3.2.1、离散性最高的列放前面

原因很简单，查询时根据定义复合索引时的列的顺序来查询的，离散性高的列放在前面的话，就能更早的将更多的数据排除在外。

3.2.2、三星索引

三星索引是一种策略。有三种条件，满足一条则索引获得一颗星，三颗星则是很好的索引。

三条策略分别是

索引将相关记录放在一起。

意思是查询需要的数据在索引树的叶子节点中连续或者足够靠近。举个例子，下面是某个索引的B+树。查询所需数据仅在叶节点的前两个范围内，即0000至a。这很明显，后面的片我们就没必要再去查询了，这无疑增加了效率。当所需数据分布在每个片上时，查询次数就会显著增加。

所以查询需要的数据在叶子节点上越连续，越窄就越好。

索引中的数据顺序与查找中的数据排序一致。

这容易理解，讲解联合索引中说过，B+树的排序顺序和索引中的数据一样，所以查询时的where的数据顺序越贴近索引中的顺序，就越能更好地利用B+树。

索引的列包含查询中的所有列。

这个可以避免回文操作，不多解释。

三星索引的权重：

一般来说第三个策略权重占到50%，之后是第一个策略27%， 第二个策略23%。

三星索引实例：

CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

我们创建以上的索引，那么对于下面的查询语句，这个索引就是三星索引。

select cno,fname from customer where lname='xx' and city ='yy' order by fname;

首先，查询条件中有lname=’xx’ and city =’yy’，这条件让我们这需要在lname=’xx’ and city =’yy’的那一片B+树的叶子节点中查询，让我们的查询变窄了很多，并且这部分的数据是连续的，因为B+树是先根据city排序，再根据lname查询。

另外，因为已经锁定lname=’xx’ and city =’yy’，所以这部分的数据是根据fname和cno排序。查询语句正好是根据`fname```排序，所以第二点也满足。

最后是查询的结果都包含正在索引中，不会有回文，第三点也满足，所以这个索引是三星索引。

The above is the detailed content of How to create a MySql index. For more information, please follow other related articles on the PHP Chinese website!