MySQL高级部分理论知识细讲

来到golang学习网的大家，相信都是编程学习爱好者，希望在这里学习数据库相关编程知识。下面本篇文章就来带大家聊聊《MySQL高级部分理论知识细讲》，介绍一下MySQL，希望对大家的知识积累有所帮助，助力实战开发！

文章目录

一、数据库分区、分表、分库、分片

YesOk ,大家好 ，我是小刘，许久不见，甚是想念 ，小刘今天来带大家学习 分库分表的基础知识

1.1 单机数据库的瓶颈

• 单个表数据量越大，读写锁，插入操作重新建立索引效率越低。
• 单个库数据量太大（一个库数据量到1T - 2T就是极限）
• 单个数据库服务器压力过大
• 读写速度遇到瓶颈（并发量几百）

1.2 分区

数据库分区是一种物理数据库的设计技术，它的目的是为了在特定的

create table sales(
    id int auto increment,
    amount double not null,
    order_day datetime not null,
    primary key(id,order_day)
) engine=Innodb

设置分区：

partition by range(year(order_day))(
    partition p_2010 values less than (2000),
    partition p_2011 values less than (2011),
    partition p_2012 values less than (2012),
    partition p_2012 values less than maxvalue
);

1.3 分表

1.3.1 什么时候考虑分表？

• 一张表的查询速度已经慢到影响使用的时候

• [session1]
  
  lock table user read;
  
      这里只能执行查询当前表，不能查询其他表，插入或更新当前表都会提示错误
  
  unlock tables;
  
  [session2]
  
  在session1锁定表后，session2能查询或更新未锁定的表，能查询锁定表，插入或者更新锁定表会一直等待锁被释放。

  案例1【加写锁】:

  [session1]
  
  lock tables user write;
  
      这里可以对锁定表做查询、更新、插入操作
  
  unlock tables;
  
  [session2]
  
  在session1锁定表后，查询、更新、插入操作均需要等到锁被释放。

  结论：

  1. 对

     [session1]
     
     set autocommit=0;
     
         这里可以对锁定表做更新操作
     
     commit;
     
     [session2]
     
     在session2锁定表后不commit时，这里对锁定表进行update操作，会等待锁释放。

     无索引行锁升级为表锁

     当某个索引列没有正常使用，如赋错误的类型的值，会导致行锁变表锁。

     间隙锁危害

     间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或拍他锁时，

     select p1.Email from person p1 where p1.Email in (select p2.Email from person p2 where p1.Id!=p2.Id);
     
     [优]SELECT email FROM `person` group by email HAVING count(email)>1;
     
     [拓展]删除重复数据
     [思路]根据重复数据进行分组,然后查出最小的id,删除其他之外的id行,这里得创建一个临时表,
     在mysql中,不能在一条Sql语句中,即查询这些数据,同时修改这些数据
     DELETE from person  where id not in( select temp.id from (SELECT min(id) id FROM person group by email)as temp);
     注意：这里在mysql5.7以上版本会报错,因为不支持select那些group by和聚合函数之外的字段

     4.2 索引创建和查看

     创建：

     create index idx_a_b on table(col_a,col_b);

     查看：

     show index from table;

     4.3 where 1=1和where 1=0的意义

     where 1=1

     用于拼接多条件语句时，这样就不用管条件是否存在，拼

     where

     还是拼

     and

     。

     where1=0

     不返回数据，仅返回结构，用于快熟建表。

     再见啦

     今天关于《MySQL高级部分理论知识细讲》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！