时间：   |   分类：   | 字数： 2002 字 | 阅读约： 4分钟 | 阅读次数：

sql语句及索引优化

案例: 设计1个表 tb_test

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条数据

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;

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，可以用以下方式来解决：

    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的限制字段

  • 利用小表去驱动大表

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

0%