数据库的索引和存储引擎

什么是数据库索引？

是一个排序的列表，存储着索引值和这个值所对应的物理地址
无须对整个表进行扫描，通过物理地址就可以找到所需数据
是表中—列或者若干列值排序的方法
需要额外的磁盘空间

索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率
当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度
可以降低数据库的IO成本，并且还可以降低数据库的排序成本
通过创建唯一性索引保证数据表数据的唯一性
可以加快表与表之间的连接
在使用分组和排序时，可大大减少分组和排序时间

普通索引

最基本的索引类型，没有唯一性之类的限制
1、直接创建索引

mysql> create  index  索引名称   on  表名称(字段名称);

2、修改表方式创建索引

mysql> alter   table   字段名称  ADD  index   索引名称(字段名称);

3、创建表时创建索引

mysql> create table 表名称(字段1  类型,字段2  类型,字段3 类型.....,index  索引名称 (字段名)); 

唯一索引

与“普通索引”基本相同
与普通索引的区别是索引列的所有值只能出现一次，即必须唯一
1、直接创建索引

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

2、修改表方式创建索引

mysql> alter   table   字段名称  ADD  index   索引名称(字段名称);

3、创建表时创建索引

mysql> create table 表名称(字段1  类型,字段2  类型,字段3 类型.....,index  索引名称 (字段名)); 

主键索引

是一种特殊的唯一索引，指定为"PRIMARY KEY"
一个表只能有一个主键，不允许有空值

mysql> create  table  表名称(字段1  类型,字段2  类型,字段3  类型,......,primary  key (字段名称));

组合索引

可以是单列上创建的索引，也可以是在多列上创建的索引
最左原则，从左往右依次执行

mysql> create table 表名称(字段1  类型,字段2  类型,...., index 表明明(需要组合的字段1,需要组合的字段2,需要组合的字段3,....));

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索
索引类型为FULLTEXT
可以在CHAR、VARCHAR或者TEXT类型的列上创建
1、直接创建索引

mysql> create index 索引名称 on 表明(字段);

2、修改表结构方式添加索引

mysql> alter  table  字段名  add  index  索引名(字段);

查看索引

mysql> show index from 表名;			
mysql> show keys from 表名;

删除索引

mysql> drop index 索引名 on 表名;
mysql> alter table index 表名 drop index 索引名;

俩张表之间建立内联

mysql> select  *  from 表1  inner  join  表2  on  表1.字段=表2.字段;

建立索引视图

create  view  视图名称  as  select  表1.字段,表2.字段  from 表1  inner join 表2  on 表1.字段=表2.字段;        #创建视图
mysql> select * from 视图名称;      #查询视图

事务的操作

begin   #事务的开始
commit	#结束
rollback	#回到前一个状态
savepoint  identifier	#保存当前事务

如果一个事务开始了，如果想回到某一个状态，这个状态是必须保存过的不然直接恢复bengin(开始)状态
例如：
mysql>  begin
mysql>  create database user1;
mysql>  savepoint  identifier;
mysql>  create database user2;
mysql>  rollback;			#可以回到create database user1完成这个状态
如果：没有保存则直接回到begin这个状态
mysql>  begin
mysql>  create database user1;
mysql>  create database user2;
mysql>  rollback;			#直接回到begin这个状态

修改存储引擎

myisam存储引擎：
1、表级锁定形式,数据在更新时锁定整个表
2、数据库在读写过程中相互阻塞
会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
3、数据单独写入或读取,速度过程较快且占用资源相对少
3、MylAM支持的存储格式
静态表
动态表
压缩表
myisam存储引擎使用场景：
公司业务不需要事务的支持
单方面读取或写入数据比较多的业务
MyISAM存储引擎数据读写都比较频繁场景不适合使
用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务一致性要求不是非常高的业务
服务器硬件资源相对比较差
InnoDB存储引擎：
1、支持4个事务隔离级别
2、行级锁定,但是全表扫描仍然会是表级锁定
3、读写阻塞与事务隔离级别相关
4、能非常高效的缓存索引和数据
5、表与主键以簇的方式存储
6、支持分区、表空间，类似oracle数据库
7、支持外键约束,5.5前不支持全文索引,5.5后支持全文索引
8、对硬件资源要求还是比较高的场合
InnoDB存储引擎使用场景：
业务需要事务的支持
行级锁定对高并发有很好的适应能力,但需确保查询是通过索引来完成
业务数据更新较为频繁的场景。如:论坛，微博等
业务数据一致性要求较高。如:银行业务
硬件设备内存较大，利用Innodb较好的缓存能力来提高内存利用率，减少磁盘IO的压力

查看创建表的默认引擎

mysql> show  create   table  表名称;

1、直接修改表的存储引擎

mysql>  alter   table   表名称   engine=引擎;

2、修改数据库配置文件(vim /etc/my.cnf)

[root@server ~]# vim /etc/my.cnf
在[mysqld]下面添加一条
default-storage-engine=InnoDB		#设置数据库的默认引擎为InnoDB

3、创建表时指定存储引擎

mysql>  create  table  表名称(字段1   类型,字段2   类型,....)   engine=引擎;