1 Performance consumption of table return
Regardless of single column index or joint index, an index corresponds to an independent B index tree. The index tree nodes only contain:
Field values in the index
Primary key value
Even if the required data is found according to the conditions according to the index tree, It is only the values of a few fields in the index and the primary key value. If you make a select *
, then there will be other fields, and you need to go back to the table and look for them in the clustered index based on the primary key. , the leaf node of the clustered index is the data page. Only by finding the data page can all the field values of a row of data be read out.
So it is similar to
select * from table order by xx1,xx2,xx3
We have to retrieve all the data in order from the index tree of the joint index, and then perform a primary key clustered index search for each piece of data, which is not very performant.
SometimesMySQL
The execution engine may think that if you are like
select * from table order by xx1,xx2,xx3
it is equivalent to scanning all the data of the joint index and clustered index, both indexes, then it is not as good as Instead of using the joint index, you can directly scan the entire table, so you only need to scan one primary key index.
But if the shape is like:
select * from table order by xx1,xx2,xx3 limit 10
The execution engine will know that you first scan the index tree of the joint index, get 10 pieces of data, and then compare the 10 pieces of data in Just search 10 times in the clustered index, then the joint index will still be used.
2 Covering index
Covering index is not an index, it is just a method based on index query, that is, only a few fields in the joint index are needed for things like
select xx1,xx2,xx3 from table order by xx1,xx2,xx3
value, then you only need to scan the index tree of the joint index without going back to the table to find other fields. This query method is a covering index.
So when you use a joint index, pay attention to whether it may cause a large number of tables to be returned to the clustered index. If the number of times to return the table to the clustered index is too many, you may be directly scanned of the whole table without using the joint index. .
If possible, specify only the fields you need in SQL instead of violently select *
. It is best to go directly to the covering index.
Even if it is unavoidable to return the table, you should try your best to use limit
and where
to limit the number of table returns, and then filter a small number of data from the joint index, and then return table, so the performance is better.
The above is the detailed content of What is the performance consumption of MySQL table return?. For more information, please follow other related articles on the PHP Chinese website!

MySQLBLOBshavelimits:TINYBLOB(255bytes),BLOB(65,535bytes),MEDIUMBLOB(16,777,215bytes),andLONGBLOB(4,294,967,295bytes).TouseBLOBseffectively:1)ConsiderperformanceimpactsandstorelargeBLOBsexternally;2)Managebackupsandreplicationcarefully;3)Usepathsinst

The best tools and technologies for automating the creation of users in MySQL include: 1. MySQLWorkbench, suitable for small to medium-sized environments, easy to use but high resource consumption; 2. Ansible, suitable for multi-server environments, simple but steep learning curve; 3. Custom Python scripts, flexible but need to ensure script security; 4. Puppet and Chef, suitable for large-scale environments, complex but scalable. Scale, learning curve and integration needs should be considered when choosing.

Yes,youcansearchinsideaBLOBinMySQLusingspecifictechniques.1)ConverttheBLOBtoaUTF-8stringwithCONVERTfunctionandsearchusingLIKE.2)ForcompressedBLOBs,useUNCOMPRESSbeforeconversion.3)Considerperformanceimpactsanddataencoding.4)Forcomplexdata,externalproc

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,idealforconsistentlengthdatalikecountrycodes;2)VARCHARforvariable-lengthstrings,suitableforfieldslikenames;3)TEXTtypesforlargertext,goodforblogpostsbutcanimpactperformance;4)BINARYandVARB

TomasterMySQLBLOBs,followthesesteps:1)ChoosetheappropriateBLOBtype(TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB)basedondatasize.2)InsertdatausingLOAD_FILEforefficiency.3)Storefilereferencesinsteadoffilestoimproveperformance.4)UseDUMPFILEtoretrieveandsaveBLOBsco

BlobdatatypesinmysqlareusedforvoringLargebinarydatalikeImagesoraudio.1) Useblobtypes (tinyblobtolongblob) Basedondatasizeneeds. 2) Storeblobsin Perplate Petooptimize Performance.3) ConsidersxterNal Storage Forel Blob Romana DatabasesizerIndimprovebackupupe

ToadduserstoMySQLfromthecommandline,loginasroot,thenuseCREATEUSER'username'@'host'IDENTIFIEDBY'password';tocreateanewuser.GrantpermissionswithGRANTALLPRIVILEGESONdatabase.*TO'username'@'host';anduseFLUSHPRIVILEGES;toapplychanges.Alwaysusestrongpasswo

MySQLofferseightstringdatatypes:CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,andSET.1)CHARisfixed-length,idealforconsistentdatalikecountrycodes.2)VARCHARisvariable-length,efficientforvaryingdatalikenames.3)BINARYandVARBINARYstorebinarydata,similartoC


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 Linux new version
SublimeText3 Linux latest version

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
