MySQL知识点整理
索引
为何要有索引?
索引的功能就是加速查找
一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。
什么是索引？
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
B+Tree
B+树是一个平衡的多叉树。
InnoDB存储引擎就是用B+Tree实现其索引结构。
在B+Tree中，所有的数据都是存放在叶子节点上的，并且是按照键值大小顺序存放的，而非叶子节点上只存储键值信息，这样可以有效的加大每个节点存储的键值数量，从而降低B+Tree的高度。
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）
相同点：
其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
不同点：
聚集索引的叶子结点存放的是整张表的行记录数据
辅助索引的叶子节点不包含行记录的全部数据。
MySQL常用索引
普通索引INDEX：加速查找 唯一索引： -主键索引 PRIMARY KEY：加速查找+约束（不为空、不能重复） -唯一索引 UNIQUE:加速查找+约束（不能重复） 联合索引： -PRIMARY KEY(id,name):联合主键索引 -UNIQUE(id,name):联合唯一索引 -INDEX(id,name):联合普通索引
创建/删除索引的语法
#方法一：创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); #方法二：CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; #方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; #删除索引：DROP INDEX 索引名 ON 表名字;
删除主键索引
如果一个主键是自增长的，不能直接删除该列的主键索引，应当先取消自增长，再删除主键索引。
alter table 表名 drop primary key; 当主键为自增长时 alter table t1 modify id int ; 【重新定义列类型】 alter table t1 drop primary key;
正确使用索引
1、应该选择区分度高的字段作为索引字段
2、范围问题（：>、>=、<、<=、!= 、between...and...、like、），范围过大，即便是有索引速度也会很慢
3、索引字段一定不能参与计算：select * from s1 where salary*12 > 200000;
4、最左前缀匹配原则:
应该把范围查询字段往右放
联合索引的第一个字段必须出现在查询条件中，顺序无所谓，否则无法命中索引。换句话说只要联合索引的第一个字段出现在查询条件中，即可命中索引。
注意事项
- 避免使用select * - 使用count(1)或count(列) 代替 count(*) - 创建表时尽量使用 char 代替 varchar - 表的字段顺序，固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 （select name from user where email=66666）
视图
视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
使用视图我们就可以将一些复杂的查询结果存到了一张临时表中，以后我们再次使用这些数据时，不用进行复杂的查询，直接使用视图就行。
但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，也就意味着扩展sql极为不便，因此并不推荐使用。
创建视图或修改视图。
创建视图需要有 create view的权限，并且对于查询涉及的列有 SELECT 权限。如果使用create or replace或者alter 修改视图，那么还需要该视图的 drop 权限。
语法：create view 视图名称 as SQL语句 create view teacher_view as select tid from teacher where tname='李平老师'; #于是查询李平老师教授的课程名的sql可以改写为 select course_name from course where teacher_id = (select tid from teacher_view);
语法：alter view 视图名称 as SQL语句 alter view course_view as select * from course where cid>3;
注意：MySQL 视图的定义有一些限制，例如，在 FROM 关键字后面不能包含子查询，这和其他数据库是不同的，如果视图是从其他数据库迁移过来的，那么可能需要因此做一些改动，可以将子查询的内容先定义成一个视图，然后对该视图再创建视图就可以实现类似的功能了。
删除视图
语法：drop view 视图名称
drop view teacher_view
查看视图
从 MySQL 5.1 版本开始，使用 show tables 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 show views 命令。
触发器
触发器有点类似于Python中的装饰器，他是为了在对表进行【增、删、改】操作之前或之后执行一些指定的操作。当然我么也可以在触发器被触发时进行判断，然后再决定是否执行指定的操作。
触发器的功能通过Python也可以实现，只不过触发器是MySQL提供给我们的一个功能。但是如果使用了触发器我们就不能在程序级别控制。最好不要使用。
创建触发器
语法： create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_stmt
trigger_name: 触发器的名称。
trigger_time: 触发器的触发时间。可以是before（之前触发）或者after（之后触发）。
trigger_event: 触发器的触发事件。可以是insert、update或者delete。
create trigger auto_add_svip after insert on user for each row # 当我们向user表中插入一条记录后激活名为auto_add_svip触发器。
使用触发器
create table user ( id int auto_increment primary key, name char(16), type enum('VIP', 'SVIP') default 'VIP' ); create table SVIP( id int auto_increment primary key, name char(16) ); delimiter $$ # 重新指定SQL语句的执行标志 create trigger auto_add_svip after insert on user for each row begin if new.type = 'SVIP' then insert into SVIP(name) values(new.name); #必须加分号 end if ; #必须加分号 end $$ delimiter ; # 重新指定SQL语句的执行标志 drop trigger auto_add_svip; insert into user(name,type) values ('jun', 'SVIP'); # 当向user表中插入类型为SVIP数据后，会激活触发器。向SVIP表中插入一条指定的数据。
注意：NEW表示即将插入的数据行，OLD表示即将删除的数据行。
查看触发器s
可以通过执行 show triggers 命令查看触发器的状态、语法等信息，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用起来不是很方便
删除触发器
drop trigger trigger_name
事务
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
MySQL 通过 set autocommit、start transaction、commit 和 rollback语句支持本地事务，具体语法如下。
start transaction | begin [work] commit [work] [and [no] chain] [[no] release] rollback [work] [and [no] chain] [[no] [release]] set autocommit = {0 | 1}
默认情况下，MySQL 是自动提交（Autocommit）的，如果需要通过明确的 Commit 和Rollback 提交和回滚事务，那么需要通过明确的事务控制命令来开始事务
start transaction 或 begin 语句可以开始一项新的事务。
commit 和 rollback 用来提交或者回滚事务。
set autocommit 用于修改当前连接的提交方式。如果设置了set autocommit=0，禁止自动提交。
如果只是对某些语句需要进行事务控制，则使用 start transaction 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改autocommit来控制事务比较方便，这样不用在每个事务开始的时候再执行start transaction语句。
示例：
mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | +----+------+ 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into svip(name) values('wu'); Query OK, 1 row affected (0.00 sec) mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | | 3 | wu | +----+------+ 2 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.07 sec) mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | +----+------+ 1 row in set (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into svip(name) values('wu'); Query OK, 1 row affected (0.00 sec) mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | | 4 | wu | +----+------+ 2 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.07 sec) mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | +----+------+ 1 row in set (0.00 sec)
mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | +----+------+ 1 row in set (0.00 sec) mysql> insert into svip(name) values('wu'); Query OK, 1 row affected (0.07 sec) mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | | 5 | wu | +----+------+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> rollback; # 已经真实的写到了数据表中无法回滚。 Query OK, 0 rows affected (0.00 sec) mysql> select * from svip; +----+------+ | id | name | +----+------+ | 1 | jun | | 5 | wu | +----+------+ 2 rows in set (0.00 sec)
详情参照 http://www.runoob.com/mysql/mysql-transaction.html
存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql。
优点：
- 用于替代程序写的SQL语句，实现程序与SQL解耦
- 基于网络传输，传别名的数据量小，而直接传SQL数据量大
缺点：
- 程序员扩展不方便
创建无参存储过程
delimiter $$ create procedure proc() begin declare i int default 1; while (i<3) do # 查询两次 select name from svip; set i=i+1; end while ; end $$ delimiter ; # 在MySQL中调用存储过程。 call proc() # 在Python中通过pymysql调用。 cur = conn.cursor() cur.callproc('proc') print(cur.fetchall()) # 存储过程中SQL语句的执行结果
创建有参存储过程
对于存储过程，可以接收参数，其参数有三类： #in 仅用于传入参数用 #out 仅用于返回值用 #inout 既可以传入又可以当作返回值
delimiter $$ create procedure proc_args( in n char(16), out x int ) begin select name,type from user where name = n; set x=1; end $$ delimiter ; # 在MySQL中调用存储过程。 set @name='jun'; set @res=0; call proc_args(@name,@res); # 查看返回值。 select @y; # 在Python中通过pymysql调用。 import pymysql conn = pymysql.connect( host='localhost', port=3306, user='root', password='123456', database='index_test', charset='utf8' ) cur = conn.cursor() cur.callproc('proc_args', ('jun', 0)) # 存储过程中SQL语句的执行结果 print(cur.fetchall()) # 存储过程的返回值 # set @_proc_args_0 # set @_proc_args_1 cur.execute('select @_proc_args_1;') print(cur.fetchall()) cur.close() conn.close()
删除存储过程
drop procedure proc;
