06.MySQL优化
# 01.MySQL优化方向
# 0、查询语句执行顺序
# 1、数据类型优化
1）NOT NULL 设置
- 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助
- 只是如果计划在列上创建索引，就应该将该列设置为NOT NULL
2）INT(11) 长度无用
- INT使用32位（4个字节）存储空间，那么它的表示范围已经确定
- 所以INT(1)和INT(20)对于存储和计算是相同的
3）UNSIGNED上限提高一倍
- UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍
- 比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255
4）DECIMAL 避免使用
- 通常来讲，没有太大的必要使用DECIMAL数据类型
- 即使是在需要存储财务数据时，仍然可以使用BIGINT
- 比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储
- 这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题
5）TIMESTAMP 使用4个字节存储
- TIMESTAMP使用4个字节存储空间，因而TIMESTAMP只能表示1970 - 2038年
6）schema的列不要太多
- 原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据
- 然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的
- 如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高
7）大表ALTER TABLE非常耗时
- MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表
- 从旧表中查出所有的数据插入新表，然后再删除旧表
- 尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久
8）建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
- TEXT 或 BLOB 类型只能使用前缀索引
9）避免使用 ENUM 类型
- 修改 ENUM 值需要使用 ALTER 语句
- ENUM 类型的 ORDER BY 操作效率低，需要额外操作
- ENUM 数据类型存在一些限制比如建议不要使用数值作为 ENUM 的枚举值
# 2、创建高性能索引
- 索引是提高MySQL查询性能的一个重要途径
- 但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能
- ① 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
- ② 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
- 并不要将符合① 和②中的字段的列都建立一个索引， 通常将 ① ②中的字段建立联合索引效果更好
# 1、前缀索引
- 如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率
CREATE INDEX idx_name ON user (name(20));
-- name VARCHAR(255)类型的列，如果对整个列进行索引，索引的空间会很大
-- 这样就可以创建一个只对“name”列的前20个字符进行索引的索引，从而提高索引效率，同时节约索引空间
2
3
# 2、多列索引和索引顺序
- 在多数情况下，在多个列上建立独立的索引并不能提高查询性能
- 理由非常简单，MySQL不知道选择哪个索引的查询效率更好
- 所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略
- 举个简单的例子，在一张电影演员表中，在actor_id和film_id两个列上都建立了独立的索引
select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1
- 老版本的MySQL会随机选择一个索引，但新版本做如下的优化
select film_id,actor_id from film_actor where actor_id = 1
union all
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1
2
3
当出现多个
索引多个AND条件，一个包含所有相关列的索引要优于多个独立索引。当出现多个
索引多个OR条件，对结果集的合并、排序等操作需要耗费大量的CPU和内存资源- 特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高
- 所以这种情况下
还不如走全表扫描
因此explain时如果发现有索引合并（Extra字段出现Using union），应该好好检查一下查询
如果查询和表都没有问题，那只能说明索引建的非常糟糕
-- 联合索引最优
SELECT * FROM payment where staff_id = 2 and customer_id = 584
-- 下面查询语句这样创建索引最优
select user_id from trade where user_group_id = 1 and trade_amount > 0
CREATE INDEX idx_trade_user_group_amount ON trade(user_group_id, trade_amount, user_id);
2
3
4
5
6
# 3、覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引
覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处
索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
# 4、使用索引扫描来排序
MySQL有两种方式可以生产有序的结果集
- 其一是对
结果集进行排序的操作 - 其二是按
照索引顺序扫描得出的结果自然是有序的
- 其一是对
如果explain的结果中
type列的值为index表示使用了索引扫描来做排序扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录
但如果
索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行这个读取操作基本上
是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢只有当
索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序如果查询需要
关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求
- 有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例
- 其他情况下都需要执行排序操作，而无法利用索引排序
-- 最左列为常数，索引：(date,staff_id,customer_id)
select staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id
-- 这里最左列索引 date = '2015-06-01' 就是固定值
2
3
# 5、冗余和重复索引
冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除
比如有一个索引(A,B)，再创建索引(A)就是冗余索引
冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)
大多数情况下都应该尽量扩展已有的索引而不是创建新索引
但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询
# 3、特定类型查询优化
# 1、count()函数
查询速度：
COUNT(1)>COUNT(*)>COUNT(列)
COUNT(*)：统计表中所有行的总数，包括NULL值COUNT(列)：统计指定列中非NULL值的行数``，列中存在NULL值不会进行计数COUNT(1)：统计所有行的总数，包括NULL值，这种方式与COUNT(*)相同- 都是统计所有行的总数，但是它使用的是常量1，而不是通配符，在某些情况下可能会比
COUNT(*)更快
- 都是统计所有行的总数，但是它使用的是常量1，而不是通配符，在某些情况下可能会比
# 2、优化关联查询
嵌套循环关联在MySQL中，执行关联查询的默认策略是
嵌套循环关联这种策略的基本思想是，先
从一个表（外层循环）中取出一行，然后在另一个表（内层循环）中寻找匹配的行这个过程会
反复进行，直到外层循环的所有行都被处理完毕
以下面关联查询为例（
如果没有创建索引）- MySQL会先
从A表中取出一行，然后在B表中进行全表扫描，寻找zz列值与A表中当前行的zz列值相等的所有行 - 这样，对于
A表中的每一行，MySQL都需要在B表中进行一次全表扫描，这将导致查询效率非常低
- MySQL会先
SELECT A.xx, B.yy
FROM A INNER JOIN B ON A.zz = B.zz
WHERE A.xx IN (5,6)
2
3
- 如果创建如下索引
CREATE INDEX idx_a_xx_zz ON A(xx, zz);
CREATE INDEX idx_b_zz ON B(zz);
2
在A表的xx和zz列上的复合索引
- MySQL快速找到xx列值为5和6的行，避免了全表扫描
- 由于这个索引同时包含了zz列，MySQL可以直接通过索引找到A表中需要参与JOIN操作的行，而不需要再次扫描A表
在B表的zz列上的索引
- 可以让MySQL在进行JOIN操作时，快速找到zz列值与A表中当前行的zz列值相等的所有行，避免了全表扫描
- 这样，对于A表中的每一行，MySQL只需要在B表的索引中进行查找，而不需要在B表中进行全表扫描
通过这样的索引优化，可以大大减少查询所需的I/O操作和CPU计算，从而显著提高查询效率
# 3、优化LIMIT分页
1）索引优化：根据查询条件创建合适的索引，可以大大提高查询效率。如果排序字段是索引的一部分，查询会更快
2）记录上一次查询的位置
- 这种方法适合数据量大，但是每页数据量相对较小的场景
- 如果你知道上一次查询的最后一条记录的ID，下一次查询时，就可以直接从这个ID之后开始查询，而不是从头开始
- 例如：
SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10
3）尽量减少偏移量
- 如果你知道你要查询的数据在表中的大致位置，可以尽量减少LIMIT的偏移量，这样可以减少MySQL需要扫描和丢弃的行数
4）使用分区
- 如果数据量非常大，可以考虑使用分区
- 通过分区，可以将数据划分到不同的物理区域，从而减少查询的数据量
5）使用覆盖索引
- 如果查询的列都包含在一个索引中，MySQL可以直接通过索引返回结果，而不需要查询实际的行数据
# 4、SQL 规范
# 1、计算不要放到sql 中
- 尽量不在数据库做运算，复杂运算需移到业务应用里完成
- 这样可以避免数据库的负担过重，影响数据库的性能和稳定性
- 数据库的主要作用是存储和管理数据，而不是处理数据
# 2、避免使用子查询
避免使用子查询，可以把子查询优化为 join 操作
通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时
才可以把子查询转化为关联查询进行优化
子查询性能差的原因：
- 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中
- 不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
- 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询
-- 子查询
SELECT c.customer_name,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;
-- join 查询优化
SELECT c.customer_name, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
2
3
4
5
6
7
8
9
10
# 3、避免过多表关联查询
对于 MySQL 来说，是
存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置在 MySQL 中，对于同一个 SQL
多关联（join）一个表，就会多分配一个关联缓存如果在一个 SQL 中
关联的表越多，所占用的内存也就越大如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下
容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性
MySQL 最多允许关联 61 个表，建议不超过 5 个
# 4、in 代替 or
对应同一列进行 or 判断时，使用 in 代替 or
in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引
# 5、WHERE禁止函数转换
- 对列进行函数转换或计算时会导致无法使用索引
-- 不推荐
where date(create_time)='20190101'
-- 推荐
where create_time >= '20190101' and create_time < '20190102'
2
3
4
# 6、使用UNION ALL
- 在明显不会有重复值时使用 UNION ALL 而不是 UNION
- 如果使用 UNION，会在表链接后筛选掉重复的记录行
- 如果使用 UNION ALL，不会合并重复的记录行
# 7、拆分复杂的大 SQL 为多个小 SQL
- 大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL
- MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
- SQL 拆分后可以通过并行执行来提高处理效率
# 8、禁止跨库查询
程序连接不同的数据库使用不同的账号，禁止跨库查询
为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险
# 02.简单说明
- MySQL一主多从，读写分离：写主库，读从库（所有数据库的数据一样）
- 数据一样的，那么当数据量太大的时候查询还是很慢
- 分库（根据用户id分库）
- 所有数据库的表结构一样，存储的数据完全不一样
- 真实环境以用户id进行分库，每一个库的数据都很小，查询起来就快了
- 无法解决问题：当一个数据库中表中量过大的时候，查询依然会慢
- 分表（根据时间分表）
- 当一个表中数据过大的时候，我们必须要对表拆分
- 购物清单表中有两千万数据
- 最近半年的购物数据时 一百万
- 半年到一年的数据有五百万
- 一年以前的数据有一千万