mysql-索引
1、索引的作用:
索引能使查询变快
2、数据库索引包括:
聚集索引
非聚集索引
聚集索引的排列顺序和磁盘上物理数据存储的顺序是一致的,它拥有最快的查询速度,且限定每一个数据表只能有一个聚集索引,这很显然,因为物理数据只能有一种排序规则。
非聚集索引:其只将设定的属性列,按照设定的顺序进行排列后的结果数据保存到数据表中。
单列索引
组合索引
普通索引
唯一索引
主键索引
组合索引
3、外键:
是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之,表的外键就是另一表的主键,外键将两表联系起来。一般情况下,要删除一张表中的主键必须首先要确保其它表中的没有相同外键(即该表中的主键没有一个外键和它相关联)。
4、外键的作用:
保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值!
5、数据引擎:
是将sql语句转换为对数据库的操作的一种中介程序,是实现了对用户的一个接口。数据库引擎是用于存储、处理和保护数据的核心服务包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程)。
索引
1、创建索引:
create index index_name on tb_name(field_name);
(给表tb_name的field_name字段添加索引)
eg: create index index_name on student(name);
2、索引分类:
主键索引:主键索引是一种特殊的唯一索引,他不允许空值。一般会在建表的同时创建主键索引。
创建方式:
在创建表的时候直接添加主键
alter tb_name add primary key index_name on (field_name);
create index index_name on tb_name(field_name);
唯一索引:
创建索引:
create unique index index_name on tb_name(username[length]);
修改表结构:
alter tb_name add unique [index] on (username[length]);
创建表的时候指定索引:在创建的字段最后加上:
unique index_name (username[length])
全文索引:(该索引只有在MyISAM 引擎下才能使用)
修改引擎:engine=MyISAM;
创建全文索引:create fulltext index index_name on tb_name(name[length]);
空间索引:(该索引只有在MyISAM引擎下才能使用)
创建索引:spatial
3、删除索引:
法一:
alter table tb_name drop index index_name;
法二:
drop index index_name on tb_name;
4、添加索引
alter table tb_student add index index_name (id);
5、添加唯一限制条件索引
alter table tb_student add unique emp_name2(id);
CREATE TABLE `student` (
`id` int(11) primary key auto_increment,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL
);
-- 如何往数据库里面插入 1000000 条数据?
DELIMITER //
create procedure proc()
begin
declare var int default 1;
while var <= 1000000
do insert student values(null,'张全蛋',12);
set var = var + 1;
end while;
end //
DELIMITER ;
第一步: 把第 888888 条数据姓名改成 '王尼玛'
update student set name='王尼玛' where id=888888;
第二步: select * from student where name = '王尼玛';
第三步: select * from student where id=888888;
-- id 查询的速度比 name 查询的速度快很多,因为 id 有主键索引
-- 如果 我们想要提高 name 的查询速度,就给 name 创建索引
第四步: CREATE INDEX index_name ON student(name);
第五步: select * from student where name = '王尼玛';
----------------------------------------------------------------
-- 查看查询过程 explain
----------------------------------------------------------------
explain select * from student where id = 999900;
****************************
-- 数据库索引
****************************
----------------------------------------------------------------
-- 1、主键索引
----------------------------------------------------------------
****************************
-- 创建索引的语法:3 种
****************************
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
-- 方式一
CREATE TABLE tableName(
id INT NOT NULL,
username VARCHAR(16) NOT NULL,
PRIMARY KEY(id)
);
-- 方式二
ALTER tableName ADD PRIMARY KEY [indexName] ON (id);
-- 方式三
CREATE INDEX indexName ON tableName(name[length]);
----------------------------------------------------------------
-- 2、普通索引
----------------------------------------------------------------
****************************
这是最基本的MySQL数据库索引,
它没有任何限制。
它有以下几种创建方式:
****************************
-- 2.1 创建索引
CREATE INDEX indexName ON tableName(name[length]);
-- 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
-- 2.2 修改表结构
ALTER tableName ADD INDEX [indexName] ON (name[length])
-- 2.3 创建表的时候直接指定
CREATE TABLE tableName(
id INT NOT NULL,
name VARCHAR(16) NOT NULL,
INDEX [indexName] (name[length])
);
----------------------------------------------------------------
-- 3、唯一索引
----------------------------------------------------------------
它与前面的普通索引类似,不同的就是:
MySQL数据库索引列的值必须唯一,但允许有空值。
如果是组合索引,则列值的组合必须唯一。
-- 3.1 创建索引
CREATE UNIQUE INDEX indexName ON tableName(username[length]);
-- 3.2 修改表结构
ALTER tableName ADD UNIQUE [indexName] ON (username[length]);
-- 3.3 创建表的时候直接指定
CREATE TABLE mytable(
id INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username[length])
);
----------------------------------------------------------------
-- 4、全文索引(MyISAM 引擎下才能使用)需要先修改引擎
----------------------------------------------------------------
CREATE TABLE `student2` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
-- 给 name 创建全文索引
CREATE FULLTEXT INDEX indexName ON tableName(name[length]);
----------------------------------------------------------------
-- 5、空间索引(MyISAM 引擎下才能使用)
----------------------------------------------------------------
SPATIAL
----------------------------------------------------------------
-- 6、组合索引/多列索引
----------------------------------------------------------------
CREATE TABLE mytable(
id INT NOT NULL,
name VARCHAR(16) NOT NULL,
city VARCHAR(50) NOT NULL,
age INT NOT NULL
);
-- 为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表时,name 16,这里用 10。
这是因为一般情况下名字的长度不会超过10,
这样会加速索引查询速度,
还会减少索引文件的大小,
提高INSERT的更新速度。
如果分别在 name,city,age上建立单列索引,
让该表有3个单列索引,
查询时和上述的组合索引效率也会大不一样,
远远低于我们的组合索引。
虽然此时有了三个索引,
但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
非组合索引:
age
city
name
建立这样的组合索引,其实是相当于分别建立了下面三组组合MySQL数据库索引:
name age ,
name city
为什么没有 city age 这样的组合索引呢?
这是因为MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,
下面的几个SQL就会用到这个组合MySQL数据库索引:
SELECT * FROM mytable WHREE name="admin" AND city="郑州";
SELECT * FROM mytable WHREE name="admin"
而下面几个则不会用到:
SELECT * FROM mytable WHREE age=20 AND city="郑州";
SELECT * FROM mytable WHREE city="郑州";
****************************
-- 删除索引的语法:2 种
****************************
-- 方式一
ALTER TABLE 表名 DROP INDEX 索引名;
-- 方式二
DROP INDEX [索引名] ON 表名;
----------------------------------------------------------------
-- 7、索引的优点和缺点?
----------------------------------------------------------------
----------------------------------------------------------------
-- 数据库引擎
----------------------------------------------------------------
http://blog.csdn.net/gaohuanjie/article/details/50944782