Mysql 优化方案
发现问题，我们去解决问题，我们发现有慢查询怎么办，肯定要想办法去优化，优化的方式有很多很多，主要分为以下几个方向
一、硬件层面优化
我们知道，Mysql性能最重要的瓶颈在磁盘的IO，所以硬件层面，最重要的其实就是磁盘。
- 提高磁盘读写能力，可以用比较新型的磁盘
- 减少寻址时间，可以横向扩展，将数据添加到不同的磁盘，每个磁盘数据的寻址通常1s 100次寻址，那么单个磁盘有限制，就多个磁盘寻址当然，除了磁盘以外，cpu、内存以及带宽也是比较重要的因素
二、增加服务器资源
部署主从、多主多从等集群
三、数据库层面优化
表结构优化
字段优化
字段设计，字段设计其实我们平时在设计表的时候，学校里面可能告诉你，你要遵循三范式，什么不可分解、数据必须是和主键相关的、不能依赖另外的非主键值。但是，我们在真实的项目场景中，要真正满足三范式是很难的，有时候我们为了性能，会有一定的冗余数据，用空间来换取我们的时间。空间与时间，需要大家找到一个平衡字段类型尽可能使用最小的并且满足业务场景的数据类型，这样行数据占用的内存越小，索引树越矮，磁盘IO次数越低。同时尽量避免null字段出现，可以指定默认值，减少判断null的开销，因为有null的时候，索引需要对null进行单独处理- 是否有适当的行格式
合适的存储引擎
Mysql 支持不同的存储引擎，那么不同存储引擎的底层实现也是不一样，那么性能也不同。
比如默认的 InnoDB 存储引擎，支持事务，会优先保证数据的一致性。如果业务场景对数据的一致性不够高的话或者读多的场景，可以采用MyIsam 或者 memory的存储引擎。
InnoDB 存储引擎优化
由于不同的存储引擎实现原理不一样，所以不同的存储引擎的优化也都不一样，我们着重说下 InnoDB 存储引擎
innoDB 架构优化
innoDB 架构，其实整体就是操作数据，然后将数据同步到磁盘，然后中间加了个内存区间，并且保证了数据库的ACID模型。
那么从这些方面，我们可以尽可能的去减少跟磁盘交互，或者去牺牲一定的数据一致性来保证性能
增加 bufferpool 大小
bufferpool 我们知道是 innodb 里面缓存数据的内存区间，默认大小为128M，bufferpool 越大，那么内存能放的数据越大，这样，查询数据去磁盘查询的次数也就越少。
-- 可以设置innodb 的 bufferpool 的大小
SET GLOBAL innodb_buffer_pool_size = 402653184;建议可以使用机器的50%-70%的内存
增加 redolog (重做日志)大小
隔离级别优化
判断是否可以牺牲数据一致性采用性能更高的隔离级别，比如RC。
Sql 语句优化
Explain 执行计划
建立索引建立在where、orderby、groupby 的字段上面，提升查询性能；
但是就算加了也不一定能走到索引，所以要学会Explain分析
Explain 输出字段
官网：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-output-columns
select_type
查询类型
table
表名，也可以是子查询的表
partitions
分区 查询语句要走哪些分区
分区官网：https://dev.mysql.com/doc/refman/8.0/en/partitioning-info.html
type
连接类型
具体定义：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types
system ( 特例const, 系统表中只有1行 )、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL
我们最好要能达到range，如果达不到，要进行优化
possible_keys
可以选择的索引查询，如果为 null 则没有索引可以供选择。
key
真正使用的索引
key_len
使用的键的长度
ref
rows
执行查询必须扫描的行数，对于InnoDB来讲，这个是个预估值，不是非常准确
filtered
行数据过滤百分比, 假设显示的 10, 则需要过滤掉 90%的数据
Extra
官网描述：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information
Using filesort: 排序没有走到索引
Using index: 在索引树中能遍历到想要的数据（覆盖索引）
Using index condition: 索引条件下推
Using index for group-by group by: 分组基于索引检索
Using temporary: 是否使用临时表，一般在 group by与order by场景
Using where: 扫描出来的数据需要进行where匹配
跳跃表扫描：https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan
order by 优化
官网介绍：https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
内存排序
如果让 orderby 的字段走索引，那么排序流程直接可以在索引树完成，如果排序的字段不走索引，整个排序流程必须先把数据放到内存，在内存实现排序。这个内存的大小由 sort_buffer_size 配置,如果内存不够保存这个数据，那么就会启用磁盘的临时文件来进行排序。
SHOW variables LIKE '%max_length_for_sort_data%';
SHOW variables LIKE 'sort_buffer_size';在内存排序也分为2种方式：
- 全字段排序模式 需要查询的字段在
max_length_for_sort_data放得下，就拿出所有字段放到内存 ，排序后返回 - row_id 排序模式 需要查询的字段在
max_length_for_sort_data放不下，只拿主键ID与排序字段，排序后多一次回表（回主键索引拿其他字段） 然后返回
怎么判断是否orderby用到了索引？
- 如果输出 Extra 的列 EXPLAIN 不包含 Using filesort，则使用索引
- 如果输出 Extra 列 EXPLAIN 包含 Using filesort，则不使用索引
示例:
假设有 product_new 表, 并针对两字段建立联合索引（ product_type, product_price）为例
- 场景一：
where条件跟 orderby 字段的联合索引，并且满足最左匹配原则，where条件得先匹配最左边条件
explain select * FROM product_new
WHERE product_type = 6
ORDER BY product_price;结果
我们发现能走到索引，因为先查询product_type=6的数据，接下来 product_price 的数据都是有序的，所以orderby 的速度很快。
- 场景二：
order by 的字段跟 where 的字段不是一个索引，会使order by 索引失效
explain select * FROM product_new
WHERE product_count = 6
ORDER BY product_price;执行结果:
- 场景三：
order by 的字段跟where的字段不满足最左匹配原则，会使 order by 索引失效
explain select * FROM product_new
ORDER BY product_price;执行结果：
- 场景四：
order by 查询的字段别名跟 orderby 的字段一样
EXPLAIN SELECT ABS(product_price) as product_price FROM product_new
WHERE product_type=6
ORDER BY product_price;执行结果：
但是如果别名跟 orderby 不一样就会走索引
EXPLAIN SELECT ABS(product_price) as a FROM product_new
WHERE product_type=6
ORDER BY product_price;执行结果：
- 场景五：
order by 字段进行了函数运算
EXPLAIN SELECT * FROM product_new
WHERE product_type = 6
ORDER BY ABS(product_price);执行结果：
group by 优化
官网：https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
首先，我们来看下 group by 如果没有走到索引的实现流程
- 会将符合条件的数据扫描后，放到一个临时表，并且这个临时表是根据group by的字段排序好的
- 然后在临时表根据用户的聚合需求，比如是求count、sum,返回给用户相关结果
如下：
product_type 属于一个索引，product_count 属于另外一个索引。
会先通过 product_type 索引拿到结果，然后放入临时表中进行分组处理
EXPLAIN SELECT SUM(product_type), product_count
FROM product_new
WHERE product_type = 6
GROUP BY product_count;结果：Using temporary 代表用到了临时表。
但是如果group by写得够好，那么就可以避免创建临时表的逻辑，让直接通过索引来去group by。
防止 group by 去创建临时表，有2种场景
Loose Index Scan 松散索引
松散索引，在 GROUP BY 语句中使用的索引包含所有查询的列，可以直接使用索引中的数据来执行分组操作，从而避免了访问表数据的开销，因此查询速度较快。
无需扫描满足条件的所有索引键即可返回结果（比如Min函数，只需要找到最小的即可，其他的不需要扫描 ）
如果松散索引扫描适用于查询，则 EXPLAIN 输出显示 Using index for group-by 在 Extra 列中。
我们来先看个例子。
以product_new 表中建立联合索引（product_type, product_price）为例
EXPLAIN SELECT product_type, MIN(product_price)
FROM product_new
GROUP BY product_type;结果:
但是，如果执行以下，就不会用到松散索引，因为 product_count 不属于索引树中
EXPLAIN SELECT product_type, MIN(product_count)
FROM product_new
GROUP BY product_type;官网说明
满足以下条件就有可能走到松散索引扫描：
- 查询条件为range（范围查询）
- 查询在单个表，因为索引树是表级别的，不能跨表建立索引。
group by 用到的索引满足最左匹配原则
EXPLAIN SELECT product_price FROM product_new WHERE product_type>6 GROUP BY product_price; -- 不满足最左匹配- 选择列表中唯一使用的聚合函数（如果有的话）是MIN()和 MAX()，它们都引用同一列。该列必须在索引中，并且必须紧跟在 GROUP BY。
- 索引建立的字段必须是完整的列值，不能是前缀索引
官网实例
场景一：
假设t1(c1,c2,c3,c4)表上 有一个索引为idx(c1,c2,c3) 。松散索引扫描访问方法可用于以下查询：
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;场景二：
假设 t1(c1,c2,c3,c4)表上 有一个索引为idx(c1,c2,c3) 。松散索引扫描访问方法可用于以下查询：
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;以下不适合用到松散索引
MIN()除了or 之外还有聚合函数MAX()SELECT c1, SUM(c2) FROM t1 GROUP BY c1;子句中的列
GROUP BY不构成索引的最左边前缀：SELECT c1, c2 FROM t1 GROUP BY c2, c3;查询指的是该部分之后的键的一部分
GROUP BY，并且与常量不相等： 字段不在group By的字段中，但是需要selectSELECT c1, c3 FROM t1 GROUP BY c1, c2;
Tight Index Scan 紧密索引
假设 t1(c1,c2,c3,c4)表上 有一个索引idx(c1,c2,c3) 。以下查询不适用于前面描述的松散索引扫描访问方法，但仍适用于紧密索引扫描访问方法。（需要扫描满足条件的所有的key）
中存在间隙
GROUP BY，但被条件覆盖c2 = 'a'：SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;不
GROUP BY以密钥的第一部分开始，但有一个条件为该部分提供常量：SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
count 优化
count 流程（基于 innoDB 存储引擎）
count() 是一个聚合函数，对于返回的结果集 的一个统计，一行一行去判断，如果 count 括号里的不是 null，那么累计值+1，否则不加，最后返回一个累计的总数。
假如 表 product_new 总共数据 505857 条，但是有1条 product_img 为 null
SELECT count(*) FROM product_new;返回结果：
以上 count(*) 数量不变, 但是换成下面 使用 product_img 字段
SELECT count( product_img ) FROM product_new;返回结果:
发现数据少了一条
那么count 括号里的参数应该是id、还是字段、还是1 、还是* ?
其实大家平时大部分用的是 count(*) 跟 count(1) ，问题不会太大。1 是扫描到数据 扫描到了就固定返回一个1，肯定不为null，不会做null判断。
count(*) 是整条数据，也进行了优化，因为整条数据肯定不会为null。所以也不需要去判断;
count(id), 主键id，肯定不为null，也不会去判断null, 但是相对于count(1)来讲，要去解析ID.稍微慢点，但是也可以忽略不计。
继续，count(字段)，这个就有影响了
字段是否有索引
首先，如果字段没有索引，就需要进行全表扫描，explain 是 all
比如：
EXPLAIN SELECT count(product_img) FROM product_new;
字段是否可为空
如果字段不为 null，那么不需要进行 null 逻辑判断，如果可为空，则每条数据要进行非空判断
-- name 不为 null，速度要比下面的语句快很多
SELECT count(product_name) FROM product_new;
-- img 可为 null 速度要慢
SELECT count(product_img) FROM product_new;总结：count(1) ≈ count(*) > count(1) > count（字段） 字段是否有索引，是否是可为null，也会影响性能
Limit 优化
limit m，n ;其实去扫描m+n条数据，然后过滤掉前面的m条数据，当m越大，那么需要扫描的数据也就越多，性能也会越来越慢。
-- 很慢很慢
EXPLAIN SELECT * FROM product_new LIMIT 300000,10
-- 需要添加排序条件 就能走到id的索引
EXPLAIN SELECT * FROM product_new ORDER BY id LIMIT 300000,10针对这种情况，有以下几种方案可以进行一定的优化。
如果id是趋势递增的，那么每次查询都可以返回这次查询最大的ID，然后下次查询，加上大于 上次最大id的条件，这样会通过主键索引去扫描，并且扫描数量会少很多很多。因为只需要扫描where条件的数据
-- 根据id查询，并且使用where过滤 SELECT * FROM product_new WHERE id > 300396 ORDER BY id LIMIT 10先
limit出来主键ID，然后用主表跟查询出来的ID进行inner join内连接，这样，也能一定上提速，因为减少了回表，查询ID只需要走聚集索引就行。SELECT * FROM product_new INNER JOIN ( SELECT id FROM product_new ORDER BY id LIMIT 300000,10 ) a ON product_new.id=a.id- 当然，如果mysql级别优化不了了。我们也可以对分页数据进行缓存，比如Redis缓存，数据进行变动的时候，做好缓存依赖即可。
- 因为越往后，一般用户行为触及不到，比如你去看淘宝，不会去翻后面几百页的数据，所以，业务层面也可以做一些让步，比如不做后面几百页的数据。
Sql 优化实战（基于美团技术文章复现）
美团技术团队文章：https://tech.meituan.com/2014/06/30/mysql-index.html
Sql 分区 ( 没啥用 )
官网：https://dev.mysql.com/doc/refman/8.0/en/partitioning.html
分区暂时只支持InnoDB以及NDB存储引擎,他的目的其实是将大块的数据磁盘分为很小很小的磁盘文件，做到物理分割。
提供很多的分区策略，比如 rang范围、hash、list。
但是分区，是不同的索引树与磁盘文件，所以后面我们的锁这块都会有问题。
分库分表
纵向分库分表
不同的业务用不同的数据库、表
比如微服务，每个微服务的数据库地址、库、表不一样
横向分库分表
一个表的数据过大，导致查询操作过慢，需要进行拆分成多个表，主要就是分区思想
引入第三方组件
Redis
Elasticsearch
分布式搜索组件，所以 为什么 mycat 等分库分表的技术慢慢落后了。
因为这种分布式搜索的数据库越来越成熟，对于查询性能已经有极大的提升。