http://www.verejava.com/?id=1717413695356
1 . 索引
索引(index) 是提升查询效率的数据库对象.
2. 索引分类
1. 唯一性索引和非唯一性索引
按照索引字段是否允许重复来划分的, 一般唯一性索引查询效率最高, 所以MySQL 给主键和唯一键自动分配唯一性索引.
2. 单索引和联合索引
按照索引基于字段的个数来划分. 例如 :如果我们经常在 工资 和 部门编号 经常执行下面的查询 . 可以在 salary 和 dept_id 建立联合索引
SELECT * FROM emp WHERE salary<=6000 AND dept_id=1;
3. 普通索引和函数索引
前面讲的是普通索引 如果我们索引是基于表的字段, 常量, 函数和运算构成的表达式,叫做函数索引.
例如 经常执行 以字母 l 开头的人查询出来 这样就要建立 SUBSTR(name,0,1) 的函数索引
SELECT * FROM emp WHERE SUBSTR(name,0,1)='l';
准备数据:
DROP TABLE emp;
创建表 emp
CREATE TABLE emp
(
id int(10),
name VARCHAR(30),
salary int(10),
sex CHAR(2),
dept_id int(10),
FOREIGN KEY (dept_id) REFERENCES dept(id)
);
批量插入数据
INSERT INTO emp VALUES(1,'王浩',6000,'女',1);
INSERT INTO emp VALUES(2,'王丽',5000,'女',1);
INSERT INTO emp VALUES(3,'李俊',4000,'男',2);
INSERT INTO emp VALUES(4,'张涛',8000,'男',2);
INSERT INTO emp VALUES(5,'李广',9000,'男',2);
INSERT INTO emp VALUES(6,'杨军',7000,'男',2);
3. 创建索引
CREATE [UNIQUE | BITMAP] INDEX 索引名 ON table(列名 ...);
例子:
创建基于 name 字段的 普通索引
CREATE INDEX name_index ON emp(name);
创建基于 dept_id 和 salary 的联合索引
CREATE INDEX deptid_salary_index ON emp(dept_id,salary);
创建基于 id 的唯一索引
CREATE UNIQUE INDEX id_unique_index ON emp(id);
创建基于 dept_id 的位图索引
CREATE BITMAP INDEX dept_bitmap_index ON emp(sex);
创建函数索引
CREATE INDEX name_function_index ON emp(SUBSTR(name,0,1));
查看表建立的所有索引
SELECT index_name,index_type FROM user_indexes WHERE table_name='EMP';
查看表的索引具体建立在那个字段
SELECT index_name,column_name FROM user_ind_columns WHERE table_name='EMP';
注意:
如果索引一旦建立了, 就会由MySQL数据库字段维护, 当对表的数据(INSERT , DELETE, UPDATE,MERGE) 时系统必须同步修改 索引表的数据,
所以在表的数据量很大, 更新频率很多的时候要谨慎使用索引, 不然适得其反.
4. 创建索引的原则
下列情况适合创建索引:
1. 字段取值分布范围很广
2. 字段中包含大量空值
3. 字段经常出现在 where 子句 或 连接条件中
4. 表经常被访问, 数据量很大, 一般每次访问的数据量 < 5%
下列情况不适合创建索引
1. 表很小
2. 字段不经常出现在 where 子句中
3. 每次访问的数据量 > 5%
4. 表经常更新
5. 删除索引
索引一旦创建不可再修改, 如果要修改必须删除掉然后重新创建.
在删除表和字段时 在该表和字段上创建的索引自动删除.
DROP INDEX 索引名