我们使用以下表作为来作为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

表的数据量：

验证mysql的密码 怎么验证mysql_验证mysql的密码


数据量尽可能的大，这样验证结果更直观准确，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'

执行时间：

验证mysql的密码 怎么验证mysql_验证mysql的密码_02

验证mysql的密码 怎么验证mysql_执行时间_03



正例：

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

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

执行时间：

验证mysql的密码 怎么验证mysql_数据库_04


验证mysql的密码 怎么验证mysql_java_05


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

反例：

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

执行时间：

验证mysql的密码 怎么验证mysql_mysql_06


正例：

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

执行时间：

验证mysql的密码 怎么验证mysql_验证mysql的密码_07


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

反例：

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

执行时间：

验证mysql的密码 怎么验证mysql_java_08


正例：

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

执行时间：

验证mysql的密码 怎么验证mysql_java_09


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

反例：

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

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

执行时间：

验证mysql的密码 怎么验证mysql_执行时间_10

验证mysql的密码 怎么验证mysql_mysql_11

正例：

/*表达式写在列的右边*/
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

执行时间：

验证mysql的密码 怎么验证mysql_mysql_12

验证mysql的密码 怎么验证mysql_数据库_13


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

反例：

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

执行时间：

验证mysql的密码 怎么验证mysql_执行时间_14

正例：

SELECT * FROM good WHERE good_code = '2020122700276'

执行时间：

验证mysql的密码 怎么验证mysql_数据库_15


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

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

组合索引比如（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 = '宠物生活'

执行时间：

验证mysql的密码 怎么验证mysql_java_16

验证mysql的密码 怎么验证mysql_数据库_17

验证mysql的密码 怎么验证mysql_java_18

正例：

/*(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 = '电玩'

执行时间：

验证mysql的密码 怎么验证mysql_mysql_19

验证mysql的密码 怎么验证mysql_mysql_20

验证mysql的密码 怎么验证mysql_mysql_21

验证mysql的密码 怎么验证mysql_验证mysql的密码_22


2. 其他查询优化

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

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

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

反例：

SELECT * FROM good WHERE cname1= '手机'

执行时间：

验证mysql的密码 怎么验证mysql_java_23


正例：

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

执行时间：

验证mysql的密码 怎么验证mysql_mysql_24

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

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

反例：

SELECT * FROM good WHERE brand_name= 'OPPO'

执行时间：

验证mysql的密码 怎么验证mysql_执行时间_25

正例：

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

执行时间：

验证mysql的密码 怎么验证mysql_mysql_26

  • 用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 = '电玩'

执行时间：

验证mysql的密码 怎么验证mysql_mysql_27


正例：

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

执行时间：

验证mysql的密码 怎么验证mysql_数据库_28


  • 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）'

执行时间：

验证mysql的密码 怎么验证mysql_数据库_29


正例：

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

执行时间：

验证mysql的密码 怎么验证mysql_执行时间_30

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

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

结构优化

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

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

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

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

  • 一张表索引列尽量不超过6个,多了降低增删改效率

一张索引不是越多越好，索引虽然会提高查询效率，但是更新数据时也会将索引更一遍，所以索引过多会导致更新数据效率低，一张表6个以内最佳

  • 删除冗余和重复索引

这种情况主要针对组合索引，上面说了组合索引比如（a,b,c）等于建立了a列索引，（a，b）和（a，b，c）索引，这样再建立a，（a，b）索引就重复了，这些索引就没必要再建立了

  • 若只含有数字的字段尽量设计为数字类型

数字类型不管比较还是排序效率都优于字符串，因此若只含有数字的字段尽量设计为数字类型，包括使用索引列的查询也是数字类型列的效率更优