数据库之索引、事务

一 索引简介

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。
索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！

二 索引语法

----创建表时

--语法：
    CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );

----创建普通索引示例：
    CREATE TABLE emp1 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        INDEX index_emp_name (name)
    ----KEY index_dept_name (dept_name)----
        );

----创建唯一索引示例：
    CREATE TABLE emp2 (
        id INT,
        name VARCHAR(30) ,
        bank_num CHAR(18) UNIQUE ,
        resume VARCHAR(50),
        UNIQUE INDEX index_emp_name (name)
        );

----创建全文索引示例：
    CREATE TABLE emp3 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        FULLTEXT INDEX index_resume (resume)
        );


----创建多列索引示例：
    CREATE TABLE emp4 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        INDEX index_name_resume (name, resume)
        );

–添加索引
----CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;

----ALTER TABLE在已存在的表上创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;

 CREATE INDEX index_emp_name on emp1(name);
 
 ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);

– 删除索引
语法：DROP INDEX 索引名 on 表名

DROP INDEX index_emp_name on emp1;
DROP INDEX bank_num on emp2;

三 索引测试实验

--创建表
create table Indexdb.t1(id int,name varchar(20));


--存储过程

delimiter $$
create procedure autoinsert() 
BEGIN
declare i int default 1;
while(i<500000)do
insert into Indexdb.t1 values(i,'yuan');
set i=i+1;
end while;
END$$

delimiter ;

--调用函数
call autoinsert();

-- 花费时间比较：
-- 创建索引前
   select * from Indexdb.t1 where id=300000;--0.32s
-- 添加索引 
   create index index_id on Indexdb.t1(id);
-- 创建索引后
   select * from Indexdb.t1 where id=300000;--0.00s

事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。
– start transaction 开启事务
– Rollback 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句)，回滚到上一次commit的位置
– Commit提交事务，提交未存储的事务
– savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;
INSERT INTO test2(name) VALUE ("alvin"),
                              ("yuan"),
                              ("xialv");



start transaction;
insert into test2 (name)values('silv');
select * from test2;
commit;


-- 保留点

start transaction;
insert into test2 (name)values('wu');
savepoint insert_wu;
select * from test2;



delete from test2 where id=4;
savepoint delete1;
select * from test2;


delete from test2 where id=1;
savepoint delete2;
select * from test2;

rollback to delete1;


select * from test2;

savepoint