提高 MySQL 查詢性能 - 記錄太多

我正在將 MySQL RDMS 用於聯繫人管理系統。現在一天，我的數據庫變得越來越大，包含超過 100 萬條記錄。檢查重複的電話號碼成為管理過程中的一個大問題。當我在整個數據庫中搜尋電話號碼時，我的伺服器負載急劇增加。由於我不想在數據庫中保留重複的電話記錄，我曾經檢查整個數據庫中是否存在電話號碼，這導致我的應用程序有點慢。我的問題：如何提高對整個數據庫的高性能查詢。

phone1 -> Datatype Varchar(10)

我嘗試了索引，它對我有一點作用。任何其他提高我的系統性能的方法。

表結構：

CREATE TABLE `phone_directory` (
 `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
 `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
 `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
 `first_name` VARCHAR(30) DEFAULT NULL,
 `middle_initial` CHAR(1) DEFAULT NULL,
 `last_name` VARCHAR(30) DEFAULT NULL,
 `address1` VARCHAR(100) DEFAULT NULL,
 `address2` VARCHAR(100) DEFAULT NULL,
 `address3` VARCHAR(100) DEFAULT NULL,
 `city` VARCHAR(50) DEFAULT NULL,
 `state` CHAR(2) DEFAULT NULL,
 `postal_code` VARCHAR(10) DEFAULT NULL,
 `phone1` VARCHAR(12) DEFAULT NULL,
 `phone2` VARCHAR(12) DEFAULT NULL,
 `phone3` VARCHAR(12) DEFAULT NULL,
 `email` VARCHAR(70) DEFAULT NULL,
 `fax_number` VARCHAR(255) DEFAULT NULL,
 `manager_name` VARCHAR(255) DEFAULT NULL,
 `status` VARCHAR(6) DEFAULT NULL,
 PRIMARY KEY (`lead_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

詢問：

SELECT * FROM phone_directory WHERE phone1 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone2 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone3 IN ('315XXXXXXX','0315XXXXXXX');

我建議不要有三列phone1,phone2和phone3，而是有一個與電話相關的表，並使您的數據完全規範化（或進一步規範化）：

CREATE TABLE `phone_directory` (
  `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
  `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
  `first_name` VARCHAR(30) DEFAULT NULL,
  `middle_initial` CHAR(1) DEFAULT NULL,
  `last_name` VARCHAR(30) DEFAULT NULL,
  `address1` VARCHAR(100) DEFAULT NULL,
  `address2` VARCHAR(100) DEFAULT NULL,
  `address3` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(50) DEFAULT NULL,
  `state` CHAR(2) DEFAULT NULL,
  `postal_code` VARCHAR(10) DEFAULT NULL,
  `email` VARCHAR(70) DEFAULT NULL,
  `fax_number` VARCHAR(255) DEFAULT NULL,
  `manager_name` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(6) DEFAULT NULL,
  PRIMARY KEY (`lead_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE phones
(
    lead_id INT(9) NOT NULL REFERENCES phone_directory(lead_id),
    phone VARCHAR(12) NOT NULL,  
    priority tinyint DEFAULT 1,  -- If you need to give them priorities (1, 2, 3, ...), or sort them
    CONSTRAINT unique_phones UNIQUE(phone), -- You don't want repetead telephones. This enforces it.
    PRIMARY KEY(lead_id, phone)  -- Covering index + clustering... for the sake of efficiency
) ;

您現在的支票只有：

SELECT 
   lead_id, phone 
FROM 
   phones 
WHERE 
   phone IN ('315XXXXXXX','0315XXXXXXX');

平均而言，這將快 3 倍……對於給定的 0、1、2、3 或任意數量的電話lead_id，並且您的UNIQUE約束強制執行唯一性，因此，如果您的應用程序中的某個時間點出現錯誤，數據庫可幫助您避免錯誤。

dbfiddle在這裡

---

注 1：一家公司擁有 3 個以上的電話號碼並不奇怪：您覆蓋了所有基地。

注意 2：由於電話號碼不會使​​用非 ASCII 字元，您可以通過指定單字元排序規則（例如latin1_bin.

dbfiddle在這裡

您需要在電話欄位上創建索引，並將它們更改為“非空”：

ALTER TABLE `phone_directory` CHANGE `phone1` `phone1` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `phone2` `phone2` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `phone3` `phone3` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
ADD INDEX ( `phone1` ) ,
ADD INDEX ( `phone2` ) ,
ADD INDEX ( `phone3` ) 

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