索引及执行计划

一、索引作用

提供了类似于书中目录的作用,目的是为了优化查询

二、索引的种类(算法)

B树索引 #mysql中最常用的的索引，90%以上
Hash索引
R树
Full text
GIS

B树
B-tree 早期使用，现在不用了
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)，在b树上进行的优化。主要要明白这个
B*Tree
B+Tree和B-Tree有几点不同：
　　非叶子节点只存储键值信息
　　所有叶子节点之间都有一个链指针
　　数据记录都存放在叶子节点中

三、在功能上的分类

1.辅助索引(S)怎么构建B+树结构的?

(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

depa_id  name  age  gender
select  *  from  t1 where idepa_id10;
问题: 基于索引键做where查询,对于depa_id列是顺序IO,但是对于其他列的查询,可能是随机IO.

View Code

2 聚集索引

2.1前提
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)
2.2 聚集索引(S)怎么构建B+树结构的?
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点
2.3 聚集索引和辅助索引构成区别
聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的

3.辅助索引细分

1)普通的单列辅助索引
(2)联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询

联合索引：
a)select * from t1 where a= b= 
我们建立联合索引时：
alter table t1 add index idx_a_b(a,b);
alter table t1 add index idx_b_a(b,a);
注意：在这种情况下建索引时，需要考虑哪个列的唯一值更多，哪个放在索引左边
b)如果出现where条件中出现不等值条件
首先要调整语句，把=的放前面，把不等的放后面
desc select * from t_100w where k2="DEEF" and num<1000;
建立索引时也注意顺序：
alter table t_100w add index idx_2_n(k2,num);
c)如果查询中出现多子句
我们需要按照子句的执行顺序进行建立索引

View Code

3)唯一索引
索引列的值都是唯一的.

4.关于索引树的高度受什么影响

(1). 数据量级, 解决方法:分表,分库,分布式
(2). 索引列值过长 , 解决方法:前缀索引
(3). 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
1 2 3

5.索引的基本管理

5.0 准备
db01 [world]>desc city;
+-------------+----------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO  | PRI | NULL    | auto_increment |
| Name        | char(35) | NO  |    |        |                |
| CountryCode | char(3)  | NO  | MUL |        |                |
| District    | char(20) | NO  |    |        |                |
| Population  | int(11)  | NO  |    | 0      |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Field :列名字
key  :有没有索引,索引类型
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)
5.1创建索引
db01 [world]>alter table city add index idx_name(name);
                                       表                    索引名（列名）
db01 [world]>create index idx_name1 on city(name);
db01 [world]>show index from city;
![image](https://upload-images.jianshu.io/upload_images/16956686-8c8421524dca6291.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
注意:
以上操作不代表生产操作,我们不建议在一个列上建多个索引
同一个表中，索引名不能同名。
5.2删除索引:
db01 [world]>alter table city drop index idx_name1;
                                        表名                 索引名
5.3 覆盖索引(联合索引)
Master [world]>alter table city add index idx_co_po(countrycode,population);
5.4 前缀索引
db01 [world]>alter table city add index idx_di(district(5));
注意：数字列不能用作前缀索引。
5.5 唯一索引
db01 [world]>alter table city add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'
5.6 查看索引
show index from tblname;
show keys from tblname;
5.7 查看某列是不是适合做唯一索引
select population,count(id) from city group by population having count(id)>1 order by count(id) desc;
重复多则不适合做唯一索引

View Code

四、执行计划获取及分析

1.介绍

(1)获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
a. 全表扫描(应当尽量避免,因为性能低)
b. 索引扫描
c. 获取不到数据

2.执行计划获取

获取优化器选择后的执行计划

desc select * from t_100w where id=9000;

table:t_100w 查询的表
type:ALL 查询类型
possible_keys:NULL 可能走的索引
key:NULL    走的索引的名
key_len:NULL 应用索引的长度
rows:997261 查询结果集的长度
Extra:Using where 额外信息


extra: 
filesort ,文件排序.
结论: 
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

View Code

联合索引:

1. SELECT * FROM t1  WHERE a=    b=   
我们建立联合索引时:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);  
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);  
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.

2.  如果出现where 条件中出现不等值查询条件
DESC  SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF';
我们建索引时:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
语句书写时
DESC  SELECT * FROM t_100w WHERE  k2='DEEF'  AND  num <1000 ;
3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.

View Code

2.1 type类型

2.1.1 ALL：全表扫描，不走索引
不走索引的案例：(辅助索引)
(1)查询条件列，没有索引
例子：select * from t_100w where id=9000;
(2)查询条件出现以下语句
<>,!= 不等于
select * from city where countrycode <> "CHN";
not in (value1,value2,value3)
select * from city where countrycode not in ("CHN","USA");
like
select * from city where countrycode like "%CH%";
注意：对于聚集索引列，使用以上语句，依然会走索引

2.1.2 INDEX:全索引扫描。整个索引树都扫描一次
注意：这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。
如果回表查询的是无序的数据，那么效率更低。如果只查询索引列的值，那么使用索引也能提升效率。
案例：
(1)查询需要获取整个索引树种的值时
select countrycode from city;
(2)联合索引中，任何一个非最左列作为查询条件时(mysql版本不同，可能有的版本会走全索引扫描，有的不会)
idx_a_b_c(a,b,c)
select * from t1 where b
select * from t1 where c

2.1.3 RANGE:索引范围扫描
辅助索引:> < = >= <= <> like, in, or, 
主键：<> not in
例子
desc select * from city where id<5;
desc select * from city where countrycode like "CH%";
desc select * from city where countrycode in ("CHN","USA");
注意：1和2的例子中，可以享受到B+树的优势，但是3例子是不能享受的(B+树叶子节点存放隔壁的指针，有指向，例3享受不到)。
所以，我们可以把3号例子改写
desc select * from city where countrycode="CHN"
union all
select * from city where countrycode="USA";

2.1.4 ref：非唯一性索引，等值查询
desc select * from city where countrycode="CHN";

2.1.5 eq_ref:在多表连接时，连接条件使用了唯一索引(uk pk)
例：
desc select b.name,a.name from city as a join country on a.countrycode=b.code 
where a.population<100;
2.1.6 const：唯一索引的等值查询
desc select * from city where id=10;
2.1.7 
system：表只有一行：system表。这是const连接类型的特殊情况。
效率：按上面的介绍顺序从低到高

View Code

3.执行计划分析

3.1重点关注信息

table: city                              ---->查询操作的表    **
possible_keys: CountryCode,idx_co_po      ---->可能会走的索引  **
key: CountryCode   ---->真正走的索引    ***
type: ref   ---->索引类型        *****
Extra: Using index condition              ---->额外信息        *****

View Code

3.2 explain(desc)使用场景（面试题）

题目意思:  我们公司业务慢,请你从数据库的角度分析原因
mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1)show processlist;  获取到导致数据库hang的语句
2) explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3) 建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

View Code

3.3 案例

#案例：
优化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t_100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 701.743 seconds
    Minimum number of seconds to run all queries: 701.743 seconds
    Maximum number of seconds to run all queries: 701.743 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;

DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 


优化后:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 0.190 seconds
    Minimum number of seconds to run all queries: 0.190 seconds
    Maximum number of seconds to run all queries: 0.190 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

View Code

五、索引应用规范

1.(必须的)建表时一定要有主键，一般是个无关列

2.选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

View Code

3.(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段

排序操作会浪费很多时间。
where  A B C      ----》 A  B  C
in 
where A   group by B  order by C
A,B，C

如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

View Code

4.尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

5.限制索引的数目

索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

6.删除不再使用或者很少使用的索引(percona toolkit)

pt-duplicate-key-checker

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.大表加索引,要在业务不繁忙期间操作

8.尽量少在经常更新值的列上建索引

9.建索引原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

六、不走索引的情况(开发规范)

1.没有查询条件，或者查询条件没有建立索引

select * from tab;       全表扫描。
select  * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
对用户查看是非常痛苦的。
对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
（2）
select  * from  tab where name='zhangsan'          name列没有索引
改：
换成有索引的列作为查询条件
将name列建立索引

View Code

2.查询结果集是原表中的大部分数据，应该是25％以上

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引
select * from tab  where id>500000;
如果业务允许，可以使用limit控制。
怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

View Code

3.索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,，统计数据不真实
DML ?   --->锁冲突

View Code

4.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

View Code

5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id    | int(11)    | YES  |    | NULL    |      |
| name  | varchar(20) | YES  |    | NULL    |      |
| telnum | varchar(20) | YES  | MUL | NULL    |      |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

View Code

6.<> ，not in 不走索引（辅助索引）

EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union
EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

View Code

7.like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

View Code

posted @ 2019-11-18 21:36 徐大阅读(955) 评论(0) 编辑收藏举报

刷新页面返回顶部

索引及执行计划

一、索引作用

二、索引的种类(算法)

三、在功能上的分类

1.辅助索引(S)怎么构建B+树结构的?

2 聚集索引

3.辅助索引细分

4.关于索引树的高度受什么影响

5.索引的基本管理

四、执行计划获取及分析

1.介绍

2.执行计划获取

2.1 type类型

3.执行计划分析

五、索引应用规范

1.(必须的)建表时一定要有主键，一般是个无关列

2.选择唯一性索引

3.(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段

4.尽量使用前缀来索引

5.限制索引的数目

6.删除不再使用或者很少使用的索引(percona toolkit)

7.大表加索引,要在业务不繁忙期间操作

8.尽量少在经常更新值的列上建索引

9.建索引原则

六、不走索引的情况(开发规范)

1.没有查询条件，或者查询条件没有建立索引

2.查询结果集是原表中的大部分数据，应该是25％以上

3.索引本身失效，统计数据不真实

4.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

6.<> ，not in 不走索引（辅助索引）

7.like "%_" 百分号在最前面不走

公告