MySQL进阶操作

视图VIEW

视图是虚拟的表，与数据库里的数据表不一样，视图只有每次调用时都会执行查询。视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

1、创建视图

CREATE VIEW 视图名 AS sql语句;

2、删除视图

DROP VIEW 视图名;

3、更新视图

ALTER VIEW 视图名 AS sql语句;

4、使用视图

SELECT * FROM 视图名;

5、示例

-- 创建视图v1
CREATE VIEW v1 AS SELECT * FROM users WHERE u_id > 10;
-- 修改视图v1的sql语句
ALTER VIEW v1 AS SELECT * FROM users WHERE u_id > 5;
-- 使用视图v1，进行分组排序限制等操作
SELECT age FROM v1 GROUP BY age ORDER BY age DESC LIMIT 2 OFFSET 1;
-- 删除视图v1
DROP VIEW v1;

函数FUNCTION

在mysql中有很多内置函数，也可以自定义函数，自定义函数可以传入参数，也可以返回值，但是函数没办法得到执行语句得到的结果，存储过程可以。

1、创建函数

CREATE FUNCTION f1(
    i1 INT,
    i2 INT
)
RETURNS INT
BEGIN
    DECLARE num INT;
    SET num = i1 + i2;
    RETURN (num);
END;

2、调用函数

SELECT f1(10,nid), username FROM tab1;

SELECT f1(10,20) INTO @1;
SELECT @1；

3、删除函数

DROP FUNCTION 函数名;

存储过程PROCEDURE

存储过程简单来说，就是为以后使用而保存的一条或多条MySQL语句的集合。

简单：通过把处理封装在容易使用的单元中，简化复杂的操作。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

安全：不要求反复建立一系列处理步骤，这保证了数据的完整性。

高性能：使用存储过程比使用单独的SQL语句要快。

1、创建存储过程

DELIMITER //
CREATE PROCEDURE 存储过程名 (参数)
BEGIN
    sql语句1;
    sql语句2;
    ...
END//
DELIMITER ;

-- 使用存储过程
CALL 存储过程名；

2、删除存储过程

DROP PROCEDURE 存储过程名;
DROP PROCEDURE IF EXISTS 存储过程名;  -- 如果存在则删除　

3、带参数的存储过程。

参数允许的数据类型与表中使用的数据类型相同；查询集不是允许的类型，因此不能通过一个参数返回多个行和列。

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

CREATE PROCEDURE p1 (
    IN v1 INT,
    OUT v2 INT,
    INOUT v3 INT
)
BEGIN
    DECLARE i INT;
    DECLARE j INT DEFAULT 5;
    SET i = 2;
    SET v1 = i + v3;
    SET v2 = v1 + j;
    SELECT age INTO v3 from users WHERE u_id = 10;  -- 将查询结果赋给v3
END;

-- 执行存储过程
SET @p1_v1 = 1;
SET @p1_v2 = 2;
SET @p1_v3 = 3;
CALL p1 (@p1_v1,@p1_v2,@p1_v3);
SELECT @p1_v1,@p1_v2,@p1_v3;  -- p1_v1=1, p1_v2=10, p1_v3=28

4、条件判断

当in参数为1时，out返回'first'，当in参数为2时，out返回'second'，当in参数为其他时，out返回'ahhh'。

CREATE PROCEDURE p4(
    IN v1 INT,
    OUT v2 VARCHAR(20)
)
BEGIN
    IF v1 = 1 THEN
        SET v2 = 'first';
    ELSEIF v1 = 2 THEN
        SET v2 = 'second';
    ELSE
        SET v2 = 'ahhh';
    END IF;
END;

SET @p4_v1 = 2;
CALL p4 (@p4_v1,@p4_v2);
SELECT @p4_v1,@p4_v2;

5、循环语句

in给一个值v1，然后求1-v1的和

CREATE PROCEDURE p5(
    in v1 INT,
    out v2 INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sums INT DEFAULT 0;
    WHILE i <= v1 DO
        SET sums = sums + i;
        SET i = i +1;
    END WHILE;
    SET v2 = sums;
END;

SET @v1 = 10;
CALL p5 (@v1, @v2);
SELECT @v1, @v2;  -- 10 55

触发器TRIGGER

触发器作用：当操作某张表时，同时自动执行一些语句。只有DELETE, INSERT, UPDATE支持触发器。

1、创建触发器

CREATE TRIGGER用来创建触发器，AFTER INSERT定义了此触发器将在INSERT语句成功执行后执行，同时插入多行时FOR EACH ROW设置对每个插入行执行。

CREATE TRIGGER 触发器名 AFTER INSERT ON 表名 FOR EACH ROW
BEGIN
    sql语句...
END;

2、删除触发器

DROP TRIGGER 触发器名;

3、使用触发器

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，通过它访问被插入的行；在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）。

-- 插入后，更新u_cls列为插入数据的c_id
CREATE TRIGGER t1 AFTER INSERT ON class FOR EACH ROW
BEGIN
    UPDATE users SET u_cls = NEW.c_id WHERE users.u_id = 7;
END;
-- 测试触发器
INSERT INTO class(c_name,c_num) VALUES('java',20021);

-- 插入前，修改插入数据中的c_num为2009，更新u_cls列为插入数据的c_id
CREATE TRIGGER t1 BEFORE INSERT ON class FOR EACH ROW
BEGIN
    SET NEW.c_num = 20099;  -- 修改NEW中的值
    UPDATE users SET u_cls= NEW.c_id WHERE users.u_id = 7;
END;
-- 测试触发器
INSERT INTO class(c_name,c_num) VALUES('js',20021);  

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，通过它访问被删除的行；OLD中的值全都是只读的，不能更新。

-- 对class表进行DELETE前，更新u_cls为删除数据中的c_id。
CREATE TRIGGER t1 BEFORE DELETE ON class FOR EACH ROW
BEGIN
    UPDATE users SET u_cls = OlD.c_id WHERE users.u_id = 10;
END;
-- 测试触发器
DELETE FROM class WHERE c_id = 11;　

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；OLD中的值全都是只读的，不能更新。

-- 对class表进行UPDATE前，将更新数据中的c_name转为小写，更新u_cls为更新前的c_num
CREATE TRIGGER t1 BEFORE UPDATE ON class FOR EACH ROW
BEGIN
    SET NEW.c_name = LOWER(NEW.c_name);
    UPDATE users SET u_cls = OLD.c_num WHERE u_id = 11;
END;
-- 测试触发器
UPDATE class SET c_num = 20071 WHERE c_id = 13;

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。InnoDB引擎支持事务管理。

CREATE PROCEDURE p1 (OUT p_return_code TINYINT)
BEGIN

    -- 报错则执行
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_return_code = 1;
        ROLLBACK;
    END;

    -- 警告则执行
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
    SET p_return_code = 2;
    ROLLBACK;
    END;

    -- 监控的sql语句
    START TRANSACTION;
        DELETE FROM tb1;
        INSERT INTO tb2 (NAME) VALUES ('seven');
    COMMIT;

    -- 成功则执行
    SET p_return_code = 0;

END;

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

普通索引，基本的索引，它没有任何限制，只是为了加快查找速度。

唯一索引，增加了唯一约束，就是索引列的值必须唯一，但允许有空值NULL。

主键索引，表中定义一个主键将自动创建主键索引，主键索引要求主键列的每个值是唯一的，并且数据不能为NULL。一个表只能创建一个主键索引。

组合索引，将多列组合建立一个索引，最左匹配。

创建表时指定索引

CREATE TABLE fruits(
    f_id INT NOT NULL auto_increment,
    f_name CHAR (30) NOT NULL,
    f_price FLOAT NOT NULL,
    PRIMARY KEY (f_id),  --创建主键
    INDEX index_name (f_name)  --创建普通索引
    UNIQUE unique_name (f_name)  --创建唯一索引，f_name列不能重复
) ENGINE=INNODB DEFAULT CHARSET=utf8;

创建索引

--创建普通索引
CREATE INDEX 索引名 ON 表名 (列名);

--创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

--创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

--创建组合索引
CREATE INDEX 索引名 ON 表名(列名1，列名2...);

删除索引

-- 删除普通索引
DROP INDEX 索引名 ON 表名;

-- 删除唯一索引
DROP INDEX 索引名 ON 表名;

-- 删除主键索引
ALTER TABLE 表名 MODIFY 字段名 INT;  -- 先取消AUTO_INCREMENT
ALTER TABLE 表名 DROP PRIMARY KEY;  -- 删除主键

-- 删除组合索引
DROP INDEX 索引名 ON 表名;

查看索引

SHOW INDEX FROM 表名;

 如何正确使用索引

1、不会命中索引的情况

1、like '%xx'
    通配符%_开头的匹配不会走索引
    SELECT age FROM tab1 WHERE username LIKE '%om';
    通配符不在开头的匹配会走索引
    SELECT age FROM tab1 WHERE username LIKE 'To%';
    
2、使用函数
    不要在列名一侧使用函数。
    SELECT age FROM tab1 WHERE LOWER(username) = 'Tom';

3、or
    尽量使用in代替or
    SELECT * FROM tab1 WHERE nid = 2 OR username = 'Tom';

4、类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来。
    SELECT * FROM tab1 WHERE username = 8848;

5、 !=
    SELECT * FROM tab1 WHERE username != 'Tom';
    如果条件判断是主键，还是会走索引
    SELECT * FROM tab1 WHERE nid != '2';

6、 >  情况不固定啊，搞不懂
    字符类型
    SELECT * FROM tab1 WHERE username > 'alex';  --不走索引
    SELECT * FROM tab1 WHERE username > 'tom';  --走索引
    int类型
    SELECT * FROM tab1 WHERE age > 2;  --不走索引
    如果是主键，还是会走索引
    SELECT * FROM tab1 WHERE nid > 2;  --走索引

7、order by
    如果排序age为索引并且只select age列，才会走索引
    SELECT age FROM tab1 ORDER BY age;
    如果对主键排序，则还是走索引：
    SELECT * FROM tab1 ORDER BY nid DESC;
 
8、 组合索引最左前缀
    如果组合索引为：(name,age)
    name and age  -- 使用索引
    name  -- 使用索引
    age  -- 不使用索引

2、注意事项

• 避免使用select*，获取所有列应该selcet所有列名。
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char代替 varchar，char是固定长度。
• 表的字段顺序固定长度的字段优先。
• 组合索引代替多个单列索引（经常使用多个条件查询时），组合索引比多个单列索引查询快，节省资源。
• 尽量使用短索引，BLOB和TEXT类型，必须指定 length。
• 使用连接（JOIN）来代替子查询(Sub-Queries)。
• 连表时注意条件类型需一致。
• 索引散列值（重复少）不适合建索引，例：性别不适合。

3、执行计划

EXPLAIN+ 查询SQL， 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

EXPLAIN SELECT * FROM tab1 WHERE username = 'tom';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tab1  | NULL       | ref  | ix_tb         | ix_tb | 60      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
id，查询顺序标识，如果使用union连接气值可能为null
select_type，查询类型
table，正在访问的表名
type，查询时的访问方式
possible_keys，可能使用的索引
key，真实使用的
key_len，MySQL中使用索引字节长度
rows，mysql估计为了找到所需的行而要读取的行数 -- 只是预估值
extra，该列包含MySQL解决查询的详细信息

SIMPLE    简单查询
PRIMARY    最外层查询
SUBQUERY    映射为子查询
DERIVED    子查询
UNION    联合
UNION RESULT    使用联合的结果

性能：从上到下越来越快
ALL，全表扫描，对于数据表从头到尾找一遍
    select * from tb1;
    如果有limit限制，则找到之后就不在继续向下扫描

INDEX，全索引扫描，对索引从头到尾找一遍
    select nid from tb1;

RANGE，对索引列进行范围查找
    select *  from tb1 where name < 'alex';
    PS:
    between and
    in
    >   >=  <   <=  操作
    注意：!= 和 > 符号

INDEX_MERGE，合并索引，使用多个单列索引搜索
    select *  from tb1 where name = 'alex' or nid in (11,22,33);

REF，根据索引查找一个或多个值
    select *  from tb1 where name = 'seven';

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;

“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列中索引的位图，并且是冗余的。

4、limit分页

 利用limit实现分页时，limit对是数据全盘扫描，在取到相应数据后停止扫描；如果数据量比较大，会有几十页、几百页，那么大页数的SQL查询性能比较慢。

-- 如果每页10条数据
SELECT * FROM tab1 LIMIT 10 OFFSET 0;  --第一页
SELECT * FROM tab1 LIMIT 10 OFFSET 10;  --第二页
SELECT * FROM tab1 LIMIT 10 OFFSET 20;  --第三页
...
SELECT * FROM tab1 LIMIT 10 OFFSET 10(n-1);  --第n页，性能会下降

可以利用上一页的最后一个值的主键，对其进行>匹配再筛选出10条；对主键进行>匹配会走索引，效率就会更高。

-- 假设主键没有删除，都是连续的
SELECT * FROM tab1 WHERE nid > 0 LIMIT 10;  --第一页
SELECT * FROM tab1 WHERE nid > 10 LIMIT 10;  --第二页
SELECT * FROM tab1 WHERE nid > 20 LIMIT 10;  --第三页
...
SELECT * FROM tab1 WHERE nid > last_nid LIMIT 10;  --第n页