mysql 索引建立和优化

1. 最左前缀匹配原则，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语句时还是按照此原则；
2. = 和 in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；
3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意：

1. 前缀索引在Oder by 和 Group  by操作的时候无法使用；
2. 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 ***************************            id: 1   select_type: SIMPLE         table: rental          type: const        //使用唯一性索引或者primary key进行查询时是const possible_keys: idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id           key: idx_rental_date       key_len: 13           ref: const,const,const          rows: 1         Extra: 1 row in set (0.00 sec)
注意：等号或者in可以乱序 mysql> explain select * from rental where customer_id = 5 and rental_date = ‘2006-05-30 10:00:00‘\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: rental          type: ref possible_keys: idx_fk_customer_id,idx_rental_date           key: idx_rental_date       key_len: 10           ref: const,const          rows: 1         Extra: 1 row in set (0.01 sec) mysql> explain select * from rental where rental_date = ‘2006-05-30 10:00:00‘ and customer_id = 5\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: rental          type: ref possible_keys: idx_fk_customer_id,idx_rental_date           key: idx_rental_date       key_len: 10           ref: const,const          rows: 1         Extra: 1 row in set (0.00 sec)

//匹配值的范围查询，对索引的值能够进行范围查询 mysql> explain select * from rental where customer_id >=373 and customer_id <= 400\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: rental          type: range possible_keys: idx_fk_customer_id           key: idx_fk_customer_id       key_len: 2           ref: NULL          rows: 745         Extra: Using where     //Using where表示优化器除了根据索引来加速访问之外，还根据索引回表查询数据。 1 row in set (0.00 sec)
Using where 
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. 

//匹配最左匹配，仅仅使用索引中的最左边列进行查找。

mysql> show create table payment\G *************************** 1. row ***************************        Table: payment Create Table: CREATE TABLE `payment` (   `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,   `customer_id` smallint(5) unsigned NOT NULL,   `staff_id` tinyint(3) unsigned NOT NULL,   `rental_id` int(11) DEFAULT NULL,   `amount` decimal(5,2) NOT NULL,   `payment_date` datetime NOT NULL,   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`payment_id`),   KEY `idx_fk_staff_id` (`staff_id`),   KEY `idx_fk_customer_id` (`customer_id`),   KEY `fk_payment_rental` (`rental_id`),   CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,   CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,   CONSTRAINT `fk_payment_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 payment add index idx_payment_date(payment_date,amount,last_update); Query OK, 16049 rows affected (2.85 sec) Records: 16049  Duplicates: 0  Warnings: 0
mysql> explain select * from payment where payment_date = ‘2006-02-14 15:16:03‘ and last_update=‘2006-02-15 22:12:32‘\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: payment          type: ref possible_keys: idx_payment_date           key: idx_payment_date       key_len: 8           ref: const          rows: 182         Extra: Using where 1 row in set (0.00 sec)
mysql> explain select * from payment where amount = 3 and last_update=‘2006-02-15 22:12:32‘\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: payment          type: ALL    //不是使用最左匹配，全表扫描 possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 16470         Extra: Using where 1 row in set (0.00 sec)
//仅仅对索引进行查询，当查询的字段在索引的字段中时，查询的效率更高。不必回表数据。 mysql> explain select last_update from payment where payment_date = ‘2006-02-14 15:16:03‘ and amount=3.98\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: payment          type: ref possible_keys: idx_payment_date           key: idx_payment_date       key_len: 11           ref: const,const          rows: 8         Extra: Using index    //using index表示直接通过访问索引就足够获取所需要的数据，无需通过索引回表，using index也就是常说的覆盖索引扫描。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能够提高效率。 1 row in set (0.04 sec)
//匹配列前缀 mysql> show create table film_text\G *************************** 1. row ***************************        Table: film_text Create Table: CREATE TABLE `film_text` (   `film_id` smallint(6) NOT NULL,   `title` varchar(255) NOT NULL,   `description` text,   PRIMARY KEY (`film_id`),   FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> create index idx_title_desc_part on film_text(title(10),description(20)); Query OK, 1000 rows affected (0.40 sec) Records: 1000  Duplicates: 0  Warnings: 0
mysql> explain select title from film_text where title like ‘AFRICAN%‘\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: film_text          type: range possible_keys: idx_title_desc_part,idx_title_description           key: idx_title_desc_part    //使用列前缀匹配，不适用全局索引idx_title_description       key_len: 32           ref: NULL          rows: 1         Extra: Using where 1 row in set (0.16 sec)
//实现索引匹配是部分精确，而其他部分进行范围匹配。 mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.86 sec) Records: 16044  Duplicates: 0  Warnings: 0 mysql> alter table rental add index idx_rental_date(rental_date,customer_id,inventory_id); Query OK, 16044 rows affected (1.43 sec) Records: 16044  Duplicates: 0  Warnings: 0 mysql> explain select inventory_id from rental where rental_date = ‘2006-02-14 15:16:03‘ and customer_id >=300 and customer_id <=400\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: rental          type: range possible_keys: idx_fk_customer_id,idx_rental_date           key: idx_rental_date       key_len: 10           ref: NULL          rows: 24         Extra: Using where; Using index 1 row in set (0.00 sec) 执行过程是先使用索引的首字段rental_date将符合rental_date = ‘2006-02-14 15:16:03‘的索引过滤，通过IO取出数据（回表，因为还要进行customer_id条件进行过滤），然后通过customer_id>=300 <=400过滤记录。
注意：将range放在前面mysql查询优化器也会将语句优化为可以使用索引的查询。 mysql> explain select * from rental where customer_id > 5 and rental_date = ‘2006-05-30 10:00:00‘\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: rental          type: range possible_keys: idx_fk_customer_id,idx_rental_date           key: idx_rental_date       key_len: 10           ref: NULL          rows: 1         Extra: Using where 1 row in set (0.00 sec)

//如果列名是索引，那么使用column_name is null 就会使用索引。 mysql> show create table payment\G *************************** 1. row ***************************        Table: payment Create Table: CREATE TABLE `payment` (   `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,   `customer_id` smallint(5) unsigned NOT NULL,   `staff_id` tinyint(3) unsigned NOT NULL,   `rental_id` int(11) DEFAULT NULL,   `amount` decimal(5,2) NOT NULL,   `payment_date` datetime NOT NULL,   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`payment_id`),   KEY `idx_fk_staff_id` (`staff_id`),   KEY `idx_fk_customer_id` (`customer_id`),   KEY `fk_payment_rental` (`rental_id`),   KEY `idx_payment_date` (`payment_date`,`amount`,`last_update`),   CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,   CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,   CONSTRAINT `fk_payment_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> explain select * from payment where rental_id is null\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: payment          type: ref possible_keys: fk_payment_rental           key: fk_payment_rental       key_len: 5           ref: const          rows: 5         Extra: Using where 1 row in set (0.00 sec)

不能使用索引的情况：

1. 以%开头的like查询不能够利用B-TREE索引，一般推荐使用全文索引来解决全文检索问题（模糊查询）；
2. 数据类型出现隐式转换的时候也不会使用索引；特别是字符串常量一定要使用引号引起来，这样才会使用索引。mysql默认会把输入的常量进行转换之后才会进行检索。

mysql> show create table actor\G *************************** 1. row ***************************        Table: actor Create Table: CREATE TABLE `actor` (   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,   `first_name` varchar(45) NOT NULL,   `last_name` varchar(45) NOT NULL,   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`actor_id`),   KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = 1\G    //没有使用引号将常量引起来 *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: actor          type: ALL possible_keys: idx_actor_last_name           key: NULL       key_len: NULL           ref: NULL          rows: 200         Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = ‘1‘\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: actor          type: ref possible_keys: idx_actor_last_name           key: idx_actor_last_name       key_len: 137           ref: const          rows: 1         Extra: Using where 1 row in set (0.00 sec)
3 .不满足最左条件不会使用索引。

• < 上一篇

  php mysql注入攻击解决方案

• 下一篇 >

  汽车之家一道SQL 面试题，大家闲来无事都来敲一敲