MySQL

一、 索引

1. 为什么要使用索引

海量数据中，查询某条记录的场景经常发生，给要查询的数据字段加索引后，查询会非常快。

 

2. 索引是什么

索引 = 数据目录

字典——目录

没有索引的时候会全表扫描，多个IO读写开销

索引 键值对 存储具体磁盘上的位置

为数据库的某个字段创建索引。相当于为这个字段的内容创建了一个目录。

通过这个目录可以快速定位到数据所在的位置。

问题？

• 索引存放位置
• 索引分类和如何创建
• 索引为什么能比查磁盘快？——数据结构（B+树 查询快）

 

3. 索引存放位置

mac: /usr/local/mysql

win: C:/programdata/mysql

innoDB：索引跟数据存放在一起 .ibd

MyISAM：索引和数据分开两个文件存储 索引：.MYI ;数据 .MYD

 

4. 索引分类和创建

1. 主键索引

   • 主键自带索引

2. 普通索引

   • 创建索引命令：

     create index 索引名称 on 表名(列名)

     create index idx_name on employees(name)

3. 唯一索引

   • 就像唯一列，列中数据唯一

   • create unique index idx_name on employees(name)

4. 组合索引（联合索引）

   • 一次性为表中多个字段一起创建索引，最左前缀法则（如何命中联合索引中的索引列）
   • 建议不要超过5个字段
   • create index idx_name_age_position on employees(name,age,position)

5. 全文索引

   • MyISAM存储引擎支持全文索引。 实际生产中，并不会使用这个来全文查找

     而是使用第三方的搜索引擎中间件（ES）

 

二、 索引数据结构

使用索引查找数据性能很快，避免了全表扫描多次磁盘IO

索引使用B+树

为什么不用红黑树？————一层存放的数据有限（二叉） 深度会非常大

MySql默认下一结点的大小是16k

B树：

​ 一个键值对是8b+1k=1032k

​ 所以一个结点可以存放 16*1024/1032 = 15个

B+树：

• 非叶子结点冗余了叶子结点中的键

• 叶子结点是从小到大、从左到右排列的

• 叶子结点之间提供了指针，提高了区间访问性能

• 只有叶子结点存放数据，非叶子结点是不存放数据的，只存放键： 非叶子结点能存放更多数据

• 非叶子结点存放键和指针域 8b+6b = 14b

• 所以一个非叶子结点最多可以存放 16*1024/14 = 1170 个索引键

  • 下一层可以存放1170 * 1170 个

• 一个结点最多能存放15个索引（键值对

• 只要查询几层就能找到数据

---

hash表查询更快，为什么不用？

• 不连续 不支持范围查找

---

索引常见面试题

关键词： 性能、维护、开销

1. 什么非主键索引的叶子结点存放的数据是主键值

主键索引，叶子结点存放的是完整数据

普通索引，叶子结点存放的是主键值（id）

1. 数据冗余，空间浪费，虽然提高了性能，但需要更多空间
2. 维护难—数据改动的时候多个索引树都要改动

2. 为什么InnoDB表必须创建主键

没有主键也可以创建表，也可以为这张表创建普通索引

如果没有主键，mysql优化器会给一个虚拟的主键，普通索引存放的就是虚拟主键 — 但是这也需要额外开销，为了性能考虑和设计初衷，创建表的时候应该创建主键

3. 为什么使用主键时推荐使用整数类型自增主键

整型

主键- 主键索引树 - 树里的叶子结点和非叶子结点的键存放的是主键的值

数据的存放是有大小顺序的

• 整型： 大小顺序好比较
• 字符串： 自然顺序的比较是要进行一次编码成为数值后再比较

自增

如果不用自增： 使用不规律的整数，主键索引树会使用更多的自旋次数来保证从小到大，所以性能会更差

---

三、InnoDB和MyISAM区别

实际开发中，追求查询性能用 InnoDB

MyISAM：

* 索引和数据分成两个表了，查询麻烦
* MyISAM天然支持表锁，而且支持全文索引（性能不ok

InnoDB：

• 聚集索引
• 索引和数据存放到同一个文件中，找到索引后就能直接在索引树上的叶子结点获得完整数据

 

四、 联合索引和最左前缀法则

1. 联合索引特点

使用一个索引来实现多个 表中字段 的索引效果

2. 联合索引是如何存储的

跟普通索引相似，只是每个节点中的键有多个值（列）

3. 最左前缀法则

最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引 / 不全表扫描）

 

五、 SQL优化

为了sql语句具备更优秀的查询性能，有很多途径可以做到：

• 工程优化：数据库标准、表的结构标准、字段标准、创建索引
• SQL语句优化： 有无命中索引

1. 工程优化

2. Explain执行计划——sql优化神器

通过对Explain工具可以对当前sql语句的性能进行判断，是否命中索引

1. select_type

• simple：简单查询（不包含子查询
• primary：外部的主查询
• derived：在from后面进行的子查询，会产生衍生表
• subquery：在from前面进行的子查询
• union：进行的联合查询

2. table

​ 正在查询的是哪张表

3. partitions

4. type

​ 可以直观的判断出当前sql语句性能。

​ type的取值和性能的优劣顺序如下

null> system > const > eq_ref > ref > range > index > all

​ 对于sql优化来说，要尽量保证type列的属性是range及以上

• null：性能最好；一般在使用聚合函数操作索引列，结果直接从索引树获取即可
• system： 少见。直接和一条记录进行匹配
• const： 常见。使用主键索引或唯一索引和常量进行比较，这种性能好
• eq_ref： 多表连接查询时，如果查询条件使用了主键比较，进行关联
• ref： 简单查询（查询条件用了普通列索引）； 复杂查询（查询条件使用了联合索引列）
• range： 在索引列上用了范围查找
• index： 所有记录都是从索引树上获取
• all： 全表扫描

5. possible_keys

​ 可能用到的索引

​ mysql内部优化器会进行判断，如果这次查询走索引的性能比全表还差

​ 内部优化器就让此次查询进行全表扫描——依据可以通过trace工具可以查看

6. key

​ 实际用到的索引

7. key_len

​ 键的长度，通过这一项可以知道当前命中的是联合索引中的哪几列（char是n；varchar是3n+2）

8. ref

9.rows

​ 可能要查询到的行数

10. filtered

11. Extra

​ 能帮助我们判断是否使用了覆盖索引，文件排序，使用了索引进行的查询条件等

• using index：
  • 使用了覆盖索引（尽量使用
  • 覆盖索引： 指当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据内容，而不需要进行查表（又要走一遍
  • 使用覆盖索引进行性能优化是经常使用的
• using where：
  • 使用了普通索引列做查询条件
  • where的条件没有使用索引列，这种性能是不OK的
  • 如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引
• using index condition
  • 查询结果没有使用覆盖索引，可以用覆盖索引来优化
• using temporary（临时
  • 在非索引列上进行去重操作，就需要一张临时表来实现
  • 性能差
• using filesort
  • 性能不好
  • 使用文件排序，会使用磁盘+内存的方式进行排序
  • 涉及两个概念： 单路排序，双路排序
• select tables optimized away
  • 直接在索引列上使用聚合函数，意味着不需要操作表

12. id

​ id越大越先执行

---

优化——目的：命中索引

使用is null、is not null、 != 、 <> 、like以通配符开头(’%abc’)、字符串不加单引号

都会导致全表扫描

如何解决like通配符问题？ ： 覆盖索引、中间件ES

少用or或in，mysql内部优化器可能不走索引

in(10w) 解决办法：将10w进行拆分，一次查1000条，查100次，业务层使用多线程

最后整合结果CountDownLatch

范围查询优化： 范围太大可能会变成全表查询 用时间多了很多2s —> 0.01s

select * from emp where age >=1 and age <=20000;

拆分优化：

select * from emp where age >=1 and age <=10000;

select * from emp where age >=10001 and age <=20000;

 

六、 Trace工具

有时候会发现即使明确使用了索引，mysql也不走索引，这是因为mysql内部优化器认为走索引比全表扫描还慢

用Trace工具可以看为什么选择这个

 

七、 SQL优化

1. order by优化

排序场景中很容易出现文件排序 filesort，文件排序会对性能造成影响，所以需要优化

-- 联合索引：name,age,position

1. 如果排序的字段创建了联合索引，尽量在业务不冲突的情况的，遵循最左前缀法则来写排序语句
select * from emp where name='a' order by position;		--文件排序
优化： select * from emp where name='a' order by age,position;--联合索引
把age也加上，能够联合索引，但又不会对结果造成影响，反而能更快

2. 如果使用了 in > 等范围，会导致文件排序，没办法避免

优化：尽量想办法覆盖索引： type从 all => index

2. group by

原理是先排序后分组，优化跟order by类似

3. 文件排序原理

执行文件排序的时候会将查询数据大小和max_length_for_sort_data 比较

mysql内置的max_length_for_sort_data 默认大小为1024字节

单路排序

• 如果要查的表数据小于它，则直接把所有数据放到内存缓存区中进行排序，排序完直接返结果

双路排序

• 如果要查的表大于这个1024值，则取查询的排序字段（列）和主键字段（id），

• 这两列放到内存缓存区中排序，

• 再将主键字段做一次回表查询，返回完整结果

4. 分页优化

对于这样的查询，mysql会把前面100010条数据都拿到，再舍弃前面100000条

select * from emp limit 100000,10

优化：

主键连续情况下，可以用主键来做条件（少见

select * from emp where id>100000 limit 10

主键不连续情况下，先进行覆盖索引（先获得id），再join做连接查询获取所有数据，比全表扫描快，

select * from emp order by name limit 100000,10
优化：		会进行全表扫描，但全表只有10条
select * from emp a inner join (select id from emp order by name limit 100000,10) b
on a.id = b.id;

5. join 优化

在join中会涉及到大表小表概念（数据量）

mysql内部优化器会根据关联字段是否创建了索引来使用不同算法

使用了索引

nlj（嵌套循环算法）： 对小表做全表扫描，再用小表的数据去和大表的数据去做索引字段的关联查询

没使用索引

bnlj（块嵌套循环算法）： mysql提供一个join buffer缓存区，先把小表放到缓冲区中，然后全表扫描大表，然后把大表数据和缓冲区中小表数据在内存中进行匹配

结论： 使用join查询时，一定要建立关联字段的索引，且两个表的关联字段在设计之初就要做到字段、长度一致，否则索引失效

6. in和exists优化

遵循小表驱动大表原则

A是大表，B是小表，使用in合适 反之使用exists

要查A表数据，A是大的用in；A是小的用exists

in：B表数据 < A表数据

select * from A where id in (select id from B)
# B的数据量少，循环次数就少

exists: B表数据 > A表数据

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

7. count优化

count优化应该在架构层面优化，因为count经常出现

用缓存 redis

 

八、 锁的定义和分类

1. 锁的定义

锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。

锁虽然安全，但是影响性能。

2. 锁的分类

1. 从性能上划分： 乐观锁和悲观锁

• 悲观锁：悲观的认为当前的并发是非常严重的，在任何时候操作都是互斥的。保证安全，牺牲并发性
• 乐观锁：乐观的认为当前并发不严重。读，大家都可以读；写，再进行上锁 （天网恢恢疏而不漏）
  • CAS自旋锁：某种情况下性能ok，但是频繁自旋会消耗很大资源。

2. 从数据的操作细粒度上划分： 表锁和行锁

• 表锁： 整张表上锁

  • MyISAM天然支持表锁，但是不支持事务。

  # 创建读写锁
  lock table 表名 read/write;
  # 查看所有所的上锁情况
  show open tables;
  # 释放当前对话所有所
  unlock tables;
  

• 行锁： 对表中某一行上锁

  • 在并发事务里，每个事务的增删改的操作相当于是上了行锁

  #上行锁
  update 表 set name='zzt' where id =8; # 对id=8的这行上行锁
  

3. 从数据库的操作类型上划分： 读锁和写锁（都是悲观锁

* 读锁（共享锁）： 对同一行数据进行“读”，可以同时进行（其他可以读，不能写）
* 写锁（排他锁）： 在上了写锁之后，及释放写锁之前，在整个过程中不能进行任何其他并发操作（其他读和写都不能进行）

 

九、 MVCC设计思想——多版本并发控制

权衡安全和性能

1. 事务特性

* 原子性
* 一致性
* 隔离性
* 持久性

2. 事务隔离级别

set session transaction isolation level [read uncommitted];

• read uncommitted： （读未提交）在一个事务中读取到另一个事务还没有提交的事务（脏读）
• read committed： （读已提交）解决脏读问题，一个事务中只会读取到另一个事务已提交的数据，但是出现不可重复读问题：事务中重复读数据，数据的内容是不一样的。
• repeatable read： （可重复读 默认）在一个事务中每次读取的数据都是一样的，不会出现脏读和不可重复读，但会出现虚读问题
• serializable： （串行化）直接不允许事务的并发发生，不存在任何并发性，相当于锁表，性能差，不考虑

虚读：

A表B表都进入事务

A表插入一条id=28的语句，提交

B表查询一下，发现没有id=28的数据，于是也插入id=28的语句，却失败了，提示已存在id=28

但是再查询，还是没看到有id=28的数据，仿佛出现了幻觉

解决办法：上行锁

3. MVCC 思想解读

对读的性能做了并发性的保障，让所有读都是快照读，

对写进行版本控制，写之前要版本（快照）更新

又能提高读的并发性，又能保证写的安全性

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

十、 死锁和间隙锁

死锁： 开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。

1. 死锁

2. 间隙锁

行锁： id=28

间隙锁:where id>13 and id <19

对13 和19 所在的间隙上锁

如图 11-14和 15-20 这段区域都被上了间隙锁 不能够对间隙内进行操作

如果是id>24 那么大于24这一大片也是被上锁的

理解为 范围锁