Mysql优化之索引

1.索引 index

关键字与数据位置关系映射关系，称之为索引

关键字：从数据中提取，用于标识，检索数据的特点给内容。
目的：加快检索
索引为什么快？

• 关键字相对于数据本身，数据量小
• 关键字都是排序的

Mysql中索引类型

支持：普通index索引，唯一unique index 索引，主键primary key 索引，全文fulltext index 索引

不同类型，仅仅对关键字的限制不同，其他方面都一致。
普通索引：对索引关键字没限制。
唯一索引：要求记录提供的关键字不能重复
主键索引：要求关键字不能重复，同时不能为null

索引的管理语法

创建索引，删除索引，查看索引

1. 查看索引

show create table table_name

查看建表语句是确定索引。

也可以看到部分索引：

desc table_name

2. 创建索引

需要在修改表结构，或者创建表时完成。
基于不同的索引类型，会使用不同的语法

create table user_index1(
	//主键索引，自动增长与业务逻辑无关的id
	id int auto_increment primary key,
	first_name varchar(16),
	last_name varchar(16),
	sh varchar(16),
	information text,
	//创建一个基于first_name,last_name上的复合普通索引，并命名为name
	key name(first_name,last_name),
	//创建了一个基于sn的唯一索引，没有指定名字，默认以字段名命名索引
	upique key (sn),
	//全文索引，鸡肋产品，
	fulltext key (information)
)engine_myisam;

3. 删除索引

删除主键索引

alter table drop primary key;

利用索引名字可以删除，普通，唯一，全文索引

alter table drop key key_name;

删除主键时，通常会伴随者自增长 。自增长还依赖于主键。
需要去掉自增长，再删除主键

 alter table user_index1
 modify id int ,drop primary key;

通常不需要删除主键，设计主键一定与业务逻辑无关 

2.执行计划

explain

在执行的select前，使用关键字explain ，
可以获取该查询语句的执行计划。

执行计划是：当执行SQL的时候，先分析、优化，形成执行计划，按照执行计划执行。

3.索引使用场景

建立的索引，会在哪些情况下被使用，根据这些情况，建立索引

导入测试数据

 calss班级，studen学生，与班级之间为多对一关系

1. where查询
   当前表只有一个主键索引，查寻计划在主键上
   
   查询没有索引的字段
   
   在没有字段的索引上索引，再次查寻

2. order by 排序
   当使用某个字段排序时，也可以使用到该字段上的索引

在没有索引的字段进行排序测试：

filesort：外部排序，相对于内部排序来说的，性能较低，需要将数据读取到内存，但是不能一次读完，需要分段读取，合并排序结果。

增加排序字段的索引。在测试：


索引可以用来排序的原因，本身就是按照关键字排序存的

3. join 连接
   在使用join查询的时候，需要在关联字段上建立索引

查询班级内的学生数量：

select c.* ,count (s.id)from class c join student s on c.id=group by c.id limit 5;

在关联字段s.class_id 上没有索引，执行查询，执行时间长，没有s.class的索引可用。


增加s.calss 的索引

完成关联表的查询

4. 索引覆盖
   是一个现象
   指的是，索引中提供的关键字覆盖了需要查询的字段数据，此时查询会有索引提供，而不是由数据提供。
   建立一个复合索引
   

select frist_name,last_name from student limit 5;

注意查询的字段列表仅仅由first_name ,last_name

更改查询字段的组合

两次查询就差一个user关键字，就不能被覆盖。
建议：在查询时，select后只写必要的字段，被覆盖的可能性提升！

4.语法细节

在满足索引使用场景下，索引也不一定被使用。
主要原因，就是语法不严谨导致！

5.如何创建索引

建立基础索引：在where，order，join 字段上建立索引
优化：

• 组合索引：基于业务逻辑
• 如果条件经常出现在一起，多字段索引，升级为复合索引。
• 如果通过增加个别字段，就可以出现索引覆盖，增加个别字段
• 查询时，不会用到的索引，应该删除掉。
• 有些字段，是否可以使用前缀即可完成标识，改用前缀索引

6. 前缀索引

语法：
index（field(10))
使用字段field的前19个字段建立索引，默认是使用字段的全部内容建立索引。

前缀的标识度，足够的情况下，需要使用前缀索引
例如：密码字段，就适合使用前缀索引
实操的难度，在于前缀的长度。需要分析，多长的前缀，标识度足够：
计算过程如下：
使用总的记录数量除以不重复的记录数量

除以：

再使用不同的前准计算标识度：截取，计算

left（字符串，长度)，从左侧截取字符

建立索引时使用前9个最合理：

使用上与普通索引一致

前缀索引：可以减少关键字的大小，提升索引的遍历速度！