数据库索引
索引概念
- 是帮助mysql高校获取数据的数据结构,其存储着数据库中所有数据的应用指针
- 可以简单理解为 : 排好序的快速查找的数据结构。
- 在Mysql 中索引也叫做‘’键“
索引的作用
- 目的在于提高查询效率。
索引的原理
索引的目的在于提高查询效率,本质是通过不断的缩小想要获取数据的范围来筛选出 最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用 同一种查找方法来锁定数据。
索引的分类
- 单值索引: 即一个索引只包含单个列,一个表可以有多个单列索引。【推荐不超过5个】
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:即一个索引包含多个列
-
普通索引和唯一索引 普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。 唯一索引要求索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。主键索引是一种特殊的唯一索引,不允许有空值。
-
单列索引和组合索引 单列索引即一个索引只包含单个列,一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引,只有在查询条件中使 用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。【组合索引上来讲的】
-
全文索引 全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找, 允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR或者TEXT类型的列上创建。MySQL中 只有MyISAM存储引擎支持全文索引。
-
空间索引 空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据 类型有4种,分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展,
添加和删除索引
- 创建索引
create [unique] index indexname on tabname(colname(length));
alter tabname add [unique] index [indexname] on (colname(length))
- 删除索引
drop index[indexname] on tabname
Alter table tabname drop index indexname ;
- 四种索引的创建方式
alter table tabname add primary key(col_list):添加主键,意味着索引值必须是唯一,且
不能为null
alter table tabname add unique index_name(col_list) 创建唯一索引(可以允许为null,
而且null可以出现多次)
alter table tabname add index index_name(col_list)添加普通索引,索引值可以出现多次
alter table tabname add fulltext index_name(col_list)指定索引为fulltext,用于全文
索引
- 组合索引的最左前缀原则(遇到范围匹配会停止生效)
and 关键字连接的情况下不会失效
索引的设计原则
(1)索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间,还会影响INSERT、DELETE、UPDATE等语句的性能,因为在表 中的数据更改时,索引也会进行调整和更新。
(2)避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。应该经常用于查询的字段创建索引,但要避免添加不必要的字段。
(3)数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
(4)在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值,因此就无须建立索引。
(5)当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引需能确保定义的列的数据完整性,以提高查询速度。
(6)在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。
创建索引的情景
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引
- where条件里用不到的字段不创建索引
- 单继/组合索引的选择问题,(在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
索引的优缺点
优点
- 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的查询速度,这也是创建索引的主要原因。
- 在实现数据的参考完整性方面,可以加速表和表之间的连接。
- 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间
缺点
- 创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费 的时间也会增加。
- 索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引 还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快 达到最大文件尺寸。
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。
数据库事务
事务概念
数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列,这些操作要么全部执行, 要么全部不执行,是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作 组成
事务ACID特性
- 原子性(Atomicity):事务具有原子不可分割的特性,要么一起执行,要么都不执行。
- 一致性(Consistency):在事务开始和事务结束时,数据都保持一致状态。
- 隔离性(Isolation):在事务开始和结束过程中,事务保持着一定的隔离特性,保证事务不受外部 并发数据操作的影响。
- 持久性(Durability):事务完成后,数据将会被持久化到数据库中。
事务的状态,使用
- 提交 :commit
- 回滚 :rollback
BEGIN ; -- 开启事务 提交 commit或 回滚rollback
UPDATE account SET abalance=abalance-2000 WHERE aid=1;
UPDATE account SET abalance=abalance+2000 WHERE aid=2;
COMMIT;
或
ROLLBACK;
-- 还可以设置打点事务,即再保存点之前的会提交,保存点之后的会回滚
START TRANSACTION ;
UPDATE account SET abalance=abalance-10 WHERE aid=1;
SAVEPOINT A;
UPDATE account SET abalance=abalance-1000 WHERE aid=1;
UPDATE account SET abalance=abalance+1000 WHERE aid=2;
ROLLBACK TO A;
事务的隔离级别
-
概念:一个事务对数据库修改与并行的另一个事务的隔离程度。
-
两个并发事务同时访问数据库表相同的行时,可能存在以下三个问题:
- 幻想读,
- 不可重复读取
- 脏读
脏读:事务A读取到事务B未提交的数据。 比如账号1的账号金额是5000,此时事务B,将1账号的金额更改为8000,但是还未提交事务,此 时事务A,查看1账号的金额,看到8000,然后事务B对操作做了回滚。事务A就读取到脏数据。 如果将事务的隔离级别提高到Read uncommitted,便不会产生以上情况。
不可重复读:事务A查看1账号的金额是2000,此时想再账号上减2000.但是还没操作的时候,事务B,将 数据更改了,并再事务A之前提交了事务。此时事务A再次读取该数据的时候,已经改变了。 事务的隔离级别提高到Read committed时,避免了脏读,但是会造
不可重复读。大多数数据库 的默认级别就是ReadCommited.可以将事务的隔离级别提高到Repeatable read。也就是事务A读取了 数据,再未提交事务的时候,事务B不能对数据进行修改。 幻读:事务A查看第一次查看记录的时候为80。此时,事务B对该记录做了更改,事务A再看的时候,发 现不是80,事务A就像出现环境一样。将隔离级别提高到Serializable就可以避免幻读。
-
解决方法:定义事务的隔离级别
-
查看mysql的事务隔离级别
SELECT @@transaction_isolation; 【 select @@tx_isolation;】
-
设置mysql的事务隔离级别、
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;