MySQL的索引
索引的概念:
索引是一个排序的列表,在列表当中存储索引的值以及索引对应数据所在的物理行。
索引值和数据是一个映射关系。
索引的作用:
使用索引之后,就不需要扫描全表来定位某行的数据。
加快数据库的查询速度。
索引可以是表中的一列,也可以是多个列
1、设置了索引之后,数据库可以利用索引快速定位,大大提高查询速度,这也是索引的主要原 因。
2、尤其的表的数据很大,以及涉及多个表查询时,索引可以大大的提高查询速度。
3、建立索引不仅能够提高查询速度,在恢复数据库的数据时,也能提高性能。
4、可以加快表与表
索引的负作用:
1、创建的索引也需要占用额外的磁盘空间。 INNODB存储引擎表数据文件和索引文件在一块 (一体)相对来说占用的空间小一点。
2、一但创建了索引,更新一个包含索引的表比没有索引的表需要花费更多的时间,表需要更新,索引也要更新,所以速度要慢很多。
理想的做法:经常被作为搜索条件的列上面创建索引。
之间连接查询的速度。
创建索引的原则和依据:
1、表的主键和外键必须有索引,主键是唯一的,外键是关联主表的,查询时可以快速定位
2、一张表有超过300行的数据,应该要创建索引。
3、经常与其他表进行连接的表,在连接字段上应该创建索引。
4、更新太频繁的字段不适合创建索引
5、经常作为where语句的条件列,应该创建索引
6、经常使用grour by 和order by 的字段上要建立索引。
7、选择一个性能高的字段作为索引,字段的值不同的越多越好。
8、索引要建立在小字段上,字符串的长度比较小的字段,对于长文本字段,超长的字段不适合建立索引。
索引的类型:
b-tree 索引,
b-树索引
绝大部分的数据都是使用b-树索引
索引左边的列开始,从左到右按顺序进行排列
哈希索引:
索引对应的hash值的方法获取表的记录行,速度比较慢。(用的比较少)
创建索引
第一种:在创建表的时候创建索引
create table member (
id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
text也是数据类型的一种,和char,varchar性质相同,都是字符串,不需要长度的参数,可以作为大文本的列,可以存储65535个字符。
index name_index (name)
);
show index from member;
查看表的状态
第二种:后天添加索引
alter table member add index id_index(id);
能在建表时创建好的条件,尽量在创建时把条件约束好,不要创完之后再添加。
怎么一次性添加多个数据
insert into member values (内容),
(内容);
查询当前语句使用索引的情况
explain select * from member where id = 1;
唯一索引:可以用key(唯一)的方式创建,也可以用index(索引)的方式创建
create unique index 列名_index on 表名(列名)
alter table 表名 drop index 列名_index;
主键索引是一种特殊的唯一索引,主键即是索引。
删除索引:
drop index 列名_index on 表名;
alter table 表名 drop index 列名_index;
组合索引:
一次性给多个列创建索引,形成一个组合。
alter table member add constraint uc_name_cardid_phone unique(name,cardid,phone);
查询内容
select * from member where name =(内容) and cardid =(内容) and phone =(内容) ;
全文索引:
适用于模糊查询,检索大文本使用的。
创建
create fulltext index 列名_index on 表名 (列名);
查询
select * from 表名 where match (列名) against ('关键字');
MySQL的总结:
索引是 MySQL 中用于提高数据库查询性能的重要结构。
一、索引的类型
主键索引(Primary Key):表中的一列或列组合,其值唯一标识表中的每一行,一个表只能有一个主键索引。
- 例如,在学生表中,学号通常被设为主键。
唯一索引(Unique Index):确保表中某一列的值是唯一的,但可以存在
NULL
值。
- 比如,身份证号可以设置为唯一索引。
普通索引(Index):可以加快数据的查询和排序速度。
- 假设在订单表中,经常按照订单创建时间查询订单,那么订单创建时间列可以设置普通索引。
全文索引(Fulltext Index):用于对文本类型的列(如
CHAR
、VARCHAR
、TEXT
)进行全文搜索。
- 例如,在文章表中,对文章内容列建立全文索引,以便快速搜索包含特定关键词的文章。
二、索引的优点
- 大大提高数据的查询速度,特别是在大型数据表中。
- 有助于优化数据库的排序和分组操作。
三、索引的缺点
- 索引会增加数据插入、更新和删除的时间,因为这些操作需要同时维护索引。
- 过多的索引会增加数据库的存储空间。
四、创建索引的注意事项
- 只在经常用于查询、连接、排序和分组的列上创建索引。
- 避免在小数据量的表上创建过多索引。
- 对于经常更新的列,要谨慎创建索引。
总之,合理地使用索引可以显著提高数据库的性能,但过度使用或不当使用可能会带来负面影响,需要根据实际的业务需求和数据特点进行权衡和设计。
MySQL的事务
mysql的事务
事务是一个机制,一个操作序列。一组或者一条数据库的操作命令。
把所有的命令作为一个整体向系统提交或者撤销的操作。要么都成功,要么都失败。
数据的一致性,数据的一致性非常重要。
事务是一个不可分割的工作逻辑单元,在数据库上执行并发操作时,事务时最小的控制单元。
数据库通过事务的控制和事务的整体性保证数据的一致性。
事务的特点:ACID 在数据库的管理系统中,事务的特性有ACID这四种
A:Atomicity 原子性 ---- 事务的最小控制单位,不可分割,要么成功,要么失败
C:consistency 一致性 ---- 事务开始之前和事务结束之后,数据库的完整性没有被破坏。
开始的时候数据是一致的,结束的时候数据也是一致的。
在事务进行的时候,数据可以处于不一致的状态,但是一但结束,
数据必须是一致的。
I:lsolation 隔离性 ---- 在并发环境中,不同的事务同时操作相同的数据时,每个事务都有自
己完整的数据空间。
对数据的修改所发生的并发事务是隔离的,每个事务之间都是独立的
一个用户的事务不给其他事务所干扰。
数据的隔离性:
1、未提交读:read uncommitted RU
允许脏读,一个事务可以看到其他事务未提交的修改
2、提交读:read committed RC
一个事务只能看到其他事务已经提交的修改,未提交的修改不可见,防止脏读
3、可重复读:repeatable read RR
一个事务在执行中,执行两次相同的select语句,得到的结果都是相同
mysql的默认隔离选项 防止脏读和不可重复读。
4、串行读:相当于锁表,完全串行化的读,一个事务在使用,其他事务的读写都会阻塞(没人用)
不可重复读:在一个事务内,多次读同一数据,一个事务没有结束,另外一个事务也访问该数据
其中一个事务连续两次查询发现结果不一致。原因是另外一个事务在对这个数据进行修改。两次看到的数据不能一致。
幻读:一个事务对一个表的数据进行了修改,另外一个事务也修改了表中数据,前一个事务会发现修改的结果不正确,类似于出现于幻觉。
不可更新:两边同时对数据进行修改,但是一方先提交,一方后提交,后提交会覆盖先提交的
D:Durability 持久性 ---- 一但写入数据库,数据不可更改。
事务控制语句:
开启一个事务:begin或者start transaction
提交事务:commitl
回滚:rollback
设置回滚点:savepoint +名称
回到回滚点:rollback to savepoint +名称
回滚点可以有多个,回滚其中一个,其他的回滚点将会没有作用。
一但提交事务,不管设置了多少回滚点将不会存在。