索引

view

  • create view

    1
    2
    3
    4
    5
    6
    7
    8
    9
    drop view student_view;
    create view student_view as select *from t_student where id%2=0;

    -- insert into t_student
    insert into t_student values(5,'java',20),(6,'javab',25),(7,'sad',20),(8,'jksd',56);

    -- update student_view
    update student_view set age=100 where id=8;

  • create complex view

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    -- 有关系的两张表（fk）
    drop view student_view2;
    create view student_view2 as select s.*,sc.c_id from t_student s left join t_sc sc  on  s.id=sc.s_id;

    -- update view
    update student_view2 set name='as' ;

    ERROR 1288 (HY000): The target table student_view2 of the UPDATE is not updatable


    -- 无关系的两张表
    drop table tt;
    drop table ta;
    create table tt(
     id int(7) primary key,
    tname varchar(20)
    );

    create table ta(
     taid int(7) primary key,
    tt_id int(7)
    );

    insert into tt values(1,'aa');
    insert into tt values(2,'aa');
    insert into tt values(3,'aa');
    insert into tt values(4,'aa');

    insert into ta values(1,1);
    insert into ta values(2,2);
    insert into ta values(3,3);
    insert into ta values(4,4);
    drop view t_view;
    create view t_view as select * from tt t join ta a on a.tt_id=t.id;
    update t_view set tname='as';

    create view t_view2 as select count(*)from tt t join ta a on a.tt_id=t.id;
    update t_view set count=10;

    视图作用：1.封装sql  2.屏蔽底层数据库结构  3.定制数据   4.安全性（grant）

    索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    -- function:提高查询效率
    底层：b+数  myisam（数据，索引分别存储）,innodb(数据，索引合二为一)
    -- 主键列默认索引列
    show index from 表名

    -- 索引分类
    1.主键索引（primary key）
    2.unique
    3.全文索引（elasticsearch）
    4.复合索引
    create index index_name on tablename(column 1,column 2)
    5.普通索引
    create index index_name on tablename(colunmn)

    drop table index_test;
    create table index_test(
    id int(7) primary key auto_increment,
    a int(7),
    b int(7),
    c varchar(20),
    d varchar(20)
    );
    insert into index_test values(1,2,3,'20w','cc'),(null,22,93,'2s0','dd'),(null,22,33,'w20','ss'),(null,22,53,'201','hh'),(null,72,43,'200','gg'),(null,2,3,'20i','ff');

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

    -- jude index valid
    explain +select....

    explain select * from index_test where a=2 and b=3 and c='20w';(ref) len=93  
    +----+-------------+------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
    | id | select_type | table      | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
    +----+-------------+------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | index_test | NULL       | ref  | t_index       | t_index | 93      | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
    -- 索引失效*
    1.复合索引最左匹配原则
    explain select *from index_test where b=3 and c='20w';(All len=0)
    explain select *from index_test where a=3 and b=2;(ref len=10)
    explain select *from index_test where c='sd' and a=10 and b=2;(ref len=93),调优
    2.模糊匹配
    -- %在末尾生效
    explain select * from index_test where a=1 and b=2 and c like 's%';(ref len=93)
    %在前部分生效同（%？%）
    explain select * from index_test where a=1 and b=2 and c like '%s';(ref len=10)-- a,b走索引
    3.查询范围
    explain select * from index_test where a=1 and b>2 and c='21';(ref len=10)
    4.索引计算
    explain select* from index_test where id+1=1;(all)
    5.索引使用函数
    explain select* from index_test where abs(id)=1;(all)
    6.c查询数据超过百分之30（数据量足够大）
    7.is null and is not null
    (is null 不走索引，is not null 走索引)
    8.in and not in
    before 5.7 in(走索引)  not in(不走)
    after 8.0 not in ( allow index)

    -- 索引的创建策略
    -- fit create index
    1.primary key or unique
    2.重复性少，经常被查询，但是更新少
    3.order by 列
    4.join on 列
    -- unfit create index
    1.null值列
    2.重复列
    3.更新频繁列

锁

1
2
3
4
5
6
7
8
9
1.行锁（两线程不能同时修改某一行的值）
update XX set xx=xx where id=1;//必须是索引列，降低锁的粒度
2.表锁（非索引列为条件更新 会触发表锁）
所有的DML操作都会阻塞
3.页锁（介于行锁与表锁之间）
一页：由若干行组成的数据（B+树）
4.共享锁（select,类似Java的读锁）
-- select语句申请排他锁
select stock from xxx where id=1 for update;（悲观锁：干啥都悲观，总担心有人改数据，索性给全锁了）

索引底层原理

1
2
3
4
5
6
7
8
9
10
11
12
13
myisam(mai se meng),innoDB----->B+树


二叉查找树->二叉搜索树->B+树

二叉查找树（失衡）-->（产生）二叉平衡树（数据节点过多）->B树（储存一页数据，key和value都存）->b+树

B+树
1.非页节点存储key
2.数据存叶子，且相对有序
3.页与页（树节点）之间是双向链表
4.叶子内部数据是单向链表

B+树

– 聚簇索引

B+

聚簇索引与非聚簇索引

1
2
3
4
5
6
innoDB为引擎的表，表中的数据都会有主键，即使不主动声明，系统会创建一个隐式的主键.B+树的键值就是主键.这种**以主键为B+树索引而构成的B+树***索引，称为聚簇索引

非聚簇索引：以其他列值构建B+树的索引

-- 非聚集索引的叶子节点并不存储数据，而是存储对应的主键，因此还需要通过索引查数据，称之为回表
（注，若所查数据为主键列，或者该列，则不需要回表）

jusearch

1
select * from xx where id>=18 and id<41;

非聚簇索引查找图

x

myisam查找图

z

1
2
3
4
5
6
7
8
9
10
11
12
key - 索引列值,value - 数据行的物理地址.
innodb中索引和数据合并到一个文件中

myisam中索引和数据是单独的俩个文件,分别是索引文件和数据文件.

myisam中采用的是"非聚集的方式"

无论是聚簇索引还是非聚簇索引,查找方式是一样.

采用的也是B+树的结构**.只是叶节点中存储的是索引的列值以及该对应的行记录的地址.**

需要再根据行记录地址到表中进行定位[回表]

  • 函数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    delimiter $$
    create function dy_add(a int ,b int) returns int
    	begin
    		return a+b;
    	end $$
    delimiter ;

    select dy_add(5,2);


    delimiter $$
    create function dy_add(a int ,b int) returns int
    	begin
    		return a+b;
    	end $$
    delimiter ;

    select dy_add(5,2);


    -- 触发器
    drop trigger customer_trriger;
    delimiter //
    create trigger  customer_trriger 
         before delete on s_customer 
    for each row
    begin
    delete from s_ord where customer_id=old.id;
    end //

    delimiter ;

    -- 触发器
    drop trigger customer_trriger;
    delimiter //
    create trigger  customer_trriger 
         before insert on s_customer 
    for each row
    begin
    	if new.id<5
    		select 'error';
    end //

    delimiter ;