SQL优化的几点建议

明明如月学长

发布于 2021-08-31 15:36:35

6400

发布于 2021-08-31 15:36:35

文章被收录于专栏：明明如月的技术专栏

一、背景

现在MySQL已经成为Java Web开发的标配。

面试也经常问到“MySQL索引的数据结构是什么？”、“MySQL慢查询怎么看？”、“聚簇索引和非聚簇索引的区别是什么？”，“怎么SQL优化”等等。

本文就“怎么SQL优化”，简单讲几个原则。

二、几条原则

对经常搜索、排序、分组列建索引
不重复的值，基数越大，效果越好
索引的数据类型尽可能的短
最左前缀原则
不要建立过多的索引
大量的insert考虑批量插入
like不要在初始位置使用通配符

三、辅助工具

3.1 explain

此章节参考自： https://segmentfault.com/a/1190000008131735

建表语句用户信息表

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4


INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);

订单信息表

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

explain语法很简单，explain +sql语句，如

 explain select * from user_info where id = 1;

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

主要根据select_type，possible_keys， key等来判断sql的性能。

详情参考：https://segmentfault.com/a/1190000008131735

3.2 SQL优化工具

https://github.com/Meituan-Dianping/SQLAdvisor

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议

四、索引应用的一些原则

4.1 索引越少越好

索引是把双刃剑，在提升检索性能的同时，以牺牲数据写性能和增加系统负载为代价的。

4.2 索引列越少越好

有些不懂sql优化，干脆把所有的where条件都加上索引，不但增加了索引的数量，还会出现很多大的复合索引。

很多时候合理的单列索

4.3 尽量少用函数索引

4.4 选择正确的索引类型

4.5 为复合索引选择正确的列顺序

如果必须建立一个包含多列的复合索引时，尽量将使用频繁且选择性好的列排列在前面。

4.6 为分区表选择正确的索引类型

五、索引应用的认识误区

5.1 只有走索引才是最优

要看具体场景

5.2 索引有益无害

索引是把双刃剑，在提升检索性能的同时，以牺牲数据写性能和增加系统负载为代价的。

5.3 索引肯定比表小

不一定，现实中，索引和表差不多一样大，有时候甚至比表还大。

5.4 索引输出的数据都有序

不一定，FFS操作输出的数据就是无序的。

5.5 索引高度会极大影响性能

理论上，索引高度会影响索引的检索速度，现实中，非高频、高并发、大数据检索，一般对性能的影响还不是很明显。

5.6 位图索引很小且很快

当位图索引列的基数较高是，位图索引就会变得很庞大。

摘录自：《高性能SQL》

六、感受

面试中MySQL是一个重点，极客时间《MySQL45讲》讲得比较全面和系统，掌握好了，MySQL面试这一块绰绰有余，甚至可以手撕面试官。

另外石杉老师的《Java工程师面试突击第1季》也是一套超不错的视频，有讲读写分离、分库分表等方面的知识。

有时间还是应该系统掌握一些数据库设计的指导原则，以及优化的指导原则，避免慢查询。

不仅是为了面试，更是为了更好地写好业务代码，提高技术。

五、其他参考

《Mysql高性能优化规范建议》

《高性能SQL》

https://www.cnblogs.com/huchong/p/10219318.html

如果觉得本文对你有帮助，欢迎点赞，欢迎关注我，如果有补充欢迎评论交流，我将努力创作更多更好的文章。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019/05/31 ，如有侵权请联系 cloudcommunity@tencent.com 删除

https

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度