前言：如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车！

一、索引介绍：

1.1索引的作用：

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

1.2索引的种类（算法）

B树索引  (B-tree   B+tree    B*tree)

B*tree 在原有B树的基础上，增加了相邻叶子节点和枝节点的互相指向的指针

Hash索引
R树
Full text
GIS

B tree 算法原理图:

image.png
image.png

二、索引分辅助索引和聚集索引

2.1辅助索引（s）怎么构建B树结构？

1）辅助索引是基于表的列进行生成的。
2）取出索引列的所有值（取出所有键值） 索引又被称为键值
3）进行所有键值的排序
4）将所有的键值按顺序落到Btree索引的叶子节点上
5）进而生成枝节点和根节点
6）叶子节点除了存储键值之外，还存储相邻叶子节点的指针和指向原表数据的指针

聚集索引的引出：

基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.辅助索引，可以查到匹配列条件列在磁盘的位置（页码），但是万一磁盘上的位置是没有顺序的呢？

2.2聚集索引（c）怎么构建B树结构的？ （效率最高）

前提：

(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

1）建表时有主键列（id）
2）表中进行数据存储，会按照id列的顺序，有序的存储一行一行的数据到数据页上（这            个动作叫做聚集索引组织表）
3）表中的数据页被作为聚集索引的叶子节点
4）把叶子节点的主键值生成上层的枝节点和根节点

2.3聚集索引和辅助索引构成区别

1）聚集索引只能有一个,非空唯一,一般是主键
2）辅助索引,可以有多个,是配合聚集索引使用的
3）聚集索引叶子节点,就是磁盘的数据行存储的数据页
4）MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
5）辅助索引,只会提取索引键值,进行自动排序生成B树结构

2.4辅助索引细分：

单列的辅助索引
联合多列的辅助索引
唯一索引

2.5关于索引树的高度受什么影响？

1.数据行                           解决方法:分表,分库,分布式
2.索引列值长度                     解决方法:前缀索引
3.数据类型 （char、varchar）      解决方案:可变字符串使用varchar
4.enum的使用                       可以优化索引高度，能用则用

三、索引语句实践：

1.建立普通索引

alter table 表名 add index 索引名（索引列）;
alter table t100w add index idx_ke(k2);

2.建立唯一索引

2.1建立唯一索引是有条件的，即该列没有重复值。

2.2检查此列是否可做为唯一索引的方法：

2.2.1通过去重对别前后行数

去重前：

mysql> select count(k1) from t100w;
+-----------+
| count(k1) |
+-----------+
|   1000000 |        <-----k1列有一百万行数据
+-----------+
1 row in set (0.33 sec)

去重后

mysql> select count(distinct(k1)) from t100w;
+---------------------+
| count(distinct(k1)) |
+---------------------+
|                3721 |       <----------k1列仅剩3721行数据
+---------------------+
1 row in set (0.75 sec)

由此可见，k1列不能创建唯一索引

2.2.2直接对该列建立唯一索引（成则能，不成则不能）

alter table t100w add unique idenx idx_k(k1);

所以创建唯一索引的语句为：

alter table 表名 add unique index 索引名（索引列）;
alter table t100w add unique index idx_k1(k1);

3.建立前缀索引

alter table 表名 add index 索引名(索引列(前缀个数));
alter table t100w add index idx_name(name(5));

4.建立联合索引

alter table 表名 add index 索引名(索引列，索引列);
alter table city add index idx_co_po(countrycode,population);

5.查询索引的三种方法：

5.1 desc

desc 表名；
desc t100w;

5.2show

show index from 库.表；
show index from oldboy.t100w;

5.3数列显示

show index from oldboy.t100w\G
注意，数列显示，皆为不需要加分号，否则结尾会提示error

6.删除索引两种方法

6.1第一种

alter table 表名 drop index 索引名;
alter table t100w drop index idx_k1;

6.2第二种

drop index 索引名 from table 表名;
drop index idx_k1 from table t100w;

接下来，做一个模拟查询（有索引查询速度VS无索引查询速度）

1.环境准备;

create databases oldboy;   创建一个oldboy库
create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);    建表

delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;

call rand_data(1000000); 插入一百万条数据）那就得等一会了，电脑会卡住，可能五分钟，可能二十分钟，甚至可能一小时！

如果你好奇插入了多少数据了，那么另开一个窗口，
select count(*) from oldboy.t100w；

命令行查询，模仿一百人同时查询KLpq(数据中有的随机数据)，执行两千次！
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t100w where k2='KLpq'" engine=innodb \
--number-of-queries=2000 -uroot -p密码 -verbose

根据上述学到的语句，创建一个普通索引，再执行模拟一百人查询两千次，比较两次时间，天壤地鳖！

四、执行计划:

4.1作用：

上线新的查询语句之前，进行提前预估语句的性能。 在出现性能问题时，可以找到合理的解决思路。

4.2执行计划的获取

desc + 语句
explain + 语句
mysql> desc select * from oldboy.t100w where k2='EF12';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | idx_ke        | idx_ke | 17      | const |  252 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4.3执行计划关键信息的参数说明：

语句结尾加  \G 即可变成列的格式：（注意，加\G结尾不用分号
mysql> desc select * from oldboy.t100w where k2='EF12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t100w           查询的表名
   partitions: NULL
         type: ref             索引的应用类型（级别）
possible_keys: idx_ke          可能会使用到的索引
          key: idx_ke          实际运用的索引
      key_len: 17              联合索引覆盖长度，越长越好
          ref: const
         rows: 252             应用这个执行计划，查询的行数（越少越好）
     filtered: 100.00
        Extra: NULL            额外的信息
1 row in set, 1 warning (0.00 sec)

4.4索引级别详细介绍： (工作中索引级别最少达到range)

所有级别如下
注:       从上到下，性能是越来越好，即NULL性能是最好的。
          可以在执行语句前加desc ,即查看语句的执行的情况（不执行）
ALL      （全表扫描，不走索引，辅助索引
          1.没有索引，
          2.你直接查看了*，所以不用走索引 select * from world.city;
          3.匹配的条件不是索引列)        select * from t100w k2='qq';   k2列不是索引列    
          4.模糊匹配，两边都有%不走索引   select * from t100w where k2 like '%xt%';
          5.条件是不等于的               select * from t100w where k2 != aaa;
          6.注意，条件是不等于的，但是出现在主键列时，将自动转换为range
          ）
index     （全索引扫描
           desc select k2 from 100w;   k2列是索引列
           ）
range     （索引范围扫描 辅助索引 即出现：< > >= <= like 
             in 和 or 能不出现就不要出现。
             当=！在主键列出现，也将自动转换为range
             1.mysql> desc select * from world.city where countrycode like 'C%'
             2.mysql> desc select * from world.city where id!=3000;
             3.mysql> desc select * from world.city where id>3000;
             4.mysql> desc select * from world.city where countrycode in ('CHN','USA');
               改写为：效率更高
               desc
               select * from world.city where countrycode='CHN'
               union all 
               select * from world.city where countrycode='USA';
             )
ref         （辅助索引等职查询
             desc select * from city where countrycode='CHN';
             )
eq_ref      （再多表连接查询的on条件是主键或唯一键
             mysql> desc select 
             a.name,
             b.name ,b.surfacearea 
             from city as a 
             join country as b 
             on a.countrycode=b.code   即这两个关联列是主键或者唯一键
             where a.population <100;
             ）
const,system    （主键或唯一键值等值查询
                 select * from world.city where id=5;
                 )
NULL             就是你要查询的数据不存在。数据库就不会查询，弃用数据库。。。。

4.5关于 Extra（额外信息）

mysql> mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

当额外信息出现using filesort的时候，说明你的索引不合理或者语句不合理了。
举例：聚集索引已经根据主键或唯一值排好序，而你又通过order by 再次排序，那么就会影响效率，
解决：当where条件和order by 同时且经常一起出现查询同数据时，需要添加联合索引。

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

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

mysql> explain select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4188 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

mysql> show processlist;
+-----+------+----------------+--------------------+---------+------+----------+------------------+
| Id  | User | Host           | db                 | Command | Time | State    | Info             |
+-----+------+----------------+--------------------+---------+------+----------+------------------+
|   4 | root | 10.0.0.1:62239 | mysql              | Sleep   | 8668 |          | NULL             |
|   5 | root | 10.0.0.1:62261 | mysql              | Sleep   | 8668 |          | NULL             |
|   6 | root | 10.0.0.1:62262 | information_schema | Sleep   | 8668 |          | NULL             |
| 109 | root | localhost      | NULL               | Query   |    0 | starting | show processlist |
+-----+------+----------------+--------------------+---------+------+----------+------------------+
4 rows in set (0.00 sec)
一般看time列，时间就的就是罪魁祸首


（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,开启slowlog日志，分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

五、索引应用规范：

5.1建立索引的原则：

1.建表时一定要有主键，一般是个无关列。

2.选择唯一性的索引。唯一性索引的值是唯一的，可以快速的通过该索引来确定某条记录，比如身份证号，id号。

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

3.经常出现where,group by ,order by ,在一个语句中，必须创建联合索引

4.限制索引的数目

索引的数目不是越多越好。十万行数据一下，不需要建立索引
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.

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

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

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

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

总之，建立索引务必遵循以下原则：

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

关于联合索引：*****

命令很简单，但原理很复杂
alter table city add index idx_co_po(countrycode,population);
创建一个city表的联合索引，列是countrycode和population，索引名字教 idx_co_po
where A group by B order C   这样的语句，联合索引顺序必须是（A,B,C）
where A B C
1.都是等值，在5.5以后，无关索引顺序，但是需要把控一个，把唯一值多的列放在联合索引的最左侧。（因为优化器会自哦对那个把你的条件按照联合索引的顺序重新排序）
2.如果有不等值， select where A=and B> and C=
索引顺序：ACB或者CAB，（必须把等值的列放在最左侧，这两个还要查看谁的唯一值多，放左边）
语句改写为：ACB（等值的放在左侧，且唯一值多的放左侧。）

不等值查询，虽然查询到是使用了联合索引，但实际上只会走到等值后边的第一个不等值的条件，再往后不会走联合索引了，效率会降低喽。