学习 尚硅谷MySQL高级 视频,总结笔记。
脑图课件:https://download.csdn.net/download/zxm1306192988/10396604
SQL执行时间长原因:
- 查询语句写的烂
- 索引失效
- 不要在建立的索引的数据列上进行下列操作:
避免对索引字段进行计算操作
避免在索引字段上使用not,<>,!=
避免在索引列上使用 IS NULL 和 IS NOT NULL
避免在索引列上出现数据类型转换
避免在索引字段上使用函数
避免建立索引的列中使用空值 - 关联查询太多join(设计缺陷或不得已的需求)
- 其他:需要服务器调优及各个参数设置(缓冲、线程数等)
内联合(inner join)只生成同时匹配表A和表B的记录集。
左外联合(left join)生成表A的所有记录,包括在表B里匹配的记录。如果没有匹配的,右边将是null。
全外联合(full join)生成表A和表B里的记录全集,包括两边都匹配的记录。如果有一边没有匹配的,缺失的这一边为null。
MySQL没有FULL OUTER JOIN,使用 UNION
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
索引简介
索引是什么
索引(Index)是帮助MySQL高效获取数据的数据结构。
除数据本身之外数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
索引往往以索引文件的形式存储在磁盘上。
索引优势
类似图书馆建立书目索引,提高数据检索效率,降低数据库的IO成本。
通过索引列对数据进行排序,降低数据排序成本,降低CPU消耗。
索引劣势
占用额外空间;
降低跟新、插入、删除表的速度。
mysql索引分类
1、单值索引
一个索引只包含单个列,一个表可以有多个单列索引
2、唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。主键索引是特殊的唯一索引。
3、复合索引
即一个索引包含多个列
mysql索引结构
1、BTree 索引
B+树更充分的利用了节点的空间,让查询速度更加稳定,
B+跟B树不同B+树的非叶子节点不保存关键字记录的指针,这样使得B+树每个节点所能保存的关键字大大增加;所有数据地址必须要到叶子节点才能获取到,所以每次数据查询的次数都一样;所以查询速度更稳定;
所有的叶子节点由指针连接。可以进行区间访问。
主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后一起返回,程序继续运行。
MySQL 巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次 I/O 就可以完全载入。
查找时,每层只进行一次IO,3层的b+树就能表示上百万的数据,使用B+树结构进行查找,能大大减少磁盘IO。
浅蓝色块为磁盘块,包含数据项(深蓝)和指针(黄色)。
真实数据只存在于叶子节点。
b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。
类似下图,索引中包含了last_name、first_name、dob列的值,索引首先根据第一个字段来排列顺序,相同再根据第二个字段:
一个加了主键的表,在磁盘上的存储结构就由整齐排列的结构转变成了平衡树结构,整个表就变成了一个索引,也就是所谓的「聚集索引」。树的所有结点(底部除外)的数据都是由主键字段中的数据构成,也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。
查找次数是以树的分叉数为底,记录总数的对数,用公式来表示就是
我们平时提到的常规索引是非聚集索引。
每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。
非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据。
聚集索引(主键)是通往真实数据所在的唯一路径。
当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。
#建立索引
create index index_birthday on user_info(birthday);
#查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1';
这句SQL语句的执行过程如下
首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值
然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对应的真实数据(数据行)存储的位置
最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果
我们把birthday字段上的索引改成双字段的覆盖索引
create index index_birthday_and_user_name on user_info(birthday, user_name);
这句SQL语句的执行过程就会变为
通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,
然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能。
覆盖索引(covering index)指一个查询语句的执行只需要从辅助索引(非聚集索引)中就可以得到查询记录,而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。覆盖索引是mysql dba常用的一种SQL优化手段。
上面提的聚集索引和非聚集索引都是 InnoDb 的。
InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。非聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。
Innodb 的一个数据库的数据文件除了.frm 存储数据库结构外,只有一个.ibd 存储数据和索引
MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。
MyISAM的一个数据库的数据文件 .MYD 存储数据,.MYI 存储索引
详见:https://www.2cto.com/database/201211/172380.html
2、Hash索引
3、full-text全文索引
4、R-Tree 索引
索引的基本语法
创建:
CREATE [UNIQUE] INDEX indexName ON mytable(columName(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columenName(length));
如果是char、varchar类型,length可以小于字段实际长度,如果是BLOB和TEXT类型,必须指定length。
删除:
DROP INDEX [indexName] ON mytable;
查看:
SHOW INDEX FROM mytable\G
各个参数的含义
- TABLE:要创建索引的表
- Non_unique:索引非唯一,1代表是非唯一索引,0代表唯一索引
- Key_name:索引的名称
- Seq_in_index:该字段在索引中的位置,单列索引该值为1,复合索引为每个字段在索引定义中的顺序
- Column_name:定义索引的列字段
- Sub_part:索引的长度
- NULL:该字段是否能为空值
- Index_type:索引类型
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 查询中排序的字段,排序的字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 单键/组合索引的选择问题?在高并发下倾向创建组合索引
哪些情况不要创建索引
- 表记录太少
- where条件里用不到的字段不创建索引
- 频繁更新的字段不适合建立索引,因为每次更新不单单是更新了记录还会更新索引
- 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
性能分析
MySQL 自带的优化器:MySQL Query Optimizer
MySQL常见瓶颈:
-
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
-
IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈:top、free、iostat 和 vmstat 来查看系统的性能状态
Explain关键字
使用explain关键字可以模拟优化器(MySQL query optimizer)执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
能干嘛:
-
表的读取顺序
-
数据读取操作的操作类型
-
哪些索引可以使用
-
哪些索引被实际使用
-
表之间的引用
-
每张表有多少行被优化器查询
怎么用:
EXPLAIN + SQL语句
EXPLAIN SELECT * FROM t_score;
执行计划包含的信息:
各字段解析
1、 id
select 查询的序列号,表示查询中执行 select 子句或操作表的顺序。
- id相同,执行顺序由上至下
- id不同,如果是子查询,id序号会递增
- id值越大优先级越高,越先被执行
2、select_type
查询的类型,主要用于区别普通查询、联合查询、子查询等的复杂查询。
- SIMPLE ------简单的select查询,查询中不包含子查询或者UNION
- PRIMARY ------查询若包含任何复杂的子部分,最外层查询则被标记为primary
- SUBQUERY------在 select 或 where 列表中包含了子查询
- DERIVED------在 from 列表中包含的子查询被标记为 derived(衍生); MySQL会递归执行这些子查询,把结果放在临时表里(必须设置临时表名)。
- UNION------若第二个 select 出现在 union 之后,被标记为 union;若 union 包含在 from 子句的子查询中,外层 select 被标记为:derived
- UNION RESULT------从 union 表获取结果的 select
3、table
显示这一行的数据是关于哪张表的
4、type
查询表获取数据的类型或者叫查询类型
从好到差依次是:
system>const>eq_ref>ref>range>index>all
- system
表里只有一条记录,const类型的特例,查他就行了,一般只在系统表出现,实际应用中不会出现。 - const
查找特定索引值的具体的一条数据,通过索引一下就找到了,不用遍历。如where 指定primary key 或 unique 索引列的具体的一个值。 - eq_ref
唯一性索引扫描,对于每个记录键,表中只有一条记录与之匹配。(与const的区别,t1,t2连表,t1可能多条记录,但t2 与之匹配的只有一条记录,即需要遍历 t1,t1 的每个记录键是 const 方式查找 t2)
另一种理解:先通过非聚集索引得到唯一一个值,然后通过聚集索引得到记录。 - ref
非唯一性索引扫描,索引列是普通索引(列数据可能相同),根据一个值可能返回多行,即先根据非聚集索引可能返回多个主键值,然后通过聚集索引,得到所有记录。 - range
有索引,where 中给定了遍历的范围,select * from t1 where id between 30 and 60; - index
全索引扫描,有索引,遍历索引树 ,select id from t1;(虽然与all都是读全表,但index是从索引中读取的,all需要更多字段,是从硬盘中读取的) - all
全表扫描,不能利用上索引,如 select name from t1;
5、possible_keys
显示可能应用在这张表中的索引,一个或多个
查询涉及到的字段上若存在索引,则该索引就爱你将被列出,但不一定被查询实际使用。
6、key
实际使用了的索引。如果显示 null 则表示该查询没有使用到索引
查询中若使用了覆盖索引,则该索引和查询的 select 字段重叠
7、key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的最大可能长度,并非实际使用长度,即key_len 是根据表定义计算而得,不是通过表内检索时实际使用的长度。
在不损失精读的情况下,长度越短越好。
8、ref
显示索引中使用的列名,或者常数值。
哪些列或常数被用于查找索引列上的值。
9、rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
10、Extra
一些额外信息
- Using filesort
这种情况很糟糕,表示 无法利用索引完成排序,而使用了一个外部索引排序,成为文件排序。 - Using temporary
这种情况很糟糕,mysql 对查询结果排序时使用了临时表保存中间结果,常见于 order by 和 group by。 - Using index
好事情,表示用上了覆盖索引,避免了访问表的数据行。如果同时出现 using where,表明索引被用来执行键值的查找;如果没出现 using where,表明索引用来读取数据而非查找。 - Using where
表明使用了 where 过滤。 - Using join buffer
表示使用了连接缓存。 - impossible where
表示 where 子句的值总是 false,无用。 - select tables optimized away
表示在没有 group by 子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM 存储引擎优化 count(*) 操作,不必等到执行阶段在进行计算,查询执行计划的生成阶段即完成优化。 - distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。
示例:
1、
查询中与其他表关联的字段,外键关系建立了索引。
由于是 select * ,t2 表中数据需要全部获取,所以
先 全表查询 t2 ,他没有用到任何索引
然后用到 t1 表的 ind_col1_col2 外键索引,t1表的col1 去匹配 t2 表的col1, t1表的col2 匹配 ‘ac’ 这个常量。根据每个 t2表的col1 可能对应多个 t1 表的 col1 ,所以 type 为 ref 。
2、
没有索引时,t2 表为全表扫描,为 t2 表添加联合索引后,查询类型 优化为 ref (非唯一性索引扫描),因为满足 t2.col1=‘ac’ 的可能有多个列;ref 项 优化为 const,因为t2.col1= 一个常数。
3、
Extra 信息中出现了 Using filesort,这可不是好现象。col1、col2、col3 有联合索引,直接对col3 排序,没有用上索引,数据库需要建立文件排序,当改为 order by col2,col3 时排序用上了索引,Using filesort 消失。
4、
Extra 信息中出现了 Using filesort,这可不是好现象。排序没用上索引,而且mysql建立了临时表,当改为 order by col1,col2 时排序用上了索引,Using filesort、temporary 消失。
5、
如果同时出现 using where ,表明索引被用来执行索引键值的查找
如果没有出现 using where ,表明索引用来读取数据而非执行查找动作。
6、
- 第一行(执行顺序4):id列为1,表示是union里的第一个select,select_type 列的primary表示该查询为外层查询,table列被标记为 < derived3> ,表示查询结果来自一个衍生表,其中derived3 中的3代表该查询衍生自di 为3的 select 查询,即 id 为3的select 。{select d1.name…}
- 第二行(执行顺序2):id列为3,是整个查询中第三个select的一部分。因查询包含在 from中,所以为 derived。{select id,name from t1 where other_column=’’}
- 第三行(执行顺序3):select列表中的子查询select_type 为subquery,为整个查询中的第二个select。{select id from t3}
- 第四行(执行顺序1):select_type 为union,说明第四个 select 是union 里的第二个 select,最先执行{select name,id from t2}
- 第五行(执行顺序5):代表从union的临时表中读取行的阶段,table 列的< union1,4>表示用第一个和第四个select的结果进行union操作。{两个结果union操作}
索引优化
索引分析案例
单表
有文章表如下:
此时没有建任何索引。
要求:查询 category_id 为1且 comments 大于1 的情况下,views 最多的 article_id ?
用 explain 分析SQL语句 : type 为 ALL,为全表扫描,即最坏情况;Extra出现了 Using filesort,也是最坏情况。优化时必须的。
开始优化:
1.1 新建索引
一般where 查询条件后面的字段适合建索引,为(category_id,comments,views) 建立复合索引
alter table article add index idx_article_ccv(category_id,comments,views);
#或
create index idx_article_ccv on article(category_id,comments,views);
seq_in_index 是 所以的顺序,等于建立索引的字段的顺序。
type 变成了 range ,使用上了索引,但依然有 Using filesort。B+树查找时,根据最左匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。由于建立所以的顺序式(category_id,comments,views),当匹配到 comments>1 , 是一个范围,停止匹配,后面 排序 views 时依然用不上索引。
1.2
删除上面所建的联合索引 idx_article_ccv,重新优化。
drop index idx_article_ccv on article;
只为(category_id,views) 建立联合索引:
type 变成了 ref ,Using filesort 消失,非常理想。
两表
类别表: class(id,card)
图书表: book(bookid,card)
没建索引前都是全部扫描。
给右表 book表 建索引:
效果不错。
如果给左表 class表 建索引:
效果和不加索引基本一样。
结论:
这是由连接的特性决定的,左连接左表全有,从右边搜索与左表匹配的行,所以需要给右表加索引。
左连接,右表加索引
右连接,左表加索引
三表
类别表: class(id,card)
图书表: book(bookid,card)
手机表:phone(phoneid,card)
没建索引前三个表都是全部扫描。
为book表和phone表建立索引:
情况好转:
索引最好设置在经常查询的字段中。
总结:
join语句的优化
尽量减少 join 语句中 嵌套循环的总次数;永远用小结果集驱动大的结果集;
优先优化NestedLoop 的内层循环;
保证Join 语句中被驱动表上 Join条件字段有索引;
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer 的设置。
避免索引失效
1、最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。(带头大哥不能死,中间兄弟不能断)
2、不在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描
如:EXPLAIN select * from t_score where score_id=3
EXPLAIN select * from t_score where score_id+1=3
EXPLAIN SELECT * FROM staffs WHERE LEFT(name, 4) = ‘July’;
用了left左截取函数(对name左截取4个长度的值为July的,与不写LEFT得出的结果一模一样),结果集返回的与不写left一样,却发现索引失效了。
3、索引中范围条件(>、<、between、in)右边的列索引会失效
4、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *
5、mysql中使用不等于(!=或<>)的时候无法使用索引,导致全表扫描
6、is null , is not null 也无法使用索引
name为主键,会出现下面的 type=NULL
7、like 以通配符开头(‘%ab…’)mysql 索引会失效,变成全表扫描
解决 like ‘%字符串%’ 时索引不被使用的方法 ?
通过覆盖索引,来利用索引
给 (name,age) 建立联合索引
8、字符串不加单引号索引失效
name 为 varchar 类型,当传入 int型时,mysql 会自动转换为 varchar类型,根据第2条 索引上做类型转换会使索引失效。
9、少用 or ,用它连接时会索引失效
练习:
where a=3 and b=5 order by c; a、b用到了,c没用到查找但用到了排序。
索引的作用就是查找和排序。
group by 是分组,但分组之前必排序, 和 order by 索引优化的原则相同。
优化总结口诀
- 全值匹配我最爱,最左前缀要遵守;
- 带头大哥不能死,中间兄弟不能断;
- 索引列上少计算,范围之后全失效;
- LIKE百分写最右,覆盖索引不写*;
- 不等空值还有or,索引失效要少用。
一般性建议
- 对于单值索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠左越好
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的