MySQL 索引 8分钟带你深入了解MySQL是如何利用索引的，网友：大师，我悟了
发布时间：2022-09-15 15:15:14 所属栏目：MySql教程 来源：
导读： mysql索引结构：
mysql索引使用B+tree，为什么使用B+tree呢，首先，使用索引是为了加快查找的速度MySQL 索引，B+tree的查找时间复杂度为log(n).那为什么不用o(1)的hashMap呢。mysql是有使用hashMap结构的
mysql索引使用B+tree，为什么使用B+tree呢，首先，使用索引是为了加快查找的速度MySQL 索引，B+tree的查找时间复杂度为log(n).那为什么不用o(1)的hashMap呢。mysql是有使用hashMap结构的
|
mysql索引结构： mysql索引使用B+tree，为什么使用B+tree呢，首先，使用索引是为了加快查找的速度MySQL 索引，B+tree的查找时间复杂度为log(n).那为什么不用o(1)的hashMap呢。mysql是有使用hashMap结构的hash索引的，但大部分情况下，我们使用的索引并不是hash索引，主要是hash索引这种结果在处理 !=, > ，< 这种范围查询时，需要全表扫描，时间复杂度为o(n)。 为什么不使用B tree？ BTree和B+Tree的区别是，B+Tree的非叶子结点只保存索引，不保存数据，这样一个节点保存的数据更多，树的高度更低，在读取索引时，可以省IO(其实，这里降低树高度基本没啥用，因为往往我们的Tree的每个节点的度都很大，BTree和B+Tree高度基本差不多)。 另一个主要的作用是，由于数据节点都在叶子结点上，而每个叶子结点又使用双向链表链接，这样，在处理范围查询时，只需要查定一个下界，然后在叶子结点上遍历即可，且天然有序。 主键索引和二级索引 以Innodb为例：主键索引是和数据文件放在一块的，即数据文件在叶子结点上。对于主键索引，找到了主键索引，主键索引对应的value即为数据row。而非主键索引被称为二级索引的原因是，非主键索引的value存放的是主键的值，我们在使用非主键索引查找时，需要先根据索引找到主键，然后根据主键去找数据row。 根据主键再去找数据row的过程称为回表。因为对于这样的索引，不直接和数据关联，所以称为二级索引。对于二级索引，如果我们查找的列，已经全部在索引列里了，这时候就不需要回表了，这种索引成为覆盖(covered)索引。 主键索引也叫聚簇索引，二级索引也叫非聚簇索引。 explain索引优化 当我们想对一个sql语句进行优化时，可以用explain查看当前sql的执行计划。对于explain的输出，几个重要的如下。 Type： extra： 建立索引和索引匹配的原则 对于联合索引，比如(row1,row2,row3)这种，mysql按照最左前缀匹配的时候，相当于给我们建了(row1),(row1,row2),(row1,row2,row3)三个索引。因此当有联合索引的时候，不再需要单独建立额外的单列索引。 数据查询时，where自己后面的顺序无所谓，mysql会自动帮你优化。 where 后面在使用or 查询的时候，大部分情况下不会走到索引。所以，对于这种查询，可以使用union来优化In many cases, MySQL won't be able to use an index to apply an OR condition, and as a result, this query is not index-able.Therefore, we recommend to avoid such OR conditions and consider splitting the query to two parts, combined with a UNION DISTINCT (or even better, UNION ALL, in case you know there won't be any duplicate results） 建索引时，范围字段放在联合索引的最后，因为按照最左前缀匹配原则，碰到范围字段就终止匹配了，后面的字段不会去匹配。 区分度大的字段在建索引时放在前面。 区分度公式：count(distinct col)/count(*)，就是一个字段当选择了一个值时，要能过滤掉大部分字段。 mysql NULLmysql架构 锁 为了解并发问题，引入锁，mysql中锁分为读锁和写锁，即share lock和exclusive lock。顾名思义，share lock之间不互斥，share lock和exclusive lock之间互斥，exclusive lock之间互斥。mysql 提供行锁row lock和表锁 table lock的multiple granularity locking。 对于表锁，mysql提供一种意图锁的机制，意图锁也是分为两种，intention share lock和intention exclusive lock。对于intention lock mysql row lock是在存储引擎层实现的，不同的存储引擎可能有不同的实现方式。 事务 事务是指一批操作，要么全部成功，要么全部失败。 数据库事务的ACID特性 隔离级别 隔离级别是对不同的事务而言的。 怎么解决不可重复读问题？在一个事务开始时，对涉及到的row加上行锁即可以保证另一个事务无法修改这一行。但是这解不了幻读的问题，因为别的事务可能insert的是别的行。这时候，需要引入gap lock。不仅锁这一个row，还锁这个row的前后间隙。 具体怎么锁，根据查询条件是走唯一索引还是非唯一索引，是走等值匹配还是范围匹配有不同的gap lock lock的范围，但一个原则就是：保证你这个语句的查询范围内的数据不会被其他事务insert进去。 事务的两段锁： 在事务的执行过程中，随时可以进行锁定，但只有事务执行完毕commit或者rollBack的时候，才会释放锁。 MVCC 多版本并发控制 前面说锁分为读锁和写锁，这是一种悲观锁，MVCC是一种乐观锁，通过版本号控制，读副本的方式，来使得select读不用加锁，每次都读副本，同时保证读到的都是事务开始之前写入的数据。 因为大多数数据库操作都是读多写少的，通过MVCC，读操作不用加锁，减少了锁冲突的概率，提高吞吐。select和事务又有啥关系呢？对于mysql，默认是auto-commit模式，如果不显示地开启一个事务，每个查询都被当作一个事务来执行。 mvcc怎么实现的 快照读和当前读 select语句读的是快照，通过读快照，在RR级别也不会有幻读，对于select for update这种当前读，通过next-key lock解决幻读问题。 （编辑：我爱制作网_沈阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330576号