SQL 优化指南

上篇文章说到使用慢日志来获取查询慢的SQL ，然后我们针对查询慢的SQL可对其进行优化
常用的优化方式如下：

• 服务器硬件
• SQL本身优化
• 反范式设计优化
• 索引优化

服务器硬件优化就不说了，无非就是公司多花点钱，买牛逼的服务器，超大运行内存，及超大空间（反正我们公司没钱，用不了牛逼的服务器，只能多动动脑，来进行SQL本身的优化了）

SQL本身优化：
举个小例子
我创建3张表以供测试，随便创建的，只是为了演示

CREATE TABLE `t_user` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(20) DEFAULT NULL,
  `createDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8619 DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_password` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `userId` int(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8619 DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_address` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `userId` int(20) DEFAULT NULL,
  `province` varchar(30) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `area` varchar(30) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `createDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8619 DEFAULT CHARSET=utf8;

里面的数据都是我随便捏造的 一共造了8618条，还怕查询的效率太高了，显示不出来效果，然后写了条SQL 执行时间是 37秒，感觉还行

这里我使用了2个关联子查询 查询另外两张表中的两个字段现在执行时间是37秒多，然后我把关联子查询修改为关联查询

修改完以后只用了6秒 比之前快了31秒 ，感觉还是数据有点少，怕后面的操作效果会不明显了
所以，对于我们的SQL来讲，尽量减少使用子查询，如果说实现查询，用了好多个子查询的话，那就只能说明，数据库表设计的还是有问题

反范式设计优化：
先说下数据库的三大范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
1．第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

2．第二范式(确保表中的每列都和主键相关)
第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

3．第三范式(确保每列都和主键列直接相关,而不是间接相关)
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

以上是数据库的三大范式 ，反范式设计优化 就是说，我们在设计表的时候可以使用冗余字段，把用户密码和用户地址都给设计到用户表中，不做拆分，实际上就是拿空间换取时间

我又重新创建了一张表

CREATE TABLE `t_user_copy` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `userId` int(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=utf8;

将查询的结果集查询出来插入到新建的表中

insert INTO t_user_copy (userId,name,age,password,address)
SELECT
  u.id userId,
	u.NAME,
	u.age,
	p.`password`,
	a.address 
FROM
	t_user u,
	t_user_password p,
	t_user_address a 
WHERE
	p.userId = u.id 
	AND a.userId = u.id

单表的查询只用了 0.053秒，可以说非常快了，符合了我们的预期

索引优化
索引是什么呢？
索引是帮助MYSQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构
索引分类

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列

normal：表示普通索引
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
SPATIAL 空间索引 创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

基本语法
查看索引
SHOW INDEX FROM table_name
创建索引
CREATE [UNIQUE] INDEX indexName ON myTable（columname(length)）
ALTER TABLE 表名 ADD [UNIQUE] INDEX [indexName] ON （columname(length)）
删除索引
DROP INDEX [indexName] ON myTable

对t_user 表创建了个复合索引

设计表的时候就可以看到已经创建成功了

接下来就来是怎么验证我们的索引被使用了呢

这样就得用到 执行计划
什么是执行计划呢
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的分析你的查询语句或是表结构性能瓶颈
语法：Explain + SQL语句
如何知道我们的SQL使用了索引呢，以上为列进行测试

这2条SQL只有最下面那1条用到了索引，因为遵从最左原则，刚才建立索引的时候，最左边的是 name 所以只有name 被使用的情况下，才算是使用了索引，可以根据执行计划中的参数key_len进行判断
执行第一条 key_len是空，说明没有用到我们创建的索引 可以看到type 是ALL说明是进行了全表扫描 ，rows 查了8673条数据

执行第二条SQL 这时候的key_len 已经有值了，并且type 是ref级别了，rows 只查了1条数据 说明使用了索引

关于key_len的算法
类型 varchar(+2) char(+0)
字符编码 utf8 *3
本身长度 255
是否为空 是+1 否 +0

key_len=255*3+2+1=768

执行这个SQL也是会使用索引的 说明复合索引遵从最左原则， 只要使用了最左边的列，与顺序无关，都会使用到索引的

关于索引有几个点需要注意一下
1.尽量全值匹配
对索引中的所有列都指定具体的值 比如上面的SQL 对复合索引中的列都指定了具体的值
2.最佳左前缀法则
如果索引了多列，要遵循最左前缀的法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列
3.不要在索引列上进行任何操作
不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而且转向全表扫描
4.范围条件放最后（范围条件一般是有序的字段，从。。到。。）
存储引擎不能使用索引中范围条件右边的列
5.覆盖索引尽量使用
尽量使用覆盖索引（索引列和查询的结果集列一致）减少使用select *
6.不等于要慎用
mysql 中使用不等于(!= ,<>)的时候无法使用索引会导致全表扫描
7.null/not有影响
注意null/not对索引可能有影响，特别是null的情况下，很可能使索引失效的
8.like 查询要当心
like以通配符% 开头的情况下，会导致索引失效，全表扫描


这两种情况下都会造成索引失效
但是注意：这种情况下索引还是有效的

使用like 多数情况下都是会失效的，尽量不要使用like
9.字符类型加引号
字符串不加单引号索引会失效 主要是针对数字

以上是关于索引相关的，再回到我们上面哪个案例，正确的索引应该是加在两个属性表的userid上的


再来执行SQL

现在执行的时间是0.092秒了