1.数据准备
(1).下载示例数据库

wget https://downloads.mysql.com/docs/sakila-db.tar.gz
tar -xvf sakila-db.tar.gz

3.索引优化_mysql

(2).执行

mysql> source /root/sakila-db/sakila-schema.sql
mysql> source /root/sakila-db/sakila-data.sql

2.where优化
(1).覆盖索引
覆盖索引是指查询语句从执行到返回结果均使用同一个索引，通俗来讲索引里包含了查询所需要的所有字段，不需要再去主索引去查询其它字段，这样可以有效减少回表。

(2).数据表准备

#原表
CREATE TABLE inventory (
  inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  film_id SMALLINT UNSIGNED NOT NULL,
  store_id TINYINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (inventory_id),
  KEY idx_fk_film_id (film_id),
  KEY idx_store_id_film_id (store_id,film_id),
  CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

inventory表索引情况。

3.索引优化_ci_02

#对照表
CREATE TABLE `inventory_1` (
  `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint(5) unsigned NOT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> insert into inventory_1 select * from inventory;

inventory表索引情况。

3.索引优化_mysql_03

(3).覆盖索引

mysql> select store_id,film_id from inventory_1 where store_id = 1;
mysql> explain select store_id,film_id from inventory_1 where store_id = 1;

3.索引优化_mysql_04


没有使用任何索引，使用的是where全表扫描。

mysql> select store_id,film_id from inventory where store_id = 1;
mysql> explain select store_id,film_id from inventory where store_id = 1;

3.索引优化_java_05


实际使用了idx_store_id_film_id索引。

(4).覆盖索引

mysql> select inventory_id,store_id,film_id from inventory where store_id = 1;
mysql> explain select inventory_id,store_id,film_id from inventory where store_id = 1;

3.索引优化_字段_06


加上inventory_id，也不需要回表的原因是，idx_store_id_film_id索引不仅包含store_id和film_id两个联合索引字段，还包含主键，可以进行回表操作。

(5).非覆盖索引

mysql> select inventory_id,store_id,film_id,last_update from inventory where store_id = 1;
mysql> explain select inventory_id,store_id,film_id,last_update from inventory where store_id = 1;

3.索引优化_java_07


因为last_update字段不在联合索引中，所以需要根据主键进行回表操作。

3.count查询慢
(1).简介
count函数是用来统计结果集中不为null的数据个数，首先存储引擎查询出结果集，然后server层逐个判断是否为null，不为null则加1。

(2).customer表的索引情况

mysql> show index from customer;

3.索引优化_字段_08

(3).count非索引字段

mysql> select count(first_name) from customer;
mysql> explain select count(first_name) from customer;

3.索引优化_mysql_09


可以看到，查询未使用覆盖索引，需要回表查询所有first_name数据，并逐一判断是否为null。

mysql> explain select count(first_name) from customer;

(4).count索引字段

mysql> select count(last_name) from customer;
mysql> explain select count(last_name) from customer;

3.索引优化_mysql_10


使用索引字段虽然不需要回表，但是依然需要逐一判断字段是否为空。

(5).count(1)
不需要取出数据，但需要判断1是否为null。

(6).count()
MySQL专门优化了count()，会直接返回主键索引树中的数据个数。

4.order by优化
(1).简介

mysql> select * from film where film_id > 80 order by title;
  1. MySQL首先根据where条件查询结果集
  2. 将结果集放入sort_buffer
  3. 对中间结果集按照排序字段排序
  4. 回表生成完整结果集

(2).中间结果集

  • 中间表比较小时，直接放入内存中，中间表大于sort_buffer_size时，放在硬盘中
  • 当列大于等于max_length_for_sort_data，生成全字段表，否则只生成主键和排序字段中间表，提高了排序效果，但是需要回表操作

(3).索引覆盖

  • order字段需要有索引
  • where字段在上述索引中
  • 输出字段均在上述索引中
mysql> select film_id,title from film where title like 'm%' order by title;
mysql>  explain select film_id,title from film where title like 'm%' order by title;

3.索引优化_字段_11

5.limit优化

mysql> select film_id,title,description from film order by title limit 900,10;
mysql> explain select film_id,title,description from film order by title limit 900,10;

先对全表进行排序，然后再取出符合条件的数据，所以效率低下。优化思路，先想办法走索引覆盖获取900-910条数据，然后回表查询行数据。

select f.film_id,f.title,f.description from film as f 
inner join(select film_id from film order by title limit 900,10) as m
on f.film_id = m.film_id;

6.有合适的索引不走
(1).简介
MySQL在选取索引时，会参考索引的基数cardinality，基数是估算的，不是精确的，反映这个字段有多少种取值，可以根据索引基数，判断索引性能的好坏，基数越大，效果越好。
可以使用在SQL语句最后加上force index强制使用某条索引，也可以使用analyze table重新统计索引信息。

(2).基数表

#创建只有一个字段的city_1表
mysql> CREATE TABLE city_1(city varchar(50) not null);

#将city表数据复制5次到city_1表
mysql> insert into city_1 select city from city;
mysql> insert into city_1 select city from city;
mysql> insert into city_1 select city from city;
mysql> insert into city_1 select city from city;
mysql> insert into city_1 select city from city;

#打乱city_1表顺序
mysql> update city_1 set city=(select city from city order by rand() limit 1);
#以city字段首字符创建索引
mysql> alter table city_1 add key(city(1));
mysql> alter table city_1 add key(city(2));
mysql> alter table city_1 add key(city(3));
mysql> alter table city_1 add key(city(4));
mysql> alter table city_1 add key(city(5));
mysql> alter table city_1 add key(city(6));
mysql> alter table city_1 add key(city(7));
mysql> alter table city_1 add key(city(8));
mysql> show index from city_1;

3.索引优化_字段_12


可以看到使用4个字符创建的索引的区分度最大，5-8个字符创建的索引效果和4个字符一样，但是却浪费更多的存储空间。

7.索引下推
(1).数据表准备

#对照表
CREATE TABLE `inventory_3` (
  `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint(5) unsigned NOT NULL,
  `store_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> insert into inventory_3 select * from inventory;

3.索引优化_mysql_13

(2).测试

mysql> select * from inventory_3 where store_id = 2 and film_id = 3;
mysql> explain select * from inventory_3 where store_id = 2 and film_id = 3;

3.索引优化_ci_14


store_id作为索引树上的第一排序字段，SQL语句先根据where条件筛选出4行数据，然后通过主键inventory_id回表，查询出4条数据的行数据，然后找film_id为3的数据。

(3).索引下推

mysql> select * from inventory_3 where store_id in (1,2) and film_id = 3;
mysql> explain select * from inventory_3 where store_id in (1,2) and film_id = 3;

3.索引优化_字段_15


3.索引优化_mysql_16


store_id作为索引树上的第一排序字段，SQL语句先根据store_id in (1,2)筛选出符合条件的数据，一共4581条。MySQL5.6之前会回表查询全部数据进行判断，而MySQL5.6之后，根据索引树的第二个字段film_id进行过滤，过滤之后只有5条可能的数据，然后回表查询数据进行判断。

8.索引失效场景
(1).列上做运算

explain select * from film where film_id + 1 = 100;

3.索引优化_字段_17

(2).模糊查询不用左而用右

explain select * from film where title like 'tt%';

3.索引优化_mysql_18

explain select * from film where title like '%tt';

3.索引优化_字段_19

(3).字符串与数字比较

explain select * from film where title = '1';

3.索引优化_java_20

explain select * from film where title = 1;

3.索引优化_java_21

(4).查询条件含有is null、is not null

explain select * from film where title is not null;

3.索引优化_java_22

(5).查询条件含有负向关键字not like，!=

explain select * from film where title not like 'tt%';

3.索引优化_字段_23

explain select * from film where title = '1';

3.索引优化_mysql_24