mysql的一些总结

mysql作为我们工作中经常结束的数据库,我们可能并没有深入了解过,那现在我们这篇文章一起来深入认识下它吧。


一、mysql存储引擎

mysql的存储引擎有两种:1、InnoDB 2、MyISAM
  InnoDB是mysql默认的存储引擎,能够支持事务;
  MyISAM对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它

InnoDBMyISAM
支持事务不支持事务
支持行级锁只支持表级锁,不支持行级锁
支持外键不支持外键
崩溃后发生损坏的概率大且恢复慢崩溃后发生损坏的概率小且恢复快

二、mysql索引

1.什么是索引

  索引本质是为了满足高效查询数据的一种数据结构。数据库中的索引分为很多种,InnoDB中采用的是B+树。在讲B+树前我们先来了解下其他的索引。

1.1 hash索引

  大家应该都知道哈希表是数组加链表的结构,再查询数据时先计算hash值确定数据所在的位置,如果坐在位置是链表要遍历链表。而链表是无序的,这样虽然提高了数据插入的效率,但是在查询特定区间的数据(比如查询年龄在3-7)要遍历整个链表,显然效率是不高的。也正是这个原因,hash索引只适用在等值查询的场景,如memcached及其他一些nosql的引擎
在这里插入图片描述

1.2 二叉树

B+树是由二叉查找树、二叉平衡树、B树演变而来,所以在讲B+树前我们一起先来看看这三种数据结构

1.2.1 二叉查找树

  二叉查找树具有以下特点:
1、左节点的值要小于当前节点的值
2、右节点的值要大于当前节点的值
如下图,我们要查找19时,只需要3步就可以完成,很大程度提高了查询的效率。在这里插入图片描述
但是在极端情况下二叉查找树会退化成一个链表,如下图。在这种情况下查询的时间复杂度就蜕变为了O(n)
在这里插入图片描述
也是上面二叉树的不稳定性,二叉搜索树没有作为mysql索引的数据结构

1.2.2 二叉平衡树

有没有方法能够解决二叉查找树的不稳定的问题呢?二叉平衡树就是一种解决方法。
  二叉树平衡树具有每个阶段左右子树的高度差不能超过1的特点。为了保持二叉树的平衡,在数据插入的时候,需要进行n此的左旋或者右旋处理,这就会导致插入的性能较差。
  那么为什么二叉平衡树叶没有作为mysql索引的数据结构呢,本质还是因为在数据量庞大的情况下,所有的树的高度会变的极高,查询的效率不高

1.2 B树

  B树是一种多路平衡查找树,它与类似于平衡二叉树,不同的是每个节点可以存在多子节点
在这里插入图片描述
  如上图,B树有这些特点:
1、每个节点同时存储数据和指针,指针指向其他子节点
2、查询一个数据最高效的时间复杂度是O1,正常情况下解决二分查找的效率
3、每次查询都需要节点的数据也加载到内存中,并不是最高效的

1.3 B+树

  B+树是B树的优化,非叶子节点上不存储数据,只存储键值,所有数据均存储在叶子节点。所有的叶子节点是双向链表链接,叶子节点的内容是是通过单向链表链接如下图是B+树的简图
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/755cd2f4bc01484eaf74010240688029.png

由于以上的特性,也决定了B+树相较于B树的优势:
1、查询数据时更加稳定
2、读写磁盘的代价更低
3、更便于区间查询和扫库

三、聚簇索引和非聚簇索引

聚簇索引: 将数据和索引放在一块存储,找到索引页就找到了数据,也就是索引结构叶子节点存储着行数据,聚簇索引有且只有一个
非聚簇索引: 将数据和索引分开存储,索引结构的节点节点没有存储数据只是存储了数据对应的主键,非聚簇索引可以存在多个
聚簇索引选取的原则:
1、优先选用主键作为聚簇索引,如果主键存在,主键索引就是聚簇索引
2、如果主键不存在,选用唯一索引作为聚簇索引
3、如果主键和唯一索引均不存在,innode会自动生成一个行id(rowid)作为隐型的聚簇索引
下面我们在结合几个图来理解下这两种索引,假设我们现在有这么一个表。id是自增主键

idnameage
1tom30
2jack50
3zhaosi10
4wangwu40
5lili33
6zhangsan29
7shensui12
8shenhu19

在这里插入图片描述

上图是主键索引的简图,我们可以看到当我们通过id查询时,比如查询id=8的数据,查到索引id=8时,行数据在当前节点存储,索引相当于同时找到了对应的数据,这种就是聚簇索引
如山
上面这个简图是用age左右索引的结构,因为叶子节点存的是对于的主键,索引不能直接查询到对应的数据。需要再通过主键索引查询才能查询到数据。
回表查询: 比如select name from info where age=50;这个sql在查询数据时,先通过非聚簇索引查询到对应的主键id,再通过聚簇索引查询到对应的行数据,这个过程称之为回表查询。
覆盖索引: 查询数据时使用了索引,且查询的数据能够再返回的数据中均能够找到,这种现象就是覆盖索引
还用上面简单的sql来说明白:
1、select id,age from info where id=2;
  根据上面聚簇索引的图,通过id直接可以查询到当前行数据,行数据中包含id和age索引,这种是覆盖索引
2、select id,name from info where age=12;
  根据上面非聚簇索引的图,通过age不能直接查询到id和name,需要再经过一次回表查询,才能查询到id和name,所以这种不是聚簇索引
3、select id,age from info where age=12;
  根据上面非聚簇索引的图,通过age直接查询到对应的主键id,age不需要再次查询,所以这种也是覆盖索引

四、索引的创建原则&sql优化

4.1索引的创建原则

 &emsp通过上面的几节,相信我们已经对索引有较好的理解了,那我们在创建索引时有什么原则呢?
这里我总结几点:
1、对于数据量较大且查询频繁的表,我们需要创建索引
2、对于查询where,分组group by ,排序order by频繁使用的字段创建索引
3、尽量创建联合索引,避免单列索引,联合索引可以达到覆盖索引避免回表查询从而提高查询的效率
4、同一个表索引不需要太多,并不是越多越好
5、在创建索引时尽量使用区别度高的字段,比如国家country字段可能上千万个数据多家都是相同的,这个字段就不便于作为索引,区别太低
6、如果较长的字段太长且必须作为索引,可以采用前缀索引,取字段开头的部分字符作为索引
7、如果部分字段不能存放null的数据,在建表时需要限制字段 not null

4.2sql优化

   我们按照上面的创建的索引有时候也会出现失效的情况,那具体什么原因会造成原因失效呢?我们结合下面这个表来看假如有这样一个表
在这里插入图片描述
一个主键索引,一个唯一索引
sq_name_status unique (name, age, status)
1、违反最左前缀原则
如果索引了多列,查询时需要满足最左前缀原则。查询时从最左侧开始不能跳过。

explain select  * from sql_learn where name='马云' and age=40;
explain select  * from sql_learn where age=10 and status=1;

如上sql1查询条件的字段顺利和索引顺序一致,能够正确命中索引,对比sql2,没有只用name,使用age和status查询违反了最左前缀原则,sql执行计划中可以看到走的是全表扫描
在这里插入图片描述
在这里插入图片描述
2、查询条件全匹配如“%desc%“、“%desc“
3、索引列不能使用运算
4、字符串不加引号,会造成类型转换,也会造成索引失效
5、区间查询的右侧,索引会失效
6、如果mysql使用全表扫描要比使用索引快,则不使用索引
7、如果条件中有or,即使其中有条件带索引也不会使用(这也就是为什么尽量少用or的原因)

  • 24
    点赞
  • 24
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值