mysql索引分享

最新推荐文章于 2024-07-13 10:38:18 发布
最新推荐文章于 2024-07-13 10:38:18 发布
阅读量884 收藏 18
点赞数 30
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42559561/article/details/135290767
版权

1、索引

索引有什么作用

索引的结构是什么样的

如何建立索引

什么情况下使用索引

如何利用索引优化查询

为什么使用索引

  • 避免对全表的扫描

  • 在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而查找到数据。

  • 扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

1.1、索引分类

  • 从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

  • 从应用层次来分：普通索引，唯一索引，复合索引

  • 根据数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

1.2、索引介绍

1.2.1、BTree索引

测试

B-Tree

  1. 特征

    1. 元素只出现一次

    2. 非叶子节点也存储数据

  2. 三阶B树

  3. 查找

    1. 在B树上找到某个结点后，先在有序表中进行查找，若找到则查找成功，否则按照对应的指针信息到所指的子树中去查找。查找到叶结点时（对应指针为空指针），则说明树中没有对应的关键字，查找失败。

  4. 插入

    1.  定位。利用B树查找算法，找出插入该关键字的最低层中的某个非叶结点。

    2. 插入。在B（m阶）树中，每个非叶子结点的关键字的个数都在区间[ m / 2  − 1 , m − 1 ]内。

      1. 插入后的结点关键字个数小于m ，可以直接插入；

      2. 当插入后的结点关键字个数大于m − 1时，必须对结点进行分裂。

      3. 分裂的方法：

        • 取一个新结点，在插入key后的原始结点，从中间位置将其关键字分为两部分：

        • 左部分包含的关键字放在原始结点中；

        • 右部分包含的关键字放在新结点中；

        • 中间位置（[m / 2] ）的结点插入原结点的父结点。

  5. 删除

    1. 删除的关键字不在终端结点（最低层非叶结点）    

      1. 前驱取代。若小于k的子树中关键字个数＞  m / 2 − 1 ＞，则找出k的前驱值k ′，并用k ′ 来取代k，再递归地删除k即可。

      2. 后继取代。若大于k的子树中关键字个数＞  m / 2 − 1 ＞则找出k的后继值k ′，并用k ′来取代k，再递归地删除k即可。

      3. 子节点合并。若前后两个子树中的关键字个数均为 m / 2  − 1 ，则直接将两个子结点合并，直接删除k即可。

    2. 删除的关键字在终端结点（最低层非叶结点）

      1. 直接删除关键字。若被删除关键字所在结点的关键字个数 >  m / 2 − 1 ，表明删除该关键字后仍满足B树的定义，则直接删去该关键字。

      2. 兄弟够借。若被删除关键字所在结点删除前的关键字个数 =  m / 2  − 1 ，且与此结点相邻的右（左）兄弟结点的关键字个数 >= m / 2 − 1 ，则需要调整该结点、右（左）兄弟结点以及其双亲结点（父子换位法），以达到新的平衡。

        1. 将借来的关键码上移到被删结点的双亲结点中，将双亲结点中相应的关键码下移

      3. 兄弟不够借。若被删除关键字所在结点删除前的关键字个数 =  m / 2  − 1 ，且此时与该结点相邻的右（左）兄弟结点的关键字个数 =  m / 2  − 1 ，则将关键字删除后与右（左）兄弟结点及双亲结点中的关键字进行合并。

        1. 被删结点与兄弟结点合并成一个结点；并将双亲中它们所夹的关键码下移

B+Tree

  1. 特征

    1. 叶子节点存放数据信息以及数据的地址，非叶子结点存放索引

    2. 中间节点的数据在子节点中也存在，且是最值元素，元素多次出现

    3. 叶子节点构成一个链表（适合范围查询）

  2. 三阶B+树

  3. msyql默认索引结构

  4. 查找

    1. 单个元素

      1. 在查找到某一节点时，判断查找元素所在的子节点，知道叶子节点中，遍历叶子节点是否存在查找的值。B+树中查找任何一个元素都要从根结点一直走到叶子结点才可以。即使在根节点中已存在，但那只是查找控制的值，并非数据本身。

    2. 区间查找

      1. 先查找 查找区间的最小值，由于B+树的子节点是链表，直接从最小值遍历到查找区间的最大值

  5. 插入

    1. 若被插入关键字所在的结点，其含有关键字数目小于阶数 m，则直接插入；

    2. 若被插入关键字所在的结点，其含有关键字数目等于阶数 m，则需要将该结点分裂为两个结点，一个结点包含 m/2，另一个结点包含 m/2-1。同时，将m/2的关键字上移至其双亲结点。

      1. 假设其双亲结点中包含的关键字个数小于 m，则插入操作完成。

      2. 如果上移操作导致其双亲结点中关键字个数大于 m，则应继续分裂其双亲结点。

    3. 若插入的关键字比当前结点中的最大值还大，破坏了B+树中从根结点到当前结点的所有索引值，此时需要及时修正后，再做其他操作。

  6. 删除

    1. 找到存储有该关键字所在的结点时，当结点中关键字个数大于m/2，做删除操作不会破坏 B+树，则可以直接删除。

    2. 当删除某结点中最大或者最小的关键字，就会涉及到更改其双亲结点一直到根结点中所有索引值的更改。

    3. 当删除该关键字，导致当前结点中关键字个数小于 m/2

      1. 若其兄弟结点中含有多余的关键字，可以从兄弟结点中借关键字完成删除操作。

      2. 如果其兄弟结点没有多余的关键字，则需要同其兄弟结点进行合并。

    4. 当进行合并时，可能会产生因合并使其双亲结点破坏 B+树的结构，需要依照以上规律处理其双亲结点。

    5. 总结

      1. 删除该关键字，如果不破坏 B+树本身的性质，直接完成删除操作

      2. 如果删除操作导致其该结点中最大（或最小）值改变，则应相应改动其父结点中的索引值

      3. 在删除关键字后，如果导致其结点中关键字个数不足，有两种方法：一种是向兄弟结点去借，另外一种是同兄弟结点合并。（注意这两种方式有时需要更改其父结点中的索引值。）

对比

  1. B-树内部节点是保存数据的;而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。相同的数据量，B+树更矮壮，也是就说，B+树数据结构，查询磁盘的次数会更少。

  2. B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。

  3. 查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束，B+树的查询是稳定的，必须走到叶子节点。

  4. B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次，但只做查找的条件限制，并非数据本身

问题：自增主键与uuid主键谁好，为什么

在进行数据库插入时，位置相对固定（B+树中的右下角）增加数据插入效率，减少插入的磁盘IO消耗，每页的空间在填满的情况下再去申请下一个空间，底层物理连续性更好，能更好的支持区间查找

由于UUID是随机生成的 插入时位置具有一定的不确定性，无序插入，会存在许多内存碎片，内存空间的占用量也会比自增主键大，区间查找也没自增主键性能优

  • 其他树型结构

    • 树形高

    • 或插入删除操作麻烦

不适合拿来做索引

1.2.2、Hash索引

  • 建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引的哈希码(HashCode)，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。

  • 在MySQL中，只有memory存储引擎支持显式的哈希索引。如果多个值有相同的哈希码，索引就会把行指针以链表的方式保存在哈希表的同一条记录中。

explain 
select * from goods where url = 'https://www.etsy.com/listing/15095113154/32828dad-9ed4-4dac-aca0-ea7af899de62?click_key=13110495-e216-4b81-b13f-0dc830db759b&click_sum=23452&ref=1'

alter table goods add index url_hash_index(url) using hash

  • 伪哈希索引 例如 存储URL进行编码索引crc32(url) 可结合触发器计算新字段的值 建立has_url的索引。

explain
select  * from goods   where crc_url = '2147483647' and url = 'https://www.etsy.com/listing/15831137906/3ba16a13-507e-49e7-83c5-781af4c74bfb?click_key=d3cfa89f-02a2-4806-99c1-1a65d6f6e4ab&click_sum=1020&ref=1'

explain select  * from goods where crc_url = CRC32('https://www.etsy.com/listing/15757389409/b166149f-ac8e-453a-81c9-d9efa1e80166?click_key=2a171802-19d5-4aee-94be-5547fc8ef590&click_sum=130&ref=1')

2147483647 是int的最大值，

无法避免回表

无法排序

为什么添加and url = ‘xxx’

若是数据量大出现hash冲突的可能性增加，加上and条件做进一步的筛选，由于在经过索引的筛选后数据量将会减少很多，并不会导致效率的低下

1.2.3、full-index全文索引

  • 创建

alter table product_detail  add fulltext index description_fulltext_index (description)

索引

#用不同的数字索引不同的句子（比如以下三句在文本中是按照0，1，2的顺序排列的）
 
0 : "I love you"
1 : "I love you too "
2 : "I dislike youyou "
#如果要用单词作为索引，而句子的位置作为被索引的元素，那么索引就发生了倒置：
 
"I" : {0,1,2}
"love" : {0, 1}
"you" : {0,1,2}
"dislike" : {2}
#如果要检索 "I dislike you" 这句话，那么就可以这么计算 :  {0,1,2} 交集 {0,1,2}交集 {2}

  • 使用 语法有所不同

explain 
select * from product_detail where match `description` against('you')

其他查询操作，如相关性排序、通配符使用，相比于 like 将会返回更准确的数据，速度更快

1.2.4、复合索引

对于复合索引：MySQL从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分,本质为最左前缀原则，是B+树构建的。

  • 当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引。想要索引生效，只能使用a和a,b和a,b,c三种组合。当然a,c组合也可以，但实际上只用到了a的索引，c并没有用到。

  • 要根据业务需求，where子句中使用最频繁的一列放在最左边。可以筛选最多的列在前面

问题 以下语句的执行是否有使用到索引

KEY `province_city_create_time_index` (`province`,`city`,`create_time`)
explain select * from user where  province  = 20 and  city = 20 and create_time > '1990-01-01 00:00:00'  
explain select * from user where  province  = '20' and  city = 20 and create_time > '1990-01-01 00:00:00'  
explain select * from user where  province = '20' and  city = 20 and create_time = '1990-01-01 00:00:00'  
explain select * from user where  city = 20 and create_time > '1990-01-01 00:00:00' 
explain select province from user where  city = 20 and create_time > '1990-01-01 00:00:00' 
explain select * from user where  province ='20' and create_time > '1990-01-01 00:00:00'  
explain select * from user where  province >'20' and  city = 20 and create_time > '1990-01-01 00:00:00'  
explain select province,city,create_time from user where  province >'20' and  city = 20 and create_time > '1990-01-01 00:00:00' 
explain select * from user where  province ='20' and  city > 20 and create_time > '1990-01-01 00:00:00'  
explain select * from user where  province ='20' and  city = 20 
explain select * from user where  province ='20' or  city = 20

索引下推。适用于复合索引，如a and c 时，前面描述的最左前缀匹配无法使用到c的索引，因而会取出满足条件a的记录再去server层计算是否满足c条件；那索引下推就是将这个过程进行优化，索引下推时并不会将所有满足a条件的记录取出，而会利用已经存在的复合索引进行c值的判断，将满足条件的记录返回。这样做的好处是减少IO操作。执行explain 时extra字段中值为Using index condition

1.2.5、聚集索引与辅助索引

  1. 聚集索引

    1. 只有一个 数据存贮在叶子节点上（B+Tree） 主键索引（存放整个表的数据）

    2. 对于主键的排序查找和范围查找速度非常快

    3. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能

    4. 更新主键的代价很高，因为将会导致被更新的行移动

  2. 辅助索引

    1. 存放聚集索引的索引值

    2. 查找数据时，通过辅助索引找到聚集索引的索引值，再在聚集索引中查找（回表）

  3. 创建

alter table table_name add index index_name(field1,、、、)
alter table table_name add unique index_name(field)
alter table table_name add fulltext index_name(field)
create unique (nonclustered) index on table_name(field)(默认非聚集)

1.2.6、 覆盖索引

如果查询只扫描索引，无需回表则会更快

复合索引province_city_create_time_index

select province ,city,create_time from user where  province =1 and city = 12

alter table user add index province_city_create_time_index(province,city,create_time)

问题：以下查询会回表吗

select * from user where city = 2

explain
select uuid,province ,city from user where  province =1 and city = 12

1.3、索引创建原则

  • 选择唯一性索引

    • 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

  • 为经常需要排序、分组和联合操作的字段建立索引

    • 经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

  • 为常作为查询条件的字段建立索引

    • 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。为这样的字段建立索引，可以提高整个表的查询速度。

    • 如果是复合索引、可以考虑将筛选性高的列放在前面

  •  限制索引的数目

    • 索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。

    • 如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

    • 删除不再使用或者很少使用的索引

  • 尽量使用数据量少的索引

    • 如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

    • 数据量小的表最好不要使用索引

  • 尽量使用前缀来索引

    • 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

1.4、索引优化

  • 索引合并（复合索引） 查询能够同时使用这多个单列索引进行扫描，并将结果进行合并，筛选结果集（并集、交集）

explain select * from `user`  where  city >990 and create_time > '1990-01-01 00:00:00' 

alter table user add index city_create_time_index(city,create_time)

force index(city_index)

有时候并不见的是一件好事，可能是索引过多了、此时我们的索引可能需要进行优化调整了、设计符合的索引

  • 前缀索引

alter table user add index phone_index7(phone(7))

使索引变小节约空间，无法排序、分组，也不能覆盖扫描（需要再次读取值进行比较）

1.5、查看索引大小

SELECT
	DATA_LENGTH,
	INDEX_LENGTH,
	CONCAT( ROUND( SUM( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS data_len,
	CONCAT( ROUND( SUM( INDEX_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS index_len 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'employees' 
	AND table_name = 'user';



SHOW TABLE STATUS LIKE 'user' 
optimize table user

2、查询

2.1、查询过程

2.1.1索引的选择

选择索引是优化器的工作

  • 优化器选择索引是找到最优方案，在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，优化器则以为访问磁盘数据的次数越少，IO越少。

  • 优化器会结合是否使用临时表，是否排序等进行综合判断执行代价

  • 优化器在处理时可能会选择‘错’索引，可以指定索引 force index

以下查询语句会走什么索引，是最优的吗

select * from test_index where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

扫描行数的估计值不准确。

因为又order by b,优化器认为走索引b可以避免排序。

limit 1 让优化器认为只要找到一条满足记录的条件就可以提前终止，即使要扫描500001条记录，但是这是值得冒险的，所以走了b

2.2查询优化

补充 type的类型值

ststem > const > eq_ref > ref > range > index > all

唯一 ，非唯一，范围，索引中所有，全表

外连接优化

从表type中读入一行数据 R；

从数据行R中，取出a字段到表book里去查找；

取出表book中满足条件的行，跟R组成一行，作为结果集的一部分；

重复执行步骤1到3，直到表t1的末尾循环结束。

book表（被驱动表 有索引将会更高效）

如果被驱动表没有索引，是怎么执行的

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

#为book表的card字段建索引
CREATE INDEX Y ON book(card);
 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

#再为type表的card字段建索引
CREATE INDEX X ON `type`(card);
 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

#此时将book表中作用于card字段的索引删除
DROP INDEX Y ON book;
 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

内链接优化
#删除两张表中多余的索引，只剩主键索引
DROP INDEX Y ON book;
DROP INDEX X ON type;
 
SHOW INDEX FROM book;
SHOW INDEX FROM `type`;

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

CREATE INDEX Y ON book(card);
 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

CREATE INDEX X ON `type`(card);
 
#结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
#删除索引
DROP INDEX Y ON book;
#结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

#结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card where type.`card` > 18;

  • 保证被驱动表的JOIN字段已经创建了索引

  • 需要JOIN 的字段，数据类型保持绝对一致。

  • LEFT（RIGHT） JOIN 时，选择小表作为驱动表（筛选条件后数据少的表）， 大表作为被驱动表 。减少外层循环的次数。

  • INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。

  • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

  • 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

  • 临时表建不了索引

子查询优化

explain select * from employees e where e.`emp_no` in (select emp_no from `salaries` s where s.`salary` > 10000)

explain select * from `employees` e join salaries s on e.emp_no  = s.emp_no where s.`salary` > 10000

通过下面的优化（连接查询），type级别从range提升到了 ref。
order by 、group by优化 

注意 max_length_for_sort_data 和 sort_buffer_size的影响

explain
select SQL_NO_CACHE * from goods where  sales > 2000  and  stock > 200  order by  price;

create index sales_price_inidex on goods (sales,price)

drop index  sales_price_index on goods

explain
select price from goods where  sales < 4  and  stock > 10000  group by  price;

利用索引分页

select * from employees limit 10000,10;
select * from employees where id > 90000 limit 5;


explain
select * from user order by user_name limit 90000,5	
explain 
select * from user e inner join ( select uuid from user order by user_name limit 9000,5	) ed on e.uuid = ed.uuid;

避免对null的判断

explain select * from `order` where price is null
等价
explain select * from `order` where  isNull(price)

is null 可以使用索引

  • 应尽量避免在 where 子句中使用!=或<>操作符

  • 引擎放弃使用索引而进行全表扫描。

  • MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 

explain select * from `order` where  price !=100
select * from user where user_name like '姓名12%'
select * from user where user_name like '%12'

用union优化or语句

  • 当使用OR语句连接查询条件时，只要有一个条件没有使用索引，MySQL就不会使用索引，而是进行全表扫描，所以使用OR时，尽量所有条件都走索引。

select * from user where phone like '1592782%' or province like '1%'

select * from user where phone like '1592782%' 
union all select * from user where province like '1%'

explain select * from user where province = '23' or city = '23' and province = '24'


explain
select * from user where province  like '4%'

where子句中不要进行表达式操作、函数

select * from user where age/2 = 10
select * from user where age = 20
select * from user where substring(phone,7) = '1592782'
select * from user where phone like '1592782%'

避免使用带有前导通配符的表达式

  • 形如%2782

  • 1592782%最左前缀索引可用

  • 可以创建全文索引

  • 使用between and

  • 使用NOT IN 时索引失效，当IN范围太大时索引失效，范围连续时可以用BETWEEN … AND … 代替。

字段合理选择

  • 设计数据库表时，应当尽可能使用能够满足特性的最短的数据类型如tinyint,mediumint

2.2.1、使用索引排序

MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

  • 当explain 的type列为index时 MySQL的排序使用的是索引排序

explain
select province ,city ,create_time from user where  city = 20 and create_time > '1990-01-01 00:00:00' order by create_time

explain
select phone ,city ,create_time from user where  city = 20 and create_time > '1990-01-01 00:00:00' order by create_time

explain
select province  city ,create_time from user where  city = 20 and create_time > '1990-01-01 00:00:00'   order by  province asc,create_time desc

explain
select * from user where  province >'20' and  city = 20  order by  user_name

explain
select province,city from user where  province >'20' and  city = 20  order by  province

  • 当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集(实际上就是外排序)。对于filesort，MySQL有两种排序算法。

    • 两遍扫描算法(Two passes)

      • 实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存(通过参数sort_buffer_size设定)中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。

      • 该算法需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。但内存开销较小。

    • 一次扫描算法(single pass)

      • 该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。

      • 该算法减少了I/O的次数，效率较高，但是内存开销也较大如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。

      • 在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

3、 索引失效的情况

3.1、单索引

假设T表有id,a,b,c四列，其中a列有索引。

  1. 查询没有用索引的相关字段

  2. 查询所有，limit m 此时不会使用a列索引，此类建议使用自增id限制查询范围

  3. 查询条件过于宽泛，导致优化器认为走索引还不如全表扫描（索引区分度不够，可以删除索引）

3.2、复合索引

假设T表有id,a,b,c四列，其中a,b,c列有复合索引。

  1. 查询条件为b

  2. 查询条件为c

  3. 查询条件为b和c（不满足最左前缀规则）

3.3、连接查询

  1. t1，t2表连接字段的数据类型不一致

3.4、排序、分组、limit

  1. a，b字段有联合索引索引，排序a desc，b asc 索引失效

  2. a，b分别有索引，同时对a，b进行排序

  3. 对索引a排序，但没有限制limit

3.5、其他操作

  1. 查询字段进行数值或函数操作

  2. 给出的查询条件与表字段的设计类型不一致

  3. like 左侧使用了通配符

  4. 使用or（两个条件均有索引则不影响）

  5. not in ，not exists 关键字索引失效

4、索引与引擎的支持情况

特征

InnoDB

MyISAM

Memory

B树索引

是的

是的

是的

备份/时间点恢复（在服务器中实现，而不是在存储引擎中。）

是的

是的

是的

集群数据库支持

不

不

不

聚集索引

是的

不

不

压缩数据

是的

是（仅当使用压缩行格式时才支持压缩的 MyISAM 表。使用带有 MyISAM 的压缩行格式的表是只读的。）

不

数据缓存

是的

不

不适用

加密数据

是（通过加密函数在服务器中实现；在 MySQL 5.7 及更高版本中，支持静态数据加密。）

是（通过加密功能在服务器中实现。）

是（通过加密功能在服务器中实现。）

外键支持

是的

不

不

全文检索索引

是（MySQL 5.6 及更高版本提供对 FULLTEXT 索引的支持。）

是的

不

地理空间数据类型支持

是的

是的

不

地理空间索引支持

是（MySQL 5.7 及更高版本提供对地理空间索引的支持。）

是的

不

哈希索引

否（InnoDB 在内部使用哈希索引来实现其自适应哈希索引功能。）

不

是的

索引缓存

是的

是的

不适用

锁定粒度

行锁

表锁

表锁

MVCC（多版本并发控制）

是的

不

不

复制支持（在服务器中实现，而不是在存储引擎中。）

是的

是的

-

存储限制

64TB

256TB

内存

T-树索引

不

不

不

事物

是的

不

不

更新数据字典的统计信息

是的

是的

是的

KH.
关注
  • 30
    点赞
  • 踩
  • 18
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL 之 索引合并
黄老师的博客
09-23 1129
历史背景 MySQL 5.0版本之前，一个表一次只能选择并使用一个索引。 MySQL 5.1版本开始，引入了Index Merge Optimization技术，使得MySQL支持一个表一次查询同时使用多个索引。 官方文档：MySQL Index Merge Optimization Index Merge Optimization支持三种合并算法 The Index Merge Intersection Access Algorithm 对应SQL 中的 AND 场景 The Index Merg
MySQL索引下推
xp_xpxp的博客
12-13 2421
索引下推(Index Condition PushDown,简称ICP)是从MySQL5.6开始引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率;
mysql翻转索引_mysql索引
weixin_36420089的博客
02-02 702
一、 MySQL: 索引以B树格式保存Memory存储引擎可以选择Hash或BTree索引，Hash索引只能用于=或<=>的等式比较。1、普通索引：create index on Tablename(列的列表)alter table TableName add index (列的列表)create table TableName([...], index [IndexName] (列的...
MySQL索引理解
小杰312的博客
11-16 1261
MySQL索引理解 , 个人对于MySQL数据库的索引的一些粗略见解, 和大家分享一下
MySQL 索引与性能调优
CSDN
07-06 1万+
索引用于快速找出在某个列中有一特定值的行,如果不使用索引MySQL必须从第l条记录开始读完整个表,直到找出相关的行.表越大,查询数据所花费的时间越多,如果表中查询的列有一个索引,MySQL能快速到达某个位置去搜寻数据文件,而不必查看所有数据,可加快数据查询的查询速度提高效率,索引可在创建表时增加,也可动态调整已有表.创建唯一索引的主要原因是减少查询索引列操作的执行时间,尤其是对比较庞大的数据表.它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一.
MySQL索引详解
qq_34222160的博客
04-17 1403
热衷学习，热衷生活！😄 沉淀、分享、成长，让自己和他人都能有所收获！😄 一、什么是索引？索引有什么作用？ 索引是一种用于快速查询和检索数据的数据接口。常用的索引数据结构有：B树、B+树、Hash表。 索引的作用就相当于目录的作用。比如：我们在查字典的时候如果没有目录，我们就只能一页一页去查找字，速度很慢，如果有目录，我们只需要查找字所在的页数，然后直接翻到那一页就可以了。 索引的优点缺点 优点 索引可以让我们更快的检索出我们需要查找的数据，这也是创建索引的最主要的原因。 可以通过创建唯一索引，保证数据.
【MySQL】索引
保护小周ღ的博客
03-18 4061
哈喽，大家好~我是保护小周ღ，本期为大家带来的是 MySQL 数据库中表的索引，详细得讲解了索引得基本概念，使用场景，如何添加索引，删除索引，展示索引，以及索引背后的数据结构，确定不来看看~ 更多相关知识敬请期待：保护小周ღ *★,°*:.☆(￣▽￣)/$:*.°★*
MySQL索引下推：原理与实践
码到三十五
01-21 1万+
（Index Condition Pushdown, ICP）是MySQL优化查询的一种方式，其核心思想是将原本在服务层（上层）进行的部分过滤操作下推到存储引擎层（下层）执行，从而减少不必要的数据行检索，提高查询效率。我们先简单了解一下MySQL大概的架构:索引下推优化的核心思想是将WHERE子句中的部分条件直接下推到索引扫描的过程中。这样，在扫描索引时，就可以提前过滤掉不满足条件的索引项，从而减少后续需要访问的数据行数。
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 170万+
总结MySQL高频基本面试问题整理，用于快速查缺补漏
MySQL创建全文索引分享
09-09
MySQL中的全文索引是一种特殊的索引类型，特别适用于在大量文本数据中进行高效搜索。全文索引使用分词技术和特定的算法，分析文本中的关键词，评估它们的重要性，并为快速查找和排序提供支持。与传统的B-Tree索引...
Mysql之索引分享
12-14
综上所述，理解并合理利用MySQL索引，如选择合适的数据结构、创建有效的索引策略，以及利用优化特性如ICP，对于提升数据库性能至关重要。在设计数据库和编写SQL查询时，应充分考虑这些因素，以确保系统的高效运行。
mysql索引优化分享
05-29
关于mysql索引一些优化介绍与创建原则，还有对order by排序的算法的介绍等等
后台 — mysql索引
12-14
ps：本人也是自己刚了解mysql索引，有很多不足地方，大佬勿喷，在这里小弟给大佬请安了，这只是我自己的理解个人的看法，肯定有很多问题，欢迎大家一起探讨。我最后测试了一个六十万的数据测试，有索引和无索引的...
mysql之比较两个表的数据
hebtu666
07-08 513
要比较两个结构相同的表中的某条数据是否完全一样，您可以使用JOIN语句或者EXISTS关键字来对比这两个表中相应的行。
如何快速将Excel定义的表结构转换为MySQL的建表语句
这家伙很懒，什么都没有留下
07-09 1108
在数据管理和数据库设计中，经常需要从Excel文件中获取表结构并快速转换为MySQL数据库的建表语句。这个过程不仅可以节省大量手动输入的时间，还能减少因人为错误导致的数据库设计问题。本文将详细介绍如何高效地完成这一过程，包括使用不同的方法和工具，以及提供实际案例和代码示例，旨在帮助初学者和技术人员快速掌握这一技能。
MySQL 进阶（三）【SQL 优化】
最新发布
梦想是动物管理员
07-13 626
MySQL SQL 优化
mysql笔记1
m0_62975692的博客
07-11 277
首先查看systemctl status mysqld，如果是关闭的可以进入的配置文件打开mysql，我的mysql配置文件就是在/usr/local/mysql/support-filess/下，在此目录下执行./mysql.server start,打开mysql服务，也可以通过systmectl start mysqld，如果你嫌麻烦的话可以systemctl enable mysqld 一直打开mysql的服务;重新给字段添加约束的时候不可以添加not null约束的！
mysql快速精通（一）DQL数据查询语言
不起眼小菜菜的博客
07-10 1047
文章建立在已知基础用法的前提下进行深入了解……
MySQL优化分享.pptx
12-10
MySQL优化分享pptx介绍了MySQL优化的相关内容，包括MySQL架构分析、慢查询解决和索引分析等方面。在MySQL优化中，首先进行了MySQL架构分析，其中连接器起着重要作用。连接器主要负责用户登录数据库，进行用户的身份...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值