一、背景

现在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;

SQL优化的几点建议_mysql

各列的含义如下:

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

 

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

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