数据库索引

索引是存储引擎快速找到记录的一种数据结构。
添加索引原则：
在查询中很少使用或者参考的列不应该创建索引。

只有很少数据值的列也不应该增加索引。
定义为text、image和bit数据类型的列不应该增加索引。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构
B-Tree
B-Tree是一种平衡的多路查找(又称排序)树，在文件系统中和数据库系统中有所应用。主要用作文件的索引。其中的B就表示平衡(Balance) 。
定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：1.d>=2，即B-Tree的度；
2.h为B-Tree的高；
3.每个非叶子结点由n-1个key和n个指针组成，其中d<=n<=2d；
4.每个叶子结点至少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶结点的指针均为NULL；
5.所有叶结点都在同一层，深度等于树高h；
6.key和指针相互间隔，结点两端是指针；
7.一个结点中的key从左至右非递减排列；
8.如果某个指针在结点node最左边且不为null，则其指向结点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。
9.如果某个指针在结点node最右边且不为null，则其指向结点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。
10.如果某个指针在结点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向结点的所有key小于v(keyi+1)且大于v(keyi)。
一个d=2的B-Tree示意图:
在这里插入图片描述
B-Tree是一个非常有效率的索引数据结构。这主要得益于B-Tree的度可以非常大，高度会变的非常小，只需要二分几次就可以找到数据。例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2))，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。
索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
B+Tree的特性
区别于B-Tree:
每个节点的指针上限为2d而不是2d+1
内节点不存储data，只存储key；叶子节点不存储指针
带有顺序访问指针的B+Tree

数据库添加索引

1.普通索引：
仅加速查询最基本的索引，没有任何限制，是大多数情况下使用到的索引。CREATE INDEX index_name on user_info(name)

#创建表的时候同时创建索引：

create table healerjean (
  id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(32) NOT NULL COMMENT '姓名',
  email VARCHAR(64) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) COMMENT  = '索引测试表';

#在存在的表上创建索引

1	create index index_name on healerjean(name)

#对于创建索引时如果是blob 和 text 类型，必须指定length。

1 2	create index ix_extra on in1(message(200)); alter table employee add index emp_name (name);

2.唯一索引：
与普通索引类型不同的是：加速查询 + 列值唯一（可以有null）CREATE UNIQUE INDEX mail on user_info(name)
3.全文索引：
全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。
4.组合索引：
将几个列作为一条索引进行检索，使用最左匹配原则。
create index ind_sales2_companyid_moneys on sales2(company_id,moneys)

想要查看表有几个字段设置了索引, show index from healerjean

MySQL索引实现

MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址。
在这里插入图片描述
MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的。
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
在这里插入图片描述
InnoDB索引实现
InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

叶结点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。
在这里插入图片描述

数据库事务：底层如何实现事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
一个数据库事务通常包含了一个序列的对数据库的读/写操作。作用：
1.为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
2.当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
当事务被提交给了DBMS（数据库管理系统），则DBMS（数据库管理系统）需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态;同时，该事务对数据库或者其他事务的执行无影响。
一个逻辑工作单元要成为事务，必须满足ACID（原子性、一致性、隔离性和持久性） 属性。
原子性（Atomicity）：
事务作为一个整体被执行，事物的所有操作要不成功，要不失败回滚而回滚可以通过日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作。
一致性（Consistency）：
数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。
隔离性（Isolation）：
多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：
一旦事务提交，则其所做的修改将会永远保存到数据库中。

数据库事务隔离

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

MySQL 4 种隔离级别

未提交读READ UNCOMMITTED：
一个事务在提交之前，对其他事务是可见的，即事务可以读取未提交的数据。存在“脏读”（读到了脏数据）问题；
提交读READ COMMITTED：
事务在提交之前，对其它事务是不可见的。存在“不可重复读”（两次查询的得到的结果可能不同，即可能在查询的间隙，有事务提交了修改）问题。解决了“脏读”问题。
可重复读REPEATABLE READ：
在同一事务中多次读取的数据是一致的。解决了脏读和不可重复读问题，存在“幻读”（在事务两次查询间隙，有其他事务又插入或删除了新的记录）。— MySQL默认隔离级别。 可串行化SERIALIZABLE：
强制事务串行化执行。即一个事物一个事物挨个来执行，可以解决上述所有问题。

mysql的MVCC机制

MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。MVCC是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的创建版本号和删除版本号，然后Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

SQL优化

在经常性的检索列上，建立必要索引，以加快搜索速率，避免全表扫描（索引覆盖扫描）；
多次查询同样的数据，可以考虑缓存该组数据；
审视select * form tables, 你需要所有列数据吗？
切分查询（大查询切分成为小查询，避免一次性锁住大量数据）
分解关联查询（单表查询，结果在应用程序中进行关联，可以减少处理过程中的锁争用）
尽量先做单表查询；

inner join和left join

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
表A、 B记录如下：
| aID　 | 　aNum | |bID | |
|–|–|-|–|–|
| 1 | a20050111 | | 1| 2006032401|
| 2 | a20050112|| 2|2006032402 |
| 3 | a20050113 || 3|2006032403 |
| 4 | a20050114|| 4| 2006032404|
| 5 | a20050115| |8 | 2006032408|
sql语句如下:

1
2
3

select * from A
left join B 
on A.aID = B.bID

结果如下:
| aID　 | 　aNum | |bID | |
|–|–|-|–|–|
| 1 | a20050111 | | 1| 2006032401|
| 2 | a20050112|| 2|2006032402 |
| 3 | a20050113 || 3|2006032403 |
| 4 | a20050114|| 4| 2006032404|
| 5 | a20050115| |NULL | NULL|

数据库引擎innoDB与MyISAM

数据库引擎是用于存储、处理和保护数据的核心服务。在Mysql数据库中，常用的引擎为innoDB和MyISAM（MySQL5.6版本开始Innodb已经成为Mysql的默认引擎，之前是MyISAM）。

数据库引擎 innoDB与MyIASM区别：
1.InnoDB支持事务，支持外键，MyISAM都不支持;
2.InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的;

行数保存： InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
Innodb不支持全文索引，MyISAM支持全文索引，查询效率上MyISAM要高于Innodb；

如何选择存储引擎
如果在应用中执行大量select(查询)操作，应该选MyIASM，如果在应用中执行大量的insert（插入）和update（更新）操作，应该选择innoDB
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

#查看数据库支持的存储引擎
show engines

#查看数据库当前使用的存储引擎,就是默认引擎是什么。
show variables like 'storage_engine'
也可以在MySQL配置文件中查看。
windows - my.ini Linux - my.cnf

#查看数据库表所用的存储引擎
show create table table_name

#创建表指定存储引擎
create table table_name (column_name column_type) engine = engine_name

#修改表的存储引擎
alter table table_name engine=engine_name

#修改默认的存储引擎
在MySQL配置文件中修改下述内容：default-storage-engine=INNODB
MySQL的端口号是多少，如何修改这个端口号
查看端口号:
登录mysql: mysql -u root –p
查看端口号: show global variables like 'port'
mysql的默认端口是3306。（补充：sqlserver默认端口号为：1433；oracle默认端口号为：1521；DB2默认端口号为：5000；PostgreSQL默认端口号为：5432）
修改端口号：编辑/etc/my.cnf文件，早期版本有可能是my.conf文件名，增加端口参数，并且设定端口，注意该端口未被使用，保存退出。重启mysql。

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
目的：把处理封装，简化复杂的操作；所有开发人员和应用程序都使用同一存储过程，防止错误保证了数据的一致性；简化对变动的管理，只需要更改存储过程的代码。

#创建存储过程

create procedure porcedureName () 
begin 
    select name from user; 
end;

#调用存储过程：

1	call procedureName ();

#删除存储过程

1	DROP PROCEDURE IF EXISTS porcedureName;

#含参数

create procedure getTotalById ( 
    in userId int, 
    out total decimal(8,2) 
) 
BEGIN 
    select SUM(r.price) from order r 
    where r.u_id = userId 
    into total; 
END;

调用

1 2	call getTotalById(1, @total); select @total;

数据库的第一二三范式

第一范式（1NF）
数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
通俗讲就是：一个字段只存储一项信息。
第二范式
如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式（属性完全依赖于每一个主键）。满足第一范式前提，当一个主键由多个属性共同组成时，才会发生不符合第二范式的情况。比如有两个属性的主键，不能存在这样的属性，它只依赖于主键中的一个属性，这就是不符合第二范式。
非主键列是否完全依赖于主键，还是依赖于主键的一部分？
第三范式
定义：第三范式（属性不能传递依赖于主属性）。满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。
非主键列是直接依赖于主键，还是直接依赖于非主键列？

delete、truncate、drop的区别及应用场所

1、delete属于DML（数据库操作语言），DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作，truncate、drop属于DDL（数据库定义语言），不能回滚
2、在速度上，一般来说，drop> truncate > delete
3、TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）
4、Truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
实例：删除学生表
1、当你不再需要该表时，用 drop;
2、当你仍要保留该表，但要删除所有记录时，用 truncate;
3、当你要删除部分记录时，用 delete。
drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;
truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;
delete from table_name :删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;
delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;

DELETE FROM users WHERE name = ‘Mike’ ORDER BY id DESC LIMIT 6;

Redis数据类型

Redis通常被称为数据结构服务器
Redis支持五种数据类型：
1）String: 字符串：整数值、embstr编码的简单动态字符串、简单动态字符串（SDS)
2）List: 列表：压缩列表、双端链表
3）Hash: 哈希：压缩列表、字典
4）Set: 集合：整数集合、字典
5）Sorted Set: 有序集合：压缩列表、跳跃表和字典

mongodb和redis的区别

内存管理机制上： Redis 数据全部存在内存，定期写入磁盘，当内存不够时，可以选择指定的 LRU 算法删除数据。MongoDB 数据存在内存，由 linux系统 mmap 实现，当内存不够时，只将热点数据放入内存，其他数据存在磁盘。
支持的数据结构上： Redis 支持的数据结构丰富，包括hash、set、list等。
MongoDB 数据结构比较单一，但是支持丰富的数据表达，索引，最类似关系型数据库，支持的查询语言非常丰富

Redis是单线程的，但是为什么这么高效呢?

I/O多路复用监听多个套接字
完全基于内存，内存操作快速
单进程单线程不用考虑切换/锁的问题

单进程多线程模型：MySQL、Memcached、Oracle（win）
多进程模型：Oracle（Linux）

Redis的rehash怎么做的，为什么要渐进rehash，渐进rehash又是怎么实现的?

因为redis是单线程，当K很多时，如果一次性将键值对全部rehash，庞大的计算量会影响服务器性能，甚至可能会导致服务器在一段时间内停止服务。不可能一步完成整个rehash操作，所以redis是分多次、渐进式的rehash。渐进性哈希分为两种：
1）操作redis时，额外做一步rehash
对redis做读取、插入、删除等操作时，会把位于table[dict->rehashidx]位置的链表移动到新的dictht中，然后把rehashidx做加一操作，移动到后面一个槽位。
2）后台定时任务调用rehash
后台定时任务rehash调用链，同时可以通过server.hz控制rehash调用频率