Mysql优化4:索引

1、索引

1.1 什么是索引？
索引本身是一个存储单位，在该单位里边有记录着数据表某个字段个字段对应的物理空间。索引内部有算法支持。可以使得查询速度非常快。
有了索引，我们根据索引作为条件进行数据查询速度就非常快
索引字段与物理地址有直接对应，帮助我们可以快速的找到想要的信息
1.2 索引类型
主键 primary key (auto_increment必须要给主键索引设置自增，信息内容不能为null，且唯一)
唯一索引 unique index (信息内容不能重复）
普通索引 index (没有具体要求)
全文索引 fulltext index
1.3 操作(创建、删除)索引
创建索引：(创建索引时没设置索引名，索引名默认是字段名)
①创建表时创建索引
创建其他索引：

create table student (
id int not null auto_increment comment  '主键',
name varchar(32) not null default `` comment `姓名`,
height tinyint not null comment `身高` ,
school varchar(32) not null default `` comment `学校`,
addr varchar(32) not null default `` comment `地址`,
intro text comment `简介`,
primary key (id),
unique index [索引名](name),
idnex [索引名](height),
fulltext index [索引名](intro),
)engine=myisam charset=utf-8

创建复合索引：

index ‘索引名称’; 创建符合索引
②给已有表创建索引

alter table student add primaty key (id);    #创建主键
alter table student add unique index [索引名](name);    #创建唯一索引
alter table student add index [索引名](school);    #创建普通索引
alter table student add fulltext index [索引名](intro);    #创建全文索引

以上就是创建表创建索引。
删除索引：

alter table student drop primary key;    #删除主键，注意，删除主键的时候一定要先删除自增属性

alter table student modify id int not null comment `主键`; 

alter table student drop index 索引名称;  #删除其他索引

1.4 执行计划explain
针对查询语句设置执行计划，当前数据库只有查询语句支持执行计划。
每个select查询sql语句执行之前，需要把该语句需要用到的资源都计划好。
例如：CPU资源，内存资源，索引支持，涉及到的数据量等。
查询sql语句真是执行之前所有的资源计划就是执行计划。
我们讨论的执行计划就是看查询sql’语句是否可以使用索引。
具体操作：

     explain    查询语句    \G；

一条sql语句在执行之前，可以看下执行计划。
1.5 索引的适合场景
① where 条件查询
where 之后设置的查询条件字段都可以设置索引。
②排序查询
order by 字段 //排序查询的字段适合做索引。
排序的没有索引，排序查询就不会使用到索引。
③ 索引覆盖
给两个字段设置索引覆盖。
查询的这两个字段已经在索引里，可以直接获取，不需要从表中获取了，因此称为‘索引覆盖’，查询速度非常快，效率高，也称之为‘黄金索引’。
索引本身是需要消耗资源的(空间资源、升级维护困难) 。
④ 连接查询
join on
goods：id cate_id name……
category：cate_id name……
在goods表中给外键/约束字段设置索引，可以提高联表查询的速度。

1.6 索引原则
① 字段独立原则1
select * from user where id = ‘32’; #独立字段使用索引
select * from user where id+2 = ‘32’; #非独立字段不使用索引
② 左原则
模糊查询，like % _
%：关联多个模糊内容；
_：关联一个模糊内容；
select * from user like ‘adc%’; #关联多个模糊内容的左原则
select * from user like ‘abc_’; #关联一个模糊内容的做原则
查询条件信息在左边出现，就使用索引
%abc% abc %abc _abc 这四种则不使用索引
③ 复合索引
例如一个复合索引有多个字段；
第一个字段作为查询条件则可以使用索引，后面的字段作为查询条件则不可以使用索引。如果使用and条件查询，第一个字段已经确定有了索引，那么第二个字段即使没有索引也会使用到索引。(这两个字段必须在复合索引内，不是所有的and条件查询都是这样)。
④ OR原则
OR两边的关联条件必须都具备索引才能使用索引。

1.7 索引设计依据
要估算每个数据表全部的sql语句类型。
分析、统计每个sql语句的特点(where/order by/or等)。
原则:
① 被频繁执行的sql语句需要设置
② 执行时间比较长的sql语句要设置(可以统计哪些sql语句是执行时间较长的)
③ 业务逻辑比较重要的sql语句需要设置

1.8 前缀索引
设计索引的字段不使用全部内容，而只使用该字段前边一部分内容。
如果字段前边的N为信息足够标识当前信息，就可以把前面N位信息设置为索引内容，好处：索引占据的物理空间小、运行速度快。
具体实现：
① alter table 表名 add index (字段(位数));
② 前边到底取多少位才是记录的唯一标识；
总记录数/前n位记录数目 = 比值。
当比值稳定的或接近1的时候就取这个n的值当做位数。
select count(*)/count(distinct substring(字段,开始位置,位数)) from 表名。

1.9 索引设计原则
字段内容需要样式丰富，例如性别就不能做索引

2.1 全文索引
mysql5.5 myisam存储引擎支持全文索引
mysql5.6 myisam和innodb支持全文索引
select version(); #查看mysql版本
目前中文不支持全文索引
全文索引可以应用在 like %XXX%的操作上面。
全文想要模糊查询使用索引就要改成下面的语句
select * from articles where match(title,body) against(‘查询的模糊内容’,’查询的模糊内容’);

2.2 索引结构
2.2.1 myisam非聚集索引结构(了解)
索引结构：B+Tree
叶子节点的关键字对应物理地址，物理地址对应整条记录信息
2.2.2 innodb聚集索引结构
索引结构：B+Tree
① 主键索引结构
重要一点：叶子节点的关键字(主键id)对应整条记录信息
② 非主键索引结构
叶子节点的关键对应对应整条记录信息
非主键索引——>innodb的主键索引——>整条记录
这样我们可以看到：数据和索引是在一起的
innodb表生成后缀是ibd文件里的索引和数据就是在一起的
概念问题：
B+Tree B-Tree Binary Tree
B+Tree是B-Tree的一个变形，他们的明显区别是：B-Tree的每一个节点的关键字都对应一个物理地址
Binary Tree 二进制树结构