数据库索引类型和引擎

数据库索引类型和引擎

一、数据库索引

1、 索引的优缺点

优点：能够加快查询速度（相当于书的目录）

缺点：会降低插入、更新表的速度，需要占用磁盘存储空间

2、 索引的类型

INDEX：普通索引

UNIQUE：唯一索引

FULLTEXT：全文索引

PRIMARY  KEY：主键

FOREIGN  KEY：外键

*不同的索引有不同的作用的限制方式

3、 INDEX：普通索引

（1） 当把表中的字段设置为index字段后，会把字段的值用的索引的内置算法排序。

在表中创建index字段要遵循的规则：

使用说明：

一个表中可以有多个index字段

对应的字段值允许重复

把经常用作查询条件的字段设置为index字段

Index字段的KEY标识是MUL

（2） 如何创建index字段

建表时就指定index字段，默认索引名和字段名同名

create  table  t102(

name varchar(10),

sex enum("boy","girl"),

age  tinyint(2),

index(name,sex)

);

show index from t102;   //查看索引表的详细信息

在已有表中创建index索引

mysql>create index 索引名  on  表名（字段）;

mysql>create index name  on  stu_info(name);

删除表中的索引字段

Drop  index  索引名   on  表名；

mysql>drop  index on stu_info;

show  index  from  表名

字段的索引的名字（Key_name） 索引字段（Column_name）

使用算法BTREE 二叉树算法

算法根据记录的条目数自动树的深度

4、 UNIQUE：唯一索引

（1） 使用说明

一个表中可以有多个UNIQUE字段

对应的字段值不允许有重复

UNIQUE字段的KEY标识是UNI

UNIQUE字段的值允许为NULL，当将其修改为不允许为NULL，则此字段限与主键相同

（2） 在已有的表里创建唯一索引  unique

mysql> create  unique  indexstu_id on  t001(stu_id);

（3） 在创建表时创建unique

create table t002(

stu_id char(4),

name varchar(10),

age  tinyint(2)  unsigned  not null,

index(name),

unique(stu_id)

);

删除表中的unique（与删除index索引相同）

drop index  stu_id  on t002

5、 PRIMARY  KEY主键

（1） 注意事项：

*一个表中只能有一个primary字段

*对应的字段值不允许重复

*如果有多个字段都作为primary key，称为复合主键，必须在建表时一起创建

*主键字段的KEY标识是PRI

*通常与auto_increment（作用：让字段自动加1，初始值默认为1）连用

（2） 通常把表中能够唯一定义某个记录的子段设置为primary key字段，

把存放记录编号字段设置为primary key字段

在创建表时创建primary  key

mysql> create table t003(

   -> id int(10) auto_increment,

   -> name varchar(10) not null,

   -> sex enum("boy","girl") default"boy" not null,

   -> age tinyint(2) unsigned not null default 20,

   -> primary key(id),

   -> index(name)

   -> );

赋值：

mysql> insert into t003(name,sex,age) values("baihe","girl",20);

mysql>insertinto t003(id,name,sex,age) values (100,"haoming","boy",20);

mysql> select * from t003;

删除主键字段：（如有自增属性必须先去掉）

alter table t003 drop primary key;

在已有的表 中创建primary  key：

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

在表里创建复合主键

（表中的多个字段同时做主键，只要主键字段的值不同时重复就可以）

6、 外键 FOREIGN  KEY

被关联的表、字段不可以删除，记录可以删除

删除表：drop  表名；

删除字段：alter  table  表名  drop  字段名

删除记录：delete   from  表名  where  字段名=字段值

FOREIGN  KEY  (当前表里的字段名)  references  表B （表B里的字段）

On   update  cascade（同步更新）   on  delete  cascade（同步删除）

Insert  into  表名 （字段名） values （值）；

更新字段： update  表名  set  字段=字段值  where   原字段=字段值；

删除记录：delete  from  表名  where  字段名=字段值

如果在当前表里设置了外键字段，当前表里的外键字段的值，受关联表中字段值的限制

使用外键的条件：

表都采用InnoDB存储引擎

外键字段的数据类型要一致

被关联字段要用明确索引

二、 引擎

1、 什么是存储引擎？

mysql体系结构。 (8部分组成每一部分都不同的作用)              

由8部分组成：

1.客户端：mysql

2.连接器： NativeC API 本地C语言接口、JDBCJAVA的数据库连接器、 ODBC开放式数据库互联，非常底层 Oracle，SOLServer都支持

3.连接池（conetcionpool）：它的后方是MYSQL的真正功能

4.SQL接口：用户通过sql客户端发过来的命令，由sql接口接收

DML数据操作语言：查询，修改，升级数据等

DDL数据定义语言：创建一个新的数据库，新的索引，删除一个用户等存储过程视图触发器）

5.Parser:分析器（查询事务对象权限）

6.optimizer 优化器：（访问路径、生成执行树）

7.caches&buffers：缓存和缓冲（优化里最重要的是缓存的优化）

8.pluggablestroage engines存储引擎（插件式）：将逻辑结构转换为物理结构的程序

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

  innodb

  mysql-server.5.6.rpm

9.ManagementServices & Utilites工具：备份，恢复，安全，移植，集群等，这些工具一般和文件系统打交道，不需要和mysql-server打交道，它们对应的都是命令。

mysql> show engines;

存储引擎名称

数据库服务器是否支持这种存储引擎   YES   支持

                                     NO   不支持

                                    DEFAULT 建表时默认使用的存储引擎

对存储引擎的描述信息：

Innodb  支持事务 和事务回滚，支持行锁  支持外键

一次sql操作从开始到结束的过程 称为事务

通过查看建表过程查看存储引擎：

Show  create  table  表名   查看建表信息

建表时指定表使用的存储引擎：
*不同存储引擎有不同的功能和存储数据方式。

Create  table  表名（字段 类型）engine=memory；  //临时表

修改表的存储引擎：
alter  table 表名  engine=innodb；

修改数据库服务默认使用的存储引擎  /etc/my.cnf

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

.. ..

[mysqld]

Default-storage-engine=xxx

重启服务：service mysql  restart

Myisam 与innodb存储引擎的不同

Maisam

1,、独享表空间

[root@localhost db100]# ls /var/lib/mysql/mysql/user.*

user.frm   表结构

user.MYD 表里数据

user.MYI  索引文件

2、不支持事务

3、支持表锁  不支持行锁

innodb

1、 共享表空间

索引信息和数据在一个文件中（.ibd）

[root@localhost db100]# ls /var/lib/mysql/db100

yg.frm   表结构

yg.ibd   表数据+索引信息

2、 支持事务、事务回滚

3、 支持行锁

Mysql的锁机制：解决用户并发访问的冲突问题。

锁粒度：行锁 、表锁

锁类型：读锁：desc 、select、show    共享锁

写锁：insert、delete、update   互斥锁、排他锁

当当前的记录加了写锁后，后面再来的读请求或写请求都要等待，当前的写锁解锁后才能对当前的记录进行操作，当既有读请求又有写请求时，写请求优先处理

Innodb  行锁  写操作比较多  系统开销大 但并发访问量高

Myisam  表锁  读操作比较多  系统开销小 但并发访问量低

数据库索引类型和引擎,古老的榕树,5-wow.com