mysql

脏读/不可重复读/幻读都可能

脏读不可能，不可重复读/幻读都可能

不可重复读/脏读不可能，幻读都可能

脏读/不可重复读/幻读都不可能

CREATE TABLE employees (
id INT NOT NULL,
NAME VARCHAR ( 30 ),
hired DATE NOT NULL DEFAULT '2018-12-01',
job VARCHAR ( 30 ) NOT NULL,
dept_id INT NOT NULL
)
PARTITION BY RANGE ( dept_id )(
PARTITION p0 VALUES LESS THAN ( 6 ),
PARTITION p1 VALUES LESS THAN ( 11 ),
PARTITION p2 VALUES LESS THAN ( 16 ),
PARTITION p3 VALUES LESS THAN ( 21 ) );

CREATE TABLE employees (
         id INT NOT NULL,
         NAME VARCHAR ( 30 ),
         hired DATE NOT NULL DEFAULT '2015-12-10',
         job_code INT,
         store_id INT )
PARTITION BY LIST ( store_id )(
PARTITION pQY VALUES IN ( 3, 5, 6, 17 ),
PARTITION pJN VALUES IN ( 1, 10, 11, 19 ),
PARTITION pCH VALUES IN ( 4, 12, 14, 18 ),
PARTITION pJJ VALUES IN ( 2, 9, 13, 16 ),
PARTITION pGX VALUES IN ( 7, 8, 15, 20 ));

常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)

线性hash分区使用的是一个线性的2的幂运算法则

partition by key(expr) partitions num;
-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键
partition by key() partitions num;
-- linear key
partition by linear key(expr)

create table ts (
id int,
purchased date
)
partition by range(year(purchased))
subpartition by hash(to_days(purchased)) subpartitions 2
(
partition p0 values less than (1990),
partition p0 values less than (2000),
partition p0 values less than maxvalue
);

id相同,执行顺序由上至下;
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
id相同不同,都存在

SIMPLE:简单的select查询，查询中不包括子查询或者UNION

PRIMARY：查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY

SUBQUERY:在select或where列表中包含了子查询

DERIVED: 在FROM列表中,包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,
把结果放在临时表里

UNION: 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,
外侧SELECT将被标记为 DERIVE

UNION RESULT: 从UNION表获取结果的SELECT

ALL：全部扫描，效率低

index：Full Index Scan，index与ALL区别为index类型只遍历索引树
explain select * from film; file中有name字段为索引

range：只检索给定范围的行，使用一个索引来选择行
explain select * from employee where rec_id < 3其中rec_id为主键

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）
explain select * from film where name = "film1" ref name是film中的普通索引

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
eq_ref 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的
explain select * from film_actor left join film on film_actor.film_id= film.id eq_ref 根据主键或唯一索引查询film

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
select 1 from actor where id =1 const
explain extended select * from (select * from film where id =1) tmp 临时表中只有一条记录，system

NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成explain select min(id) from film id是主键

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，
这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by;distinct

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer

数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度

不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。
innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高

金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度
如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal
如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围
不建议使用ENUM、SET类型，使用TINYINT来代替

根据实际需要选择能够满足应用的最小存储日期类型。

如果应用只需要记录年份，那么仅用一个字节的year类型。
如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31
如果记录时间时分秒使用它time类型
如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多
如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应
datetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6)
timestamp与datetime

两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

都可以使用自动更新CURRENT_TIMESTAMP

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
datetime占用8个字节 ：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大

注意：

禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算
也尽量不要使用int来存储时间戳

MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。

在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”， 而空值”“是不占用空间的。

含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。

联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义

注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。

1) NULL<>NULL的返回结果是NULL，而不是false。
2) NULL=NULL的返回结果是NULL，而不是true。
3) NULL<>1的返回结果是NULL，而不是true。

根据版本号控制

锁定表或者行，让其他数据操作等待

表锁偏向MyISAM存储引擎，开销小，加锁快，无思索，锁定粒度大，发生锁冲突的概率最高，并发度最低
当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞 

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MYISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

快照读 snapshot read

当前读 current read

特点

过程

两阶段锁实现

通过索引+mvcc 实现

当前正在等待锁定的数量

从系统启动到现在锁定总时间长度

每次等待所花平均时间

从系统启动到现在等待最长的一次所花时间

系统启动后到现在总共等待的次数