mysql2

视图-view

我们使用create命令来构建database,user,table(基本单位),view,索引,函数,存储过程,触发器 - 数据库的单位.

视图实际上就是一张”虚拟表”,实际上是不存在的.只是逻辑上的查询结果的集合.

视图的分类

  1. 简单视图

    视图来自于单表查询的集合 - 允许执行DML操作

  2. 复杂视图

    视图来自于多表关联查询的集合 - 不一定允许执行DML操作.

创建的语法

1
2
3
4
5
DROP view 视图名;

CREATE VIEW 视图名
AS
SELECT查询语句;
1
2
3
create view t_acc_view as select * from t_acc;

select * from t_acc_view;

复杂视图

1
2
3
4
5
6
7
8
9
10
11
12
13
drop view co_view;
create view co_view as
select c.cname,o.ord_no from t_customer c join t_ord o on c.id = o.customer_id;

update co_view set cname='xx'; //允许update

drop view co_view;
create view co_view as
select customer_id,count(*) c_ from t_ord group by customer_id;

-- 不允许执行update操作.
mysql> update co_view set c_=1;
ERROR 1288 (HY000): The target table co_view of the UPDATE is not updatable

测试

  1. 修改原表,视图肯定变化.有可能还会导致视图数据全部丢失.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    drop view t_acc_view;
    create view t_acc_view
    as
    select * from t_acc where balance=16000;

    -- 更新原表

    -- 更新了这个视图来源的那个条件列.
    update t_acc set balance = balance+1000 where id=2;

    mysql> select * from t_acc_view;
    Empty set (0.00 sec)

  2. 修改视图 - 同样也会对原表造成影响.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    drop view t_acc_view;
    create view t_acc_view
    as
    select * from t_acc where id=1;

    -- 更新视图
    update t_acc_view set balance=1000 where id=1;

    mysql> select * from t_acc;
    +------+----------+
    | id   | balance  |
    +------+----------+
    |    1 |  1000.00 |
    |    2 | 17000.00 |
    |    3 | 15000.00 |
    +------+----------+

  3. 针对题1的情况,更新列可能导致视图失效.

    1
    2
    3
    4
    drop view t_acc_view;
    create view t_acc_view
    as
    SELECT带where的条件查询语句 with check option

    with check option作用:不允许更新视图的来源的那个条件列. 肯定是要配合where语句一起使用的,否则没有任何意义

    1
    2
    3
    4
    5
    6
    7
    drop view t_acc_view;
    create view t_acc_view
    as
    select * from t_acc where balance=1000 with check option;

    mysql> update t_acc_view set balance=2000 where id=1;
    ERROR 1369 (HY000): CHECK OPTION failed 'dy.t_acc_view'

视图的好处

  1. 封装SQL语句

    封装比较复杂的SQL语句,在Java中直接查询视图即可.

  2. 定制数据

    系统不同的角色 - 看到的数据应该是不一样的.

  3. 安全性

    数据库应该有多个user账号的,可以给不同的用户授予不同的视图的权限(select,insert,update,delete)

    授权grant

  4. 合并抽离出去的数据 - 对用户屏蔽的底层的数据库的结构的设计.

索引-index

作用:提高查询的效率,类似于书的那个目录.

介绍

  1. Myisam存储引擎

    索引文件和数据文件是分开存储的.是俩个独立的文件

    数据结构:B+树

  2. Innodb存储引擎

    数据和索引是合二为一的,是一个文件.

    B+树

  • 索引也是占用物理空间的.
  • 对表中的数据进行DML操作的时候,维护索引的-消耗一点额外的时间
  • 如果表中某列重复的数据比较多,没有必要创建索引 - 查找的时候接近于全表扫描花费的时间.
  • 当表中没有指定索引列,那么默认的索引列就是主键列.
  • show index from 表名 \G;

索引的分类

  • 主键索引

    1
    2
    3
    4
    Key_name: PRIMARY

    -- 非要删除主键约束
    alter table 表名 drop primary key;

  • 唯一索引

    1
    某列增加unique唯一约束,自动创建唯一索引

  • 全文索引

    后期会使用elasticsearch来进行全文搜索 - 搜索引擎.

  • 复合索引 - 遵守最左匹配原则.

    1
    create index index_name on 表名(列1,列2,列3);

  • 普通索引

    1
    2
    create index index_name on 表名(列);
    drop index index_name;

索引失效场景※

如何查看索引是否生效 - 索引执行计划explain select语句;

type - const>eq_ref>ref>range>index>all)

type-all 全表扫描 - 索引是失效的.

1
2
3
4
5
6
explain select * from index_test where id=1;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | index_test | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
drop table index_test;
create table index_test(
	id int(7) primary key,
  a int(7),
  b int(7),
  c varchar(20),
  d varchar(20)
);
insert into index_test values(1,100,10,'aaa','AAA');
insert into index_test values(2,200,30,'aab','BBB');
insert into index_test values(3,300,20,'caa','CCC');
insert into index_test values(4,100,10,'daa','DDD');
insert into index_test values(5,500,50,'aad','FFF');

drop index index_test_index;
create index index_test_index on index_test(a,b,c);

create index index_test_d on index_test(d(10));//10 - 索引长度

1. 复合索引需要遵守最左匹配原则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- a,b,c都是走了索引的.
explain select * from index_test where a = 2 and b=200 and c='aaa';

type:ref   key_len: 73

-- a走了索引
explain select * from index_test where a = 2;
type: ref  key_len:5

-- a,b都是走了索引
explain select * from index_test where a = 2 and b=200;
type:ref key_len:10

-- b是不走的
explain select * from index_test where b=200;
type:ALL key_len:NULL

-- type:ref   key_len: 73
-- mysql底层发现where条件列a,b,c都是复合索引,先进行优化,a,b,c,进行查询.
explain select * from index_test where b=200 and c='aaa' and a=2;

2. 模糊匹配

1
2
3
4
5
6
7
8
-- %在末尾 - 生效
explain select * from index_test where a = 2 and b=200 and c like 'a%';

-- %在开头,a,b是走索引,但是c没有走索引
explain select * from index_test where a = 2 and b=200 and c like '%a';

-- %?%,a,b是走索引,但是c没有走索引
explain select * from index_test where a = 2 and b=200 and c like '%a%';

3. 查询范围之后

1
2
3
4
5
-- b>=10 条件成立,范围之后的索引是生效的.  a,b,c都是走索引
explain select * from index_test where a = 100 and b>=10 and c='aaa';

-- b>10 条件不成立,范围之后的索引是失效的.  只有a,b是走索引的
explain select * from index_test where a = 100 and b>10 and c='aaa';

4. 索引列参加了计算

1
2
-- type:ALL
explain select * from index_test where id+1=1;

5. 索引列使用了函数

1
2
-- type:ALL
explain select * from index_test where abs(id)=1;

6. 查询的数据超过整张表的30%

7. is null和is not null

1
2
3
4
5
6
7
create index emp_index on s_emp(commission_pct);

-- typ:ALL is null 没有走索引
explain select * from s_emp where commission_pct is null;

-- type:range is not 走索引.
explain select * from s_emp where commission_pct is not null;

8. in和not in

1
2
3
4
5
6
7
8
explain select * from s_emp where id in(1,2,3);
explain select * from s_emp where id not in(1,2,3);

-- mysql5.7之前
in - 走索引
not in - 不走索引

-- 8.x not in允许走索引

关于索引的长度key_len

计算方式

  1. 数值类型 - int

    4 + 1(该列是否为null,没有设置not null,就需要加1,存储null也占1个)

  2. 字符串类型 - varchar(n) - utf8 - n*3+2+1(没有设置not null,就需要加1,存储null也占1个)

索引长度作用

1
2
3
4
5
6
7
8
site: www.baidu.com
   www.baobao.com
   www.baxy.com
   
site索引长度是5,不够,前缀重复比较多.
索引长度要有区分度.

推荐反转之后进行存储.减少索引长度.

索引的创建策略

  • 哪些列创建索引

    • 主键列,唯一性列

    • 重复性比较少,经常被查询但是同时更新不是特别频繁的列.

    • order by + 排序列 - 创建索引.

      1
      2
      order by语句本身性能就很低下的.  根据索引进行排序.
      如果是id列索引,插入的时候就会先排序了.

    • join on 列

      1
      2
      用来连接表的列适合建索引的.本身join越多,性能越低.
      开发中几乎都是单表查询.尽量不要去使用复杂查询.

  • 不适合创建索引

    • null值太多的列
    • 重复值太多的列
    • 更新比较频繁的列

锁

  1. 行锁

    1
    2
    3
    4
    5
    6
    begin;
    update s_emp set first_name='xxx' where id=1;//条件列是索引列 - 行锁 - 降低锁的粒度

    行锁和表锁
    1. 行锁粒度小,涉及到频繁的加锁和释放锁的过程.
    3. 表锁会影响到查询效率
    1
    2
    3
    4
    begin;
    update s_emp set first_name='xxx' where id=1;//如果上面的事务没有commit,此处会阻塞

    如果此处更新的是另外一行,可以直接执行update s_emp set first_name='xxx' where id=2;

  2. 表锁

    1
    2
    非索引列条件更新的时候 - 锁表.
    所有的DML操作默认的申请表锁

  3. 页锁

    介于行锁和表锁之间的.

  4. 共享锁

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    select语句默认申请的共享锁.
    select申请排他锁.

    select语句 for update;

    实现悲观锁

    更新库存stock

    伪代码:

    begin;

    select stock from xxx where id=1 for update;

    //更新stock


    commit;