如何优化MySQL数据库性能：建立索引指南(mysql数据库建立索引)

MySQL数据库是现代应用程序中使用最广泛的关系型数据库管理系统之一。其高效性和可扩展性使其成为开发者们的首选。然而，随着数据库中数据量的不断增加，许多开发者会发现性能会变得越来越慢。这时，建立索引就成为了优化MySQL数据库性能的关键。本篇文章将指导你如何正确地建立索引，以充分利用MySQL的性能。

成都创新互联公司服务项目包括长汀网站建设、长汀网站制作、长汀网页制作以及长汀网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，长汀网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到长汀省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

1. 索引的概念

让我们来理解一下索引的概念。索引可以看作是一本书的目录，能够帮助我们更快地找到特定的信息。在数据库中，索引指向数据表中特定列的位置，允许快速查找和排序数据。无需遍历整个数据表，只需在索引中搜索就可以找到需要的数据。

2. 建立索引的好处

建立索引的好处是显而易见的。它可以提高查询速度。如果你有大量的数据，没有索引的查询就会变得非常耗时。相反，有了索引，则可以仅在索引上进行查询，从而减少了不必要的搜索，大大加快了查询速度。

建立索引可以加速数据的排序。如果没有索引，则需要遍历整个数据表并逐个比较记录的值。而有了索引，可以直接在索引上进行排序。

此外，索引还可以提高数据库的整体性能。通过加快查询速度和排序速度，可以为应用程序节省大量的时间和资源。

3. 如何建立索引

现在，我们来具体了解如何建立索引。虽然建立索引很容易，但是错误的设计和实现会产生不利影响。因此，需要注意以下几点：

（1）选择正确的列建立索引

根据你的数据表中最常查询的列建立索引是更好的选择。这些列通常是那些在WHERE语句中出现的列。如果你有多个常用查询的列，则需要根据你的查询模式来判断利用哪些列建立索引。

（2）避免过度索引

虽然索引可以提高查询速度，但是过于频繁的索引也会降低数据库的性能。每个索引都需要额外的空间和时间来维护，特别是在数据表的写入过程中。因此，要注意合理选择需要建立索引的列。

（3）使用复合索引

如果你需要同时查询多个列，那么可以使用复合索引。复合索引指的是同时在多个列上建立索引，从而优化多列查询的速度。与单列索引相比，复合索引大大减少了数据库的负担。

（4）避免在大文本/二进制列上建立索引

在大文本、二进制列上建立索引需要大量的空间和时间，且无法提高查询速度。因此，尽可能避免在这些列上建立索引。

（5）使用前缀索引

如果你需要在长文本列上建立索引，那么可以使用前缀索引。前缀索引只索引字符串的前面部分，从而节省空间和时间。

（6）避免重复的数据

重复的数据在索引中也需要占用额外的空间，因此尽可能避免使用重复数据。如果需要使用，请考虑建立唯一索引。

4. 如何优化索引

通过上述建立索引的方法可以大大提高MySQL数据库的性能。然而，在实践中，只是简单地建立索引是不够的，你还需要优化它们以便充分利用索引在MySQL中的功效。以下是一些优化建立索引的方法：

（1）使用EXPLN命令来检查查询

在执行查询之前，可以使用EXPLN命令来检查MySQL如何处理查询。它可以帮助你了解查询如何使用索引、使用哪个索引以及是否进行全表扫描。这样可以发现索引的不足之处，并尝试添加新的索引。

（2）使用MySQL的Profiler来监控查询性能

MySQL的Profiler是一个有用的工具，可以帮助你监控查询性能并分析查询速度。它可以告诉你哪些查询使用了索引，哪些查询需要改进。通过定期使用Profiler来分析数据库的性能，可以帮助你诊断问题并进行调整。

（3）定期优化索引

需要注意的是，索引并非一成不变。随着时间的推移，数据表中的数据会发生变化，因此需要定期优化索引。定期执行OPTIMIZE TABLE命令可以清理垃圾数据并重新构建索引，从而使索引更加高效。

建立索引是优化MySQL数据库性能的关键。正确地建立索引可以极大地加快查询速度、排序速度和整体性能。通过正确地选择要索引的列、避免过度索引、使用复合索引等优化技术，可以更大限度地发挥索引的作用，从而提高MySQL的性能。

相关问题拓展阅读：

• 如何创建mysql索引以及索引的优缺点

如何创建mysql索引以及索引的优缺点

在满足语句需求的情况下，尽量少的访问资源是数据库设计的重要原则，这和执行的 SQL 有直接的关系，索引问题又是 SQL 问题中出现频率更高的，常见的索引问题包括：无索引（失效）、隐式转换。

1. SQL 执行流程看一个问题，在下面这个表 T 中，如果我要执行 select * from T where k between 3 and 5; 需要执行几次树的搜索操作，会扫描多少行？mysql> create table T (    -> ID int primary key,    -> k int NOT NULL DEFAULT 0,    -> s varchar(16) NOT NULL DEFAULT ”,    -> index k(k))    -> engine=InnoDB;mysql> insert into T values(100,1, ‘aa’),(200,2,’bb’),\      (300,3,’cc’),(500,5,’ee’),(600,6,’ff’羡缓),(700,7,’gg’);

这分别是 ID 字段索引树、k 字段索引树。 

这条 SQL 语句的执行流程：

1. 在 k 索引树上找到 k=3，获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录，对应 R33. 在 k 索引树找到下一个值 k=5，ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R4

5. 在 k 索引树去下一个值 k=6，不符合条件，循环结束

这个过程读取了 k 索引树的三条记录，回表了两次。因为查询结果所需要的数据只在主键索引上有，所以必须得回表。所以，我们该如何通过优化索引，来避免回表呢？

2. 常见索引优化2.1 覆盖索引覆盖索引，换言之就是索引要覆盖我们的查询请求，无需回表。

如果执行的语句是 select ID from T wherek between 3 and 5;，这样的话因为 ID 的值在 k 索引树上，就不需要回表了。

覆盖索引可以减少树的搜索次数，显著提升查询性能，是常用的性能优化手段。宴派绝

但是，维护索引是有代价的，所以在建立冗余索引来支持覆盖索引时要权衡利弊。

2.2 最左前缀原则

B+ 树的数据项是复合的数据结构，比如 (name,sex，age) 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，当 (张三,F,26) 这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的检索晌姿方向，如果 name 相同再依次比较 sex 和 age，最后得到检索的数据。

# 有这样一个表 P

mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;

mysql> insert into P values(1,’张三’,’F’,26),(2,’张三’,’M’,27),(3,’李四’,’F’,28),(4,’乌兹’,’F’,22),(5,’张三’,’M’,21),(6,’王五’,’M’,28);

# 下面的语句结果相同

mysql> select * from P where name=’张三’ and sex=’F’;     ## A1

mysql> select * from P where sex=’F’ and age=26;## A2

# explain 看一下

mysql> explain select * from P where name=’张三’ and sex=’F’;

+—-++++——+-+——+++——+++

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref| rows | filtered | Extra|

+—-++++——+-+——+++——+++

|  1 | SIMPLE      | P     | NULL| ref  | tl| tl   || const,const |    1 |   100.00 | Using index |

+—-++++——+-+——+++——+++

mysql> explain select * from P where sex=’F’ and age=26;

+—-+++++-+——++——+——+++

| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |

+—-+++++-+——++——+——+++

|  1 | SIMPLE      | P     | NULL| index | NULL| tl   || NULL |    6 |    16.67 | Using where; Using index |

+—-+++++-+——++——+——+++

可以清楚的看到，A1 使用 tl 索引，A2 进行了全表扫描，虽然 A2 的两个条件都在 tl 索引中出现，但是没有使用到 name 列，不符合最左前缀原则，无法使用索引。所以在建立联合索引的时候，如何安排索引内的字段排序是关键。评估标准是索引的复用能力，因为支持最左前缀，所以当建立（a，b）这个联合索引之后，就不需要给 a 单独建立索引。原则上，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。上面这个例子中，如果查询条件里只有 b，就是没法利用（a，b）这个联合索引的，这时候就不得不维护另一个索引，也就是说要同时维护（a，b）、（b）两个索引。这样的话，就需要考虑空间占用了，比如，name 和 age 的联合索引，name 字段比 age 字段占用空间大，所以创建（name，age）联合索引和（age）索引占用空间是要小于（age，name）、(name）索引的。

2.3 索引下推

以人员表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字之一个字是张，而且年龄是26岁的所有男性”。那么，SQL 语句是这么写的mysql> select * from tuser where name like ‘张%’ and age=26 and sex=M;

通过最左前缀索引规则，会找到 ID1，然后需要判断其他条件是否满足在 MySQL 5.6 之前，只能从 ID1 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。这样，减少了回表次数和之后再次过滤的工作量，明显提高检索速度。

2.4 隐式类型转化

隐式类型转化主要原因是，表结构中指定的数据类型与传入的数据类型不同，导致索引无法使用。所以有两种方案：

修改表结构，修改字段数据类型。

修改应用，将应用中传入的字符类型改为与表结构相同类型。

3. 为什么会选错索引3.1 优化器选择索引是优化器的工作，其目的是找到一个更优的执行方案，用最小的代价去执行语句。在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

3.2 扫描行数

MySQL 在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，只能通过索引的区分度来判断。显然，一个索引上不同的值越多，索引的区分度就越好，而一个索引上不同值的个数我们称为“基数”，也就是说，这个基数越大，索引的区分度越好。# 通过 show index 方法，查看索引的基数mysql> show index from t;++++++++++——+++-+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |++++++++++——+++-+| t     || PRIMARY  || id| A||     NULL | NULL   |      | REE      || || t     || a|| a| A||     NULL | NULL   | YES  | REE      || || t     || b|| b| A||     NULL | NULL   | YES  | REE      || |++++++++++——+++-+

MySQL 使用采样统计方法来估算基数：采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

on 表示统计信息会持久化存储。默认 N = 20，M = 10。

off 表示统计信息只存储在内存中。默认 N = 8，M = 16。

由于是采样统计，所以不管 N 是 20 还是 8，这个基数都很容易不准确。所以，冤有头债有主，MySQL 选错索引，还得归咎到没能准确地判断出扫描行数。

可以用 yze table 来重新统计索引信息，进行修正。

ANAZE TABLE tbl_name …

关于mysql数据库建立索引的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。

香港服务器选创新互联，2H2G首月10元开通。
创新互联（www.cdcxhl.com）互联网服务提供商,拥有超过10年的服务器租用、服务器托管、云服务器、虚拟主机、网站系统开发经验。专业提供云主机、虚拟主机、域名注册、VPS主机、云服务器、香港云服务器、免备案服务器等。

分享名称：如何优化MySQL数据库性能：建立索引指南(mysql数据库建立索引)
文章起源：http://www.gawzjz.com/qtweb/news49/197799.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联