MySQL性能优化之索引使用与分析
文章目录
1 查看执行频率
- 查看当前数据库中
insert、updata、delete、select的访问频次
show global status like 'Com_______';
可以看到查询次数是最多的。
2 慢查询日志
mysql慢查询日志默认未开启，需要在配置中开启
# 慢查询日志1开启2关闭
slow_query_log=1
# 慢查询时间限制为2秒
long_query_time=2
3 profile详情
3.1 profile开启
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前mysql是否支持profile操作。
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set (0.05 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.04 sec)
mysql> set profiling = 1;
Query OK, 0 rows affected (0.02 sec)
3.2 profile使用
- 执行一些业务SQL操作，然后使用一下命令查看执行耗时
// 查看每一条SQL的耗时基本情况
show profiles;
// 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query 4;
// 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query 4;
3.2.1 show profiles
- 查看每一条
SQL的耗时基本情况。 mysql> show profiles;
mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------------------------------+
| 1 | 0.00038750 | show databases |
| 2 | 0.01756175 | use yf |
| 3 | 0.00055200 | select * from sys_user |
| 4 | 0.00047775 | SELECT config_id,config_name,config_key,config_value,config_type FROM sys_config |
+----------+------------+----------------------------------------------------------------------------------+
4 rows in set (0.10 sec)
这个查询结果是一个数据表，显示了关于数据库查询的一些信息。每一行代表一个查询，包含三个字段：Query_ID、Duration 和 Query。
Query_ID：查询的标识符，用于唯一标识每一个查询。
Duration：查询的执行时间，以某种时间单位（可能是秒）表示。这表示查询完成所需的时间。
Query：执行的SQL查询语句。
3.2.2 show profile for query 4
- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query 4;
mysql> show profile for query 4;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000075 |
| checking permissions | 0.000007 |
| Opening tables | 0.000209 |
| init | 0.000017 |
| System lock | 0.000008 |
| optimizing | 0.000004 |
| statistics | 0.000010 |
| preparing | 0.000009 |
| executing | 0.000004 |
| Sending data | 0.000068 |
| end | 0.000004 |
| query end | 0.000007 |
| closing tables | 0.000006 |
| freeing items | 0.000037 |
| cleaning up | 0.000015 |
+----------------------+----------+
15 rows in set (0.10 sec)
这个查询结果展示了一个数据库查询或操作的不同阶段及其相应的持续时间。从结果来看，这很可能是MySQL或其他关系型数据库在执行一个查询时内部各个阶段的耗时情况。下面是对每个阶段的解释：
starting：查询开始阶段，可能涉及一些初始化操作。
checking permissions：检查执行查询的用户是否有足够的权限。
Opening tables：打开查询中涉及的表。
init：初始化查询的某些部分。
System lock：获取系统锁，以确保查询在执行过程中不会被其他操作干扰。
optimizing：优化查询，例如选择最佳的索引或查询计划。
statistics：收集与查询相关的统计信息。
preparing：准备查询的执行，例如解析SQL语句。
executing：实际执行查询。
Sending data：将查询结果发送给客户端。
end：查询执行结束阶段。
query end：查询完全结束，包括所有后续清理工作。
closing tables：关闭之前打开的表。
freeing items：释放查询执行过程中使用的资源。
cleaning up：执行最后的清理工作，如释放内存或文件句柄。
3.2.3 show profile cpu for query 4
- 查看指定
query_id的SQL语句CPU的使用情况
show profile cpu for query 4;
mysql> show profile cpu for query 4;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000075 | 0.000067 | 0.000000 |
| checking permissions | 0.000007 | 0.000007 | 0.000000 |
| Opening tables | 0.000209 | 0.000211 | 0.000000 |
| init | 0.000017 | 0.000014 | 0.000000 |
| System lock | 0.000008 | 0.000008 | 0.000000 |
| optimizing | 0.000004 | 0.000004 | 0.000000 |
| statistics | 0.000010 | 0.000010 | 0.000000 |
| preparing | 0.000009 | 0.000009 | 0.000000 |
| executing | 0.000004 | 0.000003 | 0.000000 |
| Sending data | 0.000068 | 0.000068 | 0.000000 |
| end | 0.000004 | 0.000004 | 0.000000 |
| query end | 0.000007 | 0.000007 | 0.000000 |
| closing tables | 0.000006 | 0.000006 | 0.000000 |
| freeing items | 0.000037 | 0.000037 | 0.000000 |
| cleaning up | 0.000015 | 0.000015 | 0.000000 |
+----------------------+----------+----------+------------+
15 rows in set (0.09 sec)
这个查询结果展示了MySQL在执行特定查询（在这个例子中是查询ID为4的查询）时，各个阶段的CPU使用情况。这是通过MySQL的查询性能剖析（profiling）功能得到的。在MySQL中，你可以使用SHOW PROFILE语句来查看查询执行的详细信息，包括每个阶段的耗时以及对应的CPU使用情况。
这里的列解释如下：
Status：这是查询执行过程中的一个阶段或状态。
Duration：这个状态或阶段所花费的时间（单位通常是秒）。
CPU_user：用户模式下的CPU时间，即执行用户空间代码所消耗的CPU时间。
CPU_system：内核模式下的CPU时间，即执行内核空间代码所消耗的CPU时间。
每一行都代表查询执行过程中的一个阶段，从查询开始到结束。这些阶段通常包括检查权限、打开表、初始化、优化查询、准备执行、执行查询、发送数据、结束查询、关闭表、释放资源以及清理工作等。
4 explain执行计划
- 示例
EXPLAIN SELECT * FROM sys_config;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | sys_config | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.05 sec)
- 字段含义
id: 查询的标识符。这个数字表示查询的执行顺序。在多表查询中，你会看到多个不同的id，并可能包含DERIVED、SUBQUERY、UNION等标识。在这个例子中，只有一个查询，所以id为1。
select_type: 查询的类型。对于简单的查询，这通常是SIMPLE。在更复杂的查询中，例如子查询或联合查询，你可能会看到其他值，如SUBQUERY、UNION等。
table: 查询涉及的表名。在这里，查询涉及的表是sys_config。
partitions: 查询涉及的分区。如果表是分区的，并且查询涉及特定的分区，这里会显示分区的名称。在这个例子中，没有分区，所以显示为NULL。
type: 访问类型。这是表示MySQL如何连接表的关键信息。ALL意味着MySQL对全表进行了扫描，通常这是一个性能不佳的标志。其他常见的值包括index（全索引扫描）、range（范围扫描）、ref（基于非唯一索引或唯一索引前缀的查找）、eq_ref（基于唯一索引的查找）等。
possible_keys: 可能使用的索引。MySQL会考虑使用这些索引来优化查询，但并不意味着它真的使用了这些索引。在这个例子中，没有可用的索引，所以显示为NULL。
key: 实际使用的索引。在这个例子中，没有使用任何索引，所以显示为NULL。
key_len: 使用的索引的长度。这可以帮助你了解索引的大小和可能的影响。在这里，没有使用索引，所以显示为NULL。
ref: 显示哪些列或常量被用作索引查找的条件。在这个例子中，没有使用索引，所以显示为NULL。
rows: MySQL估计为了找到所需的行而必须检查的行数。这是一个近似值，但可以帮助你了解查询的效率。
filtered: 表示返回结果的行数占需要检查的行数的百分比。
Extra: 包含MySQL解决查询的额外信息。例如，它可能会告诉你MySQL是否使用了文件排序或临时表。在这个例子中，没有额外的信息，所以显示为NULL。
基于这个EXPLAIN结果，你可以看到查询对sys_config表进行了全表扫描（type为ALL），并且没有使用任何索引。这通常意味着查询性能可能不是最优的。为了提高性能，你可以考虑添加适当的索引，特别是针对经常用于查询条件的列。
5 索引使用
5.1 索引语法
- 索引类型
UNIQUE索引：
含义：unique表示唯一约束，即所有记录中字段的值不能重复出现。
用途：当某个字段的值需要保证唯一性时，可以为该字段设置UNIQUE索引。例如，身份证号或邮箱地址等需要唯一性的字段。
特点：一个表中可以有多个UNIQUE索引，并且设置UNIQUE约束的列允许有空值，但只能有一个空值。
FULLTEXT索引：
含义：fulltext表示全文搜索的索引。
用途：它主要用于在大量文本数据中执行全文搜索操作，特别适用于搜索长篇文章或文档。
特点：FULLTEXT索引仅可用于MyISAM表，并且只能在CHAR、VARCHAR或TEXT列上创建。对于大容量的数据表，生成全文索引是一个非常消耗时间和硬盘空间的操作。因此，在较短的文本上使用FULLTEXT索引可能不是最佳选择，普通INDEX通常更为合适。 - 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
- 查看索引
SHOW INDEX FROM table_name;
- 删除索引
DROP INDEX index_name ON table_name;
- 起名规则：idx_表名_字段名1_字段名2
- 示例
CREATE INDEX idx_level_type ON xy_vip(vip_level,project_type);
mysql> SHOW INDEX FROM xy_vip;
+--------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| xy_vip | 0 | PRIMARY | 1 | vip_id | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
| xy_vip | 1 | idx_level_type | 1 | vip_level | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
| xy_vip | 1 | idx_level_type | 2 | project_type | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.04 sec)
- 各个字段解释
Table: 表名，这里是xy_vip。
Non_unique: 如果索引不能包含重复项，则为0；如果可以包含重复项，则为1。对于主键，这个值通常是0，因为主键必须是唯一的。
Key_name: 索引的名称。这里，主键索引的名称是PRIMARY。
Seq_in_index: 索引中的列序号。这是对于复合索引而言的，对于单一列的索引，这个值通常是1。
Column_name: 索引中的列名。这里是vip_id，意味着vip_id列是主键索引的一部分。
Collation: 列在索引中的排序顺序。A表示升序。
Cardinality: 索引中唯一值的数量。这个数字可以用来估计索引的选择性，从而帮助判断索引的效率。
Sub_part: 对于BLOB和TEXT列，这是索引的前缀长度。NULL表示没有使用前缀索引。
Packed: 指示索引是如何压缩的。NULL表示没有使用压缩。
Null: 如果列可以包含NULL值，则为'YES'；否则为''。
Index_type: 使用的索引类型。这里是BTREE，表示使用的是B树索引。
Comment: 索引的注释。这里是空的。
Index_comment: 关于索引的额外注释。这里是空的。
Visible: 表示索引是否可见。YES表示索引是可见的。
Expression: 对于函数式索引，这里会包含生成索引值的表达式。NULL表示不是函数式索引。
在优化数据库性能时，以下字段的信息通常是比较重要的：
Non_unique: 帮助你了解索引是否允许重复值。
Cardinality: 帮助你了解索引的选择性，从而判断索引是否有效。
Index_type: 了解使用的索引类型可以帮助你确定是否需要进行调整或优化。
Column_name: 了解哪些列被索引了，这对于理解查询性能和优化策略至关重要。
5.2 索引使用规则
5.2.1 最左前缀法则
- 联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，
索引将部分失效（后面的字段索引失效）。
SELECT * FROM xy_vip WHERE vip_level = '1' and project_type = '3' and unique_identification = '8_5FE2.'
SELECT * FROM xy_vip WHERE vip_level = '1' and project_type = '3'
SELECT * FROM xy_vip WHERE vip_level = '1'
SELECT * FROM xy_vip WHERE project_type = '3' and unique_identification = '8_5FE2.'
SELECT * FROM xy_vip WHERE unique_identification = '8_5FE2.'
- 使用
*会发生，回表操作。不建议。 - 建议select 结果与索引一致。
EXPLAIN SELECT vip_level,project_type,unique_identification FROM xy_vip WHERE vip_level = '1' and project_type = '3' and unique_identification = 'ZFM3LWYRDefault5.'
5.2.2 索引列运算
- 不要在索引列上进行运算操作，
索引会失效。
EXPLAIN SELECT * FROM xy_vip WHERE SUBSTRING(unique_identification,1,4) = 'sdg'
5.2.3 字符串不加引号
- 字符串类型字段使用时，不加引号，
索引失效。
EXPLAIN SELECT * FROM xy_vip WHERE vip_level = 1
5.2.4 模糊查询
- 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，
索引失效。
-- 有效
EXPLAIN SELECT * FROM xy_vip WHERE vip_level LIKE '1%'
-- 无效
EXPLAIN SELECT * FROM xy_vip WHERE vip_level LIKE '%1'
-- 无效
EXPLAIN SELECT * FROM xy_vip WHERE vip_level LIKE '%1%'
5.2.5 or连接的条件
- 用
or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。or前后的字段都要建立索引。
CREATE INDEX idx_level ON xy_vip(vip_level);
-- 索引不生效
EXPLAIN SELECT * FROM xy_vip WHERE vip_level = '1' OR project_type = '3'
CREATE INDEX idx_type ON xy_vip(project_type);
-- or后的字段也建立索引，索引生效
EXPLAIN SELECT * FROM xy_vip WHERE vip_level = '1' OR project_type = '3'
5.2.6 数据分布影响
- 如果MySQL评估使用索引比全表更慢，则
不使用索引。
5.3 SQL提示
SQL提示，是加入一些认为的提示，来达到优化操作的目的。
- use index
EXPLAIN SELECT * FROM xy_vip USE INDEX(idx_level_type_identification) WHERE vip_level = '1'
- ignort index
EXPLAIN SELECT * FROM xy_vip IGNORE INDEX(idx_level_type_identification,idx_level) WHERE vip_level = '1'
- force index
EXPLAIN SELECT * FROM xy_vip FORCE INDEX(idx_level) WHERE vip_level = '1'
5.4 覆盖索引
- 尽量使用覆盖索引（查询使用的索引在返回的列，在该索引中已经全部找到），减少select * 。
- using index confition：查找使用了索引，但是需要回表找除索引之外的数据。
- using index：查找使用了索引，索引中已经包含了需要返回的列。
5.5 前缀索引
- 当字段类型为字符串(
varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘I0，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。 - 语法
CREATE INDEX idx_xxx ON table_name(column(n));
- 前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
SELECT count(DISTINCT unique_identification) / count(*) FROM xy_vip;
SELECT count(DISTINCT substring(unique_identification,1,5)) / count(*) FROM xy_vip;
- 前缀索引，一般会回表。
5.6 索引设计原则
- 针对于数据量较大（单表超过
一百万），且查询比较频繁的表建立索引。 - 针对于常作为查询条件(
where)、排序 (order by)、分组 (group by) 操作的字段建立索引。 - 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立
前缀索引。 - 尽量使用
联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 - 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储
NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
