事务特性ACID&＃xff1a;原子性&＃xff08;Atomicity&＃xff09;、一致性&＃xff08;Consistency&＃xff09;、隔离性&＃xff08;Isolation&＃xff09;、持久性&＃xff08;Durability&＃xff09;。
read committed&＃xff0c;一个事务只能读到已经提交的修改。第一范式1NF
确保数据库表字段的原子性。
比如字段 userInfo: 广东省 10086&＃39; &＃xff0c;依照第一范式必须拆分成 userInfo: 广东省 userTel:10086两个字段。
第二范式2NF
首先要满足第一范式&＃xff0c;另外包含两部分内容&＃xff0c;一是表必须有一个主键&＃xff1b;二是非主键列必须完全依赖于主键&＃xff0c;而不能只依赖于主键的一部分。
举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)&＃xff0c;主键为(student_no, course_name)。其中学分完全依赖于课程名称&＃xff0c;姓名年龄完全依赖学号&＃xff0c;不符合第二范式&＃xff0c;会导致数据冗余&＃xff08;学生选n门课&＃xff0c;姓名年龄有n条记录&＃xff09;、插入异常&＃xff08;插入一门新课&＃xff0c;因为没有学号&＃xff0c;无法保存新课记录&＃xff09;等问题。
可以拆分成三个表&＃xff1a;学生&＃xff1a;student(stuent_no, student_name, 年龄)&＃xff1b;课程&＃xff1a;course(course_name, credit)&＃xff1b;选课关系&＃xff1a;student_course_relation(student_no, course_name, grade)。
第三范式3NF
首先要满足第二范式&＃xff0c;另外非主键列必须直接依赖于主键&＃xff0c;不能存在传递依赖。即不能存在&＃xff1a;非主键列 A 依赖于非主键列 B&＃xff0c;非主键列 B 依赖于主键的情况。
假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)&＃xff0c;主键为"学号"&＃xff0c;其中学院id依赖于学号&＃xff0c;而学院地点和学院电话依赖于学院id&＃xff0c;存在传递依赖&＃xff0c;不符合第三范式。
可以把学生关系表分为如下两个表&＃xff1a;学生&＃xff1a;(student_no, student_name, age, academy_id)&＃xff1b;学院&＃xff1a;(academy_id, academy_telephone)。
2NF和3NF的区别&＃xff1f;
先了解下几个概念&＃xff1a;脏读、不可重复读、幻读。
不可重复读和脏读的区别是&＃xff0c;脏读是某一事务读取了另一个事务未提交的脏数据&＃xff0c;而不可重复读则是读取了前一事务提交的数据。
幻读和不可重复读都是读取了另一条已经提交的事务&＃xff0c;不同的是不可重复读的重点是修改&＃xff0c;幻读的重点在于新增或者删除。
事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。
MySQL数据库为我们提供的四种隔离级别&＃xff1a;
查看隔离级别&＃xff1a;
select &＃64;&＃64;transaction_isolation;
设置隔离级别&＃xff1a;
set session transaction isolation level read uncommitted ;
索引是存储引擎用于提高数据库表的访问速度的一种数据结构。
优点&＃xff1a;
缺点&＃xff1a;
数据是存储在磁盘上的&＃xff0c;查询数据时&＃xff0c;如果没有索引&＃xff0c;会加载所有的数据到内存&＃xff0c;依次进行检索&＃xff0c;读取磁盘次数较多。有了索引&＃xff0c;就不需要加载所有数据&＃xff0c;因为B&＃43;树的高度一般在2-4层&＃xff0c;最多只需要读取2-4次磁盘&＃xff0c;查询速度大大提升。
where条件中用不到的字段不适合建立索引索引的数据结构主要有B&＃43;树和哈希表&＃xff0c;对应的索引分别为B&＃43;树索引和哈希索引。InnoDB引擎的索引类型有B&＃43;树索引和哈希索引&＃xff0c;默认的索引类型为B&＃43;树索引。
B&＃43;树索引
B&＃43; 树是基于B 树和叶子节点顺序访问指针进行实现&＃xff0c;它具有B树的平衡性&＃xff0c;并且通过顺序访问指针来提高区间查询的性能。
在 B&＃43; 树中&＃xff0c;节点中的 key 从左到右递增排列&＃xff0c;如果某个指针的左右相邻 key 分别是 keyi 和 keyi&＃43;1&＃xff0c;则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi&＃43;1。
进行查找操作时&＃xff0c;首先在根节点进行二分查找&＃xff0c;找到key所在的指针&＃xff0c;然后递归地在指针所指向的节点进行查找。直到查找到叶子节点&＃xff0c;然后在叶子节点上进行二分查找&＃xff0c;找出key所对应的数据项。
MySQL 数据库使用最多的索引类型是BTREE索引&＃xff0c;底层基于B&＃43;树数据结构来实现。
mysql> show index from blog\G;
*************************** 1. row ***************************Table: blogNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: blog_idCollation: ACardinality: 4Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
哈希索引
哈希索引是基于哈希表实现的&＃xff0c;对于每一行数据&＃xff0c;存储引擎会对索引列进行哈希计算得到哈希码&＃xff0c;并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的&＃xff0c;将哈希码的值作为哈希表的key值&＃xff0c;将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)&＃xff0c;一般多用于精确查找。
1、主键索引&＃xff1a;名为primary的唯一非空索引&＃xff0c;不允许有空值。
2、唯一索引&＃xff1a;索引列中的值必须是唯一的&＃xff0c;但是允许为空值。唯一索引和主键索引的区别是&＃xff1a;唯一约束的列可以为null且可以存在多个null值。唯一索引的用途&＃xff1a;唯一标识数据库表中的每条记录&＃xff0c;主要是用来防止数据重复插入。创建唯一索引的SQL语句如下&＃xff1a;
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
3、组合索引&＃xff1a;在表中的多个字段组合上创建的索引&＃xff0c;只有在查询条件中使用了这些字段的左边字段时&＃xff0c;索引才会被使用&＃xff0c;使用组合索引时需遵循最左前缀原则。
4、全文索引&＃xff1a;只有在MyISAM引擎上才能使用&＃xff0c;只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。
如果 SQL 语句中用到了组合索引中的最左边的索引&＃xff0c;那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配&＃xff0c;后面的字段不会用到索引。
对(a,b,c)建立索引&＃xff0c;查询条件使用 a/ab/abc 会走索引&＃xff0c;使用 bc 不会走索引。
对(a,b,c,d)建立索引&＃xff0c;查询条件为a &＃61; 1 and b &＃61; 2 and c > 3 and d &＃61; 4&＃xff0c;那么a、b和c三个字段能用到索引&＃xff0c;而d无法使用索引。因为遇到了范围查询。
如下图&＃xff0c;对(a, b) 建立索引&＃xff0c;a 在索引树中是全局有序的&＃xff0c;而 b 是全局无序&＃xff0c;局部有序&＃xff08;当a相等时&＃xff0c;会根据b进行排序&＃xff09;。直接执行b &＃61; 2这种查询条件无法使用索引。
当a的值确定的时候&＃xff0c;b是有序的。例如a &＃61; 1时&＃xff0c;b值为1&＃xff0c;2是有序的状态。当a &＃61; 2时候&＃xff0c;b的值为1&＃xff0c;4也是有序状态。 当执行a &＃61; 1 and b &＃61; 2时a和b字段能用到索引。而执行a > 1 and b &＃61; 2时&＃xff0c;a字段能用到索引&＃xff0c;b字段用不到索引。因为a的值此时是一个范围&＃xff0c;不是固定的&＃xff0c;在这个范围内b值不是有序的&＃xff0c;因此b字段无法使用索引。
InnoDB使用表的主键构造主键索引树&＃xff0c;同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的&＃xff0c;使用双向链表连接&＃xff0c;叶子节点按照主键的顺序排序&＃xff0c;因此对于主键的排序查找和范围查找速度比较快。
聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。
对于InnoDB来说&＃xff0c;聚集索引一般是表中的主键索引&＃xff0c;如果表中没有显示指定主键&＃xff0c;则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引&＃xff0c;那么InnoDB内部会生成一个隐藏的主键作为聚集索引&＃xff0c;这个隐藏的主键长度为6个字节&＃xff0c;它的值会随着数据的插入自增。
select的数据列只用从索引中就能够取得&＃xff0c;不需要回表进行二次查询&＃xff0c;也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引&＃xff0c;如果索引能覆盖到查询的列&＃xff0c;那么就可以避免对主键索引的二次查询。
不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值&＃xff0c;而哈希索引、全文索引不存储索引列的值&＃xff0c;所以MySQL使用b&＃43;树索引做覆盖索引。
对于使用了覆盖索引的查询&＃xff0c;在查询前面使用explain&＃xff0c;输出的extra列会显示为using index。
比如user_like 用户点赞表&＃xff0c;组合索引为(user_id, blog_id)&＃xff0c;user_id和blog_id都不为null。
explain select blog_id from user_like where user_id &＃61; 13;
explain结果的Extra列为Using index&＃xff0c;查询的列被索引覆盖&＃xff0c;并且where筛选条件符合最左前缀原则&＃xff0c;通过索引查找就能直接找到符合条件的数据&＃xff0c;不需要回表查询数据。
explain select user_id from user_like where blog_id &＃61; 1;
explain结果的Extra列为Using where; Using index&＃xff0c; 查询的列被索引覆盖&＃xff0c;where筛选条件不符合最左前缀原则&＃xff0c;无法通过索引查找找到符合条件的数据&＃xff0c;但可以通过索引扫描找到符合条件的数据&＃xff0c;也不需要回表查询数据。
导致索引失效的情况&＃xff1a;
%abc&＃xff0c;无法使用索引&＃xff1b;非%开头的like查询如abc%&＃xff0c;相当于范围查询&＃xff0c;会使用索引or连接&＃xff0c;也会导致索引失效有时需要在很长的字符列上创建索引&＃xff0c;这会造成索引特别大且慢。使用前缀索引可以避免这个问题。
前缀索引是指对文本或者字符串的前几个字符建立索引&＃xff0c;这样索引的长度更短&＃xff0c;查询速度更快。
创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高&＃xff0c;因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。
建立前缀索引的方式&＃xff1a;
// email列创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
MySQL中常用的四种存储引擎分别是&＃xff1a; MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。
InnoDB存储引擎
InnoDB是MySQL默认的事务型存储引擎&＃xff0c;使用最广泛&＃xff0c;基于聚簇索引建立的。InnoDB内部做了很多优化&＃xff0c;如能够自动在内存中创建自适应hash索引&＃xff0c;以加速读操作。
优点&＃xff1a;支持事务和崩溃修复能力&＃xff1b;引入了行级锁和外键约束。
缺点&＃xff1a;占用的数据空间相对较大。
适用场景&＃xff1a;需要事务支持&＃xff0c;并且有较高的并发读写频率。
MyISAM存储引擎
数据以紧密格式存储。对于只读数据&＃xff0c;或者表比较小、可以容忍修复操作&＃xff0c;可以使用MyISAM引擎。MyISAM会将表存储在两个文件中&＃xff0c;数据文件.MYD和索引文件.MYI。
优点&＃xff1a;访问速度快。
缺点&＃xff1a;MyISAM不支持事务和行级锁&＃xff0c;不支持崩溃后的安全恢复&＃xff0c;也不支持外键。
适用场景&＃xff1a;对事务完整性没有要求&＃xff1b;表的数据都会只读的。
MEMORY存储引擎
MEMORY引擎将数据全部放在内存中&＃xff0c;访问速度较快&＃xff0c;但是一旦系统奔溃的话&＃xff0c;数据都会丢失。
MEMORY引擎默认使用哈希索引&＃xff0c;将键的哈希值和指向数据行的指针保存在哈希索引中。
优点&＃xff1a;访问速度较快。
缺点&＃xff1a;
ARCHIVE存储引擎
ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能&＃xff0c;拥有高效的插入速度&＃xff0c;但是这种引擎不支持索引&＃xff0c;所以查询性能较差。
MyISAM 只有表级锁&＃xff0c;而InnoDB 支持行级锁和表级锁&＃xff0c;默认为行级锁。MyISAM 不提供事务支持。而InnoDB提供事务支持&＃xff0c;具有事务、回滚和崩溃修复能力。MyISAM不支持&＃xff0c;而InnoDB支持。MyISAM不支持&＃xff0c;InnoDB支持。应对高并发事务&＃xff0c;MVCC比单纯的加锁更高效。MyISAM不支持聚集索引&＃xff0c;InnoDB支持聚集索引。MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式&＃xff0c;进而实现并发控制。在查询的时候&＃xff0c;通过read view和版本链找到对应版本的数据。
作用&＃xff1a;提升并发性能。对于高并发场景&＃xff0c;MVCC比行级锁开销更小。
MVCC 实现原理如下&＃xff1a;
MVCC 的实现依赖于版本链&＃xff0c;版本链是通过表的三个隐藏字段实现。
DB_TRX_ID&＃xff1a;当前事务id&＃xff0c;通过事务id的大小判断事务的时间顺序。DB_ROLL_PRT&＃xff1a;回滚指针&＃xff0c;指向当前行记录的上一个版本&＃xff0c;通过这个指针将数据的多个版本连接在一起构成undo log版本链。DB_ROLL_ID&＃xff1a;主键&＃xff0c;如果数据表没有主键&＃xff0c;InnoDB会自动生成主键。每条表记录大概是这样的&＃xff1a;
使用事务更新行记录的时候&＃xff0c;就会生成版本链&＃xff0c;执行过程如下&＃xff1a;
undo log&＃xff0c;作为旧版本用于回滚&＃xff1b;下面举个例子方便大家理解。
1、初始数据如下&＃xff0c;其中DB_ROW_ID和DB_ROLL_PTR为空。
2、事务A对该行数据做了修改&＃xff0c;将age修改为12&＃xff0c;效果如下&＃xff1a;
3、之后事务B也对该行记录做了修改&＃xff0c;将age修改为8&＃xff0c;效果如下&＃xff1a;
4、此时undo log有两行记录&＃xff0c;并且通过回滚指针连在一起。
接下来了解下read view的概念。
read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时&＃xff0c;到t时间点拍的“照片”上取数据。
在read view内部维护一个活跃事务链表&＃xff0c;表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务&＃xff0c;不包含创建read view之后提交的事务。
不同隔离级别创建read view的时机不同。
read view的记录筛选方式
前提&＃xff1a;DATA_TRX_ID 表示每个数据行的最新的事务ID&＃xff1b;up_limit_id表示当前快照中的最先开始的事务&＃xff1b;low_limit_id表示当前快照中的最慢开始的事务&＃xff0c;即最后一个事务。
DATA_TRX_ID <up_limit_id&＃xff1a;说明在创建read view时&＃xff0c;修改该数据行的事务已提交&＃xff0c;该版本的记录可被当前事务读取到。DATA_TRX_ID >&＃61; low_limit_id&＃xff1a;说明当前版本的记录的事务是在创建read view之后生成的&＃xff0c;该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本&＃xff0c;然后重新判断该版本的记录对当前事务的可见性。up_limit_id <&＃61; DATA_TRX_ID <low_limit_i&＃xff1a;DATA_TRX_ID的值的事务。总结&＃xff1a;InnoDB 的MVCC是通过 read view 和版本链实现的&＃xff0c;版本链保存有历史版本记录&＃xff0c;通过read view 判断当前版本的数据是否可见&＃xff0c;如果不可见&＃xff0c;再从版本链中找到上一个版本&＃xff0c;继续进行判断&＃xff0c;直到找到一个可见的版本。
表记录有两种读取方式。
SELECT就是快照读。通过mvcc来进行并发控制的&＃xff0c;不用加锁。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。快照读情况下&＃xff0c;InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据&＃xff0c;这时如果两次查询中间有其它事务插入数据&＃xff0c;就会产生幻读。
下面举个例子说明下&＃xff1a;
1、首先&＃xff0c;user表只有两条记录&＃xff0c;具体如下&＃xff1a;
2、事务a和事务b同时开启事务start transaction&＃xff1b;
3、事务a插入数据然后提交&＃xff1b;
insert into user(user_name, user_password, user_mail, user_state) values(&＃39;tyson&＃39;, &＃39;a&＃39;, &＃39;a&＃39;, 0);
4、事务b执行全表的update&＃xff1b;
update user set user_name &＃61; &＃39;a&＃39;;
5、事务b然后执行查询&＃xff0c;查到了事务a中插入的数据。&＃xff08;下图左边是事务b&＃xff0c;右边是事务a。事务开始之前只有两条记录&＃xff0c;事务a插入一条数据之后&＃xff0c;事务b查询出来是三条数据&＃xff09;
以上就是当前读出现的幻读现象。
那么MySQL是如何避免幻读&＃xff1f;
mvcc来避免幻读。next-key来避免幻读&＃xff08;加行锁和间隙锁来实现的&＃xff09;。next-key包括两部分&＃xff1a;行锁和间隙锁。行锁是加在索引上的锁&＃xff0c;间隙锁是加在索引之间的。
Serializable隔离级别也可以避免幻读&＃xff0c;会锁住整张表&＃xff0c;并发性极低&＃xff0c;一般不会使用。
SELECT 的读取锁定主要分为两种方式&＃xff1a;共享锁和排他锁。
select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁
这两种方式主要的不同在于LOCK IN SHARE MODE多个事务同时更新同一个表单时很容易造成死锁。
申请排他锁的前提是&＃xff0c;没有线程对该结果集的任何行数据使用排它锁或者共享锁&＃xff0c;否则申请会受到阻塞。在进行事务操作时&＃xff0c;MySQL会对查询结果集的每行数据添加排它锁&＃xff0c;其他线程对这些数据的更改或删除操作会被阻塞&＃xff08;只能读操作&＃xff09;&＃xff0c;直到该语句的事务被commit语句或rollback语句结束为止。
SELECT... FOR UPDATE 使用注意事项&＃xff1a;
for update 仅适用于innodb&＃xff0c;且必须在事务范围内才能生效。like或者不等于&＃xff0c;主键字段产生表锁。某个表有近千万数据&＃xff0c;查询比较慢&＃xff0c;如何优化&＃xff1f;
当MySQL单表记录数过大时&＃xff0c;数据库的性能会明显下降&＃xff0c;一些常见的优化措施如下&＃xff1a;
MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 bin log&＃xff08;二进制日志&＃xff09;和 redo log&＃xff08;重做日志&＃xff09;和 undo log&＃xff08;回滚日志&＃xff09;。
bin log
bin log是MySQL数据库级别的文件&＃xff0c;记录对MySQL数据库执行修改的所有操作&＃xff0c;不会记录select和show语句&＃xff0c;主要用于恢复数据库和同步数据库。
redo log
redo log是innodb引擎级别&＃xff0c;用来记录innodb存储引擎的事务日志&＃xff0c;不管事务是否提交都会记录下来&＃xff0c;用于数据恢复。当数据库发生故障&＃xff0c;innoDB存储引擎会使用redo log恢复到发生故障前的时刻&＃xff0c;以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1&＃xff0c;那么在执行commit时会将redo log同步写到磁盘。
undo log
除了记录redo log外&＃xff0c;当进行数据修改时还会记录undo log&＃xff0c;undo log用于数据的撤回操作&＃xff0c;它保留了记录修改前的内容。通过undo log可以实现事务回滚&＃xff0c;并且可以根据undo log回溯到某个特定的版本的数据&＃xff0c;实现MVCC。
bin log会记录所有日志记录&＃xff0c;包括InnoDB、MyISAM等存储引擎的日志&＃xff1b;redo log只记录innoDB自身的事务日志。bin log只在事务提交前写入到磁盘&＃xff0c;一个事务只写一次&＃xff1b;而在事务进行过程&＃xff0c;会有redo log不断写入磁盘。bin log是逻辑日志&＃xff0c;记录的是SQL语句的原始逻辑&＃xff1b;redo log是物理日志&＃xff0c;记录的是在某个数据页上做了什么修改。MySQL主要分为 Server 层和存储引擎层&＃xff1a;
Server 层基本组件
当单表的数据量达到1000W或100G以后&＃xff0c;优化索引、添加从库等可能对数据库性能提升效果不明显&＃xff0c;此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担&＃xff0c;缩短查询的时间。
数据切分可以分为两种方式&＃xff1a;垂直划分和水平划分。
垂直划分
垂直划分数据库是根据业务进行划分&＃xff0c;例如购物场景&＃xff0c;可以将库中涉及商品、订单、用户的表分别划分出成一个库&＃xff0c;通过降低单库的大小来提高性能。同样的&＃xff0c;分表的情况就是将一个大表根据业务功能拆分成一个个子表&＃xff0c;例如商品基本信息和商品描述&＃xff0c;商品基本信息一般会展示在商品列表&＃xff0c;商品描述在商品详情页&＃xff0c;可以将商品基本信息和商品描述拆分成两张表。
优点&＃xff1a;行记录变小&＃xff0c;数据页可以存放更多记录&＃xff0c;在查询时减少I/O次数。
缺点&＃xff1a;
水平划分
水平划分是根据一定规则&＃xff0c;例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致&＃xff0c;但是数据得以拆分&＃xff0c;从而提升性能。
优点&＃xff1a;单库&＃xff08;表&＃xff09;的数据量得以减少&＃xff0c;提高性能&＃xff1b;切分出的表结构相同&＃xff0c;程序改动较少。
缺点&＃xff1a;
join性能差&＃xff0c;逻辑复杂分区表是一个独立的逻辑表&＃xff0c;但是底层由多个物理子表组成。
当查询条件的数据分布在某一个分区的时候&＃xff0c;查询引擎只会去某一个分区查询&＃xff0c;而不是遍历整个表。在管理层面&＃xff0c;如果需要删除某一个分区的数据&＃xff0c;只需要删除对应的分区即可。
按照范围分区。
CREATE TABLE test_range_partition(id INT auto_increment,createdate DATETIME,primary key (id,createdate)) PARTITION BY RANGE (TO_DAYS(createdate) ) (PARTITION p201801 VALUES LESS THAN ( TO_DAYS(&＃39;20180201&＃39;) ),PARTITION p201802 VALUES LESS THAN ( TO_DAYS(&＃39;20180301&＃39;) ),PARTITION p201803 VALUES LESS THAN ( TO_DAYS(&＃39;20180401&＃39;) ),PARTITION p201804 VALUES LESS THAN ( TO_DAYS(&＃39;20180501&＃39;) ),PARTITION p201805 VALUES LESS THAN ( TO_DAYS(&＃39;20180601&＃39;) ),PARTITION p201806 VALUES LESS THAN ( TO_DAYS(&＃39;20180701&＃39;) ),PARTITION p201807 VALUES LESS THAN ( TO_DAYS(&＃39;20180801&＃39;) ),PARTITION p201808 VALUES LESS THAN ( TO_DAYS(&＃39;20180901&＃39;) ),PARTITION p201809 VALUES LESS THAN ( TO_DAYS(&＃39;20181001&＃39;) ),PARTITION p201810 VALUES LESS THAN ( TO_DAYS(&＃39;20181101&＃39;) ),PARTITION p201811 VALUES LESS THAN ( TO_DAYS(&＃39;20181201&＃39;) ),PARTITION p201812 VALUES LESS THAN ( TO_DAYS(&＃39;20190101&＃39;) ));
在/var/lib/mysql/data/可以找到对应的数据文件&＃xff0c;每个分区表都有一个使用#分隔命名的表文件&＃xff1a;
-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt-rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...
list分区
对于List分区&＃xff0c;分区字段必须是已知的&＃xff0c;如果插入的字段不在分区时枚举值中&＃xff0c;将无法插入。
create table test_list_partiotion(id int auto_increment,data_type tinyint,primary key(id,data_type))partition by list(data_type)(partition p0 values in (0,1,2,3,4,5,6),partition p1 values in (7,8,9,10,11,12),partition p2 values in (13,14,15,16,17));
hash分区
可以将数据均匀地分布到预先定义的分区中。
create table test_hash_partiotion(id int auto_increment,create_date datetime,primary key(id,create_date))partition by hash(year(create_date)) partitions 10;
LOAD DATA INFILE和一次删除多行数据。查询语句的执行流程如下&＃xff1a;权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子&＃xff0c;查询语句如下&＃xff1a;
select * from user where id > 1 and name &＃61; &＃39;大彬&＃39;;
id > 1 还是 name &＃61; &＃39;大彬&＃39;&＃xff0c;优化器根据自己的优化算法选择执行效率最好的方案&＃xff1b;更新语句执行流程如下&＃xff1a;分析器、权限校验、执行器、引擎、redo log&＃xff08;prepare状态&＃xff09;、binlog、redo log&＃xff08;commit状态&＃xff09;
举个例子&＃xff0c;更新语句如下&＃xff1a;
update user set name &＃61; &＃39;大彬&＃39; where id &＃61; 1;
redo log&＃xff0c;此时redo log进入 prepare状态。binlog&＃xff0c;然后调用引擎接口&＃xff0c;提交redo log为commit状态。为什么记录完redo log&＃xff0c;不直接提交&＃xff0c;而是先进入prepare状态&＃xff1f;
假设先写redo log直接提交&＃xff0c;然后写binlog&＃xff0c;写完redo log后&＃xff0c;机器挂了&＃xff0c;binlog日志没有被写入&＃xff0c;那么机器重启后&＃xff0c;这台机器会通过redo log恢复数据&＃xff0c;但是这个时候binlog并没有记录该数据&＃xff0c;后续进行机器备份的时候&＃xff0c;就会丢失这一条数据&＃xff0c;同时主从同步也会丢失这一条数据。
exists用于对外表记录做筛选。exists会遍历外表&＃xff0c;将外查询表的每一行&＃xff0c;代入内查询进行判断。当exists里的条件语句能够返回记录行时&＃xff0c;条件就为真&＃xff0c;返回外表当前记录。反之如果exists里的条件语句不能返回记录行&＃xff0c;条件为假&＃xff0c;则外表当前记录被丢弃。
select a.* from A awhere exists(select 1 from B b where a.id&＃61;b.id)
in是先把后边的语句查出来放到临时表中&＃xff0c;然后遍历临时表&＃xff0c;将临时表的每一行&＃xff0c;代入外查询去查找。
select * from Awhere id in(select id from B)
子查询的表比较大的时候&＃xff0c;使用exists可以有效减少总的循环次数来提升速度&＃xff1b;当外查询的表比较大的时候&＃xff0c;使用in可以有效减少对外查询表循环遍历来提升速度。
int(10)中的10表示的是显示数据的长度&＃xff0c;而char(10)表示的是存储数据的长度。
相同点&＃xff1a;
truncate和不带where子句的delete、以及drop都会删除表内的数据。drop、truncate都是DDL语句&＃xff08;数据定义语言&＃xff09;&＃xff0c;执行后会自动提交。不同点&＃xff1a;
where子句作用于表和视图&＃xff0c;having作用于组。where在数据分组前进行过滤&＃xff0c;having在数据分组后进行过滤。主从同步使得数据可以从一个数据库服务器复制到其他服务器上&＃xff0c;在复制数据时&＃xff0c;一个服务器充当主服务器&＃xff08;master&＃xff09;&＃xff0c;其余的服务器充当从服务器&＃xff08;slave&＃xff09;。
因为复制是异步进行的&＃xff0c;所以从服务器不需要一直连接着主服务器&＃xff0c;从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件&＃xff0c;可以指定复制所有的数据库&＃xff0c;某个数据库&＃xff0c;甚至是某个数据库上的某个表。
数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。
version字段&＃xff0c;在修改提交之前检查version与原来取到的version值是否相等&＃xff0c;若相等&＃xff0c;表示数据没有被修改&＃xff0c;可以更新&＃xff0c;否则&＃xff0c;数据为脏数据&＃xff0c;不能更新。实现方式&＃xff1a;乐观锁一般使用版本号机制或CAS算法实现。show processlist 或 show full processlist 可以查看当前 MySQL 是否有压力&＃xff0c;正在运行的SQL&＃xff0c;有没有慢SQL正在执行。返回参数如下&＃xff1a;
id&＃xff1a;线程ID&＃xff0c;可以用kill id杀死某个线程
db&＃xff1a;数据库名称
user&＃xff1a;数据库用户
host&＃xff1a;数据库实例的IP
command&＃xff1a;当前执行的命令&＃xff0c;比如Sleep&＃xff0c;Query&＃xff0c;Connect等
time&＃xff1a;消耗时间&＃xff0c;单位秒
state
&＃xff1a;执行状态&＃xff0c;主要有以下状态&＃xff1a;
SELECT查询的记录&＃xff0c;同时把结果发送给客户端kill语句&＃xff0c;杀死指定线程GROUP BY做排序ORDER BY做排序info&＃xff1a;正在执行的SQL语句
![[大整数乘法] java代码实现](https://img1.php1.cn/3cd4a/24c6f/9f3/0133bb25da242824.jpeg)
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有