mysql索引

MySQL官方对索引的定义为：索引（Index)是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。

索引的目的在于提高查询效率，可以类比一本英文字典：
如果要查"mysql"这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要a–z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
是不是觉得如果没有索引，就无法完成这些工作。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式实例：

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，折样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

一般MySQL中的索引指的数据结构是Btree或者B+tree。

Btree:

B+tree:

聚簇索引与非聚簇索引：

主键是聚簇索引，其他的都是非聚簇索引

创建索引：

explain<<性能分析>>【重点】

语法：explain 查询语句

各字段解释：

• id:
  id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
  id如果相同，可以认为是一组，从上往下顺序执行；
  注意：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

• select_type:
  * simple：简单的select查询，查询中不包含子查询或者UNION
  * primary：查询中若包含任何复杂的子部份，最外层查询则被标记为Primary
  * derived：在FROM列表中包含的子查询被标记为DERIVED（衍生）Mysql会递归执行这些子查询，把结果放在临时表里。
  * subquery：在select或where列表中包含了子查询
  * dependent subquery：在select或where列表中包含了子查询，子查询基于外层
  * uncacheable subquery：系统变量
  * union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select被标记为：derived
  * union result：从union表获取结果的select

type:
possible_key:
key:
key_len: where后面的字段用上索引的长度，复合索引中key_len命中的长度越长，数据越大越好
计算：
1、先看索引上字段的类型+长度比如 int = 4; varchar(20) =20; char(20) = 20
2、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3，GBK要乘2，
3、varchar这种动态字符串要加2个字节
4、允许为空的字段要加1个字节
rows: 真正物理扫描的行数，数字越小越好
extra：

• Using filesort：但凡order by没有用上索引，就会出现，说明查询速度很慢。
• Using temporary：但凡group by没有用上索引，就会出现，说明查询速度极慢
• Using join buffer：
• Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。【利用索引进行了排序或分组】
• Using where：表明使用了where过滤
• Impossible where：查询语句错误：不可能的查询
• select tables optimized away：用上了优化器（Myisam）

查询优化【重点】

例题

• 批量数据脚本

往表里插入50W数据
1、建库：
create database if not exists mydb;
2、建表：
create table `dept`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
ceo INT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`empno` INT NOT NULL,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptld` INT(11) DEFAULT NULL,
PRIMARY KEY(`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY(`deptld`) REFERENCES `t_dept`(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
3、设置参数log_bin_trust_function_creators：见下图

4、创建函数，保证每条数据都不同
————随机产生字符串
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWSYZ';
declare return_str varchar(255) default'';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$

————随机产生部门编号
delimiter $$
create function rand_num(from_num int, to_num int) returns int(11)
begin
declare i int default 0;
set i=floor(from_num + rand()*(to_num-from_num+1));
return i;
end$$

5、创建存储过程
————创建往emp表中插入数据的存储过程
delimiter $$
create procedure insert_emp(start int, max_num int)
begin
declare i int default 0;
set autocommit = 0; # 关闭自动提交，切换到手动提交
repeat
set i = i + 1;
insert into emp(empno, name, age, deptid) values((start+i), rand_string(6), rand_num(30, 50), rand_num(1, 10000));
until i = max_num
end repeat;
commit;
end$$
————创建往dept表中插入数据的存储过程
delimiter $$
create procedure `insert_dept`(max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into dept(deptname, address, ceo) values(rand_string(8), rand_string(10), rand_num(1, 500000));
until i = max_num
end repeat;
commit;
end$$
6、执行存储过程
————往dept表添加1万条数据
delimiter ;
call insert_dept(10000);
————往emp表添加数据
delimiter ;
call insert_emp(100000, 500000);

• 单表使用索引及常见索引失效
  构建复合索引(idx_age_deptid_name)----B+树的示意图：
  
  
  
  
  
• 关联查询优化
  
• 子查询优化
  

#1
explain sql_no_cache select emp.`name`, e.`name` CEOname from emp
left join dept on emp.`deptId` = dept.`id`
left join emp e on dept.`ceo` = e.`id`
where e.`age` < emp.`age`;

#2
explain select sql_no_cache emp.`name`, emp.`age`, aa.`avg_age`
from emp inner join
(select emp.`deptId`, avg(emp.`age`) avg_age from emp 
 where emp.`deptId` is not null group by emp.`deptId`) aa
on emp.`deptId` = aa.`deptId` where emp.`age` < aa.avg_age;

加上索引：
CREATE INDEX idx_depId_age ON emp(deptId, age);
CREATE INDEX idx_deptId ON emp(deptId);

• 排序分组优化
  使用order by时
  无过滤不索引；——没有筛选条件，排序就用不上索引，这时可以加分页限制limit 10
  顺序错必排序；——字段的使用顺序要跟复合索引中的字段顺序保持一致
  方向饭必排序； ——排序要么全递增要么全递减
  
• 最后使用索引的手段：覆盖索引