mysql索引建立和优化_MySQL

建立索引的几大原则

最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、 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 *************************** 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 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 =300 注意：将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)

不能使用索引的情况：
以%开头的like查询不能够利用B-TREE索引，一般推荐使用全文索引来解决全文检索问题（模糊查询）；
数据类型出现隐式转换的时候也不会使用索引；特别是字符串常量一定要使用引号引起来，这样才会使用索引。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 .不满足最左条件不会使用索引。 4.用or分割的条件，如果or前的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。因为后面的列的查询要使用全表扫描，没有必要再使用前面的列进行一次索引扫描。 mysql> explain select * from payment where customer_id = 203 or amount = 3.96\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALL possible_keys: idx_fk_customer_id key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)

mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 2 | | Handler_read_key | 2 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 33123 | +-----------------------+-------+ 6 rows in set (0.00 sec)
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引不经常使用。Handler_read_rnd_next表示在数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或者写入查询没有用到索引。

慢查询优化基本步骤

先运行看看是否真的很慢，注意设置SQL_NO_CACHE；
where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高；
explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）；
order by limit 形式的sql语句让排序的表优先查；
了解业务方使用场景；
加索引时参照建索引的几大原则；
观察结果，不符合预期继续从1分析；