数据库的索引和约束
概念:
数据库使用索引定位表中的行,与普通的的数据表不同,索引是一种以特定顺序保存的专用表。索引并不包含表中的所有数据,而是那些用于定位表中行的列,以及描述这些行的物理位置的信息。因此索引的作用就是便携化检索表中行和列的子集,而不需要检索表中的每行。
1,创建索引
创建表
CREATE TABLE `department` (
`dept_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`dept_id`),
UNIQUE KEY `dept_name_idx` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into `department`(`dept_id`,`name`) values (3,'Administration'),(2,'Loans'),(1,'Operations');
为name列添加索引:
alert table department add index dept_name_idx (name)
MySQL查看索引: SHOW INDEX FROM department;
mysql删除索引: ALERT TABLE department DROP IDEX dept_name_idx; 或者:DROP INDEX dept_name_idx;
oracle 创建索引:
CREATE INDEX dept_name_idx ON department (name);
2,创建唯一索引
除了常规的索引的好处外,还作为一种机制限制索引列出现重复值。
ALTER TABLE department ADD UNIQUE dept_name_idx1 (NAME)
3,创建多列索引
除了单列的索引,还可以创建跨域多列的索引。例如:
ALTER TABLE employee ADD INDEX employee_name_inx (lname,fname)
这个索引对一下两种查询有用:一是指定了姓名(姓氏和名字),二是指定了(姓氏)。但是指定了名字的查询没有用。
因此,在创建所列索引时,读者必须仔细考虑那一列作为第一列,哪一列作为第二例等,这样索引才有用,也可以基于不同顺序为同意列集穿件多列索引。
4,索引的类型
B树索引(平衡树索引),擅长处理包含许多不同值的列,比如客户的姓氏\姓名。
位图索引,处理包含少量值却占据大量行的列。也就是说少量值的每一行都有一个位图,查找这些值得时候就可以快速找到这些行了。oracle 创建位图索引:CREATE BITMAP IDEX nnnnn on accout (product_cd).
文本索引。mysql中只有MyISAM支持,Oracle Text 是强大的工具集。
5,如何使用
分析sql:
EXPLAIN
SELECT cust_id,COUNT(avail_balance)
FROM `account`
WHERE cust_id IN (1,5,9,11)
GROUP BY cust_id
结果如图:
加了为查询结果的两列加索引:
ALTER TABLE account ADD INDEX acc_bal_idx (cust_id, avail_balance)
分析结果如图:
使用了新的索引 acc_bal_idx ,只需要查询8列即可完成。由此可知,服务器可以使用索引定位关联表中的行,或者只要索引包含查询所需要的列,服务器可以把索引当做表一样使用。。。。
7,索引的不足
既然索引这么有用,那么为啥不索引一切?事实上,每个索引都是一张特殊的表,每次对表中的数据进行crud的时候,所有的额索引都要进行修改,因此索引越多,服务器需要做越多的工作来保持所有模式对象的最新,这回拖累服务器的处理速度。。。
仅当出现清晰需求的时候才添加索引。
装载数据前删除索引,然后在仓库开放营业前重建他们。
主键和外键键索引,还有被频繁检索的列。