锤爆mysql之索引

索引是什么？

• 帮助Mysql高效获取数据的数据结构，通俗来讲：数据库索引就像是一本书的目录，能够加快数据库的查询速度
  作用：
  方便查找—检索
  索引查询内容—覆盖索引
  排序
• 索引本身也要被春村在磁盘文件中的
• 索引包括：聚集索引，覆盖索引，组合索引，前缀索引，唯一索引等，没特别说明默认都是使用B+数结构组织（多路搜索树，并不一定是二叉树）的索引。

索引的优势和劣势

优势

• 可以提高检索效率，降低IO成本—检索

• 通过索引进行排序，降低排序成本，减低CPU消耗—排序

  • 被索引的列自动排序，包括【单列索引】和【组合索引】组合索引的排序复杂
  • 如果按照索引列的顺序进行排序，对应order by 语句来说，效率就会提高很多
  • where索引列，在存储引擎层处理
  • 覆盖索引不需要回表查询

劣势

• 索引会占据磁盘空间
• 降低更新表效率，对表进行增删改操作，不仅要保存数据，还要更新对应的索引条件

索引分类

• 单列索引

  • 普通索引（辅助索引）：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。 add index
  • 唯一索引：索引列中的值必须是唯一的，但允许为空值。 add unique index
  • 主键索引：是一种特殊的唯一索引，不允许有空值。 pk

• 组合索引

  • 在表中的多个字段组合上创建索引 add index (col1,col2…)
  • 遵循最左前缀原则（最左匹配原则）
  • 除主键索引外，建议使用组合索引代替单列索引。

• 全文索引

  只有在MyISAM引擎和InnoDB(5.6以后)才能使用，而且只能在char ,varchar,text 类型字段上使用全文索引。 fulltext
  优先级最高，不会执行其他索引
  存储引擎 决定执1个索引

空间索引

索引的使用

创建索引

• 普通索引

CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

• 唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);

• 单列索引之全文索引

CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)

• 组合索引

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

• 删除索引

DROP INDEX index_name ON table

• 查看索引

SHOW INDEX FROM table_name 

索引的存储结构

索引存储结构

• 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
• MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也就是说默认使用BTREE，不能够更换
• MEMORY/HEAP存储引擎：支持HASH和BTREE索引
  B树和B+树
  B树是为了磁盘或其它存储设备而设计的以种多叉平衡查找树。
  
  B树叶子和非叶子节点都会存储数据
  B+树
  

B+树只在叶节点储存，而且数据都在一行，有指针指向，有顺序，索引列使用 order by

非聚集索引（MyISAM）

• B+树叶节点只存储了数据（数据文件）的指针（地址），就是非聚集索引。
• 非聚集索引包含主键索引和辅助索引都会存储指针的值
  主键索引
  主键储存在B+树中，进行索引时遍历B+树找到主键值及其对应的指针（地址），再根据地址在另外一个文件中找到具体的相应的内容。
  辅助索引
  与主键索引基本相同，区别在于主键不可重复。
  同样也是一颗 B+树,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法：先按照B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

聚集索引（InnoDB）

主键索引
数据和索引在一起的就是聚集索引，叶节点包含了完整的数据记录， InnoDB 的数据本身要按主键聚集。
InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以
唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。
辅助索引
InnoDB 的辅助索引 data 域存储相应记录主键的值，而不是不是全部数据，找到主键在进行主键索引

select * from user where name='Alice'     回表查询 检索两次 非主键索引 --- pk---索引--->数据
select id,name from user where name='Alice'       不需要回表 在辅助索引树上就可以查询到了 覆盖索引
(多用组合索引)

常见问题：

哪些情况需要创建索引？

• 1.主键自动建立唯一索引
• 2.频繁作为查询条件的字段，应当创建索引
• 3.多表关联查询，关联字段应创建索引on 两边都要创建索引
• 4.查询中排序的字段应创建索引
• 5.频繁查找字段 覆盖索引
• 6.查询中统计或分组字段，应当穿件索引group by

哪些情况不需要创建索引

• 1.表记录太少
• 2.经常进行增删改操作的表
• 3.频繁更新的字段
• where条件里使用频率不高的字段

为什么要使用组合索引

mysql创建组合索引的规则是：先会对组合索引的最左边的，也就是第1个name字段的数据进行排序，在第1个字段的排序基础上，然后再对第二个的cid字段排序。其实就相当于实现了类似order by name cid的排序规则。
节省mysql索引存储空间以及提升搜索性能，可建多组合索引（能使多组合索引就不使单列索引）

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3'）

创建了组合索引（col1 , col1 col2, col1 col2 col3 三个索引）
一颗索引树上创建3个索引：省空间

组合索引遵循最左前缀原则：
1、前缀索引 like a%
2、从左向右匹配直到遇到范围查询 > < between like 停止
建多组合索引 (a,b,c,d)当执行where a=1 and b=1 and c>3 and d=1
到c>3停了 所以d 用不到索引了，如何解决？删除索引重新构建，调整索引顺序。

create table 'tablename' (id  int primary key , a int , b int ,c int,d int);
alter table 'tablename' add index index_com(a,b,c,d);
drop index idx_com on "tablename" ;
create index idx_com on 'tablename'(a,b,d,c)

索引失效

MySQL 提供了 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划分析, 并输出 SELECT 执行的
详细信息,查看该SQL语句有没有使用上了索引，有没有做全表扫描，了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及运用SQL语句时哪种策略预计会被优化器采用等。以供针对性优化。

explain select * from user;

参数：

类型	含义
system	表只有一行
const	表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext	全文搜索
ref_or_null	与ref类似，但包括NULL
index_merge	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话
unique_subquery	在in子查询中，就是value in (select…)把形如“select unique_key_column”的子查询替换。不一定in子句中使用子查询就是低效的！
index_subquery	同上，但把形如”select non_unique_key_column“的子查询替换
range	常数值的范围
index	a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描

部分内容来着开课吧。

本文地址：https://blog.csdn.net/ClarkMOSS/article/details/107622409