MySQL 数据量太大，日常卡死！想跑路了，java 三大框架面试题
[我总结了 72 份面试题，累计 3170 页，斩获了 30+互联网公司 offer（含 BATJM）](
)
[2020 首战告捷,这份 Java 面试神技 Plus 版，让我成功拿到了阿里、京东、字节跳动等大厂 offer](
)
[膜拜！阿里内部都在强推的 K8S(kubernetes)学习指南，不能再详细了](
)
[](
)问题概述
=======================================================================
使用阿里云 rds for MySQL 数据库（就是 MySQL5.6 版本），有个用户上网记录表 6 个月的数据量近 2000 万，保留最近一年的数据量达到 4000 万，查询速度极慢，日常卡死。严重影响业务。
问题前提：老系统，当时设计系统的人大概是大学没毕业，表设计和 sql 语句写的不仅仅是垃圾，简直无法直视。原开发人员都已离职，到我来维护，这就是传说中的维护不了就跑路，然后我就是掉坑的那个！！！
我尝试解决该问题，so，有个这个日志。
[](
)方案概述
=======================================================================
方案一：优化现有 mysql 数据库。
优点：不影响现有业务，源程序不需要修改代码，成本最低。
缺点：有优化瓶颈，数据量过亿就玩完了。
方案二：升级数据库类型，换一种 100%兼容 mysql 的数据库。
优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能。
缺点：多花钱
方案三：一步到位，大数据解决方案，更换 newsql/nosql 数据库。
优点：扩展性强，成本低，没有数据容量瓶颈
缺点：需要修改源程序代码
以上三种方案，按顺序使用即可，数据量在亿级别以下的没必要换 nosql，开发成本太高。三种方案我都试了一遍，而且都形成了落地解决方案。该过程心中慰问跑路的那几个开发者一万遍 ??
[](
)方案一详细说明：优化现有 mysql 数据库
=======================================================================================
跟阿里云数据库大佬电话沟通 and Google 解决方案 and 问群里大佬，总结如下（都是精华）：
1.数据库设计和表创建时就要考虑性能
2.sql 的编写需要注意优化
3.分区
4.分表
5.分库
1.数据库设计和表创建时就要考虑性能
mysql 数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。也就是说开发人员能力高，则 mysql 性能高。这也是很多关系型数据库的通病，所以公司的 dba 通常工资巨高。
设计表时要注意：
1.表字段避免 null 值出现，null 值很难查询优化且占用额外的索引空间，推荐默认数字 0 代替 null。
2.尽量使用 INT 而非 BIGINT，如果非负则加上 UNSIGNED（这样数值容量会扩大一倍），当然能使用 TINYINT、SMALLINT、MEDIUM_INT 更好。
3.使用枚举或整数代替字符串类型
4.尽量使用 TIMESTAMP 而非 DATETIME
5.单表不要有太多字段，建议在 20 以内
6.用整型来存 IP
索引
1.索引并不是越多越好，要根据查询有针对性的创建，考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 EXPLAIN 来查看是否用了索引还是全表扫描
2.应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描
3.值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
4.字符字段只建前缀索引
5.字符字段最好不要做主键
6.不用外键，由程序保证约束
7.尽量不用 UNIQUE，由程序保证约束
8.使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引
简言之就是使用合适的数据类型，选择合适的索引
选择合适的数据类型
（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int 类型存储时间类型，bigint 类型转 ip 函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用 enum、char 而不是 varchar
（4）尽可能使用 not null 定义字段
（5）尽量少用 text，非用不可最好分表 # 选择合适的索引列
（1）查询频繁的列，在 where，group by，order by，on 从句中出现的列
（2）where 条件中<，<=，=，>，>=，between，in，以及 like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count 越大，离散程度越高：
原开发人员已经跑路，该表早已建立，我无法修改，故：该措辞无法执行，放弃！
2.sql 的编写需要注意优化
1.使用 limit 对查询结果的记录进行限定
2.避免 select *，将需要查找的字段列出来
3.使用连接（join）来代替子查询
4.拆分大的 delete 或 insert 语句
5.可通过开启慢查询日志来找出较慢的 SQL
6.不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
7.sql 语句尽可能简单：一条 sql 只能在一个 cpu 运算；大语句拆小语句，减少锁时间；一条大 sql 可以堵死整个库
8.OR 改写成 IN：OR 的效率是 n 级别，IN 的效率是 log(n)级别，in 的个数建议控制在 200 以内
9.不用函数和触发器，在应用程序实现
10.避免 %xxx 式查询
11.少用 JOIN
12.使用同类型进行比较，比如用’123’和’123’比，123 和 123 比
13.尽量避免在 WHERE 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
1
4.对于连续数值，使用 BETWEEN 不用 IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
15.列表数据不要拿全表，要使用 LIMIT 来分页，每页数量也不要太大
“原开发人员已经跑路，程序已经完成上线，我无法修改 sql，故：该措辞无法执行，放弃！
[](
)引擎
=====================================================================
引擎
目前广泛使用的是 MyISAM 和 InnoDB 两种引擎：
MyISAM
MyISAM 引擎是 MySQL 5.1 及之前版本的默认引擎，它的特点是：
1.不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
2.不支持事务
3.不支持外键
4.不支持崩溃后的安全恢复
5.在表有读取查询的同时，支持往表中插入新纪录
6.支持 BLOB 和 TEXT 的前 500 个字符索引，支持全文索引
7.支持延迟更新索引，极大提升写入性能
8.对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
InnoDB
InnoDB 在 MySQL 5.5 后成为默认索引，它的特点是：
1.支持行锁，采用 MVCC 来支持高并发
2.支持事务
3.支持外键
4.支持崩溃后的安全恢复
5.不支持全文索引
还未添加个人签名 2021.03.18 加入
还未添加个人简介
评论