Mysql——索引

索引

1. 建立起一个在存储表阶段就有的一个存储结构能在查询的时候加速。

2. 提高读的速度，降低写的速度。

3. 读写比例为10:1

索引原理：

因软件时听过操作系统来进行对硬盘的读取工作（硬盘7200转，表示每分钟硬盘转7200圈）如果软件每次提取数据都只提取一点（像提取文件，每次只提取一行），那么系统和硬盘的负载会非常大（硬盘提取一般会花费0.095ms时间完成，这时间如果让CUP运行它会运行50000条CPU指令或10000条的python语句），所以操作系统会将后续的数据进行预读。所提取的大小称之为block块，一个块为4096字节，windows一般提取2个block块，mysql提取4个block块。

数据库的树形数据结构：

树形结构是指数据库在存储数据时，以特定关键值为中心进行向下扩散，以切割的方式将数据切割成最直接，最方便，耗费最小block块将数据进行提取。

1. 由根（root），分支（branch），叶子（leaf）构成

2. 根和叶子是必须有的

3. 只能有一个根和一层叶子，可以有多层分支。

4. 叶子为最外层结构

5. 树的高度是指数结构的层数。

 

数的层数决定了block块提取的速度，所以再基本树的情形下，发展出来b+树，尽量让层数控制在3层以内。

平衡树，B+树

1. 分支节点和根节点都不在存储实际的数据了，让分支和根节点能存储更多的索引信息，就降低了数的高度。

2. 在叶子节点之间加入了双向的链式结构，方便在查询中可以平向查找。

mysql当中所有b+树索引的高度都基本控制在3层，这样使得提取block块是大大节省了提取速度。让读取速度飞起来

聚集索引和辅助索引：

聚集索引：

1. 聚集索引只存放设置为主键的关键信息，因此每张表中只能由一个聚集索引，primery key 自动生成聚集索引，非空，唯一

2. 在innodb中，聚集索引和辅助索引并存，即设置主键的为聚集索引，而非主键的为辅助索引。

3. 聚集索引数据直接存储在树结构的叶子节点中。

4. 纪录的索引顺序与无力顺序相同因此更适合between and和order by操作

辅助索引：

1. 叶子节点除了包含键值以外，还包含了一个书签（bookmark），该书签告诉innoDB去哪个区域来进行数据的提取

2.myisam中只有辅助索引，没有聚集索引，所以它有3张表。

3. 每张表可以有多个非聚集索引，需要更多磁盘和内容多个索引会影响insert和update的速度

innoDB使用聚集索引和辅助索引：

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

索引的配置：

1. primary key主键，自动生成聚集索引

2. unique 自带辅助索引

3. index 手动配置辅助索引

添加：create index 索引名称一般用：ind_名字 on 表（字段）;

删除：drop index 索引名 on 表名;

在不用索引时，最好删除掉相应的索引，因为设置多个无用索引后，会拖慢写入数据的速度。

 索引设置和未设置对比:

 

索引不生效的原因：

要查询的数据范围太大，可能涉及到每个数据的查询。

1. <,>,<=,>=,!=,条件列进行运算：select * from where id*10 = 10000;

2. between and：最好使用索引字段进行查找：select * from 表 where id between 100 and 200;

3. like：结果的范围大，索引不生效。精确查找在模糊查找前的快，反之慢。

4. and: and条件两端的内容：优先选择一个有索引的，并且树形结构更好的，尽量缩小范围后，再进行另一个的查找

5. or：or条件不会进行优化的，如果想命中那么两个列都有索引

联合索引：

cereate index ind_mix on s1(id,email)

联表查询时会多个值一起进行查询，这里就涉及到一个最左前缀原则：

即：创建联合索引时第一项为必选项，每一次查都要带上此字段一起查，才会命中索引（像id字段为第一项），那么就需要每次都带上。如果不带则无法命中索引。

什么时候使用联合索引，什么时候使用单列索引比较好：

1.值对A或带有A进行的查询，使用联合索引。

2. 不会对B，C进行单独查找时使用联合索引。

3. 选择一个区分度高的，使用单列索引

4. 条件的范围尽量小的，使用单列索引

5. 使用and作为条件连接符时，使用单列索引。

索引合并：

对两个字段分别创建索引，由于sql的条件上两个索引同时生效了，那么这个时候这两个索引就成了合并索引，跟联合索引时有区别的。

索引其他注意事项：

1. 避免使用select * 

2. 使用count（*）

3. 创建表时尽量使用char，代替varchar

4. 表的字段顺序固定长度的字段优先

5. 组合索引代替多个单利索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时，更适合使用组合索引）

6. 尽量使用短索引

7. 使用连接（join）来代替子查询（sub_querise）

8. 联表时注意条件类型需一致

9. 索引散列值重复少，不适合建立索引（例如：性别）