高性能MySQL读书笔记

[toc]

序言

开篇2022/11/11，希望这本书是个开端。

本书从MySQL的架构开始说起，显示介绍了锁、事务、隔离日志等基础的概念

然后说起我们应该以什么样的标准去定义监控这个事情

接着介绍起了Performance Schema这个监控工具，如何配置、使用、理解。

接着就说部署MySQL的时候，操作系统和硬件如何优化、服务器如何设置。

最后是老生常谈的数据库schema设计原则、索引优化、查询优化

这部分是开发人员最应该关注的部分

接着是如何复制MySQL、如何备份与恢复，这部分是架构师和运维最应该关注的部分。

最终介绍了MySQL的扩展、云上MySQL等等。

MySQL架构篇

连接管理与安全性

默认情况下，每个客户端连接都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的县城中执行，该线程驻留在一个内核或者CPU裳。服务器维护了一个缓存区，用于存放已就绪的线程，因此不需要为每个新的连接创建或者销毁线程。

原文如是说，根据从其他博客看到的说法，那就是当连接数等于CPU的物理内核数量的时候，MySQL会获得最佳性能。

并发控制

假设把数据库看做成一个Excel表格，那么当很多人同时读取与修改的时候，会发生什么呢？如果是为了避免修改冲突，可能有人会建议轮流修改。但是这样效率太低了。

读写锁

引入读写锁可以保证读取和写入得到正确结果、

锁分为两种，一种叫共享锁（shared lock），也叫读锁（read lock）；一种叫做排他锁（exclusive lock），也叫做写锁（write lock）

锁的概念描述如下：**资源上的读锁是共享的，或者说是相互不阻塞的。**多个客户端可以同时读取同一个资源而互不干扰。**写锁是排他的，也就是说，一个写锁即会阻塞读锁也会阻塞其他的写锁，这是处于安全策略的考虑。只有这样才能确保在特定的时间点只有一个客户端能执行写入。**在实际的数据库中，每时每刻都在发生锁定，但是只要锁的管理速度足够快，就不会引起客户端的感知。

锁的粒度

加锁也是要消耗资源的。上锁、检查锁、释放锁等都会影响数据库的性能。我们要在安全性和性能找到一个平衡点。

表锁

表锁（table lock）是开销最小的锁。它会锁定整张表。写锁队列和读锁队列是分开的，但是写锁队列的优先级绝对高于读队列。

行级锁

大部分商业数据库提供的都是这种锁，可以最大程度的支持并发处理，但是开销也大。行级锁相当于锁定Excel表格中的一行，其它客户端读写其他行是不受影响的。

行级锁是在存储引擎而不是服务器中实现的。

事务

事务有四个原则--ACID原则。

• 原子性 atomicity

  一个事务必须设为一个不可分割的工作单元。也就是要么一起成功，要么一起失败。

• 一致性 consistency

  数据库总是从一个一致性状态转换到下一个一致性状态。

• 隔离性 isolation

  一个事务所做的修改在最终提交以前，对其他事务是不可见的，这就是隔离性带来的结果。

• 持久性 durability

  一旦提交，事务所做的修改就会被永远保存到数据库中。此时几时系统崩溃，数据也不会丢失。

可以通过START TRANSACTION开启事务

隔离级别

• READ UNCOMMITTED 未提交读

  可以读取其他事务未提交的数据，存在脏读，不建议使用

• READ COMMITTED 提交读

  大部分数据库的默认隔离级别，但MySQL不是。这个的定义是一个书屋可以看到其他事物在他开始之后提交的修改，但在改事务提交之前，其所做的任何修改

• REPEATABLE READ 可重复读

• SERIALIZABLE 可串行化

死锁

两个事务都在等对方的资源那么就会产生死锁。数据库系统实现了各种死锁检测和锁超时机制，而InnoDB检测到死锁之后就会立即返回一个错误。这样就不需要等死锁检测了。InnoDB目前处理死锁的方式是将持有最少航迹排他锁的事物回滚。

事务日志

事务日志有助于提高事务的效率。存储引擎只需要更改内存中的数据副本，而不用每次修改磁盘中的表，这样会非常快。然后再讲更改的记录写入事务日志中，事务日志会被持久化保存在硬盘上。因为事务日志采用的是追加写操作，是在硬盘中的一小块区域的顺序IO，所以会比随机IO会快。最后会有一个后台进程在某个时间去更新硬盘中的表。因此，大多数使用这种技术的存储引擎修改数据最终需要斜土磁盘两次。

MySQL的事务

单个INSERT、UPDATE或者是DELETE操作，都会被隐式包装在一个事务中并在执行成功后立即提交，这成为自动提交摩米士。

在当前的连接中，可以通过 SET AUTOCOMMIT = '0'来禁止自动提交，知道发生COMMIT语句或者ROLLBACK。

不要在同一个事务中混合使用不同的存储引擎！

多版本并发控制

MySQL使用的不是普通的行级锁机制，而是会结合多版本并发控制技术（MVCC）来使用。

MVCC可以看做是行级锁的一种变种，它在很多情况下避免了加锁这个操作，所以开销低。二线写操作也是只锁定必要的行。

当一个事务首次读取到任何数据的时候，会分配一个事务ID，也就是txn ID。当事务修改记录的时候，就会想Undo日志写入一条说明如何回复改更改的Undo记录，并且将事务回滚的指针指向这里。如果崩溃了，就会利用Undo日志回滚。

如果在这个事务还没有提交的时候，Undo日志是不符合第二个事务B的要求的，这个时候B事务会一直循环获取Undo日志，直到符合要求。也就是不需要上锁，提高了性能。

数据文件结构

在8.0版本中，表的元数据重新设计为一种数据字典，包含在标的.ibd文件中。

可靠性工程世界中的监控

可靠性工程中，有几个专业的术语。

• 服务水平指标（SLI）

  也就是如何衡量呵护是否满意的指标。例如说客户需要压测的时候，接口在200ms内响应。那么这个指标就是SLI

• 服务水平目标（SLO）

  根据SLI指定的一个百分比目标，比如我这个接口要做到99.9%的时候都能满足在200ms之内。

• 服务水平协议（SLA）

  SLA回答了“我如果接受了SLO之后，会产生什么后果”。比如说我的SLO只定了90%可用，那甲乙双方应针对这剩下10%的情况下产生的后果进行责任划分，一般来说应由甲方承担，如果乙方没有达到SLO的话，又应该收到什么惩罚。

别再定什么百分百了，折磨人。

监控解决方案

既然制定了SLI和SLO，那就应当使用一些工具进行监控，商业选项有SolarWinds，开源选项有Percona。

还有一种方式是使用MySQL Performance Schema输出的信息和慢查询日志发送到一个集中的位置，使用类似于pt-query-digest工具去分析。

监控可用性

什么叫可用？现在的业务系统复杂，需要详细定义，例如当并发过高，触发了熔断的时候，降级的接口算不算可用？

验证可用性的首选方法是从客户端或者远程端点来统计，例如访问tomcat日志、客户端日志。

MySQL还有一个叫做Thread_running状态计数器可以当做关键指标。这个计数器跟踪的是给定数据库主机上当前正在运行的查询数量。

当运行的线程快速增长且没有下降的迹象时，说明查询的不够快，因此正在堆积和消耗资源。

Thread_running应当不超过CPU的核数，可以与max_connections相减得出的结果来判断工作是否过载。

监控查询延迟

可以使用类似于DataDog或者是SolarWinds之类的付费工具直接监控数据库，也可以在客户端使用Druid这种工具来统计。

监控报错，如果lock wait timeout报错急剧增加，可能是主节点上的行级锁争用扩大，即事务不断重试但是仍然失败。如果Aborted connections激增，表明数据库和客户端实例之间的某个访问层除了问题。

另外一个重大的错误是"too many connections"或者系统级别的"cannot create new thread"，这些迹象表明系统层面提供的连接数被限制了。

主动监控可以监控磁盘空间使用率增长或者是连接数量增长。

复制延迟 MySQL原生提供复制功能，但是这会导致一定的延迟

I/0使用率 数据库工程师的目标之一就是尽可能的在内存中工作，因为这样更快。但是这是不可能完全实现的。所以会吧数据写在磁盘这里。通过使用iostat这样的工具监控IO等待的话，就会比较好

自增主键空间 一个主键空间是有限的，如果空间超过了自增主键的范围，会产生错误。

Performance Schema

MySQL有一个叫做***Performance Schema***的数据库，存储了非常多参数。

先介绍两个概念，一个是程序插桩（instrument）。程序插桩在MySQL代码中插入胎侧代码，一遍获取想要了解的信息。例如想要手机关于元数据锁的使用情况。需要要启用***wait/lock/mete-data/sql/mdl***这个插桩

第二个是消费者表（consumer），指的是存储关于程序插桩代码信息的表。如果我们为查询模块添加插桩，响应的消费者表将记录诸如执行总数、未使用索引的次数、花费的时间等信息。

插桩元件

performance schema中，有一个表叫做setup_instruments，这个表可以控制不同的插桩开启;

select * from `performance_schema`.setup_instruments where DOCUMENTATION is not null LIMIT 5,5;

消费者表

消费者表是插桩发送消息的目的地。测量结果存储在Performance schema数据库中的多个表当中。

消费者表可以分为几个类别

1. 当前和历史数据

   存放事件的表名如下

   *_current 当前服务器上进行中的事件

   *_history 每个想成最近完成的10个事件

   *_history_long 全局来看，每个线程最近完成的10000个事件

   *_history 和 *_history_long的大小是可以配置的

   存放当前和历史数据

   events_waits 底层服务器等待，例如获取互斥对象

   events_statement SQL查询语句

   events_stages 配置文件信息，例如创建临时表或者发送数据

   events_transactions 事务

2. 汇总表和摘要

   汇总表保存有关该表锁建议的内容的聚合信息。memory_summary_by_thread_by_event_name

   摘要指的是去掉查询中的变量来聚合查询的方法。例如select user,info where user_id ='123'的摘要就是 select user,info where user_id = ？

3. 实例表

   指的是对象实例，用于MySQL安装程序。例如file_instances表包含了文件名和访问这些文件的线程数

4. 设置表

   设置表用于performance_schema的运行时设置。

5. 其他表

   还有一些没有遵循严格的命名模式的，以后再接触。

资源消耗

performance schema收集的数据存在内存中。可以通过设置消费表的最大大小来限制其使用的内存量。

局限性

• 需要MySQL组件的支持，如果用了不支持程序插桩的引擎则无法统计
• 要特定的插桩和用户启用之后才收集数据
• 很难释放内存

sys Schema

sys Schema是基于performance_schema的识图和存储例程组成。

理解线程

每个线程至少有两个唯一的标识符：一个是操作系统线程id，另外一个是MySQL内部线程id。

操作系统线程ID可以通过相关工具查看，列入Linux系统中可以使用 ps -eLF命令查看。

MySQL内部线程ID在大多数performance_schema中以THREAD_ID去命名。此外，每个前台线程都有一个指定的PROCESSLIST_ID：连接标识符。在SHOW PROCESSLIST命令输出中或者在MySQL命令行客户端连接时在your MySQLconnection id is字符串中可以看到

performance_schema中的threads表包含了服务器中存在的所有线程。

配置

performance_schema的部分设置只能在服务器启动时更改：比如启用或者禁用performance_schema本身以及内存使用和数据手机的限制相关的变量。

启用或者禁用Performance Schema

将变量performance_schema设置为ON或者OFF，可以在配置文件中更改，要么在MySQL服务器启东市通过命令行参数更改

启用或禁用插桩

通过setup_instruments表查看插桩的状态

select * from `performance_schema`.setup_instruments where name = 'statement/sql/select'

这个查询语句就显示select语句的插桩已经启动了。下面有几种方法进行更改状态

update 语句

update `performance_schema`.setup_instruments set ENABLED='YES' where NAME='statement/sql/select';

SYS 存储过程

sys schema提供了两个存储过程：ps_setup_enable_instrument和ps_setup_disable_instrument

可以这么使用

call sys.ps_setup_disable_instrument('statement/sql/select');

启动选项

以上两种方式重启后都会失效，可以通过performance-schema-instrument配置

performance-schema-instrument='statement/sql/select=ON'

启用或者禁用消费者表

同样也有三种方式

• setup_consumer
• ps_setup_enable_consumer和ps_setup_disable_consumer
• performance-schema-consumer

特定对象的监控

可以针对某一种对象进行启用或者禁用监控，这在setup_objects中完成

对象可以是下面五个值之一：EVENT FUNCTION PROCEDURE TABLE TRIGGER

例如要关闭test数据库中触发器的信息采集，可以使用一下语句，名称支持通配符

inset into performance_schema.setup_objects (OBJECT_TYPE,OBJECT_SCHEMA,OBEJCT_NAME,ENABLED) VALUES('TRIGGER'.'TEST','%','NO')

优化线程的监控

setup_threads表包含可以监控的后台线程列表，ENABLED列指定是否启用，HISTORY指定监测时间是否也应存在_history和_history_long当中

用户线程不在setup_threads表中，而是在setup_actors中。

对象监控和线程监控都没有配置文件选项，需要自己写进sql，然后再启动的时候使用init_file选项加载这个sql文件。

使用Performance Schema

接下来使用一些案例来演示如何使用performance schema解决常见的故障案例。

检查SQL语句的性能

想要检查SQL语句的性能，需要启动语句检测，需要启用statement类型的插桩

常规SQL语句

Performance Schema将语句指标存储在events_statements_current、events_statements_history和events_statements_history_long

这几个表的结构完全一样的，只不过是历史记录和现有记录的区别而已

直接使用performance_schema。下面是一个events_statements_history的例子

select * from events_statements_history LIMIT 1,1

为了方便查看，转为json导出的结果

{
  "RECORDS": [
    {
      "THREAD_ID": 763,
      "EVENT_ID": 30,
      "END_EVENT_ID": 30,
      "EVENT_NAME": "statement/sql/show_status",
      "SOURCE": "init_net_server_extension.cc:95",
      "TIMER_START": 588010785729729000,
      "TIMER_END": 588010786772201000,
      "TIMER_WAIT": 1042472000,
      "LOCK_TIME": 1000000,
      "SQL_TEXT": "SHOW STATUS",
      "DIGEST": "5b22cb99afb7229f7af732c4708b4811e14df98c1d8326ce65822a0af5e0fd2d",
      "DIGEST_TEXT": "SHOW STATUS",
      "CURRENT_SCHEMA": "performance_schema",
      "OBJECT_TYPE": null,
      "OBJECT_SCHEMA": null,
      "OBJECT_NAME": null,
      "OBJECT_INSTANCE_BEGIN": null,
      "MYSQL_ERRNO": 0,
      "RETURNED_SQLSTATE": null,
      "MESSAGE_TEXT": null,
      "ERRORS": 0,
      "WARNINGS": 0,
      "ROWS_AFFECTED": 0,
      "ROWS_SENT": 496,
      "ROWS_EXAMINED": 496,
      "CREATED_TMP_DISK_TABLES": 0,
      "CREATED_TMP_TABLES": 1,
      "SELECT_FULL_JOIN": 0,
      "SELECT_FULL_RANGE_JOIN": 0,
      "SELECT_RANGE": 0,
      "SELECT_RANGE_CHECK": 0,
      "SELECT_SCAN": 2,
      "SORT_MERGE_PASSES": 0,
      "SORT_RANGE": 0,
      "SORT_ROWS": 0,
      "SORT_SCAN": 0,
      "NO_INDEX_USED": 1,
      "NO_GOOD_INDEX_USED": 0,
      "NESTING_EVENT_ID": null,
      "NESTING_EVENT_TYPE": null,
      "NESTING_EVENT_LEVEL": 0,
      "STATEMENT_ID": 100,
      "CPU_TIME": 0,
      "MAX_CONTROLLED_MEMORY": 1115360,
      "MAX_TOTAL_MEMORY": 1353656,
      "EXECUTION_ENGINE": "PRIMARY"
    }
  ]
}

下面这几个指标，可以当做优化的目标，相关列的解释可以在官方的文档中找到。

如果要找出哪些语句要优化，可以选择上述列中的任何一列，并将其与0进行比较、

例如要找到所有的没有使用合适索引的查询，可以运行一下命令：

SELECT THREAD_ID,SQL_TEXT,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;

例如要查询所有创建了临时表的查询，可以运行：

SELECT THREAD_ID,SQL_TEXT,ROWS_SENT,ROWS_EXAMINED,CREATED_TMP_TABLES,CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history_long
WHERE CREATED_TMP_TABLES > 0 OR CREATED_TMP_DISK_TABLES > 0;

也可以使用这些列单独显示潜在的问题。例如查找所有返回错误的查询，可以使用条件WHERE errors>0; 要找到所有执行时间超过5秒的查询，可以使用条件WHERE TIMER_WAIT > 5000000000; (50亿纳秒)

使用sys schema

sys schema提供了一些查找有问题语句的识图。例如statements_with_errors_or_warnings列出了所有带有错误和警告的所有语句，statements_with_full_table_scans列出了需要全表扫描的所有语句。sys schema使用摘要文本而不是查询文本，因此你将获得查询文本的摘要。

其他的可以用于查找需要优化的语句的识图如表所示

预处理语句

prepared_statements_instances表包含服务器中存在的所有预处理语句。它和events_statements_[current|history|history_long]表具有相同的统计数据。此外还有关于预处理语句所属的线程以及该语句被执行了多少次的信息。和events_statements_[current|history|history_long]不同的是，这个统计数据是勒脚的，这个表包含所有语句执行的总量。

要监测预处理语句，则需要启用以下的插桩

什么是预处理语句

所谓预处理，即在真正执⾏某条SQL语句之前，先将SQL语句准备好，在执⾏过程中再绑定数据

例如下面这个例子，就展示了预处理一条SQL语句，然后定义一个变量，最后使用EXECUTE执行stmt这条语句，并且参数使用@hd

PREPARE stmt FROM 'SELECT COUNT(*) FROM employees WHERE hire_date > ?'；
SET @hd = '1995-01-01';
EXECUTE stmt USING @hd;

mybatis 使用 #{}进行参数传递的时候，就会将SQL语句进行预处理

注意，一旦预处理语句被删除，就不能再访问它的统计信息了。

存储例程

使用performance_schema可以说检查有关存储例程如何执行的信息：例如IF...ELSE流控制语句的那个分支被选择了，或者是和否调用了错误的处理程序。

要启用存储历程监测，需要启用匹配'statement/sp/%'模式的插桩。statement/sp/stmt插桩负责历程内部电泳的语句，而其他插桩则负责跟踪事件

语句剖析

events_stages_[current|history|history_long]表包含剖析信息，例如MySQL的在创建临时表、更新或者等待锁时花费了多少时间。

需要启动匹配'stage/%'模式的插桩。启用后可以找到类似“查询执行的那个阶段花费了非常长的时间”等问题的答案。下面的例子进入搜索耗时超过一秒的阶段：

SELECT
	eshl.EVENT_NAME,
	SQL_TEXT,
	eshl.TIMER_WAIT / 100000000 w_s 
FROM
	`performance_schema`.events_stages_history_long eshl
	JOIN `performance_schema`.events_statements_history_long esthl ON eshl.NESTING_EVENT_ID = esthl.EVENT_ID 
WHERE
	eshl.TIMER_WAIT > 1 * 100000000

使用这个标的另一种技术就是注意那些在一直阶段中花费超过了某个阈值的语句，这些语句会导致性能问题。

**非常重要的一点是，只有通用服务模块支持剖析。**存储引擎不支持使用performance_schema进行剖析。因此类似stage/sql/update等阶段意味着任务在存储引擎中运行，不仅包含更新自身的统计，还会包含等待存储引擎特定的锁或者其他争用的问题。

检查读写性能

Performance Schema的statement类型插桩对于理解工作负载是首都还是受写限制非常拥有。可以从统计各类语句的执行量入手：

SELECT EVENT_NAME, COUNT(EVENT_NAME) FROM events_statements_history_long GROUP BY EVENT_NAME;

如果像只大语句的延迟情况，可以按照LOCK_TIME列进行聚合

检查元数据锁

元数据锁用于保护数据库对象定义不会呗修改。执行任何sql语句都需要获取共享元数据锁：SELECT、UPDATE等。如果被上锁了，那么ALTER TABLE或者CREATE INDEX会等到锁释放才能被执行。虽然大多数元数据锁冲突是由表引起，但元数据锁本身是可以在各种数据库对象上设置的，例如SCHEMA、EVENT、TABLESPACE等。

事务执行期间一直会持有元数据锁。多语句事务的使用会是的故障排除变得更加困难。很容易搞清楚哪个语句在等待元数据锁:DDL语句会隐式提交事务，因此它们是新事务中唯一的语句，并且可以再京城列表中发现它们处于“waiting for a metadata lock”状态。但是在京城列表中可能找不到持有元数据锁的语句，这些语句已经执行完成，但是包含这些语句的事务没有提交。

performance_schema中的metadata_locks表包含关于当前由不同线程设置的锁的信息以及处于等待状态的锁请求信息。通过这种方式，可以轻松确定哪个线程阻塞了DDL请求，你可以决定是种植该语句还是等待它完成执行。

要启用元数据锁监测，需要启用wait/lock/meta-data/sql/mdl插桩

检查内存使用情况

使用了memory类的插桩。

直接使用performance schema

相关信息存储在摘要表中，以memory_summary_前缀开头。内存使用聚合统计，其参数如表所示

使用sys schema

memory_global_total视图包含一个单独的值，显示被监测内存的总量。

memory_by_thread_by_current_bytes 视图有一个user列，可以采用NAME@HOST或者是系统用户名来统计那个县城占用了大部分的内存。

检查变量

performance schema提供了很多方面的变量监测。全局变量被存储在global_variables表当中。当前会话的会话变量被存储在session_variables表当中。

variables_by_session表有一个额外的列,THREAD_ID，表示变量所属的线程。

还有会话状态[global|session]_status

按照线程聚合比较方便，可以清晰的识别那个连接正在服务器上造成了大部分的资源压力。

检查最常见的错误

按照用户、主机、账户、线程等聚合表。命名结构类似于events_errors_summary_global_by_error

小结

performance schema在以前是一个被批评的特性，因为实现方法不够好，会耗费性能，所以大家是建议关闭的。

但是在8.0以上，本书作者认为这个功能做的已经很好了，所以默认启用吧。可以很直观的获取一手性能监测的数据

操作系统与硬件优化

本章不是我的重点，但也需要了解。

什么限制了MySQL的性能

最常见到的瓶颈是CPU耗尽。这是因为MySQL尝试并发执行太多的查询。

其次是I/O饱和，但是发生频率少的多。过去使用HDD的时候才会经常出现，而现在，SSD已经很普及，性能很好。特别是PICE 4.0的固态，甚至能达到7000MB/S

最后是内存耗尽，但是可以通过配置来减少发生。

如何选择CPU

低延迟 -> CPU频率高的

高吞吐量 -> CPU核心数量多的

平衡内存与磁盘

内存并不是为了保存数据，而是为了避免磁盘IO。平衡这两者之间大大小、速度、成本。

缓存、读取与写入

如果有足够的内存，就可以完全避免磁盘读取操作。如果所有数据都能装入内存，那么服务器的缓存预热完成，每次读取都将是一次缓存命中。这种情况将会非常的快。

然而写入是另外一回事，写入依旧会在内存执行，但是迟早会被写入磁盘，这样才能持久化数据。所以缓存可以延迟写操作，消除读操作，但是不能消除写操作。

事实上，缓存还允许两种重要的方式的组合。

多次写操作，一次刷新：一个数据片段可以在内存中多次更改，然后把最后的结果合并，只需要写入一次磁盘。

I/O合并：许多不同的数据片段在内存呗修改，这些修改可以被收集在一起。因此物理写可以作为单个磁盘操作执行。

这就是为什么许多事务系统使用提前写日志策略的原因。提前写日志允许在内存中更改页面，而不用将更改刷新到磁盘。这通常涉及随机I/O，速度非常慢。

相反，先合并成为一个操作，顺序写入日志文件的话，可以进行顺序I/O，性能更好。

固态存储

2022年了，还用HDD当主存储的公司，还不跑路？最好是选择PICE 4.0的固态了。

固态硬盘建立在闪存颗粒上面，闪存可以的复杂程度仍在机械硬盘之上。

闪存最重要的特点是读取很快，但是写入要困难得多。只有进行特殊的擦除操作之后，存储单元才能重新被写入数据，并且每次擦除的块的大小较大。

固态会进行垃圾收集、损耗均衡等操作。写入放大则描述部分块写而导致数据从一处移动到另一处、多次写入数据和元数据所产生的额外写操作。

并且固态如果剩余的空间很小的话，影响垃圾收集，会显著的降低速度。

RAID性能优化

• RAID 0 最便宜，但是不会提供冗余，并且故障率比单块磁盘更高，但是性能会好。
• RAID 1 许多场景有比较好的读性能，并且可以跨磁盘复制数据，有良好的冗余。读取略高于RAID 0，对日志记录和类似工作负载的服务器很好。因为顺序写入不需要很多底层磁盘就执行的很好。

剩下的对于小公司来说比较奢侈和困难，直接上表格

服务器硬件的构建我很少用得到，所以RAID故障、恢复、和监控就不再详细赘述。

只需要记住，RAID虽然提供冗余，但是也不要低估硬盘同时出错的概率。

网络配置

网络运输不正常也是瓶颈之一。数据对包是最常见的一个问题，即使是1%的丢失，也能导致显著的性能下降。因为协议栈的各个层都会尝试通过等待一段时间然后重新发送数据包等策略来解决问题。这回增加额外的响应时间。另一个常见的问题就是DNS解析异常中断或者缓慢。

如果DNS解析中断或者缓慢，对于许多应用程序来说都是问题。如果启用skip_name_resolve的话，MySQL就不会再进行任何的DNS查找，这就意味着用户账号在host列中只能有IP地址

“localhost”或者IP地址通配符。

不过，通常更重要的是，调整设置以有效处理大量连接和小查询。一个常见的调整就是更改本地端口范围。Linux系统有一系列可以使用的本地端口。但是有可能会用完

例如下面这个配置为默认的系统：

cat /etc/proc/sys/net/ipv4/ip_local_port_range
32768 61000

可以设置为1024 65535

TCP允许系统对接收到的连接请求进行排队。也可以配置这个参数，使得排队的连接变多一些

echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog

对于仅本地使用的数据库服务器，如果服务器中断后未关闭其连接段，可以缩短关闭套接字后的超时时间。大多数系统默认一分钟，这个很长了

echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout

大多数时候，可以保留这些默认值，发生异常的时候在进行更改吧。在互联网搜索"TCP参数变量"，可以看到很多案例。

选择文件系统

Windows来说，只有NTFS是最合适的，那么Linux系统选择哪个呢？很多测试都显示，大差不差，只有系统达到极限的时候，才会体现出差别。

一般来说，选择带有日志型的文件系统。如果ext4、XFS、ZFS。作者推荐XFS

磁盘调度器

linux默认是CFQ，但是有可能导致性能问题。

可以查看哪些调度器可用

cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

前面两种差别很小，最重要的还是不要使用CFQ。

内存与交换

MySQL内存越大，性能越好。为了保证更快的内存访问，是使用外部内存分配器（如tcmalloc或者jemalloc）替换内置的内存分配器(glibc)，这样性能会快很多，减少了内存碎片。

当服务器没有足够多的内存的时候，会将一部分内存存放到磁盘里，这叫做虚拟内存。这种操作也叫做交换。

应用程序并不知道这个内存是物理内存还是虚拟内存，两种内存性能差异巨大，特别是使用了HDD的服务器。尽可能的关闭这个功能。我们希望内存不足的时候有个明显的保存，而不是使用了虚拟内存，整个系统都卡住而无法排查原因。

如果不关闭的话，可以使用vmstat来监控交换。需要查看si和so列中报告的交换I/O活动。而不是swpd中的交换使用情况。swpd可以显示已加载但未使用的进程，这不是真正的问题。我们希望si和so列的值为0，他们肯定要小于每秒10个快。

在极端的情况下，过多的内存分配会导致操作系统耗尽交换空间。如果发生这种情况，虚拟内存不足会导致MySQL崩溃。几时没有耗尽交换空间，频繁的交换活动也会导致系统无响应，甚至无法登录并终止MySQL的进程。

极端的情况下，有可能会发生Linux整个内核都被挂起，也有可能OOM Killer进程将启动并终止一些进程，一般是MySQL，也有可能是其他程序，例如SSH。如果SSH都被终止了，运维就没办法通过网络去访问系统了。很麻烦的。

所以呢，可以设置SSH进程的oom_adj或者oom_score_adj值来防止被kill。MySQL也是。

最好还是禁止内存交换。

echo 0 > /proc/sys/vm/swappiness

另外一种方式则是更改存储引擎读写数据的方式。例如设置innodb_flush_method=0_DIRECT可以减轻IO压力，这参数进队InnoDB有效。

也可以使用MySQL的memlock配置项，是的MySQL锁定在内存中，避免交换。但是有坑呢会没有足够多的内存锁定而崩溃，亦或者是占用太多，操作系统崩溃。

操作系统状态

主要教你两个工具，vmstat，iostat。除此之外，还可以使用mpstat、sar。网络部分可以使用ifconfig和netstat等工具。

vmstat

vmstat -SM 5

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0   1065     76   1638    0    0     2    40    2    6  2  1 97  0  0
 0  0      0   1080     76   1639    0    0     0   113 2855 4828  2  1 96  0  0

第一行是开机到现在的平均值，第二行是当前值，第三行开始则是每隔5秒统计一次的数据。接下来我们说明一下各个列是做什么的

procs 程序

r表示有多少进程在等待CPU；b显示有多少处于不可中断的休眠状态的进程数，这意味着他们正在等待I/O。

memory 内存

swpd列表示有多少块被交换到了磁盘，生下三列 分别显示多少个块是空闲的，有多少个快用于缓冲区，还有多少个用于操作系统的缓存。

swap 交换

显示交换活动，si表示每秒的换入（从磁盘）so显示换出，大多数时候希望si和so不超过10，最好为0;

io

bi和bo分别显示从块设备读入和写入块设备的块数，通常表示磁盘的io。

system

in表示每秒的终端数

cs表示每秒上下文切换数

CPU

分别显示运行用户代码、运行系统代码、空闲和等待I/O锁花费的时间百分比。如果是虚拟化，可能会有第5列（st）,显示从虚拟机“窃取”的百分比。

iostat

iostat -dxk 5

rrqm/s 和wrqm/s 每秒进入队列的合并读写请求数

r/s和w/s 每秒发送到设备的读写请求数

rKB/s和wKB/s 每秒读写吞吐量

await 在磁盘队列中花费的毫秒数

其他工具

dstat和collectl

mpstat

blktrace

pk-diskstats

pref

小结

硬件性能和成本要达到平衡，同时配置一些简单的参数使得MySQL更加高效运行。

优化服务器设置

本章将解释如何为MySQL服务器创建合适的配置文件。但是你必须要相信，官方的默认配置是有它的道理的。默认通常就是最好的，不用相信CSDN上鬼扯的那些最佳MySQL配置。

花费大量时间在调整参数配置，收益是很小的，与其这样，不如多去设计一下数据库结构。

MySQL配置是如何工作的

介绍MySQL中应该配置什么之前，我们首先解释MySQL的配置机制。

在Linux系统上，配置文件通常位于/etc/my.cnf或者/etc/mysql/my.cnf，如果使用操作系统的启动脚本，这通常是唯一的位置。

Debian的配置文件不在/etc/my.cnf，而是在/etc/mysql/my.cnf。有的时候好几个地方都有配置文件的时候，可以通过如下命令查询

which mysqld

/user/sbin/mysqld --verbose --help | grep -A 1 'Default options'

标准的配置文件采用INI格式，被分为多个部分，每个部分都以一行包含在方括号中的该部分名称开头。

语法、作用域和动态性

**配置文件全部都是小写，使用下划线或者短横线分隔。**命令行和配置文件，以下两种写法是等价的。

/usr/sbin/mysqld --auto-increment-offset=5
/usr/sbin/mysqld --auto_increment_offset=5

配置设置是有作用域的，有全局的，有每个会话自己的配置，有些则是基于对象。

除此之外，还有些可以在服务器运行时进行更改，MySQL称之为动态配置变量。

此外，还是使用SET命令，设置一个参数为DEFAULT，重置为默认。

持久化系统变量

利用了SET GLOBAL设置变量，重启系统还是会恢复为配置文件的配置。所以，SET GLOBAL要和配置文件同步。

设置变量的副作用

动态设置变量可能会产生一些副作用的。例如，引起缓冲区刷新脏块，导致MySQL变卡顿。

下面介绍一些常见的变量

table_open_cache

设置这个变量不会立即生效，而是下一次线程打开表的时候，如果此值大雨缓存中的数目，线程可以刷新缓存。

thread_cache_size

也不会立即生效，下次关闭连接时，MySQL会检查缓存中是否还有空间存储线程，如果有的话，就把这个线程保存起来，等待重用。如果没有，则立即终止这个线程。

也就是关闭时缓存，创建时删除缓存。

read_buffer_size

只有当查询需要时，MySQL才会为该缓冲区分配内存。

read_rnd_buffer_size

查询需要之前，不会分配任何内存，即使查询也只会需要分配所诉要的内存。所以这个应该可以叫做max_read_rnd_buffer_size

规划变量的更改

设置变量要小心，最好启用版本控制，并且，优先优化查询和schema！！！

什么不该做

不要尝试去建立一套测试套件，试图为一个机器寻找出最佳配置。不要，很浪费时间，收益太小了。并且，不要觉得调到缓存命中率很高就会认为是最佳配置，很多牛人都测试过，缓存命中是没有意义的。也不要使用网上的调优脚本。

创建MySQL配置文件

下面是一个最小的实例配置文件。

[mysqld]
# GENERAL
datadir =/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid_file=/var/lib/mysql/mysql.pid
user=mysql
port=3306
#INNODB
innodb_buffer_pool_size=<value>
innodb_log_file_size=<value>
innodb file_per_table=1
innodb_flush_method=O_DIRECT
# LOGGING
log_error=/var/lib/mysql/mysql-error.log
log_slow_queries= /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size=32M
max_heap_table_size=32M
max_connections=<value>
thread_cache_size=<value>
table_open_cache=<value>
open_files_limit=65535
[client]
socket=/var/lib/mysql/mysql.sock
port=3306

这与网上经常看到的配置文件相比，这可能看起来很短了。但是实际上，已经超过了很多人的需要了。你可能还需要使用一些其他的配置选项，例如binlog

我们配置的第一个变量是数据存放的位置。我们将其设置为/var/lib/mysql，因为它在许多UNIX变体中都很流行。选择另外的位置也没有问题，这由你决定。我们把.pid文件也放置在相同的位置，但很多操作系统默认将其放在/var/run，这也没有问题。我们只需要为这些设置配置一些东西。顺便说一句，不要让socket和.pid文件使用服务器编译的默认值: 在不同的MySQL版本中有一些bug，使用默认值可能会导致问题，所以最好显式设置。 (我们不是建议选择不同的地点，只是建议确保my.cnf文件明确地设置了这些位置，这样当升级服务器时它们就不会改变或引起问题。)我们还指定了在操作系统上以mysgI账号运行mysgld。你需要确保这个账号存在，并且确保数据目录和其中的所有文件的属主是这个账号。

配置内存使用

使用innodb_dedicated_server通常会占用50%~75%，这样至少有25%内存可用于每个连接的内存分配、操作系统开销。

每个连接的内存需求

MySQL只需要少量的内存就能保持一个连接。了解峰值使用期间将消耗多少内存是很有用的，但是一些使用模式可能会以外的消耗大量内存。例如预处理语句就是一个例子，因为可以同时打开多个预处理语句。另外一个例子是InnoDB数据字典。

为操作系统保留内存

操作系统有足够内存的最佳判断依据是，他没有交换内存。

InnoDB缓冲池

这个是使用内存最多的组件，就性能而言，InnoDB缓冲池大小通常是最重要的变量，不仅缓存着索引，还缓存着行数据、自适应哈希索引、更改缓冲区、锁和其他内部结构等。

InnoDB还是用它来实现延迟写操作，从而实现将多个写操作合并在一起并按顺序执行。你可以使用SHOW命令或者innotop等工具的变量监控它的内存使用情况。

如果数据不多，也不会快速增长，也不需要过渡分配内存。因为大型缓冲池也会带来一些挑战，例如更长的预热时间。

InnoDB在关闭的时候会进行脏页写到数据文件中，也可以强制关闭，就是当启动的时候，恢复时间会慢一些。可以配置innodb_max_dirty_pages_pct变量为比较低的值，这样等待刷新线程清理缓冲池，然后再脏页数量变少的时候关闭。可以通过innodb_buffer_pool_pages_dirty服务状态遍历或使用innotop监控SHOW INNODB STATUS来查看脏页。

线程缓存

thread_cache_size配置线程池大小。默认值为-1或者auto-sized

观察thread_created状态变量。如果每秒创建的线程小于10个，就是好的。

观察Threads_connected变量并调整thread_cache_size

每个休眠的线程 大概使用256KB内存。

配置MySQL的I/O行为

如果持久化这个操作非常及时，非常快，那么会影响性能的。反之，如果写操作没有及时被真正的写入磁盘，那么确实是可以提高并发性和减少IO等待的。只不过这个奔溃而导致的数据丢失的风险，也是要承担的。

InnoDB事务日志

InnoDB使用日志来降低提交事务的成本。他不会将在每个事务提交的时将缓冲池刷新到磁盘，而是记录到日志当中。事务对数据和所有所做的更改通常映射到表空间中的随机位置，因此需要做随机I/O（机械硬盘状态下，顺序I/O比随机I/O性能好太多了）。

使用日志，可以将随机I/O变成顺序I/O。并且一旦写入磁盘之后，这个事务就是安全的了。后续InnoDB会将事务在恢复在数据文件中。

日志的大小是固定的，采用的是循环写入。

日志文件的总大小由innodb_log_file_size和innodb_log_files_in_group控制

如果使用innodb_dedicated_server的话，日志文件的大小将根据系统的内存量来自动管理

日志缓冲区

InnoDB修改数据的时候，会将修改写入位于内存中的日志缓冲区

当缓冲区满了、事务提交时、或者每秒一次（这三个条件先满足者为准），InnoDB就会将缓冲区刷新到磁盘上的日志文件。如果有大型事务，增加缓冲区大小，有助于减少I/O。

通常将缓冲区设置为1~8M就可以了，通过innodb_log_buffer_size进行调整

刷新缓冲区的机制

通过设置innodb_flush_log_at_trx_commit来控制

0：每秒都将日志缓冲区写入日志文件，并且刷新日志文件。但是在日志提交的时候不操作。

1：每次事务提交的时候都将日志缓冲区写入日志文件，并且刷新日志文件

2：每次事务提交的时候都将日志缓冲区写入日志文件，但是不刷新，innodb会每秒刷新一次

写入和刷新什么区别

在大多数操作系统中，将日志缓冲区写入日志文件只是将InnoDB的内存缓冲区移动到操作系统的缓存中。实际上依然是在内存。

所以断电还是有危险，设置为0和2，最多会丢失一秒的事务。

有的时候，有些磁盘坑爹，操作系统将内存的数据写入，磁盘就会把它存进它自己的缓存里面，断电的话，丢失的事务更多，数据也可以丢失。

可以配置如何打开日志文件和数据文件的方式

很少配置吧，innodb_dedicated_server会自动设置，如果RAID有备用电源，就可以自己设置一下innodb_flush_method

InnoDB表空间

InnoDB将数据保存在表空间，表空间本质上是一个虚拟文件系统，由磁盘上的一个或者多个文件组成。InnoDB将表空间用于多种用途，不单只存储表和所有，还包含了Undo日志（重新创建旧行版本所需要的信息）、修改缓冲区、双鞋缓冲区和其他的内部结构。

配置表空间

innodb_data_home_dir 用于设置目录

innodb_data_file_path 用于创建文件 例如innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G创建了三个1G的表空间

ibdata3:1G:autoextend:max:2G 这个表示，初始化1G，自动扩展，最大两G

表空间可以分到不同磁盘里面，但是并没有什么卵用，因为MySQL会一个一个填满。想要利用多磁盘的性能的话，还是老老实实配置RAID吧。

**表空间一旦自动扩展了，就无法回收空间。**想要回收，唯一的办法就是导出数据，然后重新装一个MySQL，再把数据导进去。

innodb_file_per_table的作用是，为每个表单独配置一个表空间，使得表空间回收比较方便，但是呢，也有问题，会浪费多一些额外存储，也会使得drop table出现卡顿。

但是这些问题在8.0.23已经解决了。

最后的建议是什么呢？使用最新版，开启innodb_file_per_table。

行的旧版和表空间

表空间在写操作频繁的环境可能会变得非常大。这是因为如果事务长时间打开，MVVC会一直记录Undo日志等，会一直很大。这个时候要限制MySQL的速度，让它别这么快，等一等清理线程。

设置innodb_max_purge_lag

配置MySQL的并发

高并发的时候，可能会遇到瓶颈

不建议使用5.7以下的版本，性能太差了。升级MySQL是最好的选择

如果遇到并发瓶颈，分片数据是最好的选择。如果分片不可以，那么需要限制并发性，最基本的方法是使用innodb_thread_concurrency变量限制内核中同时可以有多少线程。0代表没有限制。建议将值设置为CPU的内核数量。

如果线程数超过内核数。新的线程将无法进入CPU内核，这个时候线程先休眠innodb_thread_sleep_delay的微妙数，再进行尝试进入内核。如果还是不能进入，则吧线程交给操作系统调度。

一旦线程进入了内核，则有一定数量的门票，使得线程可以免费的返回内核，无法其他并发性检查。这限制了它能在下次等待队列之前可以做多少工作。使用innodb_concurrency_tickets调整，但是很少需要调整。

安全设置

max_connect_errors 默认 100 ，可以设置的大一点，这个是当网络出现问题等原因导致连接无法在短时间成功，则客户端可能会被阻止连接，并且在刷新主机缓存之前都无法再次连接

max_connections 默认151 很小，可以调大点

skip_name_resolve 此设置禁用另外一个与网络有段的陷阱，那就是DNS解析。有些网络比较复杂得到区域，解析DNS需要很久，这个时候配置个host吧，救你狗命。

sysdate_is_now 设置sysdate()函数的结果是现在时间

read_only和super_read_only 可以使得副本MySQL唯一的修改来源是主节点的复制。

高级InnoDB设置

innodb_io_capacity 这个默认设置很糟糕，可以调大一点，因为这个以前是硬编码的，他默认磁盘每秒只能执行一百次I/O。如果是PICE 3.0或者4.0，可能需要设置很高。网上说这个SSD可以调到8000

schema设计与管理

选择优化的数据类型

有几个简单的原则

• 更小的数据类型更好（一般来说是这样的）
• 简单的更好
• 尽量避免存储NULL

下面是具体的

TIMESAMP和DATETIME都是存储时间，精确到时分秒，但是呢TIMESAMP只使用了DATETIME的一半的存储空间，并且还会根据时区变化。

*本章节只讨论基本的数据类型，因为MySQL为了兼容性，弄了很多别名，例如INTEGER到INT

整数类型

有TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，使用8、16、24、32、64位存储。

实数类型

DECIMAL可以存储小数，适合财务用，精度比DOUBLE和FLOAT都要高

字符串类型

VARCHAR 可变长，性能略差，碎片比较多，适合长度不一致的场景

CHAR 固定长度，性能好，碎片少，适合固定长度的场景，例如MD5密码，但是有个迷惑的性质，会去掉空格

BINART和VARBINARY的性质对比来说就跟上面两个差不多，但是他们由于对比的时候是二进制，速度比较好。

BLOB和TEXT类型

BLOB和TEXT属于不同数据类型家族

BLOB是二进制，没有字符集和排序规则

TEXT是字符，有字符集和排序规则

他们的排序和其他的不太一样，MySQL只会排max_sort_length字节而不是整个字符串，MySQL做不到。

枚举类型

枚举类型实际存储的是整数

枚举与枚举关联比较快，并且枚举节省空间。

枚举与VARCHAR关联比较慢

时间格式

TIMESMAP虽然节省空间，拥有时区，但是只能显示到2038年。我个人不建议再用了

用DATETIME吧，或者转为BININT

JSON数据

新支持的，但是性能没有很好，只是比较灵活。建议还是转为表结构。

选择标识符

也就是选择哪一列作为主键，如果选用了一个列当主键，则请所有的表都用同一个类型当主键，以免隐式转换带来的性能影响。

整数是最好的选择，建议使用BIGINT，配合雪花主键。字符串类型会很慢，如果使用了随机的字符串ID，性能会更加慢，例如MD5,UUID等。

如果非要使用UUID，应该删除破折号，或者更好的做法是，UNHEX()函数将UUID转为十六字节的数字，并存储到BINARY(16)，检索的时候就用HEX()函数。

特殊的数据类型

IPV4地址很多人拿VARCHAR(15)来存储，但是实际上他本来就是32位的无符号整数，不是字符串。MySQL提供了INET_ATON()和INET_NTOA函数相互转化。

MySQL schema设计中的陷阱

太多的列

缓冲区是以行来缓冲的，如果一行太多，对MySQL来说也有性能负担，即便你只是用了几列。

太多的关联

MySQL最多支持61个表连接，别搞了，多表连接真的搞死人，几个表就够了。冗余一点吧

枚举值太多

一个枚举几百个值，有没有想过，可以用多一张表呢？

null不是虚拟值

避免使用null比较好，例如说业务系统需要表示这个值是个空值，可以用一些特殊字符来代替，例如0和空字符串

但是也不要走极端，null处理起来比较明显。

还有一个细节，MySQL会对null值进行索引，但其实Oracle不会。

schema管理

当运行了几十个数据库实例，要对一个表结构进行修改的话，就很痛苦了。

**未来，将schema管理与持续集成整合。**也就是修改schema之后，生产环境的数据库也会一同更改。

**schema修改的源代码控制。**我们写代码都有版本控制，那么数据库修改有没有呢？有的，付费方案Flyway和Liquibase，开源方案，Skeema。

Skeema的实现方式，应给是导出create table导出来，进行版本控制。然后与其他数据库导出的进行对比，从而生成alter语句。

那么如果更改呢？

原生的DDL语句 原生的缺点就是，有一些操作，例如主键更改，或者是更改字符集等，这些修改都不是就地变更的（INPLACE alter）。作者强烈建议大家看文档，熟悉哪些更改是就地（INPLACE）或者是即时（INSTANT）的

使用外部工具

• pt-online-schema-change Skeema使用的就是这个工具，但是也是有局限性的，无法并发更改，外键约束会有影响，触发器容易报错（因为他就是用触发器来实现修改，如果你触发器本来就有一个，而他又创建了一个重名的触发器，那么就会报错，而且触发器性能也很差。）
• gh-ost 是github数据工程团队做的，它不影响服务，也不使用触发器，但是不支持外键约束。如果没有外键，那么go-ost是更好的选择。

创建高性能的索引

索引非常重要，最优的索引，可能比好的索引快好几个量级。

索引好比是书的目录，想要找到某个主题的内容，我们先回去看目录，找到这部分的页面，然后再通过页码去看这部分的书。

索引包含的多列的顺序也很重要，因为mysql纸能有效的使用索引的最左前缀列。

索引的类型

B-tree索引

InnoDB使用的是B+tree，即每个叶子节点都包含这下一个叶子节点的指针。每一个叶子页到根的距离是相同的。

B-tree是按照数据大小顺序存储的，很适合按照范围查询，例如找出以I到K开头的姓名。

自适应哈希索引

InnoDB有个特性，就是如果发现某些索引值被非常频繁访问，他会在原有的B-tree索引之上，再创建一个哈希索引。这个过程是自动化的，只能开或者关。

可以使用B-tree索引查询的查询类型

文中给出了一个例子，有一个索引，包含三个列，它的顺序是 姓、名、生日

那么以下查询可以使用索引。

全值匹配 查询一个姓甚名谁、生日是多少的人

匹配最左前缀 查一个姓什么的人，也可以差一个姓什么，叫什么名的人。

匹配列前缀 如果姓存储着lin，那么我可以查询li开头的人，也是使用了索引的。

匹配范围值 查找姓 在 lin和 liang之间的人，这个也同样只使用了第一列

精准匹配一列而范围匹配另外一列 例如匹配 姓lin，但是名字在 shuai和 zheng之间的人，这里用了最左的两列。

只访问索引的查询 只访问索引，而无需访问数据行，这种操作我们叫做覆盖索引优化。

下面是一些B-tree的限制

• 必须按照从左到右的顺序进行查询
• 如果查询中有某列的范围查询，则其右边所有的列都无法使用索引优化
• 不能跳过索引中的列

全文索引

这个是为了查找关键词，而不是提高查询速度。类似于搜索引擎的能力

使用索引的优点

• 减少服务器需要扫描的数据量
• 帮助服务器避免排序和创建临时表
• 将随机I/O转为顺序I/O

高性能的索引策略

索引不是随便乱建的，他有一些方式，可以快速、准确的创建高效索引。

前缀索引和索引的选择性

用于索引的列，他们区分度越大越好。

如果有一个字段保存员工编号（10001,10002,10003....），这样密度太大，不利于服务器的并行。

这个字段最好是前几位数字就有所不同，这样区别度就很大，使用 LEFT(CODE,3)这种方式筛选出来的数据就适中。所以还是强烈建议使用雪花主键，它有序，但是不连续，并且全局唯一。

如果是创建前缀索引，我们就尝试着截取前面几个字符，进行分组，看看截取哪个字符的时候，获取的分组数量最为最多。前缀选择性有0.31就已经是可用了。

前缀索引更小，但是无法用来排序，也无法利用前缀索引覆盖扫描

多列索引

常见的错误就是，为每一个列单独创建索引，或者是以错误的顺序创建多列索引。

MySQL优化器，在面对AND和OR的时候，会使用 索引合并的策略，但是依然是不如多列索引的，会耗费更多的CPU和内存资源。

更加重要的是，优化器不会把这些操作计算到查询成本中，使得有的时候，还不如不优化

所以当出现 where (a = "test" or b="test") and (c = "test" or D="test")这种情况，不妨使用union

选择合适的索引列顺序

有个法则：选择性最高的列放在索引的最前面。

当不需要排序和分组的时候，这个法则就是很好用的，但是也有一些特殊情况，那就是如果表几乎都是一样的时候，也就是查询符合的条件太高了，那这个时候索引已经没什么用了。

聚簇索引

它并不是一个单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构保存了B-tree索引和数据行。

MySQL不允许选择用来聚簇，而是默认选择主键索引来当聚簇索引，如果没有主键，那么会增加一个隐式的主键。

聚簇索引的优点

• 可以将关联的数据保存在一起，这样就可以读取较少的数据页就读取到
• 数据访问更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引的缺点

• 如果数据都在内存，聚簇索引就没什么优势了
• 插入速度严重依赖插入顺序。如果不是按照主键顺序加载数据，那么最好在加载完成后最好使用OPTIMIZE TABLE重新组织表
• 更新聚簇索引列的代价很高，会强制InnoDB将每个被更新的行移动到新的位置
• 插入新行，或者更新了主键，有可能导致页分裂，这样会占用更多的磁盘空间
• 可能会导致全表扫描变慢，尤其是行系数，或者有页分裂的情况出现
• 二级索引可能比想象中的大
• 二级索引访问需要两次索引查找

最后一点，他是这样的，先从二级索引找到主键，然后用主键去聚簇索引找数据。

InnoDB的数据分布

我们创建一个表

CREATE TABLE layout_test(
 col1 int not null,
  col2 int not null,
  PRIMARY KEY(col1),
  KEY(col2)
);

插入1~10000行

这就是聚簇索引得到存储方式，而它的二级索引存储方式如下

二级索引存储的并不是数据行的物理地址，而是**主键！**这样的好处是，为了减少行移动或者页分裂的时候，二级索引的维护工作。

在InnoDB表中安主键顺序插入行

UUID作为主键真的很糟糕，这使得聚簇索引的插入变得完全随机。书中进行了测试。

覆盖索引

大家会根据where来创建适合的索引。但是如果要查询的字段也在索引当中呢？这样数据库就不用再查一次数据行了，数据直接从索引拿就可以了。

这时候，我们就称之为覆盖索引，即一个索引包含了所有需要查询的值。

覆盖索引YYDS！他有几个好处：

• 极大提高查询性能，减少数据访问量。对缓存型应用，IO密集型的查询有优化。
• 对IO密集型的范围查询来说，因为索引是按照顺序存放数据的，所以范围查询的IO次数少的多。可以通过OPTIMIZE命令是的索引完全实现顺序排列
• 由于InnoDB的聚簇索引的特点，如果二级索引实现了覆盖索引，那么他就不用二次查询主键索引了。

使用索引扫描来做排序

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才可以使用索引来对结果做排序。

如果查询是使用了多表，则ORDER BY子句的所有字段都要在第一个表。

并且需要满足最左前缀的要求

有一个特殊情况，索引有三列，a,b,c，where 子句里面，设置了 a等于一个常量，然后order by b,c；这样也能利用索引排序。

冗余和重复索引

重复索引，即索引的顺序、字段完全一致，这样没有必要

冗余索引 加入有一个索引(A, B)，那么索引(A)就是冗余的，因为根据最左前缀规则，索引(A, B)可以当做索引(A)使用

但是索引(B, A)就不是冗余索引了。

索引(A)扩建为索引(A， ID)也是冗余的，因为索引(A)本来就包含了ID

如果一个索引包含很多了，但是查询用了只有最左列，发现很慢，这个时候就要考虑添加冗余索引了。

管理冗余索引和重复索引，就是把它删掉，可以使用一些工具来实现。

未使用的索引

如果有个索引一直没用，就删掉，因为会影响插入、更新、删除的性能

维护索引

修复表： REPAIR TABLE修复损坏的表

**更新索引的统计信息：**优化器在选择索引的时候，就是根据统计信息来选择的。例如会查询多少行之类的。但是如果这个信息不准确的话，就会出问题

可以使用 SHOW UNDEX FROM table_name来看索引的技术。运行ANALYZE TABLE重新分析

查询性能优化

操！

附件

oracle的索引种类