sholisa,卖春堂,gamye
本篇博客内容为索引，索引是为了提高数据库的查询效率。
索引就相当于书的目录，是 mysql 中一种专门的数据结构，称为 key，索引的本质原理就是通过不断地缩小查询范围，来降低 io 次数从而提升查询性能。强调：一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据。
对于一个应用来说，对数据库的读写比例基本上是10：1，即读多写少。而且对于写来说极少出现性能问题，大多数性能问题都是慢查询提到加速查，这时候就必须用到索引。
磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms 以下；旋转延迟就是磁盘转速花费的时间，比如一个磁盘7200转，表示每分钟能转7200次，也就是一秒钟能转120次，旋转延迟就是1/120/2=4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间来说可以忽略不计。那么访问一次磁盘的时间，即一次磁盘 io 的时间约等于5+4.17=9ms 左右，看起来还挺短的，但是对于现在的计算机来说，一秒钟可以执行5亿条指令，执行一次 io 的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级别数据，每次9毫秒的时间，很显然会造成大量的等待时间。下图是计算机硬件延迟的对比图：
考虑到磁盘 io 是非常高昂的操作，计算机操作系统做了一些优化，当一次 io 时，不光把当前地址的数据，而是把相邻的数据也都读取到内存缓冲区，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次 io 读取的数据称之为一页（page）。具体一页有多大数据跟操作系统有关，一般为4k 或8k，也就是我们读取一页内的数据时候才发生了一次 io。
对于索引来说，它的目的就是降低查找数据时产生的磁盘 io 数量级，最好是常数数量级。索引使用的是 b+树（b+树是通过二叉查找树，再由平衡二叉树，b 树演化而来）。
如上图，是一颗 b+数，浅蓝色的块称之为一个磁块，可以看到每个人磁块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘快1包含数据项17和35，包含指针 p1、p2、p3，p1表示小于17的磁盘块，p2表示在17和35之间的磁盘块，p3表示大于35的磁盘块。真实数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不过存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
如果所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次 io，在内存中用二分法查找确定29在17和35之间，锁定磁盘块1的 p2指针，内存时间因为非常短（相比磁盘 io）可以忽略不计，通过磁盘块1的 p2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第三次 io，同时内存中做二分查找找到29，结束查询，总计三次 io。真实情况是，3层的 b+数可以表示上百万的数据，如果上百万的数据查找只需要三次 io，性能提升将是巨大的，如果没有索引，每个数据项都要发生一次 io，那么总共需要百万次的 io，显然成本非常非常高。
在数据库中，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+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。
表中除了聚集索引外其他索引都是辅助索引，也称为非聚集索引，与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签。该书签用来告诉 innodb 存储引擎去哪里可以找到与索引相对应的行数据。
由于 innodb 存储引擎是索引组织表，因此 innodb 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。如下图：
辅助索引的存在并不影响数据再聚集索引中的组织，因此每张表中可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后通过主键索引来找到一个完整的行记录。
举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行三次查找，最终找到一个完整的行数据所在的页，因此一共需要6次 io 访问才能得到最终的一个数据页。
mysql> create table 表名(
字段名1 数据类型 [完整性约束条件...],
字段名2 数据类型 [完整性约束条件...],
[unique | fulltext | spatial] index | key
[索引名] (字段名[(长度)] [asc | desc]));
mysql> create [unique | fulltext | spatial ] index 索引名
on 表名 (字段名[(长度)] [asc | desc]);
mysql> alter table 表名 add [unique | fulltext | spatial ] index
索引名(字段名[(长度)] [asc | desc]);
mysql> drop index 索引名 on 表名;
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个字节，类型为长整型.
并不是说创建了索引就一定会加快查询速度，若想利用索引达到预期的提高查询速度的效果，我们在添加索引时，必须考虑以下问题：
范围问题，或者说条件不明确，条件中出现这些符号：>、>=、<、<=、!=、between and、like
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 均属于这种情况)，会依次往右找到一个区分度高的索引字段，加速查询
最左前缀匹配原则，非常重要的原则，对于组合索引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 * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（join）来代替子查询(sub-queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
新手学习Python2和Python3中print不同的用法
Python基于os.environ从windows获取环境变量
网友评论