数据库索引
索引及分类
- 索引的概念
索引是一种特殊的文件,包含着对数据表中所有记录的引用指针。更通俗地说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
索引的作用
- 设置了合适的索引之后,数据库利用各种快速的定位技术,可以大大加快数据的查询速度,这也是创建索引最主要的原因。
- 当表很大时,或者查询涉及多个表时,使用索引可以使查询速度加快成千倍。
- 可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本
- 通过创建唯一性索引,可以保证数据库中每一行数据的唯一性
- 可以加快表和表之间的连接
- 在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间
索引的分类
- 普通索引,这是最基本的索引类型,而且它没有唯一性之类的限制
- 唯一性索引,与普通索引的区别在于索引列的所有值只能出现一次,即必须唯一
- 主键索引,主键是一种唯一性索引,但它必须指定为“PRINMARY KEY”
- 全文索引,索引类型为FULLTEXT,全文索引可以在CHAR、VARCHAR、或者TEXT类型的列上创建
- 单列索引与多列索引,索引可以是单列上创建的索引,也可以是在多列上创建的索引。多列索引可以区分其中一列可能有相同值得行
创建索引的原则依据
索引可以提升数据库查询的速度,单并不是任何情况都需要创建索引。因为索引本身会消耗系统资源,更重要的是在有索引的情况下,数据库查询会先进行索引查询,然后定位到具体的数据行,如果使用索引不当,反而会增加数据库的负担
- 表的主键、外键必须有索引
- 数据量超过300行的表应该有索引
- 经常与其他表进行表连接的表,在连接字段上应该建立索引
- 唯一性太差的字段不适合建立索引
- 更新太频繁的字段不适合创建索引
- 经常出现在where子句中的字段,特别是大表的字段,应该建立索引
- 索引应该建在选择性高的字段上
- 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建立索引
创建及查看索引
- 创建普通索引
CREATE INDEX <索引名> ON tablename(列);
- 创建唯一性索引
CREATE UNIQUE INDEX <索引的名字> ON tablename(列)
- 创建主键索引
CREATE TABLE tablename ([...],PRIMARY KEY(列的列表));
创建表的同时创建主键
ALTER TABLE tablename ADD PRIMARY KEY(列的列表);
修改表加入主键
- 在mysql中使用全文索引(FULLTEXT index),目前只有使用MyISAM类型表的时候有效(MyISAM是默认的表类型)。全文索引可以建立在TEXT、CHAR或者VARCHAR类型的字段或字段组合上
CREATE TABLE 表名(列名 TEXT,FULLTEXT(列名)) engine=MyISAM;
ALTER TABLE 表名 ADD FULLTEXT(列名);
- 多列索引只需要在创建索引时指定多列即可
create index 索引名 on 表名(列1,列2);
- 不需要使用索引时,应删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
修改表结构,删除某一列,相应的索引也会被删除
- 查看索引
SHOW INDEX FROM tablename;
SHOW KEYS FROM tablename;
在命令后面加上"\G" ,可以竖向显示索引的信息
显示参数 | 描述 |
---|---|
Table | 表的名称 |
Non_unique | 索引值的唯一性,0表示唯一性,1表示非唯一性 |
Key_name | 索引的名称 |
Seq_in_index | 索引中的序列号,从1开始 |
Column_name | 列名称 |
MySql事务
事务的概念
事务的概念
事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行
。事务是一个不可分割的工作逻辑单元
,在数据库系统上执行并发操作时,事务是最小的控制单元
。事务适用于用户同时操作数据库系统的场景
,如银行、保险公司及证券交易系统等,通过事务的整体性以保证数据的一致性
。
事务的ACID特性
- 原子性(automicity):事务是一个完整的操作,各元素是不可分的,即原子的。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败,则整个事务将失败。
- 一致性(consistency):当事务完成时,数据必须处于一致状态。
- 隔离性(isolation):对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,他不应以任何方式依赖于或影响其他事务。
- 持久性(durability):指不管系统是否发生了故障,事务处理的结果都是永久的。一旦事务被提交,事物的结果会被永久地保留在数据库中。
事务的操作
使用事务命令控制事务
- begin:表示开始一个事务,后面会有多条数据库操作语句执行
- commit:提交一个事务,对应begin操作,他们之间的数据库操作语句一起完成
- rollback:表示回滚一个事务,在begin和commit之间,如果某一个数据库操作语句出现错误,执行rollback回滚,数据库回到begin之前的状态,也就是操作语句都没执行。
savepoint s1; 定义回滚点s1
rollback to savepoint s1; 回滚到s1
使用set设置控制事务
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交