MySQL索引，事务与存储引擎
- 数据库索引
- 索引的作用
- char和varchar的区别
- 索引的分类
- 创建索引的原则依据
- 事务的概念
数据库索引
1.是一个排序的列表，存储着索引值和这个值所对应的物理地址，相当于一本书的目录
2.无须对整个表进行扫描，通过物理地址就可以找到所需数据
3.是表中一列或者若干列值排序的方法
4.需要额外的磁盘空间
索引的作用
1.数据库利用各种快速定位技术，能够大大加快查询速率
2.当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度
3.可以降低数据库的IO成本，并且还可以降低数据库的排序成本
4.通过创建唯一性索引保证数据表数据的唯一性
5.可以加快表与表之间的连接
6.在使用分组和排序时，可大大减少分组和排序时间
char和varchar的区别
1.char的长度是不变的，而varchar的长度是可变的
字段b:类型char（10） 值为：abc 存储为：abc (abc+7个空格)
字段d:类型varchar（10） 值为：abc 存储为：abc （自动变为3个的长度）
2.超出长度自动截取
字段c:类型char（3） 值为：abcdefg 存储为：abc (defg自动删除)
字段e:类型varchar（3） 值为：abcdefg 存储为：abc（defg自动删除）
3.varchar(10)和char(10)，都表示可存10个字符，无论存放的是数字，字母还是UTF8汉字（每个汉字3字节），都可以存放十个
4. char最多可以存放255个字符
varchar最大长度为65535个字节，varchar可存放的字符跟编码有关
字符类型若为gbk，每个字符最多占2字节，最大长度不能超过32766个字符
字符类型若为utf8，每个字符最多占3字节，最大长度不能超过21845个字符
UTF-8:一个汉字=3个字节
GBK：一个汉字=2个字节
5.char和varchar的最大长度限制是MySQL规定的
索引的分类
普通索引
创建表结构时创建
mysql> create table user(id int(4) not null,name char(48) not null,age int(3) not null,sex varchar(36) not null,primary key (name),index index_id (id));
mysql> show index from user; #查看索引
直接创建索引
mysql> create index index_name on user(name);
mysql> show index from user;
修改表结构方式添加索引
mysql> alter table user add index index_age(age);
mysql> show index from user;
唯一性索引
直接创建索引
mysql> create unique index index_name on user(name); #如果创建的索引存在，需要先删除
mysql> show index from user;
修改表结构方式添加索引
mysql> alter table user add unique index_name(name);
mysql> show index from user;
主键索引
创建主键索引的方式
mysql> create table test(id int(4) not null,name char(20) not null,age int(3) not null,primary key(id));
查看索引
mysql> show index from 表名;
mysql> show keys from 表名;
删除索引
mysql> drop index 索引名 on 表名;
mysql> alter table 表名 drop index 索引名;
组合索引
创建组合索引的方式
mysql> create table users(name varchar(9), age int(3),sex tinyint(1),index users(name,age,sex));
mysql> show index from users;
全文索
直接创建索引
mysql> create index index_id on user(id);
mysql> show index from user;
修改表结构方式添加索引
mysql> alter table user add index index_age(age);
mysql> show index from user;
扩展
两张表之间建立内联
mysql> create table test1(id int(10) not null auto_increment,name varchar(64) not null,score int(3) not null,hobby int(2),primary key(id));
mysql> insert into test1(id,name,score,hobby) values(1,‘zhangsan’,80,2),(2,‘lisi’,81,3),(3,‘wangwu’,88,1);
mysql> select * from test1;
mysql> create table aihao(id int(10) primary key,aihao_name varchar(40) not null);
mysql> insert into aihao(id,aihao_name) values(1,‘看书’),(2,‘聊天’),(3,‘美术’);
mysql> select * from aihao;
mysql> select * from test1 inner join aihao on test1.hobby=aihao.id;
mysql> create table test1(id int(10) not null auto_increment,name varchar(64) not null,score int(3) not null,hobby int(2),primary key(id));
mysql> insert into test1(id,name,score,hobby) values(1,‘zhangsan’,80,2),(2,‘lisi’,81,3),(3,‘wangwu’,88,1);
mysql> select * from test1;
mysql> create table aihao(id int(10) primary key,aihao_name varchar(40) not null);
mysql> insert into aihao(id,aihao_name) values(1,‘看书’),(2,‘聊天’),(3,‘美术’);
mysql> select * from aihao;
mysql> select * from test1 inner join aihao on test1.hobby=aihao.id;
创建索引的原则依据
1、表的主键、外键必须有索引
2、记录数超过300行的表应该有索引
3、经常与其他表进行连接的表，在连接字段上应该建立索引
4、唯一性太差的字段不适合建立索引
5、更新太频繁的字段不适合建立索引
6、经常出现在where子句中的字段，特别是大表的字段，应该建立索引
7、索引应该建立在选择性高的字段上
8、索引应该建立在小字段上，对于大的文本字段甚至超长字段，不要建立索引
事务的概念
1、是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
2、是一个不可分隔的工作逻辑单元，在数据库系统上执行并发操作时，事务时最小的控制单元
3、适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
4、通过事务的整体性以保证数据的一致性
事务的特点
1、原子性
事务是一个完整的操作，事务的个元素是不可分的
事务中的所有元素必须作为一个整体提交或回滚
如果事务中的任何元素失败，则整个事务将失败
2、一致性
当事务完成时，数据必须处于一致状态
在事务开始前，数据库中存储的数据处于一致状态
在正在进行的事务中，数据可能处于不一致的状态
当事务成功完成时，数据必须再次回到已知的一致状态
3、隔离性
对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据
4、持久性
指不管系统是否发生故障，事务处理的结果都是永久的
一旦事务被提交，事务的效果会被永久地保留在数据库中
事务控制语句
1、MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交
2、事务控制语句
BEGIN或START TRANSACTION
ROLLBACK
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
ROLLBACK TO identifier
SET TRANSACTION
### 事务的控制方法
事务处理命令控制事务
BEGIN：开始一个事务
COMMIT：提交一个事务
ROLLBACK：回滚一个事务
使用set命令进行控制
set autocommit=0：禁止自动提交
set autocommit=1：开启自动提交
事务的操作
1 mysql> begin; #开始事务
2 mysql> insert into userq1(id,name,score,hobby) values(5,'yy',80,2);
3 mysql> rollback; #回滚
4 mysql> commit; #提交
