作为程序员的你，数据库作为一门必修课，而MySQL数据库毫无疑问已经是最常用的数据库了。系统的稳定、高效、高并发等指标，很大程度上取决于数据库性能是否够优，可见性能优化的重要性，这也就不难理解各位在任何一场面试中都会被问及到数据库调优相关的问题。



因此，这就是我为何考虑写本篇文章的主要原因，希望该文章（MySQL性能优化）能够给你带来收获，让你更系统、更全面的掌握MySQL性能优化的技能、技巧。如果觉得现在或者将来可能对你有用，不妨关注、收藏。



在MySQL性能优化之前，你有必要重新再认识下MySQL，便于逐步由浅入深的了解MySQL，以及更容易理解MySQL性能优化中涉及到的知识点和优化技巧。



本文将从以下几个方面开始MySQL性能优化的学习：



• MySQL的架构与核心问题

• 如何选择优化的数据类型

• 带你深入了解索引这些事

• 如何高效正确的使用索引

• 为什么你的SQL查询这么慢

• 常见优化SQL技巧

• 带你搞懂MySQL执行计划



一、MySQL的架构与核心问题



为了充分发挥MySQL的性能，并能够正确、高效的使用它，就必须理解MySQL的设计、存储引擎等问题，做到知己知彼，百战百胜。本节将从MySQL架构、一些核心问题（高并发、存储引擎等）两大方面展开讨论，这些内容将有助于你更清楚、更明白MySQL优化的原理。



1.1 MySQL逻辑架构



想深入探究MySQL之前，有必要了解一下MySQL的逻辑架构，逻辑架构图如下：

MySQL的逻辑架构中，分为三层，如上图红色虚线框的三部分。



最上层架构并不是MySQL所独有的，大多数基于客户端/服务器形态的系统或者服务，都有类似的架构，其中包含MySQL的连接处理、授权认证、安全控制等等。



第二层架构是MySQL中最为核心的部分，其中包括查询解析、分析、优化、缓存以及所有的内置函数(如：日期、时间、函数等)，所有跨存储引擎的功能都在这一层实现，例如：存储过程、触发器、视图等。



第三层架构是存储引擎。存储引擎负责MySQL中数据的存储和提取，类似与Linux系统下的各种文件系统一样，不同存储引擎都有各自的优势和劣势，不同场景可选择不同的引擎。不同存储引擎之间是不会相互通信的，只是简单地响应上层的请求。



1.2 核心问题



1.2.1 SQL执行流程



为了便于分析SQL执行的性能瓶颈，我们有必要认清楚一条SQL执行过程中，究竟经历了什么，都有谁和它亲密的接触过，下面我们就来看看SQL执行过程中经历了些什么吧。



（1）连接



1）客户端发起一次SQL请求，监听客户端的【连接管理模块】接收到请求。



2）将请求转发到【连接进/线程模块】。



3）调用【用户模块】来进行权限检查。



4）通过检查后，【连接进/线程模块】从【线程连接池】中取出空闲的被缓存的连接线程和客户端请求对接，如果失败，则创建一个新的连接请求。



（2）处理



1）先查询缓存，检查SQL语句是否完全匹配，接着再检查是否具有权限，都成功则直接取数据返回。（即：缓存命中）



2）上一步失败，则转交给【命令解析器】，经过词法分析、语法分析后生成解析树。



3）接下来是预处理阶段，处理解析器无法解决的语义，检查权限等，生成新的解析树。



4）再转交给对应的模块处理。



5）如果是SELECT查询，还会经由【查询优化器】做大量的优化，生成执行计划。



6）模块收到请求后，通过【访问控制模块】检查所连接的用户是否有访问目标表和目标字段的权限。



7）有则调用【表管理模块】，先是查看表cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件。



8）根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理。



9）上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中。



（3）结果



1）SQL请求完成后，将结果集返回给【连接进/线程模块】。



2）返回的也可以是相应的状态标识，如成功或失败等。



3）【连接进/线程模块】进行后续的清理工作，并继续等待请求或断开与客户端的连接。



1.2.2 如何控制高并发的读写？



无论何时，对于数据库而言，高并发的读写操作是很常见的，针对同一条记录在同一时刻进行修改、查询操作，都会产生并发控制的问题，处理不当将会出现大量的脏数据。那么，如何控制高并发的读写操作呢？



1.2.2.1 读写锁



在我们学习任何一门语言时，针对处理并发问题都会选择锁机制来解决并加以控制，这也是解决并发控制的经典方法，MySQL也不例外。在MySQL处理高并发的读或者写时，可以通过实现两种类型的锁来解决，这两种类型的锁通常被称为共享锁(Shared lock)和*排他锁(exclusive lock)*，其实就是大家叫的读锁(read lock)和*写锁(write lock)*。



读锁，是共享的，也就是说是互相不阻塞的。多个请求在同一时刻可以同时读取同一条记录，而互不干扰。



写锁，是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，避免在写的过程中进行读、再写的操作，这更是出于安全的考虑，只有这样才能确保数据的准确、干净。在数据库中，每时每刻都在发生锁定，当某次请求修改数据时，MySQL都会通过锁来防止其他请求读取同一数据。



1.2.2.2 锁策略



有了锁的机制，就能更好的控制高并发的读写操作，我们都知道锁也是有范围的，锁定对象范围的选择，更具有挑战性。尽量只锁定需要修改的部分数据，而不是所有数据，这也是选择锁定对象范围最想满足的。锁定范围越精确，锁定的数据量就越小，则系统的并发程度越高，加锁本身消耗的资源也就越小。



上述提到的无非就是设定锁的粒度，而MySQL则提供了多种选择，每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。下面将介绍两种最常用的锁策略。



• 表级锁(table lock)



表级锁是MySQL最基本的锁策略，并且是开销最小的策略，它会锁定整张表。一个请求在对表进行写操作(插入、修改、删除等)前，需要先获得写锁，此时会阻塞其他请求对该表的所有读写操作。只有没有写锁时，其他请求才能读取并获得读锁，<u>读锁之间是不相互阻塞的</u>。



尽管存储引擎可以管理自己的锁，而且MySQL本身还会使用各种有效的表级锁来实现不同的目的。例如，诸如ALTER TABLE之类的语句就使用了表级锁，而忽略存储引擎的锁机制。



开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。



表级锁，更适用于以查询为主，只有少量按索引条件更新数据的应用。



• 行级锁(row lock)



行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销)。



开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。



行级锁，更适合于有大量按索引发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。



1.2.3 MySQL存储引擎是怎样的？



在文件系统中，MySQL将每个数据库(即：schema)保存为数据目录data下的一个子目录。创建表时，MySQL会在数据库data目录下创建一个和表同名的.frm文件来保存表的定义。



不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。



可以使用show table status like '表名' \G命令来查看表的存储引擎以及表的其他相关信息，例如，查看mysql数据库中的user表：



mysql> use mysql;
No connection. Trying to reconnect...
Connection id:    20587
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show table status like 'user' \G;
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 125
    Data_length: 512
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 136
 Auto_increment: NULL
    Create_time: 2019-07-12 14:45:17
    Update_time: 2019-12-20 15:55:44
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

ERROR: 
No query specified



从查询结果的Engine字段可以表明，user表的存储引擎类型为MyISAM，其他字段在此就不一一说明，如想详细了解可查阅相关文档。



在了解MySQL存储引擎前，可以先看看你的MySQL数据库支持哪些存储引擎，可通过show engines命令查看。我使用的MySQL版本为5.7.25，查看结果如下图所示：

从上述结果可以看出，支持的存储引擎有： InnoDB、Mrg_Myisam、Memory、Blackhole、MyISAM、CSV、 Archive、Performance_Schema、Federated ，其中也做了简单的解释说明。



本文只针对InnoDB、MyISAM两种最常见的存储引擎进行着重说明，其它存储引擎只做简单说明，详细可查阅官方文档。



1.2.3.1 InnoDB存储引擎



InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎，并且有行级锁定和*外键约束*。



它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。



InnoDB的适用场景/特性，有以下几种：



• 经常更新的表，适合处理多重并发的更新请求。

• 支持事务。

• 可以从灾难中恢复（通过bin-log日志等）。

• 外键约束。只有他支持外键。

• 支持自动增加列属性auto_increment。



1.2.3.2 MyISAM存储引擎



MyISAM提供了大量的特性，包括全文检索、压缩等，但不支持事务和行级锁，支持表级锁。

对于只读的数据，或者表较小、可以忍受修复操作的场景，依然可以使用MyISAM。



MyISAM的适用场景/特性，有以下几种：



• 不支持事务的设计，但是并不代表着有事务操作的项目不能用MyISAM存储引擎，完全可以在程序层进行根据自己的业务需求进行相应的控制。

• 不支持外键的表设计。

• 查询速度很快，如果数据库insert和update的操作比较多的话比较适用。

• 整天 对表进行加锁的场景。

• MyISAM极度强调快速读取操作。

• MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。



1.2.3.3 MySQL内建的其他存储引擎



MySQL还有一些特殊用途的存储引擎，在一些特殊场景下用起来会很爽的。在MySQL新版本中，有些可能因为一些原因已经不再支持了，还有一些会继续支持，但是需要明确地启用后才能使用。



1.2.3.3.1 Archive存储引擎



Archive引擎**只支持insert和select操作**，并且在MySQL 5.1之前连索引都不支持。



Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM引擎的磁盘I/O更少。但是每次select查询都需要进行全表扫描，所以Archive更适合日志和数据采集类应用，况且这类应用在做数据分析时往往需要全表扫描。



Archive引擎支持行级锁和专用的缓冲区，所以可以实现高并发的插入。在一个查询开始直到返回表中存在的所有行之前，Archive引擎会阻止其他的select执行，以实现一致性读。另外，这也实现了批量插入在完成之前对读操作是不看见的。



1.2.3.3.2 Blackhole存储引擎



Blackhole引擎没有实现任何的存储机制，它会丢失所有插入的数据，不做任何保存。怪哉，岂不是一无用处？



但是服务器会记录Blackhole的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。



但这种存储引擎的存在，至今还是有些难以理解。



1.2.3.3.3 CSV存储引擎



CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但这种表不支持索引。



CSV引擎可以在数据库运行时拷入或者拷出文件，可以将Excel等电子表格软件中的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表中，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。



因此，CSV引擎可以作为一种数据交换的机制，是非常有用的。



1.2.3.3.4 Memory存储引擎



如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory引擎是非常有用的。Memory引擎至少比MyISAM引擎要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。Memory引擎的表结构在重启以后还会保留，但数据会丢失。



Memory引擎在很多场景下可以发挥很好的作用：



• 用于查找或者映射表，例如将邮箱和州名映射的表。

• 用于缓存周期性聚合数据的结果。

• 用于保存数据分析中产生的中间数据。



Memory引擎支持Hash索引，因此查找非常快。虽然Memory的速度非常快，但还是无法取代传统的基于磁盘的表。Memory引擎是表级锁，因此并发吸入的性能较低。



如果MySQL在执行查询的过程中，需要使用临时表来保存中间结果，内部使用的临时表就是Memory引擎。如果中间结果太大超出了Memory的限制，或者含有BLOB或TEXT字段，则临时表会转换成MyISAM的引擎。



看了上面的说明，大家就会经常混淆Memory和临时表了。临时表是指使用CREATE TEMPORARY TABLE语句创建的表，它可以使用任何存储引擎，因此和Memory不是一回事。临时表只在单个连接中可见，当连接断开时，临时表也将不复存在。



关于临时表和Memory引擎的那些事，可参考MySQL · 引擎特性 · 临时表那些事儿。



MySQL的存储引擎及第三方存储引擎，还有很多，在此就不一一介绍了，后续如有需要，再进一步来谈谈。



1.2.4 如何选择合适的存储引擎呢



这么多存储引擎，真是眼花缭乱，我们该如何选择呢？



大部分情况下，都会选择默认的存储引擎——InnoDB，并且这也是最正确的选择，所以Oracle在MySQL 5.5版本时终于将InnoDB作为默认的存储引擎了。



<u>对于如何选择合适的存储引擎，可以简单地归纳为一句话：”除非需要用到某些InnoDB不具备的特性，并且没有其他可以替代，否则都应该优先选择InnoDB引擎”。</u>



例如，如果要用到全文检索，建议优先考虑InnoDB加上Sphinx的组合，而不是使用支持全文检索的MyISAM。当然，如果不需要用到InnoDB的特性，同时其他引擎的特性能够更好地满足需求，就可以考虑一下其他存储引擎。



除非万不得已，建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。



如果需要使用不同的存储引擎，建议考虑从以下几个因素进行衡量考虑。



• 事务

• 备份

• 恢复

• 特有的特性



二、如何选择优化的数据类型



良好的设计是高性能的基石，应该根据系统的实际业务需求、使用场景进行设计、优化、再调整，在这其中往往需要权衡各种因素，例如，数据库表究竟如何划分、字段如何选择合适的数据类型等等问题。



MySQL支持的数据类型非常之多，对于选择恐惧症的小伙伴而言，苦不可言。大部分人在创建数据库表时，基本一股脑的使用INT、VARCHAR这两种类型最多，至于长度，则会选择足够大即可，避免日后不够用咋办。只顾当时一时爽，之后坑谁谁知道。



如果你是一个追求极致、高效的开发者，对于上面的情况肯定是不愿让其发生的。在众多的数据类型面前，如何选择正确的数据类型，对于高性能是至关重要的。本节将介绍如何选择优化的数据类型，来提高MySQL的性能，将会选取最为常用的类型进行说明，便于在实际开发中创建表、优化表字段类型时提供帮助。



2.1 选择原则



不管存储哪种类型的数据，下面几个简单的原则将有助于你做出更好的选择。



2.1.1 更小的通常更好



一般情况下，应该尽可能选择正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘空间、内存，并且处理时需要的CPU周期更少。



但是，在选择更小数据类型时，一定不要低估存储值的范围，因为后期修改数据类型及长度是一件非常痛苦、耗时的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。



2.1.2 简单就好



简单的数据类型操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校队规则(如：排序规则)使得字符比较比整型比较更复杂。



2.1.3 尽量避免用NULL



NULL是在常见不过的值了，通常都习惯对某些字段设置默认值为NULL，这其实是一种非常不好的习惯。如果查询中的字段值恰巧是设置的NULL值，对MySQl来说更难优化，因为可为NULL的字段使得索引、值比较都更复杂。



NULL值不能进行索引，影响索引的统计信息，影响优化器的判断。复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。



2.2 字符串类型



字符串类型是数据库中使用频率最高的数据类型，VARCHAR和CHAR是两种最主要的字符串类型，都可以用来存储字符串，但它们保存和检索的方式不同。VARCHAR属于可变长度的字符类型，而CHAR属于固定长度的字符类型。下面是关于这两种类型的说明、比较。



### 2.2.1 VARCHAR



VARCHAR类型用于存储可变长字符串，它比定长类型更节省空间，因为它仅使用必要的空间(例如，越短的字符串使用最少的空间)。



VARCHAR需要使用1或2个额外的字节来记录字符串的长度（如果字段的最大长度小于或等于255字节，则只使用1个字节表示长度，否则使用2个字节来表示长度）。例如，一个VARCHAR(10)的字段需要11个字节的存储空间，VARCHAR(1000)则需要1002个字节的存储空间，其中需要2个字节来存储长度。



2.2.2 CHAR



CHAR类型是定长的。当数据类型为CHAR时，MySQL会删除所有的末尾空格。



CHAR类型适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR类型非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR类型也比VARCHAR类型更好，因为定长的CHAR类型不容易产生碎片。对于存储非常短的列，CHAR类型比VARCHAR在存储空间上更有效率。例如，用CHAR(1)来存储只有Y和N的值，如果采用VARCHAR(1)却需要2个字节，因为还会有一个记录长度的额外字节。



通过下面具体例子来对CHAR进行说明，有助于更好的理解。这里创建一张只有一个CHAR(10)字段的表char_test，并往里面插入三个字符串xcbeyond，注意前后有空格的区别：



mysql> create table char_test(ch char(10));
Query OK, 0 rows affected
mysql> insert into char_test(ch) values('xcbeyond'),('  xcbeyond'),('xcbeyond  ');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0



奇怪的事情发生了，当我们查询时，会发现第三个字符串末尾的空格被自动截断了。为了更好的显示出是否有空格，对ch字段前后拼接'字符便于查看对比。



mysql> select concat("'",ch,"'") from char_test;
+--------------------+
| concat("'",ch,"'") |
+--------------------+
| 'xcbeyond'         |
| '  xcbeyond'       |
| 'xcbeyond'         |
+--------------------+
3 rows in set



如果用VARCHAR(10)字段存储相同的值，则字符串末尾的空格是不会被截断的。



2.3 日期类型



MySQL提供了两种相似的日期类型：DATETIME和TIMESTAMP,使用起来傻傻分不清，看完本节后不要再说不知道如何选择了。



对于应用程序而言，他们都能很好的表示日期，但是在某些场景下，各有不同。接下来让我们一起看看吧。



2.3.1 DATETIME



DATETIME类型能够保持很大范围的日期，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。



默认情况下，MySQL是以一种可排序、无歧义的格式显示DATETIME值，例如2020-03-05 22:38:40。



2.3.2 TIMESTAMP



TIMESTAMP，从它的名字不难看出，它和UNIX时间戳相同，保存了从1970年1月1日0时0分0秒以来的秒数。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多，只能表示从1970年到2038年。



TIMESTAMP显示的值依赖于时区，MySQL服务器、操作系统，以及客户端连接都有时区设置。因此，存储值为0的TIMESTAMP在美国东部时区显示为1969-12-31 19:00:00，与格林尼治时差5个小时。



（如果需要存储比秒更小粒度的日期和时间值该怎么办？MySQL目前没有提供合适的数据类型，但可以采用其他变通的方式，如可以使用自己的存储格式：可以使用BIGINT类型存储微妙级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。或者也可以使用MariaDB数据库替代MySQL。）



**通常应该尽量使用TIMESTAMP，因为它比DATETIME更节省存储空间，而且对于跨时区的业务，TIMESTAMP更为合适。**



2.4 TEXT和BLOB类型



一般在保存少了字符串的时候，我们会选择CHAR或VARCHAR类型，而在保存较大文本等数据时，通常会选择使用TEXT和BLOB。



TEXT和BLOB类型都是存储很大的数据而设计的字符串数据类型，分别采用字符串和二进制方式存储。例如，TEXT通常用来保存文章内容、日志等字符串内容，而BLOB通常用来保存图片、视频等二进制数据内容。有如下特点：



• TEXT类型有字符集和排序规则。



• BLOB类型存储的是二进制数据，没有排序规则或字符集。

• MySQL中不能将TEXT和BLOB类型的列进行索引，也不能使用这些索引消除排序。



与其他数据类型不同，MySQL把每个TEXT和BLOB类型的值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理，当它们的值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节来存储一个指针，然后在外部存储区域存储实际的值。



在面对TEXT、BLOB之间的选择时，应该根据实际情况选择能够满足需求的最小存储类型，接下来主要针对TEXT、BLOB类型存在的一些常见问题进行介绍。



**1.在执行了大量的删除操作时，TEXT和BLOB会引起一些性能问题**



删除操作会在数据库表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。



实战演示验证说明如下：



1）创建测试表text_test，字段id和context的类型分别为int(11)和text:



mysql> create table text_test(id int(11),context text);
Query OK, 0 rows affected



2）往表text_test中插入大量的数据，这里使用repeat函数插入字符串：



repeat函数用于字符串的复制



mysql> insert into text_test(id,context) values(1,repeat('xcbeyond',1000));
Query OK, 1 row affected
mysql> insert into text_test(id,context) values(2,repeat('xcbeyond',1000));
Query OK, 1 row affected
mysql> insert into text_test(id,context) values(3,repeat('xcbeyond',1000));
Query OK, 1 row affected
mysql> insert into text_test(id,context) values(4,repeat('xcbeyond',1000));
Query OK, 1 row affected
mysql> insert into text_test(id,context) values(5,repeat('xcbeyond',1000));
Query OK, 1 row affected
mysql> insert into text_test(id,context) values(6,repeat('xcbeyond',1000));
Query OK, 1 row affected
……



3）此时看看表text_test的物理文件大小：



2020/03/07 周六  15:58           540,672 text_test.ibd



这里显示数据文件大小为540Kb。



4）从表text_test中删除一大部分数据，这些数据占总数据量的2/3：



mysql> delete from text_test where id < 10;
Query OK, 9 rows affected



5）在此查看text_test的物理文件大小：



2020/03/07 周六  16:05           573,440 text_test.ibd



奇怪的是，数据文件大小并没有因为删除数据而减少，反而还增加了一点。



6）接下来对表text_test进行OPTIMIZE优化操作：



mysql> optimize table text_test;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| test.text_test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.text_test | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set



7）再次查看表text_test的物理文件大小：



2020/03/07 周六  16:08           458,752 text_test.ibd



可以发现，表的数据文件大小减少了，则说明“空洞”空间已经被回收了。



**2.使用合成索引来提高大文本字段(TEXT、BLOB类型)的查询性能**



合成索引，就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。



但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用MD5()函数生成散列值，也可以使用 SHA1()或 CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR 或 VARCHAR 列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些 BLOB 或 TEXT 数据列特别有用。用散列标识符值查找的速度比搜索BLOB 列本身的速度快很多。



实战演示验证说明如下：



1）创建测试表text_test2，字段id、context、hashValue字段类型分别为int(11)、text、varchar(40):



mysql> create table text_test2(id int(11),context text,hashValue varchar(40));
Query OK, 0 rows affected



2）往表text_test2中插入数据，其中hashValue用来存入context列内容的MD5值：



mysql> insert into text_test2 values(1,repeat('xcbeyond',10),md5(context));
Query OK, 1 row affected
mysql> insert into text_test2 values(2,repeat('xcbeyond',10),md5(context));
Query OK, 1 row affected
mysql> select * from text_test2;
+----+----------------------------------------------------------------------------------+----------------------------------+
| id | context                                                                          | hashValue                        |
+----+----------------------------------------------------------------------------------+----------------------------------+
|  1 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
|  2 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
+----+----------------------------------------------------------------------------------+----------------------------------+
2 rows in set



3）如果需要查询context列的值，则通过散列值hashValue来查询：



mysql> select * from text_test2 where hashValue = md5(repeat('xcbeyond',10));
+----+----------------------------------------------------------------------------------+----------------------------------+
| id | context                                                                          | hashValue                        |
+----+----------------------------------------------------------------------------------+----------------------------------+
|  1 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
|  2 | xcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyondxcbeyond | 537f6020f5b2b59456a61271a2b3f285 |
+----+----------------------------------------------------------------------------------+----------------------------------+
2 rows in set



上面的例子则是展示了合成索引的用法，由于这种技术只能用于精确匹配，在一定程度上减少 I/O，从而提高查询效率。



**3.在不必要的情况下避免检索TEXT、BLOB类型的值**



例如，SELECT * 查询就不是很好的操作，除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。这也是 BLOB 或 TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索 BLOB 或 TEXT 值。



4.把 BLOB 或 TEXT 列分离到单独的表中



在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT * 查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。



2.5 选择唯一标识符



唯一标识符，也就是我们常常所说的主键，用于充当表记录的唯一判断依据。唯一标识符，选择合适的数据类型是非常重要的。



通常唯一标识符更多的是用来与其它值或者其它表的值进行比较（如，关联查询中），标识列也可能在其它表中作为外键使用，所以为标识列选择数据类型时，应该选择根关联表中对应列一样的类型。



当选择唯一标识符的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较的，因为比较在SQL查询中使用最多，而且也是制约性能的最大因素。



一旦选定了一种类型，就一定要确保所有关联表中都使用相同的类型。因为类型直接往往都是需要精确匹配，混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误问题。



在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。



下面是一些小技巧：



1.整数类型



整数通常是标识列最好的选择，因为它很快，并且可以使用AUTO_INCREMENT。



2.字符串类型



如果可以避免，尽可能的避免使用字符串类型作为标识列的类型，因为它很消耗空间，并且通常比数字类型慢。尤其是在MyISAM存储引擎的表里使用字符串作为标识列时，要特别的小心，MyISAM默认对字符串使用压缩索引，这会导致查询慢很多。



对于完全“随机”的字符串也需多加注意，例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，会导致insert以及一些select操作变得很慢：



• 因为插入值会随机地写到索引的不同位置，所以使得insert语句更慢。这会导致页分裂、磁盘随机访问。

• select语句会变得更慢，是因为逻辑上不相邻的数据会分布在磁盘和内存的不同地方。

• 随机值会导致缓存对所有类型的查询语句效果很差，因为会使得缓存赖以工作的访问局部性原理失效。



2.6 总结



在实际开发中，有很多工具会自动生成建表脚本等等，自动生成前期给开发带来了很大的便利，但与此同时却导致严重的性能问题。有些工具生成的东西，在存储任何数据都会使用很大的VARCHAR类型，这往往是不正确的。如果是自动生成的，一定要反复检查确认是否合理。



例如，一些ORM框架(如，MyBatis、Hibernate)，会存储任意类型的数据列到任意类型的后端数据，这通常意味着没有设计使用更优的数据类型来存储，后期安全隐患很大，出现问题也很难排查。总之，一定要反复检查确认是否合理。这也是我个人不太喜欢用这类类似的工具，来生成代码的原因，检查真的很浪费我的时间。



在这里已经介绍了大部分常用的数据类型，各自都有哪些特点，哪些地方会严重影响性能等等。在选择数据类型时，把握好“选择原则”，你就成功了一半，其余细节在日常开发接触中慢慢琢磨、留意，选择类型时不要随意、盲目选择就好。



简单归纳如下：



• 对于字符串类型，最好的策略是只分配真正需要的空间。



• 日期类型，要根据实际需要选择能够满足应用的最小存储的日期类型。



• 对含有 TEXT 和BLOB字段的表，如果经常做删除和修改记录的操作要定时执行OPTIMIZE TABLE功能对表进行碎片整理。



三、带你深入了解索引这些事



索引，对于良好的数据库性能非常关键。只要提及到数据库性能优化，都会首先想到“索引”，看看表中是否添加索引。尤其是当表中的数据量越来越大时，索引对性能的影响尤为突出。在数据量较小且负载较低时，没有索引或者不恰当索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。



不过，索引却经常被忽略，有时候甚至被误解、误用，在实际使用中经常会遇到糟糕索引而导致的性能问题。本文就索引的概念、类型、优点等方面聊聊，一起深入理解索引的这点事，更有助于你清楚的理解索引，能够正确的使用它，便于利用它来进行数据库的优化。



3.1 什么是索引



索引(Index)，是帮助MySQL高效获取数据的数据结构，是存储引擎用于快速找到记录的一种数据结构。



要理解MySQL中索引是如何工作的，最简单的例子就是去看看一本书的目录“索引”部分。如果想在一本书中找到某个章节，一般我们会先看书的目录“索引”，就会立即找到对应的页码。



在MySQL中，存储引擎也是用类似的方法使用索引，首先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。



查询是数据库中最常用的操作，我们都希望查询的速度尽可能快，因此数据库系统的设计者会从查询算法角度去进行优化。最基本的查询算法当然就是顺序查找，但是这种算法的复杂度为O(n)，在数据量很大时就会显得非常糟糕。例如，在一张用户表t_user中有如下数据，想要查询到年龄为89岁的人，如果按照顺序查找，则得逐行扫描，可见查询效率有多低下(数量量越大，数据分布越不均匀，则花费的时间就更长)。



mysql> select * from t_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | xcbeyond |  22 |
|  2 | jack     |  34 |
|  3 | tom      |  77 |
|  4 | kitty    |   5 |
|  5 | make     |  91 |
|  6 | Mickey   |  23 |
|  7 | Andy     |  89 |
+----+----------+-----+
7 rows in set



好在，数据库系统的设计者早都意识到了这一点，参考了更优秀的查找算法，如二分查找、二叉树查找等等，但是分析之后发现，每种查找算法都只能应用于特定数据结构之上，如二分查找要求被查询的数据有序，而二叉树查找只能应用于二叉查找树。鉴于此，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，即：这就是数据库中的索引。



为了更好的理解索引，下图就以表t_user中的数据，展示了<u>一种可能的索引方式</u>。

左边是表中的数据，一共7条记录，为加快age列的查找，维护了一个右边所示的二叉查找树，每个节点包含索引键值及一个指向对应数据记录的指针，这样运用二叉查找就能很快的查找对应的数据了，时间复杂度为O(log2 N)。



然而，在实际数据库中，几乎没有使用这样的二叉查找树来实现（因为二叉查找树对数据是有要求的），但其原理和这类似。



3.2 索引操作



在正式介绍索引之前，先一起来看看MySQL是如何创建索引、重建索引、查询索引、删除索引等操作的，以备后续使用。(建议单独保存收藏)



3.2.1 创建索引



索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。



语法：



CREATE [UNIQUE/FULLTEXT] INDEX <索引名> ON <表名>(<列名>)



ALTER TABLE <表名> ADD INDEX|UNIQUE|PRIMARY KEY|FULLTEXT <索引名>(<列名>)



其中，创建索引时，可以指定索引类型：主键索引（PRIMARY KEY）、 唯一索引（UNIQUE）、 全文索引（FULLTEXT）、 普通索引（INDEX）。



例如：



1）以表index_test为例说明，先创建一个普通的表index_test：



(创建表时，也可以直接创建索引，此处为了说明索引的创建，则单独创建索引)



mysql> create table index_test(id int,ch varchar(32));
Query OK, 0 rows affected



2）为表index_test单独创建索引：



mysql> create index idx on index_test(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0



或者



mysql> alter table index_test add index idx(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0



3.2.2 重建索引



重建索引，在常规的数据库维护操作中经常使用。在数据库运行了较长时间后，索引都有损坏的可能，这时就需要重建。对数据重建索引可以起到提高检索效率。



重建索引，实质上的对表的修复。



例如:



mysql> repair table index_test quick;
+-----------------+--------+----------+---------------------------------------------------------+
| Table           | Op     | Msg_type | Msg_text                                                |
+-----------------+--------+----------+---------------------------------------------------------+
| test.index_test | repair | note     | The storage engine for the table doesn't support repair |
+-----------------+--------+----------+---------------------------------------------------------+
1 row in set



3.2.3 查询索引



有时，为了查看某张表是否有索引，索引情况如何，就需要通过命令show index from|in table_name来查看索引。



语法:



SHOW INDEX FROM|IN <表名>



例如：



mysql> show index from index_test;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test |          1 | idx      |            1 | id          | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set



细心的你，或许看到查询结果中的字段index_type的值BTREE，这也就是接下来会讲到的B Tree索引，从另一方面也可以知道InnoDB默认的索引类型为B Tree。



3.2.4 删除索引



删除索引可以使用DROP INDEX 或 ALTER TABLE语句来实现。



语法：



DROP INDEX <索引名> ON <表名>



ALTER TABLE <表名> DROP INDEX <索引名>



例如：



mysql> drop index idx on index_test;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0



或者



mysql> alter table index_test drop index idx;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0



3.3 索引类型



索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层实现的，所以，并没有统一的索引标准：不同存储引擎的索引的工作方式也是不一样的，也不是所有的存储引擎都支持所有类型的索引。



从存储结构上来划分：



• BTree索引（B-Tree或B+Tree索引）

• 哈希索引

• 全文索引（full-index）



从应用层次来分：



• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。

• 唯一索引：索引列的值必须唯一，但允许有空值。

• 复合索引：即一个索引包含多个列。



下面我们从索引的存储结构上，来看看MySQL支持的索引类型，底层是如何实现的，以及它们的优缺点。



MySQL默认存储引擎是Innodb，只显式支持B-Tree索引，对于频繁访问的表，Innodb会透明建立自适应哈希索引，即在B树索引基础上建立哈希索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。



3.3.1 B-Tree索引



当大家在谈论索引的时候，如果没有特别指明类型，多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据，可以让系统高效的找到数据所在的磁盘块。



B代表平衡(balance)，而不是二叉(binary)，因为B Tree是从最早的平衡二叉树演化而来的。



B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。



系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。



InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodbpagesize将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：



mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set



而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。



B-Tree定义数据记录为一个二元组[key、data]：



• key为记录的主键，即表中的主键值，用于记录唯一的数据行，key值是唯一且互不相同的。



• data为一行记录中除主键外的数据。



一棵m阶的B-Tree有如下特性：



• 每个节点最多有m个孩子。

• 除了根节点和叶子节点外，其它每个节点至少有ceil(m/2)个孩子。

• 若根节点不是叶子节点，则至少有2个孩子。

• 所有叶子节点都在同一层，且不包含其它关键字信息。

• 每个非终端节点包含n个关键字信息（p0,p1,...pn,k1,...kn）

• 关键字key的个数n满足：ceil(m/2)-1 <= n <= m-1

• ki(i=1,…n)为关键字，且关键字升序排序。

• pi(i=1,…n)为指向子节点的指针。p(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。



*注：ceil()为取整函数。*



B-Tree中的每个节点根据实际情况，可以包含大量的键值key、数据data、和指针p。如下图所示为一个3阶的B-Tree索引结构：

每个节点占用一个磁盘块空间，一个节点上有两个升序排序的关键字key和三个指向子节点的指针p，指针存储的是子节点所在磁盘块的地址。两个关键词key划分成为三个范围域对应的三个指针p，并指向的子节点的数据的范围域。以根节点为例，关键字为17和35，p1指针指向的子节点的数据范围为小于17，p2指针指向的子节点的数据范围为17~35，p3指针指向的子节点的数据范围为大于35。



模拟查找关键字为29数据行的过程：



1) 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】



2) 比较关键字29在区间（17,35），找到磁盘块1的指针p2。



3) 根据p2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】



4) 比较关键字29在区间（26,30），找到磁盘块3的指针p2。



5) 根据`p2'指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】



6) 在磁盘块8中的关键字列表中找到关键字29。



分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字key是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree(高度平衡的二叉树)缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。



3.3.2 B+Tree索引



B+Tree是在B-Tree基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。



从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。



B+Tree相对于B-Tree有几点不同：



• 非叶子节点只存储键值信息。



• 所有叶子节点之间都有一个链指针。



• 数据记录都存放在叶子节点中。



将上一小节中的B-Tree进行优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。



可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：



InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3 10^3 = 10亿 条记录。