MySQL索引与索引优化
MySQL的基本架构示意图MySQL索引就是用于优化器上。
索引：
MySQL官方对于索引的定义为：索引是帮助MySQL高效获取数据的数据结构。即可以理解为：索引是数据结构。
索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于快速获取信息。
你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
首先明白为什么索引会增加速度？
DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
索引的数据结构：hash、二叉树、B树、B+树（最终最常用的是B+树）
hash：使用存储在内存中的内容来创建表，而且数据全部存放在内存中。
缺点：hash冲突–扰动函数
1.Hash存储需将所有的数据文件添加到内存中，比较浪费内存空间
2.如果所有的查询都是等值查询，那么hash比较快，但范围查找就不太适合
如果使用hash做成的索引，因为需要全部扫描，即使在内存中，速度不容乐观。
适合场景：等值查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件。
二叉树：
⼆叉树是有序的，所以是⽀持范围查询的，但时间复杂度是O(log(N))。
缺点：二叉树还是红黑树都会因为树的深度过深而造成io次数过多，影响读取效率，以及有可能退化为链表结构。
不推荐使用select * 而使用字段，因为数据是存储在磁盘，并且MySQL服务有筛选数据，每次读取数据都会经过服务筛选，如果都是使用select *就会增加io次数。
B树：
同样的元素，B树的表示要⽐完全平衡⼆叉树要“矮”，原因在于B树中的⼀个节点可以存储多个元素，相对于完全平衡⼆叉树整体的树⾼降低了，磁盘IO效率提⾼了。
从最开始的Hash不⽀持范围查询，⼆叉树树⾼很⾼，只有B树 跟B+有的⼀⽐。
B+树
B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据，所有的叶子节点形成一条有序链表（双向链表），而且数据是按照顺序排列的。
MySQL的索引底层为何使用B+树？
为了减小IO操作数量，一般把一个节点的大小设计成最小读写单位的大小，MySQL的存储引擎InnoDB的最小读写单位是16K一页，B+树中⼀个节点为⼀⻚或⻚的倍数最为合适。
页概念
⾸先Mysql的基本存储结构是⻚(记录都存在⻚⾥边)各个数据⻚可以组成⼀个双向链表，⽽每个数据⻚中的记录⼜可以组成⼀个单向链表，每个数据⻚都会为存储在它⾥边⼉的记录⽣成⼀个⻚⽬录，在通过主键查找某条记录的时候可以在⻚⽬录中使⽤⼆分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(⾮主键)作为搜索条件：只能从最⼩记录开始依次遍历单链表中的每条记录。
没有进⾏任何优化的sql语句，默认会这样做：
定位到记录所在的⻚，需要遍历双向链表，找到所在的⻚，从所在的⻚内中查找相应的记录，由于不是根据主键查询，只能遍历所在⻚的单链表了。
很明显，在数据量很⼤的情况下这样查找会很慢！看起来跟回表有点点像
对比B树，B+树的优势是
1.每个节点存储的key数量更多，树的高度更低。
2.所有的具体数据都存在叶子节点上，所以每次查询都要查到叶子节点，查询速度比较稳定。
3.所有的叶子节点构成了一个有序链表，做区间查询时更方便。
⼩结：到这⾥可以总结出来，Mysql选⽤B+树这种数据结构作为索引，可以提⾼查询索引时的磁盘IO效率，并且可以提⾼范围查询的效率，并且B+树⾥的元素也是有序的。
MySQL存储引擎（存储引擎是形容数据表的，不是数据库）
常用有三：
meory(hash,内存存储数据)
inndb myisam ：B+树 （inndb可以有hash，但是是自适应hash:系统将B+树转化成自适应hash，人为无法判断干预）
如何选择引擎？
如果没有特别的需求，使用默认的 Innodb 即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。
聚族索引是取决于数据与索引是否存放在一起
叶子节点包含了完整的数据记录则是聚族索引
聚集索引
（1）：聚集索引就是以主键创建的索引
（2）：每个表只能有⼀个聚簇索引，因为⼀个表中的记录只能以⼀种物理顺序存放，实际的数据⻚只
能按照⼀颗 B+ 树进⾏排序
（3）：表记录的排列顺序和与索引的排列顺序⼀致
（4）：聚集索引存储记录是物理上连续存在
（5）：聚簇索引主键的插⼊速度要⽐⾮聚簇索引主键的插⼊速度慢很多
（6）：聚簇索引适合排序，⾮聚簇索引不适合⽤在排序的场合，因为聚簇索引叶节点本身就是索引和
数据按相同顺序放置在⼀起，索引序即是数据序，数据序即是索引序，所以很快。⾮聚簇索引叶节点是
保留了⼀个指向数据的指针，索引本身当然是排序的，但是数据并未排序，数据查询的时候需要消耗额
外更多的I/O，所以较慢
（7）：更新聚集索引列的代价很⾼，因为会强制innodb将每个被更新的⾏移动到新的位置
⾮聚集索引
（1）：除了主键以外的索引
（2）：聚集索引的叶节点就是数据节点，⽽⾮聚簇索引的叶节点仍然是索引节点，并保留⼀个链接指
向对应数据块
（3）：聚簇索引适合排序，⾮聚簇索引不适合⽤在排序的场合
（4）：聚集索引存储记录是物理上连续存在，⾮聚集索引是逻辑上的连续。
使⽤聚集索引为什么查询速度会变快？
使⽤聚簇索引找到包含第⼀个值的⾏后，便可以确保包含后续索引值的⾏在物理相邻
建⽴聚集索引有什么需要注意的地⽅吗？
在聚簇索引中不要包含经常修改的列，因为码值修改后，数据⾏必须移动到新的位置，索引此时会重
排，会造成很⼤的资源浪费
InnoDB 表对主键⽣成策略是什么样的？
优先使⽤⽤户⾃定义主键作为主键，如果⽤户没有定义主键，则选取⼀个Unique键作为主键，如果表中
连Unique键都没有定义的话，则InnoDB会为表默认添加⼀个名为row_id隐藏列作为主键。
⾮聚集索引最多可以有多少个？
每个表你最多可以建⽴249个⾮聚簇索引。⾮聚簇索引需要⼤量的硬盘空间和内存
BTree 与 Hash 索引有什么区别？
（1）：BTree索引可能需要多次运⽤折半查找来找到对应的数据块
（2）：HASH索引是通过HASH函数，计算出HASH值，在表中找出对应的数据
（3）：⼤量不同数据等值精确查询，HASH索引效率通常⽐B+TREE⾼
（4）：HASH索引不⽀持模糊查询、范围查询和联合索引中的最左匹配规则，⽽这些Btree索引都⽀持
innab是聚族索引，数据与索引存储在一起
最左前缀匹配原则。这是⾮常重要、⾮常重要、⾮常重要（重要的事情说三遍）的原则，MySQL会⼀直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停⽌匹配。
索引是有序的，index1索引在索引文件中的排列是有序的，首先根据a来排序，然后才是根据b来排序，最后是根据c来排序，像select * from table where a = ‘1’ and b > ‘2’ and c=‘3’ 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。
MySQL索引类型： mysql 有4种不同的索引：
主键索引（PRIMARY）
数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引（UNIQUE）
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引（INDEX）
可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引
可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 创建组合索引
全文索引（FULLTEXT）
可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引
索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引
二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录
聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少
排除缓存⼲扰
如果我们当前的MySQL版本⽀持缓存⽽且我们⼜开启了缓存，那每次请求的查询语句和结果都会以keyvalue的形式缓存在内存中的，⼀个请求会先去看缓存是否存在，不存在才会⾛解析器。
缓存失效⽐较频繁的原因就是，只要我们⼀对表进⾏更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，可能静态表可以⽤到缓存，如果⾛⼤数据离线分析，缓存也就没⽤了。
⼤家如果是8.0以上的版本就不⽤担⼼这个问题，如果是8.0之下的版本，记得排除缓存的⼲扰。
Explain：分析执⾏计划
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
常用的类型有：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作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明：
using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by；效率低
using index表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。
索引优化
最左前缀匹配原则。这是⾮常重要、⾮常重要、⾮常重要（重要的事情说三遍）的原则，MySQL会⼀直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停⽌匹配。
索引是有序的，index1索引在索引文件中的排列是有序的，首先根据a来排序，然后才是根据b来排序，最后是根据c来排序，像select * from table where a = ‘1’ and b > ‘2’ and c=‘3’ 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。
以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引组合索引：当我们的where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引，最左匹配一般组合索引一起使用。
回表：回表是发生在二级索引上的一种数据查询操作，简单点讲就是我们要查询的列不在二级索引的列中，那么就必须根据二级索引查到主键ID，然后再根据主键ID到聚簇索引树上去查询整行的数据，这一过程就叫作回表。
索引覆盖：当SQL语句中查询的列都在索引中时，我们就不需要回表去把整行数据都捞出来了，可以从非聚簇索引树中直接获取到我们需要的列的数据，这就叫索引覆盖,当所有的列都能在二级索引树中查询到，就不需要再回表了，这种情况就是索引覆盖,覆盖索引减少回表
索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满⾜条件的记录，减少回表次数。
使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
将与索引相关的条件由MySQL服务器向下传递至存储引擎，由此减少IO次数.索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。5.6之后才有，但有实际数据存储问题，唯一的缺点就是需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放在磁盘上进行，看起来成本比较高，但数据是排序的，所有的数据都是聚集存放，所以性能并不会受到影响，反而IO量会大大减少，提升性能
索引失效条件
（1）：条件是or，如果还想让or条件⽣效，给or每个字段加个索引
（2）：like开头% （3）：如果列类型是字符串，那⼀定要在条件中将数据使⽤引号引⽤起来，否则不会使⽤索引
（4）：where中索引列使⽤了函数或有运算
SQL优化
1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union
all会更好) 5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫
描，如：select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null
值，然后这样查询：select id from t where num=0
数据库索引优缺点
（1）：需要查询，排序，分组和联合操作的字段适合建⽴索引
（2）：索引多，数据更新表越慢，尽量使⽤字段值不重复比例⼤的字段作为索引，联合索引⽐多个独⽴索引效率⾼
（3）：对数据进⾏频繁查询进建⽴索引，如果要频繁更改数据不建议使⽤索引
（4）：当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速
度。