标签归档：MySQL

MySQL 事务隔离及实现

发表于由
回复

1. 事务的 ACID 特性

  • Actomic，原子性：一个事务中的所有操作，要么全部完成，要么全部失败。

  • Consistency：一致性：事务执行前和执行后数据库都处于一致的状态。

  • Isolation：隔离性：同时进行中的事务不会看到其他事务的中间状态。

  • Durability，持久性：事务提交成功后，对数据库所做的变更都是持久的。

2. SQL 标准的事务隔离级别

  • 读未提交, read uncommitted：一个事务还没提交，它做的变更就能被别的事务看到。问题：会产生”脏读”，读到的数据可能是不一致的。

  • 读已提交, read committed：一个事务提交后，它做的变更才会被别的事务看到。问题：不可重复读，在一个事务里，前后两次执行同一个 SQL 看到的数据是不一致的，产生的原因有：1. 数据被修改或删除(称为”不可重复读”)，2. 有新插入的数据（称为”幻读”）。

  • 可重复读, repeatable read：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。解决了”不可重复读”的问题，但没有解决”幻读”问题。

  • 串行化, serializable：对于同一行记录，“写”会加写锁，“读”会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。解决了”幻读”的问题，但效率低。

继续阅读 →

MySQL InnoDB MRR 优化

发表于由
回复

MRR 是 Multi-Range Read 的简写，目的是减少磁盘随机访问，将随机访问转化为较为顺序的访问。适用于 range/ref/eq_ref 类型的查询。

实现原理：

  1. 在二级索引查找后，根据得到的主键到聚簇索引找出需要的数据。
  2. 二级索引查找得到的主键的顺序是不确定的，因为二级索引的顺序与聚簇索引的顺序不一定一致；
  3. 如果没有 MRR，那么在聚簇索引查找时就可能出现乱序读取数据页，这对于机械硬盘是及其不友好的。

  4. MRR 的优化方式：

    • 将查找到的二级索引键值放在一个缓存中；
    • 将缓存中的键值按照 主键 进行排序；
    • 根据排序后的主键去聚簇索引访问实际的数据文件。

  5. 当优化器使用了 MRR 时，执行计划的 Extra 列会出现 “Using MRR” 。

  6. 如果查询使用的二级索引的顺序本身与结果集的顺序一致，那么使用 MRR 后需要对得到的结果集进行排序。

继续阅读 →

MySQL ICP 索引条件下推优化

发表于由
回复

ICP 优化的全称是 Index Condition Pushdown Optimization 。

ICP 优化适用于 MySQL 利用索引从表里检索数据的场景。

ICP 适用的场景

  • ICP 用于访问方法是 range/ref/eq_ref/ref_or_null，且需要访问表的完整行记录。
  • ICP适用于 InnoDB 和 MyISAM 的表，包括分区的表。
  • 对于 InnoDB 表，ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引，完整的记录已经读进 InnoDB 的缓存，使用 ICP 不能减少 I/O 。
  • ICP 不支持建立在虚拟列上的二级索引。InnoDB 支持在虚拟列上建立二级索引。
  • 引用子查询、存储函数的条件没法下推。
  • Triggered conditions 也没法下推。

继续阅读 →

《MySQL 实战45讲》–笔记–锁

发表于由
回复

锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁是对整个数据库实例加锁。MySQL 提供的一个加全局锁的命令： Flush tables with read lock (FTWRL)，让整个库处于只读状态。

全局锁的典型使用场景是做全库逻辑备份。

逻辑备份工具 mysqldump 使用参数 -single-transaction 的时候在导数据之前就会启动一个事务，来确保拿到一致性视图。

一致性读的前提是引擎要支持这个隔离级别的事务。

set global readonly=true 也可以让全库进入只读状态。但存在两个风险： readonly 的值可能被用来做其他逻辑，比如判等一个库是主库还是备库；设置 readonly 之后，如果客户端发生异常，则数据库会一直保持 readonly 状态。

在 slave 上，如果用户有超级权限的话，全库只读 readonly=true 是失效的。

FTWRL 执行完后如果客户端异常断开，MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。

继续阅读 →

《MySQL 实战45讲》–笔记–order by 实现

发表于由
回复

用 explain 命令查看执行计划的时候，Extra 列的值含有 “using filesort” 表示需要排序，可能是在内存里排序、也可能是磁盘文件排序。MySQL 给每个线程分配一块内存用于排序，称为 sort_buffer ，可以通过参数 sort_buffer_size 调整这个内存的大小。

如果要排序的数据都能放进 sort_buffer 则直接在内存里排序，否则需要借助磁盘临时文件进行辅助排序。

由于 InnoDB 是索引组织表，聚簇索引就是主键索引，下面的描述就用 rowid 替代主键值。

走索引全字段排序

  1. 初始化 sort_buffer，确定要放入 sort_buffer 的目标字段；
  2. 根据选择的索引查找满足条件的 rowid；
  3. 通过rowid到聚簇索引树获取整行数据，取目标字段放入sort_buffer；
  4. 从选择的索引取下一个记录的rowid；
  5. 重复步骤 3、4直到不满足条件；
  6. 对 sort_buffer 中的数据按照排序列进行排序。
  7. 遍历排序结果，把目标数据返回给客户端。

继续阅读 →

MySQL 5.7 重置 root 密码

发表于由
回复

以安全模式启动，这样登录不需要密码：

mysqld_safe --skip-grant-tables &

用 root 用户登录

mysql -u root

更新密码、刷新权限：

UPDATE mysql.user
    SET authentication_string = PASSWORD('SonarQB-0609.'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

退出安全模式，重新启动 MySQL 。

欢迎关注我的微信公众号: coderbee笔记，可以更及时回复你的讨论。

《高性能 MySQL》 — 第五章 创建高性能的索引

发表于由
回复

索引基础

索引的使用：现在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

对于有多列的索引，MySQL 只能高效地使用索引的最左前缀列。因此列的顺序很重要。

MySQL 的唯一限制和主键限制都是通过索引实现。

索引的类型

MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以没有统一的索引标准。

B-Tree 索引

B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相等，适合范围查询。

btree-structure

B-Tree 索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。

索引还可以用于查询中的 order by 操作。

B-Tree 索引的限制：

  • 如果不是安装索引的最左列开始查找，则无法使用索引。
  • 不能跳过索引中的列。
  • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

这些限制是 MySQL 优化器和存储引擎使用索引的方式导致的。

继续阅读 →

《高性能 MySQL》 — 第四章 Schema 与数据类型优化

发表于由
回复

选择优化的数据类型

选择数据类型的一般原则：

  • 更小的通常更好：尽量使用可以正确存储数据的最小数据类型。

  • 简单就好：简单数据类型的操作通常需要更少的 CPU 周期。例如整型比字符操作代价更低，以为字符集和校对规则（排序规则）使字符比较比整型更复杂。举例，应该使用 MySQL 内建的类型（date, time, datetime） 而不是字符串来存储日期和时间，应该使用整型来存储 IP 地址。

  • 尽量避免 NULL：如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM 里甚至还可能导致固定大小的索引变成可变大小的索引。例外，对于稀疏数据（很多列的值为 NULL），InnoDB 使用单独的位（bit）存储 NULL 值，有很好的空间效率。

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。下一步是选择具体类型。

继续阅读 →

MySQL 事件调度器 定时调度

发表于由
回复

MySQL 5.1 引入的时间调度器可以作为定时任务调度器，取代系统的cron调度，调度时间可以精确到秒，实时性好。

开启事件调度器

首先查看是否开启了事件调度器： show variables like "event_scheduler"; 或 SELECT @@event_scheduler;。

开启事件调度器： SET GLOBAL event_scheduler = ON;，这个命令需要具有 SUPER 权限才能执行，可以用 root 用户来执行，说明是作用在整个数据库服务上的，而不单是某个数据库实例。

MySQL服务器默认是没有开启事件调度器的，这样上述通过命令开启的在MySQL服务器重启后会失效，可以在配置文件里默认开启，在 [mysqld] 下添加配置： event_scheduler=ON 默认开启。

查看事件的执行情况

SELECT * FROM information_schema.EVENTS;

继续阅读 →

wordpress 文章索引 存储过程

发表于由
1

最近想给本站加上文章索引的页面，就是在一个页面上展示所有的分类及该分类的文章，然后置顶到首页，效果见本站文章索引。开始的思路是：先新建一个页面，然后设置好置顶等，用存储过程更新该页面的内容，用触发器在有新文章发表或文章标题有更新时触发执行存储过程。看起来应该挺顺利的，不过实现过程倒没想象那么好了。

更新索引存储过程

这个存储过程的功能是从数据库里查询出所有的分类以及该分类下的文章的ID和标题，然后用游标生成html内容（wordpress直接存储页面的html内容），再更新为索引页面的内容。
继续阅读 →