注:由于互联网行业普遍使用MySQL,以下内容无特殊注明均面向MySQL
MyIsam和Innodb差异对比
MyIsam | Innodb | |
---|---|---|
存储文件 | .frm(表定义文件)、.myd(数据文件)、.myi(索引文件) | .frm(表定义文件)、.ibd(数据文件) |
锁 | 表锁 | 表锁、行锁 |
事务 | 不支持 | ACID |
count | 专门存储的地方 | 扫表 |
性能调优
1、explain 分析执行计划
Id - 第几层执行计划
Select_type
- Simple
- Subquery
- …
Type - 依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,
unique_subquery,index_subquery,range,index_merge,index,ALL- system const的特例,仅返回一条数据的时候。
- const 查找主键索引,返回的数据至多一条(0或者1条),属于精确查找 。
- eq_ref 查找唯一性索引,返回的数据至多一条。属于精确查找。
- ref 查找非唯一性索引,返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条 。
- range 查找某个索引的部分索引,一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行,属于范围查找 。
- index 查找所有的索引树,比ALL要快的多,因为索引文件要比数据文件小的多。
- ALL 不使用任何索引,进行全表扫描,性能最差。
Key - 使用的索引
key_len - 索引键长度
Rows - 记录条数
Extra - 额外细节信息
2、只取需要的列
数据列占空间,排序占空间
3、索引
3.1 索引的类型
按聚集分类
索引可以分为聚集索引、非聚集索引。
- 一个表只有一个聚集索引(通常是主键),聚集索引直接在叶子节点上保存数据;
- 非聚集索引的叶子节点不保存数据,只保存主键,需要通过主键再在聚集索引上查找数据,所以需要二次查询。
按实现算法分类
非聚集索引索引类型分为唯一索引、普通索引、全文索引、空间索引,实现方式分为HASH和B+TREE。其中Hash索引无法实现范围查询,只在部分场景下适用。
3.2 索引的数据结构
B+Tree
为什么不使用B-树或红黑树?
对比B-树:
- B+树只有叶子节点有data域,相对B-树更节省空间
- B+数叶子节点之间有链指针相连,遍历叶子节点即可以实现区间访问
对比红黑树:
红黑树(二叉查找树)、AVL树(平衡二叉树)都是二叉树,基本只会在内存中使用,数据库的索引涉及大量的IO操作,树的深度对IO影响很大,B树可以有多个子节点,有效降低树高度。
3.3 索引的优缺点:
优点:
查询效率高
缺点:
- 更新索引的IO耗时:插入、重排
- 占用存储空间
不要盲目创建索引
3.4 组合索引
尽量使用组合索引,组合索引满足最左原则,如有组合索引(A,B,C),可理解为数据库创建了对应的三个索引(A)、(A,B)、(A,B,C),所以索引A不用独立创建索引,否则浪费存储空间和影响插入、更新的效率。
从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用。比如:
- where a=3 and b=45 and c=5 …. 这种三个索引顺序使用中间没有断点,全部发挥作用;
- where a=3 and c=5… 这种情况下b就是断点,a发挥了效果,c没有效果
- where b=3 and c=4… 这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果;
- where b=45 and a=3 and c=5 …. 这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关
3.5 合理索引减少排序性能耗损
多列索引是先按照第一列进行排序,然后在第一列排好序的基础上再对第二列排序,如果没有第一列的话,直接访问第二列,那第二列肯定是无序的,直接访问后面的列就用不到索引了。
简单理解即组合索引中第二个索引数据可实现自动排序(BTree数据结构会按顺序排列,所以查询时可免去排序运算)
3.6 避免索引失效
索引失效情况:
- like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
- 当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描
- 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作、字段上使用函数。
- 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。
4 小表驱动大表
当两个关联字段中只有一个字段有索引的时候:
Select * from A join B on A.id = B.userId where A.name like ‘陈%’
B.userId建立了索引,以A表驱动B表时,先查出A表符合的数据,然后再以表A数据匹配表B,而遍历表B时因为触发索引查询,所以匹配的效率为LogN
假设A表100条数据,B表10000条数据,查询的过程可以理解为
for 100条数据 in A {
Log(10000)
}
查询效率为 100log(10000),如果以B表驱动A表,则效率为10000log(100),显然前者性能更优。
假如使用块的嵌套循环连接的话
内存中放小表的I/O代价小于内存中放大表的 I/O代价,数据库系统实现P108,简而言之,经过化简是 B(S)+B(S)B( R)/M-1,由于加号的后面是定值,所以前面的值的话整体就是小的
in和exists的对比:
下面关于大表和小表的讨论均为A是小表
小表放到in之后的效率优于放到in之前
例:select name from B where id in(select id in A);
分析:上面的查询语句可以拆成 首先 select id in A; 其次 select name from B where B.id=A.id;这样的话
小表放到exists之前效率优于放到exists之后
例:select name from A where exitsts (Select * from B where B.id=A.id);
分析:先遍历小表A,取得A中每个值和B中的值进行比对,如果比对成功了返回true
事务
事务的并发问题
1、脏读
事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
2、不可重复读
事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
3、幻读
事务A 按照一定条件进行数据读取, 期间事务B 插入了相同搜索条件的新数据,事务A再次按照原先条件进行读取时,发现了事务B 新插入的数据 称为幻读。
(注: 期间事务B 删除了符合条件的某一条数据,导致事务A 再次读取时数据少了一条,这种情况归为 不可重复读)
MySQL事务隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 |
不可重复读(读已提交)(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read)(默认) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
MVCC机制
锁机制开销较大,Mysql的可重复读模式是采用MVCC(多版本控制)机制实现,MVCC可以在大多数情况下代替行级锁,能降低其系统开销。
MVCC是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制。当我们创建表完成后,mysql会自动为每个表添加 数据版本号(最后更新数据的事务id)db_trx_id 删除版本号 db_roll_pt (数据删除的事务id) 事务id由mysql数据库自动生成,且递增。