MySQL调优总结 [TOC] ## EXPLAIN 语句 ### 语法示例： ```mysql EXPLAIN SELECT * FROM table_name; ``` ### 输出： | 字段 | 示例 | 说明 | | ------------- | ---------- | -------------------------------------- | | id | 1 | SELECT语句的标识符 | | select_type | SIMPLE | SELECT语句的类型 | | table | table_name | 查询表名 | | partitions | NULL | 匹配的分区 | | type | const | 查询类型 | | possible_keys | NULL | 可能用到的索引 | | key | NULL | 确认用到的索引 | | key_len | 8 | 用到的索引的长度 | | ref | const | 哪个字段或常数与 key 一起被使用 | | rows | 1000 | 扫描的行数 | | filtered | 0.00 | SELECT语句的查询条件过滤的数据的百分比 | | extra | NULL | 额外的数据 | ### 重要的字段 #### select_type SELECT语句的类型，常用可取值: 1. `SIMPLE`： 表示此查询不包含 UNION 查询或子查询 2. `PRIMARY`： 表示此查询是最外层的查询 3. `UNION`：表示此查询是 UNION 的第二或随后的查询 4. `DEPENDENT UNION`：UNION 中的第二个或后面的查询语句, 取决于外面的查询 5. `UNION RESULT`： UNION 的结果 6. `SUBQUERY`： 子查询中的第一个 SELECT 7. `DEPENDENT SUBQUERY`： 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果. #### type 该字段提供了SQL语句是否高效的重要依据 可取值： 1. `ALL`：全表扫描 2. `index`：全索引扫描 3. `range`：范围索引 4. `ref`：多表查询时使用了非唯一或非主键索引的查询，或是使用了`最左前缀`原则的索引 5. `eq_ref`：多表查询时使用了唯一或主键索引的查询 6. `const`：主键或唯一索引的等值扫描，最多只返回一行数据 7. `system`：表中只有一条数据. 这个类型是特殊的 `const` 类型 性能优先级： ``` ALL < index < range < ref < eq_ref < const < system ``` system最佳， ALL最差 #### possible_keys 可能用到的索引 #### key 确认用到的索引 #### key_len 用到的索引的字节数，该字段可以用于评估组合索引是否被完全使用，或只有最左部分字段被使用到 key_len 的计算规则如下: 字符串类型： | 类型 | 字节长度 | | ---------- | ---------------------------------------- | | char(n) | n | | varchar(n) | utf8：3\*n+2 字节， utf8mb4：4\*n+2 字节 | 数值类型： | 类型 | 字节长度 | | --------- | -------- | | tinyint | 1 | | smallint | 2 | | mediumint | 3 | | int | 4 | | bigint | 8 | 时间类型： | 类型 | 字节长度 | | --------- | -------- | | date | 3 | | timestamp | 4 | | datetime | 8 | 注：若字段属性默认值为NULL则占一个字节 #### rows SQL得到结果集共需要扫描多少行。行数越少越好。 #### Extra EXplain的额外信息会显示在本字段中，重要的有： | 字段 | 说明 | | --------------- | ------------------------------------------------------------ | | Using filesort | 排序操作时无法通过索引顺序操作。建议优化 | | Using indexs | 使用了全局索引扫描 | | Using temporary | 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化. | ## 数据库结构优化 1. 将字段多的表拆分为多张表。当数据量大时数据库会因为使用频率低的字段而变慢。 2. 合理的加入冗余字段。合理的加入冗余字段可以提高查询速度。 3. 增加关联表。将需要多表联查的数据合并到一个中间表中，可以增加查询效率。 ## MySQL存储引擎 ### 常见的MySQL存储引擎的异同 | 功能 | InnoDB | MyISAM | MEMORY | ARCHIVE | | :----------- | :----- | :----- | :----- | :------ | | 存储限制 | 64TB | 265TB | RAM | NONE | | 支持事务 | YES | NO | NO | NO | | 支持全文索引 | NO | YES | NO | NO | | 支持B树索引 | YES | YES | YES | NO | | 支持哈希索引 | NO | NO | YES | NO | | 支持集群索引 | YES | NO | NO | NO | | 支持数据索引 | YES | NO | YES | NO | | 支持数据压缩 | NO | YES | NO | NO | | 支持外键 | YES | NO | NO | NO | | 支持数据缓存 | YES | NO | N/A | NO | | 空间使用率 | 高 | 低 | N/A | N/A | ### InnoDB引擎的特点 1. 支持事务 2. 锁粒度为行级锁 3. 支持热备份 4. 容灾性强。可以通过bin-log日志恢复表 5. 支持缓存。innodb_buffer_pool_size 6. 支持外键 ### MyISAM引擎的特点 1. 支持大文件 2. 支持全文索引 3. 支持数据压缩 ### 存储引擎的选型 - InnoDB：对数据安全性要求较高，频繁insert和update操作 - MyISAM：频繁新增、查询操作 - Memory：临时存放数据、数据量较小、对安全性要求不高 - archive：对数据安全性要求不高，同时有大量写的并发写入。例如系统日志 ### 存储引擎优化 #### MyISAM 1. 禁用索引。对于非空表MySQL会根据数据的插入记录建立索引，插入大量数据前关闭索引，插入后再开启可以提高效率，空表无需关闭。 2. 禁用唯一性检查（数据插入完成后恢复） #### InnoDB 1. 禁用唯一性检查（数据插入完成后恢复） 2. 禁用外键检查（数据插入完成后恢复） 3. 禁用自动提交事务（数据插入完成后恢复） ## SQL 语句的优化 1. 使用join代替子查询 2. 避免在`where`字句中使用`IS NULL`，否则将导致引擎放弃使用索引而进行全表扫描 3. 避免在`where`字句中使用`!=`或`<>`，否则将导致引擎放弃使用索引而进行全表扫描 4. 避免在`where`字句中使用`or`，否则将导致引擎放弃使用索引而进行全表扫描 5. 避免在 where 子句中对字段进行表达式操作（如：`where num/2=100`）这将导致引擎放弃使用索引而进行全表扫描 6. 避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描 7. 对于连续的数值，应使用`between`而非`in` 8. 可以考虑将`like`替换为`全文索引` ## 分库分表 ### 垂直拆分 **垂直分表** 将数据表中不常用、长度较大的字段拆分出来做为扩展表。 **垂直分库** 将不同的业务的数据表拆分为子库，例如user表拆分为user库。 ### 水平拆分 **水平分表** 将数据量较大的表，按照某种规则拆分到子表中。 **水平分库分表** 将数据量较大的表，拆分到其他数据库中。 **水平分库分表切分规则** 1. RANGE：如从0到10000一个表，10001到20000一个表 2. HASH取模。 取主键id，然后hash取模，分配到不同的数据库上 3. 地区代理：如华东、华南等 4. 时间：如将6个月前、一年前的拆分出去。因为时间越长这些数据被查询的概率就越小，所以没必要和热数据放在一起，这也是`冷热数据分离` ### 拆分后的问题 1. 跨库join 2. 事务支持 3. 多库结果集合并（group by、order by） 4. 数据一致性 2019-08-04 MySQL调优 展开评论
