Mysql 索引简单介绍

懒洋洋QWQ
15 阅读5分钟

什么是索引

我的理解是大家可以把他理解成字典里面的 目录 我们可以通过字的读音快速找到我们需要查找的字的相关信息。那是不是说索引就这一种形式了，显然不是的。例如我们需要寻找成绩 score 在 120 ~ 140 的所有学生，这时候我们就需要用到另一种索引，我们称之为范围索引。

Mysql 索引分类

  • 主键索引: 在我们创建表的时候默认会给一个主键索引也称之为聚族索引
  • 唯一索引: 在该表的所有数据中只含有一份的数据，例如个人信息表中的身份证字段
  • 普通索引:
  • 组合索引：包含多个字段的索引，例如我们的业务需要通过 名字 + 年龄 来进行查询操作那么我们就可以通过组合索引
  • 全文索引: Full Text

主键索引

创建方式:

CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` varchar(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

唯一索引

CREATE UNIQUE INDEX indexName ON user(column)
或者
ALTER TABLE table_name ADD UNIQUE indexName ON (column)

普通索引

CREATE INDEX index_name ON user(column)
或者
ALTER TABLE user ADD INDEX index_name ON (column)

组合索引

组合索引，顾名思义，给 MySQL 多个字段同时加上索引，在使用时要遵循最左匹配原则

CREATE INDEX index_name ON user(column1,column2) -- 给 column1 和 column2 加上索引

全文索引

CREATE FULLTEXT INDEX index_column ON user(column)
或者
ALTER TABLE user ADD FULLTEXT index_column(column)

慢日志查询

通过 show variables like 'slow_query_log' 命令来查询 mysql 是否开启了日志

企业微信截图_17200761743408.png Value 值代表是否开启慢查询日志如何是 No 那么我们需要更改 my.ini 文件把他修改为 Yes

修改慢查询时间

通过该命令可以查询到慢查询时间的定义 默认是 10 s show variables like '%long_query_time%' 1720076651368.png

如果需要修改慢查询时间可以通过以下命令 set global long_query_time = 5

慢查询日志分析

# Time: 2024-07-04T09:16:23.194396Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 6.011569  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1664675770;
select sleep(6);

User@Host：执行该 SQL 的用户和慢查询 IP 地址

Query_time：语句执行时长

Lock_time：获取锁的时长

Rows_sent：MySQL 返回给客户端的行数

Rows_examined：MySQL 扫描行数

timestamp：表示慢 SQL 记录时的时间戳

select sleep(6)：则是慢查询 SQL

Explain 执行计划

explain命令大家应该很熟悉，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快，因为扫描的内容基数小。

字段含义
id一次查询过程中该条 select 语句的唯一标识
select_type查询类型，共包含四种 simple、primary、subquery、derived
table查询的是哪张表
partitions表的分区信息
type访问类型，分析性能主要通过该字段
possible_keys可能会用到的索引
key实际用到的索引
key_len索引里使用的字节数
ref这一列显示了在key列记录的索引中，表查找值所用到的列或常量
rowsMySQL 预估的扫描行
filteredMySQL 过滤后，满足条件记录数的比例
Extra展示了一些额外信息

索引优化

1、正确理解和计算索引字段的区分度，下面是计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。我们创建索引的时候，尽量选择区分度高的列作为索引。

selecttivity = count(distinct c_name)/count(*)
  1. 正确理解和计算前缀索引的字段长度，下面是判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。下买呢长度为6的时候是最佳状态。
SELECT
     count(DISTINCT LEFT(empname, 3)) / count(*) AS sel3,
     count(DISTINCT LEFT(empname, 4)) / count(*) AS sel4,
     count(DISTINCT LEFT(empname, 5)) / count(*) AS sel5,
     count(DISTINCT LEFT(empname, 6)) / count(*) AS sel6,
     count(DISTINCT LEFT(empname, 7)) / count(*) AS sel7
 FROM
     emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+

sel 值越大代表着区分度越高

  1. 联合索引注意最左匹配原则：按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如 depno=1 and empname>'' and job=1 ，如果建立(depno,empname,job)顺序的索引，empname 和 job是用不到索引的。

  2. 应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

  3. 正确判断是否使用联合索引（ 策略篇 联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

  4. 避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

  5. 避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

  6. 模糊查询'%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是'value%'是可以有效利用索引。

  7. 索引覆盖排序字段，这样可以减少排序步骤，提升查询效率。

  8. 尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

  9. 无需强制索引顺序，比如 建立(depno,empno,jobno)顺序的索引，你可以是 empno = 1 and jobno = 2 and depno = 8。因为MySQL的查询优化器会根据实际索引情况进行顺序优化，所以这边不强制顺序一致性。但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。

avatar
文章
阅读
粉丝