mysql数据库事务索引_mysql数据库 索引 事务和事务回滚

本文详细介绍了MySQL中的索引类型,包括普通索引、唯一索引、主键和外键,以及它们的用途和创建方法。还讨论了不同类型的存储引擎,如InnoDB和MyISAM的特点,以及如何选择合适的存储引擎。此外,提到了事务处理和回滚,以及MySQL的工作流程和组件。
摘要由CSDN通过智能技术生成

mysql索引

索引相当于书的目录

优点:加快数据的查询速度

缺点:占物理存储空间,添加,删除,会减慢写的速度

查看表使用的索引

mysql> show index from 表名\G;(\G分行显示)

mysql> show index from mysql.db;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| db | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | |

| db | 0 | PRIMARY | 2 | Db | A | NULL | NULL | NULL | | BTREE | | |

| db | 0 | PRIMARY | 3 | User | A | 2 | NULL | NULL | | BTREE | | |

| db | 1 | User | 1 | User | A | 1 | NULL | NULL | | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Key_name(索引名)

Index_type:BTREE(二叉树算法)

Hash/B+tree

索引类型:(不同的索引有不同的约束方式和使用规则)

index:普通索引(工作中用的多)

unique:唯一索引

primary key:主键(工作中用的多)

foreign key:外键(工作中用的多)

fulltext:全文索引

在表中创建索引字段:

建表示设置索引字段

把已有表中的字段设置为索引字段

——————————————————————————————————————————————————————————————————————————————————

index:普通索引:

一个表中可以有多个index字段,值可以重复,也可以赋null值

通常把表中把做查询条件的字段设置为index字段,index字段的key标志是mul

建表时设置索引字段,

格式:

carate table 表名(字段名列表,index(字段名),index(字段名));(建表时设置索引)

mysql> create table t13(name char(5),age int(2),sex enum("m","w"),index(name),index(sex));

mysql> desc t13;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| name | char(5) | YES | MUL | NULL | |

| age | int(2) | YES | | NULL | |

| sex | enum('m','w') | YES | MUL | NULL | |

+-------+---------------+------+-----+---------+-------+

把已有表中的字段设置为索引字段,

格式:

create index 索引名 on 表名(字段名);(把已有表中的字段设置为索引字段)

mysql> create index name on t11(name);

mysql> desc t11;

+-------+--------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------------------+------+-----+---------+-------+

| name | char(10) | YES | MUL | NULL | |

| age | int(11) | YES | | NULL | |

| sex | enum('boy','girl') | YES | | NULL | |

| likes | set('girl','linux','it') | YES | | NULL | |

+-------+--------------------------+------+-----+---------+-------+

删除索引,

格式:

drop index 索引名 on 表名;

mysql> drop index name on t13;

mysql> desc t13;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| name | char(5) | YES | | NULL | |

| age | int(2) | YES | | NULL | |

| sex | enum('m','w') | YES | MUL | NULL | |

+-------+---------------+------+-----+---------+-------+

————————————————————————————————————————————————————————

primary key:主键

一个表中只能有一个主键,主键的值和值不可以重复,不可以赋null值

如果表中多个字段都做主键叫复合主键,复合主键必须一起创建

且字段的值不允许同时重复,主键的标志PRI,

主键通常和auto_increment连用,

习惯性把表中能够唯一定位一条记录的字段设置为主键字段

建表时设置主键,

格式:

create table 表名(字段名列表,primary key(字段名));

mysql> create table t14(id int,name char(3),age int,sex enum("m","w"),primary key(id));

mysql> desc t14;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

| name | char(3) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sex | enum('m','w') | YES | | NULL | |

+-------+---------------+------+-----+---------+-------+

把已有表中的字段设置主键,

格式:

alter table 表名 add primary key(字段名);

mysql> alter table t14 add primary key(id);

mysql> desc t14;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

| name | char(3) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sex | enum('m','w') | YES | | NULL | |

+-------+---------------+------+-----+---------+-------+

创建复合主键,(主要复合主键字段的值不同时重复就可以赋值)

格式:

create table 表名(字段名列表,primary key(字段名));

create table sertab(cip varchar(15),sport smallint(2),status enum("allwo","deny"),primary key(cip,sport));

mysql> desc sertab;

+--------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------------------+------+-----+---------+-------+

| cip | varchar(15) | NO | PRI | | |

| sport | smallint(2) | NO | PRI | 0 | |

| status | enum('allwo','deny') | YES | | NULL | |

+--------+----------------------+------+-----+---------+-------+

mysql> insert into sertab values("1.1.1.1",22,"allwo");

mysql> select * from sertab;

+---------+-------+--------+

| cip | sport | status |

+---------+-------+--------+

| 1.1.1.1 | 22 | allwo |

+---------+-------+--------+

主键和auto_increment连用的方式

create table t15(id int(2) primary key auto_increment,name char(10),age tinyint(2) unsigned,sex enum("m","w") default "m",index(name));

mysql> desc t15;

+-------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+----------------+

| id | int(2) | NO | PRI | NULL | auto_increment |

| name | char(10) | YES | MUL | NULL | |

| age | tinyint(2) unsigned | YES | | NULL | |

| sex | enum('m','w') | YES | | m | |

+-------+---------------------+------+-----+---------+----------------+

mysql> insert into t15 (name,age,sex) values("tom",20,"m");(没给id赋值,则自增长)

mysql> select * from t15;

+----+------+------+------+

| id | name | age | sex |

+----+------+------+------+

| 1 | tom | 20 | m |

+----+------+------+------+

删除主键,

格式:

alter table 表名 drop primary key;

mysql> alter table t14 drop primary key;

mysql> desc t14;

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| id | int(11) | NO | | 0 | |

| name | char(3) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| sex | enum('m','w') | YES | | NULL | |

+-------+---------------+------+-----+---------+-------+

——————————————————————————————————————————————————————————————————

unique:唯一索引

一个表中可以有多个unique字段,字段值不可以重复,但可以赋null值标志

当把unique字段设置为不允许为null时,约束方式和主键一样

创建表时创建unique字段,

格式:

create table 表名(字段名列表,unique(字段名),unique(字段名));

mysql> create table hh(id int primary key auto_increment,stu_id char(9),name char(10),tel char(11),age tinyint(2) unsigned,sex enum("m","w") default "m",index(name),unique(stu_id),unique(tel));

mysql> desc hh;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| stu_id | char(9) | YES | UNI | NULL | |

| name | char(10) | YES | MUL | NULL | |

| tel | char(11) | YES | UNI | NULL | |

| age | tinyint(2) unsigned | YES | | NULL | |

| sex | enum('m','w') | YES | | m | |

+--------+---------------------+------+-----+---------+----------------+

mysql> insert into hh(stu_id,name,tel,age,sex)values("fbi","hydra","1520029989",20,"m");

mysql> insert into hh(stu_id,name,tel,age,sex)values("NSA","xx","152002",20,"m");

mysql> insert into hh(stu_id,name,tel,age,sex)values("CAI","alic","15200299899",20,"m");

mysql> select * from hh;

+----+--------+-------+-------------+------+------+

| id | stu_id | name | tel | age | sex |

+----+--------+-------+-------------+------+------+

| 1 | fbi | hydra | 1520029989 | 20 | m |

| 2 | CAI | alic | 15200299899 | 20 | m |

| 4 | NSA | xx | 152002 | 20 | m |

+----+--------+-------+-------------+------+------+

把已有表中字段设置为unique字段,

格式:

create unique index 索引名 on 表名(字段名);

mysql> create unique index name on t1(name);

mysql> desc t1;

+-------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| name | char(10) | YES | UNI | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+-------+---------------------+------+-----+---------+-------+

删除uniq字段索引

drop index 索引名 on 表名

mysql> drop index name on t1;

————————————————————————————————————————————————————————————————————————

查看已有表使用的存储引擎(查看mysql服务默认使用的存储引擎)

格式:

show create table 表名;

mysql> show create table hydra;

+-------+-------------------------------+

| Table | Create Table |

+-------+-------------------------------+

| hydra | CREATE TABLE `hydra` ( |

| `name` char(3) DEFAULT NULL, |

| `age` int(11) DEFAULT NULL |

|) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ENGINE=InnoDB(默认使用innodb)DEFAULT CHARSET=latin1(字符集)

+-------+-------------------------------+

修改mysql默认使用的存储引擎

创建表时设置表使用的存储引擎

格式:

create table 表名(字段名列表)engine=存储引擎名;

设置表使用的字符集:

格式:create table 表名(字段名列表)DEFAULT CHARSET=utf-8;

修改表使用的存储引擎

格式:alter table 表名 engine=存储引擎名;

——————————————————————————————————————————————————————————————————————————

foreign key:外键

表的存储引擎必须是innodb,字段的类型必须要一致,

主表的被参考字段必须是索引的一种(primary key)

保证数据的一致性

实例:

mysql> create table jftab(jf_id int(2) primary key auto_increment,name char(5),class char(9),xf float(7,2) default 18000);

mysql> insert into jftab(name,class)values("hydra","NSA"),("xxx","CAI");

mysql> desc jftab;

+-------+------------+------+-----+----------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+----------+----------------+

| jf_id | int(2) | NO | PRI | NULL | auto_increment |

| name | char(5) | YES | | NULL | |

| class | char(9) | YES | | NULL | |

| xf | float(7,2) | YES | | 18000.00 | |

+-------+------------+------+-----+----------+----------------+

mysql> select * from jftab;

+-------+-------+-------+----------+

| jf_id | name | class | xf |

+-------+-------+-------+----------+

| 3 | hydra | NSA | 18000.00 |

| 4 | xxx | CAI | 18000.00 |

+-------+-------+-------+----------+

mysql> create table bjtab(bj_id int(2),name char(15),xf float(7,2) default 18000,foreign key(bj_id) references jftab(jf_id) on update cascade on delete cascade);(给bj_id做外键,bjtab表中的bj_id和jftab表中的jf_id同步更新,删除)

mysql> insert into bjtab values(4,"xxx",18000);

mysql> update jftab set jf_id=8 where name="xxx";(更新)

mysql> desc bjtab;

+-------+------------+------+-----+----------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+----------+-------+

| bj_id | int(2) | YES | MUL | NULL | |

| name | char(15) | YES | | NULL | |

| xf | float(7,2) | YES | | 18000.00 | |

+-------+------------+------+-----+----------+-------+

mysql> select * from bjtab;

+-------+------+----------+

| bj_id | name | xf |

+-------+------+----------+

| | | |

| 4 | xxx | 18000.00 |

+-------+------+----------+

mysql> select * from jftab;

+-------+-------+-------+----------+

| jf_id | name | class | xf |

+-------+-------+-------+----------+

| 3 | hydra | NSA | 18000.00 |

| 8 | xxx | CAI | 18000.00 |

+-------+-------+-------+----------+

mysql> delete from jftab where name="xxx";(删除)

mysql> select * from bjtab;

+-------+------+----------+

| bj_id | name | xf |

+-------+------+----------+

| 3 | x | 18000.00 |

+-------+------+----------+

mysql> select * from jftab;

+-------+-------+-------+----------+

| jf_id | name | class | xf |

+-------+-------+-------+----------+

| 3 | hydra | NSA | 18000.00 |

+-------+-------+-------+----------+

mysql> alter table bjtab drop foreign key bjtab_ibfk_1;(删除外键bjtab_ibfk_1(外键名))

——————————————————————————————————————————————————————————————————

mysql以线程的方式工作

mysql组成:

mysql工作过程(8个组件)

连接池——》sql接口——》分析器——》优化器——》查询缓存(物理内存划分给mysql,存储客户端连接服务器后查找过的数据)——》存储引擎——》文件系统——》管理工具(mysql提供的命令)

mysql存储引擎:

存储引擎就是提供mysql数据库服务软件自带的程序,用来处理表的处理器

不同的存储引擎有不同的功能和数据存储方式

查看支持哪些存储引擎,和默认的引擎

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

修改mysql默认的存储引擎:

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

default-stroage-engine=存储引擎名

[root@mysql ~]# /etc/init.d/mysql restart(重启)

工作中使用的是myisam和innodb多

myisam特点:独享表空间,支持表级锁,加锁的目的防止客户端并发冲突

不支持事务和事务回滚

表名.frm 表结构(desc 表名)

表名.MYD 记录(select * from 表名;)

表名.MYI 索引信息(index信息)

innodb特点:共享表空间,支持行级锁,加锁的目的防止客户端并发冲突

支持事务和事务回滚

表名.frm 表结构 (desc 表名)

表名.ibd 索引信息+记录

如何决定使用哪种存储引擎?

执行查询操作多的表,使用myisam存储引擎

执行写操作多的表,使用innodb存储引擎

事务和事务回滚:对数据库里的数据做操作室,从开始到结束的过程叫做事务,

在事务执行过程中,只要任意一部操作失败,

就恢复之前的所有操作叫事务回滚

事务日志文件:

[root@mysql ~]# cd /var/lib/mysql/

ib_logfile0

ib_logfile1

ibdata1

————————————————————————————————————————————————————————————————————————

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值