浅谈Mysql的那些索引

浅谈mysql优化

前言

MySql是现在很多公司最常用的关系型数据库，它的优点，体积小，速度快，开源这些就不说了。
MySQL 5.0 之前使用的是MyISAM引擎，查询快，但是不支持事务。5.5之后默认索引就是innodb了。

一、为什么是innodb

支持ACID事务，支持行级锁定。InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。
优点：同样查询sql第二次第三次查询速度显然快于第一次。 缺点：查询速度忽快忽慢，所以想要查询sql真正的时间需要加上

Select sql_no_cache count(*) from users; 不缓存

或者修改配置文件关闭mysql缓存。
MySQL中的InnoDB引擎使用B+Tree结构来存储索引，可以尽量减少数据查询时磁盘IO次数，同时树的高度直接影响了查询的性能，一般树的高度维持在 3~4 层。
B+Tree由三部分组成：根root、枝branch以及Leaf叶子，其中root和branch不存储数据，只存储指针地址，数据全部存储在Leaf Node，同时Leaf Node之间用双向链表链接
我们可以将MySQL中的索引可以看成一张小表，占用磁盘空间，创建索引的过程其实就是按照索引列排序的过程，先在sort_buffer_size进行排序，如果排序的数据量大，sort_buffer_size容量不下，就需要通过临时文件来排序，最重要的是通过索引可以避免排序操作（distinct，group by，order by）。

二、索引

索引是innodb很重要的一部分，但是也有诸多限制，如：
1 where使用 != 时，不能使用索引
2 WHERE 子句的查询条件里使用了函数（WHERE DAY（column）=），MySQL 也将无法使用索引
3 WHERE 子句的查询条件里使用比较操作符 LIKE 和 REGEXP，第一个字符不能是通配符
4 避免困难的正则表达式

1.聚集索引

指索引项的排序方式和表中数据记录排序方式一致的索引.大部分情况都会把聚集索引建在id上。聚集索引会存储整行数据

2.辅助索引

辅助索引也称为二级索引，索引中除了存储索引列外，还存储了主键id，对于user_name的索引idx_user_name(user_name)而言，其实等价于idx_user_name(user_name, id)
关于辅助索引，有一种索引回表的情况，例如：

create index idx_user_name on users(user_name);

这个时候查询的字段却不止user_name,就会出现索引回表，会先根据user_name查出id，再根据聚集索引查出整行数据
优化：
select user_id, user_name, age from users where user_name = 'make';
改为覆盖索引 将索引加上age字段

3.覆盖索引

覆盖索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。
要注意索引失效的场合，比如查询status，create_time
create index idx_status_create on users(status，create_time);就会走索引,
如果create index idx_create_status on users(create_time,status);索引失效

4.复合索引

是多个字段联合组成的索引，建复合索引有些条件：

• 需要加索引的字段，要在where条件中。
• 数据量少的字段不需要加索引。数据唯一性差的放前边，例如status和 create_date建的联合索引。
• 如果where条件中是OR关系，必须所有的or条件都必须是独立索引，否则加索引不起作用。见：mysql关于or的索引问题
• 最左匹配原则。
• 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

5.前缀索引

对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
如果需要在长度大的字段上建索引怎么办呢？可以对字段进行切割，例如都是以www.开头的，则substring()函数截取掉前面的，然后建立索引。

6.几种优化场景

1. 无where的查询条件，需要在查询列上建索引
2 分页查询优化

select id,create_date from t1 order by user_id limit 50000,10;

没建索引之前是走的全表扫描。优化：创建关于分组字段，查询列的索引

alter table t1 add index liu(user_id,create_date);

---

# 总结 说到索引就得谈explain，这里讲explain的几个字段含义

• id:选择标识符 select_type:表示查询的类型。
• table:输出结果集的表 partitions:匹配的分区
• type:表示表的连接类型 possible_keys:表示查询时，可能使用的索引
• key:表示实际使用的索引
• key_len:索引字段的长度 ref:列与索引的比较
• rows:扫描出的行数(估算的行数) filtered:按表条件过滤的行百分比
• Extra:执行情况的描述和说明

都什么意思，大家可自行百度，我只捡重点
1 type 对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

• ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 index: Full Index
• Scan，index与ALL区别为index类型只遍历索引树 range:只检索给定范围的行，使用一个索引来选择行
• ref:表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• eq_ref:类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条
• system:当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

2 Extra 该列包含MySQL解决查询的详细信息,有以下几种情况：

• Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ;
• order by Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
  explain select * from emp order by name;
• Using join buffer：强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
• Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）
• Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
• No tables used：Query语句中使用from dual 或不含任何from子句

整理了常用的索引以及优化，要学习的还有很多~