Java技术栈总结：数据库MySQL篇

一、慢查询

1、常见情形

聚合查询

多表查询

表数据量过大查询

深度分页查询

2、定位慢查询

方案一、开源工具

调试工具：Arthas
运维工具：Prometheus、Skywalking

方案二、MySQL自带慢日志

在MySQL配置文件 /etc/my.conf 中配置：

# 开启MySQL慢日志开关
slow_query_log=ON 
# 设置慢日志时间2秒，超过2秒的SQL语句会被认为是慢查询，记录慢查询日志 
long_query_time=2 
# 慢日志记录文件 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

重启MySQL服务器，后续可在对应日志文件中查看慢日志信息。

3、慢SQL优化

聚合查询，考虑增加临时表
多表查询，优化SQL语句
表数据量过大，增加索引
深度分页查询，

其中，聚合查询、多表查询、数据量过大的情况，均可以使用SQL执行计划分析，进行优化。使用MySQL自带命令 explain 或 desc ：

EXPLAIN/DESC + 原SQL语句

字段含义

possible_key，当前SQL可能会使用到的索引；
key，当前SQL实际命中的索引；
key_len，索引"key"占用空间大小；
Extra，额外的优化建议；
- Using where；Using index：使用了索引，需要的数据在索引中都能够找到，不需要回表查询。
- Using index condition：使用了索引，但是需要回表查数据。
type，该SQL数据访问/操作的类型，性能从好到差依次为：NULL、system、const、eq_ref、ref、range、index、all。
- ALL，扫描全部数据，MySQL将遍历全表以找到匹配的行；
- index，遍历索引，索引树扫描；
- range，索引范围查找；
- ref，使用非唯一索引查找数据；
- eq_ref，类似ref，区别是使用的索引为唯一索引，对于每个索引的键值，表中只有一条记录匹配。
- const，根据主键查询；
- system，查询mysql自带的表；

Q：某条SQL查询很慢，如何分析？

A：可以使用MySQL自带分析工具EXPLAIN。

通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

二、MySQL存储引擎

1、分类

存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎基于表，而非基于数据库。

# 特性	MyISAM	InnoDB	MEMORY
事务	×	✔	×
锁机制	表锁	表锁、行锁	表锁
外键	×	✔	×

在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory

InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁
- DML操作遵循ACID模型，支持事务；
- 行级锁，提高并发性能；
- 支持外键，FOREIGN KEY 约束，保证数据的完整及正确性。
MyISAM是早期的引擎，不支持事务、只有表级锁、也没有外键，用的不多
Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

2、体系结构

三、索引

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1、B树

2、B+树

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

阶数更多，路径更短
磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
B+树便于扫库和区间查询，叶子节点是一个双向链表（叶子节点内部为单向链表）。

B树与B+树对比:

①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询

3、聚簇索引与非聚簇索引

聚簇索引，数据存储和索引在一块，索引结构的叶子节点保存了行数据。聚簇索引在每张表中都有且仅有一个。

非聚簇索引（二级索引），将数据与索引分开存储，叶子节点关联的内容为对应的主键。一张表可以有多个二级索引。

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一索引（UNIQUE）作为聚集索引。
如果表没有主键，且没有合适的唯一索引，则 InnoDB 会自动生成一个rowid作为隐藏的聚集索引。

回表查询：通过二级索引找到对应的主键，然后根据主键值通过聚簇索引找到对应的行数据，这个查找的过程称为回表。

4、覆盖索引

覆盖索引：指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
如果返回的列中没有创建索引，可能会触发回表查询，尽量避免使用 select *。

# 超大分页问题处理，

数据量较大的情况，使用 limit 分页查询，查询越靠后，查询效率越低。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引 + 子查询的形式进行优化。

select * from tb_sku t,
     (select id from tb_sku order by id limit 90000,10) a 
where t.id = a.id;

Q：超大分页怎么处理？

A：超大分页一般在数据量较大时，使用了limit分页查询，且需要对数据进行排序。这种情况下查询的效率就会比较低，可以采用覆盖索引和子查询解决。

首先，分页查询数据的主键id字段，然后用子查询来过滤，只需要查询这个id列表中的数据即可。因为查询id的时候走的是覆盖索引，所以效率会提升。

5、创建索引的原则

依次考虑：主键索引、唯一索引、复合索引（根据业务情况创建）；

原则：

数据量较大，且查询比较频繁的表创建索引；（例，>10万条）
针对查询条件 where 、排序 sort by 、分组 group by 等操作的字段创建索引；
选择区分度高的字段作为索引，优先创建唯一索引，检索效率高；
字符串类型的字段，如果长度较长，可以考虑创建前缀索引；（取字段的前一部分）
涉及多字段作为查询条件的情况，尽量使用联合索引，减少单列索引。联合索引很多时候可以覆盖索引，可以避免回表，节省存储空间；
需要控制索引的数量，索引数量越多，维护索引的代价也越大，同时影响增删改的效率；
如果某个字段不能存储NULL值，建表语句对应该字段应当使用 NOT NULL 约束。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

6、索引失效的情况

（1）判断索引是否失效

explain 命令，例：

假设我们在student表中有一个联合索引“idx_age_name_grade (name，age, grade)”

explain select  *  from student where name = "zhangsan" and age = 20 and grade = 3;
explain select  *  from student where name = "zhangsan" grade = 3;
explain select  *  from student where age = 20 and grade = 3;

可以通过对比上述语句的输出内容，key 、 key_len ...，查看索引的使用情况。

（2）失效的场景

使用聚合索引，违反最前左缀原则（如，上述student表，查询条件无name且包括age或grade的情况）；
联合索引，查询语句使用了范围查询，右边的列索引不会生效（如，select * from student where name = "zhangsan" and age > 20 and grade = 3；范围查询“age > 20” 右侧的 grade 字段索引不生效）；
在索引列上进行了运算，索引会失效；
字符串对应的字段查询语句不加引号，出现类型转换，引起失效；
like查询，以 % 开头的模糊查询（如果仅仅是以 % 结尾的 like 查询，索引不会失效）；
or 前后没有同时使用索引；
索引字段使用了 IS NULL 、IS NOT NULL、not、!= 等；
mysql评估全表扫描比使用索引查询快

注意：联合索引的情况，如果只是查询条件里的查询顺序和索引定义的顺序不同，不影响走索引。索引优化器会对查询条件进行优化，即进行重新排序。

7、SQL优化总述

涉及方面：

表的设计优化
- 选用合适的字段类型
SQL语句优化 && 索引优化：关联 “5、索引创建原则”、“6、索引失效情况”
- 尽量避免使用 select *，防回表
- Join优化：能用innerjoin 就不用left join right join，如必须使用一定要以小表为驱动。内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序。
主从复制、读写分离
分库分表