再聊索引
目录
前言
尽量减少访问磁盘的次数是数据库设计的重要原则之一；
每个索引在innodb里都是一颗B+树，一张表由多棵B+树组成，B+树很好的配合了磁盘的读写性能。并且索引文件和数据文件都是存储在磁盘上的。那么这些数据的存储格式是怎样的呢？
问题导读：
-
为什么推荐使用自增的主键id做索引，不推荐无序的UUID和身份证号等建立索引？
-
为什么频繁更新的字段不推荐做索引？
-
索引为啥不存储主键的地址而是存储值呢，这样不浪费空间吗？
-
索引结构的选型，为什么选择B+ 树作为索引存储结构？
-
索引字段的选择？
数据逻辑存储结构
这里需要了解一下Innodb存储引擎的逻辑存储结构，其结构从大到小依次划分为：
表空间，
段(Segment)，
区也称为
簇(Extent)，
页(Page)也称为块
，行(Row)。
其结构图如下：
在Innodb中又一个很重要的概念：
页
结构数据与关系
-
Innodb中一个 簇中有64个连续的页，大小为： 1M；
-
一个 页page的大小为：16K；InnoDB 存储引擎磁盘管理的最小单位，可以通过 innodb_page_size 设置；
-
如果一 行的数据大小为1kb，那么一页就可以存储16行数据记录；如果一个页面用完，就会产生一个新的页，一个簇用完就会分配一个新的簇；如果数据不是连续，往一个已经写满的页中继续写入数据，就会导致 页分裂；数据过少，删除的时候就会导致 页合并；
-
磁盘扇区：512Byte。（数据库访问一次磁盘大约为10ms）
-
一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个 表空间最多存储 64TB 的数据。
tips：在
文件系统中也有页的概念，和内存打交道，最小的单位也是
page,文件系统的页大小为：4K
问题剖析
对于
问题1、2都有一个共同点：因为B+树是通过合并和分页来保证树的高度和平衡的，所以如果顺序递增的节点或者是不经常更新增加删除的节点就会
避免页分裂和合并，而分裂和合并这个操作是极其消耗性能的。
问题1: 表中设置主键
不推荐使用订单号或者UUID作为主键的原因？
-
顺序插入（有业务逻辑的字段插入非有序，成本高)，追加操作，效率高，不用挪数据；
-
没有页裂；
-
二级索引的叶子结点占用的空间小，节约空间；(例如：int和字符串的身份证号，显然int节约内存)
问题3
:因为 b+树是通过分页和合并来保持树的平衡，地址汇发生改变，所以不能存储地址；；
-
对于顺序递增的节点的优势：
-
顺序递增的索引添加新的索引节点的时候会顺序添加到当前节点索引的后续位置，一页写满自动开辟新页，形成一个紧凑的索引结构；
-
插入的效率高，不会增加很多开销在索引维护上；
-
对于随机无序的主键ID就会导致：
-
随机读写；
-
页分裂；
|
索引的结构的选型？
|
尽量减少访问资源的次数是数据库设计的重要原则之一；
|
|
数组
|
优点：
缺点：插入和删除效率低下0(N)；
适用场景：静态存储引擎[不需要修改的数据]；
|
|
二叉查找树（本质链表）
|
缺点：
|
|
哈希表
|
数据结构：数组+链表的方式；
优点：等值查询的速度比较快O(1)；
缺点：
适用场景：membercache，redis和noslq等一些存储引擎；
|
|
平衡二叉树
|
缺点：
优点：查找效率高：O(logN)
|
|
多路平衡二叉树 -B树-(balance-Tree)
|
优点：
缺点：不能满足顺序查找；
|
|
B+树
|
优点：
缺点：通过分页和合并来保证树的平衡；
|
问题4：那为啥选用B+树呢？
举个栗子:假设一条记录是 1K，一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针?
分析：
假设索引字段是 bigint 类型，长度为 8 字节。数据库中页的大小为：16kb=16384byte，指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。
非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表有 1170 个指针。
树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170*1170*16=21902400=2190w。
在查找数据时一次页的查找代表一次 IO，也就是说，
一张 2000 万左右的表，查询 数据最多需要访问 3 次磁盘
。
由此可见B+树突出的磁盘io性能，
所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。
附加重要特性：范围查询
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数 据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
检索方式
：
它是根据左闭右开的
区间 [ )来检索数据
。
时刻注意：
数据库的设计原则之一就是尽量减少磁盘io，这也是sql优化的top1原则。
问题5：索引字段的选择：离散度
-
字段离散度低：扫描的行数就多，所以索引应该建立在离散度大的字段上；
-
字段离散度的计算：count(distinct（count(field) )):distinct(*)
极限思维：离散度极低，所有的数据都一样，索引首先是全表扫描整颗B+树，然后索引文件还浪费了存储空间；
问题6：
为什么不建议使用长的字符串做索引？
过长的索引如何建立索引？
索引是一棵单独落盘的B+树，浪费空间。如果过长可以采用以下方案：
-
完整索引：直接创建 完整索引，这样可能比较占空间；
-
前缀索引：创建前缀索引，节省空间，但是会 增加扫描次数，并且不能使用覆盖索引；因为系统无法确定该索引是截取的还是完整的；
-
倒序前缀索引 ：先倒序存储，例如省份证只有后面的几位不同，再创建前缀索引，用于绕过字符串本身前缀区分读不够的问题；
-
hash索引：创建 hash字段索引，查询性能稳定，有额外的存储和消耗。
-
fullTest只有myisam支持，innodb不支持；
索引的优点：
-
减少访问磁盘的次数，提高检索速度；
-
帮助服务器避免排序和临时表
-
可以将随机io变为顺序io；
代码常用小技巧
-
把聚合排序和计算放到java业务层，让sql更简单，更高效返回；
-
limit 的使用: 避免limit 100000,100 ， 扫描行数过多。使用where id > 1000 limit 100；
-
避免select *，返回尽量少的字段，按需查询，有可能用上覆盖索引；
-
避免一次查询过多数据，数据量较大时一定要做分页；
-
如果明确只有一条返回在sql后面加上limit 1；
-
update操作，尽量避免使用通用的方法，比如update User , 用明确的updateStatus、updateUserName
-
尽量使用count(*)，不要使用count(字段)/(id)/(1) ;
常见索引失效避坑
-
隐式转换；
-
sql语句中对索引列进行函数和算术运算；
-
使用负向查询：不等于:<> not in ,!=,not exist;
-
like "%xxx";
-
前缀索引导致的覆盖索引失效；
失效情况举例：
（1）．
隐式类型转
换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
如果表字段是int类型，传入的是string类型，索引不会失效；
如果表字段是char类型，传入的是int类型，会发生类型转换导致索引失效；
（2）. 对索引列进
行运算
导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where
id-1=9;
正确的例子：select * from test where id=10;
（3）. 以下使用会使索引失效，应避免使用；
-
a. 使用 <> 、not in 、not exist、!=
-
b. like "%_" 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
-
c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个 列被where子句引用时，优化器才会选择使用该索引。
-
d. 字符型字段为数字时在where条件里不添加引号.
-
e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
结束
sql的优化核心：
减少数据库的磁盘io次数
。
乃至于整个系统的优化核心最终都是为了保证磁盘io次数尽量少；
而索引占着绝对的地位。