数据库十大经典常见经典问题

一 索引
1）索引之无索引案例
问题描述：
用户系统打开缓慢，数据库CPU 100%
问题排查：
发现数据库中大量的慢SQL，执行时间超过了 2 s
慢SQL：
select id from 'user' where user_no=13772556391 limit 0,1;
执行计划：

mysql>explain select id from 'user' where user_no=13772556391 limit 0,1;
1

关键信息：
type：ALL
key:null
说明是全表扫描

执行时间：

mysql> select id from 'user' where user_no=13772556391 limit 0,1
1

表结构

查看表结构
所查找的user 列是没有ID
验证字段的过滤性

mysql> select count(*) from user where user_no=13772556391;
1

解决：添加索引

mysql> alter table user add index ind_user_no(user_no);
1
再次查看：执行时间

mysql> select id from 'user' where user_no=13772556391 limit 0,1
1

再次查看：执行计划

mysql>explain select id from 'user' where user_no=13772556391 limit 0,1\G;
1

2）索引之隐式转换案例：
为什么索引的过滤性这么差？

表结构

由于查询条件user_no=13772556391是没有加引号，是整型，而表结构是字符型，所以涉及到类型转换

改进查询条件:
添加引号，可以看到rows:1

索引问题的最佳实践
1 通过explain查看sql的执行计划
判断是否使用到了索引以及隐式转换
2 常见的隐式转换
包括字段数据类型以及字符集定义不当导致
3 设计开发阶段
避免数据库字段定义与应用程序参数定义出现不一致
不支持函数索引，避免在查询条件加入函数：date(a.gmt_create)
4 SQL审核
所有上线的SQL都要经过严格的审核，创建合适的索引

二 SQL优化
1）SQL优化之分页优化
普通写法：

select * from buyer where sellerid=100 limit 100000, 5000
1
普通limit M,N 的翻页写法，在越往后翻页的过程中，速度越慢，原因mysql会读取表中的前M+N条数据，M越大，性能就越差

优化写法：

select t1.* from buyer t1,
       (select id from buyer where sellerid = 100 limit 100000,5000) t2
where  t1.id = t2.id
1
2
3
**注意：**需要在t表的sellerid字段上创建索引，id为表的主键

create index ind_sellerid on buyer(sellerid);
1
原始语句与优化后语句的查询时间对比

2）SQL优化之子查询优化
典型子查询

select first_name
from employees
where emp_no in
(select emp_no from salaries_2000 
where salary = 5000);
1
2
3
4
5
MySQL的处理逻辑是遍历employees表中的每一条记录，代入到子查询中去

改写子查询

select first_name
from employees emp,
(select emp_no from salaries_2000 
where salary = 5000) sal
where emp.emp_no = sal.emp_no;
1
2
3
4
5
SQL优化最佳实践
1 分页优化
采用高效的Limit写法，避免分页查询给数据库带来性能影响
2 子查询优化
子查询在5.1，5.5版本中都存在较大的风险，将子查询改为关联
使用MySQL5.6的版本，可以避免麻烦的子查询改写
3 查询需要的字段
避免用select * 查询所有字段数据，只查询需要的字段数据

三 锁
1）索之表级锁
Innodb 与 Myisam

目前比较推荐Innodb，较高版本也将其设置 为默认引擎

典型案例
创建表，引擎为Myisam

create table 't_myisam'(
'id' int(11) default null
)engine=myisam default charset=utf8;
1
2
3
查询堵塞更新

select id,sleep(100) from t_myisam
1

解决：更改引擎

alter table t_myisam engine = innodb;
1
2）锁之Metadata lock
DDL操作

alter table t add column gmt_create datetime
1
数据库连接状态：

select id , sleep(50) from t //在操作下面DDL语句之前，先执行查询状态的语句
alter table t add column gmt_create datetime
1
2

可以看到出现：Waiting for table metadata lock,导致后面所有操作都出现堵塞，因为DDL语句会破坏元数据结构，导致拿不到锁。
Tips：DDL过程中注意数据库中大长事务，大查询

锁问题最佳实践
1 设计开发阶段
1 避免使用myisam存储引擎，改用Innodb引擎
2 避免大事务，长事务导致事务在数据库中的运行时间加长
3 选择升级到MySQL5.6版本，支持online ddl
2 管理运维阶段
1 在业务低峰期执行上述操作，比如创建索引，添加字段；
2 在结构变更前，观察数据库中是否存在长SQL，大事务；
3 结构变更期间，监控数据库的线程状态是否存在lock wait;
4 阿里云ApsaraDB支持在DDL变更中加入 wait timeout；

四 延迟
1）只读实例架构
数据库需要升级到5.6版本
最多支持10个节点
采用MySQL复制原生实现数据同步

2）DDL导致延迟
常见DDL：
create index,repair,optimze table,alter table add column
大事务：
create … as select ,insert …select,load …data,delete…from,update…from

3）MDL锁导致延迟
Tips：
通过执行show processlist 查看连接的状态；
锁会阻塞复制线程导致复制延迟；

4）资源问题导致延迟
Tips：
压力：同步压力+只读业务压力
效率：CPU+IOPS资源

延迟问题最佳实践
1 排查思路
1 资源是否达到瓶颈
2 线程状态是否有锁
3 判断是否存在大事务
2 最佳实践
1 使用Innodb存储引擎
2 只读实例的规格不低于主实例
3 大事务拆分为小事务
4 DDL变更期间观察是否有大查询

五 参数优化
一个参数引发的问题
背景介绍：
将本地的业务系统迁移上云
在rds上运行时间明显要比线下自建数据库运行时间慢1倍
导致系统割接延期的风险
关键词：
上云，RDS，自建，慢1倍
经验分析：
1 数据库跨平台迁移（PG->MySQL，Oracle->MySQL）
2 跨版本升级（MySQL：5.1->5.5，5.5->5.6）
3 执行计划，优化器，参数配置，硬件配置
确定优化器版本：用户5.6，RDS的版本5.6

确定SQL执行计划：
rows=3990011140285111111*1

确定参数配置
1 用户配置：

join_buffer_size = 128M
read_rnd_buffer_size = 128M
tmp_table_size = 128M
1
2
3
2 RDS配置：

join_buffer_size = 1M
read_buffer_size = 1M
tmp_table_size = 256K
1
2
3
验证阶段：
tmp_table_size由256K调整至128MB

将临时表空间从256K调整至128Mb，时间从18.17降到7.29

参数最佳实践
1 排查思路：
1 查看SQL执行计划
2 查看数据库版本和优化器规则是否一样
3 对比参数设置
4 对比硬件配置
2 最佳实践
1 Query_cache_size
2 Temp_table_size
3 Back_log

六 CPU 100%
三大因素：慢SQL、锁、资源

CPU 100%最佳实践：
1 慢SQL问题
通过优化索引，子查询，隐式转换，分页改写等优化
2 锁等待问题
通过设计开发和管理运维优化锁等待
3 资源问题
通过参数优化，弹性升级，读写分离，数据库拆分等方式优化

七 连接数，conn 100%
三大因素：慢SQL、锁、配置

Conn 100%最佳实践
1 慢SQL问题
通过优化索引，子查询，隐式转换，分页改写等优化
2 锁等待问题
通过设计开发和管理运维优化锁等待
3 配置问题
客户端连接池参数配置超出实例最大连接数；
弹性升级RDS的规格配置；

八 iops 100%
Iops 100%最佳实践
1 慢SQL问题
通过优化索引，子查询，隐式转换，分页改写等优化
2 DDL
create index，optimze table，alter table add column
3 配置问题
内存规格不足，弹性升级RDS的规格配置

九 disk 100%
磁盘空间组成：数据文件，日志文件，临时文件

空间最佳实践
1 数据空间问题
（原理：由于MySQL做了大量删除，是不会自动释放表空间，会重复循环利用）
采用optmize table收缩表空间；
删除不必要的索引
2 日志空间问题
减少大字段的使用；
使用truncate 替代delete from;
3 临时空间问题
适当调大sort_buffer_size;
创建合适索引避免排序；

十 mem 100%
内存组成：
Buffer pool size：缓存数据和索引
Dictionary memory：字典
Thread cost memory：连接和线程相关

内存最佳实践
1 Buffer pool size
创建合适的索引，避免大量的数据扫描；
去除不必要的索引，降低内存的消耗；
2 Dictionary memory
不要过度分表；
3 Thread cost memory
创建合适的索引避免排序；
只查询应用所需要的数据；
————————————————
版权声明：本文为CSDN博主「Lin_Chang_You」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44221613/article/details/104500167