Mysql深入浅出

675445

已于 2024-10-31 10:03:09 修改

阅读量761

点赞数 21

文章标签： mysql 数据库

于 2024-10-25 16:54:28 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_43489165/article/details/142875234

版权

Mysql优化

一、索引优化

选择合适的索引类型：根据业务需求和数据特点，合理选择B-Tree、Fulltext、Hash等索引类型。
索引列的选择：优先选择查询频率高、区分度大的列作为索引列。
索引顺序：将经常一起使用的列组合成复合索引，并注意索引的顺序，以便MySQL能够更有效地使用索引。

二、查询优化

减少全表扫描：通过添加WHERE子句，限定查询范围，避免全表扫描。
避免使用SELECT *：只查询需要的列，减少数据传输和内存占用。
使用JOIN代替子查询：对于复杂的查询，使用JOIN代替子查询可以提高查询效率。
深度分页如何优化

**对于自增连续ID：使用子查询定位，通过子查询先找到关键行的位置，然后再执行外层查询获取数据（SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT [10000], 10);
）
**使用搜索引擎‌：对于需要进行复杂搜索和分页的应用，可以考虑使用Elasticsearch等专业的搜索引擎。
**对于静态数据或者不经常变化的数据，可以考虑使用缓存来提高查询效率。
**分表或分库：对于极大的表，可以考虑进行分表或分库操作，将数据散列到不同的表或数据库中。

三、存储引擎优化

选择合适的存储引擎：根据业务需求，选择InnoDB、MyISAM等存储引擎。
调整缓冲池大小：根据服务器内存和业务需求，调整缓冲池大小，提高缓存命中率。
优化表结构：合理设计表结构，避免过多冗余字段，减少存储空间和数据维护成本。

四、配置优化

调整MySQL配置参数：根据服务器硬件资源和业务需求，合理调整MySQL配置参数，如innodb_buffer_pool_size、innodb_log_file_size等。
定期分析和优化表：通过工具如pt-query-digest、myisamchk等，定期分析表性能，并根据分析结果进行优化。

五、系统层面优化

硬件升级：提升CPU、内存、硬盘等硬件性能，为MySQL提供更好的运行环境。
网络优化：优化网络环境，减少网络延迟，提高MySQL响应速度。
操作系统优化：根据MySQL的运行需求，优化操作系统参数，如文件系统、系统负载等。

五、架构层面优化

分库分表是数据库优化中的一种常见技术，它通过将数据分散存储到多个数据库或表中，以提高数据库系统的性能和可扩展性。以下是分库分表的详细说明及其优化策略：

分库分表的基本概念：

**分库：将数据按照某种规则分散存储到不同的数据库服务器上。这可以缓解单库数据量过大导致的性能瓶颈，并通过分布式架构提高系统的并发处理能力。
**分表：将一个大的数据表按照某种规则拆分为多个表，这些表可以存储在同一数据库中，也可以存储在不同的数据库中。

分库分表的类型：

**垂直分表：根据表中的字段使用频率和业务需求，将一个表拆分为多个表，每个表包含部分字段。这种方式可以减少热点数据的竞争和IO压力。
**水平分表：根据特定的业务规则（如时间、ID范围等），将数据分散到多个结构相同的表中。这种方式可以解决单表数据量过大的问题。

五、部署架构层面优化

搭建集群环境，提高数据的并发性能

索引

一、MySQL索引的原理

索引的作用
索引的作用类似于书籍的目录，通过索引可以快速找到所需的内容。在数据库中，索引可以加快查询、更新和删除操作的速度，但对于插入操作可能产生一定的性能影响。
索引的数据结构
MySQL中索引的数据结构主要有两种：B-Tree和哈希表。B-Tree是一种平衡的多路查找树，适用于范围查询和等值查询；哈希表则适用于等值查询，但不支持范围查询。

二、MySQL索引的类型

单列索引
单列索引是基于单个列创建的索引，可以加快对该列的查询速度。
复合索引(联合索引)
复合索引是基于多列创建的索引，可以加快对多列组合的查询速度。
唯一索引
唯一索引是一种特殊类型的索引，它要求被索引的列中的值必须是唯一的。
全文索引
全文索引是针对文本内容创建的索引，适用于全文检索场景。

三、MySQL索引的优化策略

选择合适的索引类型
根据业务需求和查询特点，选择合适的索引类型，以实现最佳性能。
限制索引数量
过多的索引会导致数据库性能下降，建议只创建必要的索引。
索引列的选择
选择查询频率高、区分度大的列作为索引列，以提高查询效率。
索引列的顺序
在创建复合索引(联合索引)时，注意索引列的顺序，将查询条件中频繁出现的列放在前面(最左匹配原则)。
使用前缀索引
对于较长的字符串列，可以使用前缀索引，以减少索引的大小和查询时间。
什么时候不用索引反而提升效率？

**数据量很小的表：当数据集非常小的时候，索引的开销可能比直接扫描全表要大。在这种情况下，直接访问数据可能会更快。
**频繁更新数据的表：如果表中的数据经常变动，那么维护索引的成本可能会很高。在这种情况下，可能会选择不使用索引，尤其是在对性能要求不是特别高的场景下。
**全表扫描/读取表里大部分数据的时候：如果查询需要检索表中大部分的行，那么全表扫描可能比使用索引更快，因为索引也需要读取额外的数据结构。
**查询条件不明确：如果查询条件不适用于索引列，或者查询涉及到多列，而索引只覆盖其中一部分，那么使用索引可能不会带来性能提升。
**索引列包含大量重复值（低选择性索引(男、女)）：如果索引列有很多重复值，那么索引的效果会大打折扣，尤其是在进行范围查询时。
**特定类型的查询：有些特殊的查询，如某些类型的排序或计算密集型查询，可能在没有索引的情况下运行得更快。
I/O受限的环境：在I/O受限的系统上，如果索引数据不在内存中，频繁的磁盘I/O可能会导致查询性能下降。
**临时表或中间结果集：在处理临时数据或中间结果时，可能会选择不创建索引，以节省时间和空间。

索引什么情况会失效？

**查询条件使用了OR：当SQL查询中使用OR来连接条件，且这些条件不是所有的都作用于索引列上时，即使某个条件使用了索引，该索引也可能不会被使用。
**前导模糊查询(like ‘%ace’)：在使用LIKE查询时，如果通配符%放在了搜索词的开头，会导致索引失效，因为数据库无法利用索引来快速定位数据。
**不等式条件：使用不等于（<>）、NOT IN、NOT EXISTS等操作时，可能会导致索引失效。
**范围查询：在范围查询（如使用>、<、>=、<=、BETWEEN）时，如果查询条件中涉及到索引的第一列，那么该索引后面的列将无法利用该索引。
**跳过索引列：在使用复合索引时，如果查询条件没有按照索引列的顺序使用，或者跳过某个索引列，那么该索引可能不会被使用。
**计算和函数：如果查询中对索引列使用了计算或函数，那么该索引不会生效。例如，在索引列上使用了SUM、ROUND等函数。
**类型转换：如果查询条件中对索引列进行了隐式或显式的类型转换，那么索引可能会失效。
**NULL值的查询：IS NULL可以在某些情况下使用索引，但IS NOT NULL通常无法使用索引。
**系统统计信息过时：数据库管理系统依赖于统计信息来决定是否使用索引，如果统计信息不准确（如未及时更新），可能导致索引不被使用。
索引本身问题：索引可能因为损坏、不一致或其他技术问题而失效。
**隐式转换：数据库在查询过程中自动将索引列的类型转换成另一种类型，可能导致索引失效。

聚簇索引和非聚簇索引
一、聚簇索引（Clustered Index）

1.1 定义与特性
聚簇索引是一种将数据行实际存储在索引节点的数据结构。在MySQL中，每个表只能有一个聚簇索引，通常以主键作为基础建立。聚簇索引的特点是数据行按照索引的顺序存储，这使得在执行查询时，能够直接通过索引快速访问到数据。

优势：

数据读取效率高：由于数据行直接存储在索引节点上，读取数据时无需额外访问数据行。
范围查询性能好：聚簇索引适合范围查询，因为数据行已经按顺序排列。

劣势：

写操作影响大：插入、删除、更新操作需要重组索引树，可能影响性能。
页面分裂频繁：新行插入可能导致页面分裂，影响性能和空间利用率。

二、非聚簇索引（Non-Clustered Index）

2.1 定义与特性
非聚簇索引，也称为辅助索引，它创建了一个独立于表数据结构的数据结构，包含指向数据行的指针。在MySQL中，可以创建多个非聚簇索引。

优势：

写操作性能相对稳定：由于非聚簇索引与数据行物理位置分离，写操作不会影响数据行排列。
灵活性高：可以在多个列上创建非聚簇索引，以满足不同查询需求。

劣势：

数据读取效率相对低：需要通过索引访问数据行指针，再通过指针找到数据行。
索引维护开销大：非聚簇索引的维护需要额外空间和时间。

事务

事务是作为一个整体执行的一系列操作，这些操作要么全部成功，要么全部失败。在 MySQL 中，事务可以确保数据库状态从一个一致性状态转变到另一个一致性状态。

1.1 事务的特性

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不做，不会处于中间状态。
一致性（Consistency）：事务必须使数据库从一个一致性状态转移到另一个一致性状态。
隔离性（Isolation）：一个事务的执行不能被其他事务干扰。
持久性（Durability）：一旦事务提交，其对数据库的修改是永久性的。

2. MySQL 事务操作

在 MySQL 中，事务的操作主要依赖于以下 SQL 语句：

开始事务：START TRANSACTION; 或者 BEGIN;
提交事务：COMMIT;
回滚事务：ROLLBACK;
保存点设置：SAVEPOINT;
回滚到保存点：ROLLBACK TO;

3. 事务的隔离级别

MySQL 支持以下四种事务隔离级别，不同级别提供了不同级别的隔离性和性能：

READ UNCOMMITTED：允许读未提交的数据变更。
READ COMMITTED：仅允许读已提交的数据变更。
REPEATABLE READ：确保在一个事务内多次读取同样记录的结果是一致的。
SERIALIZABLE：最严格的隔离级别，确保事务串行化执行。
通过设置合适的事务隔离级别，可以在保证数据一致性的同时，优化系统的性能。

4. 多版本并发控制（MVCC, Multi-Version Concurrency Control）

一、什么是MVCC？

MVCC是一种在数据库管理系统中实现并发控制的技术，它允许数据在事务中以一种非锁定的方式被读取，从而系统的并发能力。在MySQL中，InnoDB存储引擎采用MVCC来处理多事务并发操作，保证了数据的完整性和一致性。

二、MVCC的工作原理

读写分离：MVCC的核心思想是将读操作和写操作分离。当多个事务并发执行时，读操作不会阻塞写操作，反之亦然。这样，系统可以同时处理多个读写请求，提高了并发性能。
版本链：在MVCC中，每个数据行都有多个版本，这些版本构成了一个undolog版本链。每当数据行被修改时，系统会生成一个新的版本，并将新版本插入到版本链中。这样，不同的事务可以看到不同版本的数据。
当前读与历史读：MySQL中，读操作分为当前读和历史读。当前读是指读取最新版本的数据，而历史读是指读取事务开始时的一致性视图。MVCC通过隐藏版本链中的部分版本，确保不同事务看到的数据是一致的。
事务隔离级别：MVCC支持不同的事务隔离级别，包括读未提（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。这些隔离级别决定了事务在并发环境下的可见性和一致性。

锁

一、MySQL锁的类型

表锁（Table Lock）
表锁是MySQL中最基本的锁类型，用于对整个表进行锁定。在MyISAM存储引擎中，表锁是默认的锁机制。表锁可以有效地减少锁的开销，但在高并发场景下，可能成为性能瓶颈。
行锁（Row Lock）
行锁是一种更为精细的锁机制，它只锁定涉及到的数据行。InnoDB存储引擎默认使用行锁，可以大大提高数据库的并发能力，减少锁竞争。
页锁（Page Lock）
页锁介于表锁和行锁之间，它锁定的是数据页。虽然页锁在InnoDB中较少使用，但在某些特定场景下，它比行锁更为高效。

二、锁的粒度和开销

锁的粒度越小，锁的开销越大，但并发能力越强。反之，锁的粒度越大，锁的开销越小，但并发能力减弱。在选择锁的类型时，需要根据实际业务需求和数据库性能指标进行权衡。

三、锁的级别和兼容性

锁的级别分为共享锁（Shared Lock）和排他锁（Exclusive Lock）。共享锁允许其他事务读取锁定的数据，而排他锁则不允许其他事务进行读取或写入操作。锁的兼容性决定了不同锁类型之间的共存关系，合理地设置锁的级别可以提高数据库的并发性能。

四、锁的优化策略

减少锁的范围：尽量使用行锁而非表锁，减少锁定的数据量，提高并发能力。
减少锁的时间：优化查询语句，减少锁的持有时间，避免长时间占用锁资源。
避免死锁：合理设计索引，避免循环等待条件，减少死锁的发生概率。

聚合函数

AVG(column)：返回某列的平均值。
COUNT(column)：返回某列的行数。
MAX(column)：返回某列的最大值。
MIN(column)：返回某列的最小值。
SUM(column)：返回某列的总和。

关于GROUP BY

在 MySQL 5.7 中，默认启用了 ONLY_FULL_GROUP_BY SQL 模式。这个模式要求 GROUP BY 语句中的所有选择列都必须在 GROUP BY 子句中明确列出，或者在聚合函数中使用。这是为了确保结果集的确定性，符合 SQL 标准。

如果你遇到了关于 GROUP BY 的错误，可能是因为你的查询没有遵循这个规则。例如，如果你有一个如下查询：

SELECT name, city FROM people GROUP BY city;

这个查询在默认情况下会失败，因为 name 列没有在 GROUP BY 子句中，也没有被包含在任何聚合函数中。

为了解决这个问题，你可以：

修改你的查询以满足 ONLY_FULL_GROUP_BY 的要求，比如使用聚合函数：

SELECT city, COUNT(name) FROM people GROUP BY city;

引擎

一、InnoDB引擎

InnoDB是MySQL默认的数据库引擎，它具有以下特点：

支持事务处理：InnoDB支持ACID（原子性、一致性、隔离性、持久性）特性，可以保证数据的一致性和完整性。
行级锁定：InnoDB采用行级锁定，能够有效降低并发访问时的锁定粒度，提高并发性能。
读写分离：InnoDB支持读写分离，可以将查询和更新操作分离，提高数据库性能。

优化建议：

合理设置缓存：InnoDB具有缓冲池，可以通过调整缓冲池的大小来提高数据库性能。
索引优化：合理创建索引，避免过多的索引和冗余索引。

二、MyISAM引擎

MyISAM是MySQL早期使用的数据库引擎，它具有以下特点：

不支持事务处理：MyISAM不支持事务处理，适用于对事务要求不高的场景。
表级锁定：MyISAM采用表级锁定，并发性能相对较低。
索引存储：MyISAM将索引和数据分开存储，可以提高查询效率。

优化建议：

表分区：将大表进行分区，降低单个表的大小，提高查询效率。
索引优化：合理创建索引，避免冗余索引。

三、Memory引擎

Memory引擎是MySQL的一种特殊数据库引擎，它将数据存储在内存中，具有以下特点：

高速读取：Memory引擎的数据存储在内存中，读取速度非常快。
不支持事务处理：Memory引擎不支持事务处理，适用于对事务要求不高的场景。
自动失效：Memory引擎中的数据在服务器重启后自动失效。

优化建议：

合理设置内存大小：根据服务器内存大小和业务需求，合理设置Memory引擎的内存空间。
选择合适的数据类型：选择合适的数据类型，以减少内存占用。

日志

一、错误日志（Error Log）

错误日志是记录MySQL服务器在启动、运行过程中出现的错误信息的日志文件。通过查看错误日志，您可以快速定位问题所在，并进行相应的故障排除。

配置方法：在MySQL配置文件（通常为my.cnf或my.ini）中设置log_error参数，指定错误日志文件的存储路径。
示例：log_error=/var/log/mysql/error.log

二、慢查询日志（Slow Query Log）

慢查询日志用于记录执行时间超过指定阈值的SQL查询语句。通过分析慢查询日志，您可以发现性能瓶颈，优化查询语句，提高数据库的整体性能。

配置方法：在配置文件中设置slow_query_log参数为1，并指定long_query_time参数，以定义查询语句执行时间的阈值。
示例：slow_query_log=1 和 long_query_time=2（表示执行时间超过2秒的查询将被记录）

三、二进制日志（Binary Log）

二进制日志记录了对数据库执行写操作（INSERT、UPDATE、DELETE）的所有SQL语句。它对于数据备份、复制和数据恢复等场景至关重要。

配置方法：在配置文件中设置server_id、log_bin和binlog_format等参数。
示例：server_id=1、log_bin=/var/log/mysql/mysql-bin.log 和 binlog_format=ROW

四、事务日志（Transaction Log），包括undolog、redolog

事务日志用于记录事务的开始、提交和回滚等操作。它确保了事务的原子性和一致性，即使在系统崩溃或故障的情况下也能保证数据的一致性。

Undo Log: 用于保证事务的原子性和一致性。当事务执行更新操作时，Undo Log会记录旧数据的副本，以便在事务失败或者回滚时能够恢复数据到事务开始之前的状态。

Redo Log: 用于保证事务的持久性。当事务执行更新操作时，Redo Log会记录新数据的副本，以便在系统崩溃或者宕机时，能够通过Redo Log重新执行事务以恢复数据到持久化状态。

五、中继日志（Relay Log）

中继日志是MySQL复制功能的组成部分，用于记录从主服务器接收的复制事件。在从服务器上，这些事件将被重新执行，以同步主从服务器之间的数据。

配置方法：在从服务器上设置relay_log参数，指定中继日志的存储路径。
示例：relay_log=/var/log/mysql/relay-bin.log

存储过程

** 一、理解存储过程**

存储过程是一段存储在数据库中的预编译SQL代码，它包含了一系列的SQL语句和控制结构。存储过程可以在数据库服务器上直接执行，无需再次编译，从而减少客户端和服务器之间的交互，提高执行效率。

特点与优势

代码复用：将常用的SQL操作封装起来，便于重复调用。
提高性能：减少网络传输开销，提升查询效率。
安全性：限制用户直接操作数据表，提高数据安全性。
简化维护：集中管理业务逻辑，便于维护和更新。
二、创建存储过程

在MySQL中，创建存储过程需要使用CREATE PROCEDURE语句。

参数管理：合理使用输入、输出和输入输出参数。
错误处理：使用DECLARE EXIT HANDLER来处理异常情况。
事务管理：在存储过程中使用事务确保数据一致性。
三、调用与执行

一旦存储过程被创建，就可以使用CALL语句来执行它：

四、存储过程的优化

为了确保存储过程的性能，以下是一些实用的优化策略：

索引优化：确保存储过程使用到的表都进行了合理的索引。
查询优化：避免复杂的关联查询和子查询，尽可能使用批处理操作。
内存管理：合理使用局部变量和临时表，减少内存消耗。

join，left join, right join， inner join

在数据库查询中，JOIN操作是连接两个或多个表的关键手段
一、JOIN概述
在MySQL中，JOIN操作用于从两个或多个表中根据相关列之间的关系提取数据。常见的JOIN类型有INNER JOIN（内连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）和FULL JOIN（全连接，MySQL不支持）。下面，我们将分别探讨这些JOIN类型的用法和特性。

二、INNER JOIN（内连接）
INNER JOIN是最常见的连接类型，它返回两个表中匹配的行。

特性：