看看这个数据库的索引对不对？

Question

我有如下三张表：CREATE TABLE `station` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
Station大概有10多条记录CREATE TABLE `point` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `downLimit` double DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `senid` varchar(255) NOT NULL,
  `upLimit` double DEFAULT NULL,
  `station_id` bigint(20) NOT NULL,
  `sort` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK49B657052A80738` (`station_id`),
  CONSTRAINT `FK49B657052A80738` FOREIGN KEY (`station_id`) REFERENCES `station` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;
Poin十多条记录
CREATE TABLE `datasource` (
  `senid` varchar(255) NOT NULL,
  `t` datetime NOT NULL,
  `v` double NOT NULL,
  PRIMARY KEY (`senid`,`t`),
  UNIQUE KEY `IDX_DATASOURCE_SENID` (`senid`,`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DataSource这张表有20万条数据，其中senid和t确定唯一性我的查询语句：
SELECT * FROM DATASOURCE D,POINT P WHERE D.SENID = P.SENID ORDER BY D.T DESC LIMIT 100;在DataSource中没有加
UNIQUE KEY `IDX_DATASOURCE_SENID` (`senid`,`t`)约束时查询时间大概是6到10秒，limit 1 也一样
昨天早上加上了IDX_DATASOURCE_SENID索引查询时间锐减为0.几秒，
可是到了晚上突然失效了，又变成6.多秒，索引还是一样，数据库重装了都没用咋回事？索引失效？还是索引原本就建错了？（ 数据库目前就我一个人访问）

Answer 1

create index xxxx on datasource(t,senid)

Answer 2

select * from tb force index(IDX_DATASOURCE_SENID)

Answer 3

select * from datasource d force index (IDX_DATASOURCE_SENID) ,point p where d.senid = p.senid order by d.t desc;
试过没用。

Answer 4

贴出你的EXPLAIN 和SHOW INDEX FROM

Answer 5

语句：
mysql> select * from DataSource d,Point p,Station s where d.senid = p.senid and
p.station_id = s.id order by d.t desc limit 100 ;EXPLAIN:
TABLE       POSSIBLE_KEY   KEY   KEY_LEN   REF        ROWS   EXTRA
station     primary        null  null      null        7      use tem...;use file..
point       fk...          fk..  8         ybsq.s.id   2      null
datasource  primary        primary 767     ybsq.p.senid 12575  null
SHOW INDEX FROM DATASOURCE
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name             | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+
| DATASOURCE |          0 | PRIMARY              |            1 | senid       |
A         |          15 |     NULL | NULL   |      | BTREE      |         |
| DATASOURCE |          0 | PRIMARY              |            2 | t           |
A         |      200642 |     NULL | NULL   |      | BTREE      |         |
| DATASOURCE |          1 | IDX_DATASOURCE_SENID |            1 | t           |
A         |      200642 |     NULL | NULL   |      | BTREE      |         |
| DATASOURCE |          1 | IDX_DATASOURCE_SENID |            2 | senid       |
A         |      200642 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+

Answer 6

MYSQL候选中甚至都没有IDX_DATASOURCE_SENID,
这回我用force index(idx_.....)突然就可以了，可是我的程序不能用force index,
照理说有了INDEX，MYSQL会选择INDEX来查询，
这是今天的情况，昨天的情况更糟糕，POSISABLE_KEY候选中已经出现IDX_DATASOURCE_SENID,
最终KEY也是IDX_....,然而最还是查询6，到10多的时间，
查询0.0几秒的情况就出现过半个小时，也不知道我做了什么，而后突然就不行了。

Answer 7

是这个
mysql> select * from DataSource d,Point p,Station s where d.senid = p.senid and
p.station_id = s.id order by d.t desc limit 100 ;

Answer 8

贴出以下结果explain select * from DataSource d,Point p,Station s where d.senid = p.senid and p.station_id = s.id order by d.t desc limit 100;
show index from station;
show index from point;贴的时候向你贴SHOW INDEX FROM DATASOURCE
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name             | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | P
这种方法贴出，方便阅读。

Answer 9

explain select * from DataSource d,Point p,Station s where d.senid = p.senid and p.station_id = s.id order by d.t desc limit 100;+----+-------------+-------+------+-------------------+-------------------+-----
----+--------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_
len | ref          | rows  | Extra                           |
+----+-------------+-------+------+-------------------+-------------------+-----
----+--------------+-------+---------------------------------+
|  1 | SIMPLE      | s     | ALL  | PRIMARY           | NULL              | NULL
    | NULL         |     7 | Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | FK49B657052A80738 | FK49B657052A80738 | 8
    | ybsq.s.id    |     2 |                                 |
|  1 | SIMPLE      | d     | ref  | PRIMARY           | PRIMARY           | 767
    | ybsq.p.senid | 12540 |                                 |
+----+-------------+-------+------+-------------------+-------------------+-----
----+--------------+-------+---------------------------------+
show index from station;
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
| station |          0 | PRIMARY  |            1 | id          | A         |
       7 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+
show index from point;
+-------+------------+-------------------+--------------+-------------+---------
--+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collatio
n | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------+--------------+-------------+---------
--+-------------+----------+--------+------+------------+---------+
| point |          0 | PRIMARY           |            1 | id          | A
  |          32 |     NULL | NULL   |      | BTREE      |         |
| point |          1 | FK49B657052A80738 |            1 | station_id  | A
  |          16 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-------------------+--------------+-------------+---------
--+-------------+----------+--------+------+------------+---------+
show index from dataSource;
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name             | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+
| dataSource |          0 | PRIMARY              |            1 | senid       |
A         |          16 |     NULL | NULL   |      | BTREE      |         |
| dataSource |          0 | PRIMARY              |            2 | t           |
A         |      201204 |     NULL | NULL   |      | BTREE      |         |
| dataSource |          1 | IDX_DATASOURCE_SENID |            1 | t           |
A         |      201204 |     NULL | NULL   |      | BTREE      |         |
| dataSource |          1 | IDX_DATASOURCE_SENID |            2 | senid       |
A         |      201204 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------------------+--------------+-------------+-
----------+-------------+----------+--------+------+------------+---------+

Answer 10

试试这个select STRAIGHT_JOIN * 
from DataSource d FORCE  INDEX (IDX_DATASOURCE_SENID) ,Point p,Station s 
where d.senid = p.senid and p.station_id = s.id order by d.t desc limit 100;

Answer 11

force 是可以，不过WEB程序已经写好，不能改
原本那条语句是可以的，只是突然不行了。

Answer 12

关键还是您的senid太长了,您可以在后面两个表中建立senid_crc字段=crc32(senid),参考下面的用法
Building your own hash indexes. If your storage engine doesn’t support hash indexes,
you can emulate them yourself in a manner similar to that InnoDB uses. This will
give you access to some of the desirable properties of hash indexes, such as a very
small index size for very long keys.
The idea is simple: create a pseudohash index on top of a standard B-Tree index. It
will not be exactly the same thing as a real hash index, because it will still use the B-
Tree index for lookups. However, it will use the keys’ hash values for lookups,
instead of the keys themselves. All you need to do is specify the hash function manu-
ally in the query’s WHERE clause.
An example of when this approach works well is for URL lookups. URLs generally
cause B-Tree indexes to become huge, because they’re very long. You’d normally
query a table of URLs like this:
mysql> SELECT id FROM url WHERE url="http://www.mysql.com";
But if you remove the index on the url column and add an indexed url_crc column
to the table, you can use a query like this:
mysql> SELECT id FROM url WHERE url="http://www.mysql.com"
    -> AND url_crc=CRC32("http://www.mysql.com);
This works well because the MySQL query optimizer notices there’s a small, highly
selective index on the url_crc column and does an index lookup for entries with that
value (1560514994, in this case). Even if several rows have the same url_crc value,
it’s very easy to find these rows with a fast integer comparison and then examine
them to find the one that matches the full URL exactly. The alternative is to index
the full URL as a string, which is much slower.104 | Chapter 3: Schema Optimization and Indexing
One drawback to this approach is the need to maintain the hash values. You can do
this manually or, in MySQL 5.0 and newer, you can use triggers. The following
example shows how triggers can help maintain the url_crc column when you insert
and update values. First, we create the table:
CREATE TABLE pseudohash (
   id int unsigned NOT NULL auto_increment,
   url varchar(255) NOT NULL,
   url_crc int unsigned NOT NULL DEFAULT 0,
   PRIMARY KEY(id)
);
Now we create the triggers. We change the statement delimiter temporarily, so we
can use a semicolon as a delimiter for the trigger:
DELIMITER |
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
|
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
|
DELIMITER ;
All that remains is to verify that the trigger maintains the hash:
mysql> INSERT INTO pseudohash (url) VALUES ('http://www.mysql.com');
mysql> SELECT * FROM pseudohash;
+----+----------------------+------------+
| id | url                  | url_crc    |
+----+----------------------+------------+
|  1 | http://www.mysql.com | 1560514994 |
+----+----------------------+------------+
mysql> UPDATE pseudohash SET url='http://www.mysql.com/' WHERE id=1;
mysql> SELECT * FROM pseudohash;
+----+-----------------------+------------+
| id | url                   | url_crc    |
+----+-----------------------+------------+
|  1 | http://www.mysql.com/ | 1558250469 |
+----+-----------------------+------------+
If you use this approach, you should not use SHA1( ) or MD5( ) hash functions. These
return very long strings, which waste a lot of space and result in slower compari-
sons. They are cryptographically strong functions designed to virtually eliminate col-
lisions, which is not your goal here. Simple hash functions can offer acceptable
collision rates with better performance.
If your table has many rows and CRC32( ) gives too many collisions, implement your
own 64-bit hash function. Make sure you use a function that returns an integer, not aIndexing Basics | 105
string. One way to implement a 64-bit hash function is to use just part of the value
returned by MD5( ). This is probably less efficient than writing your own routine as a
user-defined function (see “User-Defined Functions” on page 230), but it’ll do in a
pinch:
mysql> SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64;
+---------------------+
| HASH64              |
+---------------------+
| 9761173720318281581 |
+---------------------+
Maatkit (http://maatkit.sourceforge.net) includes a UDF that implements a Fowler/
Noll/Vo 64-bit hash, which is very fast.
Handling hash collisions. When you search for a value by its hash, you must also
include the literal value in your WHERE clause:
mysql> SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com")
    -> AND url="http://www.mysql.com";
The following query will not work correctly, because if another URL has the CRC32( )
value 1560514994, the query will return both rows:
mysql> SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com");
The probability of a hash collision grows much faster than you might think, due to
the so-called Birthday Paradox. CRC32( ) returns a 32-bit integer value, so the proba-
bility of a collision reaches 1% with as few as 93,000 values. To illustrate this, we
loaded all the words in /usr/share/dict/words into a table along with their CRC32( ) val-
ues, resulting in 98,569 rows. There is already one collision in this set of data! The
collision makes the following query return more than one row:
mysql> SELECT word, crc FROM words WHERE crc = CRC32('gnu');
+---------+------------+
| word    | crc        |
+---------+------------+
| codding | 1774765869 |
| gnu     | 1774765869 |
+---------+------------+
The correct query is as follows:
mysql> SELECT word, crc FROM words WHERE crc = CRC32('gnu') AND word = 'gnu';
+------+------------+
| word | crc        |
+------+------------+
| gnu  | 1774765869 |
+------+------------+
To avoid problems with collisions, you must specify both conditions in the WHERE
clause. If collisions aren’t a problem—for example, because you’re doing statistical

Answer 13

这是由于MYSQL内部优化语句时的处理原因， MYSQL会自动根据各表的索引情况（包括索引的记录数，唯一值的个数）来找出一个MYSQL它自己认为最优的执行计划，所有当表中一些数据记录有变化时，MYSQL会对同一个SQL语句产生不同的执行计划。

Answer 14

从昨天到今天数据并没有更改，只是出了问题后，我频繁的删除索引，测试语句，增加索引，测试语句，重启数据库，有时明明是同样的数据，同样的语句，同样的索引，explain出来的却不一样，难道优化器是随机的？

Answer 15

你的表的统计或者是没有及时更新，即在索引中的统计信息与实际数据不一致，而MYSQL会定期的来维护这个统计信息。当统计信息更新后，MYSQL的优化就会根据最新的统计信息来进行。

Answer 16

昨天重写的WEB程序的查询，用上了force index,
在测试机上的MYSQL上没有问题，可是部署到服务器上时，连force index的不管用了，

Answer 17

后来将服务器上的数据库换成和我测试机一样就OK乐，还有一个疑虑，
select * from DataSource d force index(idx_datasource_senid_t),Point p,Station s where d.senid = p.senid and p.station_id = s.id order by d.t desc limit 100 ;
select * from DataSource d force index(idx_datasource_senid_t),Point p,Station s where d.senid = p.senid and p.station_id = s.id order by d.t desc limit 300000,300100;这2条语句的查询时间简直是天壤之别，这个正常吗?难道后者也是ALL整个表吗？