查看执行频率（看哪一类操作较多）
show global status like 'Com_______';
慢查询日志是否支持，以及日志文件的位置
show variables like 'slow_query%';
show profiles; 在SQL优化时帮助我们了解时间消耗在哪里
查看是否支持profile
select @@have_profiling;
查看是否开启profile
select @@profiling;
#开启profile
set profiling =1;
select * from message;
select count(*) from message;
select * from message where status=1;
查看SQL的耗时情况
show profiles;
#查看某个Query_ID的SQL各个阶段的执行时间
show profile for query 116;
查看各个阶段cpu的使用情况
show profile cpu for query 137;
explain执行计划
1.id
select 查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下；id不同，值越大，越先执行)
2.type
表示连接类型，性能由好到差的连接类型为
- null
不查询任何表 比如 select 'A' - system
一般是访问系统表 - const
一般是主键和唯一索引 - eq_ref
- ref
一般是非唯一性索引 - range
- index
用到了索引但遍历了整个索引树 - all
全表扫描
3.possible_key
可能应用在这张表上的索引(一个或者多个)
4.Key
实际使用的索引，如果为NULL 则没有使用索引
5.key_len
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度，在不损失精度性的前提下，长度越短越好
6.rows
MySQL认为必须要执行查询的行数，在Innodb引擎的表中，是一个估计值，可能并不总是准确的。
7.filtered
表示返回结果的行数占需读取行数的百分比，值越大越好
主键优化
以下几点均为在满足业务需求的情况下考虑
1. 满足业务需求的情况下，尽量降低主键的长度
- 较短的主键可以减少存储空间的消耗，从而减少数据库的 I/O 操作，提高查询和插入的性能。
- 索引结构的大小直接影响查询性能，较小的主键意味着索引可以更高效地存储和访问。
2. 插入数据时，尽量选择顺序插入，避免页分裂出现
- 数据库使用 B-tree 结构存储索引，当插入数据时，随机插入会导致
页分裂（详情可去搜索下），这不仅增加了 I/O 操作，也会影响性能。 - 顺序插入可以保持 B-tree 的平衡，减少页的分裂。
3. 尽量不使用 UUID 作为主键或其他自然主键如身份证号
- UUID 通常是 128 位的字符串，作为主键使用时，会导致索引变得庞大，影响查询和插入性能。
- 自然主键如身份证号的唯一性可能会受到业务变更的影响，同时其长度较长，导致存储和处理效率低下。
4. 业务操作时，避免对主键的修改
- 主键是唯一标识一条记录的字段，修改主键不仅会影响数据完整性，还可能导致外键关系失效、增加数据更新的复杂性。
- 对主键的修改会导致相关索引的重建，增加了 I/O 操作和性能损耗。
总结
以上优化原则旨在提高数据库的性能和可维护性。在设计主键时，考虑长度、插入顺序、选择合适的主键类型和避免主键修改可以有效提升数据库的整体效率。合理的主键设计是数据库性能优化的重要组成部分。
ORDER BY优化
1.Using filesort:通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer完成排序操作，所有不是通过索引直接返回排序结果的排序都是 fileSort排序
2.Using Index 通过有序索引扫描直接返回数据 不需要额外排序 效率高
1. 根据排序字段建立合适的索引，多字段排序时遵循最左前缀法则
解释：
- 为了优化
ORDER BY的性能，最佳实践是为排序字段创建索引。当需要对多个字段进行排序时，应确保索引的第一个字段是查询中最左侧的排序字段（即遵循最左前缀法则）。
示例：
假设有一个 如下索引，包含字段 age 和 phone(不指定排序方式默认为升序)
CREATE INDEX idx_age_phone ON orders (age, phone);
SELECT *
FROM orders
ORDER BY age;
执行计划
| key | extra |
|---|---|
| idx_age_phone | Using Index |
SELECT *
FROM orders
ORDER BY age,phone;
执行计划
| key | extra |
|---|---|
| idx_age_phone | Using Index |
SELECT *
FROM orders
ORDER BY phone，age;
执行计划
| key | extra |
|---|---|
| idx_age_phone | Using Index ; Using fileSort |
违背了最左前缀法则
2. 尽量使用覆盖索引
解释：
- 覆盖索引是指索引中包含了查询所需的所有列，这样数据库可以仅通过索引而不需要访问表数据，从而提高查询效率。
- 使用覆盖索引可以减少磁盘 I/O，因为只需要读取索引而不需要读取实际的数据行。
示例：
假设我们查询 orders 表，获取字段 order_id 和 order_date，如：
SELECT order_id, order_date FROM orders
ORDER BY customer_id asc, order_date asc;
优化建议：
创建覆盖索引，包括所需的所有字段：
CREATE INDEX idx_covering ON orders (customer_id, order_date);
3. 多字段排序，一个升序一个降序时注意联合索引的创建规则（ASC/DESC）
解释：
- 当进行多字段排序，并且字段的排序方向不一致（如一个升序，一个降序）时，联合索引的创建需要特别注意。
- 创建联合索引时，字段的排序方向（ASC/DESC）应与实际查询的
ORDER BY语句保持一致，以确保能有效利用索引。
示例：
假设有一个 如下索引，包含字段 age 和 phone(不指定排序方式默认为升序)
CREATE INDEX idx_age_phone ON orders (age , phone);
SELECT *
FROM orders
ORDER BY age,phone;
执行计划
| key | extra |
|---|---|
| idx_age_phone | Using Index |
SELECT *
FROM orders
ORDER BY age desc,phone desc;
执行计划
| key | extra |
|---|---|
| idx_age_phone | Backward index scan(反向扫描索引),Using Index |
SELECT *
FROM orders
ORDER BY age asc,phone desc;
执行计划
| key | extra |
|---|---|
| idx_age_phone | Using Index ; Using fileSort |
优化
CREATE INDEX idx_age_phone ON orders (age , phone desc);
4. 不可避免fileSort时
不可避免fileSort 且有大量数据排序时，适当增加排序缓冲区大小（ sort_buffer_size 默认未256K ）
GROUP BY优化
1.可以使用索引提高效率 但仍需满足最左前缀法则
示例：
假设有一个 如下索引，包含字段 profession ,age, status(不指定排序方式默认为升序)
create index idx_prf_age_status on tb_user(profession,age,status)
select prfession ,count(*) form tb_user group by profession
执行计划
| key | extra |
|---|---|
| idx_prf_age_status | Using Index |
select age,count(*) form tb_user group by age
执行计划
| key | extra |
|---|---|
| idx_prf_age_status | Using Index ;Using temporary |
select prfession ,age,count(*) form tb_user group by prfession , age
执行计划
| key | extra |
|---|---|
| idx_prf_age_status | Using Index |
select ,age,count(*) form tb_user where prfession ='软件工程' group by prfession , age
执行计划
| key | extra |
|---|---|
| idx_prf_age_status | Using Index |
limit优化
- 童话覆盖索引+子查询方式进行优化
原SQL
select * from tb_sku limit 9000000,10
优化后SQL
select s.* from tb_sku s,(select id from tb_sku order by id limit 9000000,10) a where s.id=a.id
错误SQL
select s.* from tb_sku where id in (select id from tb_sku order by id limit 9000000,10 )
# 此SQL 会出现 This version of MySQL doesn't yer support 'limit IN/ALL/ANY/SOME subquery'
count优化
暂无较好优化方案，但有以下几点可以注意
count 是一个聚合函数 对于返回的结果集 一行一行的判断 如何 count 的参数不是 null 则累计+1 如果为null 则不加 ，最后返回累计值
1.count(*)
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务蹭饭直接按行进行累加
2.count(主键)
InnoDB引擎会遍历整张表，把每一行的id都取出来，返回给服务层，服务层拿到数据后，直接按行进行累加 （主键不可能为null）
3.count(字段)
无 not null 约束 InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层拿到数据后，根据是否为null 进行计数
有 not null 约束 InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加
4.count(1)
.
InnoDB引擎会遍历整张表，但不取值，服务层对于返回的每一行 放一个数字 1 进去 直接按行进行累加
update优化
InnoDB的行锁时针对索引加的锁，不是针对记录家的锁，并且该索引不能失效，负责会从行锁升级为表锁·
where 后面的条件如果不是索引 则词条修改语句过程中锁为表锁，如果该字段是索引 则锁为行锁
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
