MySQL索引介绍

比如我们要在字典中找某一字，如何才能快速找到呢？那就是通过字典的目录。

对数据库来说，索引的作用就是给‘数据’加目录。创建所以的目的就是为了提高查询速度

索引 - 键值对 - key(索引列的值) - value(记录的物理地址)

select * from xxx where id = 1;

先到索引文件中寻找key为id=1的key,然后再找到value[物理地址] - 时间的消耗.

然后根据这个物理地址直接导航到具体的某一行.

索引算法

1. btree(二叉树)索引 log₂N
2. hash(哈希)索引 1
3. B+Tree

优缺点

1. 好处:加快了查询速度(select )
2. 坏处:降低了增,删,改的速度(update/delete/insert),增大了表的文件大小(索引文件甚至可能比数据文件还大)

索引类型

1. 普通索引(index)：仅仅是加快了查询速度
2. **唯一索引(unique)：**行上的值不能重复
3. **主键索引(primary key)：**不能重复
4. 全文索引(fulltext):仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
5. 组合索引[覆盖索引]：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

索引语法

创建索引总览

CREATE TABLE table_name(
    [col_name data type]
	[unique|fulltext][index|key] [index_name](col_name[length]) [asc|desc]
)

-- 唯一索引
-- 第一种方式 - 添加唯一性约束 - 自动添加唯一索引
-- 第二种方式
create table tt01(
	id int(7),
    name varchar(20),
    -- 如果没有设置索引的名称,那么索引的名称默认和列名是一致的
    -- col_name(长度) - 只有varchar类型的,才会有length的概念
    -- 长度越大,区分度越明显.
    unique uq_index(name(3))
);

-- 长度和区分度的一个说明
-- name: 李三   李三子  李三姐   李四哥  李小龙三 李
-- 比如如果name(1) where name = '李'

1. unique|fulltext为可选参数，分别表示唯一索引、全文索引
2. index和key为同义词，两者作用相同，用来指定创建索引
3. col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4. index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5. length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6. asc或desc指定升序或降序的索引值存储

索引使用方式

1. 查看某张表上的所有索引

   show index from tableName [\G,如果是在cmd窗口，可以换行];

2. 建立索引

   alter table 表名 add index/unique/fulltext 索引名 (列名) ; —索引名可不写,不写默认使用列名

   CREATE INDEX 索引名 ON 表名(列值)

   create index tbl_name_cols_index on tbl_name(列);
   -- 给student表中的sgender添加一个普通索引
   mysql>create index student_sgender on student(sgender);
   

   alter table 表名 add primary key(列名) --不要加索引名，因为主键只有一个

3. 删除非主键索引

   alter table 表名 drop index 索引名；

   mysql>alter table student drop index student_sgender;
   

4. 删除主键索引：

   alter table 表名 drop primary key;

查看查询是否使用到了索引

mysql>explain select id from innodb where id = 2222;

组合索引

(5)复合索引

代码如下:

mysql>create index ut_index on innodb_test(uid,cid);

索引失效情况

索引type从优到差：System–>**const–>eq_ref–>ref–>ref_or_null–>index_merge–>unique_subquery–>index_subquery–>**range–>index–>all

1. ** 如果索引了多列，要遵守最左前缀法则。**所谓最左前列，指的是查询从索引的最左前列开始，并且不跳过索引中的列

   总结:1. select 查询的列 要是索引列

   ​ 2. 基于第一个要求.满足最左前缀法则,查询的列应该就是复合索引列.

   create table innodb_test(
   	uid int(7),
       cid int(7),
       cname varchar(20) unique,
       age int(7)
   );
   insert into innodb_test values(1,1,'tom',23);
   insert into innodb_test values(2,2,'jack',25);
   
   --组合索引
   create index ut_index on innodb_test(uid,cid);
   
   
   mysql>explain select uid,cid from innodb_test where uid = 2222;//using index
   mysql>explain select uid,cid from innodb_test where uid = 2222 or cid = 1000;//using index 
   
   //using index - 索引检索不回表
   //using where - 索引检索回表 - type - all - 全表扫描 - 索引相当于是失败的
   mysql>explain select * from innodb_test where cid = 2222;
   
   -- 查询的select后面跟的列中出现了非索引列tid
   -- 查询的列- 不能出现非索引列
   mysql>explain select uid,cid,tid from innodb_test where uid = 222;//Extra直接null
   
   -- Innodb存储引擎中是允许同时出现多种索引
   -- 复合索引(uid,cid)
   -- 聚集索引(id主键索引)
   mysql>explain select id,uid,cid from innodb_test where uid = 222;//use index
   
   -- 第一阶段是不会使用到索引的
   -- 但是经过mysql中的索引解释器会帮助我们进行纠正.
   -- 创建索引的顺序是uid,cid - 永远只会认识从最左边开始的索引列uid
   -- 实际不需要纠正的情况:uid=?   或者  uid=? and cid=?
   -- 但是直接使用cid =? 或者 cid=? and uid=?
   mysql>explain select uid from innodb_test where cid = 2222;
   mysql>explain select uid from innodb_test where cid = 2222 and uid = 1111;
   

2. 当MySQL一旦估计检查的行数可能会”太多”，范围查找优化将不会被使用。

   mysql>explain select uid from xxx where uid<101111111111111;
   

3. **索引列不应该作为表达式的一部分，即也不能在索引列上使用函数 **???

   mysql>select uid from xxx where uid/10>1000;//跟DB版本..
   mysql>select uid from xxx where uid>10*1000;//推荐使用的
   mysql>select uid from xxx where round(uid)/10>10000;//不要在索引列加上函数使用
   

4. 尽量借用覆盖索引，减少select * from …语句使用

5. 慎用left join语句,避免创建临时表 使用left join语句的时候，避免出现创建临时表。尽量不要用left join，分而治之。非要使用的时候，要询问自己是不是真要必须要使用。

   Teacher t = xx.getById(10);//select * from teacher where id=10
   

​

索引失效:

1. 索引列使用到了模糊查询

2. 索引列参与了计算

3. 索引列使用到了mysql的函数

4. 谨防where子句中的OR。where语句使用or，且没有使用覆盖索引,会进行全表扫描。应该尽量避免这样OR语句。尽量使用UNION代替OR

索引和排序

1. 检查的行数过多，且没有使用覆盖索引。第3句，虽然跟第2句一样，order by使用了索引最左前列uid，但依然使用**了filesort方式排[性能是及其低下]**序，因为status并不在索引中，所以没办法只扫描索引。

2. 使用了不同的索引，MySQL每回只采用一个索引.第4句,order by出现二个索引，分别是uid_fuid和聚集索引(pk)

3. 对索引列同时使用了ASC和DESC。 通过where语句将order by中索引列转为常量，则除外。第5句,和第6句在order by子句中，都出现了ASC和DESC排序,但是第5句却使用了filesort方式排序,是因为第6句where uid取出排序需要的数据,MySQL将其转为常量,它的ref列为const。

4. where语句与order by语句，使用了不同的索引。

   mysql>explain select uid from innodb_test where uid=2222 order by id;//出现filesort
   

5. where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式。参见第8，9句

6. where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。

7. order by子句中加入了非索引列,且非索引列不在where子句中。

8. order by或者它与where组合没有满足索引最左前列。参见第11句和12句,where与order by组合，不满足索引最左前列. （uid, fsex)跳过了fuid

9. 当使用left join，使用右边的表字段排序。参见第13句，尽管user.uid是pk，依然会使用filesort排序。

建立索引的策略

1. 主键列和唯一性列 √
2. 不经常发生改变的[在update列数据的数据的时候,也会更新索引文件] √
3. 满足以上2个条件,经常作为查询条件的列 √
4. 重复值太多的列 ×
5. null值太多的列 ×

总结

• 索引的分类
• 索引的底层 - B+Tree,存储形式key[列值]-value[表中行记录的物理地址]
• 索引失效的情况
• order by中如何正确使用索引. - order by where同时出现 - 列都应是同一种索引列,并且满足最左原则.
• 特别注意非索引列的出现.
• 推荐创建复合索引.
• 哪些列不适合创建索引.