MySQL，文本索引不能工作。

Question

我创建了一个这样的桌子

CREATE TABLE `text_tests` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `text_st_date` text NOT NULL,
     `varchar_st_date` varchar(255) NOT NULL DEFAULT '2015-08-25',
     `text_id` text NOT NULL,
     `varchar_id` varchar(255) NOT NULL DEFAULT '0',
     `int_id` int(11) NOT NULL DEFAULT '0',
     `created_at` datetime DEFAULT NULL,
     `updated_at` datetime DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_of_text_st_date` (`text_st_date`(50),`id`),
     KEY `idx_of_varchar_st_date` (`varchar_st_date`,`id`),
     KEY `idx_of_text_id` (`text_id`(20),`id`),
     KEY `idx_of_varchar_id` (`varchar_id`,`id`),
     KEY `idx_of_int_id` (`int_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后我用Ruby做一些数据

(1..10000).each do |_i|
  item = TextTest.new
  item.text_st_date = (Time.now + _i.days).to_s
  item.varchar_st_date = (Time.now + _i.days).to_s
  item.text_id = _i
  item.varchar_id = _i
  item.int_id = _i

  item.save
end

最后，我尝试使用索引的文本，但它不能工作，它总是全表扫描。

EXPLAIN SELECT id
FROM text_tests
ORDER BY text_st_date DESC
LIMIT 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: text_tests
         type: ALL  
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9797
        Extra: Using filesort
1 row in set (0.02 sec)

EXPLAIN SELECT id
FROM text_tests
ORDER BY text_id DESC
LIMIT 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: text_tests
         type: ALL 
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9797
        Extra: Using filesort
1 row in set (0.00 sec)

varchar很好

EXPLAIN SELECT id
FROM text_tests
ORDER BY varchar_st_date DESC
LIMIT 20\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: text_tests
         type: index
possible_keys: NULL
          key: idx_of_varchar_st_date `enter code here`
      key_len: 771
          ref: NULL
         rows: 20
        Extra: Using index
1 row in set (0.00 sec)



EXPLAIN SELECT id
FROM text_tests
ORDER BY varchar_id DESC
LIMIT 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: text_tests
         type: index
possible_keys: NULL
          key: idx_of_varchar_id 
      key_len: 771
          ref: NULL
         rows: 20
        Extra: Using index
1 row in set (0.00 sec)

为什么文本索引不能工作，以及如何使用文本索引？

Answer 1

索引对于满足返回结果集中的表的所有行的查询没有很强的用途。它们的主要目的之一是加速WHERE和JOIN ... ON子句。如果查询没有WHERE子句，那么如果查询规划器决定扫描整个表，请不要感到惊讶。

另外，您的第一个查询执行ORDER BY text_column。但是索引只包含该列的前50个字符。因此，为了满足查询，MySql对整件事情进行了排序。更重要的是，它必须在硬盘上对其进行排序，因为内存中的表支持不能处理BLOB或文本大对象。

Answer 2

MySQL非常擅长处理日期，但您需要告诉它您有日期，而不是VARCHAR(255)。

对日期列使用DATE数据类型！如果Ruby不能帮你做到这一点，那么就摆脱Ruby吧。