数据库专题

数据库专题

mysql一般分为4层 链接层 服务层 引擎层 存储层

存储引擎

innodb 支持外键, ACID 全部事务

myISAM 适合于大量数据读 而少量数据写的操作 (myISAM是性能优先,大量数据查询分析,适合myISAM)

innoDB 适合有较多更新操作的情况 (innoDB是事务优先,如果有较多的高并发操作,那么适合innodb )

memory 适合不需要永久存储的情况

索引的分类

  1. B+ Tree 索引
  2. Hash索引
  3. full-text 全文索引
  4. R-tree 索引

也可以这么分类

  1. 单值索引 单列,age: 一个表可以有多个单值索引
  2. 唯一索引: 不能重复 id 它与主键索引的一个区别就是唯一索引能为null 但是主键索引不能为null
  3. 复合索引: 多个列构成的索引
  4. 主键索引: 主键索引是不能重复的id 它和唯一索引的区别是 主键索引不可以为null 唯一索引可以

创建方式1

1
2
3
4
5
create  索引类型 索引名 on 表(字段)

单值索引:create index name_index on t_student(name)
唯一索引:create unique index name_index on t_student(name)
复合索引:create index age_name_index on t_student(age,name)

mysql 事务隔离级别

  1. read uncommitted
    所有事务都可以看到其他事务未提交的执行结果 读取未提交的数据 称为脏读

  2. read committed(读已提交)
    事务 只能看见已经提交的事务 所做的改变,同一个transaction中 两个语句相同的查询可能返回不同的结果 造成了不可重复读的问题

  3. repeatable read(可重复读 mysql默认的)
    确保同一个事务 在并发读取数据的时候 会看到同样的读取结果

  4. serializable (串行化)
    不会发生以上的三类问题.(串行化是指 两个窗口 同时对一组数据进行事务操作的时候 会冲突 只有一个窗口执行好后 再执行另一个窗口的操作)

行锁 mysql的 innodb是支持行锁的

sql编写过程

1
2
3
4
5
编写过程:
select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit ..

解析过程:
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

mysql 优化

分析SQL的执行计划: 使用 explain 可以模拟SQL优化器,从而让开发人员知道自己编写的SQL执行

查询执行计划 : explain + SQL 语句

explain有以下的字段

1
2
3
4
5
6
7
8
9
id: 编号
select_type: 查询类型
table: 表
tyope: 类型
possible_key: 预测用到的索引
key: 实际运用到的索引
key_len:实际使用的索引长度
ref:表之间的引用
rows:通过索引查询到的数据个数

如果在多张表联合查询,一般来说先查数据量小的表,然后查数据量大的表

id值不同:id值越大越优先查询 (本质:在嵌套子查询时,先查内层 再查外层)

查询教授SQL课程的老师的描述(desc)
explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
and t.tcid = tc.tcid and c.cname = ‘sql’ ;

将以上 多表查询 转为子查询形式:

explain select tc.tcdesc from teacherCard tc where tc.tcid =
(select t.tcid from teacher t where t.tid =
(select c.tid from course c where c.cname = ‘sql’)
);

子查询+多表:
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = ‘sql’) ;

id值有相同,又有不同: id值越大越优先;id值相同,从上往下 顺序执行

  • A:原子性(Atomicity)
    表示事务内操作不可分割。要么都成功、要么都是失败。
    
  • C:一致性(Consistency)
    要么都成功、要么都是失败.后面的失败了要对前面的操作进行回滚。
    
  • I:隔离性(Isolation)
    多个用户并发访问数据库的时候,一个事务的执行不能被其他事务干扰。
    
  • D:持续性/永久性(Durability)
    一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。
    

select_type:查询类型

PRIMARY:包含子查询SQL中的 主查询 (最外层)
SUBQUERY:包含子查询SQL中的 子查询 (非最外层)
simple:简单查询(不包含子查询、union)
derived:衍生查询(使用到了临时表)
a.在from子查询中只有一张表
explain select cr.cname from ( select * from course where tid in (1,2) ) cr ;

b.在from子查询中, 如果有table1 union table2 ,则table1 就是derived,table2就是union
    explain select  cr.cname     from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

union:上例
union result :告知开发人员,那些表之间存在union查询

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

type:索引类型、类型

system>const>eq_ref>ref>range>index>all   ,要对type进行优化的前提:有索引

const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 (类型 与索引类型有关)

eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0, 就是说查出来的每条数据都不重复)
如果teacher表的数据个数 和 连接查询的数据个数一致(都是3条数据),则有可能满足eq_ref级别;否则无法满足。

ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)
准备数据:
insert into teacher values(4,’tz’,4) ;
insert into teacherCard values(4,’tz222’);

range:检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ,从而转为 无索引all)

index:查询全部索引中数据
explain select tid from teacher ; –tid 是索引, 只需要扫描索引表,不需要所有表中的所有数据

all:查询全部表中的数据
explain select cid from course ; –cid不是索引,需要全表所有,即需要所有表中的所有数据

system/const: 结果只有一条数据
eq_ref:结果多条;但是每条数据是唯一的 ;
ref:结果多条;但是每条数据是是0或多条 ;

ref : 注意与type中的ref值区分。

作用: 指明当前表所 参照的 字段。
    select ....where a.c = b.x ;(其中b.x可以是 常量-const)

alter table course add index tid_index (tid) ;

explain select * from course c,teacher t where c.tid = t.tid  and t.tname ='tw' ;

rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)

explain select * from course c,teacher t  where c.tid = t.tid
and t.tname = 'tz' ;

Extra:

(i).using filesort (文件内排序): 性能消耗大;需要“额外”的一次排序(查询)  。常见于 order by 语句中。
where 和 order by  不要跨列使用

explain select * from test02 where a1 =’’ order by a2 ; –using filesort
小结:对于单索引, 如果排序和查找是同一个字段,则不会出现using filesort;如果排序和查找不是同一个字段,则会出现using filesort;
避免: where哪些字段,就order by那些字段2

复合索引:不能跨列(最佳左前缀)
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
explain select from test02 where a1=’’ order by a3 ; –using filesort
explain select
from test02 where a2=’’ order by a3 ; –using filesort
explain select from test02 where a1=’’ order by a2 ;
explain select
from test02 where a2=’’ order by a1 ; –using filesort
小结:避免: where和order by 按照复合索引的顺序使用,不要跨列或无序使用。

(ii). using temporary:性能损耗大 ,用到了临时表。一般出现在group by 语句中。
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary
避免:查询那些列,就根据那些列 group by .

(iii). using index :性能提升; 索引覆盖(覆盖索引)。原因:不读取原文件,只从索引文件中获取数据 (不需要回表查询)
    只要使用到的列 全部都在索引中,就是索引覆盖using index

例如:test02表中有一个复合索引(a1,a2,a3)
    explain select a1,a2 from test02 where a1='' or a2= '' ; --using index   

    drop index idx_a1_a2_a3 on test02;

    alter table test02 add index idx_a1_a2(a1,a2) ;
    explain select a1,a3 from test02 where a1='' or a3= '' ;


    如果用到了覆盖索引(using index时),会对 possible_keys和key造成影响:
    a.如果没有where,则索引只出现在key中;
    b.如果有where,则索引 出现在key和possible_keys中。

    explain select a1,a2 from test02 where a1='' or a2= '' ;
    explain select a1,a2 from test02  ;

(iii).using where (需要回表查询)
    假设age是索引列
    但查询语句select age,name from ...where age =...,此语句中必须回原表查Name,因此会显示using where.

explain select a1,a3 from test02 where a3 = '' ; --a3需要回原表查询


(iv). impossible where : where子句永远为false
    explain select * from test02 where a1='x' and a1='y'  ;

sql优化原理

(1)单表优化:

1
2
3
4
5
6
7
8
create table book
(
bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);

优化:加索引
alter table book add index idx_bta (bid,typeid,authorid);

索引一旦进行 升级优化,需要将之前废弃的索引删掉,防止干扰。
drop index idx_bta on book;

根据SQL实际解析的顺序,调整索引的顺序:
alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid,但是将bid放到索引中 可以提升使用using index ;

再次优化(之前是index级别):思路。因为范围查询in有时会实现,因此交换 索引的顺序,将typeid in(2,3) 放到最后。
drop index idx_tab on book;
alter table book add index idx_atb (authorid,typeid,bid);
explain select bid from book where  authorid=1 and  typeid in(2,3) order by typeid desc ;

–小结:

  • a.最佳做前缀,保持索引的定义和使用的顺序一致性
  • b.索引需要逐步优化
  • c.将含In的范围查询 放到where条件的最后,防止失效。

多表查询优化

索引往哪张表加?   -小表驱动大表  
              -索引建立经常使用的字段上 (本题 t.cid=c.cid可知,t.cid字段使用频繁,因此给该字段加索引) [一般情况对于左外连接,给左表加索引;右外连接,给右表加索引
              原因: 左外连接 会和左表中的所有数据都加索引

避免索引失效的原则

  1. 复合索引 不要跨列使用或者无序使用(无序有时候会被sql优化器优化 有时候不会)(最佳左前缀原则)
  2. 不要对索引进行任何操作(计算、函数、类型转换),否则索引失效
  3. 复合索引不能使用不等于(!= <>)或is null (is not null),否则自身以及右侧所有全部失效。
    复合索引中如果有>,则右侧索引全部失效,自身不失效。 但是如果各自是单值索引 那么不会影响。

  4. 补救。尽量使用索引覆盖(using index)

    (a,b,c)
    

    select a,b,c from xx..where a= .. and b =.. ;

    1. like尽量以“常量”开头,不要以’%’开头,否则索引失效

      1
      2
      3
      4
      5
      6
      select * from xx where name like '%x%' ; --name索引失效

      explain select * from teacher where tname like '%x%'; --tname索引失效
      explain select * from teacher where tname like 'x%';

      explain select tname from teacher where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询,可以使用索引覆盖 挽救一部分。
    2. 尽量不要使用类型转换(显示、隐式),否则索引失效
      explain select from teacher where tname = ‘abc’ ;
      explain select
      from teacher where tname = 123 ;//程序底层将 123 -> ‘123’,即进行了类型转换,因此索引失效

    3. 尽量不要使用or,否则索引失效
      explain select * from teacher where tname =’’ or tcid >1 ; –将or左侧的tname 和右边的tcid都失效。

一些SQL优化的方法

1. exist和in
1
2
select ..from table where exist (子查询) ;
select ..from table where 字段 in (子查询) ;
如果主查询的数据集大,则使用In ,效率高。 如果子查询的数据集大,则使用exist,效率高。 exist语法: 将主查询的结果,放到子查需结果中进行条件校验(看子查询是否有数据,如果有数据 则校验成功) , 如果 复合校验,则保留数据; select tname from teacher where exists (select * from teacher) ; --等价于select tname from teacher select tname from teacher where exists (select * from teacher where tid =9999) ; in: select ..from table where tid in (1,3,5) ; 2. order by 优化 using filesort 有两种算法:双路排序、单路排序 (根据IO的次数) MySQL4.1之前 默认使用 双路排序;双路:扫描2次磁盘(1:从磁盘读取排序字段 ,对排序字段进行排序(在buffer中进行的排序) 2:扫描其他字段 ) --IO较消耗性能 MySQL4.1之后 默认使用 单路排序 : 只读取一次(全部字段),在buffer中进行排序。但种单路排序 会有一定的隐患 (不一定真的是“单路|1次IO”,有可能多次IO)。原因:如果数据量特别大,则无法 将所有字段的数据 一次性读取完毕,因此 会进行“分片读取、多次读取”。 注意:单路排序 比双路排序 会占用更多的buffer。 单路排序在使用时,如果数据大,可以考虑调大buffer的容量大小: set max_length_for_sort_data = 1024 单位byte 如果max_length_for_sort_data值太低,则mysql会自动从 单路->双路 (太低:需要排序的列的总大小超过了max_length_for_sort_data定义的字节数) 提高order by查询的策略: a.选择使用单路、双路 ;调整buffer的容量大小; b.避免select * ... c.复合索引 不要跨列使用 ,避免using filesort d.保证全部的排序字段 排序的一致性(都是升序 或 降序)
1
2
3
4
5
6
7
8
9
   
--获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

--获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

--按照时间排序,前10条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
11.锁机制 :解决因资源共享 而造成的并发问题。 示例:买最后一件衣服X A: X 买 : X加锁 ->试衣服...下单..付款..打包 ->X解锁 B: X 买:发现X已被加锁,等待X解锁, X已售空 分类: 操作类型: a.读锁(共享锁): 对同一个数据(衣服),多个读操作可以同时进行,互不干扰。 b.写锁(互斥锁): 如果当前写操作没有完毕(买衣服的一系列操作),则无法进行其他的读操作、写操作 操作范围: a.表锁 :一次性对一张表整体加锁。如MyISAM存储引擎使用表锁,开销小、加锁快;无死锁;但锁的范围大,容易发生锁冲突、并发度低。 b.行锁 :一次性对一条数据加锁。如InnoDB存储引擎使用行锁,开销大,加锁慢;容易出现死锁;锁的范围较小,不易发生锁冲突,并发度高(很小概率 发生高并发问题:脏读、幻读、不可重复度、丢失更新等问题)。 c.页锁

(2)行表(InnoDB)
create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values(‘1’) ;
insert into linelock(name) values(‘2’) ;
insert into linelock(name) values(‘3’) ;
insert into linelock(name) values(‘4’) ;
insert into linelock(name) values(‘5’) ;

–mysql默认自动commit; oracle默认不会自动commit ;

为了研究行锁,暂时将自动commit关闭; set autocommit =0 ; 以后需要通过commit

会话0: 写操作
    insert into linelock values(    'a6') ;

会话1: 写操作 同样的数据
    update linelock set name='ax' where id = 6;

对行锁情况:
    1.如果会话x对某条数据a进行 DML操作(研究时:关闭了自动commit的情况下),则其他会话必须等待会话x结束事务(commit/rollback)后  才能对数据a进行操作。
    2.表锁 是通过unlock tables,也可以通过事务解锁 ; 行锁 是通过事务解锁。



行锁,操作不同数据:

会话0: 写操作

    insert into linelock values(8,'a8') ;
会话1: 写操作, 不同的数据
    update linelock set name='ax' where id = 5;
    行锁,一次锁一行数据;因此 如果操作的是不同数据,则不干扰。


行锁的注意事项:
a.如果没有索引,则行锁会转为表锁
show index from linelock ;
alter table linelock add index idx_linelock_name(name);


会话0: 写操作
    update linelock set name = 'ai' where name = '3' ;

会话1: 写操作, 不同的数据
    update linelock set name = 'aiX' where name = '4' ;



会话0: 写操作
    update linelock set name = 'ai' where name = 3 ;

会话1: 写操作, 不同的数据
    update linelock set name = 'aiX' where name = 4 ;

--可以发现,数据被阻塞了(加锁)
-- 原因:如果索引类 发生了类型转换,则索引失效。 因此 此次操作,会从行锁 转为表锁。

b.行锁的一种特殊情况:间隙锁:值在范围内,但却不存在
 --此时linelock表中 没有id=7的数据
 update linelock set name ='x' where id >1 and id<9 ;   --即在此where范围中,没有id=7的数据,则id=7的数据成为间隙。
间隙:Mysql会自动给 间隙 加索 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁(行锁)。
行锁:如果有where,则实际加索的范围 就是where后面的范围(不是实际的值)


如何仅仅是查询数据,能否加锁? 可以   for update 
研究学习时,将自动提交关闭:
    set autocommit =0 ;
    start transaction ;
    begin ;
 select * from linelock where id =2 for update ;

通过for update对query语句进行加锁。

行锁:
InnoDB默认采用行锁;
缺点: 比表锁性能损耗大。
优点:并发能力强,效率高。
因此建议,高并发用InnoDB,否则用MyISAM。

行锁分析:
  show status like '%innodb_row_lock%' ;
     Innodb_row_lock_current_waits :当前正在等待锁的数量  
      Innodb_row_lock_time:等待总时长。从系统启到现在 一共等待的时间
     Innodb_row_lock_time_avg  :平均等待时长。从系统启到现在平均等待的时间
     Innodb_row_lock_time_max  :最大等待时长。从系统启到现在最大一次等待的时间
     Innodb_row_lock_waits :    等待次数。从系统启到现在一共等待的次数