数据库优化:
索引失效的场景:
- like 以%开头,索引无效;当like前缀没有%,后缀有%时,索引有效。
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,该索引失效,只有当or左右查询字段均为索引时,才会生效。
- 组合索引,不是使用第一列索引,索引失效。
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描。
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引,可以用其他的办法处理,例如:数字类型,判断大于0,字符串类型设置一个默认值,判断是否等于默认值即可。
- 在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作。
- 在索引字段上使用函数。
- 当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效。
索引失效分析工具:
可以使用explain命令加在要分析的sql语句前面,在执行结果中查看key这一列的值,如果为NULL,说明没有使用索引。
索引使用注意事项
- 不要滥用索引
- 索引提高查询速度,却会降低更新表的速度,因为更新表时,mysql不仅要更新数据,保存数据,还要更新索引,保存索引
- 索引会占用磁盘空间
- 索引不会包含含有NULL值的列 ,复合索引只要有一列含有NULL值,那么这一列对于此符合索引就是无效的,因此我们在设计数据库设计时不要让字段的默认值为NULL。
- MySQL查询只是用一个索引,如果where字句中使用了索引的话,那么order by中的列是不会使用索引的
- like:like ‘%aaa%’不会使用索引而like “aaa%”可以使用索引
联合索引最左匹配原则
- 是不是用索引,和查询条件的顺序无关(优化器会自动调整条件的顺序),但和这些字段的查询手段有关
- 建立了abc的联合索引,相当于建立了 a的单列索引,ab的联合索引,以及abc的联合索引
- 一般根据最左匹配的原则,但在遇到范围查询后,匹配终止,也就是说,当条件为:
a like ‘%str%’、a like ‘%str’ 时,不走索引;当条件为 a like ‘str%’ 或者 “>”, “<”, “between”时, 仅使用了联合索引中a的部分;
b,c 同理,根据查询方式不同,即便条件中的3个字段都在索引里,也不一定使用了全索引
假如条件是 a = 1 and b = 2 and c = 3 这类情况,是必然走这个联合索引了
我们的数据库当中如何做的优化?
- 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
- 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
- 应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描
- in 和 not in 也要慎用,否则会导致全表扫描(当数据库人为IN和NotIn使用索引查询的效率不如全表扫描时会放弃使用索引转而使用权标扫描)
- like模糊全匹配也将导致全表扫描
回表查询和索引覆盖是什么?
概念:
- 回表查询:先定位主键值,再定位行记录,它的性能较扫一遍索引树更低
- 索引覆盖:在一棵索引树上就能获取SQL所需的所有列数据,无需回表,即实现了索引覆盖
如何实现索引覆盖:
- 主键索引存储的是主键值和行记录(即聚簇索引),无需回表查询
- 普通索引存储的是索引和rowid的值,查询其他字段(非rowid字段和其他字段)需要回表查询,建立联合索引实现索引覆盖可以解决这个问题
结合下面的实例:
1 | # 有一张表 |
Select * from table where a = ‘1’ and b > ‘2’ and c=’3’ 索引(a,b,c)是否走索引
在a、b走完索引后,c肯定是无序了,所以c就没法走索引,数据库会觉得还不如全表扫描c字段来的快(扫描全部满足a=1和b>2的纪录)
始终记得,在数据库中是先按照a排序,后按照b排序,最后按照c排序。 首先索引找a,找到后在a相等的条件下,b必然是有序的。因为先按照a排序,若a相同,按照b排序,因此a相等的条件下,b必然有序。 好,我们继续。当找到所有b>2的之后,c还有序么?不,c不有序。因为(b,c)的组合可以是(100000,1),也可以是(1,100)。发现了么,当你根据b找到区间之后,c是无序的,因为b和c之间没有关系。你不能保证所有大于2的b的那些记录中,c还是有序的。
现在一张表table,有a,b,c三个索引,select * from table where a = 1 and b = 2 order by c,这些字段是否都能使用索引?如果不能怎么优化
https://blog.csdn.net/gb4215287/article/details/78037835
与其说是数据库只支持一条查询语句只使用一个索引,倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
所以如上条的情况,最佳推荐是使用index(column1,column2,column3) 这种联合索引,此联合索引可以把b+tree结构的优势发挥得淋漓尽致:
一条主二叉树(column=1),查询到column=1节点后基于当前节点进行二级二叉树column2=foo的查询,在二级二叉树查询到column2=foo后,去三级二叉树column3=bar查找。
索引底层数据结构:
MySql中InnoDB表为什么要建议用自增列做主键
https://blog.csdn.net/bigtree_3721/article/details/73151028
InnoDB引擎表的特点:
- InnoDB引擎表是基于B+树的索引组织表(IOT)
关于B+树
B+ 树的特点:
(1)所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;
(2)不可能在非叶子结点命中;
(3)非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;
- 如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。
- 数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)
- 如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页
- 如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
综上总结,如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的,也就是下面这几种情况的存取效率最高:
- 使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;
- 该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;
除此以外,如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。
MySql为什么使用B-Tree作为索引而不是其他二叉查找树(比如红黑树等)?
https://www.cnblogs.com/aspirant/p/9214485.html
平衡二叉树不适合作为索引。那么什么才适合作为索引——B树。
平衡二叉树没能充分利用磁盘预读功能,而B树是为了充分利用磁盘预读功能来而创建的一种数据结构,也就是说B树就是为了作为索引才被发明出来的的。
来看看关于“局部性原理与磁盘预读”的知识:
1 | 局部性原理与磁盘预读: |
搞清楚上面的意思。磁盘预读是具体实现,其理论依据是局部性原理。
为什么说红黑树没能充分利用磁盘预读功能,引用一篇博文的一段话:
1 | 红黑树这种结构,h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。 |
也就是说,使用红黑树(平衡二叉树)结构的话,每次磁盘预读中的很多数据是用不上的数据。因此,它没能利用好磁盘预读的提供的数据。然后又由于深度大(较B树而言),所以进行的磁盘IO操作更多。
B树的每个节点可以存储多个关键字,它将节点大小设置为磁盘页的大小,充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字,树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少,更多的是在内存中对读取进来的数据进行查找。
B树的查询,主要发生在内存中,而平衡二叉树的查询,则是发生在磁盘读取中。因此,虽然B树查询查询的次数不比平衡二叉树的次数少,但是相比起磁盘IO速度,内存中比较的耗时就可以忽略不计了。因此,B树更适合作为索引。
为什么用B+-Tree作为索引而不是B-Tree
比B树更适合作为索引的结构是B+树。MySQL中也是使用B+树作为索引。它是B树的变种,因此是基于B树来改进的。为什么B+树会比B树更加优秀呢?
B树:有序数组+平衡多叉树;
B+树:有序数组链表+平衡多叉树;
B+树的关键字全部存放在叶子节点中,非叶子节点用来做索引,而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。
引用一段话:
1 | 走进搜索引擎的作者梁斌老师针对B树、B+树给出了他的意见(为了真实性,特引用其原话,未作任何改动): “B+树还有一个最大的好处,方便扫库,B树必须用中序遍历的方法按序扫库,而B+树直接从叶子结点挨个扫一遍就完了,B+树支持range-query非常方便,而B树不支持。这是数据库选用B+树的最主要原因。 |
举个例子来对比。
B树:
比如说,我们要查找关键字范围在3到7的关键字,在找到第一个符合条件的数字3后,访问完第一个关键字所在的块后,得遍历这个B树,获取下一个块,直到遇到一个不符合条件的关键字。遍历的过程是比较复杂的。
B+树(叶节点保存数据,其他的节点 全部存放索引):
相比之下,B+树的基于范围的查询简洁很多。由于叶子节点有指向下一个叶子节点的指针,因此从块1到块2的访问,通过块1指向块2的指针即可。从块2到块3也是通过一个指针即可。
引用一篇博文中网友评论的一段话:
数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题,B+树应运而生。
B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作(或者说效率太低)。
正如上面所说,在数据库中基于范围的查询是非常频繁的,因此MySQL最终选择的索引结构是B+树而不是B树。
B+ 树和 B 树在构造和查询性能上有什么差异呢?
B+ 树的中间节点并不直接存储数据。
- B+树的查询效率更加稳定:由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
- B+树的磁盘读写代价更低:B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。
- 由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。
数据库索引,为什么不适用用二叉树:
- 平衡二叉树必须满足(所有节点的左右子树高度差不超过1)。执行插入还是删除操作,只要不满足上述条件,就要通过旋转来保持平衡,而旋转是非常耗时的,所以AVL树适合用于查找多的情况。
- 二叉树的数据结构,会导致“深度”,比较深,这种“瘦高”的特性,加大了平均查询的磁盘IO次数,随着数据量的增多,查询效率也会受到影响;
数据库锁
MySql悲观锁和乐观锁如何实现
确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性,乐观锁和悲观锁是并发控制主要采用的技术手段。
悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作
- 在查询完数据的时候就把事务锁起来,直到提交事务
- 实现方式:使用数据库中的锁机制
乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。
- 在修改数据的时候把事务锁起来,通过version的方式来进行锁定
- 实现方式:使用version版本或者时间戳
悲观锁:缺点性能低
1
2select * from eb_sku where sku_id = 1001 for update;
update eb_sku set stock = 100- ${quantity} where sku_id = 10001乐观锁:
1
update eb_sku set stock = 100-${quantity}, version = ${version}+1 where sku_id = ${sku_id} and version=${version} and stock > ${quantity}
行锁、表锁
间隙锁、临键锁
分库分表
为什么要分库分表
按照什么逻辑分库分表
分库分表如何扩容
其他知识点
Mysql中utf-8和utf8mb4的区别与使用场景
MySQL中的utf8编码只支持最大3字节每字符,utf8mb4才是 真正意义上的“UTF-8”,utf8mb4支持4字节每字符,支持存储emoji,而utf8不支持。
MySql Innodb引擎和MyIASM引擎的区别
Innodb引擎
- Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别。
- 该引擎还提供了行级锁和外键约束
- 使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
- 它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统
- MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。
- 该引擎不支持FULLTEXT类型的索引(不支持全文索引)。
- 它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。
- 当需要使用数据库事务时,该引擎当然是首选。
- 由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。
MyIASM引擎
- 但是它没有提供对数据库事务的支持,也不支持行级锁和外键,
- 因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。
- MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。
- 如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。
主要区别:
- MyIASM是非事务安全的,而InnoDB是事务安全的
- MyIASM锁的粒度是表级的,而InnoDB支持行级锁
- MyIASM支持全文类型索引,而InnoDB不支持全文索引
- MyIASM相对简单,效率上要优于InnoDB,小型应用可以考虑使用MyIASM
- MyIASM表保存成文件形式,跨平台使用更加方便
应用场景:
- MyIASM管理非事务表,提供高速存储和检索以及全文搜索能力,如果再应用中执行大量select操作,应该选择MyIASM
- InnoDB用于事务处理,具有ACID事务支持等特性,如果在应用中执行大量insert和update操作,应该选择InnoDB
MyIASM | InnoDB | |
---|---|---|
事务支持 | 不支持 | 支持 |
锁的粒度 | Table | Row |
存储容量 | 没有上限 | 64TB |
哈希索引 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
外键 | 不支持 | 支持 |