MySQL索引

索引：

索引在MySQL中也叫是一种“键”，是存储引擎⽤于快速找到记录的一种数据结构。

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

磁盘IO与预读：

   磁盘预读：当一次IO时不光把他当前磁盘地址的数据，而是把相邻的数据也都读取到内存的缓冲区内。（一次磁盘IO对于CPU非常慢）

索引的数据结构

数据结构：数组、栈、链表、队列、树、图
树： 树状图是一种数据结构，它是由n（n>=1）个有限结点组成一个具有层次关系的集合。
深度：树的高度（“层数”）

特点： 每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树 B+树： （ 树 -- 二叉查找树 -- 平衡二叉树） INnodb -- 索引默认B+树* * * * 叶子节点：带指针

性质：索引字段要尽量小

B+树详解（有链接-详细解释-原地址）

               聚集索引和辅助索引（非聚集索引）

聚集索引：
优点：

对主键的排序查找和范围查找速度非常快，叶子结点数据就是用户所要查询的数据。
范围查询（即如果要查找主键某⼀范围内的数据，通过叶⼦节点的上层中间节点就可以得到⻚的范围，之后直接读取数据⻚即可）

辅助索引：
      除了聚集索引外都是辅助索引（区别聚集索引：辅助索引的叶子节点不包含行记录的全部数据。每个叶子节点中的索引行中还包含一个书签）

聚集索引	非聚集索引
纪录的索引顺序与无力顺序相同， 因此更适合between and和order by操作	索引顺序和物理顺序无关
|叶子结点直接对应数据， 从中间级的索引的索引行直接对应数据页	叶子结点不直接指向数据页
每张表只能创建一个聚集索引	每张表可以有多个非聚集索引，需要更多磁盘和内容，多个索引会影响insert和update的速度

 MySQL索引管理

功能：快速查找
         mysql中primary key，unique，联合唯一也都是索引，这些索引除去加速查找以外，还有约束功能

 

MySQL常用的索引：

 普通索引index：加速查找

唯一索引：主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
唯一索引UNIQUE:加速查找+约束（不能重复）

 联合索引：

PRIMARY KEY(id,name):联合主键索引

UNIQUE(id,name):联合唯一1索引

INDEX(id,name):联合普通索引

 索引的两大类型hash与btree：

hash类型：查询单条快，范围查询慢
btree类型：b+树，层数越多，数据量指数级增长（此时使用，因为innodb默认支持btree）

 

创建、删除索引-语法：

1.   创建表时：（直接加入）
2.   create index 索引名 on 表名 （字段名）
3.   alter table 表名 add index 索引名 （字段名）

 

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

#法1

create table t1(
 …………unique key uni_id(id),
 index(name) #index没有key
);
#法2
create index ix_age on t1(age);
#法3
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. 创建索引存在范围问题，条件不明确，条件中出现关键字：>、>=、<、<=、!= 、between...and...、*like
2. 范围大时间长，范围小时间短
3. like匹配字符中有%切处于开头时，速度慢
4. id字段索引，参与计算，无法得到一个明确的值去索引树中查找，每次都需要临时计算，会导致速度慢
5. 为字段添加索引，看字段的区分程度，程度高的可以加速查询
6. 无用索引会降低查询速度，可以删掉加快速度
7. 在建立索引时没有将条件中关于范围的字段添加到后面，查询速度很慢
8. 在建立索引时将条件中关于范围的字段添加到后面，查询速度很快
9. 避免使用select *
10. 使用count(*)
11. 创建表时尽量使用 char 代替 varchar 
12. 表的字段顺序固定长度的字段优先
13. 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更 适合使用组合索引）
14. 尽量使用短索引
15. 使用连接（JOIN）来代替子查询(Sub-Queries)
16. 连表时注意条件类型需一致
17. 索引散列值（重复少）不适合建索引，例：性别不适合