数据库

1.1数据库设计：

1.存储(文件系统) ： 将数据最终磁盘化存入硬盘中，如机械硬盘，或固态硬盘

2.程序实例（用逻辑结构映射出物理结构来，并且在程序中，提供、获取、以及管理数据的方式，还有必要的问题追踪机制）：频繁的IO，会使数据库的执行效率慢如蜗牛，因为一次IO读取多条数据，和单条数据花费的时间并没有多大的区别，我们可  以一次性的区读取多行，提升IO的效能，所以行就失去了它的意义。实际，数据库也是把存储单位，用块或者叶来表示，每个块，或者叶中，会存放多行数据。读取的时候，可以将多个块一起加载进内存当中

3.存储管理模块

4.缓存机制：缓存机制不宜过大，且算法里需要有淘汰机制，淘汰掉一些之后不常用的数据

5.SQL解析模块

6.日志管理：此外咱们做的SQL操作需要记录下来，方便做数据库的主从同步

7.权限划分

8.容灾机制

9.索引管理

10.锁管理

1.2索引模块常见问题：

1.为什么使用索引？

因为全表扫描，在数据量很大的时候效率很低，所以需要引入索引机制，它的灵感来自于字典，只要我们把一些关键信息组织起来，查询的时候依据这些信息就能查到我们需要的页面。通过索引大幅提升查询速度。

2.什么样的信息能成为索引
        主键、唯一键以及普通键等

3.索引的数据结构

平衡二叉树、红黑树、以及B-Tree、B+-Tree、哈希Hash结构。

通常索引的数据结构是B+树，比较小众的还有Hash结构还有BitMap等。其中Mysql数据库不支持BitMap索引，同时基于innoDB和MyISAM引擎的Mysql不显式支持Hash。

B+树更适合来用作存储索引，B+树的查询效率更加稳定，B+树更有利于对数据库的扫描（ B+树在做范围查询有着更高的性能。

Hash索引也可以考虑一下，就是根据Hash函数的运算，只需要一次定位，便可以找到查询数据所在的头，不像B+索引，需要从根节点到非叶子节点，最后才能访问到我们需要的数据，可能会经过多次的IO访问，Hash索引的效率理论上要高于B+索引。

Hash索引的缺点：

1.仅仅能满足"=","in"不能使用范围查询（由于hash索引比较的是经过hash运算的Hash值，所以它只能用于等值的过滤，不能用于基于范围的查询，因为经过相应hash算法处理过后的Hash值的大小关系，并不能保证和Hash运算前的完全一样）。

2.无法被用来避免数据的排序操作（由于Hash索引中存储的是经过Hash运算的Hash值，而且Hash值的大小并不和经过Hash运算之前的Hash值一样，所以数据库无法利用索引的数据来避免任何排序运算）

3.不能利用部分索引键查询。（对于组合索引，Hash索引在计算Hash值的时候，是组合键，就是将组合索引键合并之后再一起进行运算的Hash的值，而不是单独计算hash值的，所以通过组合索引前面一个或者几个进行查询的时候，Hash索引也无法被利用，而B+树是支持利用组合索引中的部分索引的）

4.不能避免表扫描。(hash索引是将索引键通过Hash运算之后，将运算结果的Hash值和所对应的行指针信息，存放再一个bucket当中的，由于不同的索引键存在相同的Hash值，所以即使取出满足某个Hash键值的那些数据，也无法从Hash索引中完成查询，也是要通过访问Bucket中的实际数据，进行相应的比较，所以这是不能避免表扫描的原因。 )

5.遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高。（对于选择性比较低的索引键，如果创建Hash索引，那么将存在大量记录指针信息存放同一个Bucket情况，从而造成整体性能非常低下，就跟我们之前用到的二叉树一样，有可能变成线性的存储结构，有可能在一个很极端的情况下，所有的键计算出来的hash值都是相同的，也就是都放在同一个bucket当中，那我们查询bucket里面最后一条数据的时候，就会变成线性的了，这也是Hash索引不能成为主流索引的原因，因为它比较不稳定，不支持一些范围的查询）

一般数据库除了用B+树，Hash作为其索引以外，还有没有别的数据结构呢？

答案：BitMap，即位图索引，当表中的某个字段只有几种值的时候，就比如说我们要表示性别的时候，只有男女两种情况，如果仅仅是为了在这个字段上实现高效的统计，此时用位图索引是一个最佳的选择，不过要注意的是，目前只有很少的数据库支持位图索引，已知比较主流的是Oracle数据库，位图索引的结构类似于B+树，就像这样子，B+树用来定位叶子节点，这些节点包含指定键值的位图，位图位于叶子节点上，它的段信息就在这里，该索引不是主流索引，只作简单讲解。由于数据的值种类固定了，只有blue、green、red、yellow这几种形态，在存储方式上会先按照状态值进行分开，每一种值的空间会存储每一个实际的数据行，是否是这个值，我们可以看到这里有0101这样的一个位图，第一行是blue，那就用1来表示，第二行不是blue那就用0来表示，它是red，那red的第二个，就是1，那么在这种情况下，由于只需要存储是与否，通常只需要一个Bit位来存放，理论上一个叶子块可以存放非常多的bit位来表示不同的行，因此用它来统计时，是非常快的 。下载到内存中后，几乎是存CPU的叠加操作，位图索引虽然好，但是它只适用于某个字段的值只有固定的几种情况，同时需要注意的是，位图索引有一个很大的缺陷，它的锁的力度非常的慢，尝试新增或者修改一条数据的时候，通常与它在同一个位图的数据都会被锁住，因为某行所在的位置有可能因为数据的添加或删除而发生改变，像这里我们可以看到它的01的顺序，和行的顺序是一一对应的，如果发生增删改查的情况，它的逻辑顺序可能发生变化，因此在发生改变的时候，必须得将其锁定，以防止取错数据。所以它并不适合高并发的联机事务处理系统，即咱们常见的OLPP系统，而适合并发较少，且统计运算较多的OLAP类系统。

4.密集索引和稀疏索引的区别

1. 密集索引文件中每个搜索码值都对应一个索引值

2. 稀疏索引文件只位索引码的某些值建立索引项。

左边是密集索引，右边是稀疏索引，密集索引这个定义是什么意思呢？

密集索引：可以理解为，叶子节点不仅仅是保留键值，还保存了同一行里位于其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，一个表只能创建一个密集索引，

稀疏索引：叶子节点仅保留了键位信息，以及该行数据的地址，有的稀疏索引是仅保留了键位信息，及其主键，那么定位到叶子节点之后呢，任然需要地址或者主键信息定位到数据。

上面是一般数据库的情况，我们呢来对Mysql做具体分析：
            它主要由两种存储引擎，MyISAM，另一种是innoDB，还有其他种，但这两种是主流，前者，不管是主键索引，唯一键索引，或者普通索引，其索引均属于稀疏索引，InnoDB呢，它必须有，且仅有一个密集索引，那么这一个密集索引，它的选取规则是如何的呢，1. 如果一个主键被定义，该主键则作为密集索引， 2.如果没有主键被定义，那么该表的第一个唯一非空索引则作为密集索引，3.如果没有主键，也没有合适的唯一索引，InnoDB内会生成一个隐藏的主键，作为密集索引，这个隐藏的主键是一个六字节的列，该列的值会随着数据的插入而自增，也就是说我们的innoDB呢必须有一个主键，而该主键就必须作为唯一的密集索引而存在，那为什么它一定要有主键索引呢？，因为有一条需要注意的规定，即 ：非主键索引， 就是我们的稀疏索引的叶子节点并不存储行数据的物理地址，而是存储的该行的主键值。所以非主键索引包含了两次查找，一次是查找次级索引自身，然后呢再查找主键。

innoDB使用的是密集索引：将主键组织到一棵B+树中，而行数据就存储在叶子节点上，因为innoDB的主键索引和对应的数据是保存在同一个文件当中的，所以检索的时候，在加载叶子节点的主键进入内存的同时，也加载了对应的数据，即   若使用Where ID=14 的条件查询主键，则按照B+树的检索算法呢，即可查找到对应的叶子节点，并获得对应的行数据，那么对稀疏索引进行条件筛选，那么要经历两个步骤，第一步，在稀疏索引的B+树中检索该键，比如说检索Ellison，那么就定位到主键信息了，获取到主键信息了，还需要进行第二步，第二步就是使用主键Where=14在B+树中再执行一遍我们的检索操作，最终到达叶子节点，获取整行的数据。

MyISAM使用的是稀疏索引：稀疏索引的两棵B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不一样而已，主键B+树索引存储了主键，辅助键B+树索引存储了辅助键，表数据存储在独立的地方，跟它的数据是分开存储的，这两棵B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别，由于索引树是独立的，通过辅助键检索，无需访问主键的检索树，这下大家对Mysql的两种存储结构是比较熟悉了。

InnoDB的索引和数据是存在一块的，MyISAM它的索引和数据是分开的

衍生出来的问题，以Mysql为例：

1.如何定位并优化慢查询sql：

1.根据慢日志定位慢查询SQL（慢日志就是用来记录执行的比较慢的一些sql）

命令：show variables like '%quer%';
           slow_query_log：慢日志   OFF关闭(需要 将它打开)
           slow_query_log_file：慢日志的全地址
           long_query_time：10.00000 （表示每次执行sql花费十秒钟，就会记录进慢日志）
           命令：show status like '%slow_querie%';
           Slow_queries：即慢查询的数量
           命令：打开慢日志记录  set global slow_query_log = on;
           命令： 设置慢查询时间为1秒  set global long_query_time = 1; (需要重新连接数据库才能刷新)
                
           制造慢查询：
           先查看有多少条数据（非必须操作）：select count(id) from person_info_large;
                    select name from person_info_large order by name desc; 执行完这条sql，慢日志会增加一条记录 ，打开慢日志地址log，里面会记录这条sql

实际项目运行当中，可以通过慢日志，去捕获一些慢sql，去分析这个sql为什么这么慢，然后再对它进行调优。

2.2.使用explain等工具分析sql

在分析性能的时候explain非常管用，这个关键字一般放在select name from person_info_large order by name desc; select前面，用于描述mysql如何执行查询操作，以及msql成功返回及结果需要查询的函数，expain可以帮我们分析select语句，让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器能够更好的工作。
                例：explain select name from person_info_large order by name desc;
其中有两列对于调优sql非常重要，

id：标明了sql的执行顺序，越大的越先执行，比如我们的复合查询，里面的语句是最先执行的，所以里面的语句是比较大的。

type：表示mysql找到数据行的方式，方式有很多，不需要去一一记忆，性能从最优到最差会按照system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquey>index_subquery>range>index>all;

index和all表明本次查询走的是全表扫描，因此我们看到语句是慢查询语句，并且type是这两个值中的一个，证明语句是需要优化的。
Extra：虽然extra没有type直观，但可以从它这里获取到更为详细的信息，辅助我们了解语句的执行方式，它的类型也非常多，咱们主要关注两种极有可能需要优化的方式。

extra项：Using filesort：表示Mysql会对结果使用一个外部索引排序，而不是从表里按索引依次序读到相关内容。可能在内存或者磁盘上进行排序，Mysql中无法利用索引完成的排序操作称为“文件排序”。
            Using temporary：表示Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

3.修改sql或者尽量让sql走索引

将select name from person_info_large order by name desc;改为由account索引的查询语句 select account from person_info_large order by account desc；
有时候业务就是要按照名字来排序，此时就需要加索引，name是没有索引的，就需要执行命令：

alter table person_info_large add index idx_name(name); 
explain select count(id) from person _info_large;            
type：index
key：account
type用的是预期的index,key呢居然用的是account，用的是唯一键，并不是用的主键，为什么用的是account而不用ID呢，iD才是它的主键啊，之所以去走这个索引，是由我们这个Mysql的查询优化器来做决定的，Mysql的查询优化器最终的目标是尽可能的使用索引，并使用最严格的索引，来消除尽可能多的数据行，最终目标是提交select语句查找数据行，而不是排除数据行，优化器排除数据行的原因在于它排除数据行的速度越快，那么找到于条件匹配的数据行就越快。因此，查询优化器会根据它的判断和分析来决定走哪个索引。它没选择主键索引，大致原因应该是由于密集索引的叶子节点，把其他列的数据也存放到了叶子节点当中。因为数据都放到了一起，所以查询速度比稀疏索引要低，因为毕竟稀疏索引只存储了关键字以及主键的值，这样我们在内存里就能加载更多的关键字和主键的值。所以查询优化器选择了它。
                我们可以强制语句走主键索引，对比一下两者的耗时：explain select count(id) from person _info_large force index(primary);

联合索引的最左匹配原则的成因：

什么是最左匹配原则呢？
            select * from person_info_large where area = ' ' AND title = ''
                possible_keys：index_area_title
                key：index_area_title
如果将title删除了呢？
            select * from person_info_large where area = ' ' ；
                possible_keys：index_area_title
                key：index_area_title 依然走的是Area和title索引

如果将area删除了呢？
            select * from person_info_large where  title = ''；
                possible_keys：NULL
                key：NULL
                type：ALL    走的是全表扫描，这是最差的性能
1.最左前缀匹配原则：非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a=3 and b =4 and c >5 and d=6如果建立(a、b、c、d)顺序的索引，d使用不到索引的（因为遇到>号停止了），如果建立(a、b、d、c)的索引则都可以用到，a、b、d的顺序可以任意调整。

2. = 和 in 可以乱序：比如 a =1 and b=2 and c= 3建立(a、b、c)索引可以任意顺序，mysql查询优化器会帮你优化成索引可以实别的形式。

最左匹配原则的成因：mysql创建复合索引的规则是首先，会对复合索引的最左边，也就是第一个索引字段的数据进行排序，在第一个字段的排序基础上，再对后面第二个字段进行排序，其实就相当于实现了 order by 字段1   再order by 字段2 这样一种排序规则。所以第一个字段是绝对有序的，第二个字段就是无序的了，因此通常情况下直接使用第二个字段进行条件判断使用不到索引的，这就是mysql为什么要强调联合索引最左匹配原则的原因。

索引是建立的越多越好吗？
        数据量小的表不需要建立索引，建立会增加额外的索引开销。
        数据表更需要维护索引，因此，更多的索引需要更多的维护成本。
        更多的索引意味着需要更多的空间。（好比一本一百页的书却有五十页的目录）

锁模块常见问题：

1.MyISAM与InnoDB关于锁方面的区别是什么？
            MyISAM默认用的是表级锁，不支持行级锁。
            InnoDB默认用的是行级锁，也支持表级锁。
            例：多个session操作同一张表或者表里的同一个数据。
                两张表，person_info_large 基于innoDB引擎，person_info_MyISAM基于MyISAM引擎的。
                select * from person_info_myisam where id between 1 and 2000000;
                updata person_info_myisam set account where id =2000001;
                当表进行查询的时候MyIsam会自动给表上一个表锁，等待更新完成后，才会执行添加操作。
                MyISAM执行select的时候会自动会表加上一个读锁，
                执行增删改的时候会自动给表加上一个写锁。当读锁未被释放，另外一个session想给表加上一个写锁，表就会被堵塞。直到所有的读锁都被释放为止。
2.如何显式给表加上读锁？
                lock tables person_info_myisam read;加上读锁
                unlock tables ; 释放读锁

3.读锁有另外一个名字叫做共享锁，为什么叫做共享锁呢？
            session1给表加上读锁的同时，session2也给表加上一个读锁。
            session2模拟取出第两百万和两百万零一的数据：
                select * from person_info_myisam where id in (2000000,2000001)
                执行很快！再执行范围查询的时候，依然能对表里的数据执行读操作，所以读锁也叫共享锁。
            反过来，先上写锁，后上读锁：
                updata  person_info_myisam set account = account  where id between 1 and 200000; 
                写锁的别名排他锁。
                除了可以对增删改上排他锁，还可以对select上排他锁，
                select * from person_info_myisam where id  between 1 and 2000000 for updata;

总结：myisam引擎默认支持表级锁，它不支持行级锁，表级锁会锁住整张表，像这里咱们对第一条和第两百万条数据进行查询的时候，第两百万另一条数据进行更新，它同样会被锁住，如果是行级锁，他只会对1到两百万条数据锁住，第两百万零一条数据是不会被锁住的。

锁分两种：共享锁和排他锁，上了共享锁依然可以再上共享锁，但是不支持上排他锁，要是先上了排他锁，另外的读或者写都是不允许的。共享锁和排他锁这种情况，也同样支持行级锁的innoDB引擎mysql是默认自动提交事务的。innoDB用的是二段锁，二段锁是加锁和解锁是分为两个步骤来进行的。全程用排他锁锁定，就是悲观锁。

悲观锁：悲观锁并发控制实际上是先取锁，再访问的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制，让数据库产生了额外的开销。增加产生死锁的机会，另外，在只读型事务处理中，由于不会产生冲突也没必要使用锁，上锁会增加系统负担，同时还会降低并行性，如果锁住了某行数据，其他数据就 必须等待该数据处理完。

乐观锁：乐观锁认为一般数据一般情况下不会造成冲突，所以在提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做，相对于悲观锁在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制，一般乐观锁的方式就是记录数据版本，实现数据版本有两种方式，一种是使用版本号，另外一种是使用时间戳。

4.数据库事务的四大特性。
            原子性：要么全部成功。要么全部失败。要么做，要么不做。
            一致性：数据库中的数据应满足完整性约束。用户的钱有两千，不管转账几次，转账的钱加上转账后的钱要是2000
            隔离性：多个事务并发执行，一个事务的执行不能影响其他事务的执行。
            持久性：一个事务一旦提交，它对数据库的修改应该永久保存在数据库中。持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失，即对已提交事务的更新能恢复。所以持久性主要在DBMS的恢复性能，就拿innoDB来讲，它会将所有对页面的修改写入一个专门的文件，当数据库启动时，从此文件进行恢复操作。

5.事务隔离级别以及各级别下的并发访问问题。
Mysql会利用锁机制创建出不同的事务隔离级别，咱们会按照事务隔离级别，从低到高的顺序讲解。首先我们来看看事务并发引起的问题：
1.更新丢失---mysql所有事务隔离级别在数据库层面上均可避免：

更新丢失，即一个事务的更新覆盖了另一个事务的更新。由于现在主流数据库都会为我们加锁来避免这种更新丢失的情况，所以我们在数据库层面上不好模拟。

2.脏读----READ_COMMITTED事务隔离级别以上可以避免：

一个事务读到另一个事务未提交的数据。而该问题可以在已提交读事务隔离级别上去避免

解决方法：

1.查看当前事务隔离级别：select @@tx_isolation;  REPEATABLE--READ

2.设置当前session的隔离级别 : set session transaction isolation level read uncommitted;  即：读未提交(此隔离级别容易引发脏读）

3.开启事务:start transaction：如何避免脏读：只需要将隔离级别往上调一个等级，READ--COMMITED 就行
set session transaction isolation level read committed ;

 3.不可重复读---REPEATABLE -- READ事务隔离级别以上可以避免：

事务A多次读取同一数据， 事务B在事务A读取数据的同时对数据进行修改，导致事务A多次读取同一数据，数据不一致。

解决：将事务再调高一个级别： set session transaction isolation level repeatable read;
            
 4.幻读--SERIALIZABLE事务隔离级别可避免
                他的含义是事务A读取于搜索条件相匹配的若干行，事务B以插入或者删除行的方式来修改事务A的结果集，导致事务A看起来像出现幻觉一样。
                解决：将事务隔离级别调整至最高。
                set session transaction isolation level serializeble;

为什么不直接将事务级别设置为最高？

因为事务级别越高，安全性越高，串行化就越严重，这样就降低了数据库的并发度，因此可以依赖业务的需要设置隔离级别。

4.InnoDB可重复读隔离级别下如何避免幻读。
            表象：快照都(非阻塞读)---伪MVCC
            内在：next-key锁（行锁+gap锁）