mysql索引特点及索引优化方案

一、什么是索引？

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。

二、有哪些索引

普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制。唯一任务是加快对数据的访问速度。

唯一索引
唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。

主键索引
一般情况主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。(这里只考虑InnoDB搜索引擎)
聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。

复合主键
就是指你表的主键含有一个以上的字段组成 。

三、索引区别

（1）InnoDB的主键采用聚簇索引存储，使用的是B+Tree作为索引结构，但是叶子节点存储的是索引值和数据本身（注意和MyISAM的不同）。
（2）InnoDB的二级索引不使用聚蔟索引，叶子节点存储的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。
（3）MyISAM的主键索引和二级索引叶子节点存放的都是列值与行号的组合，叶子节点中保存的是数据的物理地址
（4）MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址
（5）为什么用B+Tree 不是BTree：
B-Tree：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。
B+Tree：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。
主键B+结构:

普通索引B+结构:

区别总结:
主键存储的是 索引值和数据,根据主键查询直接返回数据.
普通索引,存储的是 KEY字段加主键,查询根据主键再进行一次回表,数据量大,耗时也长.

四、覆盖索引

一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。(例 普通索引返回主键值)
只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

EXPLAIN SELECT id from student WHERE age =55        (age为普通索引) 0.0X秒

如果查询100万条数据(age为普通索引),查询年龄是55岁的,结果：3秒多完成,因为name没有走覆盖索引.

select name,age from student WHERE age =55          (age为普通索引)3秒

五、联合索引(左前缀原则)

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
相比较 a索引,b索引,c索引,联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的一个索引去使用.

创建得分表score,创建联合索引 index (name,age),插入数据

CREATE TABLE `teacher` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
   `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COMMENT='老师';

联合索引在查找的时候，比如要找 Alice，34 这条记录 WHERE name = ‘Alice’ AND age= 34
先根据name查找 Alice ，找到了2条记录，在根据age查找 34，然后获取到主键 15 ，在根据主键去查找 主索引。
如果 是 WHERE age= 34，由于只有联合索引 (name, age)，没有name的单列索引。

联合索引,利用到个数越多,索引字段的长度key_len长度越大(占据资源越也越大)

EXPLAIN SELECT * from teacher WHERE name='a' 

EXPLAIN SELECT * from score  WHERE name='Alice'  and  age=34 

EXPLAIN SELECT * from teacher WHERE name='a'  group by age   
--或者
EXPLAIN SELECT * from teacher WHERE name='a'  order by age

可以通过 key_len来判断 索引利用几个.

注意:
联合索引的使用在写where条件的顺序无关，mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力，最好和索引的从左到右的顺序一致。
使用等值查询，多列同时查询，索引会一直传递并生效。因此等值查询效率最好。
索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。
排序也能使用索引，合理使用索引排序，避免出现file sort。
group by也可以用索引,用法和order by 类似,没有索引会产生中间表和重排序(Using temporary; Using filesort)
having 不能用索引.

参考文章:https://blog.csdn.net/Abysscarry/article/details/80792876
参考文章:https://blog.csdn.net/klchht/article/details/78146443
参考文章:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

创建联合索引index(age,name)

select name,age from student WHERE age =55       0.0x秒