MYSQL笔记 之 索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

• mysql的索引分为单列索引(主键索引,唯一性索引,普通索引)和组合索引。
• 单列索引:一个索引只包含一个列,一个表可以有多个单列索引.
• 组合索引:一个组合索引包含两个或两个以上的列

一、索引的创建

---

1. 单列索引

1.1 普通索引,这个是最基本的索引。

/*索引名应是 字段_index  */
/*写法一,用这个*/
create index 索引名 on 表名(字段);   
/*写法二*/
alter table 表名 add index 索引名（字段）；    

1.2 唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

/*写法一,用这个*/
create [unique|fulltext|spatial] index 索引名 on 表名(字段名 [长度] [asc|desc]);

1.3 主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

/*写法一,用这个*/
create [unique|fulltext|spatial] index 索引名 on 表名(字段名 [长度] [asc|desc]);

2. 组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称
如果你建立了 组合索引(nickname_account_createdTime_Index)
那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)

create index 索引名 on 表名(字段名,字段名,...);  

二、最左前缀 !!!重点

---

创建组合索引后最左边那个字段就是最左前缀
组合索引(nickname_account_createdTime_Index)

1. 不按索引最左列开始查询（多列索引）

   例如index(‘c1’, ‘c2’, ‘c3’) 
   where ‘c2’ = ‘aaa’ /*不使用索引*/
   where 'c2' = 'aaa' and 'c3' = 'sss' /*不能使用索引*/
   

2. 查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）

   Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ /*该查询只会使用索引中的前两列,因为like是范围查询*/
   

3. 不能跳过某个字段来进行查询,这样利用不到索引
   比如我的sql 是

   explain select * from `award` 
   where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822; 
   那么这时候他使用不到其组合索引.
   

因为我的索引是 (nickname, account, created_time),如果第一个字段出现 范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引,利用的索引是(nickname)
因为上面说了建立组合索引(nickname, account, created_time), 会出现三个索引。

Mysql 5.7版本后，组合索引只要使用到最左前缀查询就会使用组合索引，并且会自动按照组合查询字段排序处理
当用组合索引字段查询时：
mysql会自动将查询条件按照组合索引字段顺序排序处理
组合索引范围查询如下：（userLoginAccount ，userAge ，userName）

explain select * from user u where u.userloginaccount = '12333' and userage = '18' and u.username > '321'

等同于（均为userage未使用索引）

explain select * from user u where u.userloginaccount = '12333' and u.username > '321' and userage = '18'

三、聚集索引与非聚集索引 （了解）

---

3.1 聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。
聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引（一般指主键值不连续，且分布范围不均匀），如使用UUID来作为聚集索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。

3.2 非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在B-Tree中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上建立的索引就是非聚集索引。

四、覆盖索引

---

如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引。
因为要查询的字段（nickname, account, created_time）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

五、如何使用索引来排序 重点

---

在排序操作中如果能使用到索引来排序，那么可以极大的提高排序的速度，要使用索引来排序需要满足以下两点即可。

1. order by子句后的列顺序要与组合索引的列顺序一致
2. 5.7版本只要使用到组合索引中的“最左前缀”即可使用索引

代码演示:

以下代码都可以使用到组合索引
(t.phone),(t.address),(t.age)

desc select t.phone from test_big_t t where t.phone = '16600765212';    --    效率：93  查询时间：0.017s  查询行数：1
desc select t.phone,t.address,t.age from test_big_t t  group by t.phone;  --  效率：191  查询行数：28087125    OK
desc select t.phone,t.address,t.age from test_big_t t  group by t.phone, t.address; --  效率：191  查询行数：28087125    OK
desc select t.phone,t.address,t.age from test_big_t t group by t.phone desc, t.address desc; --效率：191 查询行数：28087125  OK
desc select t.phone,t.address,t.age from test_big_t t where t.phone = '16600765212' group by t.address; --  效率：90  查询行数：1    OK

注：第4条比较特殊一点，如果where查询条件为索引列的第一列，且为常量条件，那么也可以使用到索引
– 排序列的方向虽然不一致,但是 select 后面的字段为索引中字段

desc select t.phone,t.address,t.age from test_big_t t  group by t.phone asc, t.address desc; --  效率：19 查询行数：28087125  NO

无法使用索引排序的案例
– sex 字段不在索引中

desc select t.phone,t.address,t.age from test_big_t t group by t.phone, t.sex; -- 效率:0  查询行数：28087125  NO
desc select t.phone, t.address, t.age,from test_big_t t whele t.phone like '16600765%' group by t.address;

六、索引的删除

---

drop index 索引名 on 表名;

七、索引的优点

---

1. 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2. 建立索引可以大大提高检索的速度,以及减少表的检索行数
3. 在表连接的连接条件 可以加速表与表直接的相连
4. 在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
5. 建立索引,在查询中使用索引可以提高性能

八、使用索引的缺点

---

1. 在创建索引和维护索引会耗费时间,随着数据量的增加而增加
2. 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3. 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但*如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

九、什么情况下不创建索引

---

1. 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2. 很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3. 定义为text和image和bit数据类型的列不应该增加索引,
4. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

十、什么情况下索引会失效

---

1. 不能再索引列上做任何操作（计算，函数或者类型状态），会导致索引失效
2. 组合索引要遵守“最左前缀”原则，否则索引无法使用
3. mysql在**使用不等于（!=、<>）**时，索引失效
4. is null,is not null也无法使用索引
5. like使用时以通配符开始（%xxx），mysql索引也会失效
6. 字符串不加单引号索引也会失效
7. 减少用or，用它连接时索引会失效。