MySQL

Mysql 简易安装 安装Mysql8.0(centos8为例)， 可参考 MySQL官方文档 # CentOS el8下载 MySQL YUM 仓库（替换为最新版本） wget https://dev.mysql.com/get/mysql80-community-release-el8-6.noarch.rpm # el9 使用sudo yum install mysql84-community-release-el9-x86_64.noarch.rpm sudo rpm -Uvh mysql80-community-release-el*.rpm sudo yum makecache # 安装 MySQL 社区版服务器 # el9 的话sudo yum install mysql84-community-release-el9-x86_64.noarch.rpm sudo yum install -y mysql-community-server # 可以通过手动编辑/etc/yum.repos.d/mysql-community.repo文件来选择发布系列。 # **install时如果出现GPG 密钥问题** # sudo curl -o /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 https://repo.mysql.com/RPM-GPG-KEY-mysql-2023 # sudo chmod 644 /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 # sudo rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 # 重新执行 yum install -y mysql-community-server # 启动 MySQL 服务 sudo systemctl start mysqld sudo systemctl enable mysqld sudo systemctl status mysqld #MySQL 首次启动会生成一个临时 root 密码： sudo grep 'temporary password' /var/log/mysqld....

MySQL事务与隔离级别深度解析 一、 事务基础概念 1. 什么是事务 事务(Transaction)是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作。事务具有以下四个关键特性(ACID)： 原子性(Atomicity)：事务中的所有操作要么全部完成，要么全部不完成 一致性(Consistency)：事务执行前后，数据库从一个一致状态变到另一个一致状态 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务 持久性(Durability)：事务一旦提交，其结果就是永久性的 2. MySQL中的事务控制语句 START TRANSACTION; -- 或 BEGIN -- 执行SQL操作 COMMIT; -- 提交事务 ROLLBACK; -- 回滚事务 二、事务隔离级别详解 1. 四种标准隔离级别 MySQL支持四种事务隔离级别，控制不同事务之间的可见性： 隔离级别 脏读 不可重复读 幻读 说明 READ UNCOMMITTED（读未提交） 可能 可能 可能 最低隔离级别 READ COMMITTED（读已提交） 不可能 可能 可能 大多数数据库默认级别 REPEATABLE READ（可重复读） 不可能 不可能 可能 MySQL默认级别 SERIALIZABLE（序列化） 不可能 不可能 不可能 最高隔离级别 2. 并发问题说明 脏读(Dirty Read)：读取到其他事务未提交的数据 不可重复读(Non-repeatable Read)：同一事务内多次读取同一数据结果不同 幻读(Phantom Read)：同一事务内多次查询返回不同的行集合 3. 隔离级别设置与查看 -- 设置当前会话隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 查看当前隔离级别 SELECT @@transaction_isolation; 三、各隔离级别实现机制 1....

MYSQL中的回（Back to Table）表说明 一、 基本概念 回表是指当使用非聚簇索引（二级索引）进行查询时，首先在索引中查找到所需数据的主键值，然后再根据这个主键值回到主键索引（聚簇索引）中查找完整数据行的过程。 二、核心原理 聚簇索引 vs 非聚簇索引 -- 创建测试表 CREATE TABLE user_info ( id INT PRIMARY KEY, -- 主键，聚簇索引 name VARCHAR(50), email VARCHAR(100), age INT, created_at DATETIME, INDEX idx_email (email), -- 非聚簇索引（二级索引） INDEX idx_name_age (name, age) -- 复合非聚簇索引 ); 索引结构对比 聚簇索引（主键索引）结构： 索引节点 -> 叶子节点包含完整数据行 [id:1] -> [id:1, name:'张三', email:'[email protected]', age:25, ...] [id:2] -> [id:2, name:'李四', email:'[email protected]', age:30, ...] 非聚簇索引（二级索引）结构： 索引节点 -> 叶子节点只包含索引列 + 主键值 [email:'[email protected]'] -> [主键id:2] [email:'zhang@xx....

MYSQL中的锁 一、 MySQL锁概述 MySQL锁机制是数据库管理系统实现并发控制的核心技术，它通过在共享资源上实施访问限制，确保数据的一致性和完整性。作为PHP高级开发者，深入理解MySQL锁机制对于构建高性能、高并发的Web应用至关重要。 二、 MySQL锁分类体系 1. 按锁的粒度划分 1. 表级锁 特点 开销小，加锁快 锁定整个表 并发度低 使用场景 -- 显式加表锁 LOCK TABLES users READ; -- 共享锁 LOCK TABLES users WRITE; -- 排他锁 -- 操作完成后释放 UNLOCK TABLES; 注意事项： MyISAM引擎默认使用表锁 在InnoDB上应谨慎使用，会严重影响并发 2. 行级锁 特点 开销大，加锁慢 只锁定需要的行 并发度高 使用场景 -- 共享锁(S锁) SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- MySQL 8.0+推荐语法 SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- 排他锁(X锁) SELECT * FROM accounts WHERE id = 1 FOR UPDATE; 注意事项：...

​MySQL优化常用方法 1. EXPLAIN EXPLAIN 查看SQL执行计划 主要字段说明： type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。 key_len列，索引长度。 rows列，扫描行数。该值是个预估值。 extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。 2. SQL语句中IN包含的值不应过多 MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。 3. SELECT语句务必指明字段名称 SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。 4. 当只需要一条数据的时候，使用limit 1 这是为了使EXPLAIN中type列达到const类型 5. 如果排序字段没有用到索引，就尽量少排序 6. 如果限制条件中其他字段没有索引，尽量少用or or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。 7. 尽量用union all代替union nion和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。 8. 不使用ORDER BY RAND() select id from dynamic order by rand() limit 1000; 上面的SQL语句，可优化为： select id from dynamic t1 join (select rand() * (select max(id) from dynamic) as nid) t2 on t1....

Mysql主从复制原理及保证数据一致性 提升数据库的并发能力 提在实际工作中，我们常常将Redis作为缓存与MySQL来配合使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出，如果不存在再访问数据库。这样就提升了读取的效率，也减少了对后端数据库的访问压力。 此外，对于一般数据库应用而言，都是读多写少的，当数据库读取数据压力较大时，我们可以从成本较小的方案开始优化，可以首先考虑优化SQL和索引，其次就是缓存策略，最后才是主从架构。 主从复制的作用 读写分离。 在读多写少的情况下，可以采用读写分离，主库当做写库，然后根据实际需要，选择使用多个读库，分散读的压力，提高并发性。 数据备份。 主从复制其实就相当于一种热备份的机制。 实现高可用。 数据备份其实就是一种冗余机制，当主服务器出现故障是时，可以切换到从服务器上，提高服务器可用性。 主从复制原理 实际上主从同步的原理就是基于binlog进行数据同步的。在主从复制过程中，会基于3个线程来操作，一个主库线程，两个从库线程。 二进制日志转储线程是一个主库线程。 当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件的时候，会在Binlog上加锁，读取完成之后，再将锁释放掉。 从库I/O线程会连接到主库，向主库发送请求更新Binlog。 这时从库的I/O线程就可以读取到主库的二进制日志转储线程发送的Binlog更新部分，并且拷贝到本地的中继日志。 从库SQL线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。 总结起来就是三步： 步骤1：Master将写操作记录到二进制日志（binlog），这些记录叫做二进制日志事件（binary log events）； 步骤2：Slave 将 Master 的 binary log events拷贝到它的中继日志（relay log）； 步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 搭建 TODO 此处省略，待补充 如何解决数据一致性问题 进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟，这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。 方案一、异步复制 异步模式就是客户端提交COMMIT之后不需要等从库返回任何结果，而是直接将结果返回给客户端，这样做的好处是不会影响主库写的效率。 但这样可能会存在主库宕机，而Binlog还没有同步到从库的情况，也就是此时的主库和从库数据不一致。 这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。 方案二、半同步复制 半同步复制的原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了Binlog，并且写入到中继日志中，再返回给客户端。 这样做的好处是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。 在MySQL5.7版本中还增加了一个参数，可以对应答的从库数量进行设置，默认为1，也就是说只要有1个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。 方案三、组复制 异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景。 组复制技术MGR很好地弥补了这两种复制模式的不足，它是MySQL在5.7.17版本中推出的一种新的数据复制技术，是基于Paxos协议的状态机复制。 原文链接：

MySQL 分库分表 + 主从复制 一、主从复制 1. 环境说明 1个master节点，2个slave节点 mysql-master mysql-slave1 mysql-slave2 此处使用docker启动多个 MySQL 容器,为每个容器挂载配置文件 docker run -d --name mysql-master -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:8.0 docker run -d --name mysql-slave1 -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 mysql:8.0 docker run -d --name mysql-slave2 -e MYSQL_ROOT_PASSWORD=root -p 3308:3306 mysql:8.0 创建配置文件目录 mkdir -p ~/mysql/master ~/mysql/slave1 ~/mysql/slave2 2. 修改配置 master的 my.cnf （~/mysql/master/my.cnf） [mysqld] server-id=1 log_bin=mysql-bin binlog_format=ROW gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON slave1 的 my.cnf（~/mysql/slave1/my.cnf） [mysqld] server-id=2 relay_log_recovery=ON read_only=ON super_read_only=ON gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON slave2 的 my....

Mysql的一些常见题 请写出数据类型(int char varchar datetime text)的意思；请问 varchar 和 char有什么区别？ Int 整数char 定长字符 Varchar 变长字符 Datetime 日期时间型Text 文本型 Varchar与char的区别 char是固定长度的字符类型，分配多少空间，就占用多长空间。Varchar是可变长度的字符类型，内容有多大就占用多大的空间，能有效节省空间。由于varchar类型是可变的，所以在数据长度改变的时，服务器要进行额外的操作，所以效率比char类型低。 MyISAM和 InnoDB 的基本区别？索引结构如何实现？ A、MyISAM类型不支持事务，表锁，易产生碎片，要经常优化，读写速度较快，适合用于频繁查询的应用； B、InnoDB类型支持事务，行锁，有崩溃恢复能力，读写速度比MyISAM慢，适合于插入和更新操作比较多的应用，空间占用大，不支持全文索引等。创建索引：alert table tablename add index 索引名 (字段名) 什么是事务？及其特性？ 答：事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。 事务特性：A、原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。B、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态C、隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，D、持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。 或者这样理解：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。 什么是锁？ 答：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。 加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。 基本锁类型：锁包括行级锁和表级锁 索引的作用？和它的优点缺点是什么？ 索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。 怎么防止sql注入？ 1）过滤掉一些常见的数据库操作关键字：select,insert,update,delete,and,*等，或者通过系统函数：addslashes(需要被过滤的内容)来进行过滤。 2）在PHP配置文件中 Register_globals=off;设置为关闭状态 //作用将注册全局变量关闭。 比如：接收POST表单的值使用$_POST[‘user’],如果将register_globals=on;直接使用$user可以接收表单的值。 3）SQL语句书写的时候尽量不要省略小引号(tab键上面那个)和单引号 4）提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的 5）对于常用的方法加以封装，避免直接暴漏SQL语句 6）开启PHP安全模式 Safe_mode=on; 7）打开magic_quotes_gpc来防止SQL注入 Magic_quotes_gpc=off;默认是关闭的，它打开后将自动把用户提交的sql语句的查询进行转换，把’转为'，这对防止sql注入有重大作用。 因此开启：magic_quotes_gpc=on; 8）控制错误信息 关闭错误提示信息，将错误信息写到系统日志。 9）使用mysqli或pdo预处理。 数据库优化策略？ 合理的表设计。 依据三范式，设计表. ​ 三范式：1.原子性，每个字段都是不可在分的。 在1方式的基础上，表中每一列必须有唯一性，其他字段依赖主键。 在2方式的基础上，表中的每一列只与主键直接相关，而不是间接相关。 选择合适的字段。 I.尽量使用TYPEINT、SMALLINT、MEDIUM_INT代替INT的使用，一般索引，并且是字段递增，可以考虑设置为UNSIGNED. ​ II.使用枚举代替字符串类型。 ​ III.将少null的使用，null很难优化，并且还占用额外的空间。...

MySQL 死锁：产生原因、分析与解决 在高并发系统中，数据库死锁是比较常见的问题。 很多开发者第一次遇到死锁时，会认为： 数据库是不是出问题了？ 实际上，死锁本身并不意味着 MySQL 出现了故障。对于 InnoDB 来说，死锁是并发事务之间形成循环等待后，由存储引擎主动检测并处理的一种正常机制。 真正需要解决的问题不是简单地“关闭死锁”，而是： 为什么会产生死锁？ 哪些 SQL 导致了循环等待？ 为什么某个事务被回滚？ 如何从根本上减少死锁？ 应用程序收到死锁异常后应该怎么处理？ 理解这些问题，才能正确处理生产环境中的死锁。 1. 什么是死锁 死锁（Deadlock）是指两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，最终形成循环等待。 例如有两个账户： 账户 1 账户 2 事务 A： 锁住账户 1 ↓ 等待账户 2 事务 B： 锁住账户 2 ↓ 等待账户 1 最终形成： 事务 A → 等待事务 B 持有的锁 事务 B → 等待事务 A 持有的锁 这就是典型的死锁。 需要注意，死锁和锁等待不是一回事。 普通锁等待可能是： 事务 A 持有锁 ↓ 事务 B 等待事务 A 只要事务 A 最终提交或回滚，事务 B 就可以继续执行。...

MySQL 常用优化方法 MySQL 性能优化不能简单理解为“加索引”。SQL、索引、数据量、执行计划、表结构以及业务访问方式都会影响最终性能。 下面整理一些实际开发中比较常用的 MySQL 优化方法。 1. 使用 EXPLAIN 分析 SQL 分析慢 SQL 时，首先应该查看执行计划： EXPLAIN SELECT * FROM orders WHERE user_id = 10001 AND status = 1 ORDER BY created_at DESC LIMIT 20; 重点关注： 字段 说明 type 表的访问方式，如 const、ref、range、ALL possible_keys 可能使用的索引 key 实际使用的索引 key_len 实际使用的索引长度 rows 优化器估算需要扫描的行数 filtered 过滤后的数据比例 Extra 额外执行信息 重点关注： type = ALL rows 很大 key = NULL Using filesort Using temporary 但这些结果并不代表一定有问题，需要结合实际数据量和执行时间判断。 MySQL 8.x 还可以使用： EXPLAIN ANALYZE SELECT ....