索引及执行计划

什么是索引

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。 2）让获取的数据更有目的性，从而提高数据库检索数据的性能。

注意：给指定的字段，排序，添加'目录'功能，索引并不是越多越好，也不是每个字段都必须加索引

MySQL中索引的类型

1)BTREE:B+树索引 2)HASH：HASH索引 3)FULLTEXT：全文索引 4)RTREE：R树索引

Btree算法

• Rtree

三路Btree

• 根节点
• ​枝节点
• ​叶节点

select * from tb1 where id=15;  精确查询：3次IO
select * from tb1 where id>10 and id<30; 范围查询：6次

• B+tree

select * from tb1 where id=15;  精确查询：3次
select * from tb1 where id>10 and id<30; 范围查询：4次



# B+tree和Btree比
1.优化了范围查询
2.在叶子节点上添加了相邻的指针

• B*tree

原理差不多，mysql 8.0也不一定用上了，所以了解即可

索引管理

索引建立在表的列上(字段)的。 在where后面的列建立索引才会加快查询速度。

如何创建索引

## 创建普通索引
 alter table 表名 add index idx_name(字段名`一般和索引名字后缀一样`);
 alter table student add index idx_name(name);
 
 ## 查看索引
 show index from 表名;
 show index from student;
 
 desc查看索引
 PRI：主键索引
 UNI：唯一键索引
 MUL：普通索引
 
 ## 删除索引(key和index一个意思)
 alter table student drop index idx_name;
 alter table student drop key idx_name;
 
 ## 添加主键索引
  create table test(id int,name varchar(10) not null);
  desc test;
  +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | YES  |     | NULL    |       |
 | name  | varchar(10) | NO   |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 alter table test add primary key(id);
 
 ## 删除主键索引
 alter table test drop primary key; 
 
 ## 添加唯一键索引
 alter table test add unique key uni_name(name);
 
 ## 报错原因
 alter table student add unique key uni_name(name);
 ERROR 1062 (23000): Duplicate entry 'wzj' for key 'uni_name'
 添加唯一键要求：该字段的数据不能有重复的
 
 ## 判断是否可以在name字段上创建唯一键
 ### 1.先统计该字段总共有多少行
 select count(name) from test;
 ### 2.再统计，去重后，该字段有多少行
 select count(distinct(name)) from test;
 ### 如果两个数值一样，则可以创建唯一键索引，如果两个数值不一样，则无法创建唯一键索引（有重复值）
 
 ## 删除唯一键索引
 alter table test drop index 索引名;
 alter table test drop index uni_name;

前缀索引

给某一字段数据内容特别长的列，创建前缀索引

## 普通前缀索引的创建
 alter table city add index idx_district(district(4)); ## 前四个前缀索引
 
 ## 唯一索引前缀索引创建
 alter table student2 add unique key uni_name(name(3));
 
 1.避免对大列（数据长的列）建索引
 2.如果有，就使用前缀索引

联合索引

将多个字段，做成一个索引

联合索引的查询顺序要和创建时的顺序一致，才可以提高效率
 A B C D E
 
 all_(B,A,C)
 
 select B,A,C  #正确
 A B C   # 错误
 A,C     # 正确
 B,C     # 正确
 A,C,B  # 错误
 
 使用场景：婚恋网站
 gender,salary,age
 
 ## 普通联合索引创建
 alter table student2 add index idx_all(gender,age,name);
 
 mysql[zls]> show create table student2;
 CREATE TABLE `student2` (
   `id` int(11) NOT NULL,
   `name` varchar(10) NOT NULL,
   `age` tinyint(4) DEFAULT NULL,
   `gender` enum('0','1') DEFAULT NULL,
   `status` enum('0','1') DEFAULT '1',
   KEY `idx_all` (`gender`,`age`,`name`)    # 主要看这边
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
 
 desc student2;
 +--------+---------------+------+-----+---------+-------+
 | Field  | Type          | Null | Key | Default | Extra |
 +--------+---------------+------+-----+---------+-------+
 | id     | int(11)       | YES  |     | NULL    |       |
 | name   | varchar(10)   | YES  |     | NULL    |       |
 | age    | tinyint(4)    | YES  |     | NULL    |       |
 | gender | enum('0','1') | YES  | MUL | NULL    |       |
 | status | enum('0','1') | YES  |     | 1       |       |
 +--------+---------------+------+-----+---------+-------+
 
 show index from student2;
 
 
 ## 主键联合索引
 alter table student2 add primary key (id,name);
 
 show create table student2;
 CREATE TABLE `student2` (
   `id` int(11) NOT NULL,
   `name` varchar(10) NOT NULL,
   `age` tinyint(4) DEFAULT NULL,
   `gender` enum('0','1') DEFAULT NULL,
   `status` enum('0','1') DEFAULT '1',   
   PRIMARY KEY (`id`,`name`)     # 主要看这边
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
 
 ## 唯一键联合索引
 alter table student2 add unique key uni_all(id,name);
 
 show index from student2;
 
 alter table student2 add unique key uni_all(id,name(3));
 
 mysql[zls]> show create table student2;
 CREATE TABLE `student2` (
   `id` int(11) NOT NULL,
   `name` varchar(10) NOT NULL,
   `age` tinyint(4) DEFAULT NULL,
   `gender` enum('0','1') DEFAULT NULL,
   `status` enum('0','1') DEFAULT '1',
   UNIQUE KEY `uni_all` (`id`,`name`(3))   # 主要看这边
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
 
 
 
 ### 索引删除
 普通索引
 唯一索引
 alter table 表名 drop index 索引名;
 
 主键索引
 alter table 表名 drop primary key;
 alter table 表名 drop primary;
 
 联合索引
 idx_all(id,name,age)
 
 
 alter table 表名 drop index idx_all;
 
 索引，无法直接修改，删除索引后，重新创建。

索引sql语句合集

################################# DDL
 ## 增
 ### 创建普通索引
 alter table 表名 add index 索引名(字段名);
 alter table stu add index idx_name(name);
 
 ### 建表语句创建
 create table stu1(
 id int auto_increment,
 name varchar(10) not null,
 age tinyint unsigned not null,
 
 ## 建表语句中的普通索引
 key idx_name (name),
 ## 建表语句中的主键索引
 primary key (id),
 ## 建表语句中的唯一索引
 unique key phone (phone)
 )
 
 ### 创建主键索引
 alter table 表名 add primary key(字段);
 alter table stu add primary key(id);
 
 ### 唯一键索引
 alter table 表名 add unique key 索引名(字段);
 alter table 表名 add unique key uni_age(age);
 
 
 ### 前缀索引
 # 唯一键
 alter table 表名 add unique key 索引名(字段名(字符个数))；
 # 普通索引
 alter table 表名 add index 索引名(字段名(字符个数));
 
 
 ### 联合索引
 # 主键
 alter table 表名 add primary key(字段名1,字段名2)；
 
 # 唯一键
 alter table 表名 add unique key 索引名(字段名1,字段名2);
 
 # 普通索引
 alter table 表名 add index 索引名(字段名1,字段名2);
 
 
 ## 删
 ### 删除主键索引
 alter table 表名 drop primary key;
 alter table 表名 drop primary;
 
 ### 删除普通索引和唯一索引
 alter table 表名 drop index 索引名;
 alter table 表名 drop key 索引名;
 
 ############################################## DQL
 ## 查
 desc 表名;
 show index from 表名;
 show create table 表名;

索引效率查询

## explain(执行计划)
 检测一个SQL语句的执行效率
 将explain加入在要执行的SQL语句之前即可
 
 mysql[zls]> explain select * from student2;
 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | SIMPLE      | student2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 1 row in set, 1 warning (0.02 sec)
 
 mysql[zls]> desc select * from student2;
 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | SIMPLE      | student2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 
 
 查看SQL语句的执行效率，主要看type列
 效率：
     - 全表扫描
     - 索引扫描
 
 ## 全表扫描
 ALL：企业中绝对不会出现
     1.业务确实要获取所有数据
     2.不走索引导致的全表扫描
         - 没索引
         - 索引创建有问题
         - 语句有问题
         - 索引损坏
 
 ## 索引扫描
 index：全索引扫描，创建索引的列，全部数据都查询出来了
  explain select id from student2;
 
 
 range：范围查询，一般来说，一条SQL语句，只要达到range级别，就OK
 mysql[world]> explain select * from student2 where id>2;
 
 ref：唯一索引的前缀扫描或者非唯一索引扫描（精确查询）
 mysql[world]> explain select * from city where countrycode='chn';
 
 eq_ref：连表查询，传统连接，join on
 explain select city.countrycode,country.name as 国家,city.name as 城市,city.population as 城市人口数 from city join country on city.country.countrycode=country.code and city.population<100;
 
 
 const、system：主键精确查询
 mysql[world]> explain select * from city where id=10;
 
 null：不进行表的扫描，没有这样的数据
 explain select * from city where id>100000000000000000000000000000000000000000000000;
 
 
 ### 范围查询优化
 mysql[world]> explain select * from city where countrycode='CHN' or countrycode='USA';
 mysql[world]> explain select * from city where countrycode in ('CHN','USA');
 
 ### 联合查询优化，or精确范围
 explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

索引建立的原则

索引的创建，是为了提示查询的效率，需要知道在哪些字段上创建索引

索引不是每一个字段都要创建，也不是越多越好

根据用户的喜好，被查询的越多的字段，要创建索引

1.索引首要选择，唯一键索引

判断是否可以创建唯一键索引

select count(需要创建唯一键索引的字段)from表；

select count(distinct(需要创建唯一键索引的字段)) from 表；

2.其次，如果无法创建唯一索引，重复值比较多，创建联合索引

经常需要order by 、grep by 、distinct和union等操作的字段，排序操作会浪费很多时间。

如果为其建立索引，可以有效地避免排序操作。

3.为经常作为查询条件的字段，创建普通索引

经常查询

列值重复值少的

4.尽量能使用前缀索引，就用前缀索引

减少创建索引的排序时间

增加查询的效率

5.索引的数量不是越多越好

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得浪费时间。

6.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式半被改变后，原有的一些索引可能不i在需要。

数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

不走索引的原因

数据库表查询效率低，导致网站加载速度慢，如何解决？

在企业中，开启MySQL的慢日志(慢查询日志)

查看，哪一条SQL语句执行的速度慢，取出SQL语句，使用explain查看该SQL语句的否走索引

没有走索引

explain select * from student;
 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
 |  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
 ########################## 没有走索引

1.全表扫描，使用select * 查询没有加条件

使用limit来优化
 explain select * from city where population>100 limit 60;

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

explain select * from city where population>100 limit 60;

3.条件本身做运算

## 不走索引
 explain select * from city where id-1=9;   # 字符串做运算不走索引
 explain select * from city where id=10-9;  # 运算结果走索引

4.隐式转换导致索引失效

root@localhost:world>create table test(id int,name varchar(10),phone char(11));
 
 root@localhost:world>alter table test add index idx_phone(phone);
 
 root@localhost:world>desc test;
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | YES  |     | NULL    |       |
 | name  | varchar(10) | YES  |     | NULL    |       |
 | phone | char(11)    | YES  | MUL | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
 
 ## 走索引
 explain select * from test where phone='123';  ## 效率高
 
 ## 不走索引
 explain select * from test where phone=111; ## 效率低
 
 优化方案：
     1.查看字段的创建数据类型
     2.告诉开发，在查询的时候，一定要按照字段的数据类型来查询
     3.如果是字符串，就加引号
     4.如果是整形，就不加引号

5.不等于>< 或者 not in

使用不等于><或者Not in会影响到结果集
 优化方案：
    - 使用limit来优化
    - 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成union

6.使用like模糊查询%在前面

explain select * from city where countrycode like '%HN';
 
 优化方案：
     - 尽量不要使用%在前面的SQL语句，尽量将%放在后面
     - 那就不要使用MySQL，使用搜索式引擎式数据库：elasticsearch

7.使用联合索引查询数据时

不走索引，要根据联合索引创建的顺序来查询数据
 创建顺序：ABC
 查询顺序：ABC  AB AC BC

8.索引损坏或者失效

删了重建