데이터 베이스 MySQL 튜토리얼 MySQL数据库性能优化六大技巧_MySQL

MySQL数据库性能优化六大技巧_MySQL

Jun 01, 2016 pm 01:44 PM
개발하다 기능 데이터베이스 테이블

bitsCN.com

 

数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间。Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕。如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况。因此,本文将介绍对MySQL进行性能优化的技巧和窍门。

1.存储引擎的选择

如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性。如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下:在一个事务处理中,一些数据表使用InnoDB,而其余的使用MyISAM。结果呢?整个subject将被取消,只有那些在事务处理中的被带回到原始状态,其余的被提交的数据转存,这将导致整个数据库的冲突。然而存在一个简单的方法可以同时利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表,但如果你选择MyISAM,你仍然可以单独下载InnoDB,并把它作为一个插件。很简单的方法,不是吗?

2.计数问题

如果数据表采用的存储引擎支持事务处理(如InnoDB),你就不应使用COUNT(*)计算数据表中的行数。这是因为在产品类数据库使用COUNT(*),最多返回一个近似值,因为在某个特定时间,总有一些事务处理正在运行。如果使用COUNT(*)显然会产生bug,出现这种错误结果。

3.反复测试查询

查询最棘手的问题并不是无论怎样小心总会出现错误,并导致bug出现。恰恰相反,问题是在大多数情况下bug出现时,应用程序或数据库已经上线。的确不存在针对该问题切实可行的解决方法,除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试,才能被认可。

4.避免全表扫描

通常情况下,如果MySQL(或者其他关系数据库模型)需要在数据表中搜索或扫描任意特定记录时,就会用到全表扫描。此外,通常最简单的方法是使用索引表,以解决全表扫描引起的低效能问题。然而,正如我们在随后的问题中看到的,这存在错误部分。

5.使用”EXPLAIN”进行查询

当需要调试时,EXPLAIN是一个很好的命令,下面将对EXPLAIN进行深入探讨。

首先,创建一个简单的数据表:

CREATE TABLE awesome_pcq(

emp_id INT(10) NOTNULL DEFAULT '0',

full_name VARCHAR(100) NOTNULL,

email_id VARCHAR(100) NOT NULL,

password VARCHAR(50) NOT NULL,

deleted TINYINT(4) NOTNULL,

PRIMARYKEY(emp_id)

) COLLATE=utf8_general_ci

ENGINE=InnoDB

ROW_FORMAT=DEFAULT

这个数据表一目了然,共有五列,最后一列“deleted”是一个Boolean类变量flag来检查帐号是活动的还是已被删除。接下来,您需要用样本记录填充这个表(比如,100个雇员记录)。正如你看到的,主键是“emp_id”。因此,使用电子邮件地址和密码字段,我们可以很容易地创建一个查询,以验证或拒绝登录请求,如下(实例一):

SELECT COUNT(*) FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0

之前我们提到,要避免使用COUNT(*)。代码纠正如下(实例二):

SELECT emp_id FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0

现在回想一下,在实例一中,代码查询定位并返回“email_id”和“password”等于给定值的行数。在实例二中,进行了同样的查询,不同的是明确要求列出“emp_id”所有满足给定的标准的值。哪个查询更费时?

很显然,这两个实例都是同样费时的数据库查询,因为无意间,两个实例查询都进行了全表扫描。为了更好地读懂指令,执行如下代码:

EXPLAIN SELECT emp_id FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0  在输出时,集中在倒数第二列:“rows”。假设我们已经将表填充了100个记录,它会在第一行显示100,这是MySQL需要进行扫描用来计算查询的结果的行数。这说明了什么?这需要全表扫描。为了克服这个弊端,则需要添加索引。

6.添加索引

先从重要的说起:给每一个可能遇到的次要问题创建索引并不明智。过多的索引会导致效能减慢和资源占用。在进一步讨论之前,在实例中创建一个样本索引:

ALTER TABLE awesome_pcq ADDINDEX LoginValidate(email_id)  接下来,再次运行该查询:

EXPLAIN SELECT emp_id FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0

请注意运行后的值。不是100,而是1。因此,为了给出查询结果,MySQL只扫描了1行,多亏先前创建的索引。你可能会注意到,索引只在电子邮件地址字段创建,而查询对其他字段同样进行了搜索。这表明MySQL先执行了一个cros-check,检查是否有在WHERE子句中的定义的值有索引指定,如果有这样的值就执行相应的操作。

但是,它不是每次重复将减少到一个。例如,如果不是唯一的索引字段(如employee names列可以有两行相同的值),即使创建索引,也将有多个记录留下。但它仍然比全表扫描好。并且,在WHERE子句中指定列的顺序没有在这个过程中发挥作用。例如,如果在上面的查询中,改变字段的顺序,使电子邮件地址出现在最后,MySQL仍将遍历索引列的基础上。那么,就要在索引上动脑筋,注意如何避免大量的全表扫描,并获得更好的结果。不过,这需要经历一个很长的过程。

 

作者kongdaoxian

bitsCN.com
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

뜨거운 기사 태그

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

Apple Vision Pro를 PC에 연결하는 방법 Apple Vision Pro를 PC에 연결하는 방법 Apr 08, 2024 pm 09:01 PM

Apple Vision Pro를 PC에 연결하는 방법

iPhone에서 Shazam 앱이 작동하지 않음: 수정 iPhone에서 Shazam 앱이 작동하지 않음: 수정 Jun 08, 2024 pm 12:36 PM

iPhone에서 Shazam 앱이 작동하지 않음: 수정

권장되는 AI 지원 프로그래밍 도구 4가지 권장되는 AI 지원 프로그래밍 도구 4가지 Apr 22, 2024 pm 05:34 PM

권장되는 AI 지원 프로그래밍 도구 4가지

Win11 팁 공유: Microsoft 계정으로 로그인을 건너뛰는 한 가지 요령 Win11 팁 공유: Microsoft 계정으로 로그인을 건너뛰는 한 가지 요령 Mar 27, 2024 pm 02:57 PM

Win11 팁 공유: Microsoft 계정으로 로그인을 건너뛰는 한 가지 요령

최고의 AI 프로그래머는 누구일까요? Devin, Tongyi Lingma 및 SWE 에이전트의 잠재력을 살펴보세요. 최고의 AI 프로그래머는 누구일까요? Devin, Tongyi Lingma 및 SWE 에이전트의 잠재력을 살펴보세요. Apr 07, 2024 am 09:10 AM

최고의 AI 프로그래머는 누구일까요? Devin, Tongyi Lingma 및 SWE 에이전트의 잠재력을 살펴보세요.

Go 언어를 사용하여 모바일 애플리케이션을 개발하는 방법을 알아보세요. Go 언어를 사용하여 모바일 애플리케이션을 개발하는 방법을 알아보세요. Mar 28, 2024 pm 10:00 PM

Go 언어를 사용하여 모바일 애플리케이션을 개발하는 방법을 알아보세요.

Ubuntu 24.04에서 Ubuntu Notes 앱을 설치하고 실행하는 방법 Ubuntu 24.04에서 Ubuntu Notes 앱을 설치하고 실행하는 방법 Mar 22, 2024 pm 04:40 PM

Ubuntu 24.04에서 Ubuntu Notes 앱을 설치하고 실행하는 방법

초보자를 위한 양식 작성 요령은 무엇입니까? 초보자를 위한 양식 작성 요령은 무엇입니까? Mar 21, 2024 am 09:11 AM

초보자를 위한 양식 작성 요령은 무엇입니까?

See all articles