SQL優化的幾點建議

一、背景

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

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

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

二、几条原则

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

 

三、辅助工具

3.1 explain

建表语句用户信息表

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的性能。

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季》也是一套超不错的视频，有讲读写分离、分库分表等方面的知识。

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

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