SQL查询与索引优化-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_30054997/article/details/80315194

基础

字符串类型必须加单引号

执行顺序

#5 SELECT 
#1 FROM
#2 WHERE
#3 GROUP BY
#4 HAVING
#7 ORDER BY

数据类型

int(n) 写多少都无所谓读取的都是11位

TINYINT 适合作为状态码 (-127 ~ 127)

varchar(N) N是字符 (一个字母就是一个字符) 开头会用1-2个字节储存字符串实际长度(以255分界)

DATE 只有3个字节 DATETIME 占用8字节

常用命令

查看帮助信息 help 等同于？show 查看数据库/表

查看表的基本情况 show table status like 表数据，平均行长

show index from tab 查看索引

show colunms from tab 查看列、

show session variables /show global variables/session status查看变量值

full processlist 会影响性能查看当前连接用户和执行sql

set names utf8mb4 防止乱码

truncate table tab_name 删除表，性能高于delete

delete from table where id //这里会判断id是否有值有值就判定成功

insert into + select 会有S Lock 产生严重的锁在线上使用会及其缓慢

索引规范

单张表不超过5

单个索引的字段数不超过5

索引名小写 idx_xxx uniq_xxx

表必须有主键 Unsigned 自增列

禁止冗余重复索引禁止外键

连表查询join列数据必须相同且要建立索引

选择区分度大的列建索引，组合索引中区分度大的字段放在前面

命名规范

（1）库表命名规范小写字母下划线分割，禁止超过32字符见名知意禁止保留字临时库tmp_test1_20180724 备份库 bak_test2_20180724

（2）字段规范小写字母下划线

（3）索引规范小写字母下划线非唯一索引『idx_字段名』唯一索引『uniq_字段名』组合索引用缩写

（4）业务命名

基础规范

（1）使用InnoDB存储引擎并且使用业务不相关自增ID为主键

（2）表字符集使用UTF-8/UTF8MB4字符集

（3）所有表字段都需要添加注释推荐英文标点避免乱码

（4）禁止数据库存储图片文件等大数据

（5）每张表数据量控制在5000W以内

（6）禁止线上数据库压力测试

（7）禁止直接从测试、开发环境直连数据库

效率探索

**count(*) 与 count(col)**

count(col) 可以查出此列所有不为空的数量如果此列没索引则效率会下降

count(*) 查所有列或自动使用不能为空的索引

count(*)和count(1)执行的效率是完全一样的。

1.关于count(1)，count(*),和count(列名)的区别

相信大家总是在工作中，或者是学习中对于count()的到底怎么用更快。一直有很大的疑问，有的人说count(*)更快，也有的人说count(列名)更快，那到底是谁更快，我将会在本文中详细介绍一下到底是count(1)，count(*)和count(列明)的区别，和更适合的使用场景。

往常在工作中有人会说count(1)比count(*)会快，或者相反，首先这个结论肯定是错的，实际上count(1)和count(*)并没有区别。

接下来，我们来对比一下count(*)和count(列)到底谁更快一些

首先我们执行以下sql，来看一下执行效率（下面sql针对的是ORACLE数据库，大致逻辑为先删除t别，然后在根据dba_objects创建t表，在更新t表根据rownum）

drop table t purge;
create table t as select * from dba_objects;
--alter table T modify object_id  null;
update t set object_id =rownum ;
set timing on 
set linesize 1000
set autotrace on --开启跟踪
select count(*) from t;
/
select count(object_id) from t;
/

然后咱们分别看一下“select count(*) from t”和“select count(object_id) from t”语句的执行计划。（执行计划是指sql的一个执行顺序和耗费的资源，耗费的资源越少越快，如果在plsql中，使用F8可以查看sql的执行计划）

通过我们执行sql的实验来说，count(*)和count(列)消耗的资源是一样的，说面他们是一样快的，但是真的是这样么。那么咱们接着以下的实验。

这次咱们给object_id这一列加一个索引试一下。我们执行一下索引sql

create index idx_object_id on t(object_id);
select count(*) from t;
/
select count(object_id) from t;
/

然后我们在分别看一下两条sql的执行计划

通过我们建完索引后。突然发现count(列)变快了好多，但是count(*)还是和以前一样的。这说明了count(列)可以用到索引，而count(*)不行，但是真的这样么，咱们在往下看。

接下来我们给object_id这个字段加上不可为空条件。我们执行以下sql

create index idx_object_id on t(object_id);
select count(*) from t;
/
select count(object_id) from t;
/

接下来我们在来看一下count(*)的执行计划

现在count(*)和count(列)一样快了，由此我们得出了这个结论：count(列)和count(*)其实一样快，如果索引列是非空的，count(*)可用到索引，此时一样快。

总结：但是真的结论是这样的么。其实不然。其实在数据库中count(*)和count(列)根本就是不等价的，count(*)是针对于全表的，而count(列)是针对于某一列的，如果此列值为空的话，count(列)是不会统计这一行的。所以两者根本没有可比性，性能比较首先要考虑写法等价，这两个语句根本就不等价。也就失去了去比较的意义！！！

2.关于表中字段顺序的问题

首先我们建一张有25个字段的表并加入数据在进行count(*)和count(列)比较。由于建表语句和插入语句和上面雷同。就不贴出代码了。

然后我们分别执行count(*)和count每一列的操作来看一下到底谁更快一些，由于执行计划太多，就不一一贴图了。我整理了一个excel来给大家看一下执行的结果

经过实验我们看出，count(列)越往后。我们的执行效率越慢。所以，我们得出以下结论：

1.列的偏移量决定性能，列越靠后，访问的开销越大。

2.由于count(*)的算法与列偏移量无关，所以count(*)最快。

总结：所以我们在开发设计中。越常用的列，要放在靠前的位置。而cout(*)和count(列)是两个不等价的用法，所以无法比较哪个性能更好，在实际的sql优化场景中要根据当时的业务场景再去考虑是使用count(*)还是count(列)（其中的区别上文有提到）。

Where 与 Join

where进行关联实际上被转为join. 效率差不多但在left join时副表数据少效率会有一定提升

对 on 判断的字段加索引会大幅提升效率

on判断和加where判断有一定区别见下面的分析

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

两条SQL:

select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

1、中间表

-- on条件:
tab1.size = tab2.size

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	20	BBB
2	20	20	CCC
3	30	null	null

2、再对中间表过滤

-- where 条件：
tab2.name=’AAA’

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA

第二条SQL的过程：

中间表

-- on条件:
tab1.size = tab2.size and tab2.name=’AAA’

(条件不为真也会返回左表中的记录)

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	(null)	(null)
3	30	(null)	(null)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

IN 优化 & 去重问题

需求一个表中有一些脏数据需要清理,需要获得所以相同appId下action也相同的脏数据,既留下一条其他的全部获取到.

方法1:

SELECT a.* FROM behaviour_log a where id not in 
(select t.id from (SELECT b.* FROM behaviour_log as b
GROUP BY b.action,b.appId HAVING COUNT(*)>=1)as t)

思路使用多字段去重方式获取所以不重复的再NOT IN获取脏数据

方法2:

SELECT
	a.* 
FROM
	behaviour_log AS a
	LEFT JOIN ( SELECT id FROM behaviour_log  GROUP BY action, appId ) AS tmp ON tmp.id = a.id 
WHERE
	tmp.id IS NULL

使用优化NOT IN的方式注意比较值必须使用非空不重复列

最后做删除 IN 的优化:

DELETE a 
FROM behaviour_log AS a
	LEFT JOIN ( SELECT id FROM behaviour_log  GROUP BY action, appId ) AS tmp ON tmp.id = a.id 
WHERE
	tmp.id IS NULL

<DELETE 别名 FROM TABLE AS 别名 > 的方法来进行删除脏数据.

需求2:查询每科前两名的学生信息

-- 查询每科前两名
SELECT * FROM class A
WHERE student IN (SELECT TOP 2 student FROM class B
-- mysql不支持top  SELECT student FROM class ORDER BY score limit 2
                      WHERE B.class = A.class
                      ORDER BY B.score DESC)
ORDER BY A.class, A.score DESC

思路先查询最高分的个人再用in语句

--避免使用in语句优化
SELECT * FROM class A
WHERE (SELECT COUNT(*)
          FROM class B 
          WHERE B.class = A.class
         -- 比A表成绩高的有一个人或者没有人就是第一名和第二名
           AND B.score > A.score) < 2
ORDER BY A.student, A.score DESC

既每科没有比表A大的则只有最高分的那个人如果有一个比它分数高的则是第二名

索引

索引基础

1、索引是什么

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

可以理解为“排好序的快速查找数据结构”

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,

这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2、优势

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。

通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

3、劣势

实际上索引也是一个另外保存的数据结构，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。

虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。

都会调整因为更新所带来的键值变化后的索引信息。

4、哪些情况需要创建索引

①主键自动建立唯一索引

②频繁作为查询条件的字段应该创建索引

③查询中与其他表关联的字段，外键关系建立索引

④频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

⑤WHERE条件里用不到的字段不创建索引

⑥单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

⑦查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

⑧查询中统计或者分组字段

5、哪些情况不要创建索引

①表记录太少

②经常增删改的表

提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。

③注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

6.为什么 B-/+ Tree 更适合作为索引的数据结构

根据B-Tree的定义，可知检索一次最多需要访问h个结点。数据库系统的设计者巧妙的利用了磁盘预读原理，将一个结点的大小设为等于一个页面，这样每个结点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建结点时，直接申请一个页面的空间（磁盘块），这样可以保证一个结点的大小等于一个页面，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根结点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出读d是非常大的数字，通常超过100，因此h非常小。

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树结构，h明显要深得多。由于逻辑上很近的结点（父子结点）物理上可能离得很远，无法利用局部性原理。所以即使红黑树的I/O渐进复杂度也为O(h)，但是查找效率明显比B-Tree差得多。

B+Tree更适合外存索引，是和内结点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于结点内key和data的大小：dmax=floor(pagesize/(keysize+datasize+pointsize))。

floor表示向下取整。由于B+Tree内结点去掉了data域，因此可以拥有更大的出度，拥有更好的性能

使用索引

1.查询比例太大的时候(大概20%以上) 如果走索引反而可能因为太多随机查询的原因导致效率下降 SQL自动回进行全表查询不走索引

2.先走唯一索引

P.S.索引触达总结 https://www.cnblogs.com/tgycoder/p/5410057.html

WIKI InnoDB 锁的粒度小

联合索引

数据结构如下:

WIKI SELECT 的字段在有聚集索引的时候也会查询优化

EXPLAIN Output Columns

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）
possible_keys	揭示哪一些索引可能有利于高效的查找
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	显示了之前的表在key列记录的索引中查找值所用的列或常量
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra	额外信息，如using index、filesort等

id

id是用来顺序标识整个查询中SELELCT 语句的，在嵌套查询中id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果。

select_type

表示查询的类型

类型	说明
simple	简单子查询，不包含子查询和union
primary	包含union或者子查询，最外层的部分标记为primary
subquery	一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询
derived	派生表——该临时表是从子查询派生出来的，位于form中的子查询
union	位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived
union result	用来从匿名临时表里检索结果的select被标记为union result
dependent union	顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
subquery	子查询中第一个SELECT语句
dependent subquery	和DEPENDENT UNION相对UNION一样

table

对应行正在访问哪一个表，表名或者别名

关联优化器会为查询选择关联顺序，左侧深度优先
当from中有子查询的时候，表名是derivedN的形式，N指向子查询，也就是explain结果中的下一列
当有union result的时候，表名是union 1,2等的形式，1,2表示参与union的query id

注意：MySQL对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

type

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

possible_keys

显示查询使用了哪些索引，表示该索引可以进行高效地查找，但是列出来的索引对于后续优化过程可能是没有用的

key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。

而如果索引相同的情况下越大索引使用率越高

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

Extra

Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

类型	说明
Using filesort	MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
Using temporary	用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists	MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Using index	说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。
Using index condition	这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
Using where	使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer	使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作