How to optimize mysql query performance? How to use mysql index?
Jun 04, 2025 pm 06:24 PM優(yōu)化MySQL查詢(xún)性能和正確使用索引需從合理創(chuàng)建索引、避免全表掃描、優(yōu)化SQL寫(xiě)法、定期維護(hù)表四方面入手。1. 合理創(chuàng)建索引,主鍵自動(dòng)有索引,常用于查詢(xún)條件的字段如用戶(hù)ID、訂單號(hào)建議加索引,組合查詢(xún)多時(shí)可用聯(lián)合索引并遵守最左匹配原則;2. 避免全表掃描,通過(guò)EXPLAIN查看是否使用索引,避免因函數(shù)操作、模糊查詢(xún)開(kāi)頭用通配符、類(lèi)型轉(zhuǎn)換、OR連接導(dǎo)致索引失效;3. 優(yōu)化SQL寫(xiě)法,避免SELECT *,減少數(shù)據(jù)傳輸,改用JOIN代替多層子查詢(xún),分頁(yè)大數(shù)據(jù)時(shí)采用基于索引的游標(biāo)方式;4. 定期分析維護(hù)表,使用ANALYZE TABLE更新統(tǒng)計(jì)信息,頻繁更新的表適當(dāng)重建索引或OPTIMIZE TABLE,監(jiān)控慢查詢(xún)?nèi)罩静?yōu)化耗時(shí)SQL。
MySQL查詢(xún)性能優(yōu)化和索引使用是數(shù)據(jù)庫(kù)開(kāi)發(fā)中非常關(guān)鍵的環(huán)節(jié)。如果處理不當(dāng),即使是結(jié)構(gòu)良好的數(shù)據(jù)庫(kù)也可能出現(xiàn)響應(yīng)緩慢、資源占用過(guò)高的問(wèn)題。下面從幾個(gè)實(shí)際場(chǎng)景出發(fā),講講怎么優(yōu)化查詢(xún)和正確使用索引。
1. 合理創(chuàng)建索引,別亂加也別不加
索引不是越多越好,也不是越少越好。它能加快查詢(xún)速度,但也會(huì)拖慢寫(xiě)入操作(比如INSERT、UPDATE)。所以要根據(jù)實(shí)際查詢(xún)需求來(lái)決定哪些字段需要加索引。
- 主鍵自動(dòng)有索引,不需要額外添加。
- 常用于查詢(xún)條件的字段(如用戶(hù)ID、訂單號(hào))建議加上索引。
- 對(duì)于組合查詢(xún)較多的情況,可以考慮建立聯(lián)合索引(復(fù)合索引),注意順序很重要,最左匹配原則必須遵守。
舉個(gè)例子:你經(jīng)常用 WHERE name = 'Tom' AND age = 25
查詢(xún),那么在 (name, age)
上建一個(gè)聯(lián)合索引就比單獨(dú)給兩個(gè)字段加索引更高效。
2. 避免全表掃描,讓查詢(xún)走索引
當(dāng)你執(zhí)行一條SQL語(yǔ)句時(shí),可以通過(guò) EXPLAIN
查看是否使用了索引。如果看到 type=ALL
,說(shuō)明是在做全表掃描,效率很低。
常見(jiàn)的導(dǎo)致索引失效的原因包括:
- 使用函數(shù)或表達(dá)式操作字段,例如
WHERE YEAR(create_time) = 2023
- 模糊查詢(xún)以通配符開(kāi)頭,比如
LIKE '%abc'
- 類(lèi)型轉(zhuǎn)換,比如字符串字段傳入數(shù)字進(jìn)行比較
- 使用 OR 連接多個(gè)條件,其中一個(gè)沒(méi)索引
解決辦法也很直接:調(diào)整SQL語(yǔ)句結(jié)構(gòu),避免上述情況,盡量讓查詢(xún)命中索引。
3. SQL語(yǔ)句寫(xiě)法也要講究
有時(shí)候問(wèn)題不在索引,而在SQL本身。比如:
- 不要寫(xiě)
SELECT *
,只選你需要的字段,減少數(shù)據(jù)傳輸量 - 盡量避免在子查詢(xún)中嵌套太多層,改用 JOIN 更高效
- 分頁(yè)查詢(xún)大數(shù)據(jù)量時(shí),慎用
LIMIT offset, size
,offset太大容易卡頓
舉個(gè)例子,分頁(yè)查到第10萬(wàn)條的時(shí)候,LIMIT 100000, 10
會(huì)先掃描前10萬(wàn)零10行再丟棄前面的,效率很低。這時(shí)候可以用基于索引的“游標(biāo)”方式分頁(yè),比如記錄上一頁(yè)最后一條的ID,然后 WHERE id > last_id ORDER BY id LIMIT 10
。
4. 定期分析和維護(hù)表
即使你的SQL和索引都寫(xiě)得不錯(cuò),隨著數(shù)據(jù)量增長(zhǎng),查詢(xún)性能還是會(huì)慢慢變差。這時(shí)就需要定期做一些維護(hù)工作:
- 使用
ANALYZE TABLE
更新統(tǒng)計(jì)信息,幫助優(yōu)化器選擇更好的執(zhí)行計(jì)劃 - 對(duì)于頻繁更新的表,適當(dāng)重建索引或優(yōu)化表(
OPTIMIZE TABLE
) - 監(jiān)控慢查詢(xún)?nèi)罩?,找出?zhí)行時(shí)間長(zhǎng)的SQL并針對(duì)性?xún)?yōu)化
你可以設(shè)置 MySQL 的慢查詢(xún)閾值(如0.1秒),把超過(guò)這個(gè)時(shí)間的SQL記錄下來(lái),作為優(yōu)化的重點(diǎn)對(duì)象。
基本上就這些。MySQL優(yōu)化不是一蹴而就的事,需要結(jié)合具體業(yè)務(wù)、數(shù)據(jù)分布和查詢(xún)模式來(lái)持續(xù)調(diào)整。關(guān)鍵是理解索引原理,掌握常見(jiàn)問(wèn)題的排查方法,才能做到有的放矢。
The above is the detailed content of How to optimize mysql query performance? How to use mysql index?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

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

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

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

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

The top ten authoritative cryptocurrency market and data analysis platforms in 2025 are: 1. CoinMarketCap, providing comprehensive market capitalization rankings and basic market data; 2. CoinGecko, providing multi-dimensional project evaluation with independence and trust scores; 3. TradingView, having the most professional K-line charts and technical analysis tools; 4. Binance market, providing the most direct real-time data as the largest exchange; 5. Ouyi market, highlighting key derivative indicators such as position volume and capital rate; 6. Glassnode, focusing on on-chain data such as active addresses and giant whale trends; 7. Messari, providing institutional-level research reports and strict standardized data; 8. CryptoCompa

Ethereum is a decentralized application platform based on smart contracts, and its native token ETH can be obtained in a variety of ways. 1. Register an account through centralized platforms such as Binance and Ouyiok, complete KYC certification and purchase ETH with stablecoins; 2. Connect to digital storage through decentralized platforms, and directly exchange ETH with stablecoins or other tokens; 3. Participate in network pledge, and you can choose independent pledge (requires 32 ETH), liquid pledge services or one-click pledge on the centralized platform to obtain rewards; 4. Earn ETH by providing services to Web3 projects, completing tasks or obtaining airdrops. It is recommended that beginners start from mainstream centralized platforms, gradually transition to decentralized methods, and always attach importance to asset security and independent research, to

The most suitable tools for querying stablecoin markets in 2025 are: 1. Binance, with authoritative data and rich trading pairs, and integrated TradingView charts suitable for technical analysis; 2. Ouyi, with clear interface and strong functional integration, and supports one-stop operation of Web3 accounts and DeFi; 3. CoinMarketCap, with many currencies, and the stablecoin sector can view market value rankings and deans; 4. CoinGecko, with comprehensive data dimensions, provides trust scores and community activity indicators, and has a neutral position; 5. Huobi (HTX), with stable market conditions and friendly operations, suitable for mainstream asset inquiries; 6. Gate.io, with the fastest collection of new coins and niche currencies, and is the first choice for projects to explore potential; 7. Tra

The real use of battle royale in the dual currency system has not yet happened. Conclusion In August 2023, the MakerDAO ecological lending protocol Spark gave an annualized return of $DAI8%. Then Sun Chi entered in batches, investing a total of 230,000 $stETH, accounting for more than 15% of Spark's deposits, forcing MakerDAO to make an emergency proposal to lower the interest rate to 5%. MakerDAO's original intention was to "subsidize" the usage rate of $DAI, almost becoming Justin Sun's Solo Yield. July 2025, Ethe

Table of Contents Crypto Market Panoramic Nugget Popular Token VINEVine (114.79%, Circular Market Value of US$144 million) ZORAZora (16.46%, Circular Market Value of US$290 million) NAVXNAVIProtocol (10.36%, Circular Market Value of US$35.7624 million) Alpha interprets the NFT sales on Ethereum chain in the past seven days, and CryptoPunks ranked first in the decentralized prover network Succinct launched the Succinct Foundation, which may be the token TGE

Stablecoins are cryptocurrencies with value anchored by fiat currency or commodities, designed to solve price fluctuations such as Bitcoin. Their importance is reflected in their role as a hedging tool, a medium of trading and a bridge connecting fiat currency with the crypto world. 1. The fiat-collateralized stablecoins are fully supported by fiat currencies such as the US dollar. The advantage is that the mechanism is simple and stable. The disadvantage is that they rely on the trust of centralized institutions. They represent the projects including USDT and USDC; 2. The cryptocurrency-collateralized stablecoins are issued through over-collateralized mainstream crypto assets. The advantages are decentralization and transparency. The disadvantage is that they face liquidation risks. The representative project is DAI. 3. The algorithmic stablecoins rely on the algorithm to adjust supply and demand to maintain price stability. The advantages are that they do not need to be collateral and have high capital efficiency. The disadvantage is that the mechanism is complex and the risk is high. There have been cases of dean-anchor collapse. They are still under investigation.

What is Treehouse(TREE)? How does Treehouse (TREE) work? Treehouse Products tETHDOR - Decentralized Quotation Rate GoNuts Points System Treehouse Highlights TREE Tokens and Token Economics Overview of the Third Quarter of 2025 Roadmap Development Team, Investors and Partners Treehouse Founding Team Investment Fund Partner Summary As DeFi continues to expand, the demand for fixed income products is growing, and its role is similar to the role of bonds in traditional financial markets. However, building on blockchain

A verbal battle about the value of "creator tokens" swept across the crypto social circle. Base and Solana's two major public chain helmsmans had a rare head-on confrontation, and a fierce debate around ZORA and Pump.fun instantly ignited the discussion craze on CryptoTwitter. Where did this gunpowder-filled confrontation come from? Let's find out. Controversy broke out: The fuse of Sterling Crispin's attack on Zora was DelComplex researcher Sterling Crispin publicly bombarded Zora on social platforms. Zora is a social protocol on the Base chain, focusing on tokenizing user homepage and content
