精心总结十三条建议,帮你创建更合适的MySQL索引

上篇文章讲到使用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索引

对于已经创建好的索引,我们还可以使用MySQL命令查看每个索引的区分度排名:

精心总结十三条建议,帮你创建更合适的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.

精心总结十三条建议,帮你创建更合适的MySQL索引

图中就是在(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,都需要更新大量索引文件,得不偿失。

知识点总结:

精心总结十三条建议,帮你创建更合适的MySQL索引

文章持续更新,可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。

Original: https://www.cnblogs.com/yidengjiagou/p/16538652.html
Author: 一灯架构
Title: 精心总结十三条建议,帮你创建更合适的MySQL索引

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505115/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 爬虫基础_正则表达式_补

    正则表达式 正则表达式是对字符串操作的一种逻辑公式,就是用事先定义好的一些特定字符、及这些特定字符的组合,组成一个 “规则字符串”,这个 “规则…

    数据库 2023年6月11日
    0188
  • 重返MySQL之MySQL基础

    重返MySQL之MySQL基础 本章详细介绍了,什么是数据库,常见的关系型数据库有哪些,什么是MySQL,及MySQL中DDL操作表,DML操作表记录。 1.0 数据库概述 1.1…

    数据库 2023年6月9日
    0208
  • 多商户商城系统功能拆解24讲-平台端分销会员

    多商户商城系统,也称为B2B2C(BBC)平台电商模式多商家商城系统。可以快速帮助企业搭建类似拼多多/京东/天猫/淘宝的综合商城。 多商户商城系统支持商家入驻加盟,同时满足平台自营…

    数据库 2023年6月14日
    0155
  • 解决:阿里云服务器添加安全组端口映射后仍无法访问

    “忘记了不该忘的人,我的内心满是伤痕…….” “呸,渣女,什么人?那是配置!配置!配置!” 问题: 阿里云服…

    数据库 2023年6月6日
    0123
  • 源码 | 为金融场景而生的数据类型:Numeric

    高日耀 资深数据库内核研发毕业于华中科技大学,喜欢研究主流数据库架构和源码,并长期从事分布式数据库内核研发。曾参与分布式 MPP 数据库 CirroData 内核开发(东方国信),…

    数据库 2023年5月24日
    0139
  • ​探秘 Web 水印技术

    Web 水印技术在信息安全和版权保护等领域有着广泛的应用,对防止信息泄露或知识产品被侵犯有重要意义。水印根据可见性可分为可见水印和不可见水印(盲水印),本文将分别予以介绍,带你探秘…

    数据库 2023年6月14日
    0147
  • Vue自定义组件实现v-model指令

    Tips: 本文所描述的Vue均默认是Vue2版本 在我们初次接触 Vue的时候,一定会了解到一个语法糖,那就是 v-model指令,它带给我们的第一印象就是它可以实现双向绑定 那…

    数据库 2023年6月11日
    0138
  • zabbix

    1. zabbix介绍 2. zabbix特点 3. zabbix配置文件 4. 部署zabbix zabbix介绍 zabbix是一个基于WEB界面的提供分布式系统监视以及网络监…

    数据库 2023年6月14日
    0122
  • yum安装Mysql8.0

    停止MySQL service mysqld status service mysqld stop 卸载已经安装过的MySQL 检查是否已经安装 rpm -qa|grep mysq…

    数据库 2023年6月9日
    0116
  • 编程过程中常用的英文单词

    引用 parameter和argument的区别 parameter和argument的区别 parameter是指函数定义中参数,而argument指的是函数调用时的实际参数。 …

    数据库 2023年6月9日
    0130
  • Vue 3.x基础

    Vue 3.x基础 模版 <template> // html </template> <script setup> // setup API …

    数据库 2023年6月11日
    0139
  • 人如蝼蚁,跌倒再起。

    本文来自博客园,作者:ukyo–BlackJesus,转载请注明原文链接:https://www.cnblogs.com/ukzq/p/16750141.html Or…

    数据库 2023年6月11日
    0146
  • 开源者的自我修养|为 ShardingSphere 贡献了千万行代码的程序员,后来当了 CEO

    当一个人处在持续地追求与锋锐地思索状态中,他将收获怎样的价值与自由? 有人说,敲过万行代码的程序员,可以写一份基础版入行指南,给初阶人群作入门参考。码过十万行代码的程序员,能写一本…

    数据库 2023年6月16日
    0164
  • Shell 中 [[]]和[]的区别

    多数情况下 []和 [[]]是可以通用的,两者的主要差异是: test或 []是符合POSIX标准的测试语句,兼容性更强,几乎可以运行在所有Shell解释器中,相比较而言 [[]]…

    数据库 2023年6月14日
    0136
  • SQL的约束

    概念:约束是作用于表中字段上的规则,用于限制存储表中的数据; 常见的约束分类:约束 描述 关键字 非空约束 限制该字段的数据不能为null not null 唯一约束 保证该字段的…

    数据库 2023年6月16日
    0132
  • [LeetCode]21. 合并两个有序链表

    将两个升序链表合并为一个新的 升序 链表并返回。新链表是通过拼接给定的两个链表的所有节点组成的。 示例: 输入:1->2->4, 1->3->4输出:1-&…

    数据库 2023年6月9日
    0159
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球