在MySQL的InnoDB存储引擎中count(*)函数的优化
在MySQL中,日常开发中比较常用的有MyISAM和InnoDB两种存储引擎。两者之间的其中一个区别是使用count(*)函数计算表的具体行数。
写这篇文章之前已经看过了很多数据库方面的优化内容,大部分都是加索引、使用事务、要什么select什么等等。然而,只是停留在阅读的层面上,很少有实践,因为没有遇到真实的项目,一切都是纸上谈兵。实践是检验真理的唯一标准,于是就想在数据库上测试一些性能优化的方案,比如索引之类的,但是不想使用假的数据,于是就想着能不能抓取网上的一些数据来作分析,后来自己通过PHP抓取了一些数据(这个博文即将补上),抓了大约110W的用户数据之后,当然需要统计一下具体的数量,于是我使用了以下的SQL语句(我使用的存储引擎是InnoDB):
SELECT COUNT(*) FROM tbl_name;
然而,发现需要运行14-20s的时间才能看到结果。
这样的时间开销在真实的环境的用户体验是十分差的,试想一下,打开一个页面还要等接近20s才能看到数据,别说20s,就算是3s也是十分差的,于是便想在这方面做优化。
存储引擎在MySQL中,日常开发中比较常用的有MyISAM和InnoDB两种存储引擎。两者之间的其中一个区别是使用count(*)函数计算表的具体行数。
因为MyISAM会保存表的具体行数,因此这段代码在MyISAM存储引擎中执行,MyISAM只要简单地读出保存好的行数即可。因此,如果表中没有使用事务之类的操作,这是最好的优化方案。然而,InnoDB存储引擎不会保存表的具体行数,因此,在InnoDB存储引擎中执行这段代码,InnoDB要扫描一遍整个表来计算有多少行。
查询优化命令--Explain要弄懂查询性能在哪,首先,需要知道导致查询缓慢的瓶颈在哪。explain命令显示的rows是核心的性能指标,rows大,说明mysql需要扫描的行数就多,绝大部分rows大的语句执行一定很快。所以优化语句基本上都是在优化rows。
首先,看看上面的语句:
可以看到,mysql扫描了整个表来执行本次查询。
奇怪的地方在数据表的设计中,我是添加了唯一索引的,但是后来有一个语句是根据其中一个字段统计数量,当时添加了一个普通的索引,当我再执行了一遍上面的SQL语句,发现只需要0.2-0.3s的时间就能统计出表中的行数。
不禁吓了一跳,误打误撞就发现了优化的方法:在InnoDB中,除了唯一索引之外,在其他字段添加一个普通索引(称为辅助索引)就能够提升count(*)函数的性能。但是这是为什么呢?explain一下:
同样是扫描一样的行数,为什么添加一个普通索引就可以提高这么多的性能?于是便开始查找资料和阅读文档弄懂这个问题。
count(*)函数执行原理正如在不同的存储引擎中,count(*)函数的执行是不同的。在MyISAM存储引擎中,count(*)函数是直接读取数据表保存的行记录数并返回,而在InnoDB存储引擎中,count(*)函数是先从内存中读取表中的数据到内存缓冲区,然后扫描全表获得行记录数的。在使用count函数中加上where条件时,在两个存储引擎中的效果是一样的,都会扫描全表计算某字段有值项的次数。
索引原理因为是添加了索引之后才得到性能上的提升,于是便想到从索引的角度来探索。
根据官方文档上的定义:索引是帮助MySQL高效获取数据的数据结构。可以得知,索引的本质就是数据结构,添加索引的目的就是为了提高查询的效率。
使用索引的查询可以类比到字典,如果要查”mysql“这个单词,我们首先会定位到m字母,然后在m字母下面的单词中找y字母,以此类推,直到找到mysql这个单词,就能看到它在第几页,然后就去该页获取该单词更多的信息。想象一下,如果没有索引,那你就要在字典里一页一页的翻阅,效率十分低下。使用索引就是通过这样不断地缩小查询的范围来筛选出最终的结果。
那么在数据库也是一样的,但显然在数据库里使用索引要复杂许多。
磁盘存取与预读一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。那么数据库在构建索引的时候就需要先从磁盘读取数据了,此时就要产生磁盘I/O消耗。而每次的数据读取,都要经历寻道时间、旋转延迟、传输时间三个部分。寻道时间是指磁臂移动到指定磁道所需要的时间,一般在5ms以内;旋转延迟就是磁盘转速;传输时间指的是将数据从磁盘读出并写入到内存的时间,这个时间较短,可以忽略不计。相对于内存存取,I/O存取的消耗要高几个数量级。因此,评价一个数据结构作为索引的优劣最重要的指标就是查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
从上面的描述可以得知磁盘I/O是非常高昂的操作,根据操作系统的局部性原理:
当一个数据被用到时,其附近的数据也通常会马上被使用。
计算机操作系统在这方面做了一些优化,当一次I/O时,不光把当前磁盘地址的数据读取到内存缓冲区内,而且把相邻的数据也都读取到内存缓冲区内。这样一来,在读取数据时产生的I/O就少了很多了。因为在数据库中,每一次I/O读取的数据我们称之为一页(page),,一般为4k或8k,也就是说,我们读取一页内的数据时,实际上才发生了一次I/O。
根据以上的描述,我们可以初步得出结论,增加索引前后的性能差距体现在磁盘读取过程。但是在添加新的索引之前,我是添加了一个唯一索引的,后来发现在mysql中,我添加的唯一索引被称为聚簇索引,而后面添加的索引称为辅助索引,因此,让我们再来看看聚簇索引和辅助索引的区别。
聚簇索引(clustered index)和辅助索引(secondary index) 聚簇索引(clustered index)每一个InnoDB存储引擎下的表都有一个特殊的索引用来保存每一行的数据,称为聚簇索引。通常情况下,聚簇索引是主键的同义词。在InnoDB中,mysql是这样选择聚簇索引的:
如果表中定义了PRIMARY KEY,那么InnoDB就会使用它作为聚簇索引;
否则,如果没有定义PRIMARY KEY,InnoDB会选择第一个有NOT NULL约束的唯一索引作为PRIMARY KEY,然后InnoDB会使用它作为聚簇索引;

핫 AI 도구

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

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

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

Clothoff.io
AI 옷 제거제

AI Hentai Generator
AI Hentai를 무료로 생성하십시오.

인기 기사

뜨거운 도구

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

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

스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경

드림위버 CS6
시각적 웹 개발 도구

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

뜨거운 주제











InnoDB는 디스크의 테이블에 데이터를 저장하는 스토리지 엔진이므로 종료하고 다시 시작한 후에도 데이터가 계속 존재합니다. 실제 데이터 처리 과정은 메모리에서 일어나므로 디스크에 있는 데이터를 메모리에 로드해야 하며, 쓰기나 수정 요청을 처리하는 경우에도 메모리에 있는 내용을 디스크에 새로 고쳐야 합니다. 그리고 우리는 디스크를 읽고 쓰는 속도가 매우 느리다는 것을 알고 있습니다. 이는 메모리에서 읽고 쓰는 것과는 몇 배 정도 다릅니다. 따라서 테이블에서 특정 레코드를 얻으려면 InnoDB 스토리지 엔진이 읽어야 합니다. 디스크의 레코드가 하나씩? InnoDB가 채택한 방식은 데이터를 여러 페이지로 나누고, 디스크와 메모리 간 상호 작용의 기본 단위로 페이지를 사용하는 것입니다. InnoDB의 페이지 크기는 일반적으로 16입니다.

InnoDB는 MySQL의 데이터베이스 엔진 중 하나이며 현재 MySQL AB의 바이너리 릴리스 표준 중 하나입니다. InnoDB는 이중 트랙 인증 시스템을 채택합니다. 하나는 GPL 인증이고 다른 하나는 독점 소프트웨어입니다. 권한 부여. InnoDB는 트랜잭션 데이터베이스에 선호되는 엔진이며 트랜잭션 보안 테이블(ACID)을 지원합니다. InnoDB는 최대 범위의 동시성을 지원할 수 있는 행 수준 잠금을 지원합니다.

1. Mysql 트랜잭션 격리 수준 이 네 가지 격리 수준은 여러 트랜잭션 동시성 충돌이 있는 경우 더티 읽기, 반복 불가능 읽기 및 팬텀 읽기 문제가 발생할 수 있으며 innoDB는 반복 읽기 격리 수준 모드에서 이를 해결합니다. 2. 팬텀 읽기란 동일한 트랜잭션에서 첫 번째 트랜잭션에서 범위 쿼리를 실행한 것처럼 전후에 동일한 범위를 두 번 쿼리했을 때 얻은 결과가 일치하지 않는 것을 의미합니다. 이때 조건에 맞는 데이터는 1개뿐이며, 두 번째 트랜잭션에서는 데이터 행을 삽입하여 제출합니다. 첫 번째 쿼리입니다. 첫 번째 트랜잭션의 첫 번째 쿼리와 두 번째 쿼리는 모두 동일합니다.

1. mysql을 롤백하고 다시 설치합니다. 다른 위치에서 이 데이터를 가져오는 문제를 방지하려면 먼저 현재 라이브러리의 데이터베이스 파일(/var/lib/mysql/location)을 백업합니다. 다음으로 Perconaserver5.7 패키지를 제거하고 원래의 이전 5.1.71 패키지를 다시 설치하고 mysql 서비스를 시작했는데 Unknown/unsupportedtabletype:innodb 메시지가 표시되어 정상적으로 시작할 수 없었습니다. 11050912:04:27InnoDB:버퍼풀 초기화 중, 크기=384.0M11050912:04:27InnoDB:완료

MySQL 스토리지 엔진 선택 비교: InnoDB, MyISAM 및 메모리 성능 지수 평가 소개: MySQL 데이터베이스에서 스토리지 엔진의 선택은 시스템 성능과 데이터 무결성에 중요한 역할을 합니다. MySQL은 다양한 스토리지 엔진을 제공하며, 가장 일반적으로 사용되는 엔진으로는 InnoDB, MyISAM 및 Memory가 있습니다. 이 기사에서는 이 세 가지 스토리지 엔진의 성능 지표를 평가하고 코드 예제를 통해 비교합니다. 1. InnoDB 엔진 InnoDB는 나의 것

MySQL은 널리 사용되는 데이터베이스 관리 시스템이며, 다양한 스토리지 엔진이 데이터베이스 성능에 서로 다른 영향을 미칩니다. MyISAM과 InnoDB는 MySQL에서 가장 일반적으로 사용되는 두 가지 스토리지 엔진으로 서로 다른 특성을 갖고 있으며 부적절한 사용은 데이터베이스 성능에 영향을 미칠 수 있습니다. 이 기사에서는 이 두 가지 스토리지 엔진을 사용하여 MySQL 성능을 최적화하는 방법을 소개합니다. 1. MyISAM 스토리지 엔진 MyISAM은 MySQL에 가장 일반적으로 사용되는 스토리지 엔진으로, 빠른 속도와 작은 저장 공간이 장점입니다. 마이ISA

MySQL 스토리지 엔진의 읽기 성능을 향상시키기 위한 팁 및 전략: MyISAM과 InnoDB의 비교 분석 소개: MySQL은 주로 대량의 구조화된 데이터를 저장하고 관리하는 데 사용되는 가장 일반적으로 사용되는 오픈 소스 관계형 데이터베이스 관리 시스템 중 하나입니다. 애플리케이션에서는 읽기 작업이 대부분의 애플리케이션에서 주요 작업 유형이기 때문에 데이터베이스의 읽기 성능이 매우 중요한 경우가 많습니다. 이 기사에서는 일반적으로 사용되는 두 가지 스토리지 엔진인 MyISAM과 InnoDB에 대한 비교 분석을 중심으로 MySQL 스토리지 엔진의 읽기 성능을 향상시키는 방법에 중점을 둘 것입니다.

GIS 데이터를 지원하는 MySQL 스토리지 엔진: InnoDB의 공간 인덱스 최적화 개요: 최신 데이터베이스 애플리케이션에서 지리 정보 시스템(GIS) 데이터는 점점 더 중요한 역할을 합니다. GIS 데이터 처리는 복잡하고 동적이며 기존 관계형 데이터베이스는 이러한 유형의 데이터를 처리하는 데 적합하지 않습니다. 그러나 MySQL은 GIS 데이터 처리를 최적화할 수 있는 스토리지 엔진인 InnoDB를 제공합니다. 이 기사에서는 InnoDB 스토리지 엔진에서 공간 인덱스를 사용하여 GIS 데이터를 최적화하는 방법을 소개합니다.
