1 索引作用与分类

索引类似书中的目录，目的为了优化查询
分为 下面几类
B数索引
hash索引
R树
Full text
GIS

2 索引 B树

1.将排好序的值,均匀的分布到索引树的叶子节点中(16K)
2.生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

图片.png

3.功能上区分

辅助索引(S)怎么构建B树结构的?
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO
辅助索引(S)存储
(1) 在建表时,设置了主键列(ID)
(2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
(3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

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

辅助索引与聚集索引的区别

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

辅助索引的划分

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

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

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

3 索引命令

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: 辅助索引(单列,联和,前缀)
select 中 having、like "%num%" 这种不走索引

查看索引
SHOW INDEX FROM city;

新建索引
ALTER TABLE city ADD INDEX idx_name(NAME);

CREATE INDEX idx_name1 ON city(NAME);
删除索引
ALTER TABLE city DROP INDEX idx_name1;

联合索引
ALTER TABLE city ADD INDEX idx_co_po(countrycode,population);

前缀索引
ALTER TABLE city ADD INDEX idx_di(district(5));

唯一索引（里面数据必须唯一 不然报错）
ALTER TABLE city ADD UNIQUE INDEX idx_uni1(NAME);
例如： 求出world表中name重复的值
SELECT NAME,COUNT(NAME) AS cid FROM city GROUP BY NAME  HAVING cid>1 ORDER BY cid DESC;

4 执行计划

前期准备

模拟数据库数据
DROP DATABASE IF EXISTS oldboy;
CREATE DATABASE oldboy CHARSET utf8mb4 COLLATE utf8mb4_bin;
USE oldboy;
CREATE TABLE t_100w (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 t_100w VALUES (i,FLOOR(RAND()*num),str2,str4,NOW());
END WHILE;
END;
//
DELIMITER ;

插入100w条数据：
CALL rand_data(10000000);
COMMIT;

执行计划介绍

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

3306 [oldboy]>desc select * from t_100w where id=9999;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t_100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1248180 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

3306 [oldboy]>explain select * from t_100w where id=9999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_100w
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1248622
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

案例分析观察

table: city                              ---->查询操作的表    **
possible_keys: CountryCode,idx_co_po      ---->可能会走的索引 是否使用 **
key: CountryCode   ---->真正走的索引    ***
type: ref   ---->索引类型        *****
Extra: Using index condition              ---->额外信息   提示索引     *****
key_len 根据数值大小 查看索引是否最优

其中 type类型种类 越向下 越好 range 以上的级别比较理想

1. all  全表扫描 不走索引 
辅助索引 使用非= 的 都不走索引
DESC SELECT * FROM city WHERE countrycode <> "CHN"        不走
DESC SELECT * FROM city WHERE countrycode NOT IN ("CHN","USA")   不走
DESC SELECT * FROM city WHERE countrycode = "CHN" 走
DESC SELECT * FROM city WHERE countrycode LIKE "%CHN%" 不走
DESC SELECT * FROM city WHERE countrycode LIKE "CHN%"  走

聚集索引 都走
DESC SELECT * FROM city WHERE id<>10
DESC SELECT * FROM city WHERE id NOT IN ('10','20')

2. index  全索引扫描 相当于这个目录看一遍
查询建索引的全部值
DESC SELECT CountryCode FROM city; 

联合索引 以非最左列作为查询条件 走索引 INDEX
idx_a_b_c(a,b,c) ---> a ab abc  相当于建的索引值
SELECT * FROM t1 WHERE b
SELECT * FROM t1 WHERE c

3.range 索引范围扫描  辅助索引 < > >= <= like in or ,主键 不等于<> not in 
DESC SELECT * FROM city WHERE id<10
DESC SELECT * FROM city  WHERE CountryCode LIKE'TWN%'
DESC SELECT * FROM city WHERE countrycode in ('CHN','USA')
上面2个 可以享受B+tree的优势 但是第3个不行
可以改进 得到优化
DESC SELECT * FROM city WHERE countrycode ='CHN'  
union all 
SELECT * FROM city WHERE countrycode ='USA'  

4.ref   非唯一性索引 等值查询
DESC SELECT * FROM city WHERE countrycode ='CHN' 

5.eq_ref 在多表连接时连接条件使用了唯一索引 （主键 唯一键）
DESC SELECT b.name,a.name FROM city AS a JOIN country AS b ON a.countrycode=b.code WHERE a.population <100 

6.system,const 唯一索引的等值查询
DESC SELECT * FROM city  WHERE  id=9 

7. NULL 查询错误 不存在的数据 没有意义

extra选项

extra选项 filesort 文件排序
MariaDB [world]> DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY Population;
+------+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                              |
+------+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
|    1 | SIMPLE      | city  | ref  | CountryCode   | CountryCode | 12      | const |  363 | Using index condition; Using where; Using filesort |
+------+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

extra ：Using index condition; Using where; Using filesort   

联合索引  不然只有一个索引
ALTER TABLE city ADD INDEX idx_c_p(CountryCode,Population)

MariaDB [world]> DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY Population;
+------+-------------+-------+------+---------------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type | possible_keys       | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+------+---------------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | city  | ref  | CountryCode,idx_c_p | idx_c_p | 12      | const |  363 | Using where |
+------+-------------+-------+------+---------------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
这样提高查找速度

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

例如：

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

压力测试
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='JKwx'" ENGINE=INNODB --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap 压力测试命令
concurrency  并发 相当于有多少个用户同时登陆
iterations 测试几次
number-of-queries 每个用户做多少次查询
卡满 cpu占用满
desc select * from oldboy.t_100w where k2='JKwx';
alter table t_100w add index id_k(k2);  建立索引 就正常了 
3306 [oldboy]>desc select * from oldboy.t_100w where k2='JKwx';
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_100w | NULL       | ref  | id_k          | id_k | 17      | const |  385 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3306 [oldboy]>desc select * from t_100w where k1='bC' order by k2;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | t_100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1483848 |    10.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
alter table t_100w add index idx_k_k(k1,k2); 新建联合索引后就正常了

3306 [oldboy]>desc select * from t_100w where k1='bC' order by k2;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_100w | NULL       | ref  | idx_k_k       | idx_k_k | 9       | const |  379 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

联合索引：
select * from t1 where a= b= 
alter table t1 add index id_a_b 
alter table t1 add index id_b_a  索引都生效 不考虑顺序 
注意：新建索引的时候 要考虑那个列 表唯一值跟多 那个在前

3306 [oldboy]>alter table t_100w add index idx_n_k_k(num,k1,k2);
Query OK, 0 rows affected (14.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3306 [oldboy]>desc select * from t_100w where num=2 and k1='Zy' and k2='BCef';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_100w | NULL       | ref  | idx_n_k_k     | idx_n_k_k | 31      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3306 [oldboy]>desc select * from t_100w where num=2 and k1='Zy' ;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_100w | NULL       | ref  | idx_n_k_k     | idx_n_k_k | 14      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3306 [oldboy]>desc select * from t_100w where num=2  ;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_100w | NULL       | ref  | idx_n_k_k     | idx_n_k_k | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
注意 key_len的值变化 跟匹配索引而变化

desc select * from oldboy.t_100w where num < 2 and k2="DDDE"
遇到这种语句  需要将 等值的放前面 方便索引查找
desc select * from oldboy.t_100w where   k2="DDDE" and num < 2

面试题 例如

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

限制索引的条目

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

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

不走索引的情况

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

查询结果的数据 是总数据的25%以上 不走索引 
索引本身失效，导致统计数据不真实 解决方法删除 重建索引

问题现象： select 语句之前查询很快，突然有一天很慢 分析原因
DML  锁冲突
select 索引失效 导致统计数据不真实

隐式函数

select * from tab where telnum='1333333';
select * from tab where telnum=1333333;
两个语句 查询结果一致 但是 一个走索引（上面） 一个不走（下面）

<> ，not in 不走索引（辅助索引）
EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');
like "%_" 百分号在最前面不走