MySQL 随笔

发表于 | 分类于 |

MySQL

MySQL 数据类型

数值类型

整数类型

这里的M表示指定显示宽度。一般与zerofill配合使用，意为当实际数值宽度小于M时，在数值前填充0。 当某列指定zerofill时，自动为该列添加UNSIGNED属性。

整数类型还有一个属性AUTO_INCREMENT，意为自动+1。该属性只用于整数类型，一个表最多有一个AUTO_INCREMENT列。定义时，应该同时定义为NOT NULL,PRIMARY KEY或UNIQUE。

类型 字节 范围
TINYINT(M) 1 -128127/ 0255
SAMLLINT(M) 2 -3276832767/ 065535
MEDIUMINT(M) 3 -83886088388607/ 01677215
INT(M)、INEGER(M) 4 -21474836482147483647/ 04294967295
BIGINT(M) 8 -92233720368547758089223372036854775807/ 018446744073709551615

浮点类型

M表示数值宽度，即整数位+小数位。 不指定M,D时，会按照实际精度来显示，实际精度取决于硬件和操作系统。 插入的数字超过指定精度，将被截断。

类型 字节 范围
FLOAT(M,D) 4 ±1.175494351E-38~±3.402823466E+38
DOUBLE(M,D) 8 ±2.2250738585072014E-308～±1.7976931348623157E+308

定点数类型

DECIMAL以字符串形式存放的，比浮点数更精确。 默认M为10，D为0。 插入的数字超过指定精度，在默认的SQLMode模式下被截断，并发出警告；在TRADITIONAL(传统模式)下将报错，导致数据无法插入。

类型 字节 范围
DEC(M,D)、DECIMAL(M,D) M+2 最大取值范围与DOUBLE相同

日期时间类型

日期类型字段，假如插入的数值超过其范围，将设为零值。

  • TIMESTAMP与时区有关。
  • TIMESTAMP类型默认值为CURRENT_TIMESTAMP，且只能设置一列。
类型 字节 范围 说明 零值
DATE 4 1000-01-01~9999-12-31 0000-00-00
DATETIME 8 1000-01-01 00:00:00~9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 19700101080001~2038－01-19 11:14:07 适用于经常插入或者更新时间为当前系统 0000000000000
TIME 3 -838L59:59~838:59:59 00:00:00
YEAR 1 1901~2155 0000

DATETIME与TIMESTAMP区别

DATETIME TIMESTAMP
范围大 范围小
不受时区影响 受时区影响
可设置为系统时间，由MySQL自动插入当前系统时间

插入的格式

  • 对于YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串，其中-和:可以换成任意标点符号。
  • 对于有间隔符的格式，其中的MM,DD,HH,MM,SS不需要指定两位数，1929-01-02 01:02:03等价于1929-1-2 1:2:3。
  • 对于没有间隔符的格式，例如YYYYMMDDHHMMSS会被解释为YYYY-MM-DD HH:MM:SS。
  • 对于没有间隔符的格式:
    • 假如数值是6位，会解释为YYMMDD
    • 8位：YYYYMMDD
    • 12位: YYMMDDHHMMSS
    • 14位: YYYYMMDDHHMMSS
  • now()和CURRENT_DATE的返回值适用于DATETIME,DATE,TIMESTAMP。

字符串类型

  • CHAR是以字符计算的，一个中文占一个字符。CHAR(2)表示两个字符。
  • BINARY是以字节计算的，一个中文占两个字节。BINARY(2)表示两个字节。
  • CHAR在插入时，会自动删除尾部空格。
  • BINARY在插入时，会自动填充0x00以达到指定字段定义长度。
  • TINYTEXT、SAMLLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，存储的是字符串，有排序规则和字符集。
  • TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB，存储的是二进制数据，没有排序规则和字符集。
  • 当BLOG、TEXT值太大时，InnoDB 会使用专门的外部存储区域来存储，此时值在行内需要 1~4 个字节存储一个指针。
  • BLOG存储的是二进制数据，没有排序或字符集。TEXT有字符集和排序规则。
  • TEXT、BLOG只针对每个列的最前max_sort_length字节做排序。

VARCHAR

  • 存储可变长字符串，但如果MySQL 使用ROW_FORMAT=FIXED 创建的话，每一行会使用定长存储。
  • 5.0 及其以后版本，MySQL 存储和检索时会保留末尾空格
  • InnoDB 可以把过长的 VARCHAR 存储为 BLOB
  • 适合最大长度比平均存储长度大很多的情形；列的更新较少；使用了 UTF-8 字符集

选择原则

  • 正确选择存储数据的最小数据类型
  • 简单数据类型，整型优于字符串
  • 尽量避免 NULL

字符串类型

类型 字节 范围
CHAR(M) M M为0～255
VARCHAR(M) M M为0～65535
TINYBLOB(M) M M为0～255
BLOB(M) M M为0～65535
MEDIUMBLOB(M) M M为0～167772150
LONGBLOB(M) M M为0～4294967295
TINYTEXT(M) M M为0～255
TEXT(M) M M为0～65535
MEDIUMTEXT(M) M M为0～167772150
LONGTEXT(M) M M为0～4294967295
VARBINARY(M) M M为0～M
BINARY(M) M M为0～M
ENUM 1~2 0~65535
SET 1~8 0~64

枚举类型

ENUM，1～255个成员需要1个字节存储，255～65535需要2个字节存储。 ENUM忽略大小写，会将所有数值转为大写。 ENUM插入指定范围外的值时，默认插入第一个值。 ENUM只允许存单个值。

可以使用使用枚举类型代替字符串类型。

MySQL 把枚举值保存为整数，查找的时候会转化字符串。在特定情况下，把CHAR/VARCHAR 列与枚举进行关联可能会比直接关联CHAR/VARCHAR更慢。如果不需要与CHAR/VARCHAR列进行关联，那么将列转为ENUM比较好。

位数据类型

BIT

BIT(M)，M表示有多少位二进制数。 该类型需要bin()和hex()函数进行读取。 select bin(id),hex(id) from t2;，取值范围1~64。

MyISAM 中会将所有的BIT 列合在一块存储，可以节省空间。InnoDB 和 Memory 中，每个BIT会单独使用一个足够存储的最小整数类型来存放。

在数字上下文场景中，位字符串会转化成数字进行处理。其他情况作为字符串处理。所以尽量避免使用BIT类型。

SET

SET 可以利用FIND_IN_SET、FIELD函数。SET可以包含0～64个成员。其中：

  • 1~8成员集合，占1个字符。
  • 9~16成员集合，占2个字符。
  • 17~24成员集合，占3个字符。
  • 25~32成员集合，占4个字符。
  • 33~64成员集合，占8个字符。

可以在整数类型上进行按位运算，来代替位数据类型。

特殊类型

对于 IP 地址，应当使用无符号整数类型存储，MySQL 提供了INET_ATON()、INET_NTOA进行转换。

MySQL 索引

InnoDB 引擎采用 B+Tree 来实现索引，B+Tree 适合全值匹配、匹配最左前缀、匹配列前缀、匹配范围、匹配某一列并范围匹配另一列（索引包含多列的话）、只访问索引的查询。

B+Tree 的限制：

  • 非最左列查找
  • 多列索引中，不能跳过索引中的列
  • 多列索引中，某一列使用范围查找，后续列不能使用索引优化

InnoDB 有个特殊功能『自适应哈希索引（adaptive hash index）』，当 InnoDB 注意到某些索引值使用的比较频繁时，它会在内存 中基于 B+Tree 索引之上再创建一个哈希索引。这是完全自动的内部行为，用户无法控制，但可以关闭该功能。

索引有三大优点：

  • 大大减少了扫描表的数据量
  • 帮助服务器避免排序和临时表
  • 将随机 I/O 变为顺序 I/O

高性能索引策略

  • 若列是表达式的一部分或者是函数的参数，则无法使用已有索引

  • 索引很长的字符列会让索引变得大且慢，可以使用伪哈希或者前缀索引。前缀索引无法使用ORDER BY、GROUP BY

  • 多列索引的列顺序如何选择？不考虑排序和分组时，将选择性最高的放在最前面，即重复率最少的列。

索引合并

MySQL 5.0 以后引入了索引合并的策略，一定程度上可以使用多个单列索引来定位指定的行。MySQL 使用多个单列索引进行扫描，并将结果进行合并，这种算法支持，OR、AND以及两者组合。

多索引同时进行扫描，往往使得优化器低估执行成本，往往不如直接走全表扫描。还是改为UNION的方式更好一些。

可以通过参数optimizer_switch来关闭索引合并功能，也可以使用IGNORE INDEX 让优化器忽略索引。

聚簇索引

如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键作为聚簇索引。MyISAM 的主键列和其他索引的叶子节点都是存储数据行的指针，所以需要独立的行存储。

聚簇索引的优点

  • 将相关数据保存到一起，减少磁盘I/O
  • 数据访问更快，从聚簇索引查找数据，比从普通索引查找更快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
  • 最大限度地提高 I/O 密集型应用的性能

聚簇索引的缺点

  • 如果数据都放到内存中，没什么优势
  • 插入速度严重依赖于插入顺序，如果不是按照主键顺序加载数据，最好使用OPTIMIZE TABLE 重新组织表
  • 更新聚簇索引列的代价高，需要移动更新的行
  • 基于聚簇索引的表插入或更新主键导致需要移动行时，可能面临页分裂，页分裂会导致占用更多的磁盘空间
  • 聚簇索引导致全表扫描变慢，尤其是行稀疏或者由于页分裂导致数据存储不连续
  • 非聚簇索引的叶子节点包含了聚簇索引的列，可能会导致数据量大
  • 非聚簇索引访问，需要两次索引查询

聚簇索引采用 UUID 的缺点

  • 写入目标页可能未在缓存中，需要从磁盘加载，导致大量的随机 I/O
  • 写入是乱序的话，需要频繁的做页分裂操作，页分裂会导致移动大量数据
  • 频繁的页分裂会导致页变得稀疏，导致数据会有碎片

覆盖索引

如果索引包含所有需要查询的字段的值，称之为覆盖索引。

  • 没有回表操作，减少了数据访问量，索引比数据更小，可以更容易放入内存中
  • 使得范围查询可以使用完全顺序的索引访问

索引排序

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且排序方向都一样时，MySQL 才能够使用索引来对结果排序。如果查询关联多张表，只有子句引用的字段全部为第一个表时，才能够使用索引做排序。

information_schema.index_statistics 表可以统计索引的使用频率。

索引案例

支持多种过滤条件

如果某一列在很多查询中都用到，例如 sex，可以在索引中加上该列。即时不需要通过性别过滤，也可以用sex in (0,1) 来绕过该限制。 但是这种做法不适合 IN 列表的值太多。

避免多个范围条件

对于范围条件，例如大于、小于、BETWEEN、IN。范围查询会使得 MySQL 无法使用范围列后面的其他索引列了。 可以将范围小的列转为『多个等值条件查询』。

如何避免偏移量过大的分页

1
2
3
SELECT * from ruigu_member_order INNER JOIN (
		SELECT id from ruigu_member_order limit 100000,10 
) as x USING (id)

维护索引和表

系统崩溃、硬件问题、MySQL 本身缺陷都有可能造成表损坏，尤其是 MyISAM 引擎。使用check table 可以找出大多数的表和索引的错误。然后使用repair table 来修复损坏的表。

更新索引统计信息

MySQL 的查询优化器通过两个 API 来了解存储引擎索引值的分布信息，以决定如何使用索引。

  • records_in_range()，传入两个范围值来获取在这个范围内有多少记录，对应 MyISAM 引擎其结果是精确值，对于 InnoDB 引擎其结果是估值。
  • info()，返回各种类型的数据，包括索引的基数。

MySQL 查询优化器使用的是基于成本的模型，而衡量成本的主要指标是查询需要扫描多少行。ANALYZE TABLE 可以重新生成统计信息。SHOW INDEX FROM TABLE 可以查看索引的基数。

InnoDB 引擎通过抽样方式来计算统计信息，首先随机读取少量的索引页面，然后以此为样本计算索引的统计信息，默认值是 8，可以通过innodb_stats_sample_pages设置样本页数量。

InnoDB在打开某些INFORMATION_SCHEMA表、使用SHOW TABLE、SHOW INDEX、某些 MySQL 客户端的补全功能，都会触发索引统计信息的更新，如果服务器上有大量数据的话，会造成严重的问题。可以关闭innodb_stats_on_metadata参数避免上述问题。

减少数据和索引的碎片

碎片化的索引会导致要进行磁盘随机访问，不仅索引存在碎片化，数据存储也存在碎片化，包括行碎片、行间碎片、剩余空间碎片。

可以使用OPTIMIZE TABLE、导出再重新导入的方式来重新整理数据，可以通过重建索引来消除索引的碎片化。对于不支持OPTIMIZE TABLE 操作的存储引擎，可以通过将表的存储引擎修改为当前因为即可。

MySQL 架构图

MySQL自带4个数据库，其各自功能如下：

  • information_schema，主要存储了系统中的一些数据库对象信息，比如用户表信息，列信息，权限信息，字符集信息，分区信息。
  • cluster，存储了系统的集群信息。
  • mysql，存储了系统的用户权限信息。
  • test，测试数据库，任何用户都可以使用。

MySQL 层次

第一层架构，连接处理、授权认证、安全等。
第二层架构，查询解析、分析、优化、缓存、内置函数处理、存储过程、触发器、视图等跨存储引擎的功能。
第三层架构，包含存储引擎，存储引擎负责MySQL中数据的存储和提取。存储引擎不会解析SQL，但是InnoDB例外，它会解析外键定义。

每个客户端连接都会在服务器进程拥有一个线程。

优化与执行

MySQL会解析查询，并创建内部数据结构，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。

并发控制

读锁（read lock）也叫共享锁（shared lock），互不阻塞。写锁（write lock）也叫排他锁（exclusive lock）。
一个写锁请求可能会插入到读锁队列的前面。

表锁是开销最小的锁策略。尽管存储引擎可以管理自己的锁，MySQL还是会用各种有效的表锁来实现不同目的，例如alter table 语句会使用表锁。 行级锁可以最大程度地支持并发处理，同时也带来了最大的锁开销，行级锁只在存储引擎层实现。MySQL服务器层没有实现。

事务

ACID

  • 原子性，一个事务里的所有操作要么全部成功，要么全部失败。
  • 一致性，数据库总是从一个一致性的状态转移到另外一个一致性的状态。
  • 隔离性，一个事务所做的修改再最终提交以前，对其他事务是不可见的。
  • 持久性，一旦事务提交，则其所做的修改会永久的保持到数据库中。

隔离级别

  • 读未提交，事务中未提交的修改，可被其他事务读取，造成脏读。
  • 提交读，大多数数据库的默认级别，一个事务提交前的修改对其他事务不可见。但是其他读未提交事物的修改可以读取到，也叫不可重复读。
  • 可重复读，该级别保证同一个事务多次读取的结果一致。 MySQL默认级别
  • 可串行化，强制事务串行执行，会在读取的每一行数据上都加共享锁。
隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ UNCOMMTTED Yes Yes Yes No
READ COMMITTED No Yes Yes No
REPEATABLE READ No No Yes No
SERIALIZABLE No No No Yes

死锁

多个事务试图以不同的顺序锁定资源时，就可能产生死锁，多个事务同时锁定一个资源时，也会产生死锁。InnoDB 处理死锁时将持有最少行级排他锁的事务进行回滚。

事务日志

事务日志可以提高事务的效率。使用事务日志，存储引擎在修改数据时，只需要修改内存里的数据，再将修改行为记录到硬盘中的事务日志中。在后台慢慢将修改数据刷回磁盘。 事务日志是追加方式。这种实现方式称为预写式日志（Write-Ahead Logging）。

MySQL 中的事务

MySQL 默认采用自动提交，即每条查询当做一个事务执行提交操作。可以通过修改AUTOCOMMIT 变量修改默认行为。

1
2
> SHOW VARIABLES LIKE 'AUTOCOMMIT';
> SET AUTOCOMMIT = 1;

有些命令在执行之前会强制执行COMMIT 提交当前活动事务，例如数据定义语言 DLL ，ALTER TABLE等。 还有LOCK TABLES 等其他语句也会导致同样的结果。

MySQL 可以通过SET SESSION TRANSACTION ISOLATION LEVEL 来设置隔离级别。

如果在事务中混合了事务表和非事务表，在正常情况下不会有影响，事务需要回滚时非事务表的变更无法撤销，也不会报错，至多发出警告。

隐式和显式锁定

InnoDB 采用两阶段锁定协议（two-phase locking protocol）。在事务执行过程中随时可以执行锁定，锁只有执行COMMIT、ROLLBACK 的时候才会释放，并且所有锁同一时刻被释放。这些是隐式锁定，InnoDB 根据隔离级别在需要的时候字段锁定。

InnoDB 支持特定语句进行显式锁定，这些不属于 SQL 规范。

1
2
SELECT ... LOCK IN SHARE MODE
SELECt ... FROM UPDATE

多版本并发控制（MVCC）

MVCC 是行级锁的变种，很多情况下避免了加锁操作，开销更低。

InnoDB 的 MVCC 是通过每行记录后面保存两个隐藏的列来实现的，一个保存行创建时间，一个保存行的过期时间。保存的不是实际时间，是系统版本号。每开始一个事务版本号自动递增，作为事务的版本号，用其与查询到的每行记录的版本号进行比较。

MVCC 只在REPEATABLE READ、READ COMMITTED两个隔离级别下工作。

SELECT

  • InnoDB 只查找版本号小于等于当前事务版本号的数据行。这样可以确保事务读取的行要么在事务开始之前存在，要么是事务自身插入或者修改过的。
  • 行的删除版本号要么未定义，要么大于当前事务版本号。确保事务读取到的行，在事务开始之前未被删除。

INSERT

  • 为每新插入的每一行保存当前系统版本号作为行版本号

DELETE

  • 为删除的每一行保存当前系统版本号作为删除标识

UPDATE

  • InnoDB 为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

存储引擎

MySQL可以根据用户的需要，使用不同的存储引擎。甚至可以为不同的表设置不同的存储引擎。

存储引擎特性对比

特点 MyISAM InnoDB MEMORY MERGE NDB
存储限制 有 64TB 有 没有 有
事物安全 支持
锁机制 表锁 行锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持 支持
全文索引 支持
集群索引 支持
数据缓存 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 低 高 N/A 低 低
内存使用 低 高 中等 低 低
批量插入速度 高 低 高 高 高
支持外键 支持

存储引擎适用场景

存储引擎 适用场景
MyISAM 默认存储引擎，适合以读和插入为主的操作，只有少量更新和删除，对事物完整醒和并发要求不高的场景
InnoDB 用于事物处理应用程序，支持外键，适用于对事物完整性有比较高的要求， 在并发条件下要求数据的一致性，数据出了查询和插入外，还包括很多更新和删除的操作。
MEMORY 将所有数据保存在内存中，在需要快读定位记录和其他类似数据的环境下，可提供极快的访问。缺陷是表中数据不能太大，服务一旦关闭数据将丢失
MERGE 用于将一系列等同的MyISAM表以逻辑的方式组合在一起，并作为一个对象引用它们。优点是突破单个MyISAM表大小限制;通过将不同的表分布在不同磁盘中，改善访问效率。适用于数据仓库等环境

存储引擎详细介绍

MyISAM

MyISAM不支持事务，不支持外键，优势是访问速度快。每个MyISAM在磁盘上存储成3个文件，文件名和表名相同:

  • .frm，存储表定义
  • .MYD，存储数据
  • .MYI，存储索引

数据文件和索引文件可以放在不同的目录下，获得更快的速度。

不同的MyISAM表可以放置在不同的目录下，文件路径必须是绝对路径。
MyISAM的表支持3种不同的存储格式。

  • 静态表:默认的存储格式，表中的字段都是非变长字段，这样每个记录都是固定长度。
    • 优势:存储非常迅速，容易缓存，出现故障容易恢复
    • 缺点:占用空间多
  • 动态表:包含变长字段，记录不固定长度的数据
    • 优势:占用空间少。
    • 缺点:频繁的删除和更新容易产生碎片，需要定期执行OPTIMIZE TABLE或myisamchk-r命令改善性能。
  • 压缩表:由myisampack工具创建，占据非常小的磁盘空间。
InnoDB

InnoDB具有提交，回滚和崩溃恢复能力的事务安全。但是相对MyISAM写的处理效率差，占用空间多。   InnoDB有两种存储方式:

  • 共享表空间存储，这种方式创建的表的表结构保存在.fm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
  • 多表空间存储，这种方式创建的表的表结构依然保存在.fm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是分区表，则每个分区对单独的.ibd文件，文件名是”表名＋分区名”,可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。   

开启多表空间的存储方式需要设置参数innodb_file_per_table，并重启服务后才能生效。生效后只对新建表有效。

多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等。   多表空间特性的表可以方便进行单表备份和恢复操作。

MEMORY

MEMORY使用内存中的内容创建表。一旦服务关闭，表中数据将丢失。 MEMORY默认采用HASH索引，可以指定BTREE索引。 MEMORY每个表的大小受到max_head_table_size变量的限制，并且可以指定MAX_ROWS子句限制表的最大行数。

MERGE

MERGE是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。

TokuDB

TokuDB是一个高性能、支持事务处理的MySQL和MariaDB的存储引擎，具有高扩展性、高压缩率、高效的写入性能，支持大多数在线DDL操作。

** TokuDB的特性**

  • 使用Fractal树索引保证高效的插入性能；
  • 优秀的压缩特性，比InnoDB高近10倍；
  • Hot Schema Changes特性支持在线创建索引和添加、删除属性列等DDL操作；
  • 使用Bulk Loader达到快速加载大量数据；
  • 提供了主从延迟消除技术；
  • 支持ACID和MVCC。

适用场景

  • 日志数据，因为日志通常插入频繁且存储量大；
  • 历史数据，通常不会再有写操作，可以利用TokuDB的高压缩特性进行存储；
  • 在线DDL较频繁的场景，使用TokuDB可以大大增加系统的可用性。

MySQL 优化

设计范式

第一范式 1NF

强调列表的原子性，每一列不可再分。

计数器表

对于统计下载次数、点击次数等需求，可以使用单独一张表来记录。但这样有个问题，对于更新这一行的事务来说，有个全局的互斥锁，导致事务只能串行执行，解决方案可以将计数器保存在多行中，每次选择一行进行更新。

1
2
3
4
5
6
create table hit_counter (
  slot tinyint unsigned not null primary key,
  cnt int unsigned not null
)engine=innodb;

update hit_counter set cnt = cnt + 1 where slot = rand()* 100;

可以使用on duplicate key update，语句存在则更新，不存在则插入。

如何加快 ALTER TABLE 速度

MySQL 执行大部分修改表结构操作的方法是用新的结构创建一个表，然后再将旧表中的数据复制到新表，删除旧表。因此有时 ALTER TABLE 操作甚至会执行数个小时，乃至几天。

ALTER TABLE 操作将导致服务中断，通常情况下有两种解决方式：

  • 在从库上执行操作，然后主从切换
  • 先新建一个无关的新表，通过重命名和删表操作交换两张表
  • 不是所有的ALTER TABLE 都会重建表，例如ALTER COLUMN来修改默认值

如何快速查询包含大量字符串的列

例如需要存储大量的 URL，并根据 URL 进行搜索查询，如果采用 B+Tree 来存储 URL，存储的内容就会很大，而InnoDB 不支持哈希索引。可以采取伪哈希索引，具体思路是，删除原 URL 列上的索引，新增一个被索引的 url_cc列，使用CRC32 做哈希，就可以采用如下方式查询：

1
select * from url where url='http://www.mysql.com' and url_crc=CRC32('http://www.mysql.com')

url_crc列可以手动维护，也可以用触发器维护。注意不要使用SHA1()、MD5()函数所谓作为哈希函数，因为这两个函数计算出来的哈希值是非常大的字符串，浪费大量空间。如果数据量特别大，可以考虑自己实现一个简单的 64 位哈希函数。这个函数要返回整型而不是字符串。

服务性能剖析

优化原则

  • 降低响应时间
  • 无法测量就无法有效优化，先测量慢查询

剖析单条查询

使用 SHOW PROFILE

SET profiling = 1; 开启 PROFILE。 每一条查询语句都会记录剖析信息到一张临时表，并赋予从 1 开始递增的 Query_ID。

剖析报告给出了查询执行的每个步骤及其花费的时间。可以通过SHOW PROFILES、 SHOW PROFILE FOR QUERY 1 或者直接查询information_schema.profilng 表。

SHOW STATUS

SHOW STATUS 返回一些计数器，计数器有会话级别的，也有全局级别的。使用时注意查询 MySQL 官方文档。

FLUSH STATUS 可以重置会话级别计数器。

SHOW STATUS 并不是个剖析工具，只能反映某些活动的频繁程度。其结果是实际测量的结果。EXPLAIN 是通过估计得到的结果。

慢日志

Performance Schema

诊断间歇性问题

区分是单条查询问题还是服务器问题

如果服务器所有的程序都突然变慢，又突然都变好，每一条查询也变慢了，那可能是服务器问题。

如果只是单条查询偶尔变慢，就可能是单条查询问题。

一般通过三种情况来解决：

  • SHOW GLOBAL STATUS， 定期捕获SHOW GLOBAL STATUS 输出的数据。 通过某些计数器的尖刺或凹陷发现问题。该方法简单并且对服务器影响较小。

  • SHOW PROCESSLIST ，定期捕获SHOW PROCESSLIST输出的数据，来观察是否有大量线程处于不正常状态。

    例如查询很少处于 statistics 状态，一般是服务器在查询优化阶段确认关联表的顺序。

    也可以通过查询information_schema.processlist表或者使用 innotop 工具

  • 慢查询日志，查询是在完成阶段才写入到慢查询日志里的

查询优化

这里查询的意思，不仅仅指 SELECT语句，而是泛指所有的 SQL 语句。

优化数据访问

查询性能底下的最基本的原因就是访问数据太多，可以通过下面两个步骤来分析：

  • 确认应用程序是否在检索大量超过需要的数据
  • 确认 MySQL 服务器是否在分析大量超过需要的数据行
向数据库请求了不需要的数据

某些请求超过实际需要的数据

  • 查询不需要的记录，查询出 100 条记录，实际上只需要显示 10 条。最简单有效的解决方式是使用LIMIT
  • 多表关联返回全部列
  • 全是取出全部的列，例如SELECT *。需要根据具体业务来思考是不是真的需要返回全部的列
  • 重复查询相同的记录，可以用缓存来提高性能
是否在扫描额外的记录

在确定查询只返回需要的数据后，接下来看看查询是否为了返回结果扫描了过多的数据。对于 MySQL 衡量查询开销的三个指标如下：

  • 响应时间，响应时间是服务时间和排队时间之和。 服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——等待 I/O、等待行锁等。
  • 扫描的行数
  • 返回的行数，理想情况下，扫描的行数和返回的行数应该是相同的。

如果查询需要扫描大量的数据但只返回少数的行，可以尝试下面技巧去优化它：

  • 使用索引覆盖扫描，把所需的列都放到索引里面，这样无需回表获取数据
  • 改变库结构，例如使用单独的汇总表
  • 重写 SQL

重构查询

将一个复杂的查询分成多个简单的查询可能更高效一些。例如对于删除操作，一个大查询可能需要锁住很多数据，将其分解成多个小查询可以尽可能小的影响 MySQL 性能，同时还可以减少 MySQL 复制的延迟。

分解关联查询，有以下好处

  • 让缓存的效率更高
  • 查询分解后，单个查询可以减少锁竞争
  • 容易对数据库进行拆分，提高扩展性
  • 查询本身效率也会有所提升
  • 减少冗余记录的查询

MySQL 查询原理

MySQL 执行一个查询的过程如下：

  • 客户端发送一条查询给服务器
  • 服务器检查查询缓存，有则返回，无则执行下一步
  • 服务器进行 SQL 解析、预处理，再由优化器生成对应的执行计划
  • MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
  • 将结果返回给客户端

如果客户端发送的查询过大，服务端会拒绝接受，可以通过配置max_allowed_packet 参数变更。

服务器当把所有的返回结果发送给客户端后，才会释放查询占用的资源。

查询状态

对于每一个 MySQL 连接都对应一个线程，该线程任何时刻都有一个状态，可以通过SHOW PROCESSLIST查询当前状态。

  • sleep，等待客户的发送新的请求
  • query，正在执行查询或者正在将结果发送给客户的
  • locked，在MySQL 服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如 InnoDB 的行锁不会体积在线程状态中。
  • analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • copying to tmp table [on disk]，线程正在执行查询，并且将结果集复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，要么是UNION操作。如果有 on disk 标记，表示 MySQL 正在将内存临时表放到磁盘上。
  • sorting result，正在对结果集进行排序
  • sending data，线程要么在多个状态之间传送数据、要么生成结果集、要么向客户端返回数据
查询缓存

查询缓存将在MySQL 5.7.20 中被弃用，在MySQL 8.0中被删除

如果 MySQL 的查询缓存是打开的，那么会优先检查这个查询是否命中查询缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找实现的。如果查询结果命中查询缓存后，MySQL 会校验一下 SQL 语句的权限，没问题的话，MySQL 会跳过所有其他阶段，直接从缓存中返回结果给到客户端。

查询优化处理

这一阶段包含解析 SQL、预处理、优化 SQL 执行计划。

语法解析器和预处理

MySQL通过关键字将SQL 语句进行解析，并生成解析树。MySQL 解析器将进行语法规则校验和解析查询。

预处理器进一步验证解析树是否合法，例如表、列是否存在，解析别名是否存在歧义等。

预处理器下一步会验证权限。

查询优化器

一条查询可以有很多执行计划，优化器的作用就是找到其中最好的执行计划。

MySQL 使用基于成本的优化器，它尝试预测查询使用某个执行计划时的成本，并选中其中成本最小一个。优化器通过一系列的统计信息计算得来：每个表或者索引的页面个数、索引的基数、索引和数据和的长度、索引分布情况。优化器在评估时不考虑任何层面的缓存，它假设读取任何数据需要一次磁盘 I/O。

有多种原因会导致优化器选择错误的执行计划

  • 统计信息不准确
  • 执行计划中的成本估算不等于实际执行的成本
  • 最优的执行计划并不一定是最短的执行计划
  • MySQL 不考并发的查询
  • 不是任何时候都基于成本优化，例如存在全文搜索的 match 子句，则存在全文索引的时候就使用全文索引
  • MySQL 不会考虑不受其控制的操作成本，例如存储过程或者用户自定义的函数

优化策略分为两种，一种是静态优化，例如将WHERE条件转换成另外一种等价形式。一种是动态优化，和查询的上下文有关。以下是 MySQL 能够处理的优化类型：

  • 重新定义关联表的顺序
  • 将外链接转成内链接
  • 使用等价变化规则，使用一些等价变换来简化并规范表达式。
  • 优化COUNT()、MIN()、MAX()，在 B+Tree 索引中优化器会将这个表达式作为一个常数看待，同时在EXPLAIN中可以看到”Select tables optimized away”。
  • 预估并转化为常数表达式
  • 覆盖索引扫描，索引包含索引查询的列时生效
  • 子查询优化，某些情况下将子查询转化为一种效率更高的形式
  • 提取终止查询，例如使用了LIMIT语句
  • 等值传播，在关联查询中 MySQL 能够把其中一个列的 WHERE条件传递到另一个列上
  • 列表∈的比较，MySQL 将∈中的数据线排序，通过二分查找方式来确定列表中的值是否满足条件
如何执行关联查询

MySQL 中的关联不仅仅是多表之间，MySQL 认为每一次查询都是一次关联，包括子查询、基于单表的SELECT 。

MySQL 对于关联执行的查询很简单，对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL 会在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多行以后，MySQL 返回上一层次关联表查找，依次类推。

如果 MySQL 在FROM子句中遇到子查询，子查询将结果放到临时表中，然后再将一个临时表当做普通表来看待。

UNION

对于UNION查询，MySQL 现将一系列的单个查询结果放到临时表中，再重新读出临时表数据来完成UNION 查询。

关联查询优化

MySQL 优化器会调整多表关联时的顺序，可以使用STRAIGHT_JOIN 按照你指定的顺序查询，只对INNER JOIN有效。

当然不是所有的多表关联的顺序不能随意调整，例如左连接、子查询等，后面表的查询需要依赖前面表的查询结果。

排序优化

当不能使用索引生成排序结果的时候，MySQL 需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，这个过程统称为文件排序（filesort）。

如果排序的数据量小于排序缓冲区，MySQL 使用内存进行快速排序操作。如果内存不够，MySQL 会先将数据分块，对每个独立块使用快速排序，将各个块的排序结果放到磁盘上，然后将排序好的块进行合并，最后返回排序结果。

MySQL 使用如下两种排序算法：

  • 两次传输排序（旧版本使用），读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。第二次读取数据行的时候会产生大量随机 I/O。所以两次传输成本很高，好处是会让排序缓冲区容纳更多的行数进行排序
  • 单次传输排序（新版本使用），先读取查询所需要的所有列，再根据排序列进行排序，最后直接返回排序结果。好处是只需要一次顺序I/O 读取所有数据，缺点是会额外占用大量空间

MySQL 在进行文件排序时，对每一个排序记录都会分配一个足够长的定长空间来存放，这个定长空间必须足够长以容纳最长的字符串。因此文件排序时使用的临时空间比想象中更大。

关联查询排序时，如果排序列都来自第一表，则在关联处理第一个表的时候就进行文件排序，在EXPLAIN的Extra字段会看到 “Using filesort”。反之，MySQL 会先将关联的结果存放到一个临时表中，等所有关联结束后在进行文件排序，在EXPLAIN的Extra字段会看到 “Using temporary;Using filesort”字样。

查询器优化提示

  • HIGH_PRIORITY 和 LOW_PRIORITY，HIGH_PRIORITY会让 MySQL 将该查询放到表的队列前面；LOW_PRIORITY会让语句一直处于等待状态，只要队列中还有需要访问同一个表的语句。这两个提示只针对使用表锁的存储引擎有效，千万不要在 InnoDB 或者其他有锁机制的引擎中使用。它们只能简单的控制 MySQL 访问某个数据表的队列顺序。
  • DELAYED，对INSERT、REPLACE有效，MySQL 会立即将返回给客户端，并将插入的行数据放入到缓冲区，等待空闲时批量将数据写入。适合于日志系统。
  • STRAIGHT_JOIN，放在SELECT关键字后，也可以放在任意两个关联表的名字前面。让查询中所有表按照语句中出现的顺序进行关联。
  • SQL_SMALL_RESULT 和 SQL_BIG_RESULT，只针对SELECT语句有效，它告诉优化器对于GROUP BY、DISTINCT 查询如何使用临时表及排序，SQL_SMALL_RESULT表示结果集很小，可以放到内存中的索引临时表，以避免排序操作。SQL_BIG_RESULT表示结果集很大，建议使用磁盘临时表做排序操作
  • SQL_BUFFER_RESULT，告诉优化器将查询结果放到临时表，然后尽可能的释放表锁。

MySQL 配置

long_query_time 慢查询

pt-query-digest MySQL 查询日志分析工具。

MySQL 相关命令

explain 用于查看SQL 语句执行计划。explain 的type列值为index，则说明 MySQL 使用索引扫描来做排序。

show processlist

show status

show innodb status

show profile

常用命令

查看当前默认存储引擎

1
show variables like 'table_type';

查看支持的存储引擎

1
2
3
show engines;
# 或者
show variables like 'have%';

导入导出

导出数据库

1
2

mysqldump -u root -p database_name > database_dump.sql

只导出表结构

1
2

mysqldump　--opt -d database_name -u root -p > database_dump.sql

导出所有数据库

1
2

mysqldump -u root -p --all-databases > database_dump.sql

导出指定表

1
2

mysqldump -u root -p database_name table_name > dump.sql

导入数据库，导入之前确保数据库已存在

1
2

mysql -u root -p database_name < dump.txt