Mysql索引详解， explain查看执行效果。

1、索引能够干什么

索引是一种能够帮助我们在数据库中快速定位数据的一种数据结构

优点

类似大学图书馆建书目录索引,提高数据检索的效率,降低数据库的IO成本通过索引列对数据进行排序,降低数据排序成本,降低了CPU的消耗。

可以加速表和表之间的连接 。

缺点

实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是要占内存空间的

虽然索引大大提高了查询速度,同时都会降低更新表的速度,如对表进行insert,update和delete。

因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引的字段,都会调整因为更新所带来的键值变化后的索引信息 。

2、索引分类

常规索引

快速定位数据

主键索引

就是我们数据库的主键， 主键索引不允许重复， 不允许空值。

唯一索引

和主键索引类似， 但是允许有空值

复合索引

将数据库表中的多个字段联合起来创建一个索引

全文索引

3、索引操作

创建索引

CREATE INDEX INDEXNAME ON TABLE (COLUMNNAME)

更改

ALTER TABLE TABLENAME ADD INDEX INDEXNAME (COLUMNNAME)

alter table area add index index_name (area);

删除

DROP INDEX INDEXNAME ON TABLENAME

查看索引

SHOW INDEX FROM TABLENAME

4、注

一、创建索引原则

• 索引一般创建在查询字段
• 索引不是越多越好
• 创建不易创建在频繁修改的表
• 小数据量表格不易创建索引

二、查看sql执行情况操作

EXPLAIN select * FROM area WHERE area = '红桥区'

# 分析 sql语句的执行情况

无索引情况下

创建索引

CREATE INDEX index_area on area(area)

show INDEX from area

执行查询

直接定位到该数据

**三、**以及在 mysql 数据库中索引的工作机制是什么？

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+树

四、索引失效

• 在where后使用or，导致索引失效（尽量少用or）

• 使用like ，like查询是以%开头，以%结尾不会失效

• 不符合最左原则

• 使用in导致索引失效

• 使用mysql内部函数导致索引失效,可能会导致索引失效。