Devin的文档

Appearance

MySQL ​

MySQL 的逻辑架构？ ​

MySQL的逻辑架构可以分为几个主要层次：连接层、核心服务层（SQL层）、存储引擎层和数据存储层。这些层次通过特定的接口和协议进行交互，以实现高效的数据管理和查询功能。

  1. 连接层

    • 功能：负责客户端与MySQL服务器的连接处理、身份验证和授权等。

    • 组件：包括连接池、线程池等，用于管理客户端连接和线程资源。

    • 特点：

      • 客户端通过TCP/IP协议或UNIX套接字与MySQL服务器建立连接。

      • 服务器为每个客户端连接分配一个独立的线程，用于处理该客户端的请求。

      • 线程池技术用于复用线程，减少线程创建和销毁的开销。

  2. 核心服务层（SQL层）

    • 功能：负责SQL语句的解析、优化和执行，以及管理跨存储引擎的功能（如存储过程、触发器等）。

    • 组件：

      • SQL接口：接收用户的SQL命令，并返回查询结果。

      • 解析器：对SQL语句进行词法和语法分析，生成解析树。

      • 优化器：根据解析树和数据库统计信息，生成最优的执行计划。

      • 执行器：根据执行计划调用存储引擎的API来执行查询，并将结果返回给客户端。

      • 缓存（MySQL 8.0之前）：包括查询缓存等，用于缓存查询结果以提高性能。但MySQL 8.0已移除查询缓存，因为它在多数情况下弊大于利。

    • 特点：

      • 通过对SQL语句的解析和优化，提高查询效率。

      • 支持多种内置函数和跨存储引擎的功能。

  3. 存储引擎层

    • 功能：负责数据的存储和提取，支持不同的存储机制和数据结构。

    • 组件：包括InnoDB、MyISAM、Memory等多种存储引擎。

    • 特点：

      • 不同的存储引擎具有不同的特点和性能优势。

      • InnoDB是MySQL的默认存储引擎，支持事务处理、行级锁定和外键约束等高级功能。

      • 存储引擎层通过API与核心服务层交互，实现数据的存取操作。

  4. 数据存储层

    • 功能：将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

    • 特点：

      • 存储层是MySQL数据持久化的基础。

      • 它通过文件系统与磁盘进行交互，管理数据的存储和读取。

      • 不同存储引擎的存储层实现不同，例如InnoDB使用自己的数据文件和索引文件格式，而MyISAM则使用标准的文件系统来存储数据和索引。

MySQL的逻辑架构通过分层设计实现了高效的数据管理和查询功能。连接层负责客户端连接和授权；核心服务层负责SQL语句的解析、优化和执行；存储引擎层负责数据的存储和提取；数据存储层则负责数据的持久化存储。这些层次之间通过特定的接口和协议进行交互，共同构成了MySQL强大的数据库管理系统。

MySQL数据库的与权限相关的表有哪些？ ​

MySQL数据库中与权限相关的表主要包括以下几个：

  1. mysql.user表：

    • 功能：该表包含了所有注册到MySQL实例上的用户账户和密码信息。当用户连接到MySQL服务器时，这些信息将被验证，并使用该账号的授权来确定所允许进行的数据库层级权限和操作类型。

    • 重要字段：

      • 用户名(User)：用于标识该用户的用户名。

      • 主机名(Host)：允许该用户连接MySQL服务器的主机名或IP地址。

      • 密码(Password)：在MySQL 5.7之前的版本中，此字段用于存储密码，但出于安全考虑，此字段已被官方废弃，转而使用authentication_string字段。

      • 认证方式(Authentication_string)：账号的新密码字段，安全性较高，支持多种验证方式，如sha256_password和caching_sha2_password等。

  • 权限：在user表中启用的所有权限都是全局级的，适用于所有数据库。

  1. mysql.db表：

    • 功能：存储了用户对某个数据库的操作权限。这个表维护的是SQL GRANT命令发出时指定的权限，其中每条记录都由数据库名称、用户身份验证信息和相关的数据访问权限代码组成。

    • 重要字段：Host、User、Db（这三个字段的组合构成了表的主键），以及一系列权限字段（如Select_priv、Insert_priv等），用于描述用户对数据库的操作权限。

  2. mysql.tables_priv表

    • 功能：用来对单个表进行权限设置。表中的记录详细说明了哪些用户可以对哪些表执行哪些操作。

    • 重要字段：与mysql.db表类似，但增加了Table_name字段来指定具体的表名，以及Table_priv和Column_priv字段来分别描述对表和列的权限。

  3. mysql.columns_priv表：

    • 功能：用来对单个数据列进行权限设置。列级别的授权能够大大精细化数据发放、满足用户需求和限制敏感数据的访问。

    • 重要字段：包括Host、User、Db、Table_name和Column_name，以及一系列权限字段（如Select_priv、Insert_priv等），用于描述用户对特定列的操作权限。

  4. mysql.procs_priv表：

    • 功能：包含可对MySQL存储过程进行调用的用户身份验证信息以及调用时始终有资格的操作权限。这有助于管理员为不同的使用情况和查询要求创建自定义过程，同时为调用方提供确定的权限。

    • 重要字段：包括Host、User、Db、Routine_name（存储过程或函数的名称）以及一系列权限字段（如Execute_priv），用于描述用户对存储过程和函数的操作权限。

MySQL 权限表的验证过程? ​

  1. 先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。
  2. 通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。
    1. 先检查全局权限表 user，如果user中对应的权限为Y，则此用户对所有据库的权限都为Y，将不再检查db, tables_priv,columns_priv；
    2. 如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；
    3. 如果db中为N，则检 查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

MySQL 如何获取表内所有列的名称和类型？ ​

使用INFORMATION_SCHEMA数据库中的COLUMNS表。INFORMATION_SCHEMA是MySQL的一个特殊数据库，它包含了关于所有其他数据库的信息，比如数据库名、表名、列的数据类型等。

sql
SELECT   
    COLUMN_NAME,   -- 列的名称
    DATA_TYPE,   -- 列的数据类型 
    CHARACTER_MAXIMUM_LENGTH, -- 对于字符类型，显示最大长度  
    NUMERIC_PRECISION, -- 对于数值类型，显示精度  
    NUMERIC_SCALE -- 对于数值类型，显示小数位数  
FROM   
    INFORMATION_SCHEMA.COLUMNS  
WHERE   
    TABLE_SCHEMA = 'database-name'    
    AND TABLE_NAME = 'table-name';

运行命令：desc table_name;。另外，可以使用show full columns from table_name;

sql
desc answer.activity;
show full columns from answer.activity;
  • Field：字段名。
  • Type：该字段类型。
  • Collation：描述了如何对查询出来的数据进行比较和排序。Null：是否允许为空，NO表示不允许，YES表示允许。
  • Key：键，表示该列是否有索引，例如：主键（PRI），唯一键（UNI），非唯一键或多列唯一键（MUL）等。如果该列为空，那么表示该列没有索引或该列作为多列索引的非第1列。若表中没有主键，但是某个列创建了唯一索引，且不能包含空值，则该列会显示为PRI。若某个列含有多个键，则会按照优先级显示：PRI>UNI>MUL。
  • Default：该字段默认值。
  • Extra：附加信息，如自增主键上的（auto_increment）。
  • Privileges：权限，有select、update等。
  • Comment：字段注释。

MySQL如何对一张表同时进行查询和更新？ ​

sql
UPDATE your_table yt  
JOIN another_table at ON yt.id = at.foreign_id  
SET yt.column_name = 'new_value'  
WHERE at.some_condition = 'some_value';

在MySQL里如何执行OS命令？ ​

可以通过system或“\!”来执行。

MySQL如何实现如果不存在则插入，存在则更新的操作？ ​

方式一：REPLACE INTO

REPLACE INTO 语句的工作原理是尝试将新行插入到表中。如果表中已存在具有相同唯一索引或主键的行，MySQL 会先删除该旧行，然后插入新行。如果表中有一个或多个唯一索引，并且新行的值与现有行的值在这些索引上冲突，那么现有行将被删除并由新行替换。

REPLACE INTO 可能会导致一些副作用，比如自增ID的跳跃，以及如果表中包含外键约束，它可能会违反这些约束（因为旧行会被删除）。

sql
-- MySQL的REPLACE INTO有3种形式
-- 其中，“INTO”关键字可以省略，不过最好加上“INTO”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL将自动为这些列赋上默认值。
REPLACE INTO TBL_NAME(COL_NAME) VALUES(/)
REPLACE INTO TBL_NAME(COL_NAME) SELECT /
REPLACE INTO TBL_NAME SET COL_NAME=VALUE/

方式二：INSERT ... ON DUPLICATE KEY UPDATE

依赖于表上的唯一索引或主键约束来确定记录是否已经存在。

sql
-- 如果id=1的记录不存在，MySQL会插入一个新记录。如果id=1的记录已经存在，MySQL会更新该记录的name字段为'John Doe'。
INSERT INTO users (id, name)  
VALUES (1, 'John Doe')  
ON DUPLICATE KEY UPDATE name = VALUES(name);

VALUES(name)是一个特殊的函数，它返回INSERT语句中尝试插入的name字段的值。这允许你在ON DUPLICATE KEY UPDATE部分使用与INSERT部分相同的值。

MySQL中LENGTH和CHAR LENGTH的区别是什么？ ​

在MySQL中，LENGTH() 和 CHAR_LENGTH() 函数都用于获取字符串的长度，但它们之间有一个关键的区别：它们计算长度的单位不同。

  1. LENGTH() 函数：

    • LENGTH() 函数返回字符串的字节长度。这意呀着它会根据字符串使用的字符集来计算长度。对于单字节字符集（如latin1），LENGTH() 返回的结果与字符数相同。但是，对于多字节字符集（如utf8、utf8mb4等），一个字符可能占用多个字节，因此 LENGTH() 返回的结果可能会大于实际的字符数。

    • 示例：如果你有一个使用utf8mb4字符集的字符串 '你好'，因为每个中文字符在utf8mb4下占用3个字节，所以 LENGTH('你好') 将返回 6。

  2. CHAR_LENGTH() 或 CHARACTER_LENGTH() 函数：

    • CHAR_LENGTH() 或 CHARACTER_LENGTH() 函数返回字符串的字符数，不考虑字符集。这意味着无论字符集如何，它都会返回字符串中的实际字符数量。

    • 如：对于同样的字符串 '你好'，不管它使用的是什么字符集，CHAR_LENGTH('你好') 都会返回 2，因为字符串中确实包含两个字符。

MySQL中运算符 <=> 的作用是什么？ ​

在MySQL中，<=> 运算符是NULL安全的等于运算符（Null-safe equal to operator）。这个运算符和“=”类似，都执行相同的比较操作，<=>在两个操作数均为NULL时，其返回值为1，而当一个操作数为NULL时，其返回值为0。

使用<=>运算符时，你可以避免在比较过程中因为NULL值而导致的意外结果。这在处理可能包含NULL值的数据库字段时特别有用。

MySQL中Iimit的作用是什么？ ​

MySQL中的LIMIT子句主要用于限制查询结果集的数量。LIMIT子句可以指定从查询结果中返回的行数的数量，并且可以指定起始行的偏移量。

LIMIT子句的基本语法格式有两种：

  • LIMIT m,n：其中，“m”表示从哪条记录开始显示（注意：索引值从0开始），"n" 表示显示记录的条数。
  • LIMIT n：当不指定初始位置时，记录从第一条记录开始显示，只指定返回的 n 条数。

MySQL的 limit m,n 工作原理就是先读取前m条记录，然后抛弃前m条，再读取n条想要的记录，初始记录行的偏移量是 0。m越大，性能会越差。

优化思路，在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

可以直接使用limit来进行分页操作，但这个关键字在数据量和偏移量（offset）比较大时，却很低效。所以，对limit优化，要么限制分页的数量，要么降低偏移量（offset）的大小。一般解决方法是关联查询或子查询优化法，可以先查询出主键，然后利用主键进行关联查询。

delete,drop,truncate命令有哪些区别？ ​

一、功能和使用场景

  1. DELETE：

    • 功能：用于删除表中的一行或多行数据，但保留表结构。

    • 影响：不影响表所占用的extent，高水线(high watermark)保持原位置不动，不释放表空间，使用optimize table table_name 会马上释放磁盘空间。delete后表仍保持auto_increment，但重启MySQL的话，则重启后的auto_increment会被置为1.

  2. DROP：

    • 功能：用于删除整个表，包括表结构和表中的所有数据。

    • 影响：将删除表的结构被依赖的约束，触发器，索引；依赖于该表的存储过程/函数将保留，但是变为invalid状态。将表所占用的空间全部释放。

  3. TRUNCATE：

    • 功能：用于删除表中的所有数据，但保留表结构。

    • 影响：会重置auto_increment的值为1。默认情况下，通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。可以使用reuse storage; truncate会将高水线复位(回到最开始)。

二、执行效率和回滚性

  1. 执行效率：

    • 一般来说，DROP命令的执行速度最快，因为它直接删除整个表，包括表结构和数据。

    • TRUNCATE命令次之，因为它虽然保留表结构，但直接删除表中的所有数据，不逐行处理。

    • DELETE命令的执行速度最慢，因为它需要逐行删除数据，并且每删除一行都会生成事务日志。

  2. 回滚性：

    • DROP和TRUNCATE命令都是不可回滚的，一旦执行，数据将永久丢失。

    • DELETE命令则是可以回滚的，如果当前事务尚未提交，可以使用ROLLBACK语句撤销删除操作。

三、触发器触发

  • DELETE命令会触发表中的触发器（如果有的话）。

  • TRUNCATE命令则不会触发任何触发器，因为它不是逐行删除数据。

  • DROP命令同样不会触发触发器，因为它直接删除整个表。

四、其他注意事项

  • 在使用DROP和TRUNCATE命令时，一定要谨慎操作，并确保已经做好了数据备份，以防数据丢失。
  • TRUNCATE命令虽然可以快速清空表中的数据，但它会重置表的自增计数器，这可能会影响到后续插入数据的ID值。
  • DELETE命令可以根据条件灵活删除数据，但需要注意其执行效率和可能产生的性能影响。

MySQL区分大小写吗？ ​

MySQL在大小写敏感性方面的表现取决于几个因素，包括操作系统、MySQL的版本以及配置设置。以下是关于MySQL是否区分大小写的详细解答：

操作系统的影响

在MySQL中，一个数据库会对应一个文件夹，数据库里的表会以文件的方式存放在文件夹内，所以，操作系统对大小写的敏感性决定了数据库和表的大小写敏感性。MySQL中，有一个只读的系统变量“lower_case_file_system”，其值反映的正是当前文件系统是否区分大小写。所以，MySQL在Windows下是不区分大小写的。

配置设置的影响

lower_case_table_names 是控制MySQL大小写敏感性的配置项。其值可以是0、1或2，具体含义如下：

  • 0：表名的存储是区分大小写的。但是，比较时是不区分大小写的（但在Linux上，比较时仍然是区分大小写的）。这个设置是Linux系统的默认值。
  • 1：表名的存储是转换为小写的，并且比较时也是不区分大小写的。这个设置是Windows系统的默认值，也可以在Linux上通过修改配置文件来实现。
  • 2：表名的存储是区分大小写的，但是比较时是不区分大小写的。这个设置仅在Mac OS X上有效，并且不推荐在其他操作系统上使用。

查询和字段编码的影响

  • 对于SQL索引、关键字、函数名、存储过程和事件的名字等，MySQL是不区分大小写的。
  • 变量名也是严格区分大小写的。
  • 变触发器的名字要区分字母的大小写。
  • 对于列名（或字段名）与列的别名（或字段别名），MySQL在大多数情况下也是不区分大小写的。但是，如果列名或别名使用了特殊字符或保留字，并且没有使用反引号（`）进行转义，那么可能会引发错误。
  • 如果字段使用了特定编码（如utf8_bin），则MySQL会对其值区分大小写。但是，这并不影响表名、数据库名等标识符的大小写敏感性。

列设置为AUTO INCREMENT时，达到最大值会发生什么情况？ ​

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。LAST_INSERT_ID将返回由Auto_increment分配的最后一个值，并且不需要指定表名称。

MySQL中SQL Mode的作用是什么？ ​

SQL Mode（模式）定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。常用来解决下面几类问题：

  • 通过设置SQL Mode，可以完成不同严格程度的数据校验，有效地保障数据准确性。
  • 通过设置SQL Mode为ANSI模式，来保证大多数SQL符合标准的SQL语法，这样应用在不同数据库之间进行迁移时，就不需要对业务SQL进行较大的修改。
  • 在不同数据库之间进行数据迁移之前，通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中。

SQL Mode由参数sql_mode来设置，可以在配置文件my.cnf中设置，也可在客户端中进行设置。此外，这个参数还可分别进行全局的设置或当前会话的设置，也可以在MySQL启动时设置（--sql-mode="mode"）。

若是将sql_mode设置为TRADITIONAL、STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种，则称该模式属于严格模式（Strict mode）。

其中，STRICT_ALL_TABLES模式对所有引擎的表都启用严格模式；而STRICT_TRANS_TABLES只对支持事务的表启用严格模式。在严格模式下，一旦任何操作的数据产生问题，都将终止当前的操作，对于启用STRICT_ALL_TABLES的非事务引擎而言，这时数据可能停留在一个未知的状态，因此需要非常小心这个选项可能带来的潜在影响。

MySQL有哪些数据类型？ ​

整数类型

  • TINYINT：占用1个字节，能够表示256个数值。范围为-128到127（有符号）或0到255（无符号）。
  • SMALLINT：占用2个字节，能够表示65536个数据。范围为-32768到32767（有符号）或0到65535（无符号）。
  • MEDIUMINT：占用3个字节，范围为-8388608到8388607（有符号）或0到16777215（无符号）。
  • INT/INTEGER：标准整数类型，占用4个字节，能够表示42亿多数据。范围为-2147483648到2147483647（有符号）或0到4294967295（无符号）。
  • BIGINT：大整数类型，占用8个字节，范围为-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号）。
  • 整数类型还可以是BIT和BOOL，其中BIT类型用于存储位字段值，BOOL是TINYINT(1)的别名，只能存储1（表示TRUE）或0（表示FALSE）。

任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。整数类型可以被指定长度，长度不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。

浮点类型

  • FLOAT：单精度浮点数，占用4字节。
  • DOUBLE：双精度浮点数，占用8字节，有时也被称为REAL。在不指定精度时，将被保存为硬件和操作系统所支持的最大精度。
  • DECIMAL/NUMERIC：高精度小数，可以指定精度和小数位数，不指定精度，默认为（10，0）。不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

定长字符串

  • CHAR(n)：固定长度的字符串，n表示字符数。最多可存储255个字符（在utf8编码下，实际占用空间可能更大）。如果存储的字符串长度小于定义的长度，MySQL会在字符串后面填充空格。

可变长度字符串

  • VARCHAR(n)：可变长度的字符串，n表示字符数。最多可以存储65535个字符，但实际占用的存储空间取决于存储的字符串长度。VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
  • TEXT：用于存储长文本数据，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们之间的主要区别在于可以存储的最大字符数不同。TEXT查询时会使用临时表，导致严重的性能开销。

日期和时间类型

  • DATE：用于存储日期值，格式为'YYYY-MM-DD'，范围从'1000-01-01'到'9999-12-31'。
  • TIME：用于存储时间值，格式为'HH:MM:SS'，范围从'-838:59:59'到'838:59:59'。
  • DATETIME：用于存储日期和时间值，格式为'YYYY-MM-DD HH:MM:SS'，范围从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
  • TIMESTAMP：也用于存储日期和时间值，但具有时区自动转换功能和自动更新的能力。范围从'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。
  • YEAR：用于存储年份值，可以存储四位数的年份，范围从1901到2155。

其他数据类型

  • BINARY和VARBINARY：用于存储二进制数据，如图像或文件。BINARY是定长类型，VARBINARY是可变长度类型。
  • BLOB ：BLOB用于存储二进制大对象。
  • ENUM：枚举类型，允许在列中预定义一组值，列中的值必须是这组值中的一个。ENUM会把列表值压缩到一个或两个字节。ENUM在内部存储时，存的是整数。尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。排序是按照内部存储的整数。
  • SET：集合类型，允许在列中预定义一组值，列中的值可以是这组值中的一个或多个（以逗号分隔）。
  • JSON：用于存储JSON格式的数据，支持在MySQL中对JSON文档进行查询、插入和更新等操作

MySQL 常用 SQL？ ​

MySQL 的常用SQL语句可以大致分为几类，包括数据定义语言（DDL）、数据查询语言（DQL）、数据操纵语言（DML）和数据控制语言（DCL）。

一、数据定义语言（DDL）

DDL主要用于定义或修改数据库的结构，包括创建、修改和删除数据库和数据表等。

sql
-- 显示所有数据库
SHOW DATABASES; 
-- 创建数据库
CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 删除数据库
DROP DATABASE testdb;
-- 选择数据库
USE testdb;
-- 查看某一用户的权限
SHOW GRANTS FOR USERNAME;
-- 当前BINARY LOG文件和POSITION值
SHOW MASTER STATUS;
-- 查看创建的索引及索引类型等信息
SHOW INDEX FROM tablename;
-- 查看数据库的版本、当前登录用户和当前的数据库名称
 select user();
 select version();
 select database();
 -- 查询指定数据库中指定表的所有字段名
 SHOW FULL COLUMNS FROM TB_NAME;
 select *  from INFORMATION_SCHEMA.COLUMNS;
 desc table_name;

-- 切换BINARY LOG
FLUSH LOGS;
-- 整理表数据文件的碎片
OPTIMIZE TABLE TABLENAME;
-- 查看 TA_LHR表的建表语句
SHOW CREATE TABLE TA_LHR;
-- 查询db_name数据库里所有表的信息。
show table status from db_name
-- 查询db_name里lhrusr表的信息
show table status from db_name like 'lhrusr'\G; 
-- 查询db_name数据库里表名以uc开头的表的信息。
show table status from db_name like 'uc%' 
-- 查看表
SHOW TABLES;
-- 4. 创建数据表
CREATE TABLE students (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  name VARCHAR(100) NOT NULL,  
  age INT,  
  gender ENUM('male', 'female')  
);
-- TABB表，完整复制TABA表的结构和索引，而且不要数据
CREATE TABLE TABB LIKE TABA;
-- MySQL中如何快速地复制一张表及其数据
-- 方法一：
create table t1 as select * from t
-- 方法二：先复制表结构
CREATE TABLE t1 LIKE t ; 
-- 删除数据表
DROP TABLE students;
-- 修改数据表
// 添加字段
ALTER TABLE 表名 ADD 字段名 数据类型;
// 删除字段
ALTER TABLE 表名 DROP 字段名;
// 修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型;
// 修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
// 修改表名
ALTER TABLE 旧表名 RENAME 新表名;

二、数据查询语言（DQL）

DQL主要用于从数据库中查询数据。

sql
SELECT * FROM students WHERE age > 18;
SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC|DESC];
SELECT 字段名, 聚合函数(字段名) FROM 表名 GROUP BY 字段名;

三、数据操纵语言（DML）

DML主要用于对数据库中的数据进行增加、删除和修改操作。

sql
  INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
  UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... WHERE 条件;
  DELETE FROM 表名 WHERE 条件;

四、数据控制语言（DCL）

DCL主要用于定义数据库、表、字段的访问权限和安全级别。

sql
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
FLUSH PRIVILEGES;

MySQL有哪些常用函数？ ​

MySQL 数值型函数

函数名称作 用
CEIL 和 CEILING两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR向下取整，返回值转化为一个BIGINT
RAND生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND对所传参数进行四舍五入

MySQL 字符串函数

函数名称作 用
LENGTH计算字符串长度函数，返回字符串的字节长度
CONCAT合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT替换字符串函数
LOWER将字符串中的字母转换为小写
UPPER将字符串中的字母转换为大写
LEFT从左侧字截取符串，返回字符串左边的若干个字符
RIGHT从右侧字截取符串，返回字符串右边的若干个字符
TRIM删除字符串左右两侧的空格
REPLACE字符串替换函数，返回替换后的新字符串
SUBSTRING截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

MySQL 日期和时间函数

函数名称作 用
CURDATE 和 CURRENT_DATE两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH获取指定日期中的月份
MONTHNAME获取指定日期中的月份英文名称
DAYNAME获取指定曰期对应的星期几的英文名称
DAYOFWEEK获取指定日期对应的一周的索引位置值
WEEK获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR获取年份，返回值范围是 1970〜2069
TIME_TO_SEC将时间参数转换为秒数
SEC_TO_TIME将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME时间加法运算，在原始时间上添加指定的时间
SUBTIME时间减法运算，在原始时间上减去指定的时间
DATEDIFF获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT格式化指定的日期，根据参数返回指定格式的值

MySQL 聚合函数

函数名称作用
MAX查询指定列的最大值
MIN查询指定列的最小值
COUNT统计查询结果的行数
SUM求和，返回指定列的总和
AVG求平均值，返回指定列数据的平均值

MySQL 流程控制函数

函数名称作用
IF判断，流程控制
IFNULL判断是否为空，不为空返回参数一的值，为空返回参数二的值
CASE搜索语句

​

InnoDB的特点和优缺点？ ​

InnoDB 是MySQL 的默认事务型引擎，以其高性能、高可靠性和丰富的特性而著称。以下是InnoDB引擎的主要特性：

  1. 事务支持（Transactions）:

    • InnoDB支持ACID（原子性、一致性、隔离性、持久性）事务，可以确保数据的完整性和一致性。它使用行级锁定和MVCC（多版本并发控制）机制来实现高效的并发控制。

  2. 行级锁定（Row-level Locking）:

    • InnoDB使用行级锁定，这意味着在高并发场景下，可以同时处理更多的事务，因为锁定的范围更小，减少了锁竞争。

  3. 外键支持（Foreign Keys）:

    • InnoDB支持外键约束，可以维护表之间的引用完整性，确保数据的一致性。

  4. 自动崩溃恢复（Automatic Crash Recovery）:

    • InnoDB使用redo log和undo log来实现崩溃恢复，能够在系统崩溃后自动恢复未完成的事务，确保数据的持久性和一致性。

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

    • InnoDB通过MVCC实现读写分离，允许多个事务同时读取同一数据的不同版本，提高了并发性能。

  6. 在线索引（Online Indexes）:

    • InnoDB支持在不锁定表的情况下创建和删除索引，这意味着在构建索引期间，表仍然可以被读写。

  7. 空间数据类型（Spatial Data Types）:

    • InnoDB支持空间数据类型，如GEOMETRY、POINT、LINESTRING、POLYGON等，适用于地理信息系统（GIS）应用。

  8. 压缩（Compression）:

    • InnoDB支持表压缩，可以减少存储空间的使用，提高I/O效率。

  9. 热备份（Hot Backup）:

    • 可以在数据库运行时进行备份，不影响正常的数据读写操作。

  10. 自适应哈希索引（Adaptive Hash Index）:

    • InnoDB会自动为经常访问的索引页创建哈希索引，以加速数据的定位。

  11. 双缓冲池（Double Buffer Pool）:

    • 提高了读取效率，特别是在读取热数据时。

  12. 插入缓冲（Insert Buffer）:

    • 优化了批量插入操作的性能。

  13. 自增列优化（Auto-Increment Optimization）:

    • 支持高效的自增列插入。

  14. 全文索引（Full Text Index）:

    • InnoDB支持全文搜索，适用于文本内容的快速检索。

优点

  1. 数据安全性高：

    • 通过日志和缓冲池等机制，InnoDB能够处理各种意外情况，如系统崩溃、断电等，保证数据的完整性和一致性。

  2. 高并发性能：

    • 行级锁定和MVCC机制使得InnoDB在高并发环境下表现优异，能够支持大量用户同时访问数据库。

  3. 事务支持强大：

    • ACID事务特性使得InnoDB在处理复杂业务逻辑时更加可靠，能够确保数据的一致性和完整性。

  4. 灵活性高：

    • 支持外键约束、全文索引、自动增长列等功能，为开发者提供了更多的灵活性和便利性。

缺点

  1. 内存利用率较低：

    • InnoDB需要较大的缓冲池来提高性能，内存占用率相对较高。

  2. 索引性能在某些场景下不如MyISAM：

    • InnoDB的索引更新代价较高，且在某些查询场景下，其性能可能不如MyISAM引擎。

  3. 多核性能受限：

    • 在多核CPU环境下，InnoDB可能会遇到性能瓶颈，需要通过调整参数来提高性能。

  4. 对内存要求较高：

    • 由于索引和数据是同时存放在ibd文件中的，这导致InnoDB对内存的要求较高，可能会增加系统的负担。

InnoDB引擎适用的生产业务场景：

  • 需要事务支持（具有较好的事务特性）。
  • 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
  • 数据更新较为频繁的场景，例如：电子公告牌系统（Bulletin Board System，BBS）、社交网（Social Network Site，SNS）等。
  • 数据一致性要求较高的业务。例如：充值、银行转账等。5）硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘I/O
  • 相比MyISAM引擎，innodb引擎更消耗资源，速度没有MyISAM引擎快。

InnoDB引擎调优精要

  • 主键尽可能小，避免给SECONDARY INDEX带来过大的空间负担。
  • 避免全表扫描，因为会使用表锁。
  • 尽可能缓存所有的索引和数据，提高响应速度，减少磁盘I/O消耗。
  • 在执行大量插入操作的时候，尽量自己控制事务而不要使用AUTOCOMMIT自动提交。有开关可以控制提交方式。
  • 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
  • 避免主键更新，因为这会带来大量的数据移动。

InnoDB引擎四大特性包括哪些？ ​

  • 插入缓冲（insert buffer)

    只对于非聚集索引中的非唯一索引插入和更新有效**，对于每一次的插入（对于删除、更新操作同样有效）不是直接写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，减少随机IO带来性能损耗。

  • 二次写(double write)

    InnoDB 索引页一般 16KB 大小，而操作系统写文件以 4KB 为单位，这就导致同一页需要分四块分别写入。此时就存在写完一块系统崩溃或者断电等特殊情况，此时就导致写入数据不完整的问题。二次写就是为了解决该问题，double write 分为两部分，一部分 doublewrite buffer，其大小 2MB，另一部分是磁盘上共享表空间中连续的 128 个页，也是 2MB。每次刷盘时：先将数据写入 doublewrite buffer，之后通过doublewrite buffer再分两次，每次写入1MB到共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。如果操作系统在将页写入磁盘的过程中崩溃，InnoDB 重启发现页数据损坏后，可以从共享表的 double write 中找到副本，用于数据恢复。

  • 自适应哈希索引

    当InnoDB注意到某些索引值被使用非常频繁时，它会在内存中基于BTree索引之上再创建一个哈希索引，这是一个全自动的，内部的行为，用户无法控制或者配置，可以选择关闭这个功能innodb_adaptive_hash_index=OFF，默认为ON。

  • 预读

    当 InnoDB 预计某些页很快就要被访问时，会异步加载对应页数据到缓冲池。空间局部性：如果某块内存被访问，那么它周围的内存大概率也会被访问。InnoDB 采用两种预读算法提高 I/O 性能：线性预读 和 随机预读

    • 线性预读：以块为单位，一块等于64页。如果某一块中的被顺序读取的页数超过预定值，则 InnoDB 将会异步的将下一块读取到 buffer pool 中。

    • 随机预读：以页为单位，当某一个块中的一些页在 buffer pool 中被发现时，InnoDB 会将该块中的剩余页一并读到 buffer pool 中，目前已废弃。

InnoDB存储引擎体系结构是怎样的？ ​

InnoDB存储引擎的体系结构是一个复杂而高效的系统，它结合了内存结构和磁盘结构，以支持MySQL数据库的事务处理、并发控制、恢复机制等核心功能。

一、内存结构

InnoDB存储引擎的内存结构主要由多个内存块组成，这些内存块共同构成了一个大的内存池，负责缓存数据、管理事务等任务。内存池的主要组成部分包括：

  1. Buffer Pool（缓冲池）

    • 作用：Buffer Pool是InnoDB中最重要的内存部分，它用于缓存磁盘上的数据页和索引页，以便快速访问。当数据库读取数据时，如果数据页已经在Buffer Pool中，则直接读取；否则，从磁盘读取后存入Buffer Pool。

    • 大小：可以通过innodb_buffer_pool_size参数设置Buffer Pool的大小。

    • 缓存页类型：包括索引页、数据页、undo页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB存储的锁信息、数据字典信息等。

    • 管理：Buffer Pool通过LRU（最近最少使用）算法、Free List（空闲链表）和Flush List（脏页链表）等机制进行管理。LRU算法用于管理热点数据，Free List用于记录空闲的缓存页，Flush List用于记录已修改但尚未刷新到磁盘的脏页。

  2. Redo Log Buffer（重做日志缓冲）

    • 作用：用于暂存重做日志信息，这些信息最终会被写入到磁盘上的重做日志文件（ib_logfile0、ib_logfile1等）中。重做日志记录了数据的变更信息，用于在系统崩溃后恢复数据。

  3. Doublewrite Buffer（双写缓冲）

    • 作用：为了保障InnoDB存储引擎操作数据页的可靠性，Doublewrite Buffer在内存中为脏页提供了一个副本，确保在脏页写入磁盘的过程中，如果发生崩溃，可以通过Doublewrite Buffer中的副本恢复数据。

  4. Additional Memory Pool（附加内存池）

    • 作用：用于存储InnoDB存储引擎的额外数据结构，如锁信息、内部哈希表等。

二、磁盘结构

InnoDB存储引擎的磁盘结构主要由表空间组成，表空间是InnoDB存储引擎逻辑结构的最高层，所有的数据、索引、日志等信息都存储在表空间中。表空间分为以下几种类型：

  1. 系统表空间（ibdata1文件）

    • 作用：存储InnoDB的内部数据字典、undo日志、双写缓冲等信息。

    • 特点：在MySQL 5.6.6及之前的版本中，系统表空间是默认的表空间类型，所有表的数据和索引都存储在同一个系统表空间中。从MySQL 5.6.6版本开始，可以通过设置innodb_file_per_table参数启用独立表空间模式，为每个表创建独立的表空间文件（.ibd文件）。

  2. 独立表空间（.ibd文件）

    • 作用：在启用独立表空间模式后，每个表都会有一个独立的表空间文件，用于存储该表的数据和索引。

  3. 临时表空间（ibtmp1文件）

    • 作用：用于存储临时表和排序操作产生的临时数据。

  4. Undo表空间

    • 作用：专门用于存储undo日志，以便在系统崩溃时恢复数据或实现多版本并发控制（MVCC）。

  5. 通用表空间

    • 作用：允许将多个表的数据和索引存储在同一个表空间中，可以根据需要灵活配置和管理。

三、后台线程

InnoDB存储引擎是一个多线程的存储引擎，拥有多个后台线程来处理不同的任务，主要包括：

  1. Master Thread（主线程）

    • 作用：负责将Buffer Pool中的脏页异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、undo页的回收等。

  2. IO Thread（IO线程）

    • 作用：负责处理磁盘IO请求，包括读写请求和日志IO请求。

  3. Purge Thread（清除线程）

    • 作用：负责回收已经使用并分配的undo页，以释放空间。

  4. Page Cleaner Thread（页面清理线程）

    • 作用：在InnoDB 1.2.x版本引入，专门负责脏页的刷新操作，以减轻Master Thread的工作负担。

InnoDB 逻辑存储结构? ​

InnoDB的逻辑存储结构主要由以下几个部分组成：表空间（Tablespace）、段（Segment）、区（Extent）、页（Page）和行（Row）。

  1. 表空间（Tablespace）

    • 定义：表空间是InnoDB逻辑结构的最高层，所有的数据都放在表空间中。一个MySQL实例可以对应多个表空间，用于存储记录、索引等数据。

    • 类型：

      • 共享表空间：默认情况下，InnoDB存储引擎有一个共享表空间ibdata1，即所有的数据都放在这个表空间中。

      • 独立表空间：通过innodb_file_per_table参数控制，使得每张表都有自己的独立表空间文件（以.ibd为后缀）。独立表空间文件只存放表的数据、索引和插入缓冲Bitmap页，其他如回滚信息、插入缓冲索引页、系统事务信息等还是存放在共享表空间中。

  2. 段（Segment）

    • 定义：表空间由各个段组成，例如数据段、索引段、回滚段等。在InnoDB存储引擎中，对段的管理是由存储引擎自身完成的，DBA无需对其进行控制。

    • 类型：

      • 数据段：B+树的叶子节点，存储实际的数据记录。

      • 索引段：B+树的非叶子节点，存储索引信息。

      • 回滚段：用于存储undo日志，以支持事务的原子性和持久性。

  3. 区（Extent）

    • 定义：区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎会一次性从磁盘申请4-5个区。

    • 页的数量：在默认情况下，InnoDB存储引擎的页的大小为16KB，即一个区中应有64个连续的页。但是，页的大小可以通过参数innodb_page_size进行调整，此时区中的页数也会相应变化。

  4. 页（Page）

    • 定义：页是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页也常被称为块（block）。

    • 类型：常见的页类型包括数据页（B-tree Node）、undo页（undo Log Page）、系统页（System Page）、事务数据页（Transaction System Page）、插入缓冲位图页（Insert Buffer Bitmap）、插入缓冲空闲列表页（Insert Buffer Free List）、未压缩的二进制大对象页（Uncompressed BLOB Page）和压缩的二进制大对象页（Compressed BLOB Page）等。

    • 存储内容：页中存储了实际的数据记录或索引信息，以及页头、页尾等管理信息。

  5. 行（Row）

    • 定义：InnoDB存储引擎是面向行的（row-oriented），即数据是按行进行存放的。

    • 存储方式：每个页存放的行记录数量有硬性定义，最多允许存放一定数量的行记录（具体数量取决于页的大小和行格式）。每行记录都会包含一些隐藏列，如trx_id（事务ID）和roll_pointer（回滚指针），用于支持事务的并发控制和恢复。

MySQL 有几种表空间模式？ ​

在MySQL数据库中，特别是使用InnoDB存储引擎时，表空间模式决定了数据如何被物理地存储在磁盘上。主要有两种表空间模式：共享表空间模式和独立表空间模式。

共享表空间模式（Shared Tablespace Mode）

在共享表空间模式下，InnoDB引擎的表数据和索引并不单独存储为文件，而是存储在InnoDB的共享表空间中。这个共享表空间通常是一个或多个名为ibdata的文件（如ibdata1），它包含了数据库中所有InnoDB表的数据和索引，以及MVCC（多版本并发控制）所需的信息、撤销日志（undo logs）等。

特点：

  • 集中存储：所有数据和索引都存储在一个或少数几个文件中，便于管理。
  • 空间管理：随着数据的增加，ibdata文件会动态增长。但是，当表中的数据被删除时，空间并不会立即释放回文件系统，这可能导致文件占用大量磁盘空间，即使数据库中的数据并不多。
  • 备份与恢复：备份和恢复整个数据库时较为方便，因为只需要备份和恢复这些共享表空间文件。但是，单独恢复某个表则比较复杂。

优点：

  • 表空间可以分成多个文件存放到各个磁盘上，大小不受磁盘大小限制
  • 数据和文件存放在一起方便管理。

缺点：

  • 所有的数据索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间混合存储，当数据量特别大的时候，表做了大量删除操作后表空间会有大量空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。
  • 共享表空间分配后不能回缩：当临时建索引或创建一个临时表后，表空间被扩大后，即使删除相关表也没办法回收那部分空间。
  • 进行数据库备份很慢

独立表空间模式（Per-Table Tablespace Mode）

在独立表空间模式下，InnoDB引擎的每个表都有自己的表空间文件，这些文件以.ibd为扩展名。每个.ibd文件都包含了对应表的数据和索引。这种模式从MySQL 5.6版本开始成为默认设置。

特点

  • 分散存储：每个表的数据和索引都存储在自己的文件中，便于管理和维护。
  • 空间回收：当表中的数据被删除时，表空间中的空间可以被回收并用于其他目的，减少了空间的浪费。
  • 备份与恢复：可以单独备份和恢复某个表，提高了备份和恢复的灵活性。
  • 文件数量：如果数据库中有大量的表，那么将产生大量的.ibd文件，这可能对文件系统造成一定的压力。

优点

  • 当 truncate 或者 drop 一个表时可以释放磁盘空间。如果不是独立表空间，truncate 或者 drop 一个表只是在 ibdata 文件内部释放，实际 ibdata 文件并不会缩小，释放出来的表空间只能让其他 InnoDB 引擎的表来使用
  • 对立表空间下，truncate table 操作会更快
  • 独立表空间下，可以为每个表自定义存储位置，通过命令：CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory 来实现。有时将部分热表放在不同的磁盘可以提升IO性能
  • 可以回收表空间碎片，比如在一个大的Delete 操作之后
  • 可以移动单个表，不需要移动整个数据库
  • 可以复制单独的表到另外一个实例
  • 能使用Barracuda文件格式，这个文件格式有压缩和动态行模式的特色
  • 可以提高recovery的成功率，减少损坏错误发生恢复的时间。
  • 可以快速的备份，恢复单个表。
  • 可以从备份中单独分离出一个表
  • 可以不通过MySQL的情况下，通过文件系统直接观察表的大小。
  • 当innodb_flish_method设置为O_DIRECT，通常linux文件系统不允许并发的写入同一个文件。使用独立表空间模式就会有性能提升。
  • 不启用独立表空间模式，数据都会放在系统表空间中，最大64TB，如果使用独立表空间模式每个表可以64TB。
  • 运行OPTIMEIZE TABLE，压缩或者重建创建表空间。运行OPTIMIZE TABLE InnoDB会创建一个新的ibd文件。当完成时，老的表空间会被新的代替。

缺点

  • 每个表或许有许多没有用到的磁盘空间，如果没有做好管理，会产生大量空间浪费，表空间中的空间只能被这个表使用
  • fsync操作必须在每个表上都运行一遍，多个表的IO操作无法合并为一个IO操作，增加了许多额外的fsync操作
  • mysqld必须保证每个表都有一个打开的文件句柄，表太多会影响性能。会消耗很多文件描述
  • 当drop表空间的时候会扫描buffer pool，如果太大会比较耗时。这个操作还会增加一个内部锁，可能延迟其他操作
  • 如果很多表都增长迅速，那么会产生更多分裂操作（表空间扩充），会导致drop表和表扫描性能下降
  • innodb_autoextend_increment 该参数指定当文件满了之后增长的空间。该参数在独立表空间模式下无效
  • 当单表过大时，占用空间过大，导致存储空间不足时，只能通过操作系统层面解决

MySQL 如何切换表空间模式？ ​

MySQL允许通过修改配置文件中的innodb_file_per_table选项来切换这两种表空间模式。

  • 设置为1时，启用独立表空间模式，从MySQL 5.6版本开始成为默认设置。
  • 当设置为0时，使用共享表空间模式。

修改这个设置后，需要重启MySQL服务才能生效。而且，这个设置只影响之后创建的表，已经存在的表（在修改设置之前创建的）的存储方式不会改变。

对于已经存在的表，如果需要更改其存储方式，可以使用ALTER TABLE ... ENGINE=InnoDB;命令来重建表，但这需要谨慎操作，因为这会复制表中的所有数据。

InnoDB引擎类型的表有几类表空间？ ​

InnoDB引擎类型的表在MySQL中支持多种表空间类型，这些表空间类型有助于数据的组织、管理和优化。以下是InnoDB引擎类型表的主要表空间类型：

  1. 系统表空间（System Tablespace）

    • 定义：系统表空间是InnoDB存储引擎的默认表空间，主要用于存储系统表和一些特殊表的数据，如mysql.user、mysql.db等系统表，以及InnoDB数据字典（在MySQL 8.0之前）和doublewrite缓冲存储区（在MySQL 8.0.20之前）。

    • 文件：默认情况下，系统表空间存储在名为ibdata1的共享文件中，该文件通常位于MySQL的数据目录下。

    • 特点：即使启用了innodb_file_per_table参数，系统表空间仍然会存储一些全局性的信息，如撤销信息、系统事务信息等。

  2. 独立表空间（File-Per-Table Tablespace）

    • 定义：当启用innodb_file_per_table参数时，InnoDB会为每个表创建一个独立的表空间文件（.ibd文件），用于存储该表的数据和索引。

    • 文件：每个表的表空间文件根据其表名命名，如table_name.ibd。

    • 特点：独立表空间便于管理和维护，可以更方便地备份、恢复或迁移单个表。同时，它也有助于更有效地使用存储空间，特别是在有大量小表或经常进行删除和重建表时。

  3. 通用表空间（General Tablespace）

    • 定义：通用表空间是MySQL 5.7及更高版本中引入的一个功能，允许将数据库中指定的几张表的数据写入同一个表空间文件。

    • 创建：通过CREATE TABLESPACE语句创建通用表空间，并通过ADD DATAFILE子句指定表空间文件。

    • 使用：在创建表时，可以通过TABLESPACE选项将表指定到已创建的通用表空间中。

  4. 撤销表空间（Undo Tablespaces）

    • 定义：撤销表空间用于存储撤销日志（undo logs），这些日志对于实现事务的原子性、一致性、隔离性和持久性（ACID属性）至关重要。

    • 文件：撤销表空间文件通常以.ibu为扩展名，并位于MySQL的数据存储路径下。

    • 管理：在MySQL 8.0.14及更高版本中，可以通过CREATE UNDO TABLESPACE语句创建撤销表空间，并通过ALTER UNDO TABLESPACE和DROP UNDO TABLESPACE语句进行管理和删除。

  5. 临时表空间（Temporary Tablespaces）

    • 定义：临时表空间用于存储临时表的数据，这些表在查询过程中可能由MySQL自动创建，如大表的JOIN查询时。

    • 类型：包括会话临时表空间和全局临时表空间。会话临时表空间存储用户创建的临时表和内部临时表的信息，而全局临时表空间则用于存储其他类型的临时数据。

MySQL 的锁策略有什么？ ​

一、显式锁

显式锁是直接在SQL语句中体现出来的锁，常见的显式加锁语句主要有：

行锁（Record Locks）：

  • MySQL中默认的锁机制之一，在事务中需要对行数据进行更新或删除时，会对该行数据加上锁，其他事务需要对这一行数据操作时，必须等待锁被释放。

  • 行锁是一种细粒度的锁控制，只锁住需要修改的行，而不是整个表。

  • 类型：

    • 共享锁（Shared Locks, S锁、读锁）：

      • 允许事务读取一行数据，但不允许修改。

      • 多个事务可以同时获得同一行数据的共享锁。

    • 排他锁（X锁、写锁)：
      • 允许事务修改或删除一行数据。
      • 排他锁会阻止其他事务对该行数据加任何类型的锁（共享锁或排他锁）。

  • 加锁方法：

    • InnoDB引擎默认的修改数据语句，update,delete,insert 都会自动给涉及到的数据加上排他锁。
    • select 语句默认不会加任何锁类型。
    • 如果加排他锁可以使用select ...for update 语句
    • 加共享锁可以使用 select ... lock in share mode语句

  • 特点：

    • 加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据。
    • 写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但是读锁不能插入到写锁前面，写锁和读锁优先级的问题是可以通过参数调节的：max_write_lock_count和low-priority-updates
    • InnoDB的行锁是基于索引的，只有通过索引条件检索数据才使用行级锁，否则，使用表锁。
    • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，如果多个session是访问不同行的记录， 但是如果是使用相同的索引键， 后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁。

表锁（Table Locks）：

  • MySQL中较低级别的锁机制，它锁住整个表，当一个事务对表进行操作时，其他事务无法对这个表进行任何操作，即使这些操作并不冲突。
  • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低，是MySQL中最基本的锁策略。
  • **表级锁定主要分为两种类型，一种是读锁定，另一种是写锁定。**一个用户在对表进行写操作前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获取读锁，读锁之间不相互阻塞。
  • 使用表锁会对其他事务产生较大的阻塞，因此在实际应用中要慎重使用。
  • 常见的SQL命令如LOCK TABLES ... WRITE;可以显式地对表加锁。
  • 为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，意向锁是数据库隐式完成，两种意向锁都是表锁：
    • 意向共享锁（IS）： 事务在给一个数据行加共享锁前必须先取得该表的IS锁。
    • 意向排他锁（IX）： 事务在给一个数据行加排他锁前必须先取得该表的IX锁。

全局锁

​ 主要用于逻辑备份，如FLUSH TABLES WITH READ LOCK命令，会阻塞所有表的写操作，直到全局锁被释放。

元数据锁（Metadata Locks, MDL）：

​ 在进行增删改查的时候会加MDL读锁，防止表结构被修改；在修改表结构时，会加MDL写锁，阻止其他线程对表进行读写操作。

二、隐式锁

隐式锁不是直接在SQL语句中体现出来的，而是由MySQL的存储引擎（如InnoDB）自动管理的锁。常见的隐式锁包括：

间隙锁（Gap Locks）：

  • 锁定索引范围之间的间隙，但不包含记录本身，防止其他事务在该范围内插入新行。
  • 间隙锁主要用于在可重复读（Repeatable Read）隔离级别下，防止幻读（Phantom Read），即在同一事务中，前后两次查询同一个范围的数据行数不一致的情况。
  • 有两种方式显式关闭gap锁。
    1. 将事务的隔离级别设置为 READ_COMMITTED
    2. 将参数 innodb_locks_unsafe_for_binlog 设置为 1。
  • 插入意向锁（Insert Intention Locks）：

    • 是一种特殊的间隙锁，用于支持多个事务在同一个索引间隙中插入新行。

    • 插入意向锁之间不会相互阻塞，只要它们插入的位置不冲突。

临键锁（Next-Key Locks）：

  • 是记录锁和间隙锁的组合，锁定一个范围，并包括记录本身。
  • 在InnoDB的默认隔离级别（REPEATABLE READ）下，行锁默认使用的就是临键锁。
  • InnoDB对于行的查询使用 next-key lock
  • 如果操作的索引是唯一索引或主键，InnoDB会对临键锁进行优化，将其降级为记录锁，即仅锁住索引本身，而不是范围。

三、乐观锁

乐观锁是一种与数据库中的锁机制无关的并发控制策略，它通过在进行写操作前，检查数据是否被其他事务修改，来避免脏写的问题。

  • 乐观锁通常通过应用程序逻辑实现，例如使用数据表中的版本号或时间戳字段。
  • 在更新记录时，检查版本号或时间戳是否一致，如果一致则更新并增加版本号或时间戳，否则回滚操作。

四、锁策略的选择与优化

  • 根据应用场景选择合适的锁类型：如果并发修改的数据行较少，可以选择行级锁；如果修改的数据行较多，可以选择表级锁。
  • 避免长时间持有锁：尽量缩短事务的执行时间，减少对同一资源的锁定时间。
  • 使用合适的索引：通过索引可以减少锁的范围，提高并发性能。
  • 设计避免死锁的策略：例如，使用超时机制，设置事务的超时时间；或者使用死锁检测算法，一旦检测到死锁就立即解决。

MySQL 临键锁的特点？ ​

临键锁（Next-Key Lock）是InnoDB存储引擎在可重复读（REPEATABLE READ）隔离级别下使用的一种锁定机制。临键锁存在于唯一索引和非唯一索引中，它结合了记录锁（Record Lock）和间隙锁（Gap Lock），同时锁住该范围中数据，并锁住数据前面的间隙Gap。用来防止幻读（Phantom Read）的发生，间隙锁是临键锁的一种特殊形式。

  • 记录锁（Record Lock）：锁定索引记录本身。
  • 间隙锁（Gap Lock）：间隙锁存在于非唯一索引中，锁定索引记录之间的间隙，而不是记录本身。确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在可重复读（REPEATABLE READ）或串行化（SERIALIZABLE）隔离级别下，间隙锁会被使用。

特点：

锁定范围:

  • 临键锁不仅锁定索引记录本身（记录锁），还锁定索引记录之间的间隙（间隙锁）。它会锁定一个范围，而不仅仅是单个数据行。用于锁定一段左开右闭的索引区间。

防止幻读:

  • 在可重复读隔离级别下，临键锁可以防止其他事务在当前事务的查询范围内插入新的行，从而避免幻读现象。幻读是指在一个事务中多次执行相同的查询，但每次返回的结果集不同，因为有其他事务插入了新的符合条件的数据。

锁定粒度:

  • 临键锁提供了比单纯记录锁更细粒度的锁定，因为它也考虑到了索引值之间的空隙。这有助于减少锁定冲突，提高并发性能。

索引依赖:

  • 临键锁依赖于索引来工作。如果没有合适的索引，MySQL可能会选择使用表级锁来代替，这会导致更大的锁定范围和更低的并发性。

  • 在唯一索引上，临键锁可能退化为仅对索引记录的行级锁（如果查询条件匹配唯一索引中的记录）。

锁定模式:

  • 临键锁可以是共享的（S锁）或排他的（X锁）。共享锁允许多个事务同时读取同一行，而排他锁阻止其他事务对锁定的行进行任何操作，直到持有锁的事务释放它。

锁定释放:

  • 临键锁通常在事务提交或回滚时被释放。如果事务需要长时间保持锁定状态，可能会影响其他事务的并发性。

适用场景:

  • 临键锁的主要作用是为了防止出现幻读（Phantom Read），用在Repeated-Read（简称RR）隔离级别下。在Read-Commited（简称RC）下，一般没有间隙锁，有外键情况下例外。

  • 当使用唯一索引进行范围检索，或者是唯一检索但检索结果不存在（试图锁住不存在的数据）时，会产生临键锁。

  • 当使用普通索引检索时，不管是何种查询，只要加锁，都会产生临键锁（在某些情况下会退化为间隙锁）。

  • 在可重复读（REPEATABLE READ）隔离级别下，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。

  • 间隙锁还用于恢复和复制。

锁定升级:

  • 在某些情况下，如果查询涉及大量的行，InnoDB可能会将多个临键锁合并为一个更大的锁定范围，以减少锁的数量并提高效率。

锁定监控:

  • 可以通过SHOW ENGINE INNODB STATUS命令查看当前活动的锁定信息，包括哪些事务持有哪些类型的锁，以及它们影响的范围。

注意事项

  • 在使用间隙锁和临键锁时，需要谨慎评估并发控制的成本和性能影响。
  • 如果确定不需要间隙锁或临键锁，可以考虑将事务隔离级别设置为读已提交（READ COMMITTED），以减少锁的使用和性能影响。
  • 在设计和执行SQL语句时，需要考虑到并发操作可能产生的影响，并选择合适的事务隔离级别和锁机制来保证数据的一致性和完整性。

InnoDB死锁如何解决？ ​

死锁是指多个事务在同一资源上相互占用并请求锁定对方占用的资源而导致恶性循环的现象。当多个事务试图以不同顺序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。

InnoDB内置的死锁检测与处理机制

  • 回滚事物

    1. 检测死锁：InnoDB会定期检查事务之间的锁依赖关系，以检测是否存在死锁。
    2. 选择牺牲者：一旦检测到死锁，InnoDB会基于内部算法选择一个事务作为牺牲者，旨在最小化对系统的影响。这个选择可能会考虑事务的持续时间、锁定的资源数量等因素。通常是选择持有最少行锁或最小事务量的事务。回滚后，锁将被释放，其他事务可以继续执行。
    3. 终止牺牲者事务：InnoDB会回滚牺牲者事务，释放其持有的所有锁，并返回一个错误，如“Deadlock found when trying to get lock; try restarting transaction”。
    4. 恢复死锁：一旦牺牲者事务被终止，其他事务可以继续执行，死锁被打破。

  • 超时处理

    除了自动检测死锁外，InnoDB还允许设置锁等待超时时间（innodb_lock_wait_timeout）。如果事务在指定的时间内无法获取所需的锁，InnoDB将回滚该事务并返回错误。这可以防止事务长时间挂起并影响系统的整体性能。

虽然InnoDB的死锁检测机制非常有效，但它并不能完全消除死锁的发生。因此，为了最大程度地减少死锁，开发者仍然需要采取一些预防措施，如：

优化应用程序设计以减少死锁

  1. 统一锁定顺序：确保所有事务都按照相同的顺序锁定资源，这样可以避免因为资源锁定顺序不同而导致的死锁。
  2. 减少锁定时间：尽量减少事务持有锁的时间，以降低死锁风险。可以通过优化事务逻辑、减少不必要的操作，使用索引来加速查询等方式来实现。
  3. 使用较小的锁粒度：在可能的情况下，使用行级锁而不是表级锁。行级锁可以减少锁定的资源范围，从而降低死锁的可能性。
  4. 优化事务设计：设计事务时，尽量使其简短且只涉及必要的操作，尽量避免在事务中执行复杂的查询或跨多个表的操作，较小的事务可以更快提交，及时释放资源，降低死锁概率。
  5. 使用合适的隔离级别：如果使用锁定读取（如SELECT ... FOR UPDATE），可以尝试将隔离级别降低到读已提交（READ COMMITTED），以减少锁的竞争。

手动处理死锁

  1. 查看死锁信息：可以使用SHOW ENGINE INNODB STATUS命令来查看死锁的信息，包括死锁相关事务的详细信息、引发死锁的SQL语句、事务已经获得的锁、正在等待的锁等。
  2. 终止死锁事务：如果发现死锁，可以使用KILL命令来终止其中一个事务，以解决死锁问题。例如，KILL 12345;其中12345是死锁事务的ID。

配置参数以调整死锁处理策略

  1. innodb_deadlock_detect：可以启用或禁用死锁检测机制。默认情况下，死锁检测是启用的。如果禁用死锁检测，InnoDB将不会自动检测和处理死锁，而是依靠锁等待超时（由innodb_lock_wait_timeout参数控制）来处理死锁。
  2. innodb_lock_wait_timeout：可以设置锁等待超时时间，以避免长时间等待锁而导致的死锁。例如，可以将其设置为10秒：SET innodb_lock_wait_timeout = 10;
  3. innodb_print_all_deadlocks：可以启用此参数，将有关所有死锁的信息打印到MySQL错误日志中。这有助于调试和排查死锁问题。完成调试后，建议禁用此选项以避免不必要的日志记录。

此外，通过监控和分析死锁日志，开发者可以了解死锁发生的具体情况，并据此调整数据库设计和事务处理策略，以进一步降低死锁的发生概率。

综上所述，解决InnoDB死锁问题需要从多个方面入手，包括利用InnoDB内置的死锁检测与处理机制、优化应用程序设计以减少死锁、手动处理死锁以及配置相关参数以调整死锁处理策略等。

MySQL有哪些命令可以查看锁？ ​

  • 查看表级锁
sql
-- SHOW FULL PROCESSLIST 比 SHOW PROCESSLIST提供更多信息
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

显示哪些线程正在运行。如果当前用户有SUPER权限，那么就可以看到所有线程。如果有线程正在UPDATE或者INSERT某个表，那么进程的status为updating或者sending data。

  • 查看表状态
sql
show open tables

查看当前有哪些表是打开的。in_use列表示有多少线程正在使用某张表，name_locked表示该表是否被锁，一般发生在使用DROP或RENAME命令操作这张表时。所以这条命令不能查询到当前某张表是否有死锁，谁拥有表上的这个锁等信息。

  • 查看服务器状态
sql
show status like '%lock%'
  • 查看InnoDB行级锁和表级锁

这是查看InnoDB引擎状态信息的最强大命令之一，其中包含了锁等待、死锁、事务等信息。在输出中，寻找LATEST DETECTED DEADLOCK（如果有死锁发生的话）、TRANSACTIONS（事务信息）和LATEST FOREIGN KEY ERROR（如果有外键错误）等部分。锁等待信息通常位于TRANSACTIONS部分下的WAITING FOR THIS LOCK TO BE GRANTED子部分。

sql
SHOW ENGINE INNODB STATUS\G;
  • 查询information_schema用户下的表
sql
-- 视图提供了当前被锁定的每个事务的锁信息。
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 视图展示了锁等待的情况，即哪些事务在等待获取其他事务持有的锁。
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.innodb_trx;

MySQL insert加锁流程？ ​

一、加锁前的准备

  1. 开启事务：在执行INSERT操作之前，通常会开启一个事务。事务是一系列操作的集合，这些操作要么全部成功，要么全部失败，以保持数据的一致性。
  2. 选择锁类型：MySQL在INSERT操作中主要使用行级锁，以允许高并发地访问表中的其他行。但在某些情况下，如使用特定语句或表结构时，也可能涉及表级锁。

二、加锁流程

  1. 加插入意向锁：
  • 在插入记录之前，MySQL会首先向插入记录所在的位置申请一个插入意向锁（Insertion Intention Gap Lock）。这个锁是一个特殊的间隙锁，用于表示有事务打算在这个间隙中插入记录。
  • 插入意向锁并不会真正阻塞其他事务的插入操作，只要它们不是插入到同一行。多个事务可以在相同的间隙上持有插入意向锁而不会冲突。
  • 如果该间隙已被加上了GAP锁（间隙锁）或 Next-Key 锁（含GAP 锁），则加锁失败，等待间隙释放。
  • 如果没有，则加插入意向锁成功，表示可以插入。

  1. 判断唯一键：

    • 如果插入的记录包含唯一键，MySQL会进行唯一性约束检查。

    • 如果不存在相同的键值，则准备插入记录。

    • 如果存在相同的键值，则根据该键值的锁状态（如是否已被其他事务锁定）来决定是否允许插入。

      • 如果没有锁， 判断该记录是否被标记为删除

        • 如果标记为删除，说明删除事务已经提交，数据页上的记录还没来得及 purge，这时尝试加S锁；

        • 如果没有标记删除，说明数据已存在，本次插入会导致唯一键冲突，则报 1062 duplicate key 错误；

      • 如果有锁，说明该记录正在被处理（正在被其他事务新增、删除或更新），且其他事务还未提交，则当前事务尝试加S锁后会进入阻塞，等待其他事务释放记录X锁。

  2. 插入记录并加X锁：

    • 一旦通过了唯一性约束检查（或在没有唯一键的情况下），MySQL会执行插入操作。

    • 在插入新记录时，MySQL会对该记录加上一个排他锁（X锁），以防止其他事务同时修改或删除这条记录。

  3. 提交事务：

    • 当事务提交时，MySQL会释放之前获取的所有锁，并将插入的记录永久保存到数据库中。

三、注意事项

  • INSERT操作会对新插入的记录加行锁写锁；注意：区别于执行update，执行insert时这里加的是X锁，并非next-key lock，因此不会阻塞其他的事务对gap区间的插入操作；
  • 对于唯一索引，发生唯一键冲突时，当前事务会先尝试在这条记录上加读锁S锁；加S锁的机制可能会导致死锁：即A线程占用记录的X锁，B、C阻塞，它们需要先获取S锁再获取X锁执行更新；当A释放X锁时，B、C同时拿到S锁（共享锁），但互相都无法继续获取X锁（S锁与X锁互斥），导致死锁；

MySQL update 加锁的过程？ ​

一、加锁的目的

  • 保证数据一致性：加锁可以防止多个事务同时修改同一数据，从而避免数据不一致的情况。
  • 支持并发访问：通过合理的加锁机制，可以允许多个事务并发地访问数据库，同时保证数据的安全性。

二、加锁的流程

  1. 开启事务：在执行UPDATE操作之前，需要开启一个事务，事务是一个不可分割的工作单元，包含了一系列的操作。

  2. 获取锁：

    • 在执行UPDATE语句时，MySQL会自动对需要更新的行或表加锁，加锁操作由MySQL内部完成，用户无需显式指定。如果锁已被其他事务持有，则当前事务会阻塞，直到锁被释放。

    • 锁的类型选择：MySQL会根据具体的场景和语句自动选择适当的锁级别。当更新操作只需要锁定部分数据行时，会获取行级锁；当更新操作需要锁定整个表时，会获取表级锁

    • 锁的兼容性：不同类型的锁之间具有不同的兼容性。排他锁（X锁）与其他任何类型的锁都不兼容，而共享锁（S锁）与共享锁之间是兼容的。

sql
-- 获取表锁
LOCK TABLES table_name [READ | WRITE];
-- 获取行锁
SELECT * FROM table_name WHERE key = value FOR [UPDATE | SHARE];

三、执行更新操作：

  • 在获取到锁之后，可以执行UPDATE语句来更新数据。
  • 更新操作会修改被锁定的行的数据。

四、提交或回滚事务：

  • 在更新操作完成后，可以选择提交事务或回滚事务。
  • 提交事务会释放之前获取的锁，并将更新操作的结果持久化到数据库中。
  • 回滚事务会撤销更新操作，并释放之前获取的锁。

五、释放锁

  • 在事务提交或回滚后，需要释放之前获取的锁。释放锁的操作由 MySQL 自动完成。

MySQL索引特点？ ​

一、索引类型多样

MySQL支持多种类型的索引，以满足不同的查询需求：

  1. B+Tree索引：

    • 主键索引：设定为主键后，数据库会自动建立索引，不能为空。
    • 唯一索引：索引列的值必须唯一，但允许有空值。
    • 普通索引：可以为空，可以重复。
    • 前缀索引：只考虑列值的前几个字符。
    • 组合索引：一个索引包含多个列，遵循最左前缀原则。
    • 全文索引：通过构建倒排索引来实现，支持全文搜索和模糊查询。

  2. 聚簇索引：

    • InnoDB默认支持聚簇索引，数据按照主键构造B+树，叶子节点存放整张表的行记录数据。
    • 优点：对于范围查询或按索引排序，聚簇索引通常具有更好的性能。
    • 缺点：插入速度严重依赖于插入顺序。

  3. 非聚簇索引：

    • 数据是分离的，辅助索引叶子节点存储的是主键值，通过主键值再找到数据行的数据页。
    • 优点：对于单列的查找和特定的连接操作可能更高效。
    • 缺点：范围和排序查询性能较差。

  4. Hash索引：

    • 通过将索引列的值通过哈希函数映射到一个哈希表的桶中，实现快速的索引查找。
    • 缺点：在范围查询、排序等场景下表现不佳。

  5. 空间索引：

    • 支持对空间数据进行快速的空间查询和分析，可用于存储和查询地理位置信息。

二、作用显著

索引在MySQL中发挥着至关重要的作用，主要体现在以下几个方面：

  1. 加速查询：通过索引，数据库可以快速定位到需要的数据行，而无需扫描整个表。
  2. 保证数据唯一性：唯一索引确保列中的所有值都是唯一的，防止数据重复。
  3. 优化性能：索引有助于数据库优化器更好地选择执行计划，提高查询的整体性能。

三、创建原则

在创建索引时，需要遵循一些原则以确保索引的有效性和性能：

  1. 选择适当的列：优先考虑在WHERE子句、JOIN子句和ORDER BY子句中出现的列上创建索引。
  2. 考虑列值的分布：索引的列的基数越大（即列中的不同值越多），索引的效果越好。
  3. 使用短索引：对字符串列进行索引时，可以指定一个前缀长度以节省索引空间并提高查询速度。
  4. 避免过度索引：只保持所需的索引，因为每个额外的索引都会占用额外的磁盘空间并降低写操作的性能。

四、对数据库性能的影响

索引对数据库性能的影响是双面的：

  1. 提高查询性能：索引可以显著加快SELECT查询的速度，特别是在处理大量数据时。
  2. 降低更新性能：每次对表进行INSERT、UPDATE和DELETE操作时，索引也需要更新，这增加了额外的维护成本并可能降低这些操作的速度。

综上所述，MySQL索引具有类型多样、作用显著、创建原则明确以及对数据库性能有双重影响等特点。在设计和使用索引时，需要充分考虑这些因素以确保索引的有效性和性能。

MySQL 创建使用索引的原则？ ​

使用索引是优化数据库查询性能的重要手段，但索引并非越多越好，不合理的索引不仅不能提升性能，反而可能降低数据库的性能并增加存储开销。以下是一些使用索引的原则：

选择适当的列：

  • 选择高基数的列：基数是指列中不同值的数量。选择基数高的列作为索引列，可以使得索引更加有效，因为这样可以减少索引中相同值的数量，提高索引的区分度。

  • 对经常出现在WHERE子句中的列使用索引：索引的主要作用是加快查询速度，因此应该优先考虑在查询条件（WHERE子句）中经常出现的列上创建索引。

  • 对经常参与连接操作的列使用索引：在执行JOIN操作时，如果连接条件中的列没有索引，那么数据库就需要执行全表扫描来查找匹配的行，这将极大地降低查询效率。因此，应该在连接条件中的列上创建索引。

  • 对经常需要排序和分组的列使用索引：如果某个列经常需要被排序（ORDER BY）或分组（GROUP BY），那么在该列上创建索引可以加快排序和分组的速度。

避免在索引列上进行计算或函数操作：

  • 如果在查询条件中对索引列进行了计算或函数操作，那么数据库就无法有效地利用索引，因为索引是基于列的原始值建立的。因此，应该尽量避免在索引列上进行计算或函数操作。

不要过度索引：

  • 每个索引都要占用额外的磁盘空间，并降低写操作的性能，增加维护成本。在修改表的内容时，索引必须进行更新，有时也可能需要重构，因此，索引越多，维护索引所花的时间也就越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。

考虑使用复合索引：

  • 如果查询条件中经常同时出现多个列，可以考虑在这些列上创建一个复合索引。复合索引可以覆盖多个列的查询条件，提高查询效率。但是，复合索引的列顺序也非常重要，应该根据查询条件中列的出现频率和过滤性来确定列的顺序。

利用最左前缀：

  • 在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可以起到多个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集被称为最左前缀（Leftmost Prefixing）。

定期评估和调整索引：

  • 数据库的数据和查询模式会随着时间的推移而发生变化，因此应该定期评估和调整索引。这包括删除不再需要的索引、添加新的索引以及调整复合索引的列顺序等。

注意索引的维护：

  • 索引的维护也是非常重要的。例如，当表中的数据量非常大时，索引可能会变得碎片化，这会影响查询性能。此时，可以考虑对索引进行重建或优化。

考虑使用索引提示：

  • 在某些情况下，数据库优化器可能无法选择最优的索引。此时，可以使用索引提示来强制数据库使用特定的索引。但是，应该谨慎使用索引提示，因为错误的索引选择可能会导致查询性能下降。

尽量使用数据量少的索引，如果索引字段的值很长，最好使用值的前缀来索引。

  • 如果对字符串列进行索引，那么应该指定一个前缀长度。短索引能够节省大量索引空间，也会使查询更快，因为较小的索引涉及的磁盘I/O较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。

避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。

  • 应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

数据量小的表最好不要使用索引：

  • 由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

表的主键、外键必须有索引：

  • 避免锁表

选择适当的索引类型：

  • 根据查询类型选择合适的索引类型，如B+树索引、哈希索引等。
  • B+树索引适用于范围查询和排序操作，而哈希索引则适用于等值查询。

平衡读写性能：

  • 索引会提高查询性能，但也会降低写操作的性能。
  • 因此，在创建索引时，需要权衡读写操作的频率和性能需求。

MySQL 索引失效的情况有哪些？ ​

索引失效通常指的是数据库查询无法有效利用索引，从而导致查询性能下降。在MySQL中，特别是在使用InnoDB存储引擎时，索引失效的情况有多种，以下是一些常见的情况：

隐式类型转换

  • 描述：查询条件中对索引列进行了隐式类型转换，数据库可能无法利用索引。不同字符集的比较需要转换，这也会导致索引失效 。
  • 示例：索引是在整数类型的id字段上建立的，但查询时传入的是字符串类型的值，如id = '123'，这可能导致索引失效。

索引列使用了函数或表达式

  • 描述：在查询条件中对索引列使用了函数或表达式，数据库无法直接使用索引，而是需要先计算表达式的值，然后再进行查询，这会导致索引失效。
  • 示例：如 WHERE YEAR(date_created) = 2023，这里对date_created字段使用了YEAR()函数，可能导致索引失效。

数据类型不匹配

  • 描述：查询条件的数据类型与索引字段的数据类型不匹配，数据库无法有效使用索引。
  • 示例：索引是在INT类型的age字段上建立的，但查询时使用了字符串'18'与age进行比较，如CAST(age AS CHAR) = '18'，这会导致索引失效。

LIKE操作符的模糊查询

  • 描述：当使用LIKE (not like)操作符进行模糊查询，并且通配符（如%）放在索引列的开头时，索引会失效。
  • 示例：WHERE name LIKE '%keyword%'，这种情况下索引可能会失效。

前缀索引使用不当

  • 描述：虽然前缀索引可以节省空间并提高查询效率，但如果前缀选择不当（如过短），可能导致查询结果不准确或索引失效。
  • 示例：在很长的文本字段上使用过短的前缀作为索引，可能无法有效区分不同的记录。

不等于(!=或<>)导致索引失效

  • 描述：不等查询导致索引失效。
  • 示例：where name != 'abc， name 索引失效。

IS NOT NULL无法使用索引

  • 描述：null 查询导致索引失效。
  • 示例：IS NULL可以使用索引，IS NOT NULL无法使用索引

使用IN操作符

  • 描述：当IN操作符中的取值范围较大时，可能会导致索引失效，走全表扫描 。

索引下推（ICP）失效

  • 描述：MySQL 5.6引入的索引下推可以减少回表次数，但如果条件引用了子查询或者表达式，索引下推将失效 。

使用OR连接多个条件

  • 描述：查询条件中使用了OR连接多个条件，如果其中至少一个条件无法利用索引，整个查询可能会导致索引失效。
  • 示例：WHERE status = 'completed' OR amount > 1000，如果status和amount字段中只有一个有索引，则索引可能会失效。

联合索引非最左匹配

  • 描述：如果左边的值未确定，那么无法使用此索引。
  • 示例：复合索引为(A, B, C)，但查询条件为WHERE B = 'x' AND C = 'y'，此时索引可能无法被有效利用。

范围条件右边的列索引失效（复合索引）

  • 描述：使用联合索引时，当联合索引的字段采用了范围匹配，那么该字段的右侧字段将会失效
  • 示例：复合索引为(age,classid,name)，查询条件为 WHERE student.age = 'abc' AND student.classId > 20 AND student.name = 22; name字段将会失效。

ORDER BY操作

  • 描述：如果排序顺序与索引顺序不一致，可能只使用部分索引或索引完全失效 。
  • 示例：复合索引为(age,classid,name)，如ORDER BY name, age，

索引列上存在大量重复值

  • 描述：如果索引列上存在大量重复值，数据库可能会认为使用索引并不能显著提高查询性能，因此选择不使用索引。
  • 示例：在布尔类型的is_active字段上建立的索引，如果大部分记录的值都是相同的，索引可能会失效。

数据分布不均匀

  • 描述：索引列的数据分布不均匀，即某些值出现的频率远高于其他值，数据库可能会选择不使用索引。
  • 示例：在性别字段上建立了索引，但大部分记录的性别都是相同的，索引可能会失效。

索引列上存在过多的NULL值

  • 描述：索引列上存在过多的NULL值，数据库可能会认为使用索引并不能提高查询性能，因为索引无法存储NULL值。
  • 示例：在可选的电话号码字段上建立了索引，而大部分记录的电话号码都是NULL，索引可能会失效。

数据量过大或表数据更新频繁

  • 描述：当数据表中的数据量非常大时，索引可能会失效，因为数据库可能会选择不使用索引而进行全表扫描。另外，如果表数据更新频繁，索引也需要频繁更新，这可能导致索引失效或性能下降。
  • 示例：大型数据表的全表扫描可能比索引查找更快，尤其是在索引碎片化严重或索引未得到良好维护的情况下。

MySQL 隐式类型转换的规则有哪些？ ​

MySQL 在处理不同数据类型之间的比较或运算时，为了使得这些数值可比较（也可以称为类型的兼容性），会自动进行隐式类型转换。这种机制可以帮助数据库在不完全匹配的数据类型之间执行操作，但有时也可能导致意外的结果。以下是MySQL中一些常见的隐式类型转换规则：

  1. NULL值比较：

    • 两个参数至少有一个是NULL时，比较的结果也是NULL。

    • 例外情况：使用<=>对两个NULL做比较时会返回1。

  2. 字符串与数字的比较：

    • 当一个字符串和一个数字进行比较时，如果可能的话，MySQL 会尝试将字符串转换为数字。

    • 如果字符串以数字开头，那么就使用该数字部分；如果字符串不以数字开头，则被视为0。

    • 例如，'123abc' > 100 结果为真（因为 '123abc' 被视为 123），而 'abc123' < 100 也结果为真（因为 'abc123' 被视为 0），“110”+10086会被解释为110+10086。“cba”+886，则结果将仅为数字部分886。

  3. 日期与数字/字符串的比较：

    • MySQL 支持将整数直接作为日期来解释，比如 20241021 可能被解释为日期 2024-10-21。

    • 字符串形式的日期也可以与日期类型直接比较，前提是格式正确且可以被识别。

  4. TIMESTAMP或DATETIME与常量比较：

    • 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量时，常量会被转换为TIMESTAMP。

  5. 布尔表达式中的类型转换：

    • 在布尔上下文中，所有非零数值和非空字符串都被视为TRUE，只有0和空字符串被视为FALSE。

    • 例如，在条件语句中，IF('any_non_empty_string', 1, 0) 将返回 1。

  6. 算术运算中的类型转换：

    • 当对不同类型的值执行算术运算时，MySQL 通常会尝试将非数字类型转换成数字。

    • 对于无法转换的情况，如 'a' + 1，结果将是 NULL，并且可能会产生警告信息。

  7. 排序与分组中的类型转换：

    • 在 ORDER BY 或 GROUP BY 子句中，如果列包含混合类型的数据，MySQL 可能会对这些数据进行类型转换以便能够比较它们。
    • 这种情况下，通常遵循上述提到的基本规则。

  8. 十六进制值比较：

    • 十六进制的值和非数字做比较时，会被当做二进制串。

    • 十六进制的值和数字做比较时，会按照特定的规则处理，具体规则可能因MySQL版本和配置而异。

  9. DECIMAL类型比较：

    • 有一个参数是DECIMAL类型时：

    • 如果另外一个参数是DECIMAL或者整数，会将整数转换为DECIMAL后进行比较。

    • 如果另外一个参数是浮点数，则会把DECIMAL转换为浮点数进行比较。

  10. 特殊函数的影响：

    • 使用某些函数，如 CAST() 或 CONVERT()，可以显式地控制类型转换，这有助于避免隐式转换带来的不确定性。

  11. 其他注意事项：

    • 隐式类型转换可能会引发安全问题。例如，如果password字段的类型是字符串，而查询条件是整数0，由于隐式类型转换，可能会匹配上不符合预期的结果。

    • 隐式类型转换本身非常耗费MySQL服务器性能，因此不推荐过度依赖隐式转换。

    • 依赖隐式类型转换可能会导致难以追踪的问题，尤其是在复杂的查询中。因此，推荐的做法是尽量保证数据类型的明确性和一致性，必要时使用CAST或CONVERT函数显式地进行类型转换来确保预期的行为。同时，应该定期审查并测试SQL代码，确保其符合业务逻辑需求。

MySQL 中什么是最左前缀原则，最左匹配原则？ ​

  • 最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
  • 最左前缀匹配原则，非常重要的原则，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • = 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

什么是聚簇索引和非聚簇索引？ ​

聚簇索引

定义：聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的索引方式。它定义了表的物理排序方式，使得数据行按照索引列的顺序存储在磁盘上相邻的位置。聚簇索引的索引页面指针直接指向数据页面，因此使用聚簇索引查找数据通常比使用非聚簇索引更快。

特点：

  • 一个表只能有一个聚簇索引，因为表的物理顺序只能有一种。
  • 聚簇索引的叶子节点包含实际的数据行，所以在查询时可以直接访问到数据行，减少了磁盘的I/O操作。
  • 聚簇索引适合于那些常用于范围查询或按顺序访问数据的列，如日期、自增ID等。
  • 建立聚簇索引需要额外的空间，以存放表的副本和索引中间页，通常需要至少相当于表大小120%的附加空间。
  • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

优点：

  1. 数据访问速度快：聚簇索引的叶子节点直接包含行数据，减少了查找数据时的I/O操作。
  2. 主键查询效率高：由于主键索引的B+树结构，根据主键的查询可以非常快速。
  3. 范围查询效率高：对于有序的主键数据，进行范围查询时效率很高。

缺点：

  1. 插入性能受限：如果插入的数据不是按照索引顺序，可能会导致页分裂，影响性能。
  2. 索引维护成本高：数据的增删改操作可能引起索引的调整，特别是页分裂，影响性能。
  3. 主键更新代价大：主键的变更可能引起大量数据移动，对性能影响较大。
  4. 主键大小影响：如果主键过大，会占用更多的存储空间，并且可能影响索引的层级结构。

非聚簇索引

定义：非聚簇索引是在数据行的外部构建的索引结构，它包含索引列的值和指向实际数据行的指针。非聚簇索引的顺序与数据物理排列顺序无关。

特点：

  • 一个表可以有多个非聚簇索引。
  • 非聚簇索引的叶子节点不包含实际的数据行，而是包含指向数据行的指针。因此，在查询时，需要先通过非聚簇索引定位到数据行的位置，再去访问实际的数据行。
  • 适合于那些经常用于检索数据的列，如搜索条件经常使用的字段。
  • 非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。
  • 在没有聚簇索引的表中，非聚簇索引的数量可能有限制（如在SQL Server中，最多可以有250个非聚簇索引）。

优点：

  1. 维护成本低：相对于聚簇索引，非聚簇索引在数据增删改操作时的维护成本较低。
  2. 灵活性高：可以为表中没有包含在聚簇索引中的列创建非聚簇索引，提供更多的查询优化选项。
  3. 覆盖索引：如果非聚簇索引的叶节点包含了查询所需的所有字段，这种索引称为覆盖索引，可以避免回表查询主键索引，提高查询效率。

缺点：

  1. 可能需要回表：非聚簇索引的叶节点通常包含索引列和指向行数据的指针（主键），可能需要额外的I/O操作回表查询实际数据。
  2. 范围查询效率较低：对于非聚簇索引，进行范围查询时可能需要回表，效率不如聚簇索引。
  3. 索引占用空间：非聚簇索引需要额外的存储空间，并且在表数据量大时，索引本身的维护也可能带来一定的开销。
  4. 多索引维护：如果表中有多个非聚簇索引，每个索引都需要维护，这可能会增加数据库的维护成本。

什么是索引下推？ ​

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中，从而减少不必要的数据读取和传输，提升查询性能。

具体实现方式可以是通过存储引擎提供的接口或者钩子函数，让存储引擎在读取索引页时就进行额外的过滤操作。

优势

  • 减少I/O操作：通过减少不必要的数据读取和传输，索引下推可以显著降低磁盘I/O的开销。
  • 提升查询性能：由于减少了需要处理的数据量，查询的执行速度通常会得到提升。
  • 优化资源利用：通过更高效的查询执行，索引下推有助于优化数据库服务器的资源利用，如CPU、内存和I/O资源。

注意事项

  1. 适用版本：索引下推是MySQL 5.6发布后针对扫描二级索引的一项优化改进，适用于MySQL 5.6及以上版本。

  2. 适用场景与限制：

    • 适用场景：索引下推更适用于复杂查询条件、多列条件的查询中，特别是当查询条件中包含非精确匹配（如LIKE 'A%'）和精确匹配（如=某个值）的组合时。此外，它还适用于range、ref、eq_ref和ref_or_null等访问方法。

    • 限制：索引下推并不适用于所有类型的查询。例如，它不适用于聚簇索引（因为聚簇索引本身包含表数据），也不支持在虚拟生成的列上创建的辅助索引。此外，它还不支持将引用子查询或存储函数的条件推送下去，因为存储引擎无法调用存储函数。

  3. 查看是否使用ICP：可以通过对查询语句进行EXPLAIN操作，在EXTRA信息里查看是否使用了ICP。如果显示“Using index condition”，则代表使用了ICP。

  4. 配置ICP：在MySQL中，可以通过设置系统变量optimizer_switch来控制索引下推的启用或禁用。

    • 启用 SET optimizer_switch='index_condition_pushdown=on'

    • 禁用 SET optimizer_switch='index_condition_pushdown=off'

什么是覆盖索引？ ​

当一个索引包含了查询所需要的全部列时，我们称这个索引为覆盖索引。

优点：

  1. 提高查询性能：减少了数据库系统的I/O操作，因为不需要访问表中的实际数据行（回表）。
  2. 减少锁的竞争：对于只涉及索引的查询，InnoDB等存储引擎可以仅对索引加锁，从而减少了锁的竞争。
  3. 可能使用更小的索引：如果查询只涉及表中的几个列，那么覆盖索引可以只包含这些列，从而可能比全表索引更小。

使用场景：

  • 当查询只需要表中的少数几列数据时。
  • 当这些列的数据量远小于整个表的数据量时。
  • 当这些列经常一起被查询时。

注意事项：

  • 虽然覆盖索引可以提高查询性能，但过多的索引会占用额外的磁盘空间，并降低写操作的性能，因为每次写操作都需要更新索引。
  • 在设计索引时，需要权衡查询性能和写性能之间的关系，以及索引对磁盘空间的占用。
  • 在MySQL中，可以通过使用explain命令输出的Extra列来判断是否使用了索引覆盖查询。若使用了索引覆盖查询，则Extra列包含“Using index””字符串。
  • 覆盖索引必须要存储索引列的值，因此 MySQL 只能使用 B-Tree 索引做覆盖索引。

MySQL中的索引有哪些分类？ ​

按数据结构分类

  1. B-tree索引（BTREE索引）

    • B-tree允许每个节点有更多的子节点，树的节点增多后树的层级比原来的二叉树少了，且能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。

    • B+树是B树的升级版，InnoDB存储引擎就是用B+Tree实现MySQL索引结构。B+树在叶子节点存储key和数据，非叶子节点只存储key不存储数据，这样做可以降低树的高度，提高查询效率。

  2. Hash索引

    • Hash索引是一种基于哈希表实现的索引，它支持全值匹配，但不支持范围查询和前缀匹配。在MySQL中，哈希索引主要应用于MEMORY存储引擎，也是Memory引擎表的默认索引类型。

    • Hash索引数据并不是按照索引值顺序存储的，所以无法用于排序。

按物理存储方式分类

  • 聚簇索引（Clustered Index）：将数据存储与索引放到了一块，找到索引也就找到了数据，不需要根据主键或行号去进行回表查询。InnoDB存储引擎支持聚簇索引。

  • 非聚簇索引（Non-Clustered Index）：非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。

按索引字段的特性分类

  • 普通索引（Normal Index）：仅用来提高查询速度，没有其他特性。允许在定义索引的列中插入重复值和空值。
  • 唯一索引（Unique Index）：要求索引列的所有值都只能出现一次，即必须唯一。允许有空值，但如果是组合索引，则列值的组合必须唯一。
  • 主键索引（Primary Key Index）：在MySQL的主键上创建的索引就是主键索引，主键索引会自动创建，一个表只能有一个主键索引，同时主键索引也是唯一索引。
  • 全文索引（FULLTEXT Index）：主要用来查找文本中的关键字，只能在CHAR、VARCHAR或TEXT类型的列上创建。在MySQL中，只有MyISAM存储引擎支持全文索引。
  • 空间索引（Spatial Index）：是对空间数据类型的字段建立的索引，使用SPATIAL关键字进行扩展。创建空间索引的列必须将其声明为NOT NULL，且只能在MyISAM存储引擎的表中创建。
  • 组合索引（Composite Index/Multiple-Column Index）：也称为复合索引或多列索引，是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引，可以通过这几个字段进行查询。

按索引的字段个数分类

  • 单列索引（Single-Column Index）：索引只包含原表的一个列。
  • 多列索引（Composite Index/Multiple-Column Index）：索引包含原表的多个列。

其他特殊索引

  • 自适应hash索引（Adaptive Hash Index）：是InnoDB存储引擎中的内存结构的组成部分。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立hash索引可以提高查询速度，则自动建立hash索引。
  • 覆盖索引（Covering Index）：如果一个索引包含（或覆盖）所有需要查询的字段的值，称为覆盖索引。即只需扫描索引而无须回表。
  • 降序索引（Descending Index）：从MySQL 8.0开始支持降序索引，允许在索引定义中指定列的排序顺序（升序或降序）。
  • 不可见索引（Invisible Index）：MySQL支持不可见索引，即优化器未使用的索引。该功能适用于除主键（显式或隐式）以外的索引。不可见的索引可以测试删除索引对查询性能的影响，而无需进行破坏性的更改。

B+ Tree 索引的特点？ ​

MySQL 中广泛使用 B+ Tree 索引来加速数据的检索、插入、删除和更新操作。B+Tree 索引是数据库中最常用的索引结构之一。

B+Tree 索引的特点：

  1. 所有的值都是按照顺序存储的：这意呀着B+Tree索引可以支持非常高效的顺序访问、范围查询和排序操作。
  2. 非叶子节点不存储数据：B+Tree的非叶子节点仅存储键值信息以及指根节点和向子节点的指针，不存储实际的数据记录。这使得B+Tree的每个节点可以存储更多的键值，树的高度更低，从而减少磁盘I/O操作的次数。
  3. 叶子节点之间是相互链接的：这提高了范围查询的效率，因为可以直接在叶子节点之间遍历，而不需要回到根节点。
  4. 数据记录都存储在叶子节点：这使得查询操作必须到达叶子节点，但在叶子节点中可以非常高效地利用磁盘的预读能力，因为数据是顺序存储的。
  5. 支持高效的查找、插入、删除和顺序访问：B+Tree索引的设计使其在各种数据库操作中都能保持良好的性能。

B+Tree 索引在 MySQL 中的应用：

在 MySQL 中，无论是 InnoDB 还是 MyISAM 存储引擎，都默认使用 B+Tree 索引来管理数据和加速查询。

  • InnoDB 存储引擎：InnoDB 使用聚集索引（Clustered Index）和非聚集索引（Secondary Index）。聚集索引决定了表中数据的物理存储顺序，而非聚集索引的叶子节点则存储了对应聚集索引键的值，而不是直接存储数据行本身。这意味着非聚集索引的查询最终还是需要访问聚集索引来获取完整的数据行。
  • MyISAM 存储引擎：MyISAM 存储引擎也使用 B+Tree 索引，但它不支持聚集索引。在 MyISAM 中，每个表可以有多个索引，但索引和数据是分开存储的。MyISAM 索引的叶子节点存储了指向数据行在数据文件中的位置的指针。

InnoDB 选择B+ Tree作为索引的原因？ ​

  1. 查询效率高：B+ Tree的平衡性和有序性使得查询操作能够快速定位到所需的数据，特别是在进行范围查询和排序查询时，效率更高。
  2. 减少磁盘I/O：B+ Tree的节点存储结构使得每个节点可以存储更多的索引信息，从而减少了树的高度。在查询时，需要访问的节点数更少，因此可以减少磁盘I/O次数，提高查询速度。
  3. 支持高并发：B+ Tree的分支节点值可以全部存放在内存中，并且每个叶子节点固定只指向一个聚集索引，这使得在高并发环境下也能保持较高的读写效率。
  4. 支持范围查询和排序查询：B+ Tree的叶子节点之间通过指针相连，形成了一个有序链表，这支持了范围查询和排序查询的高效执行。
  5. 索引覆盖：B+ Tree索引可以覆盖查询的列，减少IO操作，提高查询效率。当查询的列都被包含在索引中时，就可以直接通过索引来获取数据，而无需回表查询。

B+Tree索引和B树索引的区别？ ​

B+Tree索引和B树索引在数据库索引中都是重要的数据结构，它们之间存在一些关键的区别：

节点存储内容

  • B树：B树的每个节点都包含关键字和数据，即每个节点都可以存储实际的数据记录。这意味着在B树中，数据可能存储在非叶子节点中。
  • B+Tree：B+Tree的非叶子节点仅包含索引信息（即关键字和指向子节点的指针），而不包含实际的数据记录。所有的数据记录都存储在叶子节点中，并且叶子节点之间通过指针相互链接，形成一个有序链表。

查询效率

  • B树：由于B树的数据可能存储在非叶子节点中，因此在查询时可能需要在内部节点和叶子节点之间进行多次跳转，这可能会降低查询效率，尤其是在数据量很大的情况下。
  • B+Tree：由于B+Tree的所有数据都存储在叶子节点中，并且叶子节点之间通过指针链接，因此查询时只需要遍历叶子节点即可，这使得查询效率更加稳定，尤其是在进行范围查询时。

磁盘I/O次数

  • B树：由于B树的数据可能分布在多个节点中，因此在查询时可能需要多次磁盘I/O操作来访问不同的节点。
  • B+Tree：B+Tree的叶子节点通常包含更多的关键字和数据，且叶子节点之间通过指针链接，这有助于减少磁盘I/O次数，因为可以一次性加载更多的数据到内存中。

适用范围

  • B树：B树通常用于内存受限的环境或者需要随机访问的场景，如文件系统索引。
  • B+Tree：B+Tree更适合用于数据库索引，因为数据库系统通常需要大量的范围查询和顺序遍历，而B+Tree在这些操作上具有优势。

平衡性

  • B树和B+Tree：两者都是自平衡的树结构，保持所有叶子节点到根节点的高度差不超过1，这使得在树中查找数据的时间复杂度保持在O(log n)级别。

    索引大小

  • B+Tree：由于B+Tree的非叶子节点不包含数据，因此可以容纳更多的索引信息，这有助于减少树的高度，进而减少索引占用的空间。

谈谈MySQL 中的哈希索引？ ​

在MySQL中，哈希索引（Hash Index）是一种用于加速等值查询（如 =, IN）的索引类型。与B树索引不同，哈希索引不存储数据行的实际顺序，而是使用哈希函数将键值映射到哈希表中的位置。这种结构使得哈希索引在查找特定值时非常高效。

原理与功能

  • 哈希索引通过计算索引列的哈希值并将其存储在索引中，同时保存指向每行数据的指针。
  • 如果多个值的哈希码相同，会发生哈希碰撞，此时索引会使用链表来存储这些具有相同哈希值的行指针。

哈希索引的特点

  1. 快速等值查找：检索效率高，对于精确的等值查询。查找操作的时间复杂度通常是O(1)，这意味着查找速度非常快。

  2. 不支持范围查询：由于哈希索引不维护数据的顺序，因此无法高效地进行范围查询（如 <, >, <=, >=）。

  3. 不支持部分匹配：哈希索引只能用于完全匹配的键值，不支持前缀匹配或模式匹配（如LIKE查询）。

  4. 哈希冲突：如果不同的键值通过哈希函数映射到相同的哈希值，则称为哈希冲突。虽然哈希函数设计时会尽量减少冲突，但冲突仍然可能发生，并需要通过链表或其他数据结构解决。

  5. 内存使用：哈希索引通常使用内存存储哈希表，这意味着它们可以显著提高性能，但也消耗更多的内存资源。

  6. **支持的存储引擎：**MEMORY和NDB是两种原生支持哈希索引的MySQL存储引擎，并以链表方式处理哈希碰撞。

  7. 伪哈希索引：其他不支持原生的哈希索引，但InnoDB在内部可能会使用哈希表来优化某些操作（如自适应哈希索引）。InnoDB和MyISAM不直接支持哈希索引，但可以通过创建伪哈希索引实现。

    • 在InnoDB和MyISAM中，即使定义为HASH类型的索引，实际上创建的仍然是BTree索引。

    • 可以通过增加一个字段存储哈希值，并建立索引，在插入和更新时触发器自动计算并存储哈希值。

    • 查询时需要手动指定使用哈希函数，但这种方法需要额外维护哈希值。

  8. 不能用于加速ORDER BY操作：因为哈希值的顺序可能与原始键值顺序不符。

  9. 适用场景：哈希索引最适合用于等值查找非常频繁且数据集相对较小的场景。对于需要范围查询或复杂查询的情况，B树索引可能更合适。

创建哈希索引

在MySQL中，哈希索引通常通过Memory存储引擎创建。例如：

sql
CREATE TABLE test_hash (
    id INT NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id),
    UNIQUE KEY (name) USING HASH
) ENGINE=MEMORY;

在这个例子中，name 列被创建为哈希索引。注意，USING HASH 关键字用于指定使用哈希索引。

限制

  • 数据库无法准确估计值之间的行数。
  • 适用于较小的数据集。如果数据集很大，哈希表可能会占用大量内存，甚至可能不适合在内存中存储。
  • 对于组合索引，哈希索引不能用于部分索引列的查询。
  • 遇到大量哈希碰撞时，性能可能不如BTree索引。
  • 哈希索引不能避免表扫描，尤其是在确定行数时。
  • 选择性低的索引键可能导致性能下降。
  • 不支持部分索引列的查询。

MySQL 中什么是前缀索引？ ​

定义与原理

  • 定义：前缀索引是指对文本字段的前n个字符（n为具体指定的字符数）建立索引，以缩小索引的大小并提高查询效率。
  • 原理：通过仅对字符串的前部分进行索引，可以显著减少索引占用的存储空间，同时保持较高的查询效率。这是因为在实际应用中，很多查询操作都是基于字符串的前几个字符进行的。

优势与应用

  1. 节省存储空间：由于只索引字符串的前部分，因此可以大大减少索引占用的存储空间。
  2. 提高查询效率：对于大量基于字符串前缀的查询操作，前缀索引可以显著提高查询速度。

适用场景：

  • 当字段的字符串很长，但查询时通常只关注前几个字符时。
  • 对于BLOB和TEXT类型的字段，由于MySQL不允许对它们的全部长度进行索引，因此必须使用前缀索引。
  • 当需要平衡索引的查询性能和存储空间时。

注意事项

  • 前缀长度的选择：前缀的长度需要根据实际情况进行选择，既要保证索引的选择性（即不重复的索引值和数据表记录总数的比值），又要避免过长导致索引过大。
  • 索引选择性的降低：使用前缀索引会降低索引的选择性，因为多个不同的字符串可能具有相同的前缀。这可能会影响查询的精确度和效率。
  • 无法使用某些高级功能：如ORDER BY和GROUP BY等操作可能无法直接使用前缀索引，且无法使用前缀索引进行索引覆盖扫描。

创建前缀索引

sql
CREATE INDEX index_name ON table_name(column_name(prefix_length));

MySQL 中什么是全文（FULLTEXT）索引？ ​

全文索引（FULLTEXT）是为了更快地进行文本搜索而设计的索引。它能够在包含大量文本的列中搜索词语。全文索引通过分词技术等多种算法智能分析文本中的关键词频率和重要性，然后按照一定的算法规则智能地筛选出搜索结果。

全文索引一般是通过倒排索引实现的，即将文档中的每个单词映射到包含该单词的文档列表，从而快速定位到包含特定关键词的文档。

特点与优势

  1. 快速搜索：全文索引能够显著提高文本搜索的速度，特别是在处理大量文本数据时。
  2. 灵活性：全文索引支持复杂的搜索查询，包括词形变化、同义词等，提高了搜索的灵活性和准确性。
  3. 语言支持：现代数据库系统如MySQL提供了对多种语言的全文索引支持，包括中文、日文和韩文等，通过内置的全文解析器实现。
  4. 相关性评分：全文索引搜索结果的相关性评分可以帮助确定匹配项的相关程度，使用户能够更容易地找到最相关的内容。
  5. 大小写区分：默认情况下，全文索引的搜索执行方式不区分大小写。但是，当索引的列使用二进制排序后，可以执行区分大小写的全文索引。

适用范围与限制

  1. 存储引擎限制：在MySQL中，全文索引最初只支持MyISAM存储引擎。但从MySQL 5.6版本开始，InnoDB存储引擎也开始支持全文索引。
  2. 字段类型限制：全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。
  3. 额外开销：全文索引会占用额外的磁盘空间，并可能增加数据插入时的开销。尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。
  4. 语言支持限制：虽然MySQL提供了对多种语言的全文索引支持，但某些语言（如中文）的支持可能有限，这取决于MySQL的版本和配置。
  5. 最小搜索长度：MySQL的全文索引有一个最小搜索长度限制，默认情况下MyISAM引擎下为4个字符，InnoDB引擎下为3个字符。由参数ft_min_word_len控制，长度小于此限制的词语将不会被索引。
  6. 匹配单词：全文检索以整个单词作为匹配对象只有由字母、数字、单引号、下划线构成的字符串被认为是单词，带注音符号的字母仍是字母，像C++不再认为是单词。

使用方法

假设有一个名为articles的表，其中包含id（主键）、title（标题）和content（内容）三个字段。以下是在content字段上创建全文索引的示例：

sql
CREATE FULLTEXT INDEX idx_content ON articles(content);

然后，可以使用以下SQL语句进行全文搜索：

sql
SELECT * FROM articles WHERE MATCH(content) AGAINST('关键词');

MySQL有哪几类日志文件？ ​

MySQL数据库中有多种类型的日志文件，这些日志文件在数据库的运行、维护、故障排查和性能优化中起着重要的作用。

错误日志（Error Log）

  • 记录MySQL服务器运行过程中所有较为严重的警告和错误信息，以及MySQL服务器每次启动和关闭的详细信息，是排查和解决问题的重要工具。
  • 通常位于数据目录下，以hostname.err命名（其中hostname是主机名）。
  • 可以通过MySQL的配置文件（如my.cnf或my.ini）中的log-error选项来指定错误日志文件的位置和文件名。可以通过show variables like log_error;命令查看错误日志的路径。

二进制日志（Binary Log，简称binlog）

  • 记录所有修改数据库数据的操作（如INSERT、UPDATE、DELETE等），不包括SELECT操作以及SHOW操作。以二进制形式存储。这些日志在数据恢复、数据复制和审计等方面非常重要。
  • 位于数据目录下，文件名通常为mysql-bin.xxxx（其中xxxx是序号）。
  • 通过MySQL的配置文件中的log-bin选项来启用和配置二进制日志。**二进制日志记录了对数据库进行变更的所有操作，**如果想记录SELECT和SHOW，那么就需要开启全查询日志，二进制日志还包括了执行数据库更改操作的时间等信息。
  • 主要作用有： 二进制日志保证了MySQL集群架构的数据一致性，并可用于数据恢复、主从复制、数据审计和数据备份等。恢复（recovery），某些数据的恢复需要二进制日志，在全库文件恢复后，可以在此基础上通过二进制日志进行point-to-time的恢复。复制（replication），其原理和恢复类似，通过复制和执行二进制日志使得一台远程的Slave数据库与Master数据库进行实时同步。
  • 可以通过命令set sql_log_bin=1;来开启二进制日志，使用--log-bin[=file_name]选项或在配置文件中指定log-bin。启动时，mysqld写入包含所有更新数据的SQL命令的日志文件。对于未给出file_name值，默认名为-bin后面所跟的主机名。在未指定绝对路径的情形下，缺省位置保存在数据目录下。在MySQL 5.7.3及其以后的版本中，若想开启二进制日志，则必须加上server_id参数。

慢查询日志（Slow Query Log）

  • 记录执行时间超过指定时间阈值的查询语句，可用于识别和优化性能瓶颈。运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
  • 通常位于数据目录下，文件名默认为hostname-slow.log。
  • 可以通过long_query_time = 10设置慢查询的时间阈值,默认值为10，并通过slow_query_log = ON和slow_query_log_file= /var/lib/mysql/tmp_slow.log选项来启用和配置慢查询日志。默认情况下，MySQL数据库并不启动慢查询日志，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

一般查询日志（General Log）

  • 记录MySQL服务器接收到的所有客户端请求，包括连接、更新、查询数据记录的SQL语句（即使语句错误）、断开连接等操作。由于记录了所有操作，因此可能会对性能产生较大影响，通常只在需要时短暂开启。全查询日志记录了所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  • 通常位于数据目录下，文件名默认为hostname.log。
  • 在默认情况下，MySQL的全查询日志是不开启的。通过MySQL的配置文件中的general_log和general_log_file选项来启用和配置查询日志。当需要进行采样分析时可以使用命令SET GLOBAL general_log=1;开启。 general_log=ON, 开启general log，general_log_file=/tmp/general.log, 设置general log文件位置

重做日志（Redo Log）

  • 属于InnoDB存储引擎特有的日志，用于记录事务中的修改操作，确保事务的持久性，记录事务执行后的状态。
  • 在数据库发生故障时，用来恢复未写入data file的已成功事务更新的数据。
  • 物理格式的日志，记录的是物理数据页面的修改信息。
  • 重做日志是顺序写入redo log file的物理文件中的，包含内存中的日志缓冲（redo log buffer）和磁盘上的重做日志文件（redo log file）。
  • 通常位于数据目录下的ib_logfile0和ib_logfile1（或其他编号的文件）。
  • 通过InnoDB的配置参数（如innodb_log_file_size、innodb_log_files_in_group等）来配置重做日志的大小和数量。

回滚日志（Undo Log）

  • 用于保存事务发生之前的数据状态，以便在事务失败或需要回滚时恢复数据。同时，它还支持多版本并发控制（MVCC）。
  • 逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态。
  • 产生于事务开始之前，当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。
  • 在InnoDB存储引擎中，回滚日志通常存储在共享表空间（ibdata文件）中，但从MySQL 5.6开始，可以配置为独立的表空间文件。
  • 通过InnoDB的配置参数（如innodb_undo_tablespaces、innodb_undo_directory等）来配置回滚日志的存储位置和数量。

中继日志（Relay Log）

  • 在MySQL的主从复制架构中，中继日志用于在从库上记录从主库接收到的二进制日志内容。这些日志在从库上重放，以实现主从同步。
  • 通常位于从库的数据目录下，文件名和格式与主库的二进制日志相似。
  • 中继日志的配置通常与主从复制的配置一起进行，无需单独配置。

MySQL Binary Log 的格式有哪些？ ​

MySQL Binary Log（即binlog）的格式主要有三种，分别是：

基于语句的日志记录（Statement-Based Logging）：

  • 二进制日志记录所有修改操作的SQL语句（不包括SELECT和SHOW这类不修改数据的语句），而不是行级操作。

  • 如果一条SQL语句修改了多行数据，只会记录一次该语句，而不是每一行的修改。

  • 优点是生成的日志文件通常较小，因此节省了磁盘空间和网络带宽。

  • 缺点是在某些情况下，SQL语句可能不足以完全还原数据的更改，特别是涉及到随机函数或非确定性操作时，如存储过程或触发器，可能会导致主从数据不一致。

基于行的日志记录（Row-Based Logging）：

  • 二进制日志会记录对表中行数据的每个更改操作，包括INSERT、UPDATE和DELETE操作。
  • 优点是记录非常详细，可以完全还原每个更改。更精确地复制数据，避免了基于语句复制中可能遇到的主从不一致问题。
  • 缺点是可能产生较大的日志文件，因为它记录了所有更改的详细数据信息。

混合模式（Mixed）：

  • 这是Statement和Row两种模式的混合使用。
  • 结合了基于语句复制和基于行复制的优点，既能够保持binlog文件的大小适中，又能够确保数据的准确复制。
  • 在某些情况下，混合复制可能会增加复制过程的复杂性。
  • 在MySQL 5.6及更高版本中，混合复制是默认的复制模式。
  • 一般的复制使用Statement模式保存binlog，对于Statement模式无法复制的操作使用Row模式保存binlog。
  • MySQL会根据执行的SQL语句选择日志保存方式，在以下情况下，MySQL会自动切换到基于行的复制：
    • 非确定性SQL语句：当SQL语句的结果是非确定的，即在不同时间或不同环境下执行可能会产生不同结果时，MBR会切换到RBR。这包括使用如UUID()、SYSDATE()、RAND()等函数的语句，使用会话变量，使用自增ID，以及包含用户自定义函数（UDFs）的语句。
    • 系统函数或存储程序调用：如果SQL语句中调用了系统函数或存储程序（如存储过程、函数、触发器、事件等），且这些函数或程序的结果可能影响数据的一致性。
    • 复杂的数据操作：对于某些复杂的数据操作，如包含大量行的更新或删除操作，或者涉及多张表的复杂事务，或使用临时表，如果SBR可能无法正确复制这些操作。
    • 表结构或索引变更：如果主服务器上的表结构或索引发生变更。
    • 特定的SQL语句：某些特定的SQL语句，如包含LIMIT子句的UPDATE或DELETE语句，或者复杂的INSERT ... SELECT语句，在SBL模式下可能无法正确复制。
    • 存储引擎限制：如果某个存储引擎不支持SBL，而操作又必须在该存储引擎的表上进行。

可以通过设置全局或会话级别的binlog_format服务器变量来选择。但请注意，不能在运行时更改binlog_format变量，必须在MySQL启动时设置。

在某些情况下，如从存储函数或触发器中进行更改、启用NDB存储引擎、或会话当前正在使用基于行的复制时，可能无法更改binlog_format变量。

MySQL Binary Log 复制流程？ ​

MySQL二进制日志复制流程

  1. 记录二进制日志
  • 主服务器会将其数据的改变（如DDL和DML操作）记录到二进制日志（Binary Log）中。
  • 每个事务更新完毕数据之前，主服务器将这些操作的信息记录在二进制日志里面，在事件写入二进制日志完成后，主服务器通知存储引擎提交事务。
  1. 发送日志信息
  • 从服务器首先会启动一个工作的 IO 线程连接到主服务器，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
  • 主服务器接收到来自从服务器的IO进程的请求后，通过负责复制的IO线程(dump线程)根据请求信息读取日志指定位置之后的日志信息，并返回给从服务器的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到主服务器端的binlog文件的名称以及binlog的位置。
  • dump线程不会对事件进行轮询，如果该线程追赶上了主库将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒。
  1. 从服务器（Slave）接收并写入中继日志
  • 从服务器的IO进程接收到主服务器返回的日志内容后，将接收到的日志内容依次添加到从服务器端的中继日志（Relay Log）文件的最末端。
  • 同时，将读取到的主服务器端的binlog文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚地告诉主服务器从某个binlog的哪个位置开始往后的日志内容。
  1. 从服务器解析并执行日志
  • 从服务器的SQL进程检测到中继日志中新增加了内容后，会马上解析中继日志的内容，将其转化为在主服务器端真实执行时的可执行内容，并在从服务器上执行。如果是多线程复制，无论是MySQL 5.6库级别的假多线程还是MariaDB或者MySQL 5.7的真正的多线程复制，SQL线程只进行Coordinator操作，只负责把中继日志中的二进制日志读出来然后交给Worker线程，Woker线程负责具体Binlog Events的执行，从而实现备库数据的更新。
  • 当 SQL 线程追赶上 IO 线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL 线程执行的时间也可以通过配置选项来决定是否写入其自己的二进制日志中。

主从服务器配置

  • 在进行主从复制之前，需要确保主从服务器的MySQL版本兼容
  • 设置server-id：在主从数据库上也需要设置server-id参数，并确保它们的server-id不同。
  • **从服务器配置：**relay-log等参数，并设置master的连接信息。
  • 创建复制用户：在主数据库上创建一个用于复制的用户，并授予该用户必要的权限（如REPLICATION SLAVE权限）。
  • 获取binlog坐标：在从数据库配置之前，需要获取主数据库上binlog的当前坐标（即文件名和位置），以便从该点开始复制数据。这通常通过执行SHOW MASTER STATUS命令来获取。

复制流程的状态监控

  • 可以使用 SHOW MASTER STATUS 命令来查看主服务器的二进制日志状态。
  • 使用 SHOW SLAVE STATUS 命令来监控从服务器的复制状态，包括延迟、错误等。
  • 定期清理旧的二进制日志文件，以避免磁盘空间耗尽。可以设置expire_logs_days参数来自动删除过期的日志文件。
  • 从服务器会定期向主服务器发送心跳包，以确认它仍然活跃并且正在处理二进制日志。

注意事项

  • 数据一致性：默认情况下，MySQL的复制是异步进行的，即主服务器提交事务后不需要等待从服务器成功更新数据即可返回给用户。这可能会导致从服务器的数据与主服务器不一致的情况。在复制过程中，需要确保主数据库和从数据库之间的数据一致性。如果需要更高的数据一致性，可以考虑使用半同步复制或同步复制等模式。
  • 复制延迟：复制延迟是主从复制中常见的问题之一。它可能是由于网络延迟、主数据库写压力大、从数据库SQL线程处理速度慢等原因造成的。为了减少复制延迟，可以采取优化网络、增加从数据库数量、使用并行复制等措施。
  • 安全性：在配置复制时，需要注意安全性问题。例如，应该为复制用户设置强密码、限制其访问权限等。同时，还需要定期监控和检查复制用户的权限和状态，以确保其安全性。

优化和配置

  • 可以通过调整 sync_binlog 参数来控制二进制日志的同步频率，以平衡性能和数据安全性。
  • 选择合适的二进制日志格式（SBR、RBR或MBR）以适应不同的应用场景。
  • 对于大型数据库，可以考虑使用半同步复制（Semi-Synchronous Replication）来确保至少有一个从服务器接收到二进制日志事件后再提交事务。

处理复制延迟

  • 在复制过程中，可能会出现从服务器落后于主服务器的情况，即复制延迟。
  • 可以通过查看Relay_Master_Log_File和Master_Log_File的差异，以及Exec_Master_Log_Pos和Read_Master_Log_Pos的差异来判断复制的延迟情况。如果出现复制延迟，可以尝试优化主从服务器的网络性能、提高从服务器的处理能力、调整复制参数等方法来降低延迟。
  • 如果主服务器检测到从服务器落后太多，它可以采取措施，比如暂停写操作，直到从服务器赶上。

异常处理

  • 如果从服务器在复制过程中断开连接或遇到错误，它会在重新连接后从上次断开的位置继续复制。
  • 从服务器可以通过SHOW SLAVE STATUS命令查看当前的复制状态，包括最后一个成功复制的二进制日志位置。

MySQL 中MVCC 是什么？ ​

MVCC （Multi-Version Concurrency Control），即多版本并发控制，在很多情况下避免加锁，实现了非阻塞的读操作，写操作也只锁定必要的行。对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，创建的表中有主键或者非NULL唯一键时都不会包含row_id列）

  • trx_id：每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
  • roll_pointer：回滚指针，每次对某条聚簇索引记录进行改动时，会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，指向这条记录的上一个版本（存储于rollback segment里），可以通过它来找到该记录修改前的信息。

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。

InnoDB 提出了一个ReadView的概念，当事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照。它记录并维护系统当前活跃事务的ID(没有commit，当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大)，是系统中当前不应该被本事务看到的其他事务id列表。

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View有如下几个属性：

  • trx_ids: 当前系统活跃(未提交)事务版本号集合。
  • max_trx_id: 创建当前read view 时“当前系统最大事务版本号+1”。max_trx_id并不是trx_ids中的最大值，事务id是递增分配的。
  • min_trx_id: 创建当前read view 时“系统正处于活跃事务(trx_ids)最小版本号”
  • creator_trx_id: 创建当前read view的事务版本号；

Read View可见性判断条件

  • trx_id < min_trx_id|| trx_id == creator_trx_id（显示） 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。 或者数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。
  • trx_id >= max_trx_id（不显示） 如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。如果小于则进入下一个判断
  • trx_id 列在 min_trx_id 和 max_trx_id之间，就要看trx_id是否在活跃事务（trx_ids）中 不存在：则说明read view产生的时候事务已经commit了，这种情况数据则可以显示。 已存在：则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本，如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同的：

  • READ COMMITTED：每次读取数据前都生成一个ReadView
  • REPEATABLE READ：只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。

MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

MySQL InnoDB 如何实现事务？ ​

InnoDB 是 MySQL 数据库的一个存储引擎，它通过一系列复杂的机制来实现事务的 ACID 属性（原子性、一致性、隔离性、持久性）。

  1. 原子性（Atomicity）是指事务中的所有操作要么全部完成，要么全部不执行。InnoDB 使用 undo 日志来实现事务的原子性。Undo（回滚） 日志：当事务对数据库进行修改时（如 INSERT、UPDATE、DELETE），InnoDB 会生成相应的 undo 日志。

    • delete一条数据的时候，就会记录这条数据的曾经的信息，回滚的时候，insert这条旧数据

    • update一条数据的时候，就会记录之前的旧值，回滚的时候，根据旧值执行update操作

    • insert一条数据的时候，就会这条记录的主键，回滚的时候，根据主键执行delete操作

      日志记录了如何将数据库从修改后的状态恢复到修改前的状态。如果事务成功提交，则 undo 日志可以被清理或用于后续的事务回z滚（对于 INSERT 操作产生的 undo 日志，如果事务提交且没有其他事务需要访问这些记录，它们可能会被立即清理）。如果事务失败或需要回滚，InnoDB 会使用 undo 日志中的信息来撤销事务所做的所有修改，从而保持数据库的原子性。

  2. 隔离性（Isolation）是指并发执行的事务之间互不干扰，每个事务都好像在独立执行一样。InnoDB 通过锁和 MVCC 来实现隔离性。

    • 锁：InnoDB 支持行级锁和表级锁（但主要是行级锁），以防止多个事务同时修改同一数据行。锁机制可以确保事务在并发执行时不会相互干扰，从而维护数据的隔离性。

    • MVCC（多版本并发控制）：MVCC 是 InnoDB 用来解决读写冲突的一种技术。它为每个事务维护一个数据的快照，这些快照数据在undo log中。如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。 使得读操作不会阻塞写操作，写操作也不会阻塞读操作（在大多数隔离级别下）。通过 MVCC，InnoDB 可以实现高并发的数据库操作，同时保持数据的隔离性。

  3. 持久性（Durability）是指一旦事务被提交，它对数据库的修改就是永久性的，即使发生系统崩溃也不会丢失。InnoDB 通过 redo 日志来实现持久性。

    • Redo 日志：每当事务对数据库进行修改时，MySQL先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。采用redo log解决上面的问题。**当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。**当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

    • redo log进行刷盘的效率要远高于数据页刷盘，具体表现如下： redo log体积小，只记录了哪一页修改的内容，因此体积小，刷盘快；redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快

  4. 一致性（Consistency）是指事务执行的结果必须使数据库从一个一致性状态转变到另一个一致性状态。InnoDB 通过多种机制来维护一致性。

    • 约束和触发器：InnoDB 支持外键约束、唯一性约束等，这些约束在事务执行过程中会被检查，以确保数据的一致性。此外，触发器可以在事务的特定点自动执行，以维护数据的一致性。

    • Undo 日志：除了用于实现原子性外，undo 日志还可以帮助维护一致性。如果事务在执行过程中违反了约束条件，则事务会被回滚，通过 undo 日志将数据库恢复到一致的状态。

InnoDB 通过 undo 日志实现原子性，通过约束、触发器和 undo 日志维护一致性，通过锁和 MVCC 实现隔离性，通过 redo 日志实现持久性。这些机制共同确保了 InnoDB 中事务的 ACID 属性，从而支持复杂的数据库应用。

MySQL 中 InnoDB存储引擎支持哪些事务类型？ ​

InnoDB存储引擎支持多种事务类型，主要包括以下几种：

  • 扁平事务：

    • 扁平事务是使用最多的事务类型，也是最简单的事务。它通常遵循“begin transaction…commit（或rollback）”的结构。

    • 扁平事务的操作为原子级，即事务中的所有操作要么全部执行，要么全部不执行。

  • 带保存点的扁平事务：

    • 这种事务类型在扁平事务的基础上增加了保存点的概念。保存点允许在事务执行过程中将事务回滚到同一事务中较早的一个状态。

    • 通过使用“savepoint id”命令创建保存点，使用“rollback to [savepoint] id”命令回滚到指定的保存点。

  • 链事务：

    • 链事务是将上一个事务的结果传递给下一个事务。它类似于在事务中开始另一个事务，但两个事务的操作被视为一个原子操作。

    • 链事务中的回滚仅限于当前事务，即只能恢复到最近的一个保存点。

  • 嵌套事务：

    • 嵌套事务具有层级结构框架，由一个顶层事务控制着各个层次的事务。

    • 子事务既可以提交也可以回滚，但其提交并不立即生效，需要等待顶层事务的提交。

    • 需要注意的是，InnoDB存储引擎本身并不直接支持嵌套事务，但可以通过保存点技术来模拟嵌套事务的行为。

  • 分布式事务：

    • 分布式事务允许多个独立的事务资源参与到一个全局的事务中。这些事务资源可能位于不同的数据库系统或不同的节点上。

    • 分布式事务要求其中的所有参与事务要么都提交，要么都回滚。

    • InnoDB存储引擎支持分布式事务，但需要在特定的事务隔离级别（如serializable）下运行，并通过两阶段提交协议来确保事务的一致性。

MySQL InnoDB存储引擎支持XA事务吗？ ​

InnoDB存储引擎支持XA事务。XA事务，全称是“eXtended Architecture Transaction”，即扩展架构事务，是一种基于两阶段提交的分布式事务处理协议，允许多个独立的事务资源参与到一个全局的事务中。

XA事务的基本流程包括准备阶段（Prepare Phase）和提交阶段（Commit Phase）。在准备阶段，事务管理器向所有参与的资源管理器发送准备指令，资源管理器执行事务操作并记录日志，然后向事务管理器返回准备结果。如果所有资源管理器都准备好提交，事务管理器则在提交阶段向所有资源管理器发送提交指令，完成事务的提交。

MySQL的存储引擎中，只有InnoDB存储引擎才支持XA事务。在使用分布式事务时，InnoDB存储引擎的隔离级别必须设置为SERIALIZABLE。通过参数innodb_support_xa可以查看是否启用了XA事务的支持（默认为ON，表示启用），在MySQL中，XA事务有两种，内部XA事务和外部XA事务。

内部XA事务

内部XA事务发生在存储引擎与插件之间或者存储引擎与存储引擎之间。由于只在单机上工作，所以被称为内部XA，此时MySQL即是协调者，也是参与者。最为常见的内部XA事务存在于二进制日志和InnoDB存储引擎之间。在事务提交时，对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写入。

当事务提交时，MySQL通过两阶段提交：

  • 第一阶段：InnoDB prepare，持有prepare_commit_mutex，并且write/sync redo log； 将回滚段设置为Prepared状态，Binlog不作任何操作；
  • 第二阶段：包含两步，首先 write/sync Binlog，然后 InnoDB commit (写入COMMIT标记后释放prepare_commit_mutex)；

以 binlog 的写入与否作为事务提交成功与否的标志，innodb commit标志并不是事务成功与否的标志。

事务崩溃恢复过程如下：

  1. 扫描最后一个Binlog文件，提取其中的xid；
  2. InnoDB维持了状态为Prepare的事务链表，将这些事务的xid和Binlog中记录的xid做比较，如果在Binlog中存在，则提交，否则回滚事务。

通过这种方式，可以让InnoDB和Binlog中的事务状态保持一致。如果在写入innodb commit标志时崩溃，则恢复时，会重新对commit标志进行写入；在prepare阶段崩溃，则会回滚，在write/sync binlog阶段崩溃，也会回滚。这种事务提交的实现是MySQL5.6之前的实现。

外部XA事务 外部XA事务就是一般谈论的分布式事务了。MySQL支持XA START/END/PREPARE/COMMIT这些SQL语句，通过使用这些命令可以完成分布式事务的状态转移。MySQL在执行分布式事务（外部XA）的时候，MySQL服务器相当于XA事务资源管理器，与MySQL链接的客户端相当于事务管理器。内部XA事务用于同一实例下跨多引擎事务，而外部XA事务用于跨多MySQL实例的分布式事务，需要应用层作为协调者。应用层负责决定提交还是回滚。MySQL数据库外部XA事务可以用在分布式数据库代理层，实现对MySQL数据库的分布式事务支持，例如开源的代理工具：网易的DDB、淘宝的TDDL等。

MySQL InnoDB SQL 执行流程是怎样？ ​

一、客户端连接和请求

  1. 客户端连接：客户端（如应用程序、开发工具等）通过网络（通常是TCP/IP）向MySQL服务器发送连接请求。
  2. 用户验证：MySQL服务器通过连接器（Connector）验证用户的身份，包括检查用户名和密码的正确性。
  3. 权限检查：验证通过后，MySQL服务器会检查用户是否具有访问指定数据库和执行特定操作的权限。权限信息存储在MySQL的系统数据库（如mysql.user表）中。
  4. 会话维护：连接器会为每个成功连接的客户端分配一个会话（Session），会话中包含了该用户的权限信息、当前数据库、连接选项等。会话会一直保持，直到客户端断开连接或发生超时。
  5. 清空查询缓存（可选）：当执行更新语句时，MySQL会清空与更新表相关的查询缓存。这是因为更新操作会改变表的数据，使得之前的查询缓存失效。

二、SQL语句接收和解析

  1. 接收请求：MySQL服务器接收到SQL语句后，会首先对其进行解析和预处理。服务器会从网络缓冲区中读取完整的SQL语句，并准备进行下一步的解析和执行。
  2. 词法分析：将SQL语句分解成一个个token（关键字、标识符、运算符等），同时对token进行分类和解析，生成相应的数据结构。
  3. 语法分析：根据SQL语法规则检查语句的正确性，并生成语法树（Parse Tree）。语法树是SQL语句的结构化表示形式，反映了SQL语句的语法结构。
  4. 语义分析：对语法树进行遍历，确定语句中表和列的信息，包括表名、列名、列类型等，同时检查语句的语义正确性。

三、查询优化

  1. 优化器处理：MySQL通过优化器对SQL语句进行处理和优化，包括执行计划的生成、索引的选择、连接方式的选择等。优化器会根据表的结构、索引等信息，选择最优的执行路径。
  2. 执行计划生成：MySQL通过执行计划生成器生成SQL语句的执行计划，即具体的执行方式，包括数据的访问方式、索引的使用方式、连接方式、排序方式等。执行计划是后续执行SQL语句的基础。

四、执行计划执行

  1. 数据库引擎处理：MySQL将执行计划发送给相应的数据库引擎（如InnoDB、MyISAM等）进行处理。执行计划可能被翻译成一组底层操作指令，如数据扫描、索引查找、排序、分组等。

  2. 权限验证：在执行之前，MySQL会再次验证用户是否具备执行该SQL语句的权限。

  3. 执行操作：数据库引擎根据执行计划执行具体的操作，如读取数据、更新数据、删除数据等。

    • 读取数据(select)

      1. 执行器初始化：

        • 执行器根据优化器生成的执行计划进行初始化。

        • 确定数据的访问方式、索引的使用方式等。

      2. 数据访问：

        • 执行器根据执行计划逐步获取数据。

        • 这可能涉及到底层存储引擎的接口调用，如InnoDB引擎的读取接口。

      3. 条件过滤：

        • 执行器对获取到的数据进行条件过滤，确保返回的数据满足SELECT语句中的WHERE条件。

      4. 结果集构建：

        • 执行器将过滤后的数据构建成结果集。

        • 结果集可能包括选择的列、排序后的数据等。

    • 更新数据、删除数据（innodb）

      1. 加载数据到Buffer Pool：

        • 当需要更新数据时，InnoDB首先会检查Buffer Pool（内存中的一块连续空间，用于缓存数据页）中是否已经存在该数据。

        • 如果数据存在于Buffer Pool中，则直接从内存中读取；如果不存在，则从磁盘中读取该数据所在的数据页，并将其复制一份存入Buffer Pool中。

        • 对相关记录加独占锁，确保数据的一致性和防止并发冲突。锁定机制可能包括行锁、表锁等，具体取决于存储引擎和事务隔离级别。

      2. 记录Undo Log：

        • 在数据修改前，InnoDB会在Undo Log 写入 idx修改之前的值和对应的主键、事务ID原来的信息。

        • Undo Log最开始写在内存中，由后台线程定时刷入磁盘。

      3. 在Buffer Pool中执行Update语句：

        • InnoDB在Buffer Pool中调用 InnoDB引擎的ha_innobase::update_row方法更新数据。

        • 将更新记录和新生成的LSN值（日志序列号）写入Log Buffer中，更新后的数据页状态会被设置为脏页（dirty page），表示该页已修改但未刷入磁盘。

      4. 记录Redo Log：

        • InnoDB将修改操作写入Redo Log buffer pool中。Redo Log记录了数据库中发生的所有修改操作，包括表的插入、更新和删除操作。

        • 此时为二阶段提交的prepare阶段。

      5. 记录BinLog：

        • 在提交过程中，InnoDB会将事务提交信息记录到BinLog（MySQL用来记录所有DDL和DML语句的二进制文件）中。

        • BinLog中记录的信息包括事务开始时间、数据库名、表名、事务ID、SQL语句等。

      6. 二阶段提交：

        • BinLog持久化：通过write()方法将BinLog写入文件缓冲区，再通过fsync()将文件缓存区中的BinLog写入磁盘。

        • RedoLog写入磁盘：将Redo Log也写入磁盘。这是二阶段提交的commit阶段。通过二阶段提交，保证了BinLog和RedoLog的一致性。

      7. 写入磁盘：

        • 在提交过程后，InnoDB会将Buffer Pool里的脏页写入磁盘，以保证数据库的持久性。

        • 这个写入过程是后台线程异步执行的，具有延迟性。后台I/O线程根据需要择机将缓存中合适的脏页刷新到磁盘数据文件中。在刷新脏页时要先拷贝一份到双写缓冲区中（如果开启了双写缓冲区功能的话），当双写缓冲区中的数据落盘之后，再从缓冲池中把脏页刷新到各个数据文件中。

      8. 事务提交：

        • 更新操作完成后，MySQL会提交事务。

        • 事务提交时，会确保redo log和binlog都已正确写入磁盘。

      9. 释放锁

        • 事务提交后，MySQL会释放之前锁定的行或表。

      10. 清理工作

        • MySQL会进行一些清理工作，如更新统计信息、释放内存等。

五、数据返回

  1. 结果生成：执行完成后，数据库引擎将执行结果返回给MySQL服务器。
  2. 结果返回：MySQL服务器将执行结果返回给客户端，对于查询语句，执行结果通常是查询结果集；对于数据操作语句，执行结果通常是操作成功或失败的信息。

六、注意事项

  • 查询缓存：在MySQL 8.0以下的版本中，如果设置了查询缓存，MySQL会首先检查查询缓存中是否存在相同的SQL语句和结果。如果存在，则直接返回缓存的结果，以提高查询效率。但由于查询缓存的命中率往往不高，且可能导致数据不一致的问题，因此在MySQL 8.0及更高版本中已废弃该功能。

  • 并发处理：MySQL服务器通常是多线程的，能够同时处理多个客户端的请求。每个会话都会由一个独立的线程处理，这样可以确保多个客户端请求之间互不干扰。

  • 两阶段提交：MySQL的更新操作采用两阶段提交机制来确保redo log和binlog的一致性。在准备阶段，更新操作被记录到redo log和内存中；在提交阶段，这些操作被持久化到磁盘上的redo log和binlog文件中。

  • 崩溃恢复：如果MySQL服务器在更新操作过程中崩溃，它可以使用redo log来恢复已提交的事务，确保数据的持久性和一致性。

  • 并发控制：MySQL通过锁机制和事务隔离级别来控制并发操作，以避免数据不一致和冲突。

MySQL 中数据类型有哪些优化策略？ ​

  • 更小的通常更好，一般情况下尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常也更快，因为它们占用更少的磁盘、内存和 CPU 缓存。
  • 尽可能简单，简单数据类型的操作通常需要更少的 CPU 周期，例如整数比字符操作代价更低，因为字符集和校对规则使字符相比整形更复杂。应该使用 MySQL 的内建类型 date、time 和 datetime 而不是字符串来存储日期和时间，另一点是应该使用整形存储 IP 地址。
  • 尽量避免 NULL，通常情况下最好指定列为 NOT NULL，除非需要存储 NULL值。因为如果查询中包含可为 NULL 的列对 MySQL 来说更难优化，可为 NULL 的列使索引、索引统计和值比较都更复杂，并且会使用更多存储空间。当可为 NULL 的列被索引时，每个索引记录需要一个额外字节，在MyISAM 中还可能导致固定大小的索引变成可变大小的索引。如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

MySQL 中EXPLAIN 的字段含义 ？ ​

执行计划是 SQL 调优的重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执行计划，如果作用在表上，那么该命令相当于 DESC。

sql
EXPLAIN select * from students where email ! "123";

img

  • id ：表示 SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。

  • select_type ：表示查询中每个 SELECT 子句的类型

    • SIMPLE 表示不包含子查询、表连接或其他复杂语法的简单查询。

    • PRIMARY 表示复杂查询的最外层查询。

    • SUBQUERY 表示在 SELECT 或 WHERE 列表中包含了子查询。

  • table：查询的表名

  • type： 表示访问类型，性能由差到好为：

    • all 全表扫描

    • index 索引全扫描

    • range 索引范围扫描

    • ref 返回匹配某个单独值得所有行，常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找，也经常出现在 join 操作中

    • eq_ref 唯一性索引扫描，对于每个索引键只有一条记录与之匹配

    • const 当 MySQL 对查询某部分进行优化，并转为一个常量时，使用这些访问类型，例如将主键或唯一索引置于 WHERE 列表就能将该查询转为一个 const

    • system 表中只有一行数据或空表，只能用于 MyISAM 和 Memory 表、NULL 执行时不用访问表或索引就能得到结果。

​ 执行性能:system > const > eq_ref > ref > range > index > all。

​ SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是consts 最好。

  • possible_keys ：表示查询时可能用到的索引，但不一定使用。列出大量可能索引时意味着备选索引数量太多了，强制使用使用索引
sql
select * from table force index(idx_start_date) where

  • key ：显示 MySQL 在查询时实际使用的索引，如果没有使用则显示为 NULL。

  • key_len： 表示使用到索引字段的长度，可通过该列计算查询中使用的索引的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。

  • ref ： 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

  • rows： 表示 MySQL 根据表统计信息及索引选用情况，估算找到所需记录所需要读取的行数。

  • Extra： 表示额外信息，

    • 例如 Using temporary 表示需要使用临时表存储结果集，常见于排序和分组查询。

    • Using filesort 表示无法利用索引完成的文件排序，这是 ORDER BY 的结果，可以通过合适的索引改进性能。

    • Using index 表示只需要使用索引就可以满足查询表得要求，说明表正在使用覆盖索引。

MySQL 中如何定位低效 SQL？ ​

可以通过以下2种办法来定位执行效率较低的SQL语句：

  • 通过慢查询日志定位。可以通过慢查询日志定位那些已经执行完毕的SQL语句。
  • 使用SHOW PROCESSLIST来查询。慢查询日志在查询结束以后才记录，所以，在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题。此时，可以使用SHOW PROCESSLIST命令查看当前MySQL正在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

找到执行效率低的SQL语句后，就可以通过 SHOW PROFILE FOR QUERYN、EXPLAIN或trace等方法来优化这些SQL语句。

MySQL 中trace 是干什么的？ ​

MySQL 5.6.3提供了对SQL语句的跟踪功能，通过trace文件可以进一步了解优化器是如何选择某个执行计划的，与Oracle的10053事件类似。在使用时需要先打开设置，然后执行一次SQL，最后查看information_schema.optimizer_trace表的内容。需要注意的是，该表为临时表，只能在当前会话进行查询，每次查询返回的都是最近一次执行的SQL语句。

MySQL 中SHOW PROFILE 的作用？ ​

MySQL可以使用profile分析SQL语句的性能消耗情况。例如，查询到SQL会执行多少时间，并显示CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。通过have_profiling参数可以查看MySQL是否支持profile，通过profiling参数可以查看当前系统profile是否开启。

以下是有关profile的一些常用命令：

  • set profiling=1; #基于会话级别开启，关闭则用set profiling=off。
  • show profile for query 1;#1是query_id。 结果中有Sending data，该状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。
  • show profile cpu for query 1;#查看CPU的消耗情况。
  • show profile memory for query 1;#查看内存消耗情况。
  • show profile block io,cpu for query 1;#查看I/O及CPU的消耗情况。
  • show profile source for query; #查看SQL解析执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数

MySQL 中如何对的大表优化？ ​

当MySQL单表记录数过大时，数据库的CRUD（C即Create，表示增加；R即Retrieve，表示读取查询；U即Update，表示更新；D即Delete，表示删除）性能会明显下降，一些常见的优化措施如下：

  • 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。
  • 读写分离：经典的数据库拆分方案，主库负责写，从库负责读。
  • 缓存：使用MySQL的缓存。另外对重量级、更新少的数据可以考虑使用应用级别的缓存。
  • 垂直分区：根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。垂直拆分的优点如下：可以使得行数据变小，在查询时减少读取的Block次数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。
  • 水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分，在表的行数超过200万行时，就会变慢，这时可以把一张表的数据拆成多张表来存放。例如：可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。水平拆分可以支持非常大的数据量。需要注意的是，分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。水平拆分能够支持非常大的数据量存储，引用程序需要的改动也很少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。

MySQL 中优化SQL的策略有哪些？ ​

  • 尽量不要创建外键
  • 尽量使用索引并避免索引失效的情况发生
  • SELECT 子句中避免使用 '*' ，全部大写SQL
  • 保证表单数据不超过200W，适时分割表
  • 当只需要一行数据的时候使用limit 1
  • 读取适当的记录LIMIT M,N。 在偏移量非常大的时候，需要查询很多条数据再舍弃，这样的代价非常高。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。最简单的办法是尽可能地使用覆盖索引扫描，而不是查询所有列，然后根据需要做一次关联操作再返回所需的列。还有一种方法是从上一次取数据的位置开始扫描（ID），这样就可以避免使用 OFFSET。其他优化方法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。
  • 分组统计可以禁止排序。 如果没有通过 ORDER BY 子句显式指定要排序的列，当查询使用 GROUP BY 时，结果自动按照分组的字段进行排序，如果不关心结果集的顺序，可以使用 ORDER BY NULL 禁止排序。
  • 尽量使用批量INSERT插入。 需要对一张表插入很多行数据时，应该尽量使用一次性插入多个值的 INSERT 语句，这种方式将缩减客户端与数据库之间的连接、关闭等消耗，效率比多条插入单个值的 INSERT 语句高。也可以关闭事务的自动提交，在插入完数据后提交。当插入的数据是按主键的顺序插入时，效率更高。
  • 优化 COUNT 查询 COUNT 是一个特殊的函数，它可以统计某个列值的数量，在统计列值时要求列值是非空的，不会统计 NULL 值。如果在 COUNT 中指定了列或列的表达式，则统计的就是这个表达式有值的结果数，而不是 NULL。COUNT 的另一个作用是统计结果集的行数，当 MySQL 确定括号内的表达式不可能为 NULL 时，实际上就是在统计行数。当使用 **COUNT()**时， 不会扩展成所有列，它会忽略所有的列而直接统计所有的行数。某些业务场景并不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，因为执行 EXPLAIN 并不需要真正地执行查询。通常来说 COUNT 都需要扫描大量的行才能获取精确的结果，因此很难优化。在 MySQL 层还能做的就只有覆盖扫描了，如果还不够就需要修改应用的架构，可以增加汇总表或者外部缓存系统。
  • 优化关联查询 确保 ON 或 USING 子句中的列上有索引，在创建索引时就要考虑到关联的顺序。确保任何 GROUP BY 和 ORDER BY 的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程。在 MySQL 5.5 及以下版本尽量避免子查询，可以用关联查询代替，因为执行器会先执行外部的 SQL 再执行内部的 SQL。
  • 优化 UNION 查询 MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。
  • 使用用户自定义变量 在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量。例如可以使用变量来避免重复查询刚刚更新过的数据、统计更新和插入的数量等。

MySQL 中如何实现分库分表？ ​

分库分表就是按照一定的规则，对原有的数据库和表进行拆分，把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。随着时间和业务的发展，数据库中的数据量增长是不可控的，库和表中的数据会越来越大，随之带来的是更高的磁盘、IO、系统开销，甚至性能上的瓶颈，而一台服务的资源终究是有限的，因此需要对数据库和表进行拆分，从而更好的提供数据服务。

分库分表的方式

  • 垂直分库/分表：垂直划分数据库是根据业务进行划分，例如将shop库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库（表）的大小来提高性能，但这种方式并没有解决高数据量带来的性能损耗。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如用户表可根据业务分成基本信息表和详细信息表等。

    • 优点：

      1. 拆分后业务清晰，达到专库专用。
      2. 可以实现热数据和冷数据的分离，将不经常变化的数据和变动较大的数据分散再不同的库/表中。
      3. 便于维护

    • 缺点：

      1. 不解决数据量大带来的性能损耗，读写压力依旧很大
      2. 不同的业务无法跨库关联（join），只能通过业务来关联

  • 水平分库/分表：水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。又比如根据用户id的值，根据规则分成若干个表。每个表结构一致。

    • 优点：

      1. 单库（表）的数据量得以减少，提高性能
      2. 提高了系统的稳定性和负载能力
      3. 切分出的表结构相同，程序改动较少

    • 缺点：

      1. 拆分规则较难抽象
      2. 数据分片在扩容时需要迁移
      3. 维护量增大
      4. 依然存在跨库无法join等问题，同时涉及分布式事务，数据一致性等问题。

分库分表工具

  1. sharding-sphere：jar，前身是sharding-jdbc；
  2. TDDL： jar，Taobao Distribute Data Layer；
  3. Mycat：中间件。

MySQL 中如何管理MySQL多实例？ ​

MySQL多实例是指在一台机器上开启多个不同的服务端口（例如：3306、3307等），运行多个MySQL服务进程，通过不同的Socket监听不同的服务端口来提供各自的服务。一般有两种方式来部署MySQL多实例：

第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势是逻辑简单、配置简单，缺点是管理起来不太方便；

第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，可以集中管理。mysqld_multi常用的命令如下所示：

  • 启动全部实例：mysqld_multi start。
  • 查看全部实例状态：mysqld_multi report。
  • 启动单个实例：mysqld_multi start 3306。
  • 停止单个实例：mysqld_multi stop 3306。
  • 查看单个实例状态：mysqld_multi report 3306。

基于 MIT 许可发布

Copyright © 2020-现在 Devin Kong