建立索引的目的是为了加快对表中记录的查找或排序
索引虽然可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担
索引其实就是个列表是个字段,它是经过排序后的字段,是通过物理地址直接去查找所存储的数据,最大作用是加快查询速度,建议创建在最好有唯一性的,不可重复的,字段数比较少的字段上//(小字段上)//
mysql -u root -p
create database club;
use club;
create table feiwu (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
desc member;
insert club.feiwu values (1,'zhangsan','123','111111','nanjing','this is vip');
insert club.feiwu values (4,'lisi','1234','444444','nanjing','this is normal');
insert club.feiwu values (2,'wangwu','12345','222222','benjing','this is normal');
insert club.feiwu values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert club.feiwu values (3,'qianqi','1234567','333333','shanghai','this is vip');
select * from member;
最基本的索引类型,没有唯一性之类的限制
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
#(列名(length)):length是可选项,下同
#如果忽略 length 的值,则使用整个列的值作为索引
#如果指定使用列前的 length 个字符来创建索引,这样有利于减小索引文件的大小
#索引名建议以“_index”结尾
例:
create index phone_index on feiwu (phone);
#直接创建索引
select phone from feiwu;
show create table feiwu;
#展示表的结构以及创建表的具体语句
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
例:
alter table feiwu add index id_index (id);
#这是第二种创建普通索引的方式,能修改表格式
select id from feiwu;
#这里查询索引,在自动排序
select id,name from feiwu;
show create table feiwu;
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));
例:
create table xianyu(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
show create table xianyu;
与普通索引类似,但区别是唯一索引列的每个值都唯一
唯一索引允许有空值(注意和主键不同),如果是用组合索引创建,则列值的组合必须唯一,添加唯一键将自动创建唯一索引
直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
例:
create unique index name_index on feiwu (name);
#注意,创建唯一索引必须得满足每个值都是唯一的,否则无法创建
show create table feiwu;
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
例:
alter table feiwu add unique cardid_index (cardid);
select cardid from feiwu;
show create table feiwu;
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));
例:
create table www (id int,name varchar(20),unique id_index (id));
show creat table www;
是一种特殊的唯一索引,必须指定为“PRIMARY KEY”
一个表只能有一个主键,不允许有空值。 添加主键将自动创建主键索引
该索引要求主键中每个值都唯一
创建表的时候指定索引
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));
例:
create table huhu (id int primary key,name varchar(20));
#这里用了两种方式创建,一种是在字段中直接指定,第二种是在字段结束后指定
create table lala (id int,name varchar(20),primary key (id));
show create table huhu;
show create table lala;
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
例:
alter table feiwu add primary key (id);
show create table feiwu;
④创建全文索引
索引类型为 FULLTEXT
适合在进行模糊查询的时候使用,可用于在一篇文章中检索文本信息
在 MySQL5.6 版本以前 FULLTEXT 索引仅可用于 MyISAM 引擎,在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引
全文索引可以在 CHAR、VARCHAR 或 TEXT 类型的列上创建
每个表只允许有一个全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
例:
create fulltext index remark_index on fanren (remark);
#全文索引 fulltext,全文只可在 char、varchar、text 类型上创建,且每个表只允许有一个
show create table fanren;
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');
例:
select * from feiwu where match(remark) against('this is vip');
⑤组合索引
索引可以是单列上创建的索引,也可以是在多列上创建的索引
多列索引可以区分其中一列可能有相同值的行
如果经常同时搜索两列、多列、按两列或多列排序时,索引也很有帮助
例如,如果经常在同一查询中为姓和名两列设置查询条件,那么这两列上创建多列索引将很有意义
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
例:
create table yiya (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table yiya;
insert into yiya values(1,'xiaowang','123123');
select * from yiya where name='xiaowang' and id=1;
show index from 表名;
show index from 表名\G;
#在命令后面加上“\G”,可以竖向显示索引的信息
或
show keys from 表名;
show keys from 表名\G;
#这两种方法的显示结果完全相同
例:
show index from feiwu;
显示参数 | 描述 |
---|---|
Table | 表名称 |
Non_unique | 索引值的唯一性,0 表示唯一性,1 表示非唯一性 |
Key_name | 索引的名称 |
Seq_in_index | 索引中的列序号,从 1 开始 |
Column_name | 列名称 |
Collation | 列以什么方式存储在索引中。在 MySQL 中,有值 A(升序)或 NULL(无分类) |
Cardinality | 索引中唯一值数目的估计值 |
Sub_part | 如果列只是被部分地编入索引,则为被编入索引的字符的数目;如果整列被编入索引,则为 NULL |
Packed | 指示关键字如何被压缩;如果没有被压缩,则为 NULL |
Null | 如果列含有 NULL,则含有 YES。如果没有,则该列含有 NO |
Index_type | 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE) |
Comment | 备注 |
小结:
索引分为:
①普通索引:针对所有字段,没有特殊的需求/规则
②唯一索引:针对唯一性的字段,仅允许出现一次空值
③组合索引(多列/多字段组合形式的索引)
④全文索引
( varchar char text)
⑤主键索引:针对唯一-性字段、且不可为空,同时一-张表只允许包含一个主键索引
指在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间
*
对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务
*
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据,或者在另一个使用相同数据的事务结束之后访问这些数据
在事务管理中:
①脏读(读取未提交数据): (A修改1->2,B看到了2, 但是A没有保存,其实数据还是1)
脏读指的是读到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不
会存到数据库中,也就是不存在的数据
②不可重复读(前后多次读取,数据内容不-致): (A看到表里数据是1, 第二是2,两次不同)
一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起
的
③幻读(前后多次读取,数据总量不- -致): (A改修1->2,保存失败, 同时B修改1->2保存成功, A幻读)
一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也
修改这个表中的数据,这种修改是向表中插入一行新数据。那么,操作前一个事务的用户会发现表
中还有没有修改的数据行,就好象发生了幻觉一样
④丢失更新: (A把1->2,随后B把1->3,看到的结果是3)
两个事务同时读取同一条记录,A先修改记录,B也修改记录(B不知道A修改过),B提交数据后B
的修改结果覆盖了A的修改结果
read uncommitted : 读取尚未提交的数据 :不解决脏读
read committed:读取已经提交的数据 :可以解决脏读
repeatable read:重读读取:可以解决脏读 和 不可重复读 —mysql默认的
serializable:串行化:可以解决 脏读 不可重复读 和 虚读——相当于锁表
mysql 默认的事务处理级别是 repeatable read
而 Oracle 和 SQL Server 是 read committed
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
select @@global.tx_isolation;
set global transaction isolation level read committed;
select @@global.tx_isolation;
select @@tx_isolation;
set session transaction isolation level read committed;
select @@tx_isolation;
BEGIN 或 START TRANSACTION:显式地开启一个事务
COMMIT 或 COMMIT WORK:提交事务,并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改
SAVEPOINT S1:使用 SAVEPOINT 允许在事务中创建一个回滚点,一个事务中可以有多个 SAVEPOINT;“S1”代表回滚点名称
ROLLBACK TO [SAVEPOINT] S1:把事务回滚到标记点
创建数据库与数据表,用作实例
create database school;
#创建数据库
use school;
#进入数据库
create table class (id int(10) primary key not null,name varchar(40),money double);
#创建数据表
#字段一(10):主键,且不能为空值
#字段二(40)可变长度字段
#字段三:浮点类型
insert into class values(1,'A',1000);
insert into class values(2,'B',1000);
#在表中插入新数据记录
select * from class;
#查看class数据表
begin;
#开启事务
update class set money = money - 100 where name = 'A';
#更新表字段,name字段=A的money值 - 100
select * from CLASS;
#查看表
commit;
#提交事务
quit
#退出
mysql -uroot -p123123
use school;
select * from class;
#重新登录,再次查看
注:如果没有使用 commit 提交,当前依然可以查询到插入的数据,但只是在当前开启的事务中才能看到
*
因为没有真正的插入到数据库中,如果退出再查询就没有了
*
使用 begin 开始事务,执行操作语句后,必须使用 commit 进行提交,否则数据时不能自动提交的
*
也是就是说,多条数据操作语句作为一个整体需要使用 commit 进行整体的事务提交
begin;
update class set money = money + 100 where name = 'A';
select * from CLASS;
rollback;
#回滚,结束事务,并撤销正在进行的所有未提交的修改
quit
mysql -uroot -p123123
use school;
select * from class;
begin;
#再次开启一个事务
update class set money = money + 100 where name = 'A';
#更新表中A的money值+100
select * from class;
SAVEPOINT S1;
#创建回滚点S1
update class set money= money + 100 where name = 'B';
#更新表B中的money值+100
select * from CLASS;
SAVEPOINT S2;
#创建回滚段S2
insert into class values(3,'C',1000);
#再次插入一条数据记录
select * from CLASS;
ROLLBACK TO S1;
#回滚到回滚点S1
select * from CLASS;
SET AUTOCOMMIT=0; #禁止自动提交
SET AUTOCOMMIT=1; #开启自动提交,Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT'; #查看Mysql中的AUTOCOMMIT值
#如果没有开启自动提交,当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
#如果开起了自动提交,mysql会把每个sql语句当成一个事务,然后自动的commit。
#当然无论开启与否,begin; commit|rollback; 都是独立的事务
例:
use school;
select * from class;
SET AUTOCOMMIT=0;
#设置禁止自动提交,不使用commit提交操作语句
#和使用begin的情况相同,执行的语句都会失效
SHOW VARIABLES LIKE 'AUTOCOMMIT';
#查看值
update class set money = money + 100 where name = 'B';
#更新表数据
select * from class;
quit
mysql -uroot -p123123
use school;
select * from class;
可以看到,如果不使用commit提交事务,退出MySQL重新进入后,插入的数据并不存在
若开启自动提交,则不需要commit提交事务也可以自动提交
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/weixin_53560205/article/details/120911971
内容来源于网络,如有侵权,请联系作者删除!