上篇文章讲到使用MySQL的Explain命令可以分析SQL性能瓶颈,优化SQL查询,以及查看是否用到了索引。
我们都知道创建索引可以提高查询效率,但是究竟如何创建索引呢?
[En]
We all know that creating an index can improve query efficiency, but how exactly do you create an index?
哪些字段适合创建索引?
哪些字段不适合创建索引?
[En]
Which fields are not suitable for index creation?
本文将帮助您了解如何创建适当的数据库索引。
[En]
This article is with you to learn how to create an appropriate database index.
1. MySQL索引的分类
在创建索引之前了解一下MySQL有哪些索引,然后我们才能选择合适的索引。
常用索引有通用索引、唯一索引、主键索引、联合索引、全文索引等。
[En]
Common indexes are general index, unique index, primary key index, joint index, full-text index and so on.
普通索引
一般指数是最基本的指数,没有任何限制。
[En]
A general index is the most basic index, without any restrictions.
您可以使用以下命令创建普通索引:
[En]
You can use the command to create a normal index:
ALTER TABLE table_name
ADD INDEX index_name (column
);
唯一索引
与普通索引不同,唯一索引的列值必须唯一,允许为null。
创建方式是这样的:
ALTER TABLE table_name
ADD UNIQUE index_name (column
);
主键索引
主键索引是一种特殊的唯一索引,并且一张表只有一个主键,不允许为null。
创建方式是这样的:
ALTER TABLE table_name
ADD PRIMARY KEY (column
);
联合索引
联合索引同时在多个字段上创建索引,从而提高了查询效率。
[En]
A federated index creates an index on multiple fields at the same time, which makes the query more efficient.
创建方式是这样的:
ALTER TABLE table_name
ADD INDEX index_name (column1
, column2
, column3
);
全文索引
全文索引主要用于匹配字符串文本中的关键字。
[En]
Full-text indexing is mainly used to match keywords in string text.
当需要字符串中是否包含关键字的时候,我们一般用like,如果是以%开头的时候,则无法用到索引,这时候就可以使用全文索引了。
创建方式是这样的:
ALTER TABLE table_name
ADD FULLTEXT (column
);
2. 哪些字段适合创建索引?
我总结了有以下几条:
2.1 频繁查询的字段适合创建索引
表中总是有热的和冷的字段,很明显,频繁使用的字段更适合对其进行索引。
[En]
There are always hot and cold fields in a table, and it’s clear that frequently used fields are more suitable for indexing it.
2.2 在where和on条件出现的字段优先创建索引
为什么不是在select后面出现的字段优先创建索引?
因为查询SQL会先匹配on和where条件的字段,具体的匹配顺序是这样的:
from > on > join > where > group by > having > select > distinct > order by > limit
2.3 区分度高的字段适合创建索引
例如,对于用户表,生日比性别更有区别,更适合编制索引。
[En]
For example, for a user table, birthdays are more differentiated than gender and are more suitable for indexing.
您可以使用以下方法手动统计每个字段的差异。该值越大,差异化程度越高:
[En]
You can use the following method to manually count the differentiation of each field. The higher the value, the higher the differentiation:
select
count(distinct birthday)/count(*),
count(distinct gender)/count(*)
from user;
对于已经创建好的索引,我们还可以使用MySQL命令查看每个索引的区分度排名:
图中 Cardinality列表示索引的区分度排名,也被称为基数。
2.4 有序的字段适合创建索引
有序的字段在插入数据库的过程中,仍能保持B+树的索引结构,不需要频繁更新索引文件,性能更好。
3. 哪些字段不合适创建索引?
在说明了哪些字段适合创建索引之后,还有一些字段不适合创建索引。
[En]
After saying which fields are suitable for creating an index, there are fields that are not suitable for creating an index.
3.1 区分度低的字段不适合创建索引。
我只是说,用户表中的性别区别较小,因此不像生日字段那样适合创建索引。
[En]
I just said that the gender in the user table is less differentiated, so it is not as suitable for creating an index as the birthday field.
3.2 频繁更新的字段不适合创建索引
更新字段的过程中,需要维护B+树结构,会频繁更新索引文件,降低SQL性能。
3.3 过长的字段不适合创建索引
过长的字段会占用更多空间,不适合创建索引。
[En]
Fields that are too long take up more space and are not suitable for index creation.
3.4 无序的字段不适合创建索引
无序的字段在插入数据库的过程中,为了维护B+树索引结构,需要频繁更新索引文件,性能较差。
4. 创建索引的其他注意事项
4.1 优先使用联合索引
在查询时,联邦索引可以比普通索引更准确地匹配所需的数据。
[En]
When querying, the federated index can match the required data more accurately than the ordinary index.
图中就是在(age,name)两个字段上建立的联合索引,在B+树中的存储结构。
可以看出,是先age排序,age相等的数据,再按name排序。
对于这条查询SQL:
select age,name from user where age=18 and name='李四';
联合索引只需一次就可以查到所需数据,如果我们只在age字段上建立索引,会先匹配到age=18的三条数据,然后再逐个遍历,效率更差,所以平时应该优先使用联合索引。
4.2 使用联合索引时,区分度的字段放前面
这将减少查询次数,并更快地匹配所需数据。
[En]
This reduces the number of queries and matches the required data more quickly.
4.3 过长字符串可以使用前缀索引
例如,在匹配用户地址时,如果乡镇已经能区分大部分用户,就不一定要准确到邻里。
[En]
For example, when matching user addresses, if the township can already distinguish most of the users, it is not necessary to be accurate to the neighborhood.
创建普通索引时,指定索引长度,即可创建前缀索引。
[En]
When creating a normal index, specify the index length, and you can create a prefix index.
ALTER TABLE user
ADD INDEX idx_address (address(3));
4.4 值唯一的字段,使用唯一索引
使用唯一索引,可以避免程序bug导致产生重复数据。
4.5 排序和分组字段也尽量创建索引
在order by和group by中的字段也尽量创建索引,避免使用文件排序,可以使用索引排序提供性能。
4.6 避免创建过多索引
索引好用,适度即可。创建过多的索引,会占用更多存储空间,也会严重影响SQL性能,每次更新SQL,都需要更新大量索引文件,得不偿失。
知识点总结:
文章持续更新,可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。
Original: https://www.cnblogs.com/yidengjiagou/p/16538652.html
Author: 一灯架构
Title: 精心总结十三条建议,帮你创建更合适的MySQL索引
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505115/
转载文章受原作者版权保护。转载请注明原作者出处!