数据库索引类型和引擎
一、数据库索引
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 表锁 读操作比较多 系统开销小 但并发访问量低
原文:http://changnan.blog.51cto.com/8825738/1409441