MySQL
一、 索引
1. 为什么要使用索引
海量数据中,查询某条记录的场景经常发生,给要查询的数据字段加索引后,查询会非常快。
2. 索引是什么
索引 = 数据目录
字典——目录
没有索引的时候会全表扫描,多个IO读写开销
索引 键值对 存储具体磁盘上的位置
为数据库的某个字段创建索引。相当于为这个字段的内容创建了一个目录。
通过这个目录可以快速定位到数据所在的位置。
问题?
- 索引存放位置
- 索引分类和如何创建
- 索引为什么能比查磁盘快?——数据结构(B+树 查询快)
3. 索引存放位置
mac: /usr/local/mysql
win: C:/programdata/mysql
innoDB:索引跟数据存放在一起 .ibd
MyISAM:索引和数据分开两个文件存储 索引:.MYI ;数据 .MYD
4. 索引分类和创建
-
主键索引
- 主键自带索引
-
普通索引
-
创建索引命令:
create index 索引名称 on 表名(列名)
create index idx_name on employees(name)
-
-
唯一索引
-
就像唯一列,列中数据唯一
-
create unique index idx_name on employees(name)
-
-
组合索引(联合索引)
- 一次性为表中多个字段一起创建索引,最左前缀法则(如何命中联合索引中的索引列)
- 建议不要超过5个字段
create index idx_name_age_position on employees(name,age,position)
-
全文索引
-
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)
- 数据冗余,空间浪费,虽然提高了性能,但需要更多空间
- 维护难—数据改动的时候多个索引树都要改动
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这一大片也是被上锁的
理解为 范围锁