数据库索引篇

索引是帮助MySQL高效获取数据的排好序的数据结构

B+Tree(B-Tree变种)
        非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
        叶子节点包含所有索引字段
        叶子节点用指针连接，提高区间访问的性能

Explain工具介绍

        使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL；

explain中的列

        接下来我们将展示 explain 中每个列的信息。

1. id列

        id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

i        d列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行；

2. select_type列

select_type 表示对应行是简单还是复杂的查询。

1）simple：简单查询。查询不包含子查询和union

2）primary：复杂查询中最外层的 select

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含 义);

3. table列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查

询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

4. type列

这一列表示 关联类型或访问类型 ，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为： system > const > eq_ref > ref > range > index > ALL

一般来说， 得保证查询达到range级别，最好达到ref

NULL ：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可

以单独查找索引来完成，不需要在执行时访问表

explain select min(id) from film;

const, system ：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于

primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。 system是

const的特例，表里只有一条元组匹配时为system;

explain extended select * from (select * from film where id = 1) tmp;

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from film_actor left join film on film_actor.film_id = film.id;

sl>

ref ：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

explain select * from film where name = 'film1';

2.关联表查询,idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.fi
lm_id;

range ：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

explain select * from actor where id > 1;

index ：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接

对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

 explain select * from film;

ALL ：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from actor;

5. possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引

对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提

高查询性能，然后用 explain 查看效果。

6. key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7. key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通

过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

1 mysql> explain select * from film_actor where film_id = 2 ;

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中， n均代表字符数，而不是字节数， 如果是utf-8，一个数字

或字母占1个字节，一个汉字占3个字节

char(n)：如果存汉字长度就是 3n 字节

varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为

varchar是变长字符串

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型

date：3字节 timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索

引。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

9. rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

1） Using index ：使用覆盖索引

覆盖索引定义 ：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中

获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个

查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值 ;

explain select film_id from film_actor where film_id = 1;

2） Using where ：使用 where 语句来处理结果，并且查询的列未被索引覆盖

ex

explain select * from actor where name = 'a';

3) Using filesort ：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一

般也是要考虑使用索引来优化的。

1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录;

explain select * from actor order by name;

2. film.name建立了idx_name索引,此时查询时extra是using index ;

explain select * from film order by name;

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',
23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

4.存储引擎不能使用索引中范围条件右边的列

 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manage
r';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manage
r';

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

 EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position
='manager';

6.mysql在使用不等于（！=或者<>）， not in ， not exists 的时候无法使用索引会导致全表扫描

< 小于、 > 大于、 <= 、 >= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引;

 EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

7.is null,is not null 一般情况下也无法使用索引

n e

 EXPLAIN SELECT * FROM employees WHERE name is null

8.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei

9.字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

11.范围查询优化

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是

由于单次数据量查询过大导致优化器最终选择不走索引

优化方法：可以将大的范围拆分成多个小范围

ded select * fr(

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;

CREATE TABLE `employees` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
 INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('Lucy',23,'dev',NOW());
DROP PROCEDURE IF EXISTS insert_emp;
DELIMITER ;;
CREATE PROCEDURE insert_emp()
BEGIN
DECLARE i INT;
SET i=1;
WHILE(i<=100000)DO
INSERT INTO employees(NAME,age,POSITION) VALUES(CONCAT('zhuge',i),i,'dev');
SET i=i+1;
END WHILE;
END;;
DELIMITER ;
CALL insert_emp();

1、联合索引第一个字段用范围不会走索引  

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

结论：联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不 如就全表扫描 。

2、强制走索引

 EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 A
ND position ='manager';

结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表

扫描高，因为回表效率不高

3、覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manag
er';

4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

 EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position
='manager';

5、like KK% 一般情况都会走索引

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

  EXPLAIN SELECT * FROM empl

索引设计原则

1、代码先行，索引后上

不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？

这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立

索引。

2、联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的

where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原

则。

3、不要在小基数字段上建立索引

索引 基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，

其值不是男就是女，那么该字段的基数就是2。

如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没

法进行快速的二分查找，那用索引就没有太大的意义了。

一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查

找的优势来。

4、长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会

比较小，此时你在搜索的时候性能也会比较好一点。

当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立

索引，哪怕多占用一些磁盘空间也是有必要的。

对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个

字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY

index(name(20),age,position)。

此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name

字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来

完整的name字段值进行比对。

但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排

序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

5、where与order by冲突时优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到

底是让where去用上索引，还是让order by用上索引? 一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。

因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可

能会小很多。

6、基于慢sql查询做优化

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

常见sql深入优化

Order by与Group by优化

1、MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL 扫描索引本身完成排序 。index

效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用 索引最左前列 。

2) 使用where子句与order by子句 条件列组合满足索引最左前 列。

3、尽量在 索引列 上完成排序，遵循 索引建立（索引创建的顺序） 时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先 排序后分组 ，遵照 索引创建顺序 的最左前缀法则。对于group

by的优化如果不需要排序的可以加上 order by null禁止排序 。注意，where高于having，能写在where中的限定条件就不要去having限定了。

分页查询优化

CREATE TABLE `employees` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

扫描整个索引并查找到没索引 的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引 。 知道不走索引的原因，那么怎么优化呢？ 其实关键是 让排序时返回的字段尽可能少 ，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录；

mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.id = ed.id;

CREATE TABLE `t1` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
 `a` INT(11) DEFAULT NULL,
 `b` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_a` (`a`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE t2 LIKE t1;
DROP PROCEDURE IF EXISTS insert_t1;
DELIMITER ;;
CREATE PROCEDURE insert_t1()
BEGIN
DECLARE i INT;
SET i=1;
WHILE(i<=10000)DO
INSERT INTO t1(a,b) VALUES(i,i);
 SET i=i+1;
END WHILE;
END;;
DELIMITER ;
CALL insert_t1();
DROP PROCEDURE IF EXISTS insert_t2;
DELIMITER ;;
CREATE PROCEDURE insert_t2()
BEGIN
DECLARE i INT;
SET i=1;
WHILE(i<=100)DO
INSERT INTO t2(a,b) VALUES(i,i);
SET i=i+1;
END WHILE;
END;;
DELIMITER ;
CALL insert_t2();

mysql的表关联常见有两种算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为 驱动表 ）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（ 被驱动 表 ）里取出满足条件的行，然后取出两张表的结果合集。

2、 基于块的嵌套循环连接 Block Nested-Loop Join( BNL )算法

把 驱动表 的数据读入到 join_buffer 中，然后扫描 被驱动表 ，把 被驱动表 每一行取出来跟 join_buffer 中的数据做对比。

in和exsits优化

原则： 小表驱动大表 ，即小的数据集驱动大的数据集

in： 当B表的数据集小于A表的数据集时，in优于exists

 select * from A where id in (select id from B)
for(select id from B){
 select * from A where A.id = B.id}

exists： 当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（

true或false）来决定主查询的数据是否保留。

 select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
 select * from B where B.id = A.id
}

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会

忽略SELECT清单,因此没有区别

2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

count(*)查询优化

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;

 mysql> EXPLAIN select count(1) from employees;
 mysql> EXPLAIN select count(id) from employees;
 mysql> EXPLAIN select count(name) from employees;
 mysql> EXPLAIN select count(*) from employees;

常见优化方法

1、查询mysql自己维护的总行数

对于 myisam存储引擎 的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被

mysql存储在磁盘上，查询不需要计算 对于 innodb存储引擎 的表mysql不会存储表的总记录行数(因为有MVCC机制，后面会讲)，查询count需要实时计算

2、show table status

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

3、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

4、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作