MySQL进阶学习1

索引

学习地点：B站：BV1KW411u7vy

索引（index）：是帮助MySQL高效获取数据的数据结构，它是一个数据结构，可以简单的理解为“排好序的快速查找数据结构”

优点：
1、提高数据检索的效率，降低数据库的IO成本。
2、通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：
1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息。
2、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

MySQL的索引

1.Btree

一颗 b 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）

查找过程：

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指 针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

2.B+tree

B+Tree 与 B-Tree 的区别

1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；
B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

2）在 B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+树中每个记录 的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

在实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据， 这样每个节点可容纳的元素个数比 B-树多，树高比 B-树小，这样带来的好处是减少磁盘访问次数。尽管 B+树找到 一个记录所需的比较次数要比 B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+树的性能可能还会好些，而且 B+树的叶子节点使用指针连接在一起，方便顺序遍历

思考：为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？

1)B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

2)B+树的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

时间复杂度：

Mysql 索引分类

3.1 单值索引
概念：即一个索引只包含单个列，一个表可以有多个单列索引

3.2 唯一索引
概念：索引列的值必须唯一，但允许有空值

3.3 主键索引
概念：设定为主键后数据库会自动建立索引，innodb为聚簇索引

3.4 复合索引
概念：即一个索引包含多个列

3.5 基本语法

索引的创建时机

4.1 适合创建索引的情况
1、主键自动建立唯一索引；
2、频繁作为查询条件的字段应该创建索引
3、查询中与其它表关联的字段，外键关系建立索引
4、单键/组合索引的选择问题， 组合索引性价比更高
5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6、查询中统计或者分组字段

4.2 不适合创建索引的情况
1、表记录太少
2、经常增删改的表或者字段
3、Where 条件里用不到的字段不创建索引
4、过滤性不好的不适合建索引

Explain关键字

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的SQL语句的。

1.id

id大的先执行，相同id顺序执行

2.select_type(查询类型)

3.table

这个数据是基于哪张表的。

4.type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
一般来说，得保证查询至少达到 range 级别，最好能达到 ref

5.possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一
定被查询实际使用

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

7.key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的 利用上了索引。ken_len 越长，说明索引使用的越充分。

8.ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

9.rows
rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

10.Extra
其他的额外重要的信息。

12.1Using filesort（需优化）
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引 完成的排序操作称为“文件排序”。

12.2Using temporary（需优化）
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 orderby 和分组查询 group by。

12.3Using index(好事)

Usingindex 代表表示相应的 select 操作中使用了覆盖索引(CoveringIndex)，避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是
用来读取数据而非利用索引执行查找。

12.4Usingwhere
表明使用了 where 过滤。

12.5Usingjoinbuffer
使用了连接缓存。

12.6impossiblewhere
where 子句的值总是 false，不能用来获取任何元组。

单表使用索引常见的索引失效

1.1全值匹配
#创建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

这三条都可以使用索引

1.2 最佳左前缀法则
带头大哥不能死，中间兄弟不能断

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无
法被使用。

Tips：主键默认是索引，就是即使创索引时没有主键，但是查询是他也会调索引。

3. 不要在索引列上做任何计算

不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换） ，会导致索引失效而转向全表扫描。

3.1 在查询列上使用了函数

结论：等号左边无计算！

3.2 在查询列上做了转换（var类型要加单引号）
#创建索引
create index idx_name on emp(name);
结论：等号右边无转换！

4. 索引列上不能有范围查询
建议：将可能做范围查询的字段的索引顺序放在最后

5. 尽量使用覆盖索引
尽量别用*

6. 使用不等于(!= 或者<>)的时候

mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。
7. 字段的 is not null 和 is null

is not null 用不到索引，is null 可以用到索引。

8.like 的前后模糊匹配

前缀不能出现模糊匹配
like百分写最右

9. 减少使用 or

使用 unionall 或者 union 来替代

关联查询优化

永远小表驱动大表

1. left join

优化前：
建右表索引：

alter table class add index idx_card(card);

结论：
①在优化关联查询时，只有在被驱动表上建立索引才有效！
②left join 时，左侧的为驱动表，右侧为被驱动表！

通俗易懂的话说：左连接，无论怎样你左表都必须遍历，那你索引加在左表有意义吗，显然是加在右表啊，如果不加右表的话那右表也得遍历。

同理：右连接加左表索引！

2.2 inner join

结论：innerjoin 时，mysql 会自己帮你把小结果集的表选为驱动表。

2.3子查询

子查询放在驱动表上：
子查询放在被驱动表上：

上述两个案例，第一个查询效率较高，且有优化的余地。第二个案例中，子查询作为被驱动表，由于子查询是虚表， 无法建立索引，因此不能优化。

Tips:能关联就直接关联了，别用子查询。。

结论： 子查询尽量不要放在被驱动表，有可能使用不到索引； left join时，尽量让实体表作为被驱动表

排序分组优化

无过滤，不索引

oredr by 和 group by

。。不知道怎么写，直接写结论吧

order by:
1、排序按顺序排就不会有filesort
2、如果不按顺序，或者带头大哥无了，必会filesort
（方向反，顺序错，必排序）

group by:
1、其实基本和order by一样
2、有一点差别就是，能在where中搞定的就不要having了

MySQL4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 orderby 列，对他
们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序，从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出。

截取查询分析（了解）

1.慢查询日志

（1）MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具 体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
（2）具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句。
（3）由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

2. 日志分析工具 mysqldumpslow

3.show profile
利用 showprofile 可以查看 sql 的执行周期！

查看 profile 是否开启：show variables like ‘%profiling%’;

如果没有开启，可以执行 set profiling=1 开启！

执行 show prifiles 命令，可以查看最近的几次查询。

根据 Query_ID,可以进一步执行 show profile cpu,block io for query Query_id 来查看 sql 的具体执行步骤。

4.全局查询日志

必须在测试环境才进行

MySQL锁

分类：
表锁（偏读）
行锁（偏写）
页锁

1.表锁

表锁偏MyISAM存储引擎，开销小，加锁快；无死锁；锁定程度大；锁冲突概率高；并发度低

show open tables;	#查看表上的锁
lock table mylock read,book write;		#给mylock加上读锁，book加上写锁
unlock tables；		#解锁

读锁：只能读自己，也可以共享读，但不能对自己update，也不能读别的表。
写锁：对自己可读可写，但不能读别的表，没解锁前也阻塞。

读锁会阻塞写，但不阻塞读，写锁读写都阻塞

2.行锁
偏InnoDB存储引擎，开销大，加锁慢；锁定程度小；锁冲突概率低；并发度高

3.页锁

间隙锁的危害：
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙

InnoDB也会对这个间隙加锁，这就叫间隙锁。

怎么加行锁？

set autocommit =0;
begin;
select * from test_innodb_lock where a=8 for update;
commit;

检查InnoDB_row_lock状态变量来分析行锁

show status like 'innodb_row_lock%';