mysql数据库索引

1.数据库索引是什么？

为了加速对表中数据行的检索而创建的一种分散存储的数据结构。
单独的、存储在磁盘上的数据库结构，包含对数据表里所有记录的引用指针。

2.为什么要用索引?

MySQL 数据库存储数据最终是以文件的形式存储到硬盘的。一般来说，我们在程序中使用的时候肯定要把磁盘文件中的数据读到内存中也就是要进行磁盘IO。而磁盘 IO 是非常高昂的操作
●索引能极大的减少存储引擎需要扫描的数据量。
●索引能把随机I0变成顺序I0
●在进行分组与排序时索引可以让我们避免使用临时表
顺序IO：指读取和写入操作基于逻辑块逐个连续访问来自相邻地址的数据
随机IO:指读写操作时间连续，但访问地址不连续，随机分布在磁盘LUN的地址空间中。

为什么不每一列都创建索引
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

简单来说如下表：

3.mysql索引的原则

（1）列的离散型：count(distinct col) 重复的列
离散性高越好，选择越好
（2）最左匹配原则
从左到右依次匹配且不能跳过

4. MySQL为什么选择B+tree

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高很多，所以索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。而B+Tree，经过改进可以有效的利用系统对磁盘的块读取特性，在读取相同磁盘块的同时，尽可能多的加载索引数据，来提高索引命中效率，从而达到减少磁盘IO的读取次数。
这里有一个创建二叉树或者b树的网站，可以自己去玩一下
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
网站样子如下：

（1）二叉查找树
规则:
(1）非叶子节点只能允许最多两个子节点存在。
（2）每一个非叶子节点数据分布规则为左边的子节点小当前节点的值，右边的子节点大于当前节点的值
从0到9

缺点：
搜索效率不足:
一般来说，树结构中数据的深度决定它的搜索io次数

（2）平衡二叉树
规则：
（1）非叶子节点最多拥有两个子节点；
（2）非叶子节值大于左边子节点、小于右边子节点；
（3）树的左右两边的层级数相差不会大于1;
（4）没有值相等重复的节点;
缺点：
节点数据内容太少
每一个磁盘块（节点/页）保存的关键字数据量太少，没有很好利用操作系统和磁盘数据的交换特性（最少4BK）和磁盘预读能力（空间局部性原理）

（3）B tree（多路平衡查找树）
规则：
（1）排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；
（2）子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；
（3）关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数 如ceil(1.1)结果为2);
（4）所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;
从1到10


缺点：
非叶子节点，保存了数据区，减少了关键字（相对于B+tree）

（4）B+tree（加强版多路平衡查找树）
非叶子节点只存储指引搜索方向的数据项，真实的数据存在叶子节点中；
规则：
（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
（2）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
（3）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
（4）非叶子节点的子节点数=关键字数
从1到10

通过比较B数的数据结构，得出MySQL为什么选择B+tree
1）·B+树是B树的加强版，它拥有B树的优势
2）·B+树扫库，扫表能力更强
3）·B+树的磁盘读写能力更强
4）·B+树的排序能力更强
5) ·B+树的查询效率更加稳定

5.可能用到索引的地方：

where 子句，order by，group by

6.不需要创建索引的情况：

1. 表比较小
   2.赋值有限的列（枚举），不要创建索引。创建的索引返回的行越少越好，此时区分度大。
   3.用不上索引的列，不要创建索引。
   4.长字符串的列，不要全部创建索引，但可以使用短索引（名字的头8个字符）。
   索引的创建根据离散性创建索引

7.索引的种类

（1）普通索引
（2）唯一索引 （不允许其中任何两行具有相同索引值的索引。）
（3）主键索引：数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键
（4）组合索引
（5）全文索引

8.如何创建索引

创建表的时候创建索引：【create table 表名(字段名称 数据类型,unique|fulltext|spatial index 索引名(索引字段名(索引长度)|…));】
在已有表上增加索引：【alter table 表名 add unique|fulltext|spatial index 索引名(索引字段名(索引长度)|…);】
在已有表上创建索引：【create unique|fulltext|spatial index 索引名on表名(索引字段名(索引长度)|…);】
删除索引：【alter table 表名drop index 索引名;】
删除索引：【drop index索引名on 表名;】
1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

创建联合索引
create index idx_name_cardnum on user_info(name,cardNum)

9.常见的索引问题

1）.索引不是越多,越全越好.
2）.索引列的数据长度，能少则少
3）.like 99%，like 9%999, like 9%999%能不能用到索引?
以%开头的一定不会走索引，会直接扫描全表。非%开头可能走索引，也可能不走索引。
4）.not in .<>不能使用到索引。
5）. is not null 或 is null 索引会失效
6）.尽量使用Select col,而不使用Select *