这是尚硅谷的笔记:https://www.cnblogs.com/developer_chan/p/9234769.html
为了给大家更好的学习体验,建议不要看我的垃圾笔记了,上边这个真的是优质的笔记。同意的话回来给我点个赞,推广一下嘻嘻嘻。
一、索引(面面试常被问到)
这篇博客写的不错:https://blog.csdn.net/liutong123987/article/details/79384395
这个博客和我的贼像,因为我们可能是同一个老师https://www.cnblogs.com/qixidi/p/10260180.html
1.索引是什么? -----一种数据结构,提高查找效率。-----用于排序和查找,Java开发基于B+树。
索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为:快速查找排好序的一种数据结构。Mysql索引主要有两种结构:B+Tree索引和Hash索引。我们平常所说的索引,如果没有特别指明,一般都是指B树结构组织的索引(B+Tree索引)。
2.索引的优势
(1)类似大学图书馆书目索引,提高数据检索的效率,降低数据库的IO成本。
(2)通过索引列对数据进行排序,降低数据排序的成本,降低CPU的功耗。
3.索引的劣势
(1)索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列是需要占用空间的
(2)提高了查询速度,但降低了更新表的速度。因为更新(增删改)表时,MySQL不仅需要保存数据,而且需要更新索引表。
(3)索引只是提高效率的一个因素,如果MySQL有数据量很大的表,就需要研究建立更优秀的索引,或优化查询。
4.MySQL索引分类
1>单值索引:即一个索引值包含单个列,一个表可以有多个单列索引。(复合索引一定优于单值索引)
2>唯一索引:索引列的值必须唯一,但可以为null。
3>主键索引:
4>复合索引:1个索引包括多个列。
【建议】一张表不要超过5个索引,但是每次只能使用一个索引。
-------基本语法:
1>创建索引:CREATE [UNIQUE] INDEX indexname ON mytable(columnname(length))
或:ALTER mytable ADD [UNIQUE] INDEX [indexname] ON (columnname(length))
2>删除索引:DROP INDEX FROM mytable;
3>查看索引:SHOW INDEX FROM tablename\G
5.MySQL索引结构(Java只需要掌握BTree索引即可)
1>BTree索引:
2>Hash索引
3>full-text索引
4>R-Tree索引
6.哪些情况下需要建立索引
a.主键自动建立唯一索引
b.频繁作为查询条件的字段应该创建索引
c.查询中与其他表关联的字段,外键关系建立索引
d.频繁更新的字段不适合出啊关键索引,因为更新了记录还需要更新索引。
e.where条件用不到的地方不用创建索引
f.查询中统计或分组字段需要建索引
7.哪些情况下不需要创建索引
a.表记录太少(低于300万条)
b.经常增删改的的表
c.数据重复且平均分配的表字段
二、性能分析
1.MySQL Query Optimizer
MySQL自带的select语句的优化器模块,但不一定最适合本项目。例如淘宝就用自己开发的MySQL Query Optimizer
2.MySQL常见瓶颈
CPU:CPU在饱和时。一般发生在数据表装入内存或从磁盘上读取数据时
IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈:top free iostat和vmstat查看系统的性能状态
3.Explain
https://blog.csdn.net/yhl_jxy/article/details/88636685
1>是什么(查看执行计划):
2>能做什么
表的读取顺序:id的大小决定表的读取顺序。记住:工作中都是小表驱动大表。
数据读取操作的操作类型:由select_type决定。
哪些索引可以使用:possible_keys
哪些索引被实际使用:key
表之间的引用:ref
每张表有多少行被优化器查询:rows,大小越小越好
3>怎么用:explain + SQL语句 末尾加上/G,显示竖榜
执行计划包含的信息,下面将进行各字段的解释
4>各字段解释
1.id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
id相同,从上往下顺序执行。id不同,id越大优先级越高。
2.select_type:simple、primary,subquery,drived、union、union result。用于区分查询类型,
包括普通查询、联合查询、子查询等
3.table:显示这一行的数据是哪张表的
4.type:从好到差依次是system>const>eq_ref>ref>range>index>All
5.possible_keys: 它表示 mysql 在查询时,可能使用到的索引。
6.key:此字段是 mysql 在当前查询时所真正使用到的索引。
7.key_len: 表示查询优化器使用了索引的字节数,这个字段可以评估组合索引是否完全被使用。
8.ref:表示显示索引的哪一列被使用了,如果可能的话,是一个常量。前文的type属性里也有ref,注意区别。
9.rows:估算 sql 要查找到结果集需要扫描读取的数据行数,这个值非常直观的显示 sql 效率好坏,
原则上 rows 越少越好。
10.Extra
1.using filesort :表示 mysql 需额外的排序操作,不能通过索引顺序达到排序效果。一般有 using
filesort都建议优化去掉,因为这样的查询 cpu 资源消耗大。
2.using index:覆盖索引扫描,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错。
3.using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高,建议优化。
4.using where :表名使用了where过滤。
1.using index,索引覆盖。什么是索引覆盖呢?就是,查询的列都在索引里面可以找到,这样就不用去数据库里面查找了。性能全表扫描高。如果这里用到了where条件,那么条件必须是索引的前缀列。============**从索引里面查数据**
2.using where,查询的列没有被索引覆盖,并且where 的条件非索引的前缀列。注意,这个必须有where条件!!!!毕竟是using where嘛,没有where条件怎么叫using where?=========**没有从索引里面查数据**
3.using where,using index: 查询的列被索引覆盖,且where条件非索引前缀列。如果是前缀列,就成了using index了。表达的意思是通过索引覆盖没办法查到所有数据,必须通过表才行。这里的查数据要深刻理解,查数据包括两部分,在哪里查,通过什么条件查。虽然有索引覆盖数据可以通过索引取出,可是查询条件不再索引中,或没办法走索引取数据。======**从索引取数据,但是没有从索引里面查数据**
4.null ,查询的列没有被索引覆盖,但是where条件是索引前缀列。这样的情况下,using index不合适,因为查询的字段没有从索引里面取。using where也不合适,因为where条件么有走索引是 using where,而这里没有using where,所以是走了索引的。====**没有从索引取数据,但是从索引查数据**
5.using index condition,首先排除using index索引覆盖。查询的肯定不是索引覆盖的字段,另外不是using where,说明where的条件不是前缀列,不然就是null了。如果没有where条件,比如select * from 表 ,那么也是null。所以肯定有where条件,where条件要是前缀的话就成了null了。也不对,那就一个sql查询的字段没有索引覆盖,有where条件但是条件不是前缀列。那是什么?有条件但是不是前缀的话就成了using where了。答案是,未被索引覆盖,where条件是前缀列,但是是前缀列 的范围查询。就是说前缀列使用了大于小于等符号。
6.using temporary,使用了临时表来处理数据。这里讲一下临时表和衍生查询,衍生查询侧重的是查询,查询的表来自于select产出的表,不是直接查找数据库的表。临时表是经过处理的表,比如group by,distinct 等。
7.using filesort,文件排序。这里说明没有走索引排序
三、索引优化
1.索引分析
单表优化
两表优化
三表优化
2.索引失效
3.建议: