目录
数据库索引
索引虽可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担。
直接创建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
修改表结构方式创建索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
创建表的时候指定索引
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));
与普通索引类似,但区别是唯一索引列的每个值都唯一。唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建,则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
修改表方式创建唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
创建表的时候指定唯一索引
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));
是一种特殊的唯一索引,必须指定为“PRIMARYKEY"。一个表只能有一个主键,不允许有空值。添加主键将自动创建主键索引。
创建表的时候指定主键索引
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...], PRIMARY KEY (列名));
修改表方式创建主键索引
ALTER TABLE 表名 ADD PRIMARY key (列名);
可以是单列上创建的索到,也可以是在多列上创建的索引。需要满足最左原则,因为select语句的where 条件是依次从左往右执行的,所以在使用select 语句查询时 where条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。
创建表的时候指定组合索引
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
修改表方式创建组合索引
ALTER TABLE 表名 ADD INDEX 索引名 (列名1,列名2);
注意:使用的时候要注意 where 的最左原则
select * from 表名 where 字段1=值 and 字段2=值 and ....;
适合在进行模糊查的时候使用可用于在一篇文章中检索文本信息。在MySQL5.6版本以前FULLTEXT 索引仅可用于MyISAM引擎,在5.6版本之后innodb引擎也支持FULLTEXT索引。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。每个表只允许有一个全文索引。
直接创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
创建表的时候指定全文索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');
show index from 表名;
show keys from 表名;
字段
含义
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
lndex_type
用过的索引方法(BTREE,FULLTEXT,HASH,RTREE)
comment
备注
直接删除索引
DROP INDEX 索引名 ON 表名;
修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
总的来说,事务就是是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。
当多个客户端并发地访问同一个表时,可能出现下而的一致性问题
脏读:当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
不可重复读:指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。(即不能读到相同的数据内容)
幻读:一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,操作前一个事务的用户会发现表中还有没有修改的数据行,就好象发生了幻觉一样。
丢失更新:两个事务同时读取同一条记录,A先修改记录,B也修改记录(B不知道A修改过),B提交数据后B的修改结果覆盖了A的修改结果。
用以控制事务所做的修改,并将修改通告至其它并发的事务
mysq1默认的事务处理级别是repeatable read,而oracle和sQL Server是 read committed。
事务隔离级别
脏读
不可重复读
幻读
第一类丢失更新
第二类丢失更新
read uncommitted
允许
允许
允许
禁止
允许
read committed
禁止
允许
允许
禁止
允许
repeatable read
禁止
禁止
允许
禁止
禁止
serializable
禁止
禁止
禁止
禁止
禁止
事务的级别作用范围分两种
show global variables like '%isolation%';
select @@global.tx_isolation;
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
set global transaction isolation level 级别;
set session transaction isolation level 级别;
在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
打开另外一个会话
#禁止自动提交(状态为OFF)
SET AUTOCOMMIT=0;
#开启自动提交,Mysql默认为1(状态为ON)
SET AUTOCOMMIT=1;
#查看Mysql中的AUTOCOMMIT值
show variables like 'autocommit';
静态(固定长度)表
动态表
压缩表
show engines;
方法一:
show table status from 库名 where name='表名'\G;
方法二:
use 库名;
show create table表名;
1.通过alter table修改
use 库名;
alter table 表名 engine=MyISAM;
2.通过修改/etc/my.cnf 配置文件,指定默认存储引擎并重启服务
vim/etc/my.cnf
[mysqld]
default-storage-engine=myisam
systemctl restart mysql.service
注意:此方法只对修改了配置文件并重启mysq1服务后新创建的表有效,已经存在的表不会有变更。
3.通过create table创建表时指定存储引擎
use库名;
create table 表名(字段1数据类型,...)engine=MyISAM;
InnoDB行锁与索引的关系
InnoDB行锁是通过给索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。
selete frim t1 where id=1;
如果id字段是主键,innodb对于主键使用了聚簇索引,会直接锁住整行记录。
delete from tl where name='aaa';
如果name字段是普通索引,会先锁住索引的两行,接着会锁住相应主键对应的记录。
delete from t1 where age=23;
如果age字段没有索引,会使用全表扫描过滤,这时表上的各个记录都将加上锁。
死锁
死锁一般是事务相互等待对方资源,最后形成环路造成的。
案例:
create table t1(id int primary key,name char(4),age int);
insert into t1 values(1,'aaa',22);
insert into t1 values(2,'bbb',23);
insert into t1 values(3,'aaa',24);
insert into t1 values(4,'bbb',25);
insert into t1 values(5,'ccc',26);
insert into t1 values(6,'zzz',27);
会话1
begin;
delete from tl where id=5;
会话2
begin;
select * from t1 where id=1 for update;
会话1
delete from tl where id=1;#死锁发生
会话2
update t1 set name='abc' where id=5; #死锁发生
注:for update可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候,其他事务可以读取但是不能写入或更新。
如何尽可能避免死锁?
手机扫一扫
移动阅读更方便
你可能感兴趣的文章