MySQL学习笔记

这是尚硅谷的笔记：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.建议：