MySQL
MySQL 数据类型
数值类型
整数类型
这里的M表示指定显示宽度。一般与zerofill
配合使用,意为当实际数值宽度小于M时,在数值前填充0。 当某列指定zerofill
时,自动为该列添加UNSIGNED
属性。
整数类型还有一个属性AUTO_INCREMENT
,意为自动+1。该属性只用于整数类型,一个表最多有一个AUTO_INCREMENT
列。定义时,应该同时定义为NOT NULL
,PRIMARY KEY
或UNIQUE
。
类型 | 字节 | 范围 |
---|---|---|
TINYINT(M) | 1 | -128 |
SAMLLINT(M) | 2 | -32768 |
MEDIUMINT(M) | 3 | -8388608 |
INT(M)、INEGER(M) | 4 | -2147483648 |
BIGINT(M) | 8 | -9223372036854775808 |
浮点类型
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 | SELECT * from ruigu_member_order INNER JOIN ( |
维护索引和表
系统崩溃、硬件问题、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 | SHOW VARIABLES LIKE 'AUTOCOMMIT'; |
有些命令在执行之前会强制执行COMMIT
提交当前活动事务,例如数据定义语言 DLL ,ALTER TABLE
等。 还有LOCK TABLES
等其他语句也会导致同样的结果。
MySQL 可以通过SET SESSION TRANSACTION ISOLATION LEVEL
来设置隔离级别。
如果在事务中混合了事务表和非事务表,在正常情况下不会有影响,事务需要回滚时非事务表的变更无法撤销,也不会报错,至多发出警告。
隐式和显式锁定
InnoDB 采用两阶段锁定协议(two-phase locking protocol)。在事务执行过程中随时可以执行锁定,锁只有执行COMMIT
、ROLLBACK
的时候才会释放,并且所有锁同一时刻被释放。这些是隐式锁定,InnoDB 根据隔离级别在需要的时候字段锁定。
InnoDB 支持特定语句进行显式锁定,这些不属于 SQL 规范。
1 | SELECT ... LOCK IN SHARE MODE |
多版本并发控制(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 | create table hit_counter ( |
可以使用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 | show engines; |
导入导出
导出数据库
1 |
|
只导出表结构
1 |
|
导出所有数据库
1 |
|
导出指定表
1 |
|
导入数据库,导入之前确保数据库已存在
1 |
|