MySQL优化

最新推荐文章于 2025-06-24 11:23:04 发布

不做大哥好多年xw

最新推荐文章于 2025-06-24 11:23:04 发布

阅读量555

点赞数 9

CC 4.0 BY-SA版权

文章标签： mysql android 数据库

本文链接：https://blog.youkuaiyun.com/xnqwq1314/article/details/146906442

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、多列索引和索引顺序

在多数情况下，在多个列上建立独立的索引并不能提高查询性能
理由非常简单，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

当出现多个索引多个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);

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' 就是固定值

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(*)更快

2、优化关联查询

嵌套循环关联
- 在MySQL中，执行关联查询的默认策略是嵌套循环关联
- 这种策略的基本思想是，先从一个表（外层循环）中取出一行，然后在另一个表（内层循环）中寻找匹配的行
- 这个过程会反复进行，直到外层循环的所有行都被处理完毕
以下面关联查询为例（如果没有创建索引）
- MySQL会先从A表中取出一行，然后在B表中进行全表扫描，寻找zz列值与A表中当前行的zz列值相等的所有行
- 这样，对于A表中的每一行，MySQL都需要在B表中进行一次全表扫描，这将导致查询效率非常低

SELECT A.xx, B.yy 
FROM A INNER JOIN B ON A.zz = B.zz
WHERE A.xx IN (5,6)

如果创建如下索引

CREATE INDEX idx_a_xx_zz ON A(xx, zz);
CREATE INDEX idx_b_zz ON B(zz);

在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;

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'

6、使用UNION ALL

在明显不会有重复值时使用 UNION ALL 而不是 UNION
如果使用 UNION，会在表链接后筛选掉重复的记录行
如果使用 UNION ALL，不会合并重复的记录行

7、拆分复杂的大 SQL 为多个小 SQL

大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL
MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
SQL 拆分后可以通过并行执行来提高处理效率

8、禁止跨库查询

程序连接不同的数据库使用不同的账号，禁止跨库查询
为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险

02.简单说明

MySQL一主多从，读写分离：写主库，读从库（所有数据库的数据一样）
- 数据一样的，那么当数据量太大的时候查询还是很慢
分库（根据用户id分库）
- 所有数据库的表结构一样，存储的数据完全不一样
- 真实环境以用户id进行分库，每一个库的数据都很小，查询起来就快了
- 无法解决问题：当一个数据库中表中量过大的时候，查询依然会慢
分表（根据时间分表）
- 当一个表中数据过大的时候，我们必须要对表拆分
- 购物清单表中有两千万数据
  - 最近半年的购物数据时一百万
  - 半年到一年的数据有五百万
  - 一年以前的数据有一千万