概念:
Mysql 索引 事务 存储引擎
索引:索引是一个排序的列表,列表当中存储的是索引的值和包含这个值的数据所在行的物理地址
索引的作用:就好比书的目录,加快查询的速度
概念图:
索引的作用:
- 利用索引数据库可以快速定位,大大加快查询的速度
- 当表很大,或者需要关联多个表,这个时候索引也可以提高查询的速度
- 加快表与表的连接速度
- 使用分组和排序时,可以大大减少时间
- 可以提高数据库,恢复数据的恢复速度
索引的副作用:
索引也占用额外额磁盘空间,INNODB表数据文件本身也是索引,myisam:索引和数据文件是分离的,更新一个包含索引的表,要比更新一个没有索引花费的时间更多,更新值的值,也就是更新索引。
创建表的时候需要考虑的因素:
- 关联程度三张表,选好关联字段
- 每个字段的长度,也要考虑
- 设计合理的索引列
- 表数据,要控制在合理的范围之内,可以在牺牲一定性能的条件下,满足需求,5秒以上就要考虑优化了,10秒以上一般是出问题了(缓存失效,缓存击穿,缓存雪崩)
索引创建的原则:
- 如果有索引,数据库会先查询索引,然后定位数据,索引使用不当,反而会增加数据库的负担
主键和外键必须有索引(创建好了主键和外键,不需要额外声明)
- 如果一个表超过了300行的记录,必须要用索引,否则数据库会遍历表的所有数据,
- 互相之间有关联的表,如果你是关联的表,在这个关联字段设置索引,如果字段的唯一性太差的字段不适合创建索引
- 更新太频繁的字段,不适合做索引,比方说年龄
- 经常被while条件匹配的字段,尤其是大表,数据比较多的,应该创建索引
- 经常进行group by(分组语句)order by(排序语句)要建立索引
- 索引列的字段越小越好,长文本的字段,不适合建立索引
查看表的索引:
Show index from 表名
索引的类型:
- 常用类型
-
数索引又叫BTREE
树结构的索引,也是大部分数据库的默认索引类型
根节点:树的最顶端的分支节点
分支节点:指向索引里面其他的分支节点,也可以是叶子节点
叶子节点:直接指向表里的数据
就好比一本书:根节点为目录
哈希索引:散列索引,把任意长度的输入,通过散列算法变换成固定长度的输入,散列值---分别对应数据里的列和行
Mysql的默认引擎:INNODB默认的索引类型就是btree
MEMORY引擎就是可以支持HASH,也就是他的默认引擎
他是先算散列值,然后再对应,速度比较慢,比btree慢
Hash的索引匹配:= in () <=>
HASH
查看HASH,要先改变原有的默认的INNODB
如何HASH匹配查询
- 先创建一个表
创建HASH和btree索引:
创建索引(普通索引):
查看表的索引:
show index from member;
修改的方式进行添加索引:
alter table member add index idcard_index (idcard);
唯一索引:
unique与普通索引类似,唯一索引的每个值都是唯一的,唯一索引
允许空值添加唯一键才会创建唯一键,最好不要为空,unique not null
添加唯一索引:
create unique index phone_index on member (phone);
添加列:
alter table member add column address varchar(40) not null;
查看标的索引:
show index from table1;
如何在创表语句时添加唯一索引:
create table table1 (
id int(4) primary key,
name varchar(10),
card_id int(18) not null,
phone int(11)not null,
unique cardid_index (card_id),
unique phone_index (phone)
);
能在建表时创建好的条件,尽量在创建表时把条件约束好,不要创玩之后再添加
主键索引:
创建表的指定的主键就是索引,添加主键自动就是主键索引
主键:值唯一,一个表只能有一个主键,不允许为空值,创建主键,自动主键创建索引
全文索引:
适合在进行模糊查询的时候使用,可以在一边文章中检索文本信息,一般用于长文本列text
创建全文索引
如何表内创建全文索引:
create table test3 (
id int(4) primary key,
name varchar(10),
card_id int(18) not null,
phone int(11)not null,
notes text,添加一个长文本列test
unique cardid_index (card_id),
unique phone_index (phone),
fulltext notes_index (notes)
);
删除索引:
drop index notes_index on test3;
模糊查询:
select * from test3 where notes like '%'
explain:表示是否使用了索引
联合索引
如何创建联合索引:指定一个索引名,一个索引名对应多个列名
创建联合索引:
create index index_union on member1 (name,phone);
查看索引方式:
explain select * from member1 where name='王某某' and phone=13546555;
索引失效的一些情况:
创建一个联合索引:
联合索引,查询索引时必须按照查询的顺序来进行查询
查找数据
查看索引
用的主键索引
联合索引小结:
联合索引:
1.从左到右侧开始,不能跳过索引,否则就会失效
2.范围查询,有可能右侧的索引就会失效,如果索引是字符串,但是不加引号,索引还是会失效
3.使用or这个条件,mysql无法使用多个索引,索引一定失效
4.使用空值和非空值,也就是null和not null 有时候索引也会会失效,若where is null 数据的绝大多数都是空值,索引失效,若where is not null 数据多数不为null,索引失效