高性能MySql-创建高性能的索引的几大基本原则

创建高性能的索引

索引基础

  • 索引有很多类型,Mysql是使用B+tree树索引,索引是在引擎层实现而不是服务层。不同引擎的索引工作方式不一样。
  • MyISAM使用前缀压缩技术使得索引更小,InnoDB按照原数据根式进行存储。MyISAM索引通过数据到物理位置引用被索引的行,InnoDB根据主键引用被索引的行
  • 全值匹配:指的是和索引中所有列进行匹配。比如EXPLAIN 中的type=index
  • 最左匹配原则: mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)。也就是如果使用复合索引查询时优先按照索引创建的顺序进行条件查询

索引的选择策略

  • 索引字段必须是独立的列,不嗯呢该是表达式和函数运算
  • 计算合适的前缀索引长度
  • 善用复合索引,而不是为每个字段都建立索引,选择合适的索引列顺序
    *当不需要排序和分组时使用频率较高的放在复合索引前列,这时候的索引优化用于where条件
  • 尽可能将左范围查询的列放在索引的后面,以便优化器使用尽可能多的索引列。
  • 对于范围条件查询,mysql范围列后面的其它的索引列,对于多个等值条件查询则没有这种限制

Inndb主键索引和非主键索引的区别

  • 主键索引即存储了索引值,又在叶子中存储了行的数据。所以通过主键查询时效率高,一次查询即可,不需要回表操作。这种即存储索引值又存储行的所有数据的结构叫做(聚簇索引)
  • 非主键索引存储索引值,但叶子中存储的是主键ID,所以查询时需要进行一次回表操作才可以取到所有的行数据

索引覆盖

  • 索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为”索引覆盖”

理想的索引

  • 理想的索引。1:查询频繁 2:区分度高 3:长度小 4:尽量能覆盖常用查询字段.
  • 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
  • 针对列中的值,从左往右截取部分,来建索引
  • 截的越短,重复度越高,区分度越小, 索引效果越不好
    -截的越长,重复度越低,区分度越高,索引效果越好,但带来的影响也越大–增删改变慢,并间影响查询速度.所以,我们要在 区分度+长度,两者上,取得一个平衡.
  • 惯用手法:截取不同长度,并测试其区分度

select count(distinct left(word,6))/count(*) from dict;

使用索引扫描来做排序

  • mysql有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描。如果explan出来的type值为index则说明使用了索引扫描来做排序。
  • 只有索引列的顺序和order by字段的顺序完全一致,并且所有列的排序方向都一样时,mysql才能使用索引对结果进行排序。
  • 如果查询需要关联多表,则只有当order by中使用的字段完全是第一个表时,才能使用索引做排序。order by和查找型的限制一样,都要满足最左前缀要求。
  • 如果索引的第一个字段为常量时,where或join中指对这个列制定了固定的常量值,就可以不满足最左前缀要求。比如有个复合索引(c1,c2,c3), 执行 select * from where c1=1 order c2,c3 就可以使用到索引排序

重复索引与冗余索引

  • 重复索引: 是指 在同1个列(如age), 或者 顺序相同的几个列(age,school), 建立了多个索引,称为重复索引,重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.
  • 冗余索引:是指2个索引所覆盖的列有重叠, 称为冗余索引比如x,m,列,加索引 index x(x),index xm(x,m)x,xm索引,两者的x列重叠了,这种情况,称为冗余索引.
  • 甚至可以把index mx(m,x)索引也建立,mx,xm也不是重复的,因为列的顺序不一样.

索引和锁

  • 索引可以让查询锁定更少的行,Innodb在访问行的时候对其加锁,而索引可以减少Innodb访问的行数,从而减少锁的数量
已标记关键词 清除标记
<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能的MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。</span> </p>
相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页