浅谈MySQL中的高性能索引

InnoDB中创建自定义哈希索引

在B-Tree上自定义hash索引，还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。
如果我们需要存储大量的url，并根据url进行搜索查找，因为url很大的字符串会导致查询语句很慢。

1.删除原来URL列上的索引，而新增一个被索引的url_crc的列，同时使用crc32做哈希；
  此时是通过整数而不是url字符串进行索引比较，因此性能会非常高。
SELECT ID FROM url WHERE url_crc=CRC32("http://www.baidu.com") and url="http://www.baidu.com";

2.同时，可以选择使用触发器进行维护。

CREATE TABLE url(
	id int unsigned not null auto_increment,
	url varchar(255),
	url_crc int unsigned not null default 0,
	primary key(id)
);

DELIMITER //
CREATE TRIGGER url_crc_ins BEFORE INSERT ON url FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

CREATE TRIGGER url_crc_upd BEFORE UPDATE ON url FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

DELIMITER ;

3.使用hash索引时，要注意处理hash冲突。

关于多列索引的索引顺序问题

查询：SELECT * FROM pay WHERE staff_id=2 AND customer_id=584;
那么应该建立(staff_id,customer_id)还是(customer_id,staff_id)索引呢？

1.考虑该特定查询。
SELECT SUM(staff_id=2), SUM(customer_id=584) FROM pay；
结果：7992  30
从该结果看应该是：(customer_id,staff_id)

2.经验法则方法
SELECT COUNT(DISTINCT staff_id) / COUNT(*) staff，
	   COUNT(DISTINCT customer_id) / COUNT(*) customer，
	   COUNT(*) FROM pay;

结果：0.0001   0.0373  16049
从改结果看应该也是(customer_id,staff_id)

3.正常情况下，如果我们从pt-query-digest工具中提取最差查询，然后按照该特定查询进行优化，效果会不错。

4.关于覆盖索引，在SELECT * FROM TABLE WHERE语句中不能直接使用覆盖索引，所有有时候会利用“延迟关联”拿到主键。
  但是，这种方法不一定最优。有时候col1过滤性就很好的时候，可以直接不用这个多列索引。

5.需要满足索引最左前缀时，IN是“多个等值条件”，并不是范围查询，是可以满足左前缀的。