数据库

索引

原理

索引就是一个数据结构，把表中的记录用一个合适高效查找的数据结构来表示，目的就是让查询变得更高效
在mysql中是用最广泛的数据引擎是InnoDB，里面用的是B+树索引
B+树

B+树

特点

1.非叶子节点只存储键值和指针。
2.所有叶子节点之间都有一个链指针。
3.数据记录都存放在叶子节点中。
在B+树中因为叶子节点的键值是按顺序排列的所以进行键值的范围查找效率非常高。
在B+树中由于一个节点存储了更多的键值和指针，所以同样多的内容可以降低树的高度，减少磁盘io次数，从而提高效率。

优点

索引的优点是可以提高检索数据的速度

缺点

建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。
更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性——这就如同图书馆要有专门的位置来摆放索引柜，并且每当库存图书发生变化时都需要有人将索引卡片重整以保持索引与库存的一致。

分类

主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）

主键索引

create table sutdent1 (gradeID int(11) auto_increment primary key)

####### 特性
最常见的索引
确保数据记录的唯一性
确保特定数据记录在数据库中的位置
它是一种特殊的唯一索引，不允许有空值。
######## 复合主键
指表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为主键

create table test
(
   name varchar(19),
   id number,
   value varchar(10),
   primary key (name,id)
)

name和id字段组合起来就是test表的复合主键 ,它的出现是因为name字段可能会出现重名，所以要加上id字段，这样就可以保证记录的唯一性 ,一般情况下，主键的字段长度和字段数目越少越好。

当表中只有一个主键时，它是唯一的索引；当表中有多个主键时，称为复合主键，复合主键联合保证唯一索引

######## 联合主键
多个主键联合形成一个主键组合

举例：主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同，联合就在于主键A跟主键B形成的联合主键是唯一的。
下例主键A数据是1，主键B数据也是1，联合主键其实是11，11是唯一值，不允许再出现11这个值(即为多对多关系)
主键A数据主键B数据
1　　　　　 1
2　　　　　 2
3　　　　　 3
主键A与主键B的联合主键值最多为： 11、 12、 13、 21、 22、 23、 31、 32、 33。

唯一索引

create table student1  (gradeID int(11) auto_increment primary key,gradename varchar(20) not null unique)

####### 特性
避免同一个表中某数据列中的值重复
一个表中可以有多个唯一索引，只能有一个主键索引
索引列的值必须唯一，但允许有空值

常规索引

快速定位特定数据

create table student1 (id int,index index_tts_id(id))

全文索引

只能用于MyISAM类型的数据表
只能用于char，varchar，text数据列类型
使用大型数据集

单一索引和复合索引

单一索引一个索引只包含单个列。一个表可以有多个单列索引，但这不是组合索引
复合索引(组合索引) 一个索引包含多个列
create index idx1 on table1(col1)

复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。
create index idx1 on table1(col1,col2,col3)

窄索引和宽索引
窄索引是指索引列为1-2列的索引,如果不特殊说明的话一般是指单一索引。
宽索引也就是索引列超过2列的索引。

设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。
拥有更多的窄索引，将给优化程序提供更多的选择余地，这通常有助于提高性能

在where条件中字段用索引，如果用多字段就用复合索引
select * from table1 where col1= A and col2= B and col3 = C
在select的字段不要建什么索引(如果是要查询select col1 ,col2, col3 from mytable,就不需要上面的索引了)
根据where条件建索引是极其重要的一个原则

虽然索引提高了查询速度，同时也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。

最左匹配原则

对于复合索引，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高。如：
IDX1:create index idx1 on table1(col2,col3,col5)
select * from table1 where col2=A and col3=B and col5=D
如果是"select * from table1 where col3=B and col2=A and col5=D"
或者是"select * from table1 where col3=B"将不会使用索引，或者效果不明显

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

比如where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)顺序的索引，d是用不到索引的,因为c出现了“>”。
如果建立(a,b,d,c)的索引则where a = 1 and b = 2 and c > 3 and d = 4 中abcd都可以用到索引，并且a,b,d的顺序可以任意调整
即：select * from test where b=2 and d=4 and a=1and c>3中a,b,c,d也都用到了索引。

组合索引效率高于多个列索引，因为在执行多个列索引的时候，mysql只会选择其中一个效率最好的，但是通过组合索引就直接锁定那一条信息了

聚集索引和非聚集索引

数据库的索引分为聚集索引和非聚集索引

聚集索引表中的数据按主键的顺序存放，它实际上就是按主键构建的一个B+树，叶子节点存放的是数据行记录。所以数据库中的数据实际上是索引的一部分。由于实际的数据页只能按照一个顺序存放，所以每张表聚集索引只能有一个。

非聚集索引的叶子节点中存放的是键值和主键值，所以通过非聚集索引需要先查找到主键值然后通过聚集索引查询到具体的数据，因此非聚集索引的效率要低于聚集索引。
非聚集索引并不会影响到数据的存储顺序，所以非聚集索引可以存在多个。

连接

左外连接：select … from table(left) left join table(right) on …，以table(left)为基准，将table(left)的所有数据展出，table(right)有对应数据则显示，没有则显示NULL。
右外连接：select … from table(left) right join table(right) on …，以table(right)为基准，将table(right)的所有数据展出，table(left)有对应数据则显示，没有则显示NULL。
内连接：select … from table(left) inner join table(right) on …，将table(left)和table(right)共有的数据（左右都不为NULL）展出。
全连接:全外连接是左外连接和右外连接的组合。简单说就是将左外连接和右外连接同时做多一次。做在mysql中没有全连接运算，但是根据全连接的定义，我们可以写成左外连接和右外连接组合起来。
union 和union all：union默认去除重复数据 union distinct union不去除重复数据

image

笛卡尔积：对于A中的每一个元素，都有对于在B中的所有元素做连接运算。可以见得对于两个元组分别为m，n的表。笛卡尔积后得到的元组个数为m x n个元组。而对于mysql来说，默认的连接就是笛卡尔积

导致数据库性能差的原因有哪些

硬件环境问题，如磁盘IO
查询语句问题，如join、子查询、没建索引
索引失效，建了索引，查询的时候没用上
查询关联了太多的join
服务器关联缓存，线程数等
表中存在冗余字段，在生成笛卡尔积时耗费多余的时间

ACID事务的四大特性

数据库事务是指一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中

四种隔离

数据库管理系统采用锁机制来实现事务的隔离性，当多个事务同时更新数据库中的相同数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据库。

隔离级别对比表

读未提交：就是一个事务可以读取另一个未提交事务的数据。这种隔离级别的一致性是最差的，可能会产生“脏读”、“不可重复读”、“幻读”。如无特殊情况，基本是不会使用这种隔离级别的。读提交，能解决脏读问题
读提交：就是只能读到已经提交了的内容；有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读，这是各种系统中最常用的一种隔离级别，也是SQL Server和Oracle的默认隔离级别。这种隔离级别能够有效的避免脏读
可重复读：专门针对“不可重复读”这种情况而制定的隔离级别，自然，它就可以有效的避免“不可重复读”。而它也是MySql的默认隔离级别
序列化 Serializable：这是数据库最高的隔离级别，这种级别下，事务“串行化顺序执行”，也就是一个一个排队执行。这种级别下，“脏读”、“不可重复读”、“幻读”都可以被避免，但是执行效率奇差，性能开销也最大，所以基本没人会用
读数据的概念
在关系型数据库中，事务的隔离性分为四个隔离级别，先介绍几个关于读数据的概念
脏读：所谓脏读就是对脏数据的读取，而脏数据所指的就是未提交的数据。也就是说，一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是出于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系，这种现象被称为脏读。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可复读。也就是说，这个事务在两次读取之间该数据被其他事务所修改
幻读：一个事务按相同的查询条件重新读取以前检索过得数据，却发现其他事务插入了满足其查询条件的新数据，这个现象就被称为幻读
串行读：完全的串行话读，所有SELECT语句都被隐式的转换成SELECT...LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会堵塞，隔离级别最高。

锁

锁：多个用户同时对数据库并发操作时，会带来数据不一致的问题，锁主要用于多用户环境下保证数据库完整性和一致性
出现目的：处理并发问题

分类（按使用性质）：

共享锁（Share Lock）

S锁，也叫读锁，用于所有的只读数据操作。非独占的，允许多个并发事务读取其锁定的资源
性质：

多个事务可封锁同一个共享页
任何事务都不能修改该页
通常是该页被读取完毕，S锁立即被释放
例子：select * from table 首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定第二页，允许在读操作过程中，修改未被锁定的第一页

排它锁（Exclusive Lock）

X锁，也叫写锁。对数据进行写操作，若一个事务对对象加了排它锁，其他事务就不能再给他加任何锁了
性质：

仅运行一个事务封锁此页
其他任何事务必须等到X锁被释放才能对该页进行访问
X锁一直到事务结束才能被释放
例子：select * from table for update；

更新锁

U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，避免使用共享锁造成的死锁现象
性质：

用来预定要对此页施加X锁，允许其他事务读，但不允许再施加U锁或X锁
当被读取的页要被更新时，则升级为X锁
8 U锁一直到事务结束时才能被释放

分类（作用范围分）：

行锁

作用范围是行级别

表锁

作用范围是整张表
在能够确定哪些行需要锁的情况下使用行锁，不知道影响哪些行时用表锁

乐观锁

每次去拿数据的时候都认为别人不会修改数据，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制
适用场景：多读的场景，可以提高吞吐量
实现：
1、版本号
为数据增加一个版本标识，基于数据库表的版本解决方案：为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号+1，此时，将提交数据的版本数据与数据库表中对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据
2、时间戳
和版本号基本一样，只是通过时间戳来判断，使用数据库服务器的时间戳
3、待更新字段（旧系统不能修改数据库表结构）
和版本号相似，不增加额外字段，直接使用有效数据字段做版本控制信息，假设有个待更新字段叫count，先去读取这个count，更新的时候去比较数据库中的count值是不是开始读的值，如果是就更新count值，否则更新失败
4、所有字段
和更新字段类似，只是所有使用字段做版本控制信息，只有所有字段都没变化才会执行更新
区别：
新系统设计可以使用version方式和timestamp方式，需要增加字段，应用范围是整条数据，不论修改哪个字段都会更新version，因此两个事务更新同一条记录的两个不相关字段也是互斥的，不能同步进行。
旧系统不能修改数据库表结构的时候使用数据字段作为版本控制信息，不需要新增字段，带更新字段只要其他事物修改的字段和当前事物修改的字段没有重叠就可以同步进行，并发性更高。

并发控件造成两种锁

相互等待而导致

活锁

定义：T1封锁数据R，T2同时也请求封锁数据R,T3也请求封锁数据R，当T1释放锁之后，T3会锁住R，T4也请求封锁R，T2就会一直等待下去
解决方法：先到先服务策略

死锁

定义：你等我，我等你，双方就会一直等待下去，T1封锁数据R1，请求对R2封锁，T2封锁了R2，请求封锁R1，就会导致死锁
解决方法：没有完全解决方法，只能尽量预防
预防方法：
1、一次性封锁：一次性将所需数据全部锁住，扩大封锁范围，降低系统的并发度
2、顺序封锁：先对数据对象指定一个封锁顺序，要对数据进行封锁，只能按照规定顺序来封锁
判定是否死锁：
超时法:某个事物的等待时间超过指定时限，则判定为出现死锁
等待图法：事物等待图中出现了回路，判断出现了死锁
死锁解决方法：撤销一个处理死锁代价最小的事物，释放此事物持有的所有锁，同时对撤销的事务所执行的数据修改操作必须加以恢复

InnoDB和MyISAM

InnoDB和MyISAM都是MySQL的存储引擎

区别

InnoDB支持事务 MyISAM不支持事务
InnoDB支持外键 MyISAM不支持外键
InnoDB是聚集索引 MyISAM是非聚集索引

聚集索引的文件存放在主键索引的叶子节点上，因此InnoDB必须要用主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，再通过主键查询到数据，因此主键不应该过大，因为主键过大，其他索引也都会很大。不支持全文索引
MySIAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。查询效率更高，支持全文索引

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。
MySIAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度快。

InnoDB最小的锁粒度是行锁，MySIAM最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

如何选择
是否需要支持事务，若要选择InnoDB，不要可考虑MyISAM
表中绝大部分是读查询，可以考虑MyISAM，读写频繁，使用InnoDB
系统崩溃后，MyISAM恢复起来更困难

实际使用

两个表查找出语文成绩最高的人的姓名

select name from student where score =(select max(score) from student where class='语文')

根据city分类，人数大于2的城市