mysql管理与事务与引擎

mysql索引

索引即为数据表的目录，在目录中可以快速找寻匹配的数据，索引种类有五种

• 普通索引
• 唯一性索引
• 主键索引（主索引）
• 复合索引
• 全文索引

索引的优缺点

优点：

• 由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会很快，查询时间会减少
  缺点：

• 索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。

• 索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。

查询索引

desc mt2;
show index from mt2\G;  //已详细的方式显示

删除索引

alter table mt2 drop key index_age;   //怎么创建的怎么删

普通索引

最基本的索引，不具备唯一性，仅加快查询速度

创建表时添加索引

create table mt2 (
id int primary key auto_increment,
name varchar(50),
age int(3),
index index_name(name),
key index_age(age));

修改表时添加索引

alter table mt2 add index index_age(age);

唯一索引

索引列的所有值都只能出现一次，即必须唯一，用来约束内容。
唯一性允许有NULL值<允许为空>。

创建表时添加索引

create table mt3 (
id int primary key auto_increment,
name varchar(50),
age int(3),
unique index index_name(name));

修改表时添加索引

alter table mt3 add unique index index_age(age);

主键索引

主键索引就是主键，主键索引不能有NULL，唯一性索引可以有空值

创建表时添加索引

create table mt3 (
id int primary key auto_increment,
name varchar(50),
age int(3));

修改表时添加索引

alter table mt3 modify column id int primary key;

删除主键

设置自增长后不能删除

alter table mt3 drop primary key;

复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引
复合索引专门用于组合搜索，其效率大于索引合并

创建表时添加索引

create table mt4 (
id int primary key auto_increment,
name varchar(50),
age int(3),
key index_name(name,age);

修改表时添加索引

alter table mt5 add index index_name(name,age);

删除索引

alter table mt5 drop index index_name;

全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。

引擎种类

• myisam
  • 不支持事务
  • 表级锁，数据更新锁定整个表
  • 数据库在读写过程中相互阻塞
  • 可以通过缓冲区大小来设置索引
  • 对数据读取或写入速度快占用资源少
  • 不支持外键索引，只支持全文索引
• innodb
  • 支持事务
  • 行级锁定
  • 具备缓存性
  • 支持分区表空间
  • 支持外键，但不支持全文索引

配置数据库引擎

查看表使用的引擎

show table status from mydb where name='my'\G;  //mydb1是数据库的名字，ｍｙ是表的名字，可以随意更改
show create table my; //my表的名字

修改存储引擎

vi /etc/my.cnf
default-storage-engine=MyISAM; //默认引擎是innodb

创建表制定引擎

create table my (id, name) engine=MyISAM;

修改已创建的表的引擎

alter table user_info engine=MyISAM; //user_info=你的表名

命令行修改引擎

mysql_convert_table_fromat -u root -p -S /var/lib/mysql/mysql.sock -e MyISAM mydb //-u 是指定用户，-p是用户密码

创建表时添加索引

create table mt4 (
id int primary key auto_increment,
name varchar(50),
age int(3)
fulltext key index_name(name));

修改表时添加索引

alter table mt5 add fulltext index_name(name);

删除索引

alter table mt5 drop index index_name;

事务

事务时一种机制，一个操作序列，把所有的命令作为一个整体一起向数据库提交或者撤销

事务的特性

事务遵循ACID特性

A原子性：事务是一个完整的操作，各个元素不可分开，要么都执行，要么都不执行
C一致性：数据在执行前和执行后要具有一致性
I隔离型：事务之间是彼此隔离的，事务不会影响事务
D持久性：不管发生什么，事务一旦被提交就会永久保存

事务操作

数据库操作：

begin # 开始事务，只对数据有用，对添加数据库和表不管用
commit # 提交事务
savepoint p1 # 保存节点
rollback to p1 # 回滚到保存节点
rollback # 回滚事务
set autocommit=0 # 禁止自动提交 //添加到数据库里
set autocommit=1 # 允许自动提交
``