高效利用mysql索引指南-6ren

高效利用mysql索引指南

转载作者：qq735679552 更新时间：2022-09-27 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章高效利用mysql索引指南由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

前言。

mysql 相信大部分人都用过，索引肯定也是用过的，但是你知道如何创建恰当的索引吗？在数据量小的时候，不合适的索引对性能并不会有太大的影响，但是当数据逐渐增大时，性能便会急剧的下降.

本篇是对 mysql 索引的一个归纳总结，如果有错误的地方，记得评论指出哦.

索引基础。

我们都有都知道查字典的步骤，是先在索引页中找到这个字的页码，然后再到对应的页码中查看这个字的信息。mysql 的索引方法也是和这个类似的，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如有下面的 sql 语句:

 
    ? 
   
         select 
         *  
         from 
         student  
         where 
         code= 
         '2333'

加入 code 列上建立有索引，mysql 将使用该索引找到值为'2333'的数据行，然后读取数据行的所有数据返回.

索引类型。

B-Tree 索引。

（不是 B 减树，就是 B 树），绝大多数的索引类型都是 B-Tree 的（或者是 B-Tree 的变体），通常我们使用的也是这类索引。Mysql 中 MyISAM 存储引擎使用的是 B-tree，InnoDB 使用的是 B+Tree，B 树和 B+树的区别自行百度.

树结构的索引能够加快访问数据的速度，存储引擎不再需要全表扫描来获取所需的数据，取而代之的是从树的根节点来进行二分搜索，总所周知二分搜索的速度是相当快的，因此我们能够利用索引来极大的提高查询速度。B-Tree 支持以下几种类型的查询:

假设再 student 表中仅有：name,age,weight 这样一个多列索引,下面的查询都能利用到此索引。

全值匹配

和索引列中的所有列进行匹配。比如查询name='abc' and age=12,这里用到了第一列和第二列。

匹配最左前列

只是用索引的开头部分，比如查询name='ggg'只使用索引的第一列，查询name='ggg' and age=12是用索引的第1、二列.

匹配列前缀

也可以只匹配某一列的开头部分，比如查询name lik 'g%',查询 name 以 g 开头的记录。这里用到了第一列。

匹配范围值

可用于匹配范围值，比如查询name > 'abc' and name < 'bcd' 。

精确匹配某一列并范围匹配另外一列

用于匹配多列，比如查询name='abc' and age > 12.

总的来看，可以发现 B-Tree 索引适用于根据最左前缀的查找，也就是查询字段字段顺序要和索引字段顺序一样，且以第一个索引字段开头。比如查询name,name and age,name and age and weight都能使用索引，但是查询age,age and name不能使用索引.

哈希索引。

hash 索引基于 hash 表实现，只有精确匹配索引所有列才会生效。MySQL 中只有 Memory 引擎显示支持哈希索引，同时也是其默认索引.

InnoDB 无法创建 hash 索引，但是它有一个功能叫自适应hash索引,当某些索引值使用非常频繁时，引擎会在内存中基于 B-Tree 索引之上再创建一个 hash 索引，这样就让 B-Tree 索引也有了一点 hash 索引的优点。这个功能是一个完全自动的、内部的行为，也就是无法手动控制或配置.

高性能索引策略。

下面是一些常见的索引策略.

独立的列。

这个很简单，如果查询中的列不是独立，便无法使用索引，比如:

 
    ? 
   
         select 
         *  
         from 
         student  
         where 
         age+1=12

即使 age 列有索引，上面的查询语句也是无法利用索引的.

前缀索引和索引选择性。

如果需要索引很长的字符串列，直接创建索引，会让索引占用更多的空间且速度较慢。一个优化策略是模拟 hash 索引：给列计算一个 hash 值，并在 hash 值列建立索引.

另外一个办法就是建立前缀索引。只索引这个字段开始的部分字符，这样可以极大的解决空间占用，索引建立速度也会快很多。但是这样也有如下弊端:

降低了索引选择性，如果多个字符串前缀相同便无法区分，还需要进行字符串对比。
不支持order by,group by,原因显而易见，只索引了部分字符，无法完全区分。

这里的关键是确定索引多少个字符合适。既要避免长度过大，还要有足够的索引选择性。有以下两种办法来帮助确定索引字符数:

索引字段前缀数据分布均匀。也就是以索引字符开头的字符串数目分布均匀，比如索引 name 字段的前 3 个字符，下面的结果是比较合理(只取排名前 8 的）:

。

数目	索引前三个字符
500	abc
465	asd
455	acd
431	zaf
430	aaa
420	vvv
411	asv
512	pdf

。

如果每一列的数据都比较大，说明区分度还不高需要增大索引字符数，直到这个前缀的选择性接近完整列的索引性，也就是前面的数据要尽可能的小.

计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面语句用户计算完整列选择性:

 
    ? 
   
         -- 不同字符串的数目/总的数目就是完整列选择性 
        
         select 
         count 
         ( 
         distinct 
         name 
         )/ 
         count 
         (*)  
         from 
         person;

下面语句计算索引前 3 个字段选择性:

 
    ? 
   
         -- 前3个字符不同的字符串数据/总的数据 
        
         select 
         count 
         (distincy  
         left 
         (city,3))/ 
         count 
         (*)  
         from 
         person

不断增大索引字符数目，直到选择性接近完整列选择性且继续增大数据选择性提升幅度不大的时候.

创建方法。

 
    ? 
   
         -- 假设最佳长度为4 
        
         alter 
         table 
         person  
         add 
         key 
         ( 
         name 
         (4));

多列索引。

不少人有这样的误解，如果一个查询用有多个字段 ‘and'查询，那么给每个字段都建立索引不就能最大化提高效率了？事实并不是如此，mysql 只会选择其中一个字段来进行索引查找。这种情况下应该建立多列索引（又叫联合索引），就能利用多个索引字段了，注意索引列顺序要和查询的顺序一致.

在 5.0 及以上版本中引入了“索引合并”的策略。一定程度上也可以使用多个单列索引，比如下面的查询:

 
    ? 
   
         -- mysql会分别使用name和age索引查出数据然后合并 
        
         -- 如果使and则查出数据后再对比取交集 
        
         select * from person where name = "bob" or age=12

但是不推荐这么做，and 或 or条件过多会耗费大量的 CPU 和内存在算法的缓存、排序和合并操作上.

选择合适的索引列顺序。

在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先是按照最左列进行排序，然后是第二列…索引一个良好的多列索引应该是将选择性最高的索引放在最前面，然后依次降低，这样才能更好的利于索引。选择性计算方发见：前缀索引小节.

聚族索引。

聚族索引不是一种单独的索引类型，而是一种数据存储方法，具体的细节依赖其实现方式.

InnoDB 的聚族索引实际是在同一个结构中保存索引值和数据行。因为不能同时将数据行放在两个不同的地方，所以一个表只能有一个聚族索引。InnoDB 的聚族索引列为“主键列”.

如果没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果这样的索引也没有，InnoDB 会隐式定义一个主键来作为聚族索引.

聚族索引的主要优点是：可以把相关数据保存在一起，减少磁盘 IO,提高查询效率。但是也有缺点:

插入顺序严重依赖于插入顺序。按照主键的顺序插入是速度最快的方式，否则可能会导致页分裂的问题出现，会占用更多的磁盘空间，扫描速度也会变慢。可通过OPTIMIZE TABLE重新组织表。
更新聚族索引列代价很高，因为索引值变了，行数据也会跟着索引移动到新的位置上。
二级索引（非聚族索引）访问行数据需要两次索引查找，因为二级索引叶子节点存储的并不是行数据的物理位置，而是行的主键值，再通过主键值到聚族索引中取行数据。

覆盖索引。

简单来说就是一个索引覆盖了需要查询的列字段，这样就不需要再到聚族索引中利用主键进行二次查找，在一个二级索引中就能取到所需的数据.

InnoDB 的索引会在叶子节点中保存索引值，因此如果要查询的字段全部包含在某个索引中，且这个索引被使用了，那么就能极大的提高查询速度。比如如下查询语句:

 
    ? 
   
         -- name有索引的情况下，直接从索引的叶子节点中取name值返回，无需二次查找 
        
         select 
         name 
         from 
         person  
         where 
         name 
         =  
         'abc' 
        
         -- 如果存在`name,age`聚合索引，也会直接返回数据，无需二次查找 
        
         select 
         name 
         ,age  
         from 
         person  
         where 
         name 
         = 
         'abc' 
         and 
         age=12

使用索引进行排序。

mysql 的排序操作也是可以利用索引的，只有当索引的列顺序和ORDER BY的顺序完全一致，并且所有列的排序方法（正序或者倒序）也一样时，才能够使用索引来进行排序。注意：排序的字段可以比对应的索引字段少，但是顺序必须一致。如下

 
    ? 
   
 
     
       
       
         -- 假设有：(name,age,sex)联合索引 
        
 
         -- 可使用索引排序 
        
 
         select 
         ...  
         order 
         by 
         name 
         desc 
         ,age  
         desc 
        
 
         select 
         ...  
         order 
         by 
         name 
         desc 
         ,age  
         desc 
         ,sex  
         desc 
        
 
         -- 不可使用排序 
        
 
         select 
         ...  
         order 
         by 
         name 
         desc 
         ,sex  
         desc 
        
 
         select 
         ...  
         order 
         by 
         name 
         desc 
         ,age  
         asc 
        
 
     
 
   

结束。

本篇基于 mysql 5.5 的版本，更新的版本可能会有不一样的策略.

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我.

原文链接：https://www.tapme.top/blog/detail/20190422/ 。

最后此篇关于高效利用mysql索引指南的文章就讲到这里了,如果你想了解更多关于高效利用mysql索引指南的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： mysql 5.7.18 zip版安装使用教程

文章推荐：详解laravel中blade模板带条件分页

文章推荐： Yii1.0 不同页面多个验证码的使用实现

文章推荐： mysql 5.7.17 zip安装配置教程 mysql启动失败的解决方法

mysql - 只有 Mysql OR mysql+sqlite OR mysql+自己的解决方案
目前我正在构建相当大的网络系统，我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres，因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
mysql - Linux/mysql 将 mysql 表输出写入文件并保持 mysql 格式。
我在 mysql 中使用如下命令。当它显示表格数据时，它被格式化为一个非常干净的表格，间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时，如下
mysql - 无法从终端加载 mysql 但可以使用系统首选项启动 mysql
我知道这个问题之前已经被问过好几次了，我已经解决了很多问题，但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
mysql - 在 mysql 数据查询上获取不需要的输出 mysql
以下是我的 SQL 数据结构，我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
mysql - 将本地 mysql 服务器复制到基于云的 mysql
我们有一个本地 mysql 服务器(不在公共(public)域上)，并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗？2.我们的本地服务器只能在本地网络上访问
mysql - MySQL 触发器上 MySQL 变量的算术运算
我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和
mysql - 无法在创建 MySQL 服务器的同一计划中使用 mysql
我想创建一个 AWS RDS 实例，然后使用 terraform 管理数据库用户。因此，首先，我创建了一个 RDS 实例，然后使用创建的 RDS 实例初始化 mysql 提供程序，以进一步将其用于用户
mysql - MySql 用户数量对 MySql 性能影响大吗？
当用户在我的网站上注册时，他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。创建它应该
mysql - mysql 表中的下和子类别(coldfusion，mysql)
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
mysql - 如何使用 MYSQL 运算符选择列所有值都必须可用 - MYSQL
我想获取 recipes_id 列的值，以获取包含 ingredient_id 的 2,17 和 26 条目的值。假设 ingredient_id 2 丢失则不获取记录。我已经尝试过 IN 运算符
mysql - MySQL 服务器和 MySQL 客户端有什么区别
在 Ubuntu 中，我通常安装两者，但 MySQL 的客户端和服务器之间有什么区别。作为奖励，当一个新语句提到它需要 MySQL 5.x 时，它是指客户端、服务器还是两者兼而有之。例如这个链接ht
mysql - mysql - 如何在没有触发器和手动插入的情况下在插入时生成/自动增加 guid mysql？
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。这还不够独特，所以我想我会有一个指导。我来自微软 sql 背景，在 ssms 中你可以选择类型为“uniqeidentifier”并自
mysql - Oracle MySQL 与 MySQL 相同吗？
我的系统上有 MySQL，我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
mysql - 本地 mysql 服务器和生产 mysql 服务器之间的显着性能差异
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开，留下的文档很少，而且联系不上了。我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
mysql - 如何自动将数据从一个 MySQL 数据库传输到另一个 MySQL 数据库？
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说，我希望每天早上 4:00 进行数据传输。可以吗？是否有任何 MySQL 内置功能可以让我们做到这一
mysql - 从 mysql 目录外的 mysql 表查询？
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表，还是必须将它们移动到 mysql 根目录内的数据库文件夹中？我在 Google 上搜索时没有找到任何东西。最
mysql - 使用另一个 mysql 表的值更新 Mysql 表
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段，都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份，其中成员中的代码与 ClassNumb
mysql - 是否可以将本地 MySQL 数据库复制到远程 MySQL 数据库？
情况:我有 2 台服务器，其中一台当前托管一个实时 WordPress 站点，我希望能够将该站点转移到另一台服务器，以防第一台服务器出现故障。传输源文件很容易；传输数据库是我需要弄清楚如何做的。两台服
mysql - 使用 mysql 查询复制 mysql 数据库
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数？类似于将 db A 复制到新的 db B。最佳答案首先创建复制数据库: CREATE DATABASE du
mysql - 当 mySQL 已安装并由另一个应用程序配置时，为新应用程序配置 mySQL
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试，并且运行良好。当我在计算机上安装我的软件时，我必须执行以下步骤: 安装 mySQL 服务器将用户名指定为“root

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

高效利用mysql索引指南