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

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

 

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