MySQL简单基础优化方案
原创
MySQL优化方案
一、MySQL优化理论
1.优化原则
- 减少磁盘的IO次数，尽量按照索引查找，避免全表扫描。
- 只查询用到的数据，没用到的数据不要查询，减少查询结果数据大小。
2.优化方案
（1）索引优化
- 在区分度高的字段上建立索引。
- 建立组合索引的时候考虑是否会形成索引覆盖，这样会减少回表的次数。
- 建立组合索引时，遵循最左匹配原则。将查询频率比较高，区分度比较大的字段放在最左侧。
- 为作为查询条件和进行排序操作的字段建立索引，比如where、group by、order by字段建立索引。
- 索引字段作为条件查询时，不要进行函数计算，不要使用!=或<>操作符，这样不会命中索引，会退化到全表扫描。
（2）SQL优化
? 查询优化
- 尽量避免使用select * 这样的SQL语句，这样让优化器无法完成索引覆盖，降低了优化器的执行效率，同时返回了更多的数据，占用了更多的网络带宽。
? 关联优化
- 多表union关联的时候小表在前，大表在后，这是由于MySQL在进行多表union操作的时候，会先全表扫描前一张表，再关联后一张表。
- 多数场景建议使用join/union，不建议使用子查询，由于子查询会生成中间临时表，对于性能的消耗比较大。
? 分页优化
- 使用合理的分页来提高效率，在SQL中利用offset和limit来限制查询的起始位置和条数，能够在SQL做就不要在代码中做。
? 复杂语句
- 复杂语句可以拆分成多个简单语句来执行，这是由于复杂语句由于可能长时间加锁导致执行时间更长；
（3）库表优化
- 数据量比较大的情况可以进行分库分表操作，一般可以按照水平分库或者垂直分库的方式。
- 也可以通过引入集群模式，实现读写分离，这样读写的操作压力不会集中在一个节点上。
（4）配置优化
- 合理使用存储引擎，比如根据不同的业务场景选择使用InnoDB还是MySIAM的存储引擎。
- 预期开启数据库缓存，要开启多大的等，这些可以通过修改数据库配置项实现。常见的配置项修改包括最大连接数
max_connections，buffer pool的大小innodb_buffer_pool_size等。
（5）其他方面
- 通过程序操作数据库，如果短时间内频繁操作的话，可以进行批量操作。
总结：最主要的优化策略还是索引优化和SQL优化，之后就是再调整下Mysql的配置参数，想读写分离、分库分表在系统架构设计的时候就需要确定，后续变更的成本太高。
二、MySQL优化实践
几个问题主要代表了MySQL实践过程中年可能会碰到的问题。
1.通过explain查看查询计划
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。通过一下语句可以查看查询计划：
EXPLAIN SELECT [查找字段名] FROM tab_name ...查询计划结构中每个字段的含义：
1. id列
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
2. select_type列
select_type 表示对应行是简单还是复杂的查询。
3. table列
这一列表示 explain 的一行正在访问哪个表。
4. partitions列
如果查询是基于分区表的话，partitions 字段会显示查询将访问的分区。
5. type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL；一般来说，得保证查询达到range级别，最好达到ref。
6. possible_keys列
这一列显示查询可能使用哪些索引来查找。
7. key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
8. key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
9. ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
10. rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
11. filtered 列
该列是一个百分比的值，rows filtered/100 可以*估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。
12. Extra列
这一列展示的是额外信息
2.进行分库分表
MySQL数据库数据超过2000w，基本就达到了性能瓶颈，可以考虑通过分库分表来分担单点压力。但是分布分表会带来一些问题，比如对于一些排序、连接等功能会失效，或者对于单条insert、update语句会失效。以下就几个问题进行探讨。
（1）如何进行拆分？
对于分布分表，通用方式有水平拆分和垂直拆分。水平拆分是将一张表的数据水平切分为多张表来保存，可以用到的方式是按照ID范围或者进行hash运算后拆分；垂直拆分是将一张表的字段拆分成多组，每一组放到一张表中，这种一般情况下可以根据业务来进行拆封。
（2）数据如何进行排序？
对于拆分后的数据怎么进行排序？有个思路是根据查询语句select * from t1 order by id向数据库分片查询数据，查询的数据汇集到内存，如果有N个分片，则会存在N个数据块，再通过归并排序的方式对其进行排序。
（3）如何生成分布式ID？
业界有比较多的生成分布式ID的算法，核心思路是分布式ID的不同分段代表的含义不一样，比如前部分代码时间，中间部分代表数据库实例，最后部分代表的是表的自增序列。
3.开启慢查询优化
可是事后开启慢查询监控，当数据库SQL执行超过一定时长，再做优化。
使用druid数据库连接池就能查看到慢查询日志。
我正在参与 腾讯云开发者社区数据库专题有奖征文。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。