索引简介 跟存储引擎有很大关系其实就是一种排序，生成一种算法，索引主要用在大数据量的时候使用，数据小根本没必要
索引在mysql中也叫做键，是存储引擎用于快速找到记录的一种数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大的时候，索引对于性能的影响越发重要。
索引优化应该是最查询性能优化的最有效的手段了，索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则要从几百页中逐页去查。
索引优缺点
优点
加快访问速度
加强行的唯一性
缺点
带索引的表在数据库中需要更多的存储空间
操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新
按照下列标准选择建立索引的列
频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作联接的列（主键/外键）
请不要使用下面的列创建索引
仅包含几个不同值的列
表中仅包含几行
查询时减少使用*返回全部列，不要返回不需要的列
索引应该尽量小，在字节数小的列上建立索引
WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
避免在ORDER BY子句中使用表达式
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理
索引的分类
普通索引
唯一索引 ：主键本身就是一钟特殊的唯一索引
全文索引
单列索引
多列索引
空间索引
准备实验环境
批量插入记录：mysql>delimiter $$ //定义分隔符
Mysql>Create procedure autoinsert1() //创建存储过程
Begin
Declare i int default 1;
While (1<200000)do
Insert into school.t2 values(i,’ccc’); //这个表必须是事先存在的，
Set i=i+1;
End while;
End$$
Mysql>delimiter;//把分隔符还原回来
Mysql>Call autoinsert1(); //调用存储过程
创建索引(创建表时)注意事项：一开始就有索引会有个如果你批量导入数据的时候，因为某个字段上存在索引，所以速度会非常慢。
语法：create table 表名(
字段一 数据类型 [完整性约束条件.....],
字段一 数据类型 [完整性约束条件.....],
[unique|fulltext|spatial] index |key [索引名] (字段名[长度] [asc |desc])
);
创建普通索引创建表时实例：
Create table department(
Dept_id int primary key,
Dept_name varchar(30),
Commnet varchar(50),
Index index_dept_name (dept_name)
);
查看索引：desc 表名或者show create table表名 \G
创建唯一索引创建表时实例：
Create table department(
Dept_id int primary key,
Dept_name varchar(30),
Commnet varchar(50),
Unique Index index_dept_name (dept_name)
);
创建全文索引创建表时实例：
Create table department(
Dept_id int primary key,
Dept_name varchar(30),
Commnet varchar(50),
fulltext Index index_dept_name (dept_name)
)engin=myisam;
创建多列索引创建表时实例：
Create table department(
Dept_id int primary key,
Dept_name varchar(30),
Commnet varchar(50),
Index index_dept_name (dept_name,commnet)
);
模拟查询分析查询语法
Explain select * from dept 4 where commnet=’sale’ \G
创建索引(已存在的表create)
复制表的内容和结构 将t2表的所有数据复制到t4表上
Create table t4 select * from t2;
只复制表的结构 将t2表的结构复制到t4表上
Create table t4 select * from t2 where 1=2;
或者create table t5 like t2;
语法：create [unique|fullext|spatial] index 索引名 on 表名(字段名[(长度)][asc|desc]);
创建普通索引实例：
Create index index_dept_name on departmnet (dept_name);
创建唯一索引实例：
Create unique index index_dept_name on departmnet (dept_name);
创建全文索引实例：
Create fulltext index index_dept_name on departmnet (dept_name);
创建多列索引实例：
Create index index_dept_name on departmnet (dept_name,comment);
创建索引(已存在的表alter table)
语法：alter table 表名 add [unique|fullext|spatial] index 索引名 (字段名[(长度)][asc|desc]);
创建普通索引实例：
Alter table department add index index_dept_name (dept_name);
创建唯一索引实例：
Alter table department add unique index index_dept_name (dept_name);
创建全文索引实例：
Alter table department add fulltext index index_dept_name (dept_name);
创建多列索引实例：
Alter table department add index index_dept_name (dept_name,comment);
管理索引
查看索引
Show create table 表名\G
测试示例
EXPLAIN select * from department where dept_name=’hr’;
删除索引
Drop index 索引名 on 表名;
视图简介：安全和简化操作
Mysql视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包括一系列带有名称的列和行数据，但是视图并不在数据库中以存储的数据值集形式存在，行和列数据来自由定义视图的查询所引用的表，并且在引用视图的动态生成，对其中所引用的基础表来说，mysql视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图，通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的sql查询语句，它主要出于两种原因，安全原因，视图可以隐藏一些数据，如一些敏感的信息，另一原因是可以使复杂的查询便于理解和使用
创建视图
语法一
Create [algorithm={undefined |merge|temptable}]
View 视图名[(字段1,字段2.....)] as select语句
[with [caseaded|local] check option];
语法二
Create view 视图名 as select 语句;
实例：
Create database view; //创建一个数据库，建议创建
Use view
Create view view_user
As select user,host,password from mysql.user;
Select * from view_user;
查看视图
Show tables 查看视图名
Use view;
Show tables;
Show tables status
实例：查看view数据库中视图以及所有表详细信息
Show table status from view\G
实例：查看view数据库中视图名view_user详细信息
Show table status from view like “view_user” \G
查看视图定义信息
Use view;
Show create view view_user \G
查看视图结构
Use view;
Desc view_user;
修改视图
方法一删除后创建
Use view;
Drop view view_user;
Create view view_user as select user,host from mysqk.user;
Select * from view_user;
方法二：替换原有视图
语法：create or replace view 视图名 as select 语句;
实例：use view;
Create or replace view view_user as select user,password from mysql.user;
方法三：alter 修改视图
语法：alter view 视图名 as select 语句;
实例：use view;
Alter view view_user as select user,password from mysql.user;
通过视图操作基表
查询数据 select 通常都是查询操作
Select * from view_user;
更新数据update
删除数据delect
删除视图
Drop view 视图名
实例：
Use view;
Drop view view_user;
触发器简介
触发器(triggers)是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由时间来触发，比如当对一个表进行操作(insert,delect,update)时就会激活它执行,触发器经常用于加强数据的完整约束和业务规则等
例如：当学生表中增加一个学生的信息时，学生的总数就应该同时改变，因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保障学生总数和记录数一致性。
创建触发器:同一张表最多可以创建六个触发器
语法：create trigger 触发器名称 before|after 触发事件
On 表名 for each row
Begin
触发器程序体;
End
触发器名称：//最多64字符
for each row //触发器的执行间隔,这句话的意思是指的是针对每一行，如果不加就是针对整张表
Insert|update|delete//触发的事件
On表名//在哪张表上建立触发器
触发器程序体：//触发器所触发的sql语句，语句可以使用顺序，判断，循环等语句，实现一般程序需要的逻辑功能。
实例：
创建表
Create table student(
Id int unsigned auto_incremnet primary key not null,
Name varchar(50)
);
unsigned //无符号修饰符
往表里插入数据
Insert into student values(1,’jack’);
创建表2总数表
Create table student_total (total int);
往数表中插入数据
Insert into student_total values(1);
创建触发器实例一student_insert_trigger
Mysql>Delimiter && //定义结束符
Mysql>Create trigger student_insert_trigger after insert
On student for each now
Begin
Upadte student_total set total=total+1;
End&&
Mysql>Delimiter ;
创建触发器实例二
Mysql>Delimiter && //定义结束符
Mysql>Create trigger student_insert_trigger after delect
On student for each now
Begin
Upadte student_total set total=total-1;
End&&
Mysql>Delimiter ;
测试效果，往学生表中插入数据，查看总数表结果，
Insert into student values(2,’Alice’);
Select * from student_total ;
查看触发器
方法1通过show triggers语句查看
案例：show triggers\G
方法2通过系统表triggers查看
案例：use information_schema
Select * from triggers\G
SELECT * FROM TRIGGERS WHERE TGIGGER_NAME=’触发器名称’\G
删除触发器
Drop trigger 触发器名称
实例：Drop trigger student_insert_trigger;
触发器的案例
案例一：增加tab1表记录后自动将记录增加到tab2中,能同步的字段的数据类型肯定要必须一致才行。
创建tab1
Drop table if exists tab1;
Create table tab1(
Tab1_id varchar(11)
);
创建tab2
Drop table if exists tab2;
Create table tab2(
Tab2_id varchar(11)
);
触发器：after_tab1_trigger
作用：增加tab1表记录后自动将记录增加到tab2中
Mysql>Delimiter && //定义结束符
Mysql>drop trigger if exists tab1_after_trigger;
Mysql>Create trigger tab1_after_trigger after insert
On tab1 for each now
Begin
Insert into tab2(tab2_id) values (new tab1_id); new关键字指的是tab1插入以后的新增值，在删除的时候就应该是用old关键字
End&&
Mysql>Delimiter ;
案例三：
当我们更新学生表学生信息的时候，我们学生更新表也对应的改变。
创建表
Drop table if exists student;
Create table student(
student_id int auto_incremnet primary key not null,
Student varchar(30) not null,
Student_sex enum(‘m’,’f’) default ‘m’
);
插入数据：
Insert into student values
(1,’jack’,’m’),
(2,’robin’,’m’),
(3,’alice’,’f’);
创建tab2
Drop table if exists update_student;
Create table update_student(
Update_record int auto_incremnet primary key not null,
Student_id int not null,
Update_date date
);
插入数据：
Insert into update_student values
(1,1,now()),
(2,2,now()),
(3,3,now());
创建触发器ustudent_trigger
Mysql>Delimiter && //定义结束符
Mysql>drop trigger if exists student_update_trigger;
Mysql>Create trigger student_update_trigger before update
On studnet for each now
Begin
If new.student_id!=old.student_id then
Update update_student
Set student_id=new.student_id
Where student_id=old.student_id;
End if;
End&&
Mysql>Delimiter ;
改后的值叫new值，改之前叫做old值，这句话表示学生id被修改后，就会把学生id新值也会赋值给update_student表
删除同步操作案例
Mysql>Delimiter && //定义结束符
Mysql>drop trigger if exists student_delete_trigger;
Mysql>Create trigger student_delete_trigger before delete
On studnet for each now
Begin
Delete from update_student
Where student_id=old.student_id;
End&&
Mysql>Delimiter ;
本文转自 探花无情 51CTO博客，原文链接:http://blog.51cto.com/983865387/1917419
