# sql语句及索引优化


<!--more-->


案例: 设计1个表 tb_test

```mysql
CREATE TABLE tb_test (
	id INT PRIMARY KEY auto_increment,
	nickname VARCHAR ( 255 ),
	loginname VARCHAR ( 255 ),
	age INT,
	sex CHAR ( 1 ),
	STATUS INT,
address VARCHAR ( 255 ) 
);
```

向表中写入10000000条数据

```mysql
CREATE PROCEDURE test_insert () BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			i <= 10000000 DO insert INTO tb_test
		VALUES
			( NULL, concat( 'zy', i ), concat( 'zhaoyun', i ), 23, '1', 1, 'beijing' );
		SET i = i + 1;
	END WHILE;
COMMIT;
END;
```

```mysql
call test_insert(); #执行存储过程, 插入1千万条数据
```



## 1. explain查看索引使用情况

type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 

key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。

key_len列，索引长度。

rows列，扫描行数。该值是个预估值。

extra列，详细说明。注意，常见的不太友好的值，如下：Usingfilesort，Usingtemporary。

常见的索引：where字段、组合索引（最左前缀）、索引下推（非选择行不加锁）、覆盖索引（不回表）on两边、排序、分组统计

## 2. SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。

## 3. SELECT语句务必指明字段名称

SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性；当表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。

## 4. 当只需要一条数据的时候，使用limit1

limit是可以停止全表扫描的

## 5. 排序字段加索引

## 6. 如果限制条件中其他字段没有索引，尽量少用or

## 7. 尽量用union all代替union

union和unionall的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

## 8. 不使用ORDERBYRAND()

ORDER BY RAND()不走索引

## 9. 区分in和exists、notin和notexists

区分in和exists主要是造成了**驱动顺序**的改变（这是性能变化的关键），如果是**exists**，那么以外层表为驱动表，先被访问，如果是**IN**，那么先执行子查询。

所以**IN适合于外表大而内表小**的情况；**EXISTS适合于外表小而内表大**的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原SQL语句：`select colname … from A表 where a.id not in (select b.id from B表)`

高效sql: `select colname … from A表 Left join B表 on where a.id = b.id where b.id is null`

## 10. 使用合理的分页方式以提高分页的效率

分页使用 limit m,n 尽量让m 小

利用主键的定位，可以减小m的值

如:  `select * from tbiguser limit 9999998, 2;` 

优化: `select * from tbiguser where id>9999998 limit 2; `

## 11. 分段查询

一些用户选择页面中，可能一些用户选择的范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

## 12. 不建议使用%前缀模糊查询

如何解决?，答案：使用全文索引或ES全文检索

## 13. 避免在where子句中对字段进行表达式操作

如: `select user_id,user_project from user_base where age*2=36;`

优化:` select user_id,user_project from user_base where age=36/2;`

## 14. 避免隐式类型转换

where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。` where age='18'`

## 15. 对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name; school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

## 16.  必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。

## 17. 注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效

## 18. 使用join优化

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

- MySQL中没有full join，可以用以下方式来解决：

  ```mysql
  select * from A left join B on B.name = A.namewhere B.name is null 
  union all 
  select * from B;
  ```

- 尽量使用inner join，避免left join

  参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

- 合理利用索引

  被驱动表的索引字段作为on的限制字段

- 利用小表去驱动大表

  ![示意图](https://img.zhaojq.top/20260727091650719.png "示意图")

  从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。