01  Sql执行顺序

(1) FROM 子句 组装来自不同数据源的数据

(2)WHERE 子句 基于指定的条件对记录进行筛选

(3) GROUP BY 子句 将数据划分为多个分组

(4)使用聚合函数进行计算

(5)使用HAVING子句筛选分组

(6)计算所有的表达式

(7)使用ORDER BY对结果集进行排序

(8)select 获取相应列

(9) limit截取结果集

1.1 相应Case

需求:查询今日增长数据(根据video_id去重)

错误:select * from tb where date(created_at) = current_date() group by video_id

正确:select * from (select * from tb group by video_id ) as tb1 where tb1.date(created_at) = current_date()

错误原因:group by 操作在where后执行。所以,第一个语句 是查询今日 去重后数据,去重是在今日抓取的数据中去重。而且我们的需求是,对表中所有数据去重,然后获取今日新增长的的数据

02  索引基础

2.1 BTree索引

mysql默认存储引擎innodb只显式支持B树索引,对于频繁访问的表,innodb会透明建立自适应hash索引,

即在B树索引基础上建立hash索引,可以显著提高查找效率,对于客户端是透明的,不可控制的,隐式的。

B Tree:【更为细致的B树索引讲解请参考文末4.2延伸阅读 】

m阶 B树的特性:树中每个节点至多包含m棵子树  若根节点不是叶子节点,则至少包含两颗子树  除根以外的所有非终点节点至少有 (m/2)向上取整棵子树  支持范围查询,前缀匹配查询,等值查询,可以避免排序,例如order by index相关的列,排序会非常快,因为该列本身就是  有序存储的,查找时间复杂度 log m N(m为底,N的对数,N为总记录数)复制代码

2.2 Hash索引特性

只支持包括 “=” "in "在内的等值查询,不支持范围,前缀匹配查询

Hash索引是通过hash函数将,键值直接映射为物理存储地址,使时间复杂度降低到O(1).本身存储是无序的,所以不能通过hash索引避免排序

2.3 Mysql聚簇索引

B-树和B+树的区别在于B+树所有键值全部保存在叶子节点,而B-树则不然,B-树的键值根据树的结构分布在整个树上。而Mysql为什么要采用B+树索引呢?

1.遍历方便.B+树可以将键值保存在(线性表【数组或链表】)中,遍历线性表比索引树要快,因为保存在线性表中数据存储更加密集,B-Tree分散的

存储会导致更多的随机I/O,对于磁盘访问,随机I/O是比顺序I/O慢很多的,因为随机I/O需要额外的磁头寻道操作。顺序I/O有效减少寻道的次数

2.插入更新索引树时可以避免移动节点.

3.遍历任何节点的时间复杂度相同,即访问路径总是从根节点到叶子节点.相比B-树,访问时间略长.所以某些高频访问的搜索采用B-树,即访问频率越高

使其距离根节点越近。

4.(也许是最重要的)范围查找方便。对于[A,B]区间的范围查找,B-树索引可以直接找到A,B对应的线性表中节点,只需要返回区间的所有节点

即为目标结果。而B-树则稍显麻烦需要继续遍历索引树。

聚簇索引:将表中一条记录存储在索引的叶子节点中(也可能保存记录的物理地址[可能是磁盘或者扇区号也可能是文件名及对应偏移量]的指针,如果在内存中即为内存地址)。一般情况下mysql中使用主键 做聚簇索引

一个表只能有一个聚簇索引。(一条记录物理存储只有一份)

非聚簇索引中叶子节点的记录中需要保存主键,如需访问记录中其他部分还需要,通过主键回表查询。即两次索引查找?有人疑问非聚簇索引中为什么不保存记录项的物理地址呢?当然可以记录物理地址,但是主键索引更新操作带来的索引分裂合并会改变其物理地址,这样索引的维护代价比较大,而即使回表查询,主键查找速度一般较快,影响不大。另外也可以通过覆盖索引【即索引项覆盖了select中的项】避免回表查询

5.访问聚簇索引速度应该保证足够快,主键不宜选择过大存储需求的字段,例如UUID,另外非聚簇索引需要额外保存主键,主键太长存储需求较大。

也不宜选择字符串:一.字符串比较速度较数字慢,二.字符串插入时更加无序,索引树分裂合并相对更加频繁,出现更多磁盘碎片 。当有字符串和数字都能满足代理主键【该主键与业务无关只是添加一列主键保证记录唯一性】需求时,应当优先选择数字

做主键,但是如果逻辑主键【业务中有作为主键的列,也可选为主键,即为逻辑主键】是字符串类型,那也应该选择其作为主键,因为字符串相比数字性能差别不是很大。

03  索引常见优化方法

3.1 不能使用索引,不建议使用索引等常见误区

1.数据类型为Text,Blob等大对象不能建立索引,也不适合建立索引,另外字段太长的字段不适合建立索引。例如超长字符串。会使索引树过大,mysql可能无法将其放入内存,访问索引会带来过多的磁盘I/O。效率低下

2.查询表达式索引项上有函数.例如date(created_at) = 'XXXX'等.字符处理等。mysql将无法使用相应索引【查询表达式相应列不能使用函数,但是右边的值可以使用函数,例如 created_at < now() and created_at > current_date()

3.一次查询(简单查询,子查询不算)只能使用一个索引,

例如 where column1 = xxx and column2 = xxx order by column3

如果column1 ,column2 列各有一个索引,那么只能使用其中之一的索引,

具体使用哪个索引,要看mysql的统计信息,mysql执行计划中包括索引的选择,具体的选择要看哪个的索引选择率更高【唯一值/总记录数=选择率,0<选择率<=1  选择率越大,说明给定一个值可以过滤更多的行,即过滤性更高】。但是mysql的统计信息不是精确实时的。所以可能存在使用“错误的索引”的情况,这时可以强制使用某个索引

select * from tb1 as t use index(index_name) ......复制代码

但是强烈不推荐使用这种方式,可以将其作为临时方案使用,应该首先考虑优化索引设计,例如,上述Case就应该建立(column1,column2,column3)或(column2,column1,column3) 联合索引

where后的查询表达式顺序不能决定使用哪个索引.如column1 =xxx and column2 = xxx, 但并不代表优先使用column1 在前,column2在后的联合索引。使用哪个索引由相应索引项的选择率决定,最终判定标准是:扫描最少的行.使用索引过滤尽可能多的行。然后使用where中其他条件对 索引过滤后的结果集 一行行地判断 完成where条件过滤。

5.修改过于频繁的列使用索引要慎重.1s几十次的修改就要注意了,过于频繁的更新对于索引负担太重,磁盘负载过重,另外更新操作可能会锁住相关记录,有死锁和事务超时可能。但是该使就使。这些问题可以通过分区分表或者缓存解决

6.选择率低的列不适合建立索引。如果索引项对应cardinality较小,例如小于10,那么使用索引时就需要考虑是否有必要。因为访问索引的代价可能比全表扫描还要高。索引需要访问索引文件,然后访问叶子节点,拿到主键回表查询,如果结果集比较大,这个代价极可能大于全表扫描【全表扫描是顺序I/O,索引访问会涉及更多随机I/O,随机I/O比顺序I/O慢多了】。业务中常见的状态列,在设计之初,这一列的cardinality基数【唯一值的个数】即是固定的,随着记录数增加,选择率会越来越低,索引效率反而越来越低。可以考虑不建索引,或者将其作为联合索引的第一项

7.Mysql中对于唯一性检查即声明unique的列,自动建立唯一性索引,不需要再额外建立索引

8.不应该对where中每一个查询条件都建立上索引,mysql只会使用其中一个索引,过多的索引带来冗余,导致一些索引被“浪费”,同时mysql在生成执行计划时,需要考虑更多的索引,给查询优化带来更多工作,过多的索引还会给更新操作带来更沉重的索引维护代价。应该简化索引设计。同时利用联合索引满足多项条件的查询

9.Order By ,Group By 可以利用索引避免排序。但是 存在where 语句下 只能使用where 查询中使用的索引,例如where中使用了(A,B,C,D)联合索引的A,B项,如果order by ,或者group by中存在C,或者(C,D)即可使用联合索引,如果where中没有使用索引,那么即使order by,group by列中有索引也不能使用。即优先根据where 查询使用索引,然后根据where中使用的索引再决定,order by,group by是否可以 使用到索引

10.当数据量达到千万级别以上,索引本身就很大,无法装入内存,访问索引带来的磁盘随机I/O 开销很大,索引性能下降较快,当并发量不大情况下,建立分区表可有效提高速度,因为分区表的索引结构是互相独立的,可单独装入内存,减少磁盘访问。

11.更新删除时指定索引列【事务特性,及隔离级别不熟悉同学请参考 延伸阅读4.1】,mysql在默认的事物隔离级别是序列化解决了幻读,并且通过间隙锁,多并发版本读提高了并发访问性能,幻读是指:一个事务中,当用户查询一个范围中的结果时,另一个事务执行了相应的插入删除操作,导致两次查询结果不同,少了或多了一些行,就像幻象一样。mysql 解决幻读有两种方案:一.对于查询select操作只是针对本事务开启时刻的“镜像”查询。例如本事务开启后,其他事务插入删除了相关数据并提交,本事务是无法察觉的。实现方式为 版本控制。二.更新删除【包括 select ………… for update 】等写操作涉及到范围更新时,如果查询条件where中存在索引,即锁住索引树的相关键值段例如 更新 id主键索引在 1-100的数据,那么它会锁住 1- 100 这些记录的 id 索引,其他事务更新这个范围数据时,会进入锁等待,直到拥有锁的事务,或者等待超时。如果查询条件中不能使用索引,mysql为了实现序列化的隔离级别,会对全表加锁,任何写操作不能进行。当并发写操作多,事务时间长时,会出现较多锁等待及等待超时事务。需要通过添加索引,及减小事务粒度或者降低mysql默认隔离级别方式解决此类问题。

3.2 索引设计的几个“原则”

1.索引的设计应该与业务需求息息相关,没有完美的索引设计,只有满足需求的索引设计,项目前期设计的索引不可能完美的满足后期的需求。应随时根据业务合理取舍。

2.索引设计应该优先照顾查询最为频繁,或业务优先级高,与用户相关的查询。如果我们可以忍受,那么可以不建索引

3.使用短索引,索引长度不宜过大,利用B Tree的特性使用最左匹配查找高效利用索引第一列、对选择率高的列索引、使用覆盖索引避免回表查询

4.及时删除不再使用的索引,例如发现(A,B)不满足需求,新加一项(A,B,C)即可删除旧索引(A,B)

3.3 联合索引的顺序问题

1.联合索引设计时,索引顺序是很重要的。当联合索引中,每一列的查询频率都相差不多时,可以优先将选择率最高的列作为联合索引第一列,这样第一列即可过滤更多列,效率更高。由于联合索引第一列可以单独使用,例如联合索引(column1,column2,column3,column4)即可满足 where column1 =xxx 也可满足 where column1 = xxx and column2 = xxx and column3 =xxx and column4 = xxx的需求,这样不需要为第一列的独立查询额外建立单列索引

2.使用部分前缀索引键,按照联合索引声明顺序查询。例如索引(A,B,C) 只能匹配  where A = x xx ,where A = XXX and B = xxx ,where A = xxx and B = xxx and C = xxx ,不能跳过前一列,匹配后一列. 例如  where A = xxx and c = xxx 这时虽然可能也使用该索引,但是只能使用一部分,匹配A列,而B,C列不能匹配。

3.前缀匹配,与范围匹配。 BTree索引可以使用前缀匹配,例如 where A like "xxx%" ,使用前缀索引后,就不能使用前缀列的后续索引列。

4.group by,order by 本质是对where查询出的结果集进行排序操作,当待排序列匹配 where 中索引顺序时才可避免排序,直接通过索引即可返回有序结果集,例如我们需要将查询结果按照评分排名,那么就可以考虑将rank列放在联合索引的最后一列。(X, …… ,rank)。当查询结果比较大时,可以考虑这样设计

5.limit 分页查询 .limit 使用时必须排序否则可能出现不同页返回重复数据的风险。limit 返回某一位置的给定偏移量的记录,但是它的顺序依赖于存储位置顺序,索引顺序,所以分页时不同页会有出现重复数据的风险。limit 操作前需要添加order by 进行排序。由于访问非聚簇索引时,mysql有一个优化操作,当访问非聚簇索引,回表查询时,mysql 会对主键进行排序,目的是:聚簇索引是按顺序存储记录,对主键排序后,访问聚簇索引可以更加顺序的访问磁盘,减少随机I/O,提高速度,所以当分页没有特别指定的列时,指定主键排序即可,另外不需要在联合索引最后一列添加主键,因为它本身包含主键 【非聚簇索引不存储完整记录,通过访问主键索引找到完整记录 】。

3.4 索引设计优化常见小技巧

以上已经列出较多的误区及注意事项,理解即可,更重要的是根据业务对索引取舍的经验。更多的设计技巧希望同学们在实践中自己总结并分享出来。

1.数据量较大的表(千万以上)考虑是否适合建立分区。 

2.对于较长字符串例如200以上,可以考虑单独增加索引列,对其整体hash或者去其中一部分hash后存入其他一列,这 样将字符串查找变成数字查找,同时索引长度大大减小,可有效提高索引速度,降低索引大小。但是需要考虑hash函数 的“碰撞”问题,选择适合的hash函数。 

3.使用explain命令查看sql 的执行计划,请参考延伸阅读

04 延伸阅读

4.1 数据库事务及隔离级别概要

数据库事务的四个标准特征(ACID):

① 原子性:一个事务必须被一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中一部分操作。

② 一致性:数据库总是从一个一致性的状态转换到另外一个一致性的状态。指关联数据之间的逻辑关系是否正确和完整,一致性处理数据库中对所有语义约束的保护。假如数据库的状态满足所有的完整性约束,就说该数据库是一致的。

③ 隔离性:通常一个事务所做的修改在最终提交以前,对其他事务是不可见的,多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。这指的是在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时,数据所处的状态要么是另一事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看到中间状态的数据。

 ④持久性:意味着当系统或介质发生故障时,确保已提交事务的更新不能丢失。即一旦一个事务提交,DBMS保证它对数据库中数据的改变应该是永久性的,耐得住任何系统故障。持久性通过数据库备份和恢复来保证。

对于事务的隔离性而言有四种隔离级别:

① Read Uncommitted(读取未提交内容):在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。

② Read Committed(读取提交内容):这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。由于正在读取的数据只获得了读取锁,读完之后就解锁,不管当前事务有没有结束,这样就容许其他事务修改本事务正在读取的数据。导致不可重复读。解决不可重复读的问题就要求,对正在读取的若干行加上行级锁。要求在本次事务中不可修改这些行。解决ReadCommited更侧重数据行不可更新。

③ Repeatable Read(可重读):这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

解决幻读的方案应该是在表上加锁,幻读出现的场景主要是插入操作,由于插入操作使得事务不同的查询中出现不同的结果。

④ Serializable(可串行化):  这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取,而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题,在可能出现这类问题的个别场合,可以由应用程序采用悲观锁或乐观锁来控制。(乐观锁通过版本号控制是否存在不可重复读情况,如果不存在则提交,否则事务回滚。悲观锁是通过数据库系统本身在内部加锁,锁住要更新的数据,不允许其他事务修改,但是会消耗大量的性能)

4.2 explain命令讲解

Explain 的使用

ID:MySQL Query Optimizer选定的执行计划中查询的序列号。

Select_type:所使用的查询类型,主要有以下这几种查询类型。

DEPENDENT SUBQUERY:子查询内层的第一个SELECT,依赖于外部查询的结果集。

DEPENDENT UNION:子查询中的UNION,且为UNION中从第二个SELECT开始的后面所有SELECT,同样依赖于外部查询的结果集。

PRIMARY:子查询中的最外层查询,注意并不是主键查询。

SIMPLE:除子查询或UNION之外的其他查询。

SUBQUERY:子查询内层查询的第一个SELECT,结果不依赖于外部查询结果集。

UNCACHEABLE SUBQUERY:结果集无法缓存的子查询。

UNION:UNION语句中第二个SELECT开始后面的所有SELECT,第一个SELECT为PRIMARY。

UNION RESULT:UNION 中的合并结果。

Table:显示这一步所访问的数据库中的表的名称。

Type:告诉我们对表使用的访问方式,主要包含如下集中类型。

all:全表扫描。

const:读常量,最多只会有一条记录匹配,由于是常量,实际上只须要读一次。

eq_ref:最多只会有一条匹配结果,一般是通过主键或唯一键索引来访问。

fulltext:进行全文索引检索。

index:全索引扫描。

index_merge:查询中同时使用两个(或更多)索引,然后对索引结果进行合并(merge),再读取表数据。

index_subquery:子查询中的返回结果字段组合是一个索引(或索引组合),但不是一个主键或唯一索引。

rang:索引范围扫描。

ref:Join语句中被驱动表索引引用的查询。

ref_or_null:与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。

system:系统表,表中只有一行数据;

unique_subquery:子查询中的返回结果字段组合是主键或唯一约束。

Possible_keys:该查询可以利用的索引。如果没有任何索引可以使用,就会显示成null,这项内容对优化索引时的调整非常重要。

Key:MySQL Query Optimizer 从 possible_keys 中所选择使用的索引。

Key_len:被选中使用索引的索引键长度。

Ref:列出是通过常量(const),还是某个表的某个字段(如果是join)来过滤(通过key)的。

Rows:MySQL Query Optimizer 通过系统收集的统计信息估算出来的结果集记录条数。

Extra:查询中每一步实现的额外细节信息,主要会是以下内容。

Distinct:查找distinct 值,当mysql找到了第一条匹配的结果时,将停止该值的查询,转为后面其他值查询。

Full scan on NULL key:子查询中的一种优化方式,主要在遇到无法通过索引访问null值的使用。

Range checked for each record (index map: N):通过 MySQL 官方手册的描述,当 MySQL Query Optimizer 没有发现好的可以使用的索引时,如果发现前面表的列值已知,部分索引可以使用。对前面表的每个行组合,MySQL检查是否可以使用range或 index_merge访问方法来索取行。

SELECT tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段时,MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然,前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。

Using filesort:当Query 中包含 ORDER BY 操作,而且无法利用索引完成排序操作的时候,MySQL Query Optimizer 不得不选择相应的排序算法来实现。

Using index:所需数据只需在 Index 即可全部获得,不须要再到表中取数据。

Using index for group-by:数据访问和 Using index 一样,所需数据只须要读取索引,当Query 中使用GROUP BY或DISTINCT 子句时,如果分组字段也在索引中,Extra中的信息就会是 Using index for group-by。

Using temporary:当 MySQL 在某些操作中必须使用临时表时,在 Extra 信息中就会出现Using temporary 。主要常见于 GROUP BY 和 ORDER BY 等操作中。

Using where:如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where 信息。

Using where with pushed condition:这是一个仅仅在 NDBCluster存储引擎中才会出现的信息,而且还须要通过打开 Condition Pushdown 优化功能才可能被使用。控制参数为 engine_condition_pushdown 。

Impossible WHERE noticed after reading const tables:MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。

No tables:Query 语句中使用 FROM DUAL或不包含任何 FROM子句。

Not exists:在某些左连接中,MySQL Query Optimizer通过改变原有 Query 的组成而使用的优化方法,可以部分减少数据访问次数。