SQl日常查询优化

• 什么是SQl调优？ 

   减少用户响应时间，即从用户发出命令到得到响应这一段时间，让一条SQL只使用最少的资源

在这里我要引入一个知识点，那就是“索引”

• 什么是索引？

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的特殊数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

小提示：在任何数据库中主键会自动创建索引对象，在MySql中字段上有UNIQUE约束，也会自动创建索引对象

什么时候添加索引？

1.数据量庞大

2.字段经常被扫描

3.字段很少DML(insert,delete,update语句)

注意：不要随意添加索引，因为索引是需要维护的，建议通过主键或者有UNIQUE约束的字段查，效率是比较高的。

批注：一个表的索引不要超过5个，虽然索引提高了查询效率，但是执行DML的时候会重建索引

创建索引代码

CREATE INDEX ideexBookName ON books(bookName); 
/*ideexBookName表示你这个索引的名字
books(bookName)表示为那个表的那个字段添加*/

删除索引代码

DROP INDEX ideexBookName ON books;

1.我们来看看索引的效果

批注： 我们来看到rows这里只查询了一条

2.再来看看没有索引的查询效果

 批注：可以看到row这里查询了4条才查到

索引的失效：

1.查询使用模糊查询以%开始就会失效，会全表扫描

2.用OR也会失效，只要其中一个条件没有索引，其他字段有索引也不会使用(如果要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引)

3.复合索引，比如下面这个代码

CREATE INDEX ideexBookName_detail ON books(bookName,detail);

批注：这种只有左侧的bookName索引才有用，detail的索引没有用

4.数据存在数据类型隐形转换，则用不上索引，比如列类型是字符串，没有加上引号

5.在索引上面就行运算或者函数，以下代码

EXPLAIN SELECT 
bookID,bookName,bookCounts 
FROM books 
WHERE bookCounts + 1= 100; #这是进行运算

EXPLAIN SELECT 
bookID,bookName,bookCounts 
FROM books WHERE 
LOWER(bookName) = 'Java';#这是使用函数

索引分类：

1.单一索引：就在字段添加索引

2.主键索引：主键上添加

3.唯一索引：具有UNIQUE约束的字段添加

4.复合索引：两个字段或者多个字段添加索引

• SQL 优化部分(其实优化可以从考虑索引是否失效着手)

注意WHERE子句中的连接顺序

 

查询SQL尽量不要使用SELECT  * FROM，而是具体字段：

Eg：SELECT bookID,bookName,bookCounts FROM books;

统一SQL语句的格式：

SELECT bookID,bookName,bookCounts FROM books;
SELECT bookID,bookName,bookCounts from books;
/*
看from大小写不一样这是两个不一样的SQl，会生成两个计划
*/

模糊查询的优化：

#反例
SELECT bookName,bookCounts,detail FROM books WHERE bookName LIKE '%Java%';
SELECT bookName,bookCounts,detail FROM books WHERE bookName LIKE '%Java';
#正例
SELECT bookName,bookCounts,detail FROM books WHERE bookName LIKE 'Java%';

批注：尽量不要在模糊查询的前面配备%，会让索引失效，除非万不得已 

避免在WHERE子句中使用OR来连接条件：

#反例
SELECT bookID,bookName,bookCounts FROM books WHERE bookID = 1 OR bookID = 3;
#正例
SELECT bookID,bookName,bookCounts from booksWHERE bookID = 1 
UNION ALL 
SELECT bookID,bookName,bookCounts from booksWHERE bookID = 2;

批注：上面说到过使用OR可能会使索引失效，从而全表扫描

查询条件的类型要对应，特别是字符串的数字

#反例
SELECT bookName,bookCounts,detail FROM books WHERE bookCounts = 1;
#正例
SELECT bookName,bookCounts,detail FROM books WHERE bookCounts = '1';

批准：这个地方就会涉及到一个数据类型转化导致索引失效

查询条件的字段不要去运算或者使用函数

#反例
EXPLAIN SELECT bookName,bookCounts,detail,price FROM books WHERE price/20=100;
#正例
EXPLAIN SELECT bookName,bookCounts,detail,price FROM books WHERE price=100*20;
#函数同理可证

避免在WHERE子句中使用!=或<>操作符，以及IS NOT NULL判断

批注：使用!=和<>很可能会让索引失效，如果有允许为NULL应该设置默认值，避免在 WHERE 子句中对字段进行 NULL 值判断，导致索引失效而进行全表扫描

#反例
EXPLAIN SELECT bookName,bookCounts,detail,price FROM books WHERE price IS NOT NULL;
#正例
EXPLAIN SELECT bookName,bookCounts,detail,price FROM books WHERE price=0;

慎用IN 和NOT IN，当连续的应该首选BETWEEN  AND

#反例
SELECT bookID,bookName,bookCounts,detail,price FROM books WHERE bookID IN(1,2,3);
#正例
SELECT bookID,bookName,bookCounts,detail,price FROM books WHERE bookID BETWEEN 1 AND 3;

批注：这样避免了全表扫描

用连表来代替代替 IN子查询：

#反例
SELECT s.StaId,s.StaName FROM staff s WHERE DepId IN(SELECT DepId FROM department);
#正例
SELECT s.StaId,s.StaName FROM staff s,department d WHERE s.DepId = d.DepId;

插入的优化（当插入数据多的时候考虑批量插入）：

#反例
INSERT INTO `books` VALUES (1, 'Java', '1', '从入门到放弃',22.2);
INSERT INTO `books` VALUES (2, 'MySQL', '10', '从删库到跑路',13.9);
INSERT INTO `books` VALUES (3, 'Linux', '5', '从进门到进牢',34.5);
INSERT INTO `books` VALUES (7, 'SQL调优', '0','速度起来'，10.9);
#正例
INSERT INTO `books`(bookID,bookName,bookCounts,detail,price) 
VALUE
(1, 'Java', '1', '从入门到放弃',22.2),
(2, 'MySQL', '10', '从删库到跑路',13.9),
(3, 'Linux', '5', '从进门到进牢',34.5),
(7, 'SQL调优', '0','速度起来',10.9);

用TRUNCATE替代DELETE(前提是这个表的数据不要了，这个效率是很高的，但是没有办法回滚，如果你表数据没有备份，就准备好哭吧)

#反例
DELETE FROM books;
#正例
TRUNCATE books;

提升BROUP BY的效率方案

#反例
SELECT StuId,StuName FROM student GROUP BY SEX HAVING StuId<10;
#正例
SELECT StuId,StuName FROM student WHERE StuId<10 GROUP BY SEX;

批注：这里要注意是先查然后再分组，不要先分组，再查，这个效率很慢的

INNER JOIN 、LEFT JOIN、RIGHT JOIN的选择，优先使用INNER JOIN

批注：三种连接如果结果相同，优先使用INNER JOIN ，LEFT JOIN如果使用左边表尽量小，RIGHT JOIN同理