一、索引的概念
1、索引:是一个排序的列表,列表当中存储的是索引的值和包含之值的数据所在行的物理地址
2、主要作用:快速查找,加快查询速度
3、索引的缺点:索引也占用额外的磁盘
(1)innodb表数据文件本身也是索引,myisam:索引和数据文件是分离的
(2)更新一个包含索引的表,要比更新一个没有索引的表花费的时间更多,更新了值,也就更新索引
二、索引的作用
1、利用索引,数据库可以快速定位,大大加快查询速度(主要作用)
2、表的数据很多,查询需要关联多个表,这个时候索引也可以提高查询速度
3、加快表与表之间的连接速度
4、使用分组和排序时,可以大大减少时间
5、可以提高数据库恢复数据时的速度
三、索引创建的原则
1、若有索引,数据会先进行索引查询,然后定位数据,索引使用不当,反而会增加数据库的负担
2、主键、外键必须有索引(创建好主键和外键自动就有索引,不需要额外声明)
3、若一个表超过了300行记录,必须要有索引,否则数据库会遍历表的所有数据
4、互相之间有关联的表,在这个关联字段应该设置索引
5、经常被where条件匹配的字段,尤其是表数据比较多的,需要创建索引
6、经常进行group by(分组)、order by(排序)的字段,需要创建索引
7、唯一性太差的字段,不适合创建索引
8、更新太频繁的字段,不适合创建索引
9、索引列的字段越小越好,长文本的字段不适合建立索引
四、创建表的时候需要考虑的因素
1、关联程度,3张表,选好关联字段
2、每个字段的长度也要考虑
3、设计合理的索引列
4、表数据,要控制在合理的范围之内。可以在牺牲一定性能的条件下满足需求(5秒以上要考虑优化,10秒以上一般是出现问题(缓存失效、缓存失效;缓存雪崩))
五、索引的类型
1、常用类型:B-树索引(BTREE)
(1)树形结构的索引,也是大部分数据库的默认索引类型
(2)根节点:树的最顶端的分支节点
(3)分支节点:指向索引里其他的分支节点,也可以是叶子节点
(4)叶子结点:直接指向表里的数据行
(5)mysql的默认引擎:INNODB,默认的索引类型就是Btree(INNODB——Btree)
查看索引:show index from test;
2、hash索引:散列索引(一般不用)
(1)把任意长度的输入,通过散列算法变成固定长度的输出
(2)散列值:分别对应数据里的列和行
(3)先算散列值,然后再对应,速度比Btree慢
(4)hash的索引匹配:= in () <= >=
(5)memory引擎可以支持hash,也是他的默认索引(memory——hash)
3、修改引擎类型
(1)alter table test engine=memory;
(2)alter table test engine=INNODB;
4、创建索引
(1)create index name_index on test (name(length));(name可自定义)
(2)create index name_index on test (name);(name可自定义)
(3)创建btree类型的索引:
create index idx_Btree_column on table2 (address) USING btree;
(4)创建hash类型的索引:
create index idx_hash_column on table2 (address) USING hash;
5、删除索引
(1)drop index notes_index on test;
六、创建的索引类型
(一)普通索引
(二)唯一索引(与unique相关联)
1、与普通索引类似,唯一索引的每一个值都是唯一的,唯一索引允许空值
2、添加唯一键才会创建唯一索引(最好不要为空)
3、一般是:unique not null
4、唯一索引的值不能重复
5、能在创建表时创建好条件,尽量在创建时把条件约束好,不要创完之后再添加
(三)主键索引
1、创建表的时候指定的主键就是索引,添加主键自动就是主键索引
2、主键:值唯一,一个表只有一个主键,不允许有空值,创建主键自动创建主键索引
(四)全文索引(text)
1、全文索引:适合在进行模糊查询时使用,可以在一篇文章中检索文本信息
2、模糊查询
explain加在查询语句前面,可以查看索引的使用情况
(五)联合索引
1、联合索引:指定一个索引名,一个索引名对应多个列名
七、索引失效(创建索引,使用时不一定会生效)
1、联合索引
(1)联合索引:查询时必须按照创建时的顺序来进行查询
(2)联合索引:必须从左到右侧开始,不能跳过索引,否则索引会失效
(3)mysql机制:默认会找最短的索引列(最优索引选择)
2、范围索引,可能会有一侧的索引失效
(1)如果索引是字符串,不加引号,索引会失效
(2)使用or语句,索引一定失效,使用or作为条件,mysql无法同时使用多个索引
(3)使用is null 、is not null,有时候索引会失效。
①where is null 数据的绝大多数都是空值,索引失效
②where is not null 数据多数不为null,索引失效
(4)使用in或not in
①in age 索引生效
②not in age 索引失效
八、重点:一张表的查询速度是7.62s,你该如何解决?
1、先检查缓存,查看是否直接请求到后端数据库(缓存记录)
2、然后查看索引,请求的列值不是默认的索引,添加一下即可(EXPLAIN)