Mysql学习之进阶
本篇主要记录一些视图、触发器、存储过程、函数、索引等mysql知识。
一、视图
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。视图只是一个临时表、只能执行查询、执行sql调用视图时才获取数据
1、创建视图
创建视图sql：CREATE VIEW 视图名称 AS SQL语句 例子： CREATE VIEW v1 AS SELECT * FROM user_info WHERE nid > 12 临时表中数据 +-----+----------+--------+--------------+ | nid | username | passwd | user_type_id | +-----+----------+--------+--------------+ | 14 | 李四 | 123 | 1 | | 15 | 李五 | 123 | 1 | | 16 | 李三 | 123 | 1 | | 17 | 李六 | 123 | 1 | +-----+----------+--------+--------------+ 通过show tables;可以看到v1已经创建 +-------------------------+ | Tables_in_mytest | +-------------------------+ | class | | course | | permission | | score | | student | | teacher | | user_info | | user_type | | user_type_to_permission | | v1 | +-------------------------+ 再查询数据时可以通过视图 SELECT * FROM v1 WHERE nid=14 +-----+----------+--------+--------------+ | nid | username | passwd | user_type_id | +-----+----------+--------+--------------+ | 14 | 李四 | 123 | 1 | +-----+----------+--------+--------------+ row in set (0.00 sec)
2、删除视图
语法格式：DROP VIEW 视图名称
DROP VIEW v1
3、使用视图
使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。
mysql> select * from v1; +-----+----------+--------+--------------+ | nid | username | passwd | user_type_id | +-----+----------+--------+--------------+ | 14 | 李四 | 123 | 1 | | 15 | 李五 | 123 | 1 | | 16 | 李三 | 123 | 1 | | 17 | 李六 | 123 | 1 | +-----+----------+--------+--------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM v1 WHERE nid=14; +-----+----------+--------+--------------+ | nid | username | passwd | user_type_id | +-----+----------+--------+--------------+ | 14 | 李四 | 123 | 1 | +-----+----------+--------+--------------+ 1 row in set (0.00 sec)
二、触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
创建触发器
# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
示例之插入前触发器
delimiter // CREATE TRIGGER tri_before_insert_user_info BEFORE INSERT ON user_info FOR EACH ROW BEGIN INSERT INTO class (caption) VALUES ('一年一班'); END// delimiter ;
需要注意的几点： 1、NEW表示即将插入的数据行，即如果向表中插入name=‘张三’，此时NEW.name='张三' 2、OLD表示即将删除的数据行，即如果删除表中name=‘张三’，此时OLD.name='张三' 3、delimiter使用说明 delimiter，作用是设置命令段的结束符号，是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了，即遇到这个所设置的结束符号后，按回车，则命令段就可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。但是在存储过程、触发器中，过程体内可能会包含分号（;），因此需要将命令结束符号替换成其他的字符，如$$、//等，存储过程、触发器创建完成后，可以将命令段的结束符号重新设为分号。
删除触发器
DROP TRIGGER tri_before_insert_user_info;
使用触发器
触发器无法由用户直接调用，是当对表的【增/删/改】操作被动引发的
三、存储过程
存储过程定义了一系列有逻辑关系的sql语句，可以包含循环、判断等，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
存储过程有缺点
优点： 1、用于替代程序写的SQL语句，实现程序与sql解耦 2、基于网络传输，传别名的数据量小，而直接传sql数据量大 缺点： 程序员扩展功能不方便
创建存储过程
delimiter // create procedure p1() BEGIN select * from user_info; END// delimiter ; 执行存储过程 call p1(); mysql> call p1(); +-----+----------+--------+--------------+ | nid | username | passwd | user_type_id | +-----+----------+--------+--------------+ | 1 | root | abc | 1 | | 2 | yqh | 123 | 2 | | 8 | yang | abc | 1 | | 10 | bb | 123 | 2 | | 11 | bbbb | 123 | 2 | | 14 | 李四 | 123 | 1 | | 15 | 李五 | 123 | 1 | | 16 | 李三 | 123 | 1 | | 17 | 李六 | 123 | 1 | | 18 | 1111 | 222 | 1 | | 19 | 222 | 3333 | 1 | +-----+----------+--------+--------------+ 11 rows in set (0.00 sec)
对于存储过程，可以接收参数，其参数有三类：
- in 仅用于传入参数用
- out 仅用于返回值用
- inout 既可以传入又可以当作返回值
有参数存储过程
-- 创建存储过程 delimiter \\ create procedure p1( in i1 int, in i2 int, inout i3 int, out r1 int ) BEGIN DECLARE temp1 int; DECLARE temp2 int default 0; set temp1 = 1; set r1 = i1 + i2 + temp1 + temp2; set i3 = i3 + 100; end\\ delimiter ; -- 执行存储过程 set @t1 =4; set @t2 = 0; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2; 有参数的存储过程
结果集
delimiter // CREATE PROCEDURE p1 () BEGIN SELECT * FROM v1 ; END// delimiter ; 如果有多个select语句，仅返回最后一个查询结果
结果集+out
delimiter // CREATE PROCEDURE p2 ( IN n1 INT, INOUT n3 INT, OUT n2 INT, ) BEGIN DECLARE temp1 INT ; DECLARE temp2 INT DEFAULT 0 ; SELECT * FROM v1 ; SET n2 = n1 + 100 ; SET n3 = n3 + n1 + 100 ; END// delimiter ;
执行存储过程
-- 无参数 call proc_name() -- 有参数，全in call proc_name(1,2) -- 有参数，有in，out，inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2)
python执行存储过程
# 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 获取执行完存储的参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall()
删除存储过程
drop procedure proc_name;
四、索引
索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息，索引有助于更快的获取信息
1、常见索引种类
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有null）
唯一索引是不允许其中任何两行具有相同索引值的索引，当现有数据存在大量的重复的键值的时候，大多数数据库不允许唯一索引与表一起保存，数据库还可能防止添加将表中创建重复键值的新数据
- 主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
数据库表经常有一列或者多列组合，其值唯一标识表中的每一行，每一列称为表的主键，在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型，该索引要求主键索引的每个值都唯一
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
2、索引的使用
1、普通索引 普通索引仅有一个功能：加速查询
创建索引：1、创建表时创建索引2、先创建表后创建索引
创建表时创建索引 CREATE TABLE tb1 ( nid INT NOT NULL auto_increment PRIMARY KEY, NAME VARCHAR (32) NOT NULL, email VARCHAR (64) NOT NULL, extra text, INDEX ix_name (NAME) )
先创建表后创建索引
语法：create index index_name on table_name(column_name)
示例
create index in_name on tb1(name)
删除索引
语法：drop index index_name on table_name;
示例
drop index ix_name on tb1;
查看索引
语法：show index from table_name; 示例 mysql> show index from tb1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb1 | 0 | PRIMARY | 1 | nid | A | 0 | NULL | NULL | | BTREE | | | | tb1 | 1 | ix_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------
2、唯一索引 唯一索引有两个功能：加速查询 和 唯一约束（可含null）
创建索引
CREATE TABLE tb1 ( nid INT NOT NULL auto_increment PRIMARY KEY, NAME VARCHAR (32) NOT NULL, email VARCHAR (64) NOT NULL, extra text, UNIQUE ix_name (NAME) )
语法：create unique index 索引名 on 表名(列名)
示例
create unique index ix_name on tb1(name)
删除索引
语法：drop unique index 索引名 on 表名
示例
drop unique index ix_name on tb1
查看索引
mysql> show index from tb1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb1 | 0 | PRIMARY | 1 | nid | A | 0 | NULL | NULL | | BTREE | | | | tb1 | 0 | ix_name | 1 | NAME | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.01 sec)
3、主键索引 主键有两个功能：加速查询 和 唯一约束（不可含null）
创建索引
create table tb1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table tb1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name)
语法：alter table 表名 add primary key(列名);
示例
alter table tb1 add primary key(nid);
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
4、组合索引
创建索引 组合索引是将n个列组合成一个索引 其应用场景为：频繁的同时使用n列来进行查询，如：where name = 'root' and passwd = 123
创建索引
先创建表 CREATE TABLE tb1( nid INT NOT NULL auto_increment PRIMARY KEY, NAME VARCHAR (32) NOT NULL, email VARCHAR (64) NOT NULL, extra text ) 创建索引 create index ix_name_email on tb3(name,email);
查看索引
mysql> show index from tb1; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb1 | 0 | PRIMARY | 1 | nid | A | 0 | NULL | NULL | | BTREE | | | | tb1 | 1 | ix_name_email | 1 | NAME | A | 0 | NULL | NULL | | BTREE | | | | tb1 | 1 | ix_name_email | 2 | email | A | 0 | NULL | NULL | | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
注意：
如上创建组合索引之后， 查询时： name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。
5、覆盖索引
概念
解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
判断标准
使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询
mysql> EXPLAIN SELECT nid FROM tb1\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb1 type: index possible_keys: NULL key: ix_name_email key_len: 100 ref: NULL rows: 1 Extra: Using index 1 row in set (0.00 sec)
五、正确使用索引
建立索引是为了加快查询速度，但必须要正确的使用索引来查询，否则即使建立索引，索引也不会生效
下面的大多数情况不会走索引
- like ‘%xxx’ --不走索引 ‘xxx%’ --走索引 select * from tb1 where name like '%cn'; --不走索引 select * from tb1 where name like 'cn%'; --走索引 - 使用函数或者索引列参与了计算 select name from tb1 where age+10=30 ; --不走索引 select * from tb1 where reverse(name) = 'root'; --不走索引 - or select * from tb1 where nid = 1 or email = 'root@.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'root'; select * from tb1 where nid = 1 or email = 'root@.com' and name = 'aa' - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where name = 999; - != select * from tb1 where name != 'root' 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123 - > select * from tb1 where name > 'alex' 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 - order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引
注意事项
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
六、执行计划
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
注意：explain 只是相对比较准确表达出当前SQL运行状况，并不完全准确
mysql> EXPLAIN SELECT * FROM tb1\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: NULL 1 row in set (0.00 sec)
相关字段解释
id 查询顺序标识 id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。 select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 table 正在访问的表名 显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。 type 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'root@.com' select * from tb1 where email = 'root@.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'a' or nid in (11,22); REF 根据索引查找一个或多个值 select * from tb1 where name = 'rot'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A; possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
七、limit分页
无论是否有索引，limit分页可以一定程度上提高查询速度
select nid from tb1 where nid < 970 limit 10
八、慢日志查询
配置mysql自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /data/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录 配置好后需要重启zabbix才生效 如果要立马生效可以使用 set global 变量名 = 值
