MySQL复习

MySQL建表可以指定很多存储引擎，InnoDB和MyISAM比较重要。

InnoDB和MyISAM 

不同：

1、InnoDB支持事务。

2、索引不同：InnoDB的主键索引使用聚簇索引，辅助索引是非聚簇索引，MyISAM的索引是非聚簇索引。  聚簇索引（叶子节点存放数据行，数据和索引是放在一起的）：MyISAM的索引是非聚簇索引，叶子结点存数据的地址，索引文件与数据文件是分开的。

3、InnoDB支持外键（实际一般不使用数据库机制的外键约束）。

4、InnoDB支持行级锁和表级锁（行锁在索引上加锁）MyISAM表级。 

7、MyISAM select count(*) from table 很快因为用了一个变量记录了行数，但InnoDB可以在设计表结构的时候适当用冗余字段，来解决这个问题。

注：一般情况默认用InnoDB(事务真的很重要)

MySQL事务

事务要用ACID四个特性来解释。

原子：一系列数据库操作，要么都执行要么都不执行。有失败的操作，就回滚到事务前的数据状态。

一致：事务递交后，使数据从一个状态变成另一个状态，不能被坏数据库完整性约束。1、业务层面（比如余额不能为负数）2、数据库机制层面（比如唯一列约束）

隔离：事务的执行互不干扰，一个事务不能看到其他事务数据的中间状态。

持久：事务完成后，对数据的改变应该是永久的，并保存在磁盘里。

InnoDB实现事务的方式：

其实就是InnoDB怎么保证ACID四个特性

原子：undo log MVCC保证原子性

把修改之前的记录行备份在undo log里面，回滚的话去取原来的这条数据就可以。

一致：基本都是业务逻辑保障，比如余额不能为负数，数据库本身的一致性约束比如唯一约束，字段长度，类型。

隔离：多个事务同时进行，避免事务混乱。

MVCC解决脏读和不可重复读。next_key_lock 或者 gap 锁解决幻读

持久：事务操作记录在redo log 只要redo log 保存成功，buffer pool没保存数据库宕机就不会丢数据。

持久性：redo log 把数据刷进磁盘。

原子性：undo log 可以用于回滚。

隔离性：悲观锁或加上MVCC：

        悲观锁：共享锁、排他锁、间隙锁，控制对数据加锁的种类，加锁和释放锁的时机。

        MVCC：多版本并发控制。只有在读已提交和可重复读两个隔离级别使用

一致性：由三个特性一起保证

InnoDB事务的隔离级别：

sql规范要求各种隔离级别要解决的问题下图

注意：各个数据库对隔离级别实现有差异。

1、InnoDB在“可重复读隔离级别”用间隙锁gap或临键锁next-key-lock解决了幻读。

2、InnoDB所有隔离级别用了长期排他锁 解决了数据库层面的丢失更新（并不是业务层面的）。

InnoDB四个隔离级别会发生的问题和实现方式：

读未提交：会发生 =>  脏读、重复读、幻读。

        更新加长期写锁（事务提交在释放），快照读不加锁。想自己手动加共享锁也可以。

读已提交：会发生 =>             重复读、幻读。

        更新加长期写锁（事务提交在释放），快照读用MVCC，每条查新单独建立视图。读不加锁。想自己手动加共享锁也可以。

可重复度：InnoDB用了间隙锁或邻键锁，所以不会发生幻读，什么也不会发生。InnoDB默认的隔离级别。

        更新加长期写锁（事务提交在释放），快照读用MVCC，整个事务用第一次读建立的视图。读不加锁。想自己手动加共享锁也可以。

        临间锁next-key-lock：行锁（排他锁）+ 间隙锁，锁住多个区间包含记录本身(左开右闭]。

        间隙锁gap：锁住的是记录的间隙，区间不能有记录不包含记录本身(开区间)。

串行(xing)化: 什么都不会发生，所有事务串行执行，但并发效率就低了，一般不用串行化。

        可能是长期写锁+长期读锁+next-key-lock

读写冲突和写写冲突出现的五种问题结合InnoDB：

读写冲突：
脏读：事务T1读（普通读）到事务T2未提交的数据，T2未提交的数据回滚。（这里的读是普通读所以能读到）
不能重复读：事务T1对数据读取（普通读）多次的结果不同，因为事务T2在这个期间修改了这些数据且提交（也是普通读所以能读到）
幻读：事务T1多次读取某个范围的数据（和普通读没关系不是一个维度）记录条数不一致，事务T2在这个期间对这个范围增加或者删除了记录行。（记录之间没有加锁）

写写冲突（InnoDB不会出现）：
第一类丢失更新：事务T1回滚，把事务T2已经提交的数据覆盖（因为加了长期排他锁不会出现）
第二类丢失更新：事务T1提交，把事务T2已经提交的数据覆盖（因为加了长期排他锁不会出现）

数据库锁：

悲观锁和乐观锁

悲观锁：是数据库自己带的锁（数据库已实现）

1、行锁

        1.1共享锁：共享锁和共享锁不冲突，共享锁和排他锁冲突。一行可以加上多把共享锁。

-- LOCK IN SHARE MODE
select * from table where id = 1 LOCK IN SHARE MODE

        1.2排他锁：排他锁与共享锁和排他锁都冲突，只能加一把排他锁就只能他自己读写。（但可以被普通读读到）。

-- FOR UPDATE
select * from table where id = 1 FOR UPDATE

-- insert
insert into table(id,name)
values(1,'zhangsan')

-- update
update table set salary = 5000 where id = 1;

-- delete
delete from table where where id = 1;

注意：

InnoDB的行锁是基于索引来实现的，没有命中索引的就不能用到行锁（ps：怎么地都得尽量走上主键吧）。

不加 lock in share mode （共享）或 for update（排他）  的 select 属于普通查，没有加锁，且可以读到加了排他锁的行。

select * from table

2间隙锁和临键锁

        2.1间隙锁GAP：锁住的是记录的间隙，区间不能存在记录(开区间)不包含记录本身

如果排他锁读的时候，where是范围查询且区间内不存在记录且命中索引，将锁住一个开区间间隙。获得锁的事务释放前，插入更新删除这个间隙区间都会被阻塞。

-- id > 2 and id < 6锁住(2,4)开区间。
-- 注意24中不存在记录才是GAP锁
select * from table where id > 2 and id < 6 FOR UPDATE

-- 区间释放前，insert不进这个区间的数据
insert into table(id,name) values(4,'zhangsan')

        ​​​​​​​2.2邻键锁next-key-lock：行锁（排他锁）+ 间隙锁

如下图：他锁住多个区间，范围中间可以有记录，区间是 (左开右闭] （闭的是记录本身）。

 如下的一条排他锁SQL且id是索引，会锁住(4,7] ，(7,10] ，(10,+∞] 这些区间。

-- 锁住多个索引区间，邻键锁
select * from table where id > 4  FOR UPDATE

乐观锁：需要自己在应用层实现，一般用CAS加上失败重试

在读多写少的情况下建议用CAS

写多读少的情况下建议用悲观锁

因为CAS重试有占用CPU的代价，阿里巴巴开发文档：如果每次访问冲突概率小于 20%，推荐使用乐观锁，否则使用悲观锁。乐观锁的重试次数不得小于 3 次。

InnoDB的MVCC（只能在RC和RR级别用）

当前读（这条记录就不能用MVCC）

select~ lock in share mode(共享锁)
select~ for update(排他锁)

真正加锁的两种select都是当前读。（读取会加锁）读的是记录行的最新数据。（保证其他事务不能对当前记录修改）

快照读（MVCC）MVCC就不能当前读了

select * from table where id = 1

快照读的实现是MVCC

MVCC是什么：

MVCC解决了 脏读，不可重复读。

在RC和RR级别下，为了提高数据库并发性能，读不加锁（不加两种两种读锁）处理 读-写 冲突。

不加锁读取的是：其他事物开始前的数据（假设其他事物已经做了修改但是没有提交）。如果提交了就不会在活跃事物中，（且视图生成时小于即将分配的下一个ID）是可以看的。

大于就是视图生成时，又新出现了事物对读取的这记录做了修改。是不可见的。

更新数据时，会形成版本链表，新的记录的回滚指针指向上一个版本的记录。

快照读记录的时，根据读视图比较事务ID判断可见行，读取的不一定是最新记录行。

实现：

当对一条记录修改的时候。会把当前记录备份一份到他的历史版本。新插入的记录会指向当前这个记录。

依赖每行记录的三个隐式字段：

DB_RTX_ID ：指向创建这条记录、最后一次修改（删除被视为特殊的更新）这条记录的事务ID

回滚指针：指向这条记录的上一个版本

主键ID：没啥用

1、每个事务开启时分配一个单向增长的时间戳事务ID，事务越老ID越小，当作时间戳来使用

2、回滚指针指向 undo log 这条记录的上一个版本，一条一条的形成链表

3、读时，记录当前活跃事务的ID列表、活跃事务最小事务ID、和尚未分配的下一个事物ID生成读视图

4、读视图对比DB_RTX_ID事务ID做可见性判断，找到当前事务能看到的最新版本记录行

读视图：

trx_list:记录正在活跃的事务ID，比如事务4已经提交了，那么活跃的事务剩下1、2、3

up_limit_id:记录活跃事务列表的最小事务ID

low_limit_id:记录读视图生成时，还没分配的下一个事务ID，比如现在下一个分配的ID就是5

读视图可见行比较：

场景：

现有四个事务启动，1、2、3、4（1是最早的事务，4是最晚的）4修改了数据并提交。

        比如：当事务2进行快照读某行记录的时候，为这条记录生成读视图。

这时候读视图数据：

trx_list:1、2、3

up_limit_id:1

low_limit_id:5

先从链表头依次取版本的DB_RTX_ID：DB_RTX_ID = 4 ，如​​​​​​​果 DB_RTX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就回滚指针去undo log取出上一个版本的DB_TRX_ID 往复过程。

        根据比较规则：（事务提交了的数据才能被读取到）

        1、是否 DB_RTX_ID < 最小活跃事务ID，小于 说明这个DB_RTX_ID版本事务已经提交，当前事务2能看到这个版本的记录。

        2、不小于 看是否 DB_RTX_ID ​​​​​​​ >= 尚未分配的事务ID 5 ，大于等于，说明这个版本的DB_RTX_ID是现在读视图生成后出现的事务操作后的版本，这个版本对当前事务2不可见。

        3、小于 看DB_RTX_ID ​​​​​​​是否在活跃事务列表中， 如果在，说明读视图生成时，这个这个版本DB_RTX_ID事务活跃中没递交。这个版本对当前事务2不可见。

        4、如果不在 说明在读视图生成之前，这个版本DB_RTX_ID就已经提交，当前事务2可以看到这个版本的记录。

注意：在RC和RR两个隔离级别下，用读视图的方式不同 ，这也是在两个隔离级别下会不会产生重复读的原因。
已提交读RC ：整个事务用的读视图都是第一个读生成的视图数据。
可重复度RR：事务的每次读都会单独生成视图比较新的视图数据。

undo日志

undo log 分为两种：

insert undo log​​​​​​​：代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
update undo log：事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快照读和事务回滚不涉及该日志时，该对应的日志才会被 purge 线程统一清除。

索引

索引是什么？

在查询数据的时候，如果where条件能命中索引，根据索引列有序组织的B+树来比较条件列，就可以避免取出所有数据，节省性能和时间。

如果满足了覆盖索引，直接在辅助索引返回数据，不用回表了性能会更高。

索引分类？

按照列数来分的话：单列（主键索引）、组合（最左前缀）

按照聚簇索引和非聚簇：主键、辅助。一个真实数据的聚簇索引。很多非聚簇索引。

索引分类回表和索引覆盖

回表：辅助索引找到主键索引值，再去主键索引去找真正数据行

索引覆盖：查询的数据在非主键索引都能找到，直接返回，不用去聚簇索引查数据

索引数据结构：

一个表必须有一个聚簇索引来组织，规则:主键 > 唯一列 > 隐藏字段rowid（6字节）。

InnoDB索引数据结构： B+树
B+树在IO上和范围检索上都比B树更好：

1、 B+树的非叶子结点不存储数据而是当作索引来使用（非叶子结点就小了）这样MySQL在一页能够读取更多的非叶子节点，一次页读取了更多的索引和指针做判断，降低IO次数。

2、 B+树的叶子节点有一条DATA链表在做范围查询时只需要遍历这条链表，而B树需要返回父节点在 指向他们的另一个子节点

三层B+树能存多少记录？

InnoBD一页16K  B+树一个结点16K（也可以是16k整数（>1）倍）

B+树非叶子结点存的是，Key索引+ 加孩子结点的地址指针

key大小根据类型（bigint 8字节）。地址指针6字节。一共就是14字节。

所以非叶子结点一个结点能存： 16 * 1024 / 14 = 1170个索引+指针。

叶子结点存数据（假如一行数据大小 = 1k ， 一个叶子结点就能存：16k / 1k = 16 条数据）

这样高度为 3 的B+树能存：1170 * 1170 * 16 = 2千多万条数据

1170（根结点） * 1170（每个根节点的根结点） * 16（每个叶子节点的数据行） = 2千多万条数据

最多三次IO（数的高速）就能读到目标数据

补充：

int类型：4字节，表示范围是21亿多，所以能表示21亿多条数据。

bigint类型：8字节，表示范围是2^63减1 比千亿还多。

B+ 树不一定是三层，数据量少可以是两层，一般三层，最多四层。过亿的数据应该分库分表。

索引建立语句，和索引建立原则：

索引建立方法：

1、创建表的时候顺便创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`name` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	PRIMARY KEY (`id`),
	INDEX index_name (name(length))
)

2、在已建表中直接创建索引

CREATE INDEX index_name ON table(column(length))

3、修改表结构ALTER的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

原则：

查询条件列 where、表连接列 join、排序列order by适合建立索引。
每个表中建立索引的个数最大约为5个， 因为在更改插入记录行的同时索引也要同步的维护
区分度小的列不易建立索引（影响分叉）
如果需要建立 A、AB、ABC三个索引，那么只用一个ABC就好了，ABC可以当A和AB来使用。

假设有ABC索引，A、AB、ABC、ACB（优化器调整）都能用。B、BC、（A找不到，不能严格有序没，就找不到B）AC（BC用不上，但是）

索引条件下推：

5.7之后 场景：在复合索引ABC中，如果where条件是 A and B like 'xx% ' and C ，C就会失效。

根据最左前缀匹配原则：二级索引B+树在组织时（先左值后右值：先按照A字段排序，A排完了在根据A的等值排B，类推）如果索引是ABC。A等值了B有序可以找到B，但B遇到了范围查询或者模糊查询时不等值，C就无序了，C就会失效。 

MySQL三层架构：客户端 （可视化工具、代码）、Server 、 存储引擎 。

主要作用：符合条件的数据在存储引擎过滤，而不是，返回给server来过滤

不用索引下推过程：在二级索引中，找到所有的符合A和B的主键，再去主键索引中回表，去取出包含不满足C的记录行，然后把所有数据返回给MySQLServer层在MySQLServer层判断去除不满足C的记录。

采用索引下推过程：索引条件下推到InnoDB存储引擎层，在二级索引中，找到所有的符合A和B的和C的主键，在二级索引中直接判断去除的不属于C的主键，回表取出的都是满足条件的记录。

好处：直接在二级索引中判断不满足条件的主键，减少了磁盘取出记录的数量就减少了IO，返回给Server层的数据也少了。

join底层原理

        内连接：默认左表为驱动表（主表）。

但是，优化器不一定这么认为，所以优化器也可能认为右表为驱动表。

我们可以用 explain 执行计划 可以查看（表出现在上面的是主表，下面的是从表）。

        外连接：也是查询优化器判断哪个是主表，

--内连接
--默认左表A是驱动表
select * from A join B ON A.id = B.id


--外连接
select * from A left join B ON A.id = B.id
select * from A right join B ON A.id = B.id


--条件不同，会根据条件判断谁是驱动表。
explain select * from a left join b 
on a.f1 = b.f1 where a.f2 = b.f2;

explain select * from a left join b 
on a.f1 = b.f1 and a.f2 = b.f2; 


--关键字 straight_join 不根据优化器判断 强制左表A是驱动表
select * from A straight_join B ON A.id = B.id

5.5 版本之前（简单嵌套循环Simple Nested Loop Join）（不说）

5.5 之后(BNLJ)(块嵌套循环Block Nested Loop Join)

        join buffer（默认大小 join_buffer = 256k）

把主表的行存进join buffer ，在全表扫描从表比较。

如果join buffer不能一次存下主表相关列，就下次在存，剩下的在全表扫描从表比较。

优化器遵循：永远用小结果集驱动大结果集。

结果集大小：（表行数 * 每行大小）

        场景：

1、假如表 A 有100条数据，B 有1000条数据，主表选谁？

        选择表 A 作为主表合适（因为A结果集小）

2、假如表 A有100条数据，B 有1000条，加了条件 where B.id<=50 过滤后只有50条数据符合条件。选谁当主表？

        选择表 B 作为主表合适（因为B结果集小）

        优化：

1、减少主表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）。

2、可以适当增加 join buffer 大小让主表相关列一次性加载进去。

3、从表匹配的条件可以增加索引（INLJ）。

注意：整体效率比较：INLJ （从表加索引）> BNLJ > SNLJ

order by底层原理

        ​​没有命中索引时​需要额外排序：

排序字段没有命中索引 ： 就需要放到sort_buffer里面快排。sort_buffer：排序缓冲大小

排序在内存中完成 ：若待排序数据量 < sort_buffer_size，就在内存中排序。

sort_buffer不能放下所有排序字段 ：需要借助磁盘进行外部归并排序（同等情况下sort_buffer越小，分成的外排序段越多 ）。

全字段排序：避免select *

场景：没命中索引，且直接从主键取整行记录取所有字段，放在sort_buffer里一起参与排序（这样sort_buffer能存下的记录行会很少，外排序段会很多）所以若单行很大，该算法的效率不行

rowid排序：

排序字段长度 max_length_for_sort_data = 16 若单行长度超过该值字节且不是TEXT/BLOB。换成rowid排序

场景：没命中索引，且单行排序行数据长度。

算法：把参与排序的字段和id放进 sort_buffer ，这样sort_buffer就能放入更多行，在排序完成后，在用排好的值回到原表取数据。

需要排序情况总结：

内存够大的话，最好sort_buffer大一点，全字段排序不用回表，尽量减少磁盘访问。

内存不大的话，用rowid排序，一次可以排序更多行，但最后需要回表取数据，磁盘访问多。

        ​​​​​​​不需额外排序：

场景：命中了索引，（因为索引本身就是有序），且不用多读limit之外的数据。

字段：id、city、name、age

索引： index（city ， name）

--sql
select  city,  name, age  where  city = ‘北京’ order by name  limit 1000

这时候，因为索引就是按照最左前缀有序组织的，在找到city找到后，name是严格有序的，所以直接在索引种取到了前1000条（不用多读索引）满足条件的主键ID，回表就行了。这是最优的

嵌套查询时in 和exist​​​​​​​底层区别?

查询时应该遵从小结果集驱动大结果集。

应该减少查询数据库的次数。

查询数据库应该走索引。

in 会先查里表（里表当驱动表），exist 会先查外表（外表当驱动表）

in 执行流程：

select * from A
where A.id 
in( select a_id from b )

先把里表查一次数据库（里表就查1次）（有索引会走索引）里表结果集在内存生临时表。

结果集临时表的记录如果有m个，每一行代入外查询执行m次，走数据库（有索引会走索引）。

exist 执行流程：

select * from A 
where exist(
    select * from B.id=A.id
)

会先查外表当前这行一行的记录（有索引会走索引），

外表当前这行记录被当作子查询的条件，执行一次子查询（有索引会走索引），

子查询能返回记录，保留外表当前记录。子查询不能返回记录，丢弃外表当前记录。

（外表记录loop到每一行，子查询都去数据库查一次）

补充：以上情况A表有n条记录，loop执行n次。

下面的情况，外层表 A 因为走了city的索引结果集变小了。loop的结果集并不是N。

select * from A 
where city = '上海'
and exist(
    select 1 from B.id=A.id
) 

总结：​​​​​​​

exists和in是用在不能同场景的，没有谁比谁效率高。

in：子查询的结果集记录少，外查询中的表大且有索引时。

exsit：外层的结果集记录少，子查询中的表大且有索引时。

补充：

in语句中不会对null值进行匹配，即查询不到null值记录

比如sql的意思如果记录 class为null 也要查出来，

select * from class 
where class in (null,'1')

这时候结果是 null值的class查不出来。

limit10 和limit 50000 , 10效率一样吗?

不一样

limit 50000 , 10是查出 50000+10的记录，然后丢弃前50000条（InnoDB会返回给server50000条无用数据）。

解决分页深度：

先取 按照id排序的 50000+1 的 id (这时候存储引擎返回给server的只有前50001的id很小)

然后查直接根据 where 定位从 > 50000 + 1 ID 开始读10条。(这个定位是走索引的)

select * from A
where id >= (
    select id from A 
    order by id 
    limit 50000 , 1
)
order by id 
limit 10

（备注：ID得是自增的）

Explain执行计划

先看表头的字段的含义

重要的有 ID、type、key、rows、Extra

ID ：表的执行顺序，  select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下 

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 

id相同不同，（两种情况同时存在） ，d如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行 

select_type：查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

1、SIMPLE：         简单的select查询，查询中不包含子查询或者union（表连接） 
2、PRIMARY：     查询中包含任何复杂的子部分，最外层查询则被标记为primary 
3、SUBQUERY： 在select 或 where列表中包含了子查询 
4、DERIVED：     在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里 
5、UNION：        若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived 
6、UNION RESULT：  从union表获取结果的select 

table：当前数据是属于哪张表的 ，derived+id是临时表

type：访问类型 ，sql查询优化中一个很重要的指标，

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，最好能达到ref

1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略

2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where中，mysql就能将该查询转换为一个const 

3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。  

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体 

5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引 

6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL快，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取） 
7、ALL：Full Table Scan，遍历全表以找到匹配的行 
 

possible_keys：查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key：实际使用的索引，如果为NULL，则没有使用索引。

 查询中如果覆盖索引上了，则该索引名仅出现在key中 （possible_keys为null，key用到了）

key_len：表示索引中使用的字节数（用到的所有索引的字节加起来），查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

注意：比如联合索引里，根据这个字节数看看根据类型加法算一下是不是全用上了。

ref：（不是表头Type里的值）显示索引的哪一列被使用了（引用了），如果可能，是一个常量const（比如 = ‘具体常量’）。比如索引名字的name列被引用了。

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra：不适合在其他字段中显示，但是十分重要的额外信息

1、Using filesort ： 使用额外排序，而不是用索引进行排序。也就是说mysql无法利用索引完成的排序 操作成为“文件排序” 

现有索引A，B。由于索引是先按 A 排序、再按 B 排序，所以查询时如果直接按 B 排序，索引就不能满足要求了，mysql内部必须再实现一次“文件排序”

2、Using temporary： 使用临时表保存中间结果，出现了不好。也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by 

注意：order by 和 group by 都能用到索引，要学习一下。

3、Using index： 表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 
         如果同时出现Using where，表明索引被用来执行索引键值的查找
         如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作 

覆盖索引（Covering Index）：就是select列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。 
注意： 
a、如需使用覆盖索引，select列表中的字段只取出需要的列，不要使用select * 
b、如果将所有字段都建索引会导致索引文件过大，反而降低crud性能

4、Using where ： 使用了where过滤

5、Using join buffer ： 使用了连接缓存（可以适当调大）

6、Impossible WHERE： where子句的值总是false，不能用来获取任何元组

7、select tables optimized away： 
在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

8、distinct： 
优化distinct操作，在找到第一个匹配的元组后即停止找同样值得动作
 

例子：借鉴尚硅谷的视频

关于SQL优化：

索引建立的时候注意顺序

遇到范围后面的会失效

goub by 和 order by 能用上索引

Join语句的优化

表连接，索引一般加在非驱动表

尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。

优先优化NestedLoop的内层循环；

保证Join中非驱动表上Join条件字段已经有索引

当保证Join中非驱动表上Join条件字段已经有索引且内存资源充足的前提下，适当增大JoinBuffer

order by语句的优化

现有索引ABCD

where A and B order by D（错，额外排序）

where A and B order by C（C用了索引，索引直接有序）

where A and E order by B,C（B，C都用了索引）

where A and E order by C,B（X）

where A and B and E order by C,B（B，C用了索引，相当于ABC，然后回去找B）

where A and  E order by C,B（X）

where A and B order by B,C（B，C用了索引，就相当于ABC重复用了B）

where A and  B>  order by C（X）

order by B（X,A带头大哥没了，会显示用到了覆盖索引，和用到了额外排序）

where B>  order by A（可以，A带头大哥出现B也用上了索引）

where A>  order by B （X，带头大哥是范围）

order by A ASC ，B DESC（X，5.7是不同时升降序都有的）

where A   order by B,E（X，E没有索引，要用额外排序）

总结：

要么order by ABCD 自己带头大哥

要么where 是常量时，order by 接上能使用索引 

select *  容易把 sort_Buffer 用满

group by语句的优化

where A and  D group by B,C（X，D没有用上）

where A and  D group by C,B（X，BCD没用上，使用了临时表，使用了额外排序）

总结：索引定值为常量，范围之后是失效，最终看最左排序

group by与order by失效基本一致（因为分组前必排序），唯一不一致的是group by有having

能在where解决的不要再having解决

小结果集驱动大结果集

for(5){//buffer能装下
    for(100){//加索引
        //这种适合MySQL
    }
}
for(100){
    for(5){

    }
}

虽然循环次数都一样，但是第一种更合适

in（里是小结果集）

select * from A where id in (select id from B)

外是小结果集 exists ()

select * from A where exists (select 1 from e where B.id = A.id)

注意：A表与B表的ID字段应建立索引

上面讲 的更详细。

索引失效

1. 全值匹配我最爱
2. 最左前缀法则 （丢失第一列/中间列缺失 ，顺序没事）
3. 不在索引列上做任何操作（计算、函数、（自动/手动)类型转换），会导致索引失效而转向全表扫描
4. 范围条件（> 、 between ）右边的列不能使用索引（范围的这个列可以用到索引）
5. like '字符串%'  右边的列可以用到索引（本like列当然也能用到索引）
6. like '%字符串'  以通配符开头这个列直接用不到索引
7. select * 不会使用覆盖索引(只访问索引的查询(索引列和查询列一致）），
8. mysql 在使用不等于(!=或者<>)， is null ,is not null 也无法使用索引 
9. 字符串不加  ‘单引号’  索引失效
10. 少用 or 用它来连接时会索引失效
11. 如果查询数据量超过整个记录行30%，会认为走全表扫描更快。（可以用force index（索引名）） 但是不建议这样用，因为索引名修改后，sql容易忘了维护。