MySQL优化
本文主要讲述了如何加速动态网站的MySQL索引分析和优化。
一、什么是索引?
索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。
假设我们创建了一个名为people的表:
<ccid_code></ccid_code> CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL ); |
然后,我们完全随机把1000个不同name值插入到people表。在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引,MySQL将在索引中排序name列,对于索引中的每一项,MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此,如果我们要查找name等于“Mike”记录的peopleid(SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”),MySQL能够在name的索引中查找“Mike”值,然后直接转到数据文件中相应的行,准确地返回该行的peopleid(999)。在这个过程中,MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引,MySQL要扫描数据文件中的所有记录,即1000个记录!显然,需要MySQL处理的记录数量越少,则它完成任务的速度就越快。
二、索引的类型
MySQL提供多种索引类型供选择:
普通索引 :
这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
创建索引,例如CREATE INDEX ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引:
这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:
创建索引,例如CREATE UNIQUE INDEX ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主键 :
主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
全文索引:
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,要了解更多信息,请参见MySQL documentation。
三、单列索引与多列索引
索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表:
<ccid_code></ccid_code>CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) ); |
下面是我们插入到这个people表的数据:
这个数据片段中有四个名字为“Mikes”的人(其中两个姓Sullivans,两个姓McConnells),有两个年龄为17岁的人,还有一个名字与众不同的Joe Smith。
这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如,我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid(SQL命令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;)。由于我们不想让MySQL每次执行查询就去扫描整个表,这里需要考虑运用索引。
首先,我们可以考虑在单个列上创建索引,比如firstname、lastname或者age列。如果我们创建firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname);),MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录,然后再在这个“中间结果集”上进行其他条件的搜索:它首先排除那些lastname不等于“Sullivan”的记录,然后排除那些age不等于17的记录。当记录满足所有搜索条件之后,MySQL就返回最终的搜索结果。
由于建立了firstname列的索引,与执行表的完全扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引,再创建lastname或者age列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。
为了提高搜索效率,我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引,MySQL只需一次检索就能够找出正确的结果!下面是创建这个多列索引的SQL命令:
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录!
那么,如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、lastname、age的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。如果你有三个单列的索引,MySQL会试图选择一个限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。
四、最左前缀
多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:
<ccid_code></ccid_code>firstname,lastname,age firstname,lastname firstname |
从另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引:
<ccid_code></ccid_code> SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The following queries cannot use the index at all: SELECT peopleid FROM people WHERE lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17'; |
五、选择索引列
在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。请看下面这个查询:
<ccid_code></ccid_code>SELECT age ## 不使用索引 FROM people WHERE firstname='Mike' ## 考虑使用索引 AND lastname='Sullivan' ## 考虑使用索引 |
这个查询与前面的查询略有不同,但仍属于简单查询。由于age是在SELECT部分被引用,MySQL不会用它来限制列选择操作。因此,对于这个查询来说,创建age列的索引没有什么必要。下面是一个更复杂的例子:
<ccid_code></ccid_code> SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town ON people.townid=town.townid ##考虑使用索引 WHERE firstname='Mike' ##考虑使用索引 AND lastname='Sullivan' ##考虑使用索引 |
与前面的例子一样,由于firstname和lastname出现在WHERE子句中,因此这两个列仍旧有创建索引的必要。除此之外,由于town表的townid列出现在join子句中,因此我们需要考虑创建该列的索引。那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引:,>=,BETWEEN,IN,以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引,但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

MySQL과 Sqlite의 주요 차이점은 설계 개념 및 사용 시나리오입니다. 1. MySQL은 대규모 응용 프로그램 및 엔터프라이즈 수준의 솔루션에 적합하며 고성능 및 동시성을 지원합니다. 2. SQLITE는 모바일 애플리케이션 및 데스크탑 소프트웨어에 적합하며 가볍고 내부질이 쉽습니다.

MySQL의 인덱스는 데이터 검색 속도를 높이는 데 사용되는 데이터베이스 테이블에서 하나 이상의 열의 주문 구조입니다. 1) 인덱스는 스캔 한 데이터의 양을 줄임으로써 쿼리 속도를 향상시킵니다. 2) B-Tree Index는 균형 잡힌 트리 구조를 사용하여 범위 쿼리 및 정렬에 적합합니다. 3) CreateIndex 문을 사용하여 CreateIndexIdx_customer_idonorders (customer_id)와 같은 인덱스를 작성하십시오. 4) Composite Indexes는 CreateIndexIdx_customer_orderOders (Customer_id, Order_Date)와 같은 다중 열 쿼리를 최적화 할 수 있습니다. 5) 설명을 사용하여 쿼리 계획을 분석하고 피하십시오

MySQL에서 트랜잭션을 사용하면 데이터 일관성이 보장됩니다. 1) STARTTRANSACTION을 통해 트랜잭션을 시작한 다음 SQL 작업을 실행하고 커밋 또는 롤백으로 제출하십시오. 2) SavePoint를 사용하여 부분 롤백을 허용하는 저장 지점을 설정하십시오. 3) 성능 최적화 제안에는 트랜잭션 시간 단축, 대규모 쿼리 방지 및 격리 수준을 합리적으로 사용하는 것이 포함됩니다.

MySQL 대신 PostgreSQL을 선택한 시나리오에는 다음이 포함됩니다. 1) 복잡한 쿼리 및 고급 SQL 기능, 2) 엄격한 데이터 무결성 및 산 준수, 3) 고급 공간 기능이 필요하며 4) 큰 데이터 세트를 처리 할 때 고성능이 필요합니다. PostgreSQL은 이러한 측면에서 잘 수행되며 복잡한 데이터 처리 및 높은 데이터 무결성이 필요한 프로젝트에 적합합니다.

MySQL 데이터베이스의 보안은 다음 조치를 통해 달성 할 수 있습니다. 1. 사용자 권한 관리 : CreateUser 및 Grant 명령을 통한 액세스 권한을 엄격히 제어합니다. 2. 암호화 된 전송 : 데이터 전송 보안을 보장하기 위해 SSL/TLS를 구성합니다. 3. 데이터베이스 백업 및 복구 : MySQLDump 또는 MySQLPump를 사용하여 정기적으로 백업 데이터를 사용하십시오. 4. 고급 보안 정책 : 방화벽을 사용하여 액세스를 제한하고 감사 로깅 작업을 가능하게합니다. 5. 성능 최적화 및 모범 사례 : 인덱싱 및 쿼리 최적화 및 정기 유지 보수를 통한 안전 및 성능을 모두 고려하십시오.

MySQL 성능을 효과적으로 모니터링하는 방법은 무엇입니까? Mysqladmin, Showglobalstatus, Perconamonitoring and Management (PMM) 및 MySQL Enterprisemonitor와 같은 도구를 사용하십시오. 1. MySQLADMIN을 사용하여 연결 수를보십시오. 2. showglobalstatus를 사용하여 쿼리 번호를보십시오. 3.pmm은 자세한 성능 데이터 및 그래픽 인터페이스를 제공합니다. 4. MySQLENTERPRISOMITOR는 풍부한 모니터링 기능 및 경보 메커니즘을 제공합니다.

MySQL과 SqlServer의 차이점은 1) MySQL은 오픈 소스이며 웹 및 임베디드 시스템에 적합합니다. 2) SQLServer는 Microsoft의 상용 제품이며 엔터프라이즈 수준 애플리케이션에 적합합니다. 스토리지 엔진의 두 가지, 성능 최적화 및 응용 시나리오에는 상당한 차이가 있습니다. 선택할 때는 프로젝트 규모와 향후 확장 성을 고려해야합니다.

고 가용성, 고급 보안 및 우수한 통합이 필요한 엔터프라이즈 수준의 응용 프로그램 시나리오에서는 MySQL 대신 SQLServer를 선택해야합니다. 1) SQLServer는 고 가용성 및 고급 보안과 같은 엔터프라이즈 수준의 기능을 제공합니다. 2) VisualStudio 및 Powerbi와 같은 Microsoft Ecosystems와 밀접하게 통합되어 있습니다. 3) SQLSERVER는 성능 최적화에서 우수한 성능을 발휘하며 메모리 최적화 된 테이블 및 열 스토리지 인덱스를 지원합니다.


핫 AI 도구

Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool
무료로 이미지를 벗다

Clothoff.io
AI 옷 제거제

Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!

인기 기사

뜨거운 도구

VSCode Windows 64비트 다운로드
Microsoft에서 출시한 강력한 무료 IDE 편집기

ZendStudio 13.5.1 맥
강력한 PHP 통합 개발 환경

맨티스BT
Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.

메모장++7.3.1
사용하기 쉬운 무료 코드 편집기

mPDF
mPDF는 UTF-8로 인코딩된 HTML에서 PDF 파일을 생성할 수 있는 PHP 라이브러리입니다. 원저자인 Ian Back은 자신의 웹 사이트에서 "즉시" PDF 파일을 출력하고 다양한 언어를 처리하기 위해 mPDF를 작성했습니다. HTML2FPDF와 같은 원본 스크립트보다 유니코드 글꼴을 사용할 때 속도가 느리고 더 큰 파일을 생성하지만 CSS 스타일 등을 지원하고 많은 개선 사항이 있습니다. RTL(아랍어, 히브리어), CJK(중국어, 일본어, 한국어)를 포함한 거의 모든 언어를 지원합니다. 중첩된 블록 수준 요소(예: P, DIV)를 지원합니다.
