一、背景
现在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季》也是一套超不错的视频，有讲读写分离、分库分表等方面的知识。
有时间还是应该系统掌握一些数据库设计的指导原则，以及优化的指导原则，避免慢查询。
不仅是为了面试，更是为了更好地写好业务代码，提高技术。
