索引 与 索引数据结构

目录

• • 磁盘io与预读
  • b树
  • b+树

索引

初始索引

• 为什么要有索引

"""
一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，
也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
"""

• 什么是索引

"""
索引在mysql中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构,索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要,
索引优化应该是对查询性能优化最有效的手段了,索引能够轻易将查询性能提高好几个数量级,
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查
"""

• 对索引存在的误解

"""
索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，
对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，
这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，
比如编写sql语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关dba加上即可。dba往往不够了解业务的数据流，
而添加索引需要通过监控大量的sql语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，
并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台mysql服务器iostat显示磁盘使用率一直处于100%，
经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。
"""

索引的原理

"""
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，
有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。
数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，
1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。
但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgn，具有不错的查询性能。
但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，
另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，
所以简单的搜索树难以满足复杂的应用场景
"""

磁盘io与预读

"""
前面提到了访问磁盘，那么这里先简单介绍一下磁盘io和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、
旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，
比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，
一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘io的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，
但要知道一台500 -mips（million instructions per second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次io的时间可以执行约450万条指令，
数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

考虑到磁盘io是非常高昂的操作，计算机操作系统做了一些优化，当一次io时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，
因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次io读取的数据我们称之为一页(page)。
具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次io，这个理论对于索引的数据结构设计非常有帮助。
"""

索引的数据结构 树

"""
树状图是一种[数据结构]，它是由n（n>=1）个有限结点组成一个具有层次关系的[集合],把它叫做“树”是因为它看起来像一棵倒挂的树，
也就是说它是根朝上，而叶朝下的

树（tree）是由多个节点（node）的集合组成，每个节点又有多个与其关联的子节点（child node）。子节点就是直接处于节点之下的节点，
而父节点（parent node）则位于节点直接关联的上方。树的根（root）指的是一个没有父节点的单独的节点。

所有的树都呈现了一些共有的性质：
只有一个根节点；
除了根节点，所有节点都有且只有一个父节点；
无环。将任意一个节点作为起始节点，都不存在任何回到该起始节点的路径。（正是前两个性质保证了无环的成立。）
"""
#下图
根结点 : a   

父节点 : a是b,c的父节点

叶子节点：d,e是叶子节点

树的深度/树的高度：高度为3

类别	树名称
二叉查找树（binary search tree）	二叉查找树，笛卡尔树，t 树
自平衡二叉查找树（self-balancing binary search tree）	aa 树，avl 树， 红黑树（red-black tree), 伸展树（splay tree）
b 树（b-tree）	2-3 树，2-3-4 树， b 树，b+ 树，b* 树
字典树（trie-tree）	后缀树，基数树，三叉查找树，快速前缀树
空间数据分割树（spatial data partitioning tree）	r 树，r+ 树，r* 树， 线段树，优先 r 树

• 树中的术语

根（root）：树中最顶端的节点，根没有父节点。
子节点（child）：节点所拥有子树的根节点称为该节点的子节点。
父节点（parent）：如果节点拥有子节点，则该节点为子节点的父节点。
兄弟节点（sibling）：与节点拥有相同父节点的节点。
子孙节点（descendant）：节点向下路径上可达的节点。
叶节点（leaf）：没有子节点的节点。
内节点（internal node）：至少有一个子节点的节点。
度（degree）：节点拥有子树的数量。
边（edge）：两个节点中间的链接。
路径（path）：从节点到子孙节点过程中的边和节点所组成的序列。
层级（level）：根为 level 0 层，根的子节点为 level 1 层，以此类推。
高度（height）/深度（depth）：树中层的数量。比如只有 level 0,level 1,level 2 则高度为 3。

二叉树

二叉树是一种很经典的数据结构,二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。
二叉树常被用于实现二叉查找树和二叉堆。二叉树有如下特性：
1、每个结点都包含一个元素以及n个子树，这里0≤n≤2。
2、左子树和右子树是有顺序的，次序不能任意颠倒。左子树的值要小于父结点，右子树的值要大于父结点。

• 光看概念有点枯燥，假设我们现在有这样一组数[35 27 48 12 29 38 55]，顺序的插入到一个数的结构中，步骤如下:

这就是一棵二叉树啦！我们能看到，经通过一系列的插入操作之后，原本无序的一组数已经变成一个有序的结构了，并且这个树满足了上面提到的两个二叉树的特性！

# 但是如果同样是上面那一组数，我们自己升序排列后再插入，也就是说按照[12 27 29 35 38 48 55]的顺序插入，会怎么样呢？

平衡二叉树

平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉树的两个特性，同时还有一个特性：
它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

前面[35 27 48 12 29 38 55]插入完成后的图，其实就已经是一颗平衡二叉树啦。

那如果按照[12 27 29 35 38 48 55]的顺序插入一颗平衡二叉树，会怎么样呢？我们看看插入以及平衡的过程：

"""
这棵树始终满足平衡二叉树的几个特性而保持平衡！这样我们的树也不会退化为线性链表了！
我们需要查找一个数的时候就能沿着树根一直往下找，这样的查找效率和二分法查找是一样的呢！

一颗平衡二叉树能容纳多少的结点呢？这跟树的高度是有关系的，假设树的高度为h，那每一层最多容纳的结点数量为2^(n-1)，
整棵树最多容纳节点数为2^0+2^1+2^2+...+2^(h-1)。这样计算，100w数据树的高度大概在20左右，那也就是说从有着100w条数据的平衡二叉树中找一个数据，
最坏的情况下需要20次查找。如果是内存操作，效率也是很高的！但是我们数据库中的数据基本都是放在磁盘中的，每读取一个二叉树的结点就是一次磁盘io，
这样我们找一条数据如果要经过20次磁盘的io？那性能就成了一个很大的问题了！那我们是不是可以把这棵树压缩一下，让每一层能够容纳更多的节点呢？虽然我矮，但是我胖啊...
"""

b树

这颗矮胖的树就是b-tree，注意中间是杠精的杠而不是减，所以也不要读成b减tree了~
那b-tree有哪些特性呢？一棵m阶的b-tree有如下特性：

1、每个结点最多m个子结点。
2、除了根结点和叶子结点外，每个结点最少有m/2（向上取整）个子结点。
3、如果根结点不是叶子结点，那根结点至少包含两个子结点。
4、所有的叶子结点都位于同一层。
5、每个结点都包含k个元素（关键字），这里m/2≤k<m，这里m/2向下取整。
6、每个节点中的元素（关键字）从小到大排列。
7、每个元素（关键字）字左结点的值，都小于或等于该元素（关键字）。右结点的值都大于或等于该元素（关键字）。

下面我们以一个[0,1,2,3,4,5,6,7]的数组插入一颗3阶的b-tree为例，将所有的条件都串起来，就明白了.

那么，你是否对b-tree的几点特性都清晰了呢？在二叉树中，每个结点只有一个元素。但是在b-tree中，
每个结点都可能包含多个元素，并且非叶子结点在元素的左右都有指向子结点的指针。

如果需要查找一个元素，那流程是怎么样的呢？我们看下图，如果我们要在下面的b-tree中找到关键字24，那流程如下:

从这个流程我们能看出，b-tree的查询效率好像也并不比平衡二叉树高。但是查询所经过的结点数量要少很多，
也就意味着要少很多次的磁盘io，这对性能的提升是很大的。

前面对b-tree操作的图我们能看出来，元素就是类似1、2、3这样的数值，但是数据库的数据都是一条条的数据，
如果某个数据库以b-tree的数据结构存储数据，那数据怎么存放的呢？
#我们看下一张图

普通的b-tree的结点中，元素就是一个个的数字。但是上图中，我们把元素部分拆分成了key-data的形式，key就是数据的主键，
data就是具体的数据。这样我们在找一条数的时候，就沿着根结点往下找就ok了，效率是比较高的。

b+树

b+tree是在b-tree基础上的一种优化，使其更适合实现外存储索引结构。b+tree与b-tree的结构很像，但是也有几个自己的特性：

1、所有的非叶子节点只存储关键字信息。
2、所有卫星数据（具体数据）都存在叶子结点中。
3、所有的叶子结点中包含了全部元素的信息。
4、所有叶子节点之间都有一个链指针。

  如果上面b-tree的图变成b+tree，那应该如下：

#大家仔细对比于b-tree的图能发现什么不同？
  1、非叶子结点上已经只有key信息了，满足上面第1点特性！
  2、所有叶子结点下面都有一个data区域，满足上面第2点特性！
  3、非叶子结点的数据在叶子结点上都能找到，如根结点的元素4、8在最底层的叶子结点上也能找到，满足上面第3点特性！
  4、注意图中叶子结点之间的箭头，满足满足上面第4点特性！

"""
我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘io次数控制在一个很小的数量级，
最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（b+树是通过二叉查找树，再由平衡二叉树，b树演化而来）。
"""

• b+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道io次数取决于b+数的高度h，假设当前数据表的数据为n，
每个磁盘块的数据项的数量是m，则有h=㏒(m+1)n，当数据量n一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，
磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，
即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，
一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，
比如当(张三,20,f)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，
最后得到检索的数据；但当(20,f)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，
必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,f)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，
所以只能把名字等于张三的数据都找到，然后再匹配性别是f的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引与辅助索引

在数据库中，b+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次io，这倒不错。
因为当前一般的机械硬盘每秒至少可以做100次io，2~4次的io意味着查询时间只需要0.02~0.04秒。

数据库中的b+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是b+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息

• 聚集索引

#innodb存储引擎表是索引组织表，即表中数据按照主键顺序存放。
而聚集索引（clustered index）就是按照每张表的主键构造一棵b+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同b+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
#如果未定义主键，mysql取第一个唯一索引（unique）而且只含非空列（not null）作为主键，innodb使用它作为聚簇索引。
    
#如果没有这样的列，innodb就自己产生一个这样的id值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

#由于实际的数据页只能按照一棵b+树进行排序，因此每张表只能拥有一个聚集索引。
在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在b+树索引的叶子节点上直接找到数据。
此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

• 聚集索引的好处

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，
查询最后的10位用户信息，由于b+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

#参照第六小结测试索引的准备阶段来创建出表s1
mysql> desc s1; #最开始没有主键
+--------+-------------+------+-----+---------+-------+
| field  | type        | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | no   |     | null    |       |
| name   | varchar(20) | yes  |     | null    |       |
| gender | char(6)     | yes  |     | null    |       |
| email  | varchar(50) | yes  |     | null    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> explain select * from s1 order by id desc limit 10; #using filesort，需要二次排序
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | simple      | s1    | null       | all  | null          | null | null    | null | 2633472 |   100.00 | using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
row in set, 1 warning (0.11 sec)

mysql> alter table s1 add primary key(id); #添加主键
query ok, 0 rows affected (13.37 sec)
records: 0  duplicates: 0  warnings: 0

mysql> explain select * from s1 order by id desc limit 10; #基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | simple      | s1    | null       | index | null          | primary | 4       | null |   10 |   100.00 | null  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
row in set, 1 warning (0.04 sec)

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
mysql> alter table s1 drop primary key;
query ok, 2699998 rows affected (24.23 sec)
records: 2699998  duplicates: 0  warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| field  | type        | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | no   |     | null    |       |
| name   | varchar(20) | yes  |     | null    |       |
| gender | char(6)     | yes  |     | null    |       |
| email  | varchar(50) | yes  |     | null    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.12 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000; #没有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | simple      | s1    | null       | all  | null          | null | null    | null | 2690100 |    11.11 | using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);
query ok, 0 rows affected (16.25 sec)
records: 0  duplicates: 0  warnings: 0

mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | simple      | s1    | null       | range | primary       | primary | 4       | null | 1343355 |   100.00 | using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
row in set, 1 warning (0.09 sec)

• 辅助索引

表中除了聚集索引外其他索引都是辅助索引（secondary index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉innodb存储引擎去哪里可以找到与索引相对应的行数据。
由于innodb存储引擎是索引组织表，因此innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，
innodb存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，
那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑io访问才能得到最终的一个数据页。

聚集索引
1.纪录的索引顺序与无力顺序相同
   因此更适合between and和order by操作
2.叶子结点直接对应数据
 从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引

非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
   多个索引会影响insert和update的速度

mysql 常用的索引

• 索引功能

#1. 索引的功能就是加速查找
#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能  

• mysql 常用的索引

普通索引index：加速查找

唯一索引：
    -主键索引primary key：加速查找+约束（不为空、不能重复）
    -唯一索引unique:加速查找+约束（不能重复）

联合索引：
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
        
举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 int
会员姓名 varchar(10)
会员身份证号码 varchar(18)
会员电话 varchar(10)
会员住址 varchar(50)
会员备注信息 text

那么这个 会员编号，作为主键，使用 primary
会员姓名 如果要建索引的话，那么就是普通的 index
会员身份证号码 如果要建索引的话，那么可以选择 unique （唯一的，不允许重复）

#除此之外还有全文索引，即fulltext
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 index 也可以。
但其实对于全文搜索，我们并不会使用mysql自带的该索引，而是会选择第三方软件如sphinx，专门来做全文搜索。

#其他的如空间索引spatial，了解即可，几乎不用

• 索引的两大类型hash与btree

#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样
innodb 支持事务，支持行级别锁定，支持 b-tree、full-text 等索引，不支持 hash 索引；
myisam 不支持事务，支持表级别锁定，支持 b-tree、full-text 等索引，不支持 hash 索引；
memory 不支持事务，支持表级别锁定，支持 b-tree、hash 等索引，不支持 full-text 索引；
ndb 支持事务，支持行级别锁定，支持 hash 索引，不支持 b-tree、full-text 等索引；
archive 不支持事务，支持表级别锁定，不支持 b-tree、hash、full-text 等索引；

• 创建/删除索引的语法

#方法一：创建表时
    　　create table 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [unique | fulltext | spatial ]   index | key
                [索引名]  (字段名[(长度)]  [asc |desc]) 
                );


#方法二：create在已存在的表上创建索引
        create  [unique | fulltext | spatial ]  index  索引名 
                     on 表名 (字段名[(长度)]  [asc |desc]) ;


#方法三：alter table在已存在的表上创建索引
        alter table 表名 add  [unique | fulltext | spatial ] index
                             索引名 (字段名[(长度)]  [asc |desc]) ;
                             
#删除索引：drop index 索引名 on 表名字;

#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name) #index没有key
);
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index(name) #index没有key
);


#方式二
create index ix_age on t1(age);


#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

#查看
mysql> show create table t1;
| t1    | create table `t1` (
  `id` int(11) default null,
  `name` char(1) default null,
  `age` int(11) default null,
  `sex` enum('male','female') default null,
  unique key `uni_id` (`id`),
  key `ix_name` (`name`),
  key `ix_age` (`age`),
  key `ix_sex` (`sex`)
) engine=innodb default charset=latin1

测试索引

• 准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
begin
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
        set i=i+1;
    end while;
end$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\g 

#4. 调用存储过程
call auto_insert1();

• 在没有索引的前提下测试查询速度

#无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，
此时有多少个磁盘块就需要进行多少io操作，所以查询速度很慢
mysql> select * from s1 where id=333333333;
empty set (0.33 sec)

• 总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.myi

mysam索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照
b+tree（btree即balance true）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，
因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（myisam可以没有），如果没有显式定义，
则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，
这字段的长度为6个字节，类型为长整型.

正确的使用索引

• 索引未命中

"""
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

1 范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、大于号、小于号
"""

• 不等于！=

• between ...and...

• like

"""
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，
唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
"""
#先把表中的索引都删除，让我们专心研究区分度的问题
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| field  | type        | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | yes  | mul | null    |       |
| name   | varchar(20) | yes  |     | null    |       |
| gender | char(5)     | yes  |     | null    |       |
| email  | varchar(50) | yes  | mul | null    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> drop index a on s1;
query ok, 0 rows affected (0.20 sec)
records: 0  duplicates: 0  warnings: 0

mysql> drop index d on s1;
query ok, 0 rows affected (0.18 sec)
records: 0  duplicates: 0  warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| field  | type        | null | key | default | extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | yes  |     | null    |       |
| name   | varchar(20) | yes  |     | null    |       |
| gender | char(5)     | yes  |     | null    |       |
| email  | varchar(50) | yes  |     | null    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

先把表中的索引都删除，让我们专心研究区分度的问题

我们编写存储过程为表s1批量添加记录，name字段的值均为egon，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）

回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，
从小到大的顺序依次排开，即左1<左2<左3<...

而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，
字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，
name字段所有的值均为'egon'

#现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？

#1：如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'egon’），
所以查询速度很快

#2：如果条件正好是name='egon',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。
这与全表扫描的io次数没有多大区别，所以速度很慢

索引列不能在条件中参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，
b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

• and/or

#1、and与or的逻辑
    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    条件1 or 条件2:只要有一个条件成立则最终结果就成立

#2、and的工作原理
    条件：
        a = 10 and b = 'xxx' and c > 3 and d =4
    索引：
        制作联合索引(d,a,b,c)
    工作原理:
        对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，
加速查询，即按照d—>a->b->c的顺序

#3、or的工作原理
    条件：
        a = 10 or b = 'xxx' or c > 3 or d =4
    索引：
        制作联合索引(d,a,b,c)
        
    工作原理:
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

• 在左边条件成立但是索引字段的区分度低的情况下（name与gender均属于这种情况），会依次往右找到一个区分度高的索引字段，加速查询

经过分析，在条件为name='egon' and gender='male' and id>333 and email='xxx'的情况下，我们完全没必要为前三个条件的字段加索引，
因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率

最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，
有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

- 使用函数
    select * from tb1 where reverse(email) = 'egon';
            
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where email = 999;
    
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by
    select name from s1 order by email desc;
    当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
    select email from s1 order by email desc;
    特别的：如果对主键排序，则还是速度很快：
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 命中索引
    name                 -- 命中索引
    email                -- 未命中索引


- count(1)或count(列)代替count(*)在mysql中没有差别了

- create index xxxx  on tb(title(19)) #text类型，必须制定长度

- 避免使用select *
- 使用count(*)
- 创建表时尽量使用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
- 尽量使用短索引
- 使用连接（join）来代替子查询(sub-queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

满查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置sql_no_cache
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，
单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

参考文章(b树,b+树)

索引原理参考博客

作 者：

出 处：

声援博主：如果您觉得文章对您有帮助，可以点击文章右下角 【推荐】一下。您的鼓励是博主的最大动力！

自 勉：生活，需要追求；梦想，需要坚持；生命，需要珍惜；但人生的路上，更需要坚强。带着感恩的心启程，学会爱，爱父母，爱自己，爱朋友，爱他人。