MySQL高性能实战——part4——高性能索引使用

已于 2022-03-02 17:47:25 修改
阅读量631 收藏
点赞数 2
分类专栏： 数据库 面试题 文章标签： 面试 mysql 数据库开发
于 2022-03-02 11:57:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45929082/article/details/123226709
版权

前言：
此文借鉴《MySQL高性能》一书，还有MySQL官方文档，笔者将通过自身的一些实战经验和阅读习惯对本书进行一个总结，整理，归纳出企业级开发中常用的优化案列和部分概念！！
​

官方文档地址：
https://dev.mysql.com/doc/refman/5.7/en/
​

本文：由于数据库知识庞大且复杂，笔者将以，以下几个模块进行拆分解读，请大家按需自取！！！

  1. MySQL高性能实战——part1——MySQL架构与历史
  2. MySQL高性能实战——part2——Schema和数据类型优化
  3. MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)
  4. MySQL高性能实战——part4——高性能索引使用
  5. MySQL高性能实战——part5——查询性能优化
  6. MySQL主从复制与读写分离，分库分表

​

​

​

版本说明：
不同数据库版本可能对SQL的执行以及部分特性产生巨大的影响，所以在这里明确一下版本
参考书籍版本：5.1或5.5
实战案列实际使用版本：5.7
​

MySQL高性能实战——part4——高性能索引使用

一.索引基础

1.什么是索引

索引是帮助快速获取查询结果的一种数据结构！！！本质：索引是数据结构！,排好序的快速查找数据结构！
​

要理解 MySQL中索引是如何工作的,最简单的方法就是去看看一本书的“索引”部分:如果想在一本书中找到某个特定主题,一般会先看书的“索引”,找到对应的页码。
在 MySQL中,存储引擎用类似的方法使用索引,其先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。

2.索引的功能

  1. 影响where条件查找
  2. 影响order by排序
  3. 影响group by分组
  4. 影响查询是否回表，通过索引直接获取数据不用回表更快
  5. 加快连接表的速度

​

3.索引的优缺点

优点：

  • 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的I0成本，避免全表扫描
  • 通过索引列对数据进行排序**，降低数据排序的成本，降低了CPU的消耗**
  • 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。


缺点：

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
  • 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，**就需要花时间研究建立最优秀的索引，或优化查找效率，**建立在常用的字段，不一定是按照我们事先设计的，而且根据用的实际使用情况分析，举列用户使用淘宝一般进行筛选都是多个字段，取那些是经常搜索的建立索引！

​

​

4.索引选择性是什么？

索引的选择性是指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值。
索引的选择性越高则査询效率越高,因为选择性高的索引可以让 My SQL在査找时过滤掉更多的行。
唯一索引的选择性是1,这是最好的索引选择性，性能也是最好的。
​

怎么计算？

select (count(distinct [需要计算选择性的列]	)/count( * ))
from index_test

#下面这个列子就是计算name字段上创建索引，索引的选择性
select (count(distinct name)/count( * ))
from index_test

​

5.索引的基本操作命令

创建索引的命令
#方式1
ALTER TABLE 表名 ADD INDEX 索引名 (列A,列B);

#方式
CREATE INDEX 索引名 ON 表名(列A,列B);

​

查看索引

show index from 表名;
​

删除索引

drop index 索引名 on 表名;
​

​

6.索引的最左匹配原则

如果用到复合索引，一定要以复合索引开头或者说最左的那个位置作为最开始的筛选条件，如果复合索引创建了3列，如果没有用到第一列或者只有了1 3列中间有间隔都会导致索引失效
​

举例：
创建了这样的复合索引

create index index_name_age_phone on index_test(name,age,phone);


情况1：全部索引列都使用了，效率高

explain
select *
from index_test
where name='cc' and age =24  and phone = '17152154499'

image.png




情况2：只用了首个索引列，效率高

explain
select *
from index_test
where name='cc'

image.png




**情况3：没有使用索引首列，也就是最左列,**索引失效

explain
select *
from index_test
where age =24  and phone = '17152154499'

image.png




情况4：使用了首个索引列，但中间条件的索引列是范围值，导致后半部分索引失效

explain
select *
from index_test
where name ='cc' and age >24  and phone = '17152154499'

image.png
​

​

​

二.索引优化实战


优化之前我们先来看一下基本的索引类型，才能够明白在什么业务场景下用什么索引

1.索引的类型与对应独特的优化案列

create table index_test(
    id bigint unsigned auto_increment  primary key comment '主键ID',
    name varchar(20) not null comment '姓名' ,
    age tinyint unsigned not null comment '年龄' ,
    phone varchar(20) not null comment '电话',
    autobiography_title char(100) comment '自传标题',
    autobiography_body text comment '自传内容',
  	create_time   datetime        
)charset =utf8,engine =innodb;
1.单值索引

即一个索引只包含单个列，一个表可以有多个单列索引
create index index_phone on index_test (phone);
​

释义：
创建索引index_phone ，在index_test这张表上 ，索引列为phone
​

2.唯一索引

与普通索引类似，不同的是，唯一索引的列值必须唯一，但允许为空值。
create unique index index_phone on index_test (phone);
​

释义：
创建唯一索引index_phone ，在index_test这张表上 ，索引列为phone

3.主键索引

也是唯一索引，见主键索引和唯一索引的区别
id bigint unsigned auto_increment primary key comment '主键ID'
​

释义：
创建主键时就会有主键索引

4.组合索引

即一个索引包含多个列
create index index_phone_name_age on index_test (phone,name,age);
​

释义：
创建唯一索引index_phone_name_age ，在index_test这张表上 ，索引列为phone,name,age
​

很多人对多列索引的理解都不够。一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引。
​

如：

CREATE TABLE t(
c1 int,
c2 int,
c3 int,
KEY(c1),
KEY(c2),
KEY(c3)
);

​

这种索引策略,一般是由于人们听到一些专家诸如“把 WHERE条件里面的列都建上索引这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引,其性能比起真正最优的索引可能差几个数量级。有时如果无法设计个“三星”索引,那么不如忽略掉 WHERE子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引。在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL的查询性能。 MySQL5.0和更新版本引入了一种叫“索引合并”( index merge)的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的 MySQL只能使用其中某一个单列索引,然而这种情况下没有哪一个独立的单列索引是非常有效的。
​

在创建组合索引时，建议多多考虑到索引的顺序，主要从一下几个层面去考虑

  1. 根据索引的最左匹配原则（详情见上文），选择系统实际运行中，在每个查询时选择性最高的查询条件
  2. 如果不确定那个查询条件是用户最常用的，那么就选择索引选择性高的列，作为最前列
  3. 如果是统计，排序，分组比较多的 根据具体的查询条件，在这些字段上创建组合索引


​

​

5.全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询了。
​

但是,如果你希望通过关键字的匹配来进行査询过滤,那么就需要基于相似度的查询,而不是原来的精确数值比较。全文索引就是为这种场景设计的。
​

全文索引有着自己独特的语法。没有索引也可以工作,如果有索引效率会更高。用于全文搜索的索引有着独特的结构,帮助这类查询找到匹配某些关键字的记录。
​

你可能没有在意过全文索引,不过至少应该对一种全文索引技术比较熟悉:互联网搜索引擎。虽然这类搜索引擎的索引对象是超大量的数据,并且通常其背后都不是关系型数据库,不过全文索引的基本原理都是一样的。
​

全文索引可以支持各种字符内容的搜索(包括CHAR、 VARCHAR和TEXT类型),也支持自然语言搜索和布尔搜索。在 MySQL中全文索引有很多的限制,其实现也很复杂,但是因为它是 MySQL内置的功能,而且满足很多基本的搜索需求,所以它的应用仍然非常广泛。
​

不建议使用，不好用，推荐ES或Slor，主要分词很差劲
​

创建全文索引
create fulltext index index_fulltext on index_test(autobiography_title,autobiography_body);
​

释义：
创建全文索引index_fulltext，在index_test这张表上 ，索引列为autobiography_title,autobiography_body
​

通过全文索引进行查询
select *<br />from index_test<br />where match(autobiography_title,autobiography_body) against('mysql')
​

​

6.聚簇索引

**主键索引也算是聚簇索引 **
​

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于其实现方式,但 InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行当表有聚簇索引时,它的数据行实际上存放在索引的叶子页( leaf page)中。
​

术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引(不过,覆盖索引可以模拟多个聚簇索引的情况)。
​

因为是存储引擎负责实现索引,因此不是所有的存储引擎都支持聚簇索引。主要关注 InnoDB,但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。


下图展示了聚簇索引中的记录是如何存放的。注意到,叶子页包含了行的全部数据,但是节点页只包含了索引列。在这个案例中,索引列包含的是整数值。
image.png


InnodB将通过主键聚集数据,这也就是说上图中的“被索引的列”就是主键列。
如果没有定义主键, InnodB会选择一个唯一的非空索引代替。
**如果没有这样的索引,Innodb会隐式定义一个主键来作为聚簇索引。 **
innodb只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。
​

聚簇主键可能对性能有帮助,但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引,尤其是将表的存储引擎从 InnoDB改成其他引擎的时候(反过来也一样)。
​

聚集的数据有一些重要的优点:

  1. 可以把相关数据保存在一起,可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户ID来聚集数据,

这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用
聚簇索引,则每封邮件都可能导致一次磁盘IO。

  1. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
  2. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

​

如果在设计表和查询时能充分利用上面的优点,那就能极大地提升性能。
​

​

缺点：

  1. 聚簇数据最大限度地提高了IO密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势了。
  2. 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据,那么在加载完成后最好使用0PTIMIZE TABLE命令重新组织一下表。
  3. 更新聚簇索引列的代价很高,因为会强制 InnoDB将每个被更新的行移动到新的位置
  4. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂( page split)的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作页分裂会导致表占用更多的磁盘空间
  5. 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
  6. 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。
  7. 二级索引访问需要两次索引查找,而不是一次。

​

最后一点可能让人有些疑惑,为什么二级索引需要两次索引查找?
答案在于二级索引中保存的“行指针”的实质。要记住,二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。
这意味着通过二级索引査找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作:两次B-Tree查找而不是一次
​

大家可以看下图理解：
image.png
也可以看看这篇博文
https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_9016102232195953742%22%7D&n_type=-1&p_from=-1


7.前缀索引

有时候需要索引很长的字符列,这会让索引变得大且慢。
通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。
一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。对于BL0B、TEXT或者很长的 VARCHAR类型的列,必须使用前缀索引,因为 MySQL不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。
前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。换句话说,前缀的“基数”应该接近于完整列的“基数”。
​

注：
索引选择性看前面案列
​

我们以下面这2个查询列子，来说明怎么选择更合适的前缀索引
但我们前缀索引为前14位字符时，重复值很多

select count(*) as total ,left(order_no,14)as pref
from order
group by pref
order by total desc
limit 10

total,pref
919,81833190128000
771,81833190522000
744,81833190125000
741,81833190325000
720,81833190520000
664,81833190521000
645,81833190509000
621,81833190127000
563,81833190510000
498,81833190514000

​

但我们前缀索引为前16位字符时，重复值很多

select count(*) as total ,left(order_no,18)as pref
from order
group by pref
order by total desc
limit 10

91,DNBEJB001200807000
89,DNBEJB001211227000
81,DNBEJB001201027000
81,DNBEJB001201027001
74,DNBEJB001191104000
71,DNBEJB001210130000
65,DNBEJB001211228000
64,DNBEJB001201221000
59,DNBEJB001211229000
56,DNBEJB001211227001

可以看到我们的选择性提高了10倍，由此来判断决定我们前缀索引，截取多少前缀比较合适！
​

​

那么如何创建前缀索引呢？
这里的18就代表截取的字符串长度

create index index_pref_autobiography_body on index_test (autobiography_body(18));

​

​

效果演示：

explain
select * from index_test where autobiography_body like "123456789987654321%";

image.png
成功命中索引，要比去查询 整个字符串要快很多
​

​

8.覆盖索引


**通常大家都会根据查询的 WHERE条件来创建合适的索引,不过这只是索引优化的一个方面**。设计优秀的索引应该考虑到整个查询,而不单单是 WHERE条件部分。
​

索引确实是种查找数据的高效方式,但是 MySQL也可以使用索引来直接获取列的数据,这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据,那么还有什么必要再回表查询呢?
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”
​

​

覆盖索引的好处？（覆盖索引是非常有用的工具,能够极大地提高性能）

  1. 索引条目通常远小于数据行大小,所以如果只需要读取索引,那** MySQL就会极大地减少数据访问量**。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。
  2. 覆盖索引对于IO密集型的应用也有帮助,因为索引比数据更小更容易全部放入内存中(这对于 MyIsAM尤其正确,因为 MyISAM能压缩索引以变得更小)
  3. 因为索引是按照列值顺序存储的(至少在单个页内是如此),所以对于I/O密集型的范围査询会比随机从磁盘读取毎一行数据的I/O要少得多。对于某些存储引擎,例如 MyISAM和 Percona XtraDB,甚至可以通过0PTIMIZE命令使得索引完全顺序排列,这让简单的范围查询能使用完全顺序的索引访问。
  4. 一些存储引擎如 MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。这可能会导致严重的性能问题,尤其是那些系统调用占了数据访问中的最大开销的场景
  5. 由于 InnoDE的聚簇索引,覆盖索引对 InnoDB表特别有用。 InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询
  6. 可以优化like查询（大概效率会快2-3倍！！大概有150万的数据）
  7. like查询时，覆盖索引延迟关联，优化SQL(适用于需要查询X表的全部列的数据，假如x表有(id,A,B,C,D,E)6个字段，但是只有在A,B,C字段上有索引，但需要查询全部字段，且是模糊查询，怎么利用好目前建立索引快速完成查询)（如下案列3）
#1.索引失效  满足我们的需求 
select * 
from  x
where a like '%mysql%'

#2.使用索引  	但不满足我们的需求 
select id,A,B,C
from  x
where a like '%mysql%'

#3.优化案列 
select *
from x 
where id = (select id from x where like '%mysql%')


覆盖索引优化like查询的案列
索引列
image-20210624170026048.png

优化前：explain select * from oc_ofapp_account WHERE mobile like '%13123910157%'全表扫描，索引失效
image-20210624165808187.png


优化后：

explain select mobile from oc_ofapp_account WHERE mobile like '%13123910157%'

将使用覆盖索引
​

​

覆盖索引的失效情况？

  1. 查询的字段，不能被任何一个索引全部覆盖
  2. 查询的字段有被索引覆盖，但是where条件后的字段不能被索引覆盖

​

覆盖索引适用的索引类型？
不是所有类型的索引都可以成为覆盖索引。**覆盖索引必须要存储索引列的值,**而哈希索引、空间索引和全文索引等都不存储索引列的值,所以 MySQL只能使用B-Tree索引做覆盖索引。另外,不同的存储引擎实现覆盖索引的方式也不同,而且不是所有的引擎都支持覆盖索引，当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时,在 EXPLAIN的 Extra列可以看到“ Using index”的信息。
如下列子：
我们在index_test表上，有一个多列索引，包含name,age,phone字段，
image.png
​

​

主键索引和唯一索引的区别
  • 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
  • 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
  • 唯一性索引列允许空值，而主键列不允许为空值。
  • 主键列在创建时，已经默认为空值 + 唯一索引了。
  • 主键可以被其他表引用为外键，而唯一索引不能。
  • 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
  • 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

​

​

​

2.使用索引优化排序

MySQL有两种方式可以生成有序的结果

  1. 通过排序操作
  2. 按索引顺序扫描


注：
如果EXPLAIN出来的type列的值为“ index”,则说明 MySQL使用了索引扫描来做排序(不要和 Extra列的“ USing index”搞混淆)
​

扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖査询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机IO,因此按索引顺序读取数据的速度通常要比顺序地全
**表扫描慢,(所以使用我们的覆盖索引和索引排序一起使用才能达到较好的效果 下面会有案列)**尤其是在I/O密集型的工作负载时。
​

MySQL可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能地同时满足这两种任务,这样是最好的。


​

案列一：（排序字段不是索引列，导致**using fliesort**）

age字段没有索引，对age字段进行排序

mysql> explain
    -> select age
    -> from index_test
    -> order by age;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | index_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

​

​

什么是using fliesort，如果避免不了using fliesort怎么优化，什么是单路排序和双路排序？

如果索引不能用于满足 ORDER BY子句，MySQL 将执行filesort读取表行并对它们进行排序的操作
​

​

filesort文件排序方式分为

  1. **单路排序 **
    1. 是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
  2. 双路排序 （又叫回表排序模式）
    1. 是首先根据相应的条件取出相应的 排序字段 和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

可以很明显的得知，双路排序的速度肯定是要比单路排序的速度满，因为要多一次回表操作，当无法避免排序时，又无法使用索引很好的完成排序，又使用了双路排序我们应该怎么办
[

](https://blog.csdn.net/sunshine151/article/details/122052019)
怎么判断是单路排序还是多路排序
Mysql通过比较系统变量max_length_for_sort_data的大小和需要查询的字段总大小来判断使用哪种排序模式
如果max_length_for_sort_data比查询字段的总长度大，使用单路排序
如果max_length_for_sort_data比查询字段的总长度小，使用双路排序
​

​

​

​

案列二：（根据索引列排序，索引列未包含全部查询列，导致回表查询，索引失效）

已知age字段上有索引create index index_age on index_test(age);

#索引成功命中
mysql> explain
    -> select age,id
    -> from index_test
    -> order by age ;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | index | NULL          | index_age | 1       | NULL |    2 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

#索引成功命中
mysql> explain
    -> select age,id
    -> from index_test
    -> order by age desc;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | index | NULL          | index_age | 1       | NULL |    2 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

#索引失效
mysql> explain
    -> select *
    -> from index_test
    -> where name = '承承'
    -> order by age desc;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | index_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

​

​

结论：

  1. 只查询被索引覆盖列，索引不失效，且排序字段不管是asc还是desc
  2. 查询中包含索引未覆盖的列，需要回表索引失效，出现文件内排序Using filesort；

​

​

​

案列三：（多列索引，排序字段不满足，最左前缀原则，索引失效）

已知age字段上有索引create index index_create_time_age on index_test(create_time,age);

#不满足最左前缀原则   索引失效 文件排序  临时表
mysql> explain
    -> select age
    -> from index_test
    -> order by age;
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | index_test | NULL       | index | index_create_time_age | index_create_time_age | 7       | NULL |    2 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+----------------------------------------------+


#满足最左前缀原则
mysql> explain
    -> select create_time,age
    -> from index_test
    -> order by create_time,age;
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | index | index_create_time_age | index_create_time_age | 7       | NULL |    2 |   100.00 | Using index |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------+

​

案列四：（多列索引，又不想根据前面的左前缀排序，又想使用索引排序优化）

只要我们的多列索引的前缀是等值的，我们后面排序的字段就可以使用到索引

mysql> explain
    -> select age
    -> from index_test
    -> where create_time ='2020-01-01'
    -> order by age;
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | index_test | NULL       | ref  | index_create_time_age | index_create_time_age | 6       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+--------------------------+

3.使用索引优化分组

见2.使用索引优化排序，思想都一样
​

4.使用索引优化连表查询

在做连表查询的时候，当两张表关联的数据量过于多的时候，往往会出现速度较慢的问题，这个时候可以在被连接的表上创建索引来加快连表速度
​

​

案列：
此时我们使用sql脚本生成2张表，A表有（1w条数据），B表有（100w数据）

#创建a表 id列有主键索引  no_index_id没有索引
create table a(
    id bigint UNSIGNED primary key,
    no_index_id    bigint UNSIGNED
)charset = utf8,engine =INNODB;

#创建存储过程init_data，向a表插入数据
CREATE PROCEDURE init_data(IN count INT)
BEGIN DECLARE i INT DEFAULT 1;
    WHILE i <=count
    DO
    INSERT INTO a(id,no_index_id) VALUES(i,i);
    SET i = i + 1;
    end while;
end;
#调用存储过程向a表插入1万条数据
call init_data(10000);

#查询数据是否生成
select count(*) from a;

#创建b表 id列有主键索引  no_index_id没有索引
create table b(
    id bigint UNSIGNED primary key,
    no_index_id    bigint UNSIGNED
)charset = utf8,engine =INNODB;

#创建存储过程init_data_two，向b表插入数据
CREATE PROCEDURE init_data_two(IN count INT)
BEGIN DECLARE i INT DEFAULT 1;
    WHILE i <=count
    DO
    INSERT INTO b(id,no_index_id) VALUES(i,i);
    SET i = i + 1;
    end while;
end;

#调用存储过程向a表插入100万条数据
call init_data_two(1000000);

#查询数据是否生成
select count(*) from b;


这个时候我们知道，id列是有主键索引的，no_index_id列是没有主键索引的，我们通过2条SQL来对比连表有索引和没索引的差距

# 索引失效 可以看到B表是通过全表扫描去匹配a表的连接字段的 ，每一次连接都需要全表扫描性能很差
# 去除explain关键字 执行时长为500 rows in set (15.97 sec)
mysql> explain
    -> select *
    -> from a
    -> left join b on a.id  = b.no_index_id
    -> where a.id>=9500;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    501 |   100.00 | Using where                                        |
|  1 | SIMPLE      | b     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 982954 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+


# 使用主键索引 可以看到2张表都使用了主键索引,B表是通过主键索引，去匹配a表的连接字段的 
# 每一次连接只需要去索引上找，只需通过3次IO就能找到，而无需与100万数据去进行对比
# 去除explain关键字 执行时长为500 rows in set (0.01 sec) 速度提升N倍
mysql> explain
    -> select *
    -> from a
    -> left join b on a.id  = b.id
    -> where a.id>=9500;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | range  | PRIMARY       | PRIMARY | 8       | NULL             |  501 |   100.00 | Using where |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | mysql_study.a.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+

​

通过刚刚的案列我们就可以看出，使用索引连表效率的显著提升
无论是双表，还是三表，四表，n表连接，都可以在连接字段建立索引来进行优化




5.索引失效的情况，以及避免索引失效

像上面的列子都举例过具体的索引失效的案列，这里主要是总结索引失效的情况

  1. 索引列上做操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  2. 联合索引要满足最左前缀原则，在满足最左前缀的前提下范围条件右边的列不能使用索引，左边的列可以使用
  3. 联合索引的所有字段无法包含全部查询的列，会导致索引失效
  4. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
  5. is null,is not null也无法使用索引
  6. like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作，可以使用覆盖索引优化
  7. 字符串不加单引号索引失效


​

​

6.索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行,那么就会锁定更少的行,从两个方面来看这对性能都有好处。
首先,虽然 InnodB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外开销;其次,锁定超过需要的行会增加锁争用并减少并发性。
InnodB只有在访问行的时候才会对其加锁,而索引能够减少 InnodB访问的行数,从而减少锁的数量。但这只有当 InnodB在存储引擎层能够过滤掉所有不需要的行时才有效。
​

详细的案列可以看1.MySQL架构与历史—>二.并发控制
如果不使用索引可能会导致锁表或者间歇锁导致影响性能
​

7.冗余索引和重复索引

**MySQL允许在相同列上创建多个索引,**无论是有意的还是无意的。
MySQL需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能。
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。
​

比如：
test表有一个单值索引（A列），后面又创建复合索引（A列，B列），这里单值索引（A列）就是冗余索引，需要尽快删除，因为索引的最左前缀原则，在where A列=？ 时是可以使用到复合索引的

妈妈的好大儿(✪ω✪)
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
R语言笔记⑥——网络爬虫
ww0peo的博客
03-01 2262
爬虫 假设有如下页面 下面将新闻的内容提取出来 > url <- 'https://mbd.baidu.com/newspage/data/landingsuper?context=%7B"nid"%3A"news_9194136950339436704"%7D&n_type=0&p_from=1' > web <- read_html(url,enc...
经典问题-思路及解决方案
Dawn____Dawn的博客
04-22 1020
Redis分布式锁的实现和原理 https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_9114102064283707987%22%7D&n_type=1&p_from=4 高并发下秒杀商品 https://baijiahao.baidu.com/s?id=1707212968925400682&wfr=spider&for=pc ............
主键、聚集索引、非聚集索引区别
渣渣
07-14 5910
下面是一个简单的比较表 主键 聚集索引 用途 强制表的实体完整性 对数据行的排序，方便查询用 一个表多少个 一个表最多一个主键 一个表最多一个聚集索引 是否允许多个字段来定义 一个主键可以多个字段来定义 一个索引可以多个字段来定义 是否允许 null 数据行出现 如果要创建的数据列中数据存在null，无法建立主键。 创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。 ...
聚簇索引与主键的选择
Azhuzhu_chaste的博客
03-27 2609
聚簇索引与主键的选择一、什么是聚簇索引？二、什么是非聚簇索引？1. InnoDB引擎中2. MyISAM引擎中三、聚簇索引的优劣与主键选择的关系 一、什么是聚簇索引？ 首先，聚簇索引不是一种单独的索引类型，其实是数据的存储方式。聚簇索引将数据存储与索引放在了一起，找到了索引也就找到了数据。 在MySql的InnoDB引擎中，表数据的文件是按照B+树组织的一个索引结构。而聚簇索引就是按照每张表的主键...
目标检测与跟踪概述
TEDDY的博客
05-06 1342
目标检测之传统检测器 https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_10198071064666717622%22%7D&n_type=1&p_from=4 目标检测与跟踪概述 https://mbd.baidu.com/newspage/data/landin...
[高性能MySQL]-创建高性能的索引
惜暮
02-25 824
本文是关于创建索引的的内容： （1） 索引的类型 （2）索引的优点 （3）优化索引的策略 这里给出一个索引的思维导图： 索引是存储引擎用于快速找到记录的一种数据结构。索引是对 查询性能优化 最有效的手段了，索引能够轻松将查询性能提升几个数量级。索引我们一般都是对某一列加索引。存储引擎先在索引中找到对应值，然后根据匹配的索引记录上的rowid找到对应的数据行。比如运行如下查询语句：
高性能MySQL-创建高性能索引
最新发布
ylx1066863710的博客
11-27 541
高性能MySQL第五章创建高性能索引的读书笔记
高性能mysql——高清版
08-31
高性能mysql——高清版
Java高级编程——MySQL索引实现及优化原理解析
02-25
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的...
mysql高性能索引读书笔记
11-01
《高性能Mysql》第五章的读书笔记，记录的都是要点，以思维导图的方式呈现。
第1课-高性能Mysql运维应用实战第一集.flv
06-15
高性能Mysql运维应用实战
简述--------Mysql索引机制到底什么？（面试必备）
qq_43590771的博客
09-03 444
参考：https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_10409517059295814828%22%7D&n_type=1&p_from=4 1.什么是索引？ 在关系数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构，它是某个表中的一列或者若干列值的集合和相应的指向表中物理标识这些值得数据页得逻辑指针清单。 索引的作用相当于图书的目录，可以根
聚簇索引与非聚簇索引
cuantang8423的博客
08-08 338
索引有两种类型：聚簇索引和非聚簇索引。在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与...
高性能的Mysql读书笔记系列之五（创建高性能的索引）
jdk_wangtaida的博客
09-21 1896
前言： 正文： 总结：
聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
热门推荐
记录一些工作日常和学习
02-23 5万+
本文转自https://my.oschina.net/u/1866821/blog/297673 索引的重要性 数据库性能优化中索引绝对是一个重量级的因素，可以说，索引使用不当，其它优化措施将毫无意义。 聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index) 最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引
聚簇索引和非聚簇索引
lm1060891265的博客
08-07 1万+
参考博客：http://www.admin10000.com/document/5372.html 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于其实现方式。 MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustere...
数据库中关于主键和聚簇索引的概念
qq_41592403的博客
09-01 992
主键 唯一非空，一个表只能存在一个，它用来唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义 PRIMARY KEY 约束来创建主键。 聚簇索引 聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。 聚簇索引的列不一定是非空唯一的列，mysql，sqlserver一般把主键作为聚簇索引列，而oracle 和达梦数据库可以不把主键列当作聚簇索引列，它们会在创建表时未指
mysql聚簇索引 和主键的区别_[MySQL] innoDB引擎的主键与聚簇索引
weixin_39620943的博客
02-08 748
MysqL的innodb引擎本身存储的形式就必须是聚簇索引的形式,在磁盘上树状存储的,但是不一定是根据主键聚簇的,有三种情形:1. 有主键的情况下,主键就是聚簇索引2. 没有主键的情况下,第一个非空null的唯一索引就是聚簇索引3. 如果上面都没有,那么就是有一个隐藏的row-id作为聚簇索引大部分情况下,我们建表的时候都会创建主键,因此大部分都是根据主键聚簇的当我们根据主键字段来进行查询时,效率...
mysql中聚簇索引和主键索引
喜欢分享,善于记录工作中遇到的问题,和自己的思考
10-29 2119
索引分为两种: 聚簇索引:也称聚集索引,①一般建表时的主键就会被mysql作为聚簇索引,②如果没有主键,则选择非空唯一的索引作为聚簇索引,③都没有则隐式创建一个索引作为聚簇索引 辅助索引:也称非聚簇索引或二级索引,平时我们添加的索引就是辅助索引 聚簇索引和主键索引 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。 聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。 查询时用到的所有顺序 主键查询:由于主键被作为聚簇索引
Linux公社：MySQL高性能实战教程
该书详细讲解了MySQL的核心功能，如数据表设计、查询优化、索引策略、事务处理、存储引擎选择以及性能监控等方面。 书中首先会介绍MySQL的基础知识，如安装与配置，SQL语法的解析，以及如何创建和管理数据库及表格...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值