如何計算 MySQL 中索引的讀取次數

我有一個帶索引的表。

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                         P_NAME        VARCHAR(55) NOT NULL,
                         P_MFGR        CHAR(25) NOT NULL,
                         P_BRAND       CHAR(10) NOT NULL,
                         P_TYPE        VARCHAR(25) NOT NULL,
                         P_SIZE        INTEGER NOT NULL,
                         P_CONTAINER   CHAR(10) NOT NULL,
                         P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                         P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                            L_PARTKEY     INTEGER NOT NULL,
                            L_SUPPKEY     INTEGER NOT NULL,
                            L_LINENUMBER  INTEGER NOT NULL,
                            L_QUANTITY    DECIMAL(15,2) NOT NULL,
                            L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                            L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                            L_TAX         DECIMAL(15,2) NOT NULL,
                            L_RETURNFLAG  CHAR(1) NOT NULL,
                            L_LINESTATUS  CHAR(1) NOT NULL,
                            L_SHIPDATE    DATE NOT NULL,
                            L_COMMITDATE  DATE NOT NULL,
                            L_RECEIPTDATE DATE NOT NULL,
                            L_SHIPINSTRUCT CHAR(25) NOT NULL,
                            L_SHIPMODE     CHAR(10) NOT NULL,
                            L_COMMENT      VARCHAR(44) NOT NULL);

ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);

並查詢

SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
      part
WHERE  ( p_partkey = l_partkey
        AND p_brand = 'Brand#52'
        AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
        AND l_quantity >= 4
        AND l_quantity <= 4 + 10
        AND p_size BETWEEN 1 AND 5
        AND l_shipmode IN ( 'AIR', 'AIR REG' )
        AND l_shipinstruct = 'DELIVER IN PERSON' )
       OR ( p_partkey = l_partkey
            AND p_brand = 'Brand#11'
            AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
            AND l_quantity >= 18
            AND l_quantity <= 18 + 10
            AND p_size BETWEEN 1 AND 10
            AND l_shipmode IN ( 'AIR', 'AIR REG' )
            AND l_shipinstruct = 'DELIVER IN PERSON' )
       OR ( p_partkey = l_partkey
            AND p_brand = 'Brand#51'
            AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
            AND l_quantity >= 29
            AND l_quantity <= 29 + 10
            AND p_size BETWEEN 1 AND 15
            AND l_shipmode IN ( 'AIR', 'AIR REG' )
            AND l_shipinstruct = 'DELIVER IN PERSON' ); 

我想知道

• 在索引上執行了多少次讀取。
• 對錶數據執行了多少次讀取。
• 緩衝區（記憶體）和磁碟中有多少索引。
• 緩衝區（記憶體）和磁碟中有多少表數據。

謝謝！

指標

可以找到單個查詢在表中查看的行數。這些為您的連接計算索引或數據行，而不是單獨計算。如果沒有其他查詢正在執行，則可以進行總計數。

SHOW SESSION STATUS LIKE 'Handler%';

以下是使用它來幫助優化的討論：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

從

SHOW GLOBAL STATUS LIKE 'Innodb_%';

您可以獲得來自所有連接的所有 InnoDB 語句的磁碟訪問的某些全域計數等。（這不正是您所要求的，但可能很有趣。）

另一個來源是系統數據庫performance_schema。（在舊的 MySQL 版本中，information_schema有一些表，但現在大部分都移到了 PS 上。）

如果您詳細說明問題的目標，我們可能會提供更詳細的幫助。

優化該查詢

從使用現代JOIN語法開始：

FROM lineitem AS l
JOIN part AS p  ON p.partkey = l.partkey

這是 OR 的共同部分；這使得優化器可以更有效地執行 JOIN。

如果優化器決定從 開始part，有這個

lineitem:  INDEX(partkey)  -- (but see below)

現在我看到這l.shipinstruct = 'DELIVER IN PERSON'在 OR 中很常見，所以把它拉出來。那麼這可能會有用

lineitem:  INDEX(shipinstruct)  -- (but see below)

但是，所有這些可能仍然不會像變成ORinto那樣做優化UNION ALL：

( SELECT ... Brand#52 ... )
UNION ALL
( SELECT ... Brand#11 ... )
UNION ALL
( SELECT ... Brand#51 ... )

然後我們可以建構一些更好的索引：

part:  INDEX(brand, container)
part:  INDEX(brand, size)
lineitem:  INDEX(shipinstruct, quantity)
lineitem:  INDEX(shipinstruct, shipmode)
lineitem:  INDEX(partkey, shipinstruct, quantity)
lineitem:  INDEX(partkey, shipinstruct, shipmode)

優化器可能會選擇多種方式來執行查詢；我認為這些涵蓋了所有基礎。

引用自：https://dba.stackexchange.com/questions/290388