【MySQL速通篇004】这可能最详细的关于MySQL基础知识点的文章了

🍁前言

👑作者主页：CSDN丨博客园

🏆学习交流：在下周周ovoの社区

💎这篇8000多字的博客也是花了我比较久的时间，基本覆盖很大一部分的MySQL知识点，可以说是非常的详细，感谢各位的支持，对于文章中的一些语法，概念，例子等错误，欢迎并感谢各位读者的指出

文章目录

🍁一、索引种类

🍀1.1、普通索引

**

create index index_name on table(column)；

**

作用：

加速查找

🍀1.2、主键索引

一般是在建表时指定primary key(column)

作用：

加速查找，不能重复，不能为空

主键索引是数据库中的一种索引类型，它用于唯一标识每个表中的记录。主键索引通常由一个或多个列组成，这些列具有唯一性和非空性约束，因此可以确保表中每个记录都有唯一的标识。

在数据库中，主键索引的作用是提高数据查询和检索的效率。如果没有主键索引，查询数据库中的记录需要逐个扫描整个表，这样会导致查询时间变得极其缓慢。而使用主键索引，数据库可以快速定位到特定的记录，大大提高了查询效率和速度。

在设计数据库时，选择合适的列作为主键索引非常重要。通常情况下，主键应该是短小、简单且稳定的，并且不应该随着时间而改变。同时，为了最大程度地提高查询效率，主键列的数据类型应该尽可能小，以便在查询时占用更少的存储空间。

虽然主键索引可以显著提高数据库查询性能，但在某些情况下也会带来额外的开销。例如，在更新主键列值或插入新记录时，主键索引需要更新索引结构，这可能会导致性能损失。因此，在设计数据库时，需要综合考虑数据的访问模式、数据量、应用程序需求等因素来决定是否需要使用主键索引。

🍀1.3、唯一索引

create unique index index_name on table(column)；

或者创建表时指定unique index_name column

作用：

加速查找，与主键类似，但是可以为空，不能重复

🍀1.4、联合索引(包括联合唯一索引)

在多个字段（列）上创建索引（也就是由多列组成索引），遵循最左前缀原则

**

alter table t add index index_name(a,b,c);

**

作用：

加速查找。联合索引可以有（联合主键索引，联合唯一索引，联合普通索引）当然括号内的叫法有些不是很官方

🍀1.5、覆盖索引

表中的id和email列创建了索引

select email from info_test where email = '[email protected]'

覆盖索引（Covering Index）是指，当数据库查询语句可以完全从索引中获取所需的数据结果时，就称为使用了覆盖索引。

通常来说，对于一个包含多个列的表，在执行查询操作时，需要搜索整个表，并根据查询条件过滤出符合条件的记录。这种搜索可能会非常耗时，特别是在处理大量数据时。

而如果使用了覆盖索引，即建立一个包含所有需要返回结果列的复合索引，那么查询操作只需要在这个索引上进行，而不用再去搜索整个表。这样可以极大地提高查询速度和性能。

使用覆盖索引的好处不仅在于它减少了查询操作所需的时间，还减轻了系统I/O的负担。因为索引通常比表小得多，所以一旦找到了匹配的索引值，就可以直接从磁盘读取索引所包含的所有列，而无需再访问原始数据表，从而节省了大量的磁盘I/O操作。

需要注意的是，覆盖索引只有在查询的 SELECT 列都在索引中时才能发挥作用。如果 SELECT 列中包含了不在索引中的列，则无法使用覆盖索引，查询操作还是需要访问原始数据表，这样会降低查询效率。

🍀1.6、索引合并

【把多个单列索引合并使用】索引合并是指一个查询语句需要多个索引才能满足查询条件，这时数据库会使用索引合并算法来优化查询性能。索引合并可以将多个索引的结果合并成一个结果集，从而避免多次扫描索引和数据行。通过索引合并，可以提高查询性能并降低系统开销。

例子：

select email from info_test where email = '[email protected]' and id=620783；

🍀1.7、删除索引

drop index 索引名称 on 表名

🍁二、索引实现方式

🍀2.1、hash索引

哈希索引就是基于哈希算法，对于每一行数据，数据库存储引擎会对所有索引列都通过哈希算法去计算一个哈希码，然后将这个哈希码存储在哈希索引中，由于使用的是哈希算法，所以使用哈希索引就会存在两个弊端：

1、哈希算法计算出来的哈希值可能存在哈希冲突

2、由于计算出来的是个值所以无法进行范围查询使用hash索引时会自动创建一张hash索引表，索引表与数据表里的数据位置是不一致的

特别注意：对取单个词的话比较快，但是对范围查找比较慢

🍀2.2、btree索引

1. 搜索速度快：btree索引具有非常高效的搜索速度，能够快速地查找并返回相关数据。
2. 空间利用率高：btree索引能够在占用很少存储空间的情况下，存储大量数据。
3. 范围查询效率高：btree索引在进行范围查询时能够非常高效地处理，大大提高了数据查询的效率。
4. 支持数据排序：btree索引能够对存储在其中的数据进行排序，提供了更加灵活的查询和排序功能。
5. 并发控制优秀：btree索引的并发控制是非常优秀的，能够避免并发对索引的影响，确保数据的完整性和一致性。
6. 适合多种数据类型：btree索引能够适应多种数据类型，不同类型的数据都能够使用btree索引进行索引和查询

🍀2.3、创建索引的不利方面

1. 空间占用：索引需要占用额外的磁盘空间，当表数据增加时，索引也会相应增加，增加了存储需求。
2. 维护开销：索引需要维护，每次增删改查都会更新索引，增加了维护开销。
3. 性能下降：虽然索引可以加速某些查询，但是对于表的其他操作，如数据的增加、删除和更新等，由于需要维护索引，所以性能反而会下降。
4. 不必要的索引：有些索引并不是必须的，因为它们可能永远不会被查询，但是却会影响到数据的插入和更新操作的速度。
5. 索引失效：当数据分布不均时，某些索引可能会失效，即使查询中使用了这些索引，也不能达到加速查询的目的。此时，需要重新评估索引的使用情况。

从上面有一点值得思考，我们创建索引就是用来使用的，我们要懂得如何命中索引，让索引为我们工作

🍁三、无法命中索引情况

🍀3.1、模糊查询

如果查询中使用LIKE语句以通配符（%或_）开头，则无法使用索引

但是实际项目中一般会使用第三方模块进行模糊查询

🍀3.2、组合索引的最左前缀匹配

CREATE UNIQUE INDEX 组合索引名称 ON 表名(列名1, 列名2, ...) 

组合索引的最左前缀原则表示，在多个列组成的索引中，如果要使用索引，必须使用索引的最左前缀列，也就是说，只有在满足最左前缀列的搜索条件下，索引才会被使用。

举例来说，如果有如下表格：
idnameagegender1Tom21Male2Jack22Male3Lily23Female4Amy22Female5Bob21Male
如果创建一个组合索引（name, age, gender），那么满足以下查询条件时，索引会被使用：

• WHERE name = 'Tom'
• WHERE name = 'Tom' AND age = 21
• WHERE name = 'Tom' AND age = 21 AND gender = 'Male'

但是如果只使用其中部分列作为搜索条件时，索引不会被使用，例如：

• WHERE age = 21 （没有使用最左前缀列name）
• WHERE gender = 'Male' （没有使用最左前缀列name和age）
• WHERE age = 21 AND gender = 'Male'

注意：组合索引的效率 > 索引合并

🍀3.3、使用了函数或表达式

如果查询条件中使用了函数或表达式，MySQL就无法直接使用索引来匹配记录。例如，在一个查询中，如果使用了以下条件：

WHERE YEAR(date)=2019

，MySQL将无法使用date列上的索引来优化查询。一种可能的解决方法是创建一个计算列，将

YEAR(date)

的结果存储在该列中，并在查询中使用该计算列作为条件。

🍀3.4、列类型不匹配【隐式查询】

如果查询条件中的列类型与索引列的类型不匹配，MySQL将无法使用该索引。例如，如果在一个索引上查询一个字符串类型的列，但查询条件中使用了一个数字值，MySQL将无法使用索引来优化查询。一种解决方法是将查询条件中的值转换为与索引列类型相同的类型。

如：

Select

*

from` `tb1 

where

name

= 999;

名字列的话很明显是字符串类型，这里写个999类型肯定不一致，查询的速度肯定很慢，如果你用个引号引起来的话速度就会非常快了。

🍀3.5、or的错误使用

or可以命中索引的几类情况：

1. OR操作符需要将多个条件拆分成单个条件，然后对每个条件进行查询，并将结果进行组合。如果多个条件中只有一个条件能够命中索引，那么查询的效率会受到影响。
2. OR操作符不能用于对同一个列的多个条件查询。如果要对同一个列进行多个条件查询，应该使用IN操作符。
3. 如果要使用OR操作符进行多个条件查询，并且所有条件都能够命中索引，那么查询的效率会非常高。但是如果其中一个条件不能命中索引，那么查询的效率会受到影响。

例如：

select * ``from tb1 ``where nid = 1 ``or email = ``'[email protected]'``;

如果只有email列设置了索引那么这个语句整体的查询速度也是不快的

但是如果是下面这种情况：

``select * ``from tb1 ``where nid = 1 ``or email = ``'[email protected]' and name = ``'alex'

其中只有nid和name列为索引列的话查询的速度也是很快的，因为mysql会自动忽略email 这不是索引的一列

🍀3.6、其他的情况

- 使用函数
  select * from tb1 where reverse(name) = 'wupeiqi';

- !=
  select * from tb1 where name != 'alex'
  特别的：如果是主键，则还是会走索引
      select * from tb1 where nid != 123
- >
  select * from tb1 where name > 'alex'
  特别的：如果是主键或索引是整数类型，则还是会走索引
      select * from tb1 where nid > 123
      select * from tb1 where num > 123
- order by
  select email from tb1 order by name desc;
  当根据索引排序时候，选择的映射如果不是索引，则不走索引
  特别的：如果对主键排序，则还是走索引：
      select * from tb1 order by nid desc;

-  索引失效：如果索引被损坏或者过期，MySQL无法使用该索引，进行全表扫描。、

- 数据量过小：如果表中只有很少的数据，使用索引可能会比全表扫描更慢，因为耗费在索引扫描上的时间比全表扫描的时间还要长。

- 范围查询：使用BETWEEN和IN等范围查询语句，MySQL会先执行索引扫描，然后再进行分类筛选，如果筛选后的结果占比过高，MySQL会放弃使用索引，进行全表扫描。

🍁四、执行计划

🍀4.1、概念

在 MySQL 中可以通过 explain 关键字模拟优化器执行 SQL语句，从而知道 MySQL 是如何处理 SQL 语句的。

具体地说，MySQL 的执行计划包含以下信息：

1. 查询语句的类型，如 SELECT、INSERT、UPDATE 和 DELETE 等。
2. 查询涉及的表和它们之间的连接方式。
3. 查询采用的索引类型和具体使用的索引。
4. 每个表的访问方式，如全表扫描和索引扫描等。
5. 执行计划的优先级和执行顺序。
6. 估算和实际行的数量、使用的时区和排序规则等。

4.2、创建方法

执行计划可以通过使用 EXPLAIN 命令来获取，语法如下：

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化;

当使用 EXPLAIN 命令时，MySQL 会返回一个查询结果集，包含以上信息。具体而言，返回结果包括以下列：

1. • id：标识查询的标识符，一般情况下为随机生成的整数。
2. • select_type：查询类型，包括 SIMPLE、PRIMARY、UNION、SUBQUERY、DEPENDENT SUBQUERY 等。
3. • table：</查询涉及的表及其别名。 - partitions：查询涉及的分区。
4. • type：访问的类型，包括 ALL、INDEX、RANGE、REF、EQ_REF 和 CONST 等。
5. • possible_keys：可能使用的索引，其值由逗号分隔的索引列表组成。
6. • key：实际使用的索引。
7. • key_len：索引字段的长度。
8. • ref：此列显示哪个列或常量与 key 列一起被用于查找索引值。
9. • rows：估算结果集中的行数。
10. • filtered：结果集中符合条件的行数与结果集总行数的比例。
11. • Extra：额外的信息，如使用了临时表、使用了 filesort 等。

“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

重点

需要注意的是，执行计划只是查询优化的一个阶段，实际执行的结果可能会受到多种因素的影响，包括数据分布、硬件性能等。因此，执行计划不能完全反映查询在实际执行中的表现，但是它可以帮助开发人员分析查询语句的性能问题，从而进行相应的优化。

🍀4.3、表的访问方式

执行计划中的 type 字段显示了 MySQL 查询数据表的方法，同时也是最需要关注的字段之一。MySQL 中查询数据表的方式越简便，执行速度就越快。以下是一些常见的 type 类型：

1. **- ALL：全表扫描，数据表没有使用任何索引，这种方式的效率最低。 **
2. **- INDEX：全索引扫描，需要扫描数据表所有的索引，对于查询的需要和原本数据表的分布情况关联比较密切。 **
3. **- RANGE：索引区间扫描，仅仅扫描匹配条件的数据，而不是全表扫描，大多适合一些有序数据类型。 **
4. - REF：朴素索引反查，创建索引时只是索引主键、外键或唯一属性，要根据需要查询的属性从数据表中查询出结果。如果数据非常庞大，一般不会采用这种方式。
5. **- EQ_REF：联合索引扫描，按最小数量查找结果集，并针对联合索引中每个匹配的值进行搜索。 **
6. -CONST：索引匹配到一个已知的常量时，直接查找结果集，是最快的查询类型。
7. - SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。
8. - INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33);

🍀4.4、索引的使用

执行计划中的 possible_keys 字段表示可以使用的索引，key 字段表示实际使用的索引。对于一些大型数据表和查询量较大的应用程序，索引的使用就显得尤为重要。索引的使用可以在一定程度上减少数据表数据扫描的时间，减小查询时间。MySQL 用生成的解释计划告诉开发人员在查询中哪些索引可以用上，如果没有使用到索引需要优化查询语句或者添加合适的索引。

🍀4.5、 SQL 的优化

执行计划可以告诉你问题是数据库结构、索引还是 SQL 语句的问题。因此，分析执行计划有助于开发人员进行 SQL 的优化，从而提高查询性能。优化 SQL 的一些技巧包括：

1. • 将复杂查询拆分为简单查询。 - 减少使用内部查询或子查询。
2. • 优化 WHERE 子句，减少全表扫描。 - 对 SELECT 和 FROM 子句进行优化，避免不必要的计算和 JOIN 操作。
3. • 尽量减少使用 ORDER BY 和 GROUP BY 子句。

总之，执行计划是 MySQL 中一个非常重要的概念，通过分析执行计划可以有效地优化查询语句，提高数据库查询性能。同时，也要注意实际执行中的其他因素，如数据分布、硬件性能等。

🍁五、慢日志记录

🍀5.1、作用

对于那些执行时间比较长的语句等，如果我们是想要mysql自动帮我们记录下来，就要使用到慢日志

MySQL慢日志主要有以下几个作用：

1. 找出慢查询。慢查询指的是执行时间较长的SQL语句，这些语句可能会导致数据库性能下降。通过MySQL慢日志，我们可以找出这些慢查询，及时进行优化。
2. 优化SQL语句。通过分析MySQL慢查询日志，我们可以找到SQL语句的瓶颈所在，进行优化，从而提高数据库性能。
3. 统计查询频率。通过统计MySQL慢日志中的查询语句，我们可以了解哪些查询语句最常被执行，从而可以根据查询频率优化数据库索引等。
4. 应用于性能测试。MySQL慢日志提供了比较详细的查询日志信息，是进行数据库性能测试的重要工具之一。
5. 总之，MySQL慢日志是数据库性能优化的必备工具之一，它可以帮助我们找出SQL语句中的瓶颈，从而提高数据库性能

🍀5.2、查询

a、配置MySQL自动记录慢日志

slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录

注：查看当前配置信息： 　　 show variables like '%query%' 修改当前配置： 　　　　set global 变量名 = 值

b、查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

🍀5.3、详细步骤

MySQL慢日志是记录MySQL查询日志中的长时间查询的一种机制。他可以帮助我们找出执行时间较长的SQL语句，并对其进行优化。

查询MySQL慢日志的步骤如下：

1. 打开MySQL慢查询日志的开关。在MySQL配置文件my.cnf中找到slow_query_log这个参数，将其设置为on即可开启慢查询日志
2. 指定MySQL慢查询日志文件。在MySQL配置文件my.cnf中找到slow_query_log_file这个参数，将其设置为您想要的路径及文件名即可。如果没有指定，默认为MySQL数据目录下的主机名-slow.log。
3. 设置MySQL慢查询日志的阈值。在MySQL配置文件my.cnf中找到long_query_time这个参数，将其设置为您想要的阈值，即高于或等于该时长的SQL语句被记录到慢日志。默认值为10秒。
4. 重启MySQL。执行命令systemctl restart mysqld或service mysqld restart，以使配置生效。
5. 查询MySQL慢查询日志。可以通过命令查看MySQL慢查询日志：mysqldumpslow -s t /var/lib/mysql/hostname-slow.log # 查询所有慢日志 mysqldumpslow /var/lib/mysql/hostname-slow.log # 查询慢日志中执行时间大于5秒的SQL语句</ mysqldumpslow -t 5 /var/lib/mysql/hostname-slow.log
6. 以上命令中的-s参数表示按执行时间排序，-t参数表示指定执行时间阈值。
7. 分析MySQL慢查询语句。MySQL慢查询日志查出来的SQL语句并不是最优的，需要进行分析和优化。可以用EXPLAIN命令查看执行计划，找到瓶颈所在，并优化SQL语句。