Mysql 索引_mysql可命中索引的关键字有哪些-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_38144883/article/details/146535178

Mysql 的索引

在MySQL中，有多种类型的索引可以用来优化查询性能，提高数据库操作的效率。以下是一些常见的MySQL索引类型：

B-Tree索引：
- 这是最常用的索引类型，适用于全键值、键值范围或键值前缀查找。
- 它能够保持数据排序，使得范围查询非常高效。
- 适用于CHAR、VARCHAR、DATETIME、DATE、BLOB、TEXT、INT等数据类型。
哈希索引：
- 基于哈希表实现，只支持等值比较查询（即“=”或“IN”操作）。
- 适用于等值查找，但不支持范围查询。
- 适用于等值查询较多的场景，如记忆表（memory tables）。
全文索引：
- 用于对文本内容进行搜索。
- 使用FULLTEXT关键字创建，支持复杂的文本搜索操作，如模糊匹配。
- 适用于CHAR、VARCHAR或TEXT列。
空间索引：
- 用于地理空间数据类型，例如地理信息系统（GIS）数据。
- 使用SPATIAL关键字创建，支持空间数据的快速检索。
- 适用于GEOMETRY、POINT、LINESTRING等空间数据类型。
复合索引（组合索引）：
- 可以包含多个列，用于优化基于这些列的查询。
- 适用于经常一起查询的多个列，可以提高多列条件查询的效率。
- 例如，在用户表中根据用户名和邮箱地址进行搜索。
唯一索引：
- 保证索引列或列组合中的每个值都是唯一的。
- 使用UNIQUE关键字创建，可以防止数据的重复插入。
- 类似于普通索引，但具有唯一性约束。
倒排索引：
- 主要用于搜索引擎，虽然不是MySQL的内置类型，但理解其在MySQL全文索引中的应用很重要。
- 它通过存储单词和包含这些单词的文档列表来实现快速的全文搜索。

创建索引的示例：

# B-Tree 索引
CREATE　INDEX　idx_name ON table_name(column_name);

# 全文索引
CREATE FULLTEXT INDEX idx_fulltext ON table_name(column_name);

# 唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

# 符合索引
CREATE INDEX idx_composite ON table_name(column1, column2);

选择合适的索引类型和策略对于优化数据库的性能至关重要。在设计数据库和编写查询时，应考虑数据的访问模式和查询需求来选择合适的索引类型。同事，也要注意过度的索引可能回降低写的操作性能（如 INSERT、UPDATE、DELETE），因为他们需要更新更多的索引结构。因此，平衡操作和写操作的性能时设计索引时的一个重要考虑因素。

如何判断索引命中？

使用 explain 语句：执行 explain 语句可以查看 mysql 查询优化器去和处理 sql 查询，并了解是否使用了索引。如果 explain 结果中的 key 字段显示索引名称，则表示查询命中索引。
查看查询的执行时间：通过对比有索引和无索引的查询执行时间，可以判断索引是否命中。如果使用索引的查询时间明显小于无索引的查询，则说明命中了。
启用慢查询日志：在 mysql 中启用慢查询日志，记录执行时间超过设定的阙值的SQL语句。通过这些日志，可以发现哪些查询没有命中索引。

优化索引的策略

创建组合索引：如果经常需要在多个字段上过滤数据，可以考虑创建组合索引。例如：idx_age_name在（age,name)上创建。
使用覆盖索引：覆盖索引是指在索引中包含查询所需要的所有字段，这样查询可以直接使用索引中的数据，无需回表查询。

如何开启 MYSQL 的码查询日志？

开启mysql的慢查询日志可以帮助你识别出那些执行时间较长的查询，这对于优化数据库性能非常有帮助。下面是在mysql中开启慢查询日志的基本步骤：

配置慢查询日志
首先，需要修改MYSQL的配置文件（通常是 my.cnf 或 my.ini,具体取决于操作系统和Mysql的版本）

对于Linux系统：

找到 Mysql 的配置文件。通常位于 /etc/mysql/my.cnf 或 /etc/my.cnf;
使用文本编辑器打开配置文件，sudo nano /etc/mysql/my.cnf;
在 [mysqld] 部分调价或修改以下行：
```
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
```
slow_query_log = 1 启用慢查询日志。

slow_query_log_file 指定慢查询日志文件的路径。

long_query_time 设置记录慢查询的阈值，单位为秒，这里设置为2秒，意味着执行时间超过2秒的会被记录。
保存并关闭文件。
重启 MYSQL服务以应用更改。例如，在Ubuntu 上可以使用 sudo systemctl restart mysql.

对于 windows 系统

找到 MYSQL 的配置文件，通常位于MYSQL 安装目录下 my.ini
打开 my.ini文件，在[mysqld]部分添加或修改上述相同的行。
保存关闭文件。
重启 mysql 服务。

直接通过SQL命令开启慢查询日志（临时）

如果只是临时开启慢查询日志，可以通过一下SQL命令实现：

SET GLOBAL slow_query_log='ON';
SET GLOBAL slow_query_log_file = '/path/to/your/mysql-slow.log';
SET GLOBAL long_query_time=2;

这些命令只在当前 Mysql 会话中有效，重启服务后不会保留设置。要是设置永久生效，你需要修改配置文件并重启服务。