开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySql语句优化总结以及解析验证，超详细！（一） -> 正文阅读

[大数据]MySql语句优化总结以及解析验证，超详细！（一）

我们使用以下表作为来作为sql优化的验证：

CREATE TABLE `good` (
  `good_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `good_code` varchar(30) NOT NULL COMMENT '商品编码',
  `title` varchar(100) DEFAULT NULL COMMENT '标题',
  `sku_title` varchar(256) DEFAULT NULL COMMENT '详细标题',
  `sub_title` varchar(256) DEFAULT NULL COMMENT '子标题',
  `cname1` varchar(30) DEFAULT NULL COMMENT '一级类目',
  `cname2` varchar(30) DEFAULT NULL COMMENT '二级类目',
  `cname3` varchar(30) DEFAULT NULL COMMENT '三级类目',
  `brand_name` varchar(50) DEFAULT NULL COMMENT '品牌名',
  `saleable` tinyint(1) DEFAULT NULL COMMENT '是否上架，0下架，1上架',
  `images` varchar(1024) DEFAULT NULL COMMENT '商品的图片',
  `price` bigint(10) DEFAULT NULL COMMENT '销售价格，单位为分',
  `indexes` varchar(50) DEFAULT NULL COMMENT '特有规格属性在spu属性模板中的对应下标组合',
  `own_spec` varchar(1024) DEFAULT NULL COMMENT 'sku的特有规格参数键值对，json格式，反序列化时请使用linkedHashMap，保证有序',
  `enable` tinyint(1) DEFAULT NULL COMMENT '是否有效，0无效，1有效',
  `create_time` datetime DEFAULT NULL COMMENT '新增时间',
  `last_update_time` datetime DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`good_id`),
  KEY `good_index_code` (`good_code`),
  KEY `good_index_cname` (`cname3`,`cname2`,`cname1`),
  KEY `good_index_brand` (`brand_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5428013 DEFAULT CHARSET=utf8mb4

这张表一共四个索引：
主键索引good_id，联合索引cname3,cname2,cname1，普通索引brand_name和good_code

表的数据量：
在这里插入图片描述
数据量尽可能的大，这样验证结果更直观准确，100-200w左右最佳，大了就就瓶颈了小了不够直观

查询优化

1. 索引优化

关于的sql的优化的时段会涉及到数据索引的相关知识，索引相关的优化的也是常用的优化手段，关于mysql的索引优化原理可以参考我的上一篇文件，这里我们主要讲如何使用索引进行优化。

所以利用好索引优化就是尽可能的使查询命中索引，不正当的sql写法会使索引失效，即未命中索引导致全表扫描

索引列上不要使用is null， is not null，not in，!= 等反向判断

反例：

SELECT * FROM `good` WHERE good_code NOT IN ('2020122700207', '2020122700209', '2020125960477', '2020125960515', '2020125960595')

SELECT * FROM `good` WHERE good_code != '2020125960357'

执行时间：
在这里插入图片描述

正例：

SELECT * FROM `good` WHERE good_code IN ('2020122700207', '2020122700209', '2020125960477', '2020125960515', '2020125960595')

SELECT * FROM `good` WHERE good_code = '2020125960357'

执行时间：在这里插入图片描述

索引列上不要使用or拼接非索引列

反例：

/*good_code上建立了索引，title上没有建立索引*/
SELECT * FROM `good` WHERE good_code = '2020122700196' OR title = '魅族 PRO'

执行时间：
在这里插入图片描述
正例：

/*good_code和brand_name上都建立了索引*/
SELECT * FROM `good` WHERE good_code = '2020122700196' OR brand_name = 'OPPO'

执行时间：
在这里插入图片描述

索引列上不使用左模糊或全模糊匹配

反例：

SELECT * FROM `good` WHERE brand_name LIKE '%华%'

执行时间：
在这里插入图片描述
正例：

SELECT * FROM `good` WHERE brand_name LIKE '华%'

执行时间：
在这里插入图片描述

索引列避免使用内置函数或使用表达式

反例：

SELECT * FROM `good` WHERE good_id / 10 = 1

SELECT * FROM `good` WHERE LENGTH(brand_name) = 3

执行时间：
在这里插入图片描述

正例：

/*表达式写在列的右边*/
SELECT * FROM `good` WHERE good_id = 1 * 10
/*使用其他方式查询或者不使用内置函数查询*/
SELECT * FROM `good` g 
JOIN (
      SELECT t.brand_name 
      FROM (SELECT DISTINCT brand_name FROM good) t 
	    WHERE LENGTH(t.brand_name) = 3
) tt ON g.brand_name = tt.brand_name

执行时间：
在这里插入图片描述

索引列避免隐式转换(即使使用正确的字段类型值,避免字符串类型传入数字,数字类型传入字符串,会有隐式转换)

反例：

/*good_code是字符串类型，判断条件要加引号，不加引号则会隐式转换导致索引失效*/
SELECT * FROM good WHERE good_code = 2020122700276

执行时间：
在这里插入图片描述

正例：

SELECT * FROM good WHERE good_code = '2020122700276'

执行时间：
在这里插入图片描述

使用联合索引遵循最左原则

所谓最左原则具体可以参考我的上一篇文章最后那部分，这里不再详细描述

组合索引比如（a,b,c）索引等于建立了a列索引，(a,b)索引，(a,b,c)列索引 ，所以当这几个列作为查询条件时候需要合理的使用
例表中我们建立了(cname3, cname2, cname1)组合索引，下面用它来列举一些使用不当以及合理运用的例子

反例：

/*(cname3,cname2,cname1)组合索引不包括cname1索引，因此没有命中索引*/
SELECT * FROM `good` WHERE cname1 = '电脑办公'

/*(cname3,cname2,cname1)组合索引不包括cname2索引，因此没有命中索引*/
SELECT * FROM `good` WHERE cname2 = '智能设备'

/*(cname3,cname2,cname1)组合索引不包括(cname2,cname1)索引，因此没有命中索引*/
SELECT * FROM `good` WHERE cname1 = '家居家装' AND  cname2 = '宠物生活'

执行时间：
在这里插入图片描述

正例：

/*(cname3,cname2,cname1)组合索引包括cname3索引，因此命中cname3索引*/
SELECT * FROM `good` WHERE cname3 = '智能手机'

/*(cname3,cname2,cname1)组合索引包括(cname3,cname2)索引，因此命中(cname3,cname2)索引索引*/
SELECT * FROM `good` WHERE cname2 = '生活' AND cname3 = "家庭与育儿"

/*(cname3,cname2,cname1)组合索引不包括(cname3,cname1)索引，但包括cname3索引，
因此命中了cname3索引*/
SELECT * FROM `good` WHERE cname1 = '数码' AND cname3 = "滤镜"

/*(cname3,cname2,cname1)组合索引包含的列可以不按组合的顺序，mysql优化器会识别出来，
因此这样也能命中(cname3,cname2,cname1)索引*/
SELECT * FROM `good` WHERE cname1 = '电脑办公' AND  cname2 = '外设产品' AND cname3 = '电玩'

执行时间：

在这里插入图片描述

2. 其他查询优化

索引的优化一般性能提升比较明显，除了索引的优化，也有其他的一些查询的优化，尽管提升不明显，但是养成写出良好的sql习惯对整体调优也会有一定的效果

查询返回具体字段尽量不用select *

《阿里开发手册》也明确规定了写sql不允许写select * ，主要是使用select * 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担，所以尽量不要使用select *写法

反例：

SELECT * FROM good WHERE cname1= '手机'

执行时间：
在这里插入图片描述
正例：

SELECT good_id,good_code,title,sku_title,sub_title,cname1,
       cname2,cname3,brand_name,saleable,images,price,`indexes`
FROM good WHERE cname1= '手机'

执行时间：
在这里插入图片描述

如果已知查询结果只需一条,建议用limit 1

使用limit 1只要匹配到1条数据就返回，不使用则会一直匹配只要匹配出所有符合要求的数据

反例：

SELECT * FROM good WHERE brand_name= 'OPPO'

执行时间：
在这里插入图片描述

正例：

SELECT * FROM good WHERE brand_name= 'OPPO' LIMIT 1

执行时间：
在这里插入图片描述

用left join,左表结果尽可能小

这是关于表关联查询常常会听到尽量使用小表驱动大表，即左表尽量为小表

distinct慎用,查询很多字段尽量不用distinct

distinct去重会先将去重的所有字段进行排序再比较去重，相当于order by + group by的操作，因此字段多比较效率低，整体效率也就比较低

尽量用union all替换union

union会对记录进行去重操作，union all不会，所以union all的效率自然比union快

反例：

SELECT * FROM `good` WHERE cname1 = '数码' AND cname3 = "滤镜"
UNION
SELECT * FROM `good` WHERE cname1 = '电脑办公' AND  cname2 = '外设产品' AND cname3 = '电玩'

执行时间：
在这里插入图片描述
正例：

SELECT * FROM `good` WHERE cname1 = '数码' AND cname3 = "滤镜"
UNION ALL
SELECT * FROM `good` WHERE cname1 = '电脑办公' AND  cname2 = '外设产品' AND cname3 = '电玩'

执行时间：
在这里插入图片描述

group by时建议在执行该语句前把不需要的记录过滤来提交效率

group by操作会先将需要分组的列的所有记录进行比较，如果分组后再筛选那些不需要的记录也会拿来作比较，这样整体效率就低了，分组前过来掉不需要的记录则需要比较的记录就少，效率自然相对就高

反例：

SELECT good_id,good_code,title,sku_title,sub_title,cname1,
       cname2,cname3,brand_name,saleable,images,price, COUNT(*)
FROM `good` 
GROUP BY good_code
HAVING cname3 = '智能手机' OR brand_name = '小米（MI）'

执行时间：
在这里插入图片描述
正例：

SELECT good_id,good_code,title,sku_title,sub_title,cname1,
       cname2,cname3,brand_name,saleable,images,price, COUNT(*)
FROM `good` 
WHERE cname3 = '智能手机' OR brand_name = '小米（MI）'
GROUP BY good_code

执行时间：
在这里插入图片描述

大批量的增删改操作使用分批进行

当有大批量的数据需要操作时，使用分批进行，因为大批量的数据操作会占用大量的内存，可能会导致cpu占用过高或内存溢出等问题导致效率低下甚至机器崩溃

结构优化

在经常作为查询条件的列上建立索引

索引不是随便可以建立的，索引列只有在所为判断条件才能发挥其作用，所以索引应该建立在经常作为判断条件的列上，没有经常作为判断条件的列的索引发挥不了作用自然没必要建立

索引应建立在重复度低的列上

索引也不是任何列都适合建立，比如像大文本text类型以及例表中的saleable，enable这种重复度高的枚举类型就不适合建立索引，大文本类型比较的效率很低下建立索引并不见得提高查询效率，重复度高地列生成的索引树会很简单，使用索引查询几乎等同于将全部记录扫描一遍，这时候数据库优化器就会放弃索引改用全表扫描