Mysql-执行过程、分区表、锁

简单执行流程

分析器
将sql语句分析成语法树
查询优化器
语法解析
检查sql语句是否合法
执行计划
优化器是根据成本来选择执行计划的，所以优化后的不一定就是效率最高的查询；
查看某一条sql语句使用的成本：

#在执行完一条sql语句后
show status like 'last_query_cost'

就能查看上条sql语句使用的成本

CBO:基于成本的优化
RBO：基于规则的优化
多表关联

select * from user inner join role using(id) inner join authorize using(role_id)

这样的一条语句，在执行时不一定就是从左到右先查询user表、role、表、authorize表，优化器会做优化，会选择成本最低的执行计划
当然我们也可以强制指定按顺序查找表：straight_join

select straight_join * from user inner join role using(id) inner join authorize using(role_id)

分区表

分区字段要包含在主键中
null值会使分区过滤无效
分区列和索引列不匹配，会导致查询无法进行分区过滤
选择分区的成本可能很高
打开并锁住所有底层表的成本可能很高
维护分区的成本可能很高
mysql提供分区表功能，可按照字段进行分区

range分区

按照分区表达式的运算结果，判断结果落在某个范围内,从而进行分区
按date字段以年份分区

CREATE table part(
	id int auto_increment,
	date DATETIME not null,
	PRIMARY key (id,date)
)
PARTITION by range(YEAR(date))(
	PARTITION year_2018 values  less than (2019),
	PARTITION year_2019 values  less than (2020),
	PARTITION year_2020 values  less than (2021),
	PARTITION year_2021 values  less than (2022),
	PARTITION year_2022 values  less than (2023)
);

分区表是单独文件存储的：创建一个普通表使用innodb，磁盘中会有一个frm、一个idb文件，而一个分区表会会对应一个idb文件

list分区

分区字段可使用表达式
与范围分区不同，List分区是一系列精确值
使用id值模4进行分区

CREATE table part(
	id int auto_increment,
	date DATETIME not null,
	PRIMARY key (id)
)
PARTITION by list(id%4)(
	PARTITION p0 values in (0),
	PARTITION p1 values in (1),
	PARTITION p2 values in (2),
	PARTITION p3 values in (3)
);
#或不使用表达式
PARTITION by list(id)(
	PARTITION p0 values in (0,1,2,3),
	PARTITION p1 values in (4,5,6),
	PARTITION p2 values in (7,8,9),
	PARTITION p3 values in (10,11,23)
);

插入几条数据

查看分区表信息：

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='part';  

结果确实按照id模4的结果将数据存入对应分区表中

列分区

与list和range分区类似。不支持表达式，支持多种数据类型

CREATE table part(
	id int auto_increment,
	date DATETIME not null,
	PRIMARY key (id)
) 
partition by list COLUMNS(id)(
	PARTITION p0 values in (0,1,2,3),
	PARTITION p1 values in (4,5,6),
	PARTITION p2 values in (7,8,9),
	PARTITION p3 values in (10,11,23)
);

key分区

CREATE table part(
	id int auto_increment,
	date DATETIME not null,
	PRIMARY key (id)
)
partition by key()#默认使用unique key
partition 4;

CREATE table part(
	id int auto_increment,
	name varchar(10) not null,
	PRIMARY key (id,name)
)
partition by key(name)#默认使用unique key
partition 4;

hash分区

按照id使用hash分成4个分区表

CREATE table part(
	id int auto_increment,
	date DATETIME not null,
	PRIMARY key (id)
)
partition by hash(id)
partition 4;

子分区

分区再分区：先按年分区再按月分区

CREATE table part(
	id int auto_increment,
	date DATETIME not null,
	PRIMARY key (id)
) 
partition by range (year(date))
SUBPARTITION by hash (MONTH(date))
SUBPARTITIONS 12
(
	PARTITION m1 values less than (2),
	PARTITION m2 values less than (3),
	PARTITION m3 values less than (4),
	PARTITION m4 values less than (5),
	PARTITION m5 values less than (6),
	PARTITION m6 values less than (7),
	PARTITION m7 values less than (8),
	PARTITION m8 values less than (9),
	PARTITION m9 values less than (10),
	PARTITION m10 values less than (11),
	PARTITION m11 values less than (12),
	PARTITION m12 values less than (13)
);

锁

myisam表锁

myisam支持表锁，表共享读锁、表独占写锁
表独占写锁：当一个会话获得一张表的写锁后，只有持有锁的会话可以对表进行读写操作，其他会话的读写操作都会等待，知道释放锁为止
lock table xx write：表加写锁
unlock tables：释放锁
表共享读锁：当一个会话获得一张表的读锁后，持有锁的会话只能查询，更新操作和访问其他表都会提示错误，其他会话可以查询此表，更新就会等待锁被释放
lock table xx read：表加读锁
unlock tables：释放锁
myisam在执行sql时会隐式加锁、解锁

innodb

innodb支持表锁、行锁
行锁：锁的是索引，如果检索数据条件没有使用索引 那么就会使用表锁
共享锁：读锁，若事务T对数据A加共享锁，事务T可以读取但不能修改数据。其他事务可以对数据A加共享锁而不能加排他锁，直到事务T释放锁。
lock in share model
排他锁：写锁，若事务T对数据A加排他锁，事务T可以读取也能修改数据，其他事务不能对数据A加任何锁，直到事务T释放数据A的锁为止
for update
innodb下，insert、update、delete都会默认加上排他锁，select默认不会加任何锁；
所以加过排他锁的数据在其他事务中是无法被修改的
不能使用lock in share mode和for update查询数据(其他事务无法修改加锁的数据)
可以使用select 不加锁条件查，普通查询不会加锁