概述
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 MODE1.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 table2间隙锁和临键锁
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字段应建立索引
上面讲 的更详细。
索引失效
- 全值匹配我最爱
- 最左前缀法则 （丢失第一列/中间列缺失 ，顺序没事）
- 不在索引列上做任何操作（计算、函数、（自动/手动)类型转换），会导致索引失效而转向全表扫描
- 范围条件（> 、 between ）右边的列不能使用索引（范围的这个列可以用到索引）
- like '字符串%' 右边的列可以用到索引（本like列当然也能用到索引）
- like '%字符串' 以通配符开头这个列直接用不到索引
- select * 不会使用覆盖索引(只访问索引的查询(索引列和查询列一致）），
- mysql 在使用不等于(!=或者<>)， is null ,is not null 也无法使用索引
- 字符串不加 ‘单引号’ 索引失效
- 少用 or 用它来连接时会索引失效
- 如果查询数据量超过整个记录行30%，会认为走全表扫描更快。（可以用force index（索引名）） 但是不建议这样用，因为索引名修改后，sql容易忘了维护。
最后
以上就是现代纸飞机为你收集整理的MySQL复习InnoDB和MyISAM MySQL事务InnoDB实现事务的方式：InnoDB事务的隔离级别：InnoDB四个隔离级别会发生的问题和实现方式：读写冲突和写写冲突出现的五种问题结合InnoDB：数据库锁：InnoDB的MVCC（只能在RC和RR级别用）undo日志索引三层B+树能存多少记录？索引建立语句，和索引建立原则：索引条件下推：join底层原理order by底层原理嵌套查询时in 和exist底层区别?limit10 和limit 50000 , 10效率一的全部内容，希望文章能够帮你解决MySQL复习InnoDB和MyISAM MySQL事务InnoDB实现事务的方式：InnoDB事务的隔离级别：InnoDB四个隔离级别会发生的问题和实现方式：读写冲突和写写冲突出现的五种问题结合InnoDB：数据库锁：InnoDB的MVCC（只能在RC和RR级别用）undo日志索引三层B+树能存多少记录？索引建立语句，和索引建立原则：索引条件下推：join底层原理order by底层原理嵌套查询时in 和exist底层区别?limit10 和limit 50000 , 10效率一所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复