1. 索引
（1）作用
-
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
-
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
（2）分类
-
主键索引
数据列不允许重复，不允许为NULL，一个表只能有一个主键。
-
唯一索引
数据列不允许重复，允许为NULL值， 一个表允许多个列创建唯一索引。
- ALTER TABLE table_name ADD UNIQUE(column)；创建唯一索引
- ALTER TABLE table_name ADD UNIQUE(column1,column2); 创建唯一组合索引
-
普通索引
基本的索引类型，没有唯一性的限制，允许为NULL值。
- ALTER TABLE table_name ADD INDEX index_name(column)；创建普通索引
- ALTER TABLE table_name ADD INDEX index_name(column1,column2,column3); 创建组合索引
-
全文索引
是目前搜索引擎使用的一种关键技术。
- ALTER TABLE table_name ADD FULLTEXT(column); 创建全文索引
-
联合索引
MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
- MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
（3）基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。索引的原理很简单，就是把无序的数据变成有序的查询
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
（4）创建索引的三种方式
-
在执行CREATETABLE时创建索引
-
使用ALTERTABLE命令去增加索引
-
使用CREATEINDEX命令创建
CREATE INDEX index_name ON table_name(column_list);
（5）索引算法
（6）设计原则
- 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
- 基数较小的类，索引效果较差，没有必要在此列建立索引
- 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
- 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行 更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
- 左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、 between、like)就停止匹配，比如a=1andb=2andc>3andd=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- 较频繁作为查询条件的字段才去创建索引
- 更新频繁字段不适合创建索引
- 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，多也就三种，区分度实在太低)
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改 原来的索引即可。
- 定义有外键的数据列一定要建立索引。
- 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
- 对于定义为text、image和bit的数据类型的列不要建立索引。
（7）使用索引查询一定能提高查询的性能吗?
- 通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。
- 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。
- 使用索引查询不一定能提高查询性能，索引范围查询(INDEXRANGESCAN)适用于两种情况:
- 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
- 基于非唯一性索引的检索
2. 事务
- 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
- 事务是逻辑上的一组操作，要么都执行，要么都不执行。
（1）四大特征
- 原子性: 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用;
- 一致性: 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的;
- 隔离性: 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的;
- 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
（2）脏读、幻读、不可重复读
- 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
- 不可重复读(Non-repeatable read)：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据
- 幻读(Phantom Read)：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
（3）隔离等级
- READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读 或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化): 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
3. 分布式事务
4. B+树
（1）性质
- n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
- 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
- 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的大(或小)关键字。
- B+树中，数据对象的插入和删除仅在叶节点上进行。
- B+树有2个头指针，一个是树的根节点，一个是小关键码的叶节点。
- 哈希索引：通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法)，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入 Hash表的对应位置；如果发生Hash碰撞(两个不同关键字的Hash值相同)，则在对应Hash键下以链表形式存储。
（2） B+树的优点、数据库为什么使用B+树而不是B树
-
B树只适合随机检索，而B+树同时支持随机检索和顺序检索;
-
B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素;
-
B+树的查询效率更加稳定。
B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短， 只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
-
B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。
B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
-
增删文件(节点)时，效率更高。
因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。
-
B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据
- 在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
- 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。
5. 锁
数据库的乐观锁和悲观锁是什么?怎么实现的?
- 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
- 实现方式:使用数据库中的锁机制
- 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
- 实现方式:乐一般会使用版本号机制或CAS算法实现。
- 乐观锁适用于写比较少的情况下(多读场景)，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
- 多写的场景下用悲观锁就比较合适
6. 视图
（1）特点
- 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
- 视图是由基本表(实表)产生的表(虚表)。
- 视图的建立和删除不影响基本表。
- 对视图内容的更新(添加，删除和修改)直接影响基本表。
- 当视图来自多个基本表时，不允许添加和删除数据。
- 视图的操作包括创建视图，查看视图，删除视图和修改视图。
（2）使用场景
- 视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。
- 重用SQL语句：简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节
- 使用表的组成部分而不是整个表
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
（3）优点
- 查询简单化。视图能简化用户的操作
- 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
- 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性
（4）缺点
- 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
- 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的这些视图有如下特征:
- 有UNIQUE等集合操作符的视图。
- 有GROUP BY子句的视图。
- 有诸如AVG\SUM\MAX等聚合函数的视图。
- 使用DISTINCT关键字的视图。
- 连接表的视图(其中有些例外)
7. varchar与char的区别
- char
- char表示定长字符串，长度是固定的；
- 如果插入数据的长度小于char的固定长度时，则用空格填充;
- 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，
- 但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
- 对于char来说， 多能存放的字符个数为255，和编码无关
- varchar
- varchar表示可变长字符串，长度是可变的;
- 插入的数据是多长，就按照多长来存储;
- varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法;
- 对于varchar来说， 多能存放的字符个数为65532
- 总之，结合性能角度(char更快)和节省磁盘空间角度(varchar更小)，具体情况还需具体来设计数据库才是妥当的法。
8. 读写分离方案
- 读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读 (如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave)。
-
使用mysql-proxy代理
-
优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用
-
缺点：降低性能， 不支持事务
-
-
使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
- 如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select都访问salve 库，这样对于dao层都是透明。 plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。
- 不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。
-
使用AbstractRoutingDataSource+aop+annotation在service层决定数据源
- 可以支持事务
- 缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理
1111
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
