mysql5.6 函数索引,MySQL（二）：快速理解MySQL数据库索引

索引

基本概念：索引是在存储引擎层实现的，而不是在服务器层实现的，因此不一样存储引擎具备不一样的索引类型和实现。mysql

数据结构

Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，而且全部叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具备 B Tree 的平衡性，而且经过顺序访问指针来提升区间查询的性能。

在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，若是某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的全部 key 大于等于 keyi 且小于等于 keyi+1。

基本原理

进行查找操做时，首先在根节点进行二分查找，找到一个 key 所在的指针，而后递归地在指针所指向的节点进行查找。直到查找到叶子节点，而后在叶子节点上进行二分查找，找出 key 所对应的 data。

插入删除操做会破坏平衡树的平衡性，所以在进行插入删除操做以后，须要对树进行分裂、合并、旋转等操做来维护平衡性。

红黑树

红黑树等平衡树也能够用来实现索引，可是文件系统及数据库系统广泛采用 B+ Tree 做为索引结构，这是由于使用 B+ 树访问磁盘数据有更高的性能。sql

B+ 树有更低的树高：平衡树的树高 O(h)=O(logdN)，其中 d 为每一个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度通常都很是大，因此红黑树的树高 h 很明显比 B+ Tree 大很是多。

磁盘访问原理：操做系统通常将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能彻底载入一个节点。

若是数据不在同一个磁盘块上，那么一般须要移动制动手臂进行寻道，而制动手臂由于其物理结构致使了移动效率低下，从而增长磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只须要很短的磁盘旋转时间，因此 B+ 树更适合磁盘数据的读取。

磁盘预读特性：为了减小磁盘 I/O 操做，磁盘每每不是严格按需读取，而是每次都会预读。预读过程当中，磁盘进行顺序读取，顺序读取不须要进行磁盘寻道，而且只须要很短的磁盘旋转时间，速度会很是快。而且能够利用预读特性，相邻的节点也可以被预先载入。

索引分类

B+树索引

是大多数 MySQL 存储引擎的默认索引类型。

由于再也不须要进行全表扫描，只须要对树进行搜索便可，因此查找速度快不少。

由于 B+ Tree 的有序性，因此除了用于查找，还能够用于排序和分组。

能够指定多个列做为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。若是不是按照索引列的顺序进行查找，则没法使用索引。

InnoDB 的 B+Tree 索引分为聚簇索引(主索引)和非聚簇索引(辅助索引)。

惟一索引：主键就是一种特殊的惟一索引，惟一索引容许null值，但主键列不容许为null值，一张表最多建议一个主键

哈希索引

哈希索引能以O(1)时间进行查找，可是失去了有序性特色InnoDB

存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的很是频繁时，会在 B+Tree 索引之上再建立一个哈希索引，这样就让 B+Tree 索引具备哈希索引的一些优势，好比快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH(clo1,clo2..) AGAINST(value)，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

FULLTEXT (clo1,clo2)只有创建的全文索引的列能够进行全文索引

索引优化

独立的列

进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，不然没法使用索引数据库

多列索引

在须要使用多个列做为条件进行查询时，使用多列索引比使用多个单列索引性能更好。

列以下面的语句中能够将actor_id和film_id设置多列索引性能优化

SELECT film_id, actor_ id FROM sakila.film_actor

WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每一个记录都有惟一的索引与其对应。选择性越高，每一个记录的区分度越高，查询效率也越高。

让选择性最强的索引列放在最前面

输入语句：服务器

SELECT COUNT(DISTINCT addr_id)/COUNT(*) AS addr_id_selectivity,

COUNT(DISTINCT user_id)/COUNT(*) AS user_id_selectivity,

COUNT(*)

FROM user_address;

结果：数据结构

addr_id_selectivity：1.0000

user_id_selectivity：0.1250

count(*)：64

从上述结果分析：主键是惟一的，user_id会有重复，因此写sql语句时，主键应该放在最前面

这样能够逐个分析对应的索引函数

前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取须要根据索引选择性来肯定。某些列，通常是字符串类型，很长，所有做为索引大大增长存储空间，索引也须要维护，对于长字符串，又想做为索引列，一个可取的办法就是取前一部分(前缀)，表明一整列做为索引串。

如何确保这个前缀能表明或大体表明这一列？因此mysql中有个概念是索引的选择性，是指索引中不重复的值的数目(也称基数)与整个表该列记录总数(#T)的比值，好比一个列表(1,2,2,3)，总数是4，不重复值数目为3，选择性为3/4，所以选择性范围是[1/#T, 1]，这个值越大，表示列中不重复值越多，越适合做为前缀索引，惟一索引(UNIQUE KEY)的选择性是1。

MySQL性能优化神器Explain使用分析

Explain 用来分析 SELECT 查询语句，开发人员能够经过分析 Explain 结果来优化查询语句。

select_type : 查询类型，有简单查询、联合查询、子查询等

key : 使用的索引

rows : 扫描的行数

总结

数据库只作两件事情：存储数据、检索数据。而索引是在你存储的数据以外，额外保存一些路标(通常是B+树)，以减小检索数据的时间。因此索引是主数据衍生的附加结构。

一张表能够创建任意多个索引，每一个索引能够是任意多个字段的组合。索引可能会提升查询速度(若是查询时使用了索引)，但必定会减慢写入速度，由于每次写入时都须要更新索引，因此索引只应该加在常常须要搜索的列上，不要加在写多读少的列上。

多使用explain对语句进行索引分析，任何理论都是创建在实际的分析基础上，explain语句关注那几个关键词便可。盲目的加索引并非好的，有时候索引反而成为拖慢咱们系统运行速度的源头.优化索引其实就两件事，第一找到区别度大的列，第二看生产的查询场景是否有用到这个列，加上索引用explain分析，看是否会比不加上索引性能提高更快