Mysql

如果按索引列選擇,查詢不返回結果(Mysql 5.6)

  • July 5, 2019

我們遇到了一個非常奇怪的情況,即我們在 select 中使用索引列的所有查詢都沒有返回任何結果。另一方面,如果我們這樣做select *或在任何其他非索引列上,那麼我們會得到預期的結果。

例如,

SELECT product_id FROM product where product_id = 1; 

不返回任何結果。

但是如果我們這樣做:

SELECT * FROM product where product_id = 1;

我們得到了預期的結果。

表引擎曾經是 MyISAM,但是,當我們將其更改為 InnoDB 時,我們開始遇到這些問題。我們不得不將它改回 MyISAM,因為很多網站都因為上述問題而關閉。

伺服器規格:

  • Ubuntu 16.04 LTS
  • mysql 5.6
  • 27GB 記憶體
  • 使用 100 GB 時有 300 GB 可用空間

我的.cnf:

  1. innodb_log_file_size = 3G
  2. innodb_buffer_pool_instances = 21
  3. key_buffer_size = 500M
  4. innodb_buffer_pool_size = 22G
  5. 排序緩衝區大小 = 512K
  6. read_rnd_buffer_size = 512K
  7. 執行緒記憶體大小 = 9
  8. 執行緒記憶體大小 = 9
  9. innodb_file_per_table = 1
  10. innodb_buffer_pool_dump_at_shutdown = 1
  11. innodb_buffer_pool_dump_at_shutdown = 1

我們在應用程序端沒有任何錯誤,Mysql 中也沒有錯誤日誌。伺服器有 6 GB 的可用記憶體,所以我們沒有用完記憶體。以上建議由 mysqltunner.pl 提出

來自 Mysqltunner 的結果:

[OK] Slow queries: 0%    
[OK] Thread cache hit rate: 93%        
[OK] Table cache hit rate: 95%     
[OK] Open file limit used: 0%       
[OK] InnoDB File per table is activated        
[OK] InnoDB buffer pool / data size: 20.0G/21.3G        
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3G *     2/20G should be equal 25%        
[OK] InnoDB buffer pool instances: 21       
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95%        
[OK] InnoDB Write Log efficiency:  93%    
[OK] InnoDB log waits: 0.00% (0 waits / 68 writes)        

編輯 1

  1. 用於將 MyISAM 轉換為 InnoDb 的查詢
ALTER TABLE `product` ENGINE=InnoDB; 
  1. 表架構:
CREATE TABLE `product` (
 `product_id` int(11) NOT NULL,
 `product_type` int(11) NOT NULL DEFAULT '1',
 `product_quantity` float NOT NULL DEFAULT '0',
 `product_model` varchar(32) DEFAULT NULL,
 `product_sku` varchar(255) DEFAULT NULL,
 `product_label` tinyint(4) NOT NULL,
 `product_flavor` tinyint(4) NOT NULL,
 `product_new` tinyint(1) NOT NULL,
 `product_sale` tinyint(1) NOT NULL,
 `product_image` varchar(64) DEFAULT NULL,
 `product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `product` ADD PRIMARY KEY (`products_id`),

-- Added the FullText key when we changed it to InnoDB 
ALTER TABLE `product` ADD FULLTEXT KEY `model_index` (`products_model`);

我想知道如果你OPTIMIZE TABLE product;在 MySQL 伺服器上執行會發生什麼。OPTIMIZE TABLE 將“重建”表的物理儲存,包括索引。

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

為您的 my.cnf 考慮的建議

$$ mysqld $$部分

innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_page_cleaners=21  # to match your number of bp instances of 21
innodb_purge_threads=21  # to match your number of bp instances of 21
innodb_flushing_avg_loops=5  # to minimize loop delay 

免責聲明:我是我的個人資料、網路個人資料中提到的網站的網頁內容作者,可以提供額外的建議和免費的實用程序腳本來提高性能。

引用自:https://dba.stackexchange.com/questions/237597