MySQL数据库进阶

一、视图（不重要）

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用

1.1 为什么需要视图

• 简化查询
• 可以进行权限控制，我们可以把表的权限进行封闭，但是开放相应的视图权限，视图里只开放部分数据

1.2 创建视图

--格式：CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, name from A where nid > 4

1.3 使用视图

使用视图时，将其当作表进行操作即可,视图和真实的表都会相互影响

select * from v1

1.4 删除视图

--格式：DROP VIEW 视图名称
DROP VIEW v1

1.5 修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
SELET A.nid, B. NAME FROM A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

二、函数（不重要）

2.1 常见内置函数

CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。
    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。
    LOWER(str)
        变小写
    UPPER(str)
        变大写
    LTRIM(str)
        返回字符串 str ，其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ，结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列
    LOCATE(substr,str,pos)
        获取子序列索引位置
    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL，则返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ，顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始，返回从后边开始len个字符组成的子序列

2.2 自定义函数

delimiter \
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END \
delimiter ;

2.3 删除函数

drop function func_name;

2.4 执行函数

# 获取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;
# 在查询中使用
select f1(11,nid) ,name from tb2;

三、触发器（不重要）

3.1 触发器的定义

进行数据库应用软件的开发时，我们有时会碰到表中的某些数据改变，希望同时引起其他相关数据改变的需求
利用触发器就能满足这样的需求,它能在表中的某些特定数据变化时自动完成相关的操作

3.2 触发器的应用场合

• 当向一张表中添加或删除记录时，需要在相关表中进行同步操作

　　　　比如，当一个订单产生时,订单所购的商品的库存量相应减少

• 当表上某列数据的值与其他表中的数据有联系时

　　　　比如，当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该         客户的累计欠款是否超出了最大限度

• 当需要对某张表进行跟踪时

　　　　 比如，当有新订单产生时，需要及时通知相关人员进行处理，此时可以在         订单表上设计添加触发器加以实现

3.3. 触发器的创建语法

# 插入前
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

3.4 触发器案例

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END//
delimiter ;

注意：NEW表示即将插入的数据行，OLD表示即将删除的数据行

 3.5 删除触发器

DROP TRIGGER tri_after_insert_tb1;

3.6 使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的

insert into tb1(num) values(666)

四、事务

通俗的说，事务指一组操作，要么都执行成功，要么都执行失败

4.1 事务的ACID特性

• 原子性(Atomicity)，原子意为最小的粒子，即不能再分的事务，要么全部执行，要么全部取消（就像上面的银行例子）
• 一致性(Consistency)：指事务发生前和发生后，数据的总额依然匹配
• 隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的
• 持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤消，只能通过“补偿性事务”来抵消之前的错误

4.2 事务的用法

开启事务(start transaction)
执行sql操作(普通sql操作)
提交/回滚(commit/rollback)

create table user(
id int auto_increment primary key,
name varchar(32) not null default '',
money int not null default 1000
)engine=innodb charset=utf8;

 insert into user (name,money) values ('wangyong',1000),('liguo',1000);

# 正常操作
 start transaction;
update user set money=1100 where name='wangyong';
updata user set money=900 where name='liguo';
# 出现异常
 rollback;

# 最终结果， 数据未发生变化
mysql> select * from user;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | wangyong |  1000 |
|  2 | liguo    |  1000 |
+----+----------+-------+

注意：建表的时候, 选择 innodb 引擎

4.3 事务常见的执行流程

购物流程中的订单付款操作含有以下3步子操作：

• 从用户的余额中扣除订单价格
• 在用户的账户变动记录里记下流水
• 修改订单状态为已付款

这三步需保证原子性,请用事务来控制此过程

 五、索引

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

5.1 索引的时间复杂度

设有N条记录，如果不用索引，那平均查找N/2次，但是用了索引之后呢？

一般情况下，是以2为底，N的对数

这块就涉及到二叉树的查找了

5.2 索引的好处与坏处

• 好处：加快了查询的速度
• 坏处：降低了增删改的速度，增大了表的文件大小(索引文件甚至比数据文件都还要大)

问题：大数据量进行导入别的数据表的时候，怎么办？？

5.3 索引的概念

类似于字典中的目录，如何快速找到某个字?

可以给字典加目录

对数据库来说,索引的作用即是给 “数据” 加目录

所以所谓的索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构

5.4 常见的索引类型

• 主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）
• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一（可以有null）
• 组合索引：多列值组成一个索引，

5.4.1 主键索引（primary key）

1. 创建表 + 索引

# 第一种:
    create table test(
        id int auto_increment primary key,
        name varchar(32) not null default ''
    )engine=innodb charset=utf8;

# 第二种：
    create table test(
        id int not null default 0,
        name varchar(32) not null default ''
    )engine=innodb charset=utf8;
    
    alter table test change id id int auto_increment primary key

5.4.2 普通索引（index）

普通索引仅有一个功能：加速查询

1. 创建表 + 索引

# 第一种
    create table test(
        id int auto_increment primary key,
        name varchar(32) not null default '',
        index ix_name (name)
    )engine=innodb charset=utf8;

# 第二种
     create table test(
        id int auto_increment primary key,
        name varchar(32) not null default ''
    )engine=innodb charset=utf8;
    
    create index ix_name on test (name);

2. 创建索引

create index index_name on table_name(column_name)

3. 删除索引

drop index_name on table_name;

4. 查看索引

show index from table_name; 

 5.4.3 唯一索引（unique）

唯一索引有两个功能：加速查询 和 唯一约束（可含null）

1. 创建表 + 索引

# 第一种
    create table test(
        id int auto_increment primary key,
        name varchar(32) not null default '',
        unique ix_name (name)
    )engine=innodb charset=utf8;

# 第二种  create unique index 索引名称 on 表名(name);
    create table test(
        id int auto_increment primary key,
        name varchar(32) not null default ''
    )engine=innodb charset=utf8;
    
    create unique index ix_name on test (name);

2. 创建索引

create unique index 索引名 on 表名(列名)

3. 删除索引

drop unique index 索引名 on 表名

5.4.4 联合索引 （unique index）

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666

1. 创建表 + 索引

# 创建联合唯一索引  create unique index 索引名称 on 表名 (name,age);
    create table test(
        id int auto_increment primary key,
        age int not null default 0, 
        name varchar(32) not null default ''
    )engine=innodb charset=utf8;
    
    create unique index ix_name_age on test (age, name);

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

2. 创建索引

create index ix_name_email on in3(name,email);

如上创建组合索引之后，查询：

• name and email – 使用索引
• name – 使用索引
• email – 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并

5.5 使用规则

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'xxx';
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- 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)
- 索引散列值（重复少）不适合建索引，例：性别不适合

5.6 查看是否使用索引：explain

使用方式：explain selext * from test;

explain selext * from test;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------
|  1 | SIMPLE      | test  | index | NULL          | ix_name | 98      | NULL |    1 | Using index 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------

参数分析：

id
        查询顺序标识
    select_type
        查询类型
            SIMPLE          简单查询
            PRIMARY         最外层查询
            SUBQUERY        映射为子查询
            DERIVED         子查询
            UNION           联合
            UNION RESULT    使用联合的结果
            ...
    table
        正在访问的表名
    type
        查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
            ALL             全表扫描，对于数据表从头到尾找一遍
                            select * from tb1;
                            特别的：如果有limit限制，则找到之后就不在继续向下扫描
                                   select * from tb1 where email = 'xxxx@live.com'
                                   select * from tb1 where email = 'xxxx@live.com' limit 1;
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
            INDEX           全索引扫描，对索引从头到尾找一遍
                            select nid from tb1;
            RANGE          对索引列进行范围查找
                            select *  from tb1 where name < 'xxx';
                            PS:
                                between and
                                in
                                >   >=  <   <=  操作
                                注意：!= 和 > 符号
            REF             根据索引查找一个或多个值
                            select *  from tb1 where name = 'xxx';
            CONST           常量
                            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                            select nid from tb1 where nid = 2 ;
            SYSTEM          系统
                            表仅有一行(=系统表)。这是const联接类型的一个特例。
    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将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。

删除索引后效果

# drop 索引名称 on 表名
drop index ix_name_age on test;

mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

5.7 慢日志

日志文件: 记录了执行速度特别慢的SQL语句

配置MySQL自动记录慢日志

slow_query_log = OFF                            是否开启慢日志记录
long_query_time = 2                              时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log        日志文件

5.7.1 开启方式

1. show variables like '%query%';
2. set global long_query_time = 1; 设置慢查询的时间
3. set global  slow_query_log = ON
4. set global slow_query_log_file = E:programmysql-5.6.44-winx64dataoldboy-slow.log

5.7.2 普通日志记录（general log）

SQL审计 (记录sql的操作语句)
show variables like '%general%';

5.8 索引原理

https://www.cnblogs.com/wangyong123/articles/11621128.html