SQL索引的概念及语句优化

一、索引

1.什么是索引

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

2.索引的分类

单值索引：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引：索引列的值必须唯一，可为空

复合索引：一个索引包括多个列

3.索引的优势和劣势

优势：

提高数据检索效率，降低磁盘IO成本

通过对数据的排序，降低排序成本

劣势：

降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

4.适合建立索引的情况

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

5.不适合建立索引的情况

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

6.引起索引失效的原因

1）like以通配符开头('%abc')会导致索引失效，违反最左前缀法则

最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列。

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

二、存储过程

1.什么是存储过程？

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

就是数据库 SQL 语言层面的代码封装与重用。

2.存储过程的基本使用

1.基本格式：

-- 创建存储过程
delimiter 自定义结束符号  ||  $$
create procedure `数据库名`.`存储过程名`([in , out , inout] 参数名 数据类型。。。)
begin
	sql语句
	//select name from emp where id = ?;
end 自定义结束符号  || $$ 
delimiter ;

-- 调用存储过程
call 存储过程名;

参数：参数分为IN、OUT、INOUT类型三种类型。

IN类型的参数表示接受调用者传入的数据；
OUT类型的参数表示向调用者返回数据；
INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

2.定义变量：

declare var_name varchar(20) default 'aaa';  -- 声明/定义变量
set var_name = 'zhangsan'; -- 给变量赋值
select var_name; -- 输出变量的值

用户变量：

delimiter $$
create procedure `mytest`.`test`()
begin
	set @var_name = 'beijing';
	select @var_name;
end $$
dilimiter ;
call test();
select @var_name; -- 外部也是可以使用用户变量

系统变量
//系统变量分为全局变量与会话变量
语法：@@global.var_name
查看全局变量：show global variables;
修改全局变量：set @@global.xxx = xxx;

3.为什么要用存储过程

1. 简化对变动的管理。如果表名、列名、或业务逻辑有了变化。只需要更改存储过程的代码。使用它的人不用更改自己的代码。

2. 通常存储过程都是有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中，可重复使用。

3. 存储过程有助于减少应用程序和数据库服务器之间的流量。 因为应运程序不必发送多个冗长的SQL语句，只用发送存储过程中的名称和参数即可。

4.存储过程的缺点

1. 不易维护，阅读性差

2. 如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加。 此外，如果在存储过程中过度使用大量的逻辑操作，那么CPU的使用率也在增加，因为MySQL数据库最初的设计就侧重于高效的查询，而不是逻辑运算。

3. 很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

4. 开发和维护存储过程都不容易。

   开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能导致应用程序开发和维护阶段的问题。

5.为什么存储过程比sql语句效率高？

1.存储过程经过预编译处理 而SQL查询没有，SQL语句需要先被数据库引擎处理成低级的指令，然后才执行。

2.减少应用程序和数据库服务器之间的流量。

三、语句优化

1.sql的执行顺序

(8) SELECT(9) DISTINCT column,…
选择字段 、去重

(6) AGG_FUNC(column or expression),…
聚合函数

(1) FROM [left_table]
选择表

(3) <join_type> JOIN <right_table>
链接

(2) ON <join_condition>
链接条件

(4) WHERE <where_condition>
条件过滤

(5) GROUP BY <group_by_list>
分组

(7) HAVING <having_condition>
分组过滤

(10) ORDER BY <order_by_list>
排序

(11) LIMIT count OFFSET count;
分页

2.查询SQL尽量不要使用select *，而是select 具体字段

理由：

1.只取需要的字段，节省资源、减少网络开销

2.select * 进行查询时，很可能不会用到索引，就会造成全表扫描

3.避免在where子句中使用or来连接条件

例如;

使用

SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000

而不是

SELECT * FROM student WHERE id=1 OR salary=30000

理由：

1.使用or可能会使索引失效，从而全表扫描
2.对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的

4.使用varchar代替char

理由：

1.varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间

2.char按声明大小存储，不足补空格

3.其次对于查询来说，在一个相对较小的字段内搜索，效率更高

5.尽量使用数值替代字符串类型

例如：

主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启、2-暂停、3-停止
商品状态（state）：1-上架、2-下架、3-删除

6.查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。

7.正确使用索引

1.索引不宜太多，一般5个以内

2.索引不适合建在有大量重复数据的字段上

3.避免在索引列上使用内置函数

8.优化like语句

使用：

LIKE '1%'

而不是

LIKE '%1'
或
LIKE '%1%'

9.避免在where中对字段进行表达式操作

SELECT * FROM student WHERE id=+1-1+1

而不是

SELECT * FROM student WHERE id+1-1=+1

SQL解析时，如果字段相关的是表达式就进行全表扫描

字段干净无表达式，索引生效

10.避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

11.where中使用默认值代替null

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

SELECT * FROM student WHERE age>0

 12.提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

select job，avg（salary） from employee 
where job ='president' or job = 'managent' 
group by job;

13.排序字段创建索引

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

14.不要有超过5个以上的表连接

• 关联的表个数越多，编译的时间和开销也就越大
• 每次关联内存中都生成一个临时表
• 应该把连接表拆开成较小的几个执行，可读性更高
• 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
• 阿里规范中，建议多表联查三张表以下

15.优先使用inner join

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点

同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

15.in子查询的优化

#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：

#先查询部门表
SELECT id FROM tb_dept

#再由部门dept_id，查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

16.尽量使用union all替代union

理由：

1.union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
2.union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
3.union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION