MySQL索引可以说是整个MySQL体系的重点,这里一定要好好看!!!
MySQL索引可以说是整个MySQL体系的重点,这里一定要好好看!!!
MySQL索引可以说是整个MySQL体系的重点,这里一定要好好看!!!
目录
索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是所有。
简单说,索引就是一种数据结构,用来帮助MySQL获取数据的
索引的优缺点:
优势 | 劣势 |
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占用空间的 |
通过索引列队数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询效率,同时却也降低了更新表的速度,如对表进行INSERT,UPDATE,DELETE时,效率降低 |
索引的结构
MySQL的索引是在存储引擎层实现的,不同的存储结构有不同的结构
索引结构 | 描述 |
B+Tree 索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash 索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-Tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,ES |
MySQL中的 B + 树索引
说到 B+ 树,想必大家恐怕已经忘完了吧,更别说MySQL中的B+树了,这里我们就先来复习一下B+树
B+ 树说白了,就是B树的一种优化,以往的二叉树,红黑树什么的性能虽然好,但是,一旦数据变多就会导致树的深度会非常大,但B树可以指定度,一个节点可以存放多个数据,多个指针(指针就是指向下一个节点),这样可以大大减少树的深度。
咋们心里就算没有B+树,也要有点B树啊......
好了,不开玩笑了,我们先来看看B树
B 树
B树中有度的概念,度为5的话代表有4个key,5个指针
分裂过程:设定B树的度为 5
现在已经有4个元素了,当插入第五个元素时,B树将发生分裂
中间元素向上分裂,0345向上分裂
此时又插入几个元素,让子节点再次到达4个元素
此时插入1000
中间元素向上分裂,1200向上分裂
以上就是 B树的结构,是不是很简单,如果还有元素加入,就按照步骤依次加入就行
B + 树
B 树说完了,我们现在说说B+ 树,先看一看 B+ 树的结构,有没有发现和B树有什么不一样?
没错,B+ 树上面出现的元素,叶子节点也会出现,而且叶子节点还维护了一个单向链表结构
上面的元素只是为索引作用,所有元素都是叶子节点,而且叶子节点会形成一个单向的链表
分裂过程:(设置度为5,度为5的话代表有4个key,5个指针)
此时已经有4个元素了,再次插入0232,会导致0567向上分裂
567向上分裂,但叶子节点也有,且,分裂前的元素指向分裂元素形成链表
此时添加几个元素,让子节点凑够4个元素
此时再次添加2345,会导致1000向上分裂,1000之前的元素指向1000
如果还有别的元素加入,就按照以上顺序依次加入
怎么样?是不是很简单,这里每一步我都做了详细说明,如果看不懂那就多看几遍,要记住B+树的特点:
B+ 树:①所有的数据都会出现在叶子节点 ②叶子节点形成一个单向链表
MySQL中B+树索引
前面我们说了 数据结构的 B/B+ 树,现在我们来看看数据库中的 B+树 索引
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高了区间访问的性能
说白了,数据库中的 B+ 树只不过把下面的单向链表变成了双向链表罢了
Hash 索引
Hash 索引一般只有Memory 引擎再用,但Memory引擎现在几乎都不用了,所有这个索引直供大家了解一下就行
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表上
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决(这里用的是拉链法解决hash冲突,还记得有哪些解决hash冲突的方法吗?HashMap用的啥?ThreadLocal用的啥?)
hash索引特点:
- hash索引只能用于对等比较(=,in),不支持范围查询(between,<,>...)
- 无法利用索引完成排序操作
- 查询效率高,通常只需要一次检索就可以了,效率通常要高于 B+Tree 索引
在 MYSQL 中,支持 hash 索引的时 Memory 引擎,而 InnoDB 中具有自适应 hash功能,hash 索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的。
还有2种 R-tree,Full-Text 这里我就不做过多介绍了,那2种用的比 Hash索引还少,知道有这种索引就行,没必要过多了解
为什么InnoDB存储引擎选择使用 B+Tree索引结构?
那么多索引 为什么InnoDB要把 B+Tree 设置为默认索引结构呢?
这里我总结了三点:
-
相对于二叉树,层级更少,搜索效率高
- 对于 B Tree,无论是叶子节点还是非叶子节点,都会保存数据这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低
- 相对于 Hash 索引,B+Tree 支持范围匹配及排序操作
索引的分类
上面说的是索引的存储结构,现在我们说说索引的分类
索引分为:主键索引,唯一索引,常规索引,全文索引
在 InnoDB 存储引擎中,根据索引的存储形式,又可以分为以下两种:
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
- 如果表没有主键,或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
二级索引是自己根据字段设置的
例如:,设置了name的索引
执行流程:
先去二级索引查找 name 对应的主键(id),在通过id去聚集索引查找对应的数据
思考题
1. 例题:下面那条语句的查询效率高?
根据id查,会直接根据聚集索引查询
而根据name查,会先去二级索引查找,找到对应的id后再根据id去聚集索引查找,产生了回表查询,效率比较低
2. InnoDB 主键索引的 B+Tree高度为多高呢?
根据计算,一行数据大小为 1k,一页中可以存储16行这样的数据,InnoDB的指针占用6个字节的空间,主键即使为 bigint,占用的字节数为8
高度为2时:最多存放 18736 条数据
高度为3时:最多存放 21939856 条数据
索引的使用
上面大概了解了一下索引的基本使用,但只会上面的基本使用可不行.........
索引使用原则:
覆盖索引
尽量使用覆该索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *。
select id,name,age from tbl_user where id = 1 查询速度快,一次查找
select id,name,age from tbl_user where name = 'xx' 查询速度man,回表查找
例如:一张表,有四个字段(id,username,password,status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方法?
答:建立包含 username 和 password 的联合索引
最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引列中的列。如果跳过某一列,索引即将部分失效(后面的字典索引失效),与查询索引的顺序无关
假如设置了索引,包含了pro,age,sta三个字段(我只写了字段的开头几个字母),且后面的语句我都用的是这个联合索引,部分有特殊索引的我会加以说明
范围查询
联合索引中,出现范围查询(> , <),范围查询右侧的列索引失效
导致索引失效的情况
(1)索引列运算
不要在索引列上进行运算操作,否则索引将失效
(2)字符串不加引号
字符串类型字段使用时,不加引号,索引将失效
这里还设置了一个phone索引
(3)不合理的模糊查询
如果仅仅是尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引失效
or连接的条件导致索引失效
用 or 分隔开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及的所有都不会被用到
这里只设置了 一个单独的phone索引
由于 age 没有索引,所以即使id,phone有索引,索引也会失效。所以需要针对于 age 也要建立索引
数据分布影响
如果MySQL评估使用索引比全表更慢,则不适用索引
SQL提示
SQL 提示,是优化数据库的一个重要手段,简单来说,就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的,就是指定使用那些索引
- use index:使用那些索引
- ignore index:不使用那些索引
- force index:必须使用哪个索引
前缀索引
前缀索引一般很少用,我就放在了后面说。一把都是用单列索引和联合索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘 IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率
语法:
前缀长度:
可以根据索引的选择性来决定,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
如何计算索引选择性:是指不重复的索引值(基数)和数据表的记录总数的比值,
索引设计原则
(1)针对于数据量较大,且查询比较频繁的表建立索引
(2)针对于常作为查询条件(where),排序(order by),分组(group by)操作的字段建立索引
(3)尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
(4)如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引
(5)尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
(6)要控制索引的数量,索引并不是多多益善索引越多,维护索引结构的代价也就越大,会影响增删改的效率
(7)如果索引列不能存储 NULL 值,请在创建表时使用 NOT NULL约束它。当优化器知道每列是否包含 NULL 值时,他可以更好地确定哪个索引最有效地用于查询
Sql 优化
一般聊到了索引,就会说SQL 优化,毕竟好多的SQL优化都是基于索引来实现的
网上的SQL优化有很多种,我这里就说几种常见的,供大家参考
插入数据的优化
(1)insert优化
- 批量插入(每次批量插入控制在1000条数据以下)
- 手动提交事务:开启事务,多次批量插入时,使用 commit 手动提交事务
- 主键顺序插入
主键顺序插入比乱序插入效率高
(2)大批量插入数据
如果一次性需要插入大批量数据,使用 insert 语句插入性能较低,此时可以使用 MySQL 数据库提供的 load 指令进行插入
主键优化
说道,主键优化,就得先提一下 MySQL 的数据组织方式
在 InnoDB 存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称之为索引组织表(index organized table IOT)
一般会涉及到页分裂和页合并
- 页分裂
页也可以为空,也可以填充一半,也可以填充100%。每个页包含了 2-n 行数据(如果一行数据过大,会行溢出),根据主键排列
页分裂过程:
第一,二页数据已满,又插入一条新的数据时
开辟一个新的数据页,把第一页超过50%的数据移动到新的数据页,然后插入要插入的数据,最后改变每一页的指向
- 页合并
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用
页合并过程:
13,14,15,16被标记删除,已经超出了页的50%,下一页就会和本页数据合并
下次再插入数据就会直接插入3号page
所以主键优化就是避免页分裂和页合并的产生
所以一般主键要遵循以下原则
- 满足业务需求的情况下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择使用 AUTO_INCREMENT 自增主键
- 尽量不要使用 UUID 做主键或者是其他自然主键,如身份证号
- 业务操作时,避免对主键的修改
order by 优化
order by 时,在额外内容中可以看见使用索引还是全表扫描
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
- Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高
如果建立了联合索引,排序的数据与索引的顺序有关
这里建立了 ASC 的age 和phone
设计原则
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则,排序字段与索引字段的顺序有关
- 尽量使用覆该索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
- 如果不可避免的出现了 filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认 256k)
group by 优化
- 在分组操作时,额可以通过索引来提高效率
- 分组操作时,索引的使用也是满足最左前缀法则的
limit 优化
一个常见又非常头疼的问题就是 limit 2000000,10,此时需要 MySQL 排序前20000010记录,仅仅返回 20000000-20000010的记录,其他记录丢弃,查询排序的代价非常大
优化思路:一般分页查询时,通过创建覆盖索引能够比较好的提高性能,可以通过覆盖索引加子查询形式进行优化。
count 优化
count一般是数据库设计的问题,这个通常情况下是无法进行优化的,但合理的使用count也可以提高查询效率
- MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*)的时候会直接返回这个数,效率很高
- InnoDB 引擎就比较麻烦,它执行 count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后积累计数
优化思路:count效率低是 MySQL数据库的优化不好,想要优化只有自己计数
count 的几种用法:
- count(主键):InnoDB 引擎会遍历整张表,把每一行的 主键 id值都取出来,返回给服务层,服务层拿到主键后,直接按行进行累加(主键不可能为null)
- count(字段): ① 没有 not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为 null,不为null,计数累计。 ②有 not null 约束:引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接进行累加
- count(1):InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一行数据,放一个数字‘1’进去,直接按行进行累加
- count(*):InnoDB 引擎并不会把字段全部取出来,而是专门做了优化,不取值,服务层直接按行进行累加
按照效率进行排序的话:
count(字段)< count(主键)< count(1)≈ count(*)
所有尽量使用 count(*)
update 优化
InnoDB 引擎中的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁
在 update 操作中,操作一条数据会加锁,如果操作(where后的字段)的是有索引的字段会给该行加行锁,但如果操作的是没有索引的字段,那么就会导致表锁
以上即是我总结的 SQL 优化,当然 SQL 优化不止这几种,百度一搜一大堆,少说也得几十种,但一般的 SQL 优化都是围绕着索引来进行优化的。
如果有啥问题,欢迎各位大佬指出。
看都看到这里了,制作不易,点个赞在走吧!!!!