建立索引的几大原则
最左前缀匹配原则，
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整（参考原则2）。但是mysql查询优化器可能通过优化调整顺序从而使用索引，但是写sql语句时还是按照此原则；= 和 in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
?
注意：
前缀索引在Oder by 和 Group by操作的时候无法使用；
hash索引不适用于范围查询，例如<，>，<=，>=等操作。如果使用Memory/Heap引擎并且where条件中不使用"="进行索引列，那么不会用到索引。Memory/Heap引擎只有在"="条件下才会使用索引；
mysql> show create table rental\G *************************** 1. row *************************** Table: rental Create Table: CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime DEFAULT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`), KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`), CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> alter table rental drop index rental_date; Query OK, 16044 rows affected (0.92 sec) Records: 16044 Duplicates: 0 Warnings: 0
ql> alter table rental add index idx_rental_date(rental_date ,inventory_id,customer_id); Query OK, 16044 rows affected (0.48 sec) Records: 16044 Duplicates: 0 Warnings: 0
//匹配全值，对索引中的所有列都执行具体值，即是对索引中的所有列都有等值匹配的条件。 mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343\G（等值查询的话查询条件可以乱序） *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref //使用一般索引，所以此时是ref，使用唯一性索引或者primary key进行查询时是const possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 13 ref: const,const,const //显示哪些字段或者常量用来和key配合从表中查询记录出来 rows: 1 Extra: 1 row in set (0.00 sec)
mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.65 sec) Records: 16044 Duplicates: 0 Warnings: 0
mysql> alter table rental add unique index idx_rental_date(rental_date ,inventory_id,customer_id); Query OK, 16044 rows affected (0.56 sec) Records: 16044 Duplicates: 0 Warnings: 0
mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343\G *************************** 1. row ************************* |