Mysql、索引
索引
数据库中的查询操作非常普遍，索引就是提升查找速度的一种手段
索引的类型
从数据结构角度分
1.B+索引：
传统意义上的索引，最常用最普遍的索引
2.hash索引：
hash索引是一种自适应的索引，数据库会根据表的使用情况自动生成hash索引，人为无法干预
3.全文索引：
用于实现关键词搜索，但它只能根据空格分词，因此不支持中文，可以使用lucene实现搜索功能
4.RTree索引：
在mysql很少使用，仅支持geometry数据类型；相对于BTREE，RTREE的优势在于范围
物理存储角度
数据库以页为存储单位，一个页有8K（8192byte），一页存放N条记录
在B+树中分为数据页和索引页
B+树的高一般为2-4层，因为查找某一键值的记录只需要2-4次I/O，效率较高
1.聚集索引（也叫聚簇索引）
2.非聚集索引
好文：
[https://www.cnblogs.com/s-b-b/p/8334593.html]
不管是聚集索引还是非聚集索引他们的结构都是B+树，他们的唯一区别是
聚集索引的数据页存放的是完整的记录，也就是说，聚集索引决定了表的物理存储顺序
非聚集索引的数据页中存放的是指向记录的地址信息，他真正的数据已经在聚集索引中存储了
1、聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
2、聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引，聚集索引比非聚集索引有更快的访问速度
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
术语“聚簇”表示数据行和相邻的键值紧密地存储在一起，InnoDB 的聚簇索引的数据行存放在 B+Tree 的叶子页中。
因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
优点
- 可以把相关数据保存在一起，减少 I/O 操作；
- 因为数据保存在 B+Tree 中，因此数据访问更快。
缺点 - 聚簇索引最大限度提高了 I/O 密集型应用的性能，但是如果数据全部放在内存，就没必要用聚簇索引。
- 插入速度严重依赖于插入顺序，按主键的顺序插入是最快的。
- 更新操作代价很高，因为每个被更新的行都会移动到新的位置。
- 当插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，页分裂会导致表占用更多的磁盘空间。
- 如果行比较稀疏，或者由于页分裂导致数据存储不连续时，聚簇索引可能导致全表扫描速度变慢。
逻辑角度
1.普通索引：索引值不唯一
2.唯一索引：唯一索引是不允许任意两行具有相同索引值的索引，当现有数据库中存在重复键值的时候，大多数数据库不允许将唯一索引和数据库表相关联，当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
3.主键索引：数据库中经常有一列或几列的组合，其值能唯一标识表中的每一行，该列称为主键。在数据库关系图中表为主键自动创建主键索引，主键索引是唯一索引的特定类型，该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
4.空间索引
主键和唯一索引的区别：
1.一个表可以有多个唯一索引，而主键只能有一个
2.主键可以作为其它表的外键
3.主键不可以为null，而唯一索引可以为null
主键就是聚焦索引”这是极端错误的，是对聚焦索引的一种浪费。
主键并不一定是聚集索引，只是在SQL SERVER中，未明确指出的情况下，默认将主键定义为聚集，而ORACLE中则默认是非聚集
其它索引
1.联合索引：又叫复合索引，Mysql从左到右使用索引中的字段，一个查询可以只使用索引的一部分，但必须是最左侧的部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。符合最左原则
联合索引实现：每个节点含有多个关键字，排序时按照多个关键字的顺序进行排序。而这个顺序就是你创建索引时候的顺序
如果你经常要用到多个字段的多条件查询，可以考虑建立联合索引，建立了一个联合索引就相当于建立了多个索引
联合索引sql会先过滤出last_name符合条件的记录，在其基础上再过滤first_name符合条件的记录。那如果我们分别在last_name和first_name上创建两个列索引，mysql的处理方式就不一样了，它会选择一个最严格的索引来进行检索，可以理解为检索能力最强的那个索引来检索，另外一个利用不上了，这样效果就不如多列索引了。虽然此时有了两个单列索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。如果经常使用单独一列作为查询条件，那么应该使用单列索引。（如有两个单列索引a、b，查询的时候只用a或只用b）。
多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高
2.覆盖索引：只需要通过辅助索引就可以获取查询的信息，而无需再通过聚集索引查询具体的记录信息
由于覆盖索引不包含整行的记录，因此它的大小远小于聚集索引
比较适合做一些统计操作
索引的创建
在表上创建一个简单的索引，允许使用重复的值
CREATE INDEX index_name
ON table_name (column_name)
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)
创建索引还可以用alter实现
InnoDB按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键（6个字节）然后在上面进行聚集。mysql不能手动创建聚集索引。
主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
索引的实现
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，而B+Tree的高度低（多叉树），可以减少I/O次数
InnoDB（聚簇索引）的数据文件本身就是索引文件（索引和数据存放在一个文件idb）。从上文知道，MyISAM（非聚簇索引）索引文件（MYI）和数据文件（MYD）是分离的，索引文件仅保存数据记录的地址。
mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引（普通索引、唯一索引），又叫辅助索引（secondary indexes）。
实现区别
MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的。
MyISAM左图为主索引，右图为辅助索引（二级索引），两者在结构上没什么区别，都是B+树。
image.png
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
image.png
InnoDB左图为主索引、右图为辅助索引，辅助索引结构也是B+树
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。
这里以英文字符的ASCII码作为比较准则（排序）。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能有一个聚集索引。
SQL语句的执行顺寻
from--where--group by--having--select--order by
Mysql中查看索引
使用“执行过程”EXPLAIN,查看索引使用情况，查询的覆盖行数等
EXPLAIN SELECT * FROM user WHERE id = 10
image.png
B树和红黑树
B树主要是保证只有少数的磁盘访问（io次数少），解决数据结构不在主存中的数据存储问题。高度低。某一个节点可以看做一个磁盘块，里面含有指向下一个磁盘块的指针。
image.png
关键吗就是上图磁盘块中的数字
B树从最后一层开始插入，涉及到节点的分裂
一棵含n个结点的B树的高度也为O（logn），但可能比一棵红黑树的高度小许多，应为它的分支因子比较大。所以，B树可以在O（logn）时间内，实现各种如插入（insert），删除（delete）等动态集合操作。
“阶”定义为一个节点的子节点数目的最大值（非根节点关键字个数m/2向上取整-m-1个）
B+树
image.png
image.png
只有叶节点存数据，非叶节点都只是下层节点最大值的复写。叶子节点间多了指针，使得范围查找变得高效（如上图查找20到65）
红黑树：
image.png
性质1. 节点是红色或黑色。
性质2. 根是黑色。
性质3. 所有叶子都是黑色（叶子是NIL节点，空节点）。
性质4. 每个红色节点的两个子节点都是黑色。
性质5. 从任一节点到其每个叶子的所有简单路径 都包含相同数目的黑色节点。
在进行红黑树的构造的时候，为了满足第5点，则必须每次插入的节点颜色预设为红色，插入后，有可能会导致4不满足，然后进行节点调整。所以如果是构造出来的，一般来说，不会有节点全黑的红黑树
查找、插入、删除等操作的时间复杂度为O（logn）, 且最多旋转三次
红黑是用非严格的平衡来换取增删节点时候旋转次数的降低，任何不平衡都会在三次旋转之内解决，而AVL是严格平衡树，因此在增加或者删除节点的时候，根据不同情况，旋转的次数比红黑树要多。所以红黑树的插入效率更高！！！
不能有连续2个的红节点，红黑树也是二叉查找树
红黑树的平衡性：最差情况，一棵子树全是黑色，一棵子树一红一黑····，高度最多差一倍（保证黑色数量相同）
红黑树插入时，插入节点的叔叔是黑节点：也是类似于avl树中的单旋转，双旋转，并改变着色；插入节点的叔叔是红节点：直接重新着色，并再继续向上调整
红黑树某一结点，如果只有一个儿子（如左），那么该结点为黑结点且儿子为红结点，若该结点为红结点，则朝右结点（没有）方向没有黑结点，朝左结点方向有一个黑结点，导致不平衡
image.png
删除一个黑色叶结点时需要较复杂的调整，（删除非叶结点情况，可以从叶结点找一个跟该结点替换，从而变成删除叶结点的情况）
数据库引擎
InnoDB和MyISAM区别
Oracle收购sun（sun之前收购了MySQL），发布的首个版本5.5，默认使用了InnoDB作为存储引擎，而之前的版本使用MyISAM作为默认。
MyISAM 和 InnoDB的适用场景
MyISAM适合：读多写少(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。
InnoDB适合：写多读少(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。
为什么MyISAM会比Innodb 的查询速度快
INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；
1）数据块，INNODB要缓存，MYISAM只缓存索引块，这中间还有换进换出的减少；
2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快
3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护MVCC ( Multi-Version Concurrency Control )多版本并发控制（读不加锁，读写不冲突。）
（具体看下面Mysql中InnoDB的MVCC）
两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁
1.存储结构
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义；数据文件的扩展名为.MYD (MYData)；索引文件的扩展名是.MYI (MYIndex)。
InnoDB：.ibd的文件，存储与该表相关的数据、索引、表的内部数据字典（表缓存）信息；.frm表结构文件。
2.数据导出：
MyISAM只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。
3.、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少，能加载更多索引。而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb占用空间较大
4.MyISAM存储空间几乎没有限制，最多可到64PB，InnoDB最多64TB
5.InnoDB支持事务（每条sql都是事务，默认配置事务自动提交）和外键，MyISAM不支持
6.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
7.MyISAM：只支持表级锁，select，update，delete，insert语句都会给表自动加锁
InnoDB：支持行级锁，但是InnoDB的行锁，只是在WHERE的主键（唯一索引）是有效的，非主键的WHERE都会锁全表的。对索引加锁，而不是对数据行加锁，只有当查询条件能使用索引的时候才会使用行级锁
两者锁读的时候共享锁、写的时候排它锁（可以一起读，不能一起写或读写）
8.全文索引（通过关键字匹配来查询）
MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（从5.6开始支持，但不支持中文索引）
9.表的具体行数
MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。
InnoDB：没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
10.CURD操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除
image.png
Mysql其它引擎
Blackhole引擎，它会丢弃所有插入的数据，不做任何保存。但会记录日志。
CSV引擎，可以将CSV文件作为MySQL表来处理，可以作为一种数据交换机制。
Memory引擎，数据存在内存中，访问速度快，重启后数据会丢失。
Merge引擎，是MyISAM的变种，由多个MyISAM表合并而来的虚拟表。
事务的四大特性
1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。事务必须是使数据库从一个一致性状态变到另一个一致性状态。多个账户钱的总和不变
3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。（即事务隔离级别）（转钱时，从其他用户角度看）
4、持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。
事务的一致性
1.强一致性：读操作可以立即读到提交的更新操作。
2.弱一致性：提交的更新操作，不一定立即会被读操作读到，此种情况会存在一个不一致窗口，指的是读操作可以读到最新值的一段时间。
3.最终一致性：事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生，不一致窗口的大小依赖于：通信延迟，系统负载等。
其他一致性变体还有：
单调一致性：如果一个进程已经读到一个值，那么后续不会读到更早的值。
会话一致性：保证客户端和服务器交互的会话过程中，读操作可以读到更新操作后的最新值。
事务的隔离级别
1.脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元
当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。
2.不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这回导致锁竞争加剧，影响性能。另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。
例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
3，虚读(幻读)
在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。就好像产生幻觉一样，这就是发生了幻读。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
MySQL数据库的四种隔离级别（从低到高、为解决并发事务问题）：
①Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
②Read committed (读已提交)：只有在事务提交后，其更新结果才会被其他事务看见。可避免脏读的发生。
③Repeatable read (可重复读)：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可避免脏读、不可重复读的发生。
④Serializable (串行化)：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可避免脏读、不可重复读、幻读的发生。
在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。
为什么Mysql能保证失败回滚
进行事务处理的时候，MySQL 在开始事务时会切换到一个延缓操作的状态，这个状态下操作并不都是立即执行的（通常情况下语句是立即执行的）。而在 commit 时，会将延缓执行的操作都执行进去，并将状态回归到及时写入状态。同样的， rollback 时会把延缓写入的操作抛弃掉，此间申请的锁释放掉，并将状态回归到及时写入状态。
执行 rollback 的关键在于释放 申请的锁 和 回归及时写入状态，而并不是放弃未写入的操作（你关心的点在未写入的操作，然而执行与不执行 rollback 都没有操作写进去，所有你感觉执行或不执行都没什么区别）。
或者是显示地使用savepoint，rollback到之前设置的savepoint
数据库完整性约束
数据库完整性约束
数据的完整性
约束是用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。
数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。
分为以下四类：
- 实体完整性：规定表的每一行在表中是惟一的实体。
- 域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
- 参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
- 用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。
与表有关的约束
主键约束(PK)primary key constraint 唯一且不为空
唯一约束(UQ)unique constraint唯一，允许为空，即可以再其中出现null值，但只能出现一次
默认约束(DF)default constraint默认值
检查约束(CK)check constraint范围以及格式限制
外键约束(FK)foreign key constraint表关系
数据库调优：
MySQL调优分析explain；show status查看服务器状态信息
SQL语句优化：
分析：确认程序是否存在查询不需要的记录；mysql是否在扫描额外记录
1、查询不需要的记录：使用select语句查询大量结果，然后再获取前N行（如新闻网站，取100条记录，只显示前面的10条），这时可以使用limit（limit 1，10；从1开始10行）
2、总是使用SELECT ，对I/O、内存消耗较大，不必要时不要这样。
3、子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
Select from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
一种简单的优化就是用innerjoin的方法来代替子查询，查询语句改为：
Select* from A inner join B using(uuid) where b.uuid>=3000;
4、尽量少排序，排序操作会消耗较多的CPU资源（可以使用索引）
5、对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6、切分查询，将大查询切分成小查询，每个查询功能一样，只完成一小部分，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、耗尽系统资源、阻塞很多小的但重要的查询。
索引优化
1、建立索引加快查询性能，优先在经常搜索的字段上建立索引（where）；WHERE子句的查询条件里使用了比较操作符LIKE前置通配符%（如：LIKE "%ABC"），因为‘%’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。只要列中包含有NULL值都将不会被包含在索引中
2、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。不是同一个表也可以union只要列数相同，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。UNION ALL允许重复
3、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
4、有函数的参数，不使用索引
select * from user where age + 1 =20;
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
6、在多个列上建立独立的单列索引大部分情况下不能提高查询性能，可以使用复合索引（多列索引），key(name,age,sex)
多列索引有一个特点，即最左前缀（Leftmost Prefixing）。假如有一个多列索引为key(firstname lastname age)，当搜索条件是以下各种列的组合和顺序时，MySQL将使用该多列索引：
firstname，lastname，age
firstname，lastname
firstname
也就是说，相当于还建立了key(firstname lastname)和key(firstname)。但是只搜lastname用不到索引
7、由于复合索引最左前缀匹配，将搜索次数多的列放到最前列（建立B+树时从最左字段开始排序，第一个字段相同才排第二个字段，因为查找树需要一定的顺序）
8、尽量选择小而简单的数据类型做索引，减少磁盘空间
9、有时候需要索引很长的字符列，这会让索引变得大且慢。一个方式是使用哈希索引，另一个是使用前缀索引，即索引开始的部分字符串，这样可以节约索引空间，提高效率。但这样会降低索引的选择性（不重复的索引值/记录总数）。索引的选择性越高则查询效率越高，唯一索引的选择性是1，性能是最好的。一般情况，某个前缀的选择性也是足够高的。
数据库表结构优化
选择合适的数据类型
数据类型越小越好：尽量使用可以正确存储数据的最小数据类型（tinyint，占用更少的磁盘、内存、cpu缓存）；tinyint占1字节，int占4字节
简单就好：如整型比字符操作代价更低（用整型存储ip地址）
避免NULL：最好指定列为NOT NULL，因为NULL更难优化，使用的索引更复杂
不要设计太多列：减少不必要的列
适度冗余，减少join关联查询
适当拆分，水平拆分、垂直拆分
选择合适的字符编码：如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费
MySQL配置文件优化
mysql的配置文件名为my.cnf（window为my.ini），不同情况下配置文件参数设置也不相同，应该根据具体场景调优。
InnoDB中最重要的选项是：
innodb_buffer_pool_size：缓存用户表（实际数据row）及索引数据的最主要缓存空间，对 Innodb 整体性能影响也最大，默认为8MB，建议设为内存的70%~80%（MyISAM只缓存索引）
innodb_log_file_size：日志文件大小，默认为48MB，应该调大，至少有几百MB
硬件优化：
CPU的选择
最好的选择是核心数多并且主频高的。但是有时考虑成本问题，可以参考以下情况：
如果不是密集型的查询，优先选频率高的，而不是数量多的
如果是密集型的、高并发的查询，比如秒杀等活动，优先选更多的cpu
因为一条sql语句只能在一个cpu上执行
内存的选择
内存并不是容量越大，性能提升越明显。如果内存大小已经超过了总数据量的大小，那么即使再增加内存，系能提升也不会特别明显。
内存频率选择cpu支持的最高的频率，品牌、型号、规格等要一致。
磁盘配置和选择
各种磁盘性能比较：
PCIe > SSD > Raid10 > 磁盘 > 网络存储
各种磁盘的特点和应用：
传统硬盘：需要考虑存储容量、传输速度、访问时间、主轴转速、物理尺寸等参数
raid增加传统硬盘性能：主服务器建议用raid10，从服务器可以raid0（raid0：数据等量放置在2块磁盘中，raid1：让同一份数据完整保存在两块磁盘）
SSD或者PCIe卡（FusionIO）：缺点比传统硬盘更容易坏
SSD应用场景：适用于存在大量随机I/O场景（SSD随机I/O快）、适用于解决单线程负载的I/O瓶颈（用在从服务器上，适用于读的场景，频繁写会减少使用寿命）
网络存储场景（NAS、SAN）：数据库备份
操作系统
合理配置操作系统参数，选择合适的文件系统
加缓存redis
先读写分离、再垂直拆分、再水平拆分
分库：根据业务逻辑垂直拆分
分表：
纵向分表（常见为忙闲分表）
单数据表字段过多，可将频繁更新的整数数据与非频繁更新的字符串数据切分 范例 user表 ，个人简介，地址，QQ号，联系方式，头像 这些字段为字符 串类型，更新请求少； 最后登录时间，在线时常，访问次数，信件数这些字段为整数型字段，更新频繁，可以将后面这些更新频繁的字段独立拆出一张数据表，表内容变少，索引结构变少，读写请求变快。
横向切表
等分切表，如哈希切表或其他基于对某数字取余的切表（USERID奇数、偶数）。等分切表的优点是负 载很方便的分布到不同服务器；缺点是当容量继续增加时无法方便的扩容，需要重新进行数据的切分或转表。而且一些关键主键不易处理。
递增切表，比如每1kw用户开一个新表，优点是可以适应数据的自增趋势； 缺点是往往新数据负载高，压力分配不平均。
索引的好处：
创建索引可以大大提高数据库系统的查询性能、将随机I/O变为顺序I/O（磁盘顺序）。
- 大大减少了服务器需要扫描的数据量；
- 帮助服务器避免进行排序和创建临时表；
- 将随机 I/O 变为顺序 I/O。
- 提高了查询的速度
索引的代价
建立索引需要付出的代价 建立索引的目的是加快对表中记录的查找或排序。但是为表设置索引是要付出代价的：这个代价有几个个方面
a) 索引需要占物理空间 除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
b) 创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加。
c) 降低维护速度 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度，同样降低了效率。
判断是否应该建索引的条件
1、较频繁的作为查询条件的字段应该创建索引
2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3、增、删、改操作较多的数据库字段不适合建索引（加了索引后update会增加耗时）
4、表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。
5、性别字段（只有男、女）不适合建索引，性别字段的选择性低，使用索引查找还不如遍历表的效率高。最多只能少检索一半，但索引的额外开销更大，得不偿失
数据库的悲观锁和乐观锁
一、悲观锁
1、排它锁，当事务在操作数据时把这部分数据进行锁定，直到操作完毕后再解锁，其他事务操作才可操作该部分数据。这将防止其他进程读取或修改表中的数据。
2、实现：大多数情况下依靠数据库的锁机制实现
二、乐观锁
1、如果有人在你之前更新了，你的更新应当是被拒绝的，可以让用户重新操作。
2、实现：大多数基于数据版本（Version）记录机制实现
具体可通过给表加一个版本号或时间戳字段实现，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断当前版本信息与第一次取出来的版本值大小，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，拒绝更新，让用户重新操作。
并发量很高的话，建议使用悲观锁，否则的话就使用乐观锁
Mysql页锁：
页级:引擎 BDB。
表级:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行
行级:引擎 INNODB ， 单独的一行记录加锁
表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
Mysql意向锁：
①在MySQL中有表锁，
LOCK TABLE my_tabl_name READ; 用读锁锁表，会阻塞其他事务修改表数据。
LOCK TABLE my_table_name WRITe; 用写锁锁表，会阻塞其他事务读和写。
②Innodb引擎又支持行锁，行锁分为
共享锁（读锁），一个事务对一行的共享只读锁。
排它锁，一个事务对一行的排他读写锁。
③这两中类型的锁共存的问题
考虑这个例子：
事务A锁住了表中的一行，让这一行只能读，不能写。
之后，事务B申请整个表的写锁。
如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。
数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。
数据库要怎么判断这个冲突呢？
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。
注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。
于是就有了意向锁。
在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。
在意向锁存在的情况下，上面的判断可以改成
step1：不变
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。
意向锁是为了提高封锁子系统的效率。
注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。
MySQL中InnoDB的MVCC
MVCC的原理与copyonwrite类似，全称是Multi-Version Concurrent Control，即多版本并发控制。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。
由于在update操作提交之前，不能影响已有数据的一致性，所以不会改变旧的数据，update操作会被拆分成insert + delete。需要标记删除旧的数据，insert新的数据。只有update提交之后，才会影响后续的读操作。而对于读操作而且，只能读到在其之前的所有的写操作，正在执行中的写操作对其是不可见的。（读不加锁，读写不冲突）
innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。在repeated read的隔离级别下，具体各种数据库操作的实现：
select：满足以下两个条件能够查询成功：（1）该行的创建版本号小于等于当前版本号，用于保证在select操作之前所有的操作已经执行落地。（2）该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
insert：将新插入的行的创建版本号设置为当前系统的版本号。
delete：将要删除的行的删除版本号设置为当前系统的版本号。
update：不执行原地update（相当于写操作），而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。
其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。
由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。
通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。
MVCC和乐观锁区别
在数据库中，并发控制是指在多个用户/进程/线程同时对数据库进行操作时，如何保证事务的一致性和隔离性的，同时最大程度地并发。
当多个用户/进程/线程同时对数据库进行操作时，会出现3种冲突情形：
读-读，不存在任何问题读-写，有隔离性问题，可能遇到脏读（会读到未提交的数据） ，幻影读等。写-写，可能丢失更新要解决冲突，一种办法是是锁，即基于锁的并发控制，比如2PL，这种方式开销比较高，而且无法避免死锁。
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读
乐观并发控制（OCC）是一种用来解决写-写冲突的无锁并发控制，认为事务间争用没有那么多，所以先进行修改，在提交事务前，检查一下事务开始后，有没有新提交改变，如果没有就提交，如果有就放弃并重试。乐观并发控制类似自旋锁。乐观并发控制适用于低数据争用，写冲突比较少的环境。多版本并发控制可以结合基于锁的并发控制来解决写-写冲突，即MVCC+2PL，也可以结合乐观并发控制来解决写-写冲突。
Mysql5.7的新特性
MySQL 5.7版本提供了更为简单SSL安全访问配置，并且默认连接就采用SSL的加密方式
MySQL数据库从5.7.8版本开始，也提供了对JSON的支持。
generated column是MySQL 5.7引入的新特性，所谓generated column，就是数据库中这一列由其他列计算而得。
不支持子查询中排序
范式
第一范式1NF的定义为：符合1NF的关系中的每个属性都不可再分，1NF是所有关系型数据库的最基本要。
2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
码：设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码。在实际中我们通常可以理解为：假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码。一张表中可以有超过一个码。
部分函数依赖：依赖于码的一部分（其中的某几个）
非主属性：包含在任何一个码中的属性成为主属性。
3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。
BCNF范式：在 3NF 的基础上消除主属性对于码的部分与传递函数依赖
(https://www.zhihu.com/question/24696366)
反范式是通过增加冗余数据或数据分组来提高数据库读性能的过程。在某些情况下， 反范式有助于掩盖关系型数据库软件的低效。关系型的范式数据库即使做过优化， 也常常会带来沉重的访问负载。
JDBC的参数
image.png
数据库系统设计步骤
数据库设计的过程(六个阶段)
1.需求分析阶段
准确了解与分析用户需求（包括数据与处理）
是整个设计过程的基础，是最困难、最耗费时间的一步
2.概念结构设计阶段
是整个数据库设计的关键
通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型（DBMS数据库管理系统，如MySQL）
3.逻辑结构设计阶段
将概念结构转换为某个DBMS所支持的数据模型，对其进行优化
4.数据库物理设计阶段
为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）
5.数据库实施阶段
运用DBMS提供的数据语言、工具及宿主语言，根据逻辑设计和物理设计的结果
建立数据库，编制与调试应用程序，组织数据入库，并进行试运行
6.数据库运行和维护阶段
数据库应用系统经过试运行后即可投入正式运行。
在数据库系统运行过程中必须不断地对其进行评价、调整与修改
数据库各级模式的形成过程
1.需求分析阶段：综合各个用户的应用需求
2.概念设计阶段：形成独立于机器特点，独立于各个DBMS产品的概念模式(E-R图，实体-联系图)
image.png
3.逻辑设计阶段：首先将E-R图转换成具体的数据库产品支持的数据模型，如关系模型，形成数据库逻辑模式；然后根据用户处理的要求、安全性的考虑，在基本表的基础上再建立必要的视图(View)，形成数据的外模式（将E-R转换成表，根据范式设计表，表结构设计尽量简单、小）
4.物理设计阶段：根据DBMS特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式
