MySQL 的使用优化主要从优化库表结构使用合适的索引优化查询等方面考虑
优化库表结构
说说数据类型
字符串类型
在 MySQL 中表示字符串的类型有多种, 其中常见的有 Char 和 VarChar,BLOB 和 TEXT 以及可以实现某些优化的 ENUM 我们要认识到不同的数据类型在存储和使用的区别, 然后合理的使用就能实现优化
关于 MySQL 中数据类型的介绍, 可以参看我的另一篇博文: MySQL 的常见操作
Char 和 VarChar
Char 是定长类型, MySQL 会根据定义的字符串的长度分配足够的空间, 并且 MySQL 在存储 CHAR 值的时候会删除末尾所有的空格对于经常变更的数据, 一般采用 CHAR 来进行存储, 因为 CHAR 类型在变化的时候不容易产生碎片
VARCHAR 是变长类型, 它比 CHAR 更加节省空间, 但是 VARCHAR 在数据变化的时候容易产生碎片, 所以一般用于作为不经常变化的数据的数据类型 VARCHAR 需要多使用一个或者两个额外字节来记录字符串的长度, 如果列的最大长度小于等于 255 字节就用一个额外的字节来存储长度, 否则使用两个字节例如 VARCHAR(10)的列需要 11 个字节的存储空间, VARCHAR(1000)的列需要 1002 个字节
注意, 在 5.0 或者更高的版本中, MySQL 在存储或者检索 VARCHAR 数据类型时保留末尾空格, 但是在 4.1 或者更老的版本中, MySQL 在存储或者检索 VARCHAR 数据类型时和 CHAR 一样都是删除末尾的空格
BLOB 和 TEXT
BLOB 即 SMALLBLOB,TEXT 即 SMALLTEXTBLOB 和 TEXT 都是为了存储很大的数据而设计的字符串数据类型, 分别采用二进制和字符串的方式来存储
使用 ENUM 来代替字符串类型
MySQL 在存储枚举的时候非常的紧凑, 会根据列表值的数量压缩到一个或者两个字节中 MySQL 在内部会将每个值在列表中的位置保存为整数, 并且在表的. frm 文件中保存 "数字 - 字符串" 映射关系的 "查找表"
例如:
create table enum_test(e enum('apple','banana','pear'));insert into enum_test(e) values('apple'),('banana'),('pear');
然后我们所插入的数据在表中其实是存储为整数的
数字类型
MySQL 可以为整数类型指定宽度, 如 INT(1),INT(20), 但是这对大多数应用是没有意义的 MySQL 所指定的整数类型的宽度只是用于设置一些 MySQL 客户端用于显示字符的个数, 对于存储和计算而言, INT(1)和 INT(20)不会限制值的合法范围, 这两种类型都是相同的
日期和时间类型
DateTime 和 TimeStamp
DATETIME 和 TIMESTAMP 是两种日期类型, 两种类型在 MySQL 中存储数据的格式完全相同(都是 yyyy-MM-dd HH:mm:ss), 但是两者也有不同之处
DATETIME 能保存大范围的值, 从 1001 年到 9999 年, 精度为秒 MySQL 采用 8 个字节来存储 DATETIME 数据类型所包含的值默认情况下, MySQL 以一种可排序的无歧义的格式显示 DATETIME 的值
TIMESTAMP 保存了从 1970 年 1 月 1 日午夜 (格林尼治标准时间) 以来的秒数, 它和 UNIX 的时间戳相同 TIMESTAMP 仅仅使用 4 个字节的存储空间, 所以它能表示的时间范围也比 DATETIME 小, 只能表示从 1970 年到 2038 年 TIMESTAMP 也有 DATETIME 没有的特殊属性, 默认情况下, 如果插入时没有指定第一个 TIMESTAMP 列的值, MySQL 则设置该列的值为当前时间
采用合适的索引
索引优化
索引优化是一个很大的方面, 这里只是简单的介绍一些基本使用, 过后会推出关于索引优化与设计的专题
索引基础
在 MySQL 中, 索引是在存储引擎层而不是服务器层实现的 Mysql 中索引结构有: B-Tree 索引哈希索引空间数据索引 (R-Tree 索引) 全文索引等索引结构, 不同的存储引擎对于上述索引结构的实现不同, 而且也不是所有的存储引擎都有这 5 种索引结构类型
索引类型:
MySQL 中的索引类型主要有 5 种:
普通索引: 最基本的索引没有任何限制 MyIASM 中默认的 BTREE 类型的索引如
- ALTER TABLE article ADD INDEX index_title_name ON title(100);
- CREATE INDEX index_name ON table(column(100))
, 或者直接在创建表的时候定义索引
index index_title_name(title(100))
唯一索引: 索引列的值可以为空与普通索引类似, 不同之处在于索引列的值必须唯一如
- ALTER TABLE article ADD UNIQU index_title_name ON title(100);
- CREATE UNIQUE INDEX index_name ON table(column(100))
或者直接在创建表的时候定义索引
UNIQUE index_title_name(title(100))
全文索引: 主要用来查找文本中的关键字, 而不是直接与索引中的值相比较仅可用于 MyISAM 表, 针对较大的数据, 生成全文索引很耗时耗空间如
- ALTER TABLE article ADD FULLTEXT index_content(content)
- CREATE FULLTEXT INDEX index_content ON article(content)
和 FULLTEXT (content)
主键索引: 它是一种特殊的唯一索引, 不允许有空值
最左索引 (组合索引): 组合索引可以更好的提高 MySQL 效率, 最左索引遵循 "最左索引" 原则创建复合索引时应该将最常用(频率) 作限制条件的列放在最左边, 依次递减
索引方法:
可以使用 B 树索引的查询:
全值匹配的查询
匹配最左前缀的查询
匹配列前缀的查询
匹配范围值的查询
精确匹配左前列并且范围匹配另外一列
覆盖索引(只需要访问索引而无需查询数据行)
使用 B 树索引的限制
在多列索引中, 必须按照索引的最左列开始查找, 否则索引无法使用
在多列索引中, 不能跳过索引中的列比如一个三列组成的联合索引, 不能只使用第一列和第三列进行查询而跳过第二列
not in 和 <> 操作无法使用索引
如果查询中有某一个列的范围查询, 则其右边所有的列都不能使用索引
Hash 索引的特点:
Hash 索引是基于 Hash 表实现的, 只有查询条件精确匹配 Hash 索引中的所有列时, 才能使用 Hash 索引, Hash 索引只适用于等值查询不适合模糊查询和范围查询
对于 Hash 索引中的所有列, 存储引擎都会为该列的每一行计算一个 Hash 码, Hash 索引中存储的就是 Hash 码
使用 Hash 索引的限制:
Hash 索引必须进行二次的查找
Hash 索引无法进行排序
Hash 索引不支持部分索引查找也不支持范围查找
Hash 索引中 Hash 的计算可能存在 Hash 冲突
B 树索引与 Hash 索引在很多地方是不同的 B 树索引除了能加快数据的查找速度之外还可以做到排序和分组, B 树索引的叶子节点存储了索引关键字的值, 可以直接通过索引查找关键字的信息从而避免了访问数据行但是 Hash 索引的叶子节点中存储的是关键字信息的 Hash 码, 我们需要将查询信息转化成 Hash 在表中找到对应的数据行才能查找到数据的信息因此 Hash 索引不能作为覆盖索引来使用
覆盖索引:
如果一个索引包含所有需要查询的字段的值 (where 语句的参数 order by 的参数 group by 的参数), 那么我们通常称这个索引为覆盖索引对于 Memory 存储引擎不能使用覆盖索引, 查询过程中如果包含了太多的列(如 select *) 也不适合使用覆盖索引 使用覆盖索引也有很多的优点
优化缓存, 减少磁盘 I/O 操作
减少随机 I/O, 变随机 I/O 为顺序 I/O
可以避免对 Innodb 主键索引的二次查询
InnoDB 存储引擎中的索引
InnoDB 作为 MySQL 最为著名的存储引擎, 这里要做特别的介绍 InnoDB 中存储引擎支持 B + 树索引全文索引和哈希索引 InnoDB 存储引擎支持的哈希引擎是自适应的, InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引, 不能人为干预是否在一张表中生成哈希索引
传统意义上的索引就是指的 B + 树索引, 这是目前关系型数据库系统中查找最为常用和有效的索引, 其构造就是采用了二叉树的思想, 根据键值对快速找到数据通过 B + 树索引找到被查找数据行所在的页, 然后数据库把页读入到内存, 再在内存中进行查找, 找到对应的数据
InnoDB 使用的是行锁, 只有在修改行时, 才会对行进行加锁使用索引能够使得数据在查询过程中锁定更少的行, 增加了数据处理的并发性, 提高了数据库的性能
索引使用的注意事项
保证在 MySQL 中查找数据时, 表中对应的列数独立的独立的列在于索引列不能是表达式中的一部分, 也不能是函数的参数即不允许
select id from article where id+1=5
等情况的出现, 否则索引将不能使用
索引很长的字符列, 会让索引变得大且慢这个时候就要采用前缀索引, 就是选取列开始的部分字符作为索引, 前缀索引的选择也要保证合理的索引选择性(越接近 1 越好)
如果不需要考虑排序和分组的需要, 在联合索引中, 应该将选择性最高的索引放到索引的最前列将经常会被使用的列放到索引的最前列宽度较小的列放到索引的最前列
使用
pt-duplicate-key-checker h=127.0.0.1
查找重复和冗余的索引, 然后将重复冗余的索引删除
改造 SQL 查询语句
MySQL 连接过程与状态
MySQL 连接状态
MySQL 客户端和服务器之间的通信协议是 "半双工" 的, 在任何一个时刻, 要么是由服务器向客户端发送数据, 要么是由客户端向服务器端发送数据, 两个动作不能同时发生对于每一个时刻, 可以通过命令
show full processlist
来查看 mysql 当前连接的状态(Command 列就代表当前的状态)
MySQL 的状态如下:
Sleep: 线程正在等待客户端发送新的请求
Query: 线程正在执行查询或者正在将结果发送给客户端
Locked: 在 MySQL 服务器层, 该线程正在等待表锁.
Sorting result: 线程正在对结果集进行排序
Copying to tmp table [on disk]: 线程正在执行查询, 并且将其结果集都复制到一个临时表中, 这种状态要么是在做 GROUP BY 操作, 要么是文件排序操作, 或者是 UNION 操作如果状态上有 on disk 的标记, 那么表示 MySQL 正在将一个内存临时表放到磁盘上
Analyzing and statistics: 线程正在收集存储引擎的统计信息, 并生成查询的执行计划
Sending data: 这表示线程或者在多个状态之间传送数据, 或者在生成结果集, 或者在向客户端返回数据
MySQL 连接过程
MySQL 客户端发送一条查询给服务器
MySQL 如果开启了查询缓存, 那么 MySQL 服务器会优先检查查询缓存检查的过程是通过一个对大小敏感的哈希查找实现的, 如果缓存命中, 那么在返回查询结果之前 MySQL 会检查一次用户权限, 如果权限合适, 那么直接返回缓存中的结果信息, 查询完成, 否则执行下一步
服务器进行 SQL 解析预处理, 然后再由优化器生成对应的执行计划
MySQL 根据优化器生成的执行计划, 调用存储引擎的 API 来执行查询
将查询的结果返回给客户端
查询优化
在上面 MySQL 执行查询的过程中, 服务器已经提供了一些 SQL 的优化措施, 我们也需要正确理解并使用这些个优化措施
使用 explain+sql 查询语句可以查看 SQL 查询的效率
mysql 使用基于成本的优化器使用
show status like 'last_query_cost'
可以查询当前会话的 last_query_cost 值来得知 mysql 计算的当前查询的成本, mysql 会进行评估并得到成本最小的执行计划
MySQL 自带一种 "嵌套循环" 能够对我们的大多数查询进行优化操作, 调整关联表的关联顺讯以达到高效的查询
优化数据访问
优化数据访问的关键在于: 减少数据访问量, 只检索必要访问的数据, 保证向数据库发出的查询数据量只是实际需要的数据量
为了实现数据访问量的优化, 可以使用:
在 SQL 的查询语句中, 合理的使用 limit 控制行数
在多表关联的 SQL 查询中, 只查询需要的表的列, 尽量不要用 "select *"
借助第三方的缓存系统, 将经常查询的数据缓存起来
如果查询是需要扫描大量的数据但只是返回少量的行, 那么可以使用索引覆盖扫描, 把需要数据的行放到索引中
重构查询
将大的查询分解成小的查询特别是对于删除不需要的数据, 一般来说就是分批删除少量的数据, 这样可以大大减少数据库锁的持有时间
合理的分解关联查询关联查询分解成单表查询可以减少锁的竞争; 同时单表查询的结果在应用层做关联, 可以实现数据库的拆分, 做到高性能和可扩展此外, 通过将重复查询的数据做缓存可以提高效率
MySQL 的某些子查询效率很低 (如使用 in 的子查询), 我们应该使用 explain 语句测试当前查询的成本, 然后决定是否应该使用内连接或者左(右) 外连接改写 mysql 的 in()子查询但当我们需要返回一个表中的某些列时, 多表关联查询我们可以使用 exists 关键字的子查询, 这样效率也会更高在 MySQL5.6 版本以前需注意
在使用 union 关键字进行 sql 查询时, 如果有限制数据量和排序等操作, 应在每一条 sql 语句中使用这些限制
使用主键自带的排序效果和 limit 关键字来代替 max 和 min 关键字实现最大和最小值
MySQL 在需要进行分页时, 通过使用 limit 外加偏移量来实现, 同时加上合适的 order by 子句, 这样可以充分的利用具有索引的列此外, 在分页中, 偏移量如果相差数据量过大, 应该采用索引覆盖扫描
进行关联查询时, 在 on 和 using 子句的列上添加索引, 并且注意在关联顺序上, 应该在第二章表中添加索引, 提高效率
确保 group by 和 order by 子句的表达式上只涉及一个表中的列, 只有这样才有可能使用索引优化这个过程
来源: https://juejin.im/post/5a9b6342f265da23a2289281