mysql 索引性能,MySQL性能优化

爱被打了一巴掌 2023-01-14 04:52 231阅读 0赞

原标题：MySQL性能优化

MySQL性能优化就是通过合理安排资源，调整系统参数使MySQL运行更快、更节省资源。MySQL性能优化包括查询速度优化、数据库结构优化、MySQL服务器优化等。

优化简介

优化数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调整策略，提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

在MySQL中可以使用SHOW STATUS语句查询一些MySQL数据库的性能参数。

语法：

show status like 'value';

其中，value是要查询的参数值，常用的性能参数如下：

Connections：连接MySQL服务器的次数。

Uptime：MySQL服务器的上线时间。

Slow\_queries：慢查询的次数。

Com\_select：查询操作的次数。

Com\_insert：插入操作的次数。

Com\_update：更新操作的次数。

Com\_delete：删除操作的次数。

示例：查询MySQL服务器的连接次数

![401baab5ec45c666f7128712fdf7de62.png][]

优化查询

查询是数据库中最频繁的操作，提高查询速度可以有效地提高MySQL数据库的性能。

分析查询语句

通过对查询语句的分析，可以了解查询语句的执行情况，找出查询语句执行的瓶颈，从而优化查询。

MySQL中提供了EXPLAIN语句和DESCRIBE语句来分析查询语句。

语法：

EXPLAIN/DESCRIBE \[EXTENDED\] SELECT select\_options

示例：

![62e4d779089014d6709104d16027f1fe.png][]

id：SELECT 识别符，即查询序号。

select\_type：SELECT语句的类型，SIMPLE表示简单查询，不包括连接查询和子查询；PRIMARY表示主查询或最外层的查询语句；UNION表示连接查询的第二个或后面的查询语句；UNION RESULT表示连接查询的结果；SUBQUERY表示子查询中的第一个SELECT语句；DERIVED表示FROM子句的子查询。

table：查询的表。

type：表的连接类型。

索引对查询速度的影响

MySQL中提高性能的一个有效方式就是对数据表设计合理的索引。索引提供了高效数访问数据的方法。并且可以加快查询的速度，因此，索引对查询的速度有着至关重要的影响。

索引简介

索引是对数据库表中一个或多个字段的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

索引的意义

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个字段中有特定值的行。

如果不使用索引，MySQL必须从第一条记录开始检索表中的每一条记录，直到找出相关的行。那么表越大，查询数据所花费的时间就越多。

如果在表中查询的字段有索引，MySQL能够快速到达一个位置去检索数据文件，而不需要再去查看所有数据，那么将会节省很大一部分查询时间。

比如说emp表中1W个员工的记录，要查询工号为7566的员工信息select \* from emp where empno=7566，如果没有索引，服务器会从表中第一条记录开始，一条条往下遍历，直到找到empno=7566的员工信息。

如果在empno这个字段上创建索引，就可以索引文件里面找empno=7566这一行的位置，而不需要再遍历1W条记录了。

索引的优缺点

所有MySQL的字段类型都可以添加索引，但是索引也不是越多越好，而是要根据业务数据合理的使用。

优点

通过索引对数据进行检索，大大提高了数据的查询效率。

通过创建唯一索引，可以保证数据库表中每一条记录的唯一性。

在实现数据的参照完整性方面，可以加速表和表之间的连接。

在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

索引也需要占磁盘空间的，创建的索引太多，索引文件也会占用一定的存储空间。

当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，这样就降低了数据的维护速度。

创建索引的原则

索引设计不合理或缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。

需要创建索引的情况

主外键和唯一约束的字段自动创建索引

频繁作为查询条件的字段应该创建索引

查询中排序的字段应该创建索引

查询中分组或统计的字段应该创建索引

不需要创建索引的情况

表中记录太少不需要创建索引

需要频繁增删改的字段不适合创建索引

where子句中用不到的字段不需要创建索引

重复值较多的字段不需要创建索引

索引的结构

索引是在存储引擎中实现的，使用不同的存储引擎，所支持的索引也是不同的。

在mysql中常用两种索引结构BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。

MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引。

![476da735d47ff57d04a0815ca1a52912.png][]

MySQL的InnoDB存储引擎是支持hash索引的，不过我们必须启用，hash索引的创建由InnoDB存储引擎自动优化创建，我们干预不了。

索引的类型

索引的类型可以分类以下几种：

普通索引：最基本的索引，没有任何限制

唯一索引：索引字段的取值不能重复，可以有空值，但空值也只能出现一次。

主键索引：索引字段的取值不能为空，也不能重复。

组合索引：一个索引包含多个字段，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

全文索引：通过关键字符，就能找到该字段所属的记录行。仅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT类型的字段上使用。

空间索引：对空间数据类型(GEOMETRY、POINT、LINESTRING、POLYGON)的字段建立的索引，仅限MyISAM引擎，且要求索引字段的取值不能为空。

索引的操作

实际上索引也是一张表，创建索引时，数据库管理系统会在本地磁盘建立索引文件，里面保存了索引字段，并指向实体表的记录。

创建索引

创建表的同时须指定索引名、表名和字段名。 语法：

create index on ();

自动创建索引

在表中定义了主键约束时，会自动创建一个对应的主键索引。

在表中定义了外键约束时，会自动创建一个对应的普通索引。

在表中定义了唯一约束时，会自动创建一个对应的唯一索引。

示例：emp表中的job添加普通索引

mysql> create index job\_index on emp(job);

查看索引

语法：

show index from ;

示例：查看emp表中的索引

![f8a6be3f4d4bfd4dce10360f65e5ec59.png][]

使用索引

在查询语句中使用索引会大大提升数据的检索速度。 示例：

![9e4a84924693aed1c51e7b7d961b9263.png][]

删除索引

删除索引只是删除了表中的索引对象，表中的数据不会被删除。 语法：

drop index on ;

示例：

责任编辑：

[401baab5ec45c666f7128712fdf7de62.png]: /images/20221022/648064c469714b8fa2545ba4c20468d8.png
[62e4d779089014d6709104d16027f1fe.png]: /images/20221022/e59e4c25879f46be87ba8756a8b94793.png
[476da735d47ff57d04a0815ca1a52912.png]: /images/20221022/5164a3c84c65468489158e745f6da581.png
[f8a6be3f4d4bfd4dce10360f65e5ec59.png]: /images/20221022/d228ed93c52648a1b006f998b0be4dc5.png
[9e4a84924693aed1c51e7b7d961b9263.png]: /images/20221022/f6f0aafa5c4942048470c8e049d056e5.png