Mysql 索引的分类

最新推荐文章于 2024-08-17 15:13:41 发布

Sword723

最新推荐文章于 2024-08-17 15:13:41 发布

阅读量290

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_57667214/article/details/126254918

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1.索引的分类

在 MySQL 数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建 , 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

而在在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引

二级索引

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取

数据的方式，就称之为回表查询。

2.索引语法

1.创建索引 create 索引类型 index 索引名 on table_name(字段名);

2.查看索引 show index from table_name;

3.删除索引 drop index 索引名 on table_name;

4.创建联合索引 create 索引类型 index 索引名 on table_name(字段名1,字段名2,字段名3...);

3.SQL性能分析工具

1 SQL 执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信

息。通过如下指令，可以查看当前数据库的 INSERT 、 UPDATE 、 DELETE 、 SELECT 的访问频次：

-- session 是查看当前会话 ;

-- global 是查询全局数据 ;

SHOW GLOBAL STATUS LIKE 'Com_______' ;

2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（ long_query_time ，单位：秒，默认 10 秒）的所有

SQL 语句的日志。

3 profile 详情

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。

4 explain

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

Explain 执行计划中各个字段的含义 :

字段	含义
id	select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序 (id相同，执行顺序从上到下； id 不同，值越大，越先执行 ) 。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接或者子查询）、PRIMARY （主查询，即外层的查询）、 UNION（ UNION 中的第二个或者后面的查询语句）、 SUBQUERY （ SELECT/WHERE 之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为 NULL 、 system 、 const 、eq_ref、 ref 、 range 、 index 、 all 。
possible_key	显示可能应用在这张表上的索引，一个或多个
key	实际使用的索引，如果为 NULL ，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows	MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

4.索引失效情况

1.最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效( 后面的字段索引失效 ) 。

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是

第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

2.范围查询

联合索引中，出现范围查询 (>,<) ，范围查询右侧的列索引失效。

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询

3.索引列运算

不要在索引列上进行运算操作，索引将失效。

4. 字符串不加引号

字符串类型字段使用时，不加引号，数据库存在隐式类型转换，索引将失效。

5. 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

6. or 连接条件

用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

7.数据分布影响

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

5.SQL提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

1.use index

建议MySQL 使用哪一个索引完成此次查询（仅仅是建议， mysql 内部还会再次进

行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = ' 软件工

程 ' ;

2.ignore index

忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = ' 软件工

程 ' ;

3.force index

强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = ' 软件工

程 ' ;

6.覆盖索引

尽量使用覆盖索引，减少 select * 。那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并

且需要返回的列，在该索引中已经全部能够找到。

SQL执行计划最后一个字段Extra

Extra	含义
Using where; Using Index	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据