数据库 - Aminute/aminute GitHub Wiki

[toc]

数据库索引

数据库索引

分库分表

分库分表 分库分表

类型	优点	缺点
垂直分库
垂直分表
水平分库
水平分表

数据库基础知识

基本语句

//插入语句
INSERT INTO table_name(column1,column2,column3) VALUES(value1,value2,value3);
//删除语句
DELETE FROM table_name WHERE some_column=some_value;
//更改语句
UPDATE table_name SET column1=value1,column2=value2 WHERE some_column=some_value;
//查找语句
SELECT column_name,column_name FROM table_name WHERE column_name operator value;
//创建索引
CREATE INDEX indexName ON mytable(username(length)); 
//事务处理
		BEGIN 开始一个事务
		ROLLBACK 事务回滚
		COMMIT 事务确认
begin;  # 开始事务
insert into runoob_transaction_test value(5);
insert into runoob_transaction_test value(6);
commit; # 提交事务
//limit代表记录条数，offset代表开始位置
select * from employees order by hire_date desc limit 1  OFFSET 2
//连接规范
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据
//limit的用法
LIMIT m,n : 表示	从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写
//offset的用法
limit y //分句表示: 读取 y 条数据
limit x, y //分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x //分句表示: 跳过 x 条数据，读取 y 条数据 9

//count的用法
select count(*) from `user`    //count(*)是对行数目进行计数
select count(name) from `user`   //count(column_name)是对列中不为null的行进行计数
select count(password) from `user`

//distinct的用法
select distinct name from user   //返回不重复的字段
select count(distinct id) name from user  //返回不重复的字段的总数

//内连接—取出两张表中匹配到的数据，匹配不到的不保留
select * from users as u inner join topics as t on u.id=t.user_id
//上面的语句等价于
select * from users,topics where users.id=topics.user_id
//外连接—取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL
//左外连接
select * from users as u left join topics as t on u.id=t.user_id;
//右外连接(和上面的左外连接等价)
select * from topics as t right join users as u on u.id=t.user_id;

//having 的使用
https://www.cnblogs.com/mr-wuxiansheng/p/11188733.html
SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region HAVING SUM(area)>1000000
//我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。相反，having子句可以让我们筛选分组后的各组数据，having单独使用时和where功能相同

最左匹配原则

最左匹配原则就是指在联合索引中，如果 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配；

数据库三范式

• 1NF:字段不可分;
• 2NF:有主键，非主键字段依赖主键;
• 3NF:非主键字段不能相互依赖

事务

• 一个数据库事务通常包含对数据库进行读或写的一个操作序列

• 目的：

• 1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。 2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务的四大特性

原子性

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响

一致性

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态

隔离性

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

持久性

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

事务的隔离性

脏读

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据

不可重复读

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了

虚读（幻读）

幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

　　幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）

mysql提供的四种隔离级别

MySQL数据库为我们提供的四种隔离级别：

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ Read committed (读已提交)：可避免脏读的发生。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证 　　

乐观锁和悲观锁的区别

博文连接

乐观锁

相信数据之间的竞争概率很小，所以不会产生任何锁或者死锁

• 步骤：读取、校验、写入
• 乐观锁的实现方式：记录版本数据。

悲观锁

遵循先取锁再访问的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数 据，其他事务就必须等待该事务处理完才可以处理那行数

主键

• 能够唯一标识表中某一行的属性或属性组，一个表中只能有一个主键
• 作用：1、唯一标识一行。2、作为一个可以被外键有效引用的对象
• 和索引的区别：
  • 主键是逻辑键，不实际存在；索引是物理键
  • 主键不能为空值；索引可以为空值
  • 主键唯一；索引可以不唯一
  • 主键一定是唯一性索引，唯一性索引不一定是主键

索引

• 高效获取数据的排好序的数据结构（二叉树、红黑树、HASH表、B-tree、B+树）

  hash虽然效率很高，但不支持范围和模糊查找

  B树的范围查找也没有B+树的效率高，因为其叶子节点之间没有指针相连

• mysql默认设置16kb

• MyISAM存储引擎索引实现

  MyISAM索引文件和数据文件是分离的（非聚集），先去MYI中查找存储地址，找到地址后去MYD中查找数据

  • .frm文件存储的是表结构
  • .MYD文件存储的是数据
  • .MYI文件存储的是索引

• InnoDB存储引擎索引实现（聚集）

  表数据文件本身就是按照B+树组织的一个索引结构文件

  聚集索引-叶节点包含了完整的数据记录

  • .frm文件存储的是表结构
  • .ibd文件存储的是索引+数据

• 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键

  自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

• 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

• 建立联合索引的时候要遵循最左前缀法则

mysql索引分类

index ——普通索引,数据可以重复，没有任何限制。 　　unique——唯一索引,要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。

primary key——主键索引,是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建主键索引。

组合索引——在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

fulltext ——全文索引,是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。

Innodb和Myisam的区别

• Innodb是聚集索引，Myisam是非聚集索引
• Innodb支持事务，Myisam不支持事务。对于InnoDB每一条SQL语言都默认封装成事务
• Innodb支持外键，Myisam不支持外键。
• Innodb必须要有主键，不能没有主键
• Innodb支持表、行级锁，Myisam支持表级锁(给整个表加锁)

聚集索引和非聚集索引

聚集索引和非聚集索引的总结

聚集索引以及非聚集索引用的是B+树索引

聚集索引

• 创建语句

//sql server
create clustered index clustered_index on table_name(colum_name)
//mysql mysql默认主键就位聚集索引
alter table table_name add primary key(colum_name)

• 本质 ：数据行的物理顺序与列值的顺序相同

非聚集索引

• 定义：索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引 ，其实除了聚集索引外的其他索引都是非聚集索引
• 二次索引： 使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列 ，就需要进行二次索引
• 解决二次索引的方式：建立复合索引

总结

• 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
• 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
• 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。

mysql优化

• Mysql选用B+树这种数据结构作为索引，

  可以提高查询索引时的磁盘IO效率

  可以提高范围查询的效率

  B+树里的元素也是有序的

• B树和B+树的区别

  • B+树中非叶子节点中的关键字只用来索引，不保存数据，所有数据保存在叶子节点中
  • 叶子结点本身依关键字的大小自小而大顺序链接
  • 同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素

• B+树相对于B树的查询优势

  • B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
  • B+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）
  • 对于范围查找来说，B+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

结构优化

• 知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅索引都引用主索引，过长的主索引会令辅索引变得过大。
• 用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

查询优化

1、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

3、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

5、in 和 not in 也要慎用，否则会导致全表扫描

6、like模糊全匹配也将导致全表扫描