201 lines
20 KiB
Markdown
201 lines
20 KiB
Markdown
|
||
## 手写一段sql语句，具体内容忘了，好像和limit有关
|
||
|
||
## 存储引擎的区别
|
||
**InnoDB**: 支持事务，是面向在线事务处理(OLTP)的应用,特点是行锁设计，支持外键，并支持一致性非锁定读，即默认情况下读取操作不会产生锁．是默认的**存储引擎**:．还提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能．
|
||
MyISAM: 不支持事务，是表锁设计和支持全文索引，主要面向一些OLAP的数据库应用.它的缓冲池只缓冲索引文件，而不缓冲数据文件．该存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件.
|
||
**NDB**:是一个集群存储引擎，其特点是数据全部放在内存中，因此主键查找速度极快，并通过添加NDB数据库存储节点可以线性提高数据库性能，是高可用，高性能的集群系统．
|
||
**Memory**: 将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据库都将消失，它非常适合存储临时数据的临时表．默认采用哈希索引．
|
||
**Archive**: 只支持INSERT和SELECT操作，使用zlib算法将数据行进行压缩，压缩比可以达到1:10，非常适合存储归档数据.但其本身不是事务安全的存储引擎，其设计目标是提供高速的插入和压缩功能．
|
||
**Federated**: 并不存放数据，它只是指向一台远程MySQL数据库服务器上的表.
|
||
**Maria存储引擎**: 设计目标主要是用来取代原有的MyISAM存储引擎．
|
||
|
||
## MyISAM和InnoDB的区别
|
||
1. MyISAM是非事务安全型的，而InnoDB是事务安全型的。
|
||
2. MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
|
||
3. MyISAM支持全文索引，而Innodb不支持全文索引
|
||
4. MyISAM表是保存成文件形式的，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
|
||
5. InnoDB表比MyISAM表更安全，可以保证数据不丢失的情况下，切换非事务表到事务表
|
||
|
||
应用场景
|
||
1. MyISAM 管理非事务表，它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
|
||
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB,这样可以提高多用户并发操作的性能。
|
||
|
||
### sql注入原理
|
||
就是通过把SQL命令插入到Web 表单 提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令
|
||
1.猜表名，列名等
|
||
2.后台身份验证绕过漏洞
|
||
验证绕过漏洞就是'or'='or'后台绕过漏洞，利用的就是AND和OR的运算规则，从而造成后台脚本逻辑性错误.
|
||
|
||
防范：
|
||
1.永远不要信任用户的输入，要对用户的输入进行校验，可以通过正则表达式，或限制长度，对单引号和双"-"进行转换等。
|
||
2.永远不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取。
|
||
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
|
||
4.不要把机密信息明文存放，请加密或者hash掉密码和敏感的信息。
|
||
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装，把异常信息存放在独立的表中。
|
||
|
||
## 数据库范式
|
||
**第一范式（1NF）：属性不可分**。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。
|
||
**第二范式（2NF）：符合1NF，并且，非主属性完全依赖于码（也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中）**。
|
||
**第三范式（3NF）：符合2NF，并且，消除传递依赖（每一列数据都和主键直接相关，而不能间接相关）**。
|
||
BCNF:符合3NF,并且,没有任何属性完全函数依赖于非码的任何一组属性.
|
||
找个例子说.
|
||
|
||
参考： http://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html
|
||
http://blog.sina.com.cn/s/blog_46d817650100yj2i.html
|
||
|
||
## 数据库索引
|
||
索引是一个单独存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针，使用索引可以提高数据库特定数据的查询速度.索引时在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同,并且每种存储引擎也不一定支持所有索引类型．
|
||
|
||
索引的存储类型有两种：BTREE和HASH,具体和表的存储引擎有关．MyISAM和InnoDB存储引擎只支持BTREE;MEMORY/HEAD存储索引可以支持HASH和BTREE索引．
|
||
**索引的优点**:
|
||
1. 通过创建唯一索引，可以保证数据库表中每行数据的唯一性.
|
||
2. 可以加快数据的查询速度．
|
||
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接．
|
||
4. 再使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
|
||
5. 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能。
|
||
|
||
|
||
**索引的缺点**：
|
||
1. 创建索引和维护索引要耗费时间，并且随着数据量的增加耗费时间也增加．
|
||
2. 索引需要占空间内存．
|
||
3. 在对表中数据进行增加,删除和修改的时候，索引也需要动态维护，这样降低了数据维护速度．
|
||
|
||
### 索引分类
|
||
1. 普通索引和唯一索引
|
||
2. 直接创建索引和间接创建索引
|
||
3. 普通索引和唯一性索引
|
||
4. 单个索引和符合索引
|
||
5. 聚簇索引和非聚簇索引
|
||
|
||
## 索引失效？？
|
||
1. WHERE字句的查询条件里有不等于号（WHERE column!=...），MYSQL将无法使用索引
|
||
2. 如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=...），MYSQL将无法使用索引
|
||
3. 在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。
|
||
4. 如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE 'abc%',MYSQL将使用索引；如果条件是LIKE '%abc'，MYSQL将不使用索引。
|
||
5. 在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。
|
||
6. 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
|
||
7. 如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
|
||
8. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。
|
||
9. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
|
||
|
||
http://www.cnblogs.com/hongfei/archive/2012/10/20/2732589.html
|
||
http://my.oschina.net/hebad/blog/370815
|
||
|
||
|
||
## 数据库锁机制
|
||
数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问，访问变得有序所设计的一种规则。MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。
|
||
- 表级锁定（table-level）：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。表级锁分为读锁和写锁。
|
||
|
||
- 页级锁定（page-level）：页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
|
||
|
||
- 行级锁定（row-level）：行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。InnoDB的行级锁同样分为两种，共享锁和排他锁，同样InnoDB也引入了意向锁（表级锁）的概念，所以也就有了意向共享锁和意向排他锁，所以InnoDB实际上有四种锁，即共享锁（S）、排他锁（X）、意向共享锁（IS）、意向排他锁（IX）；
|
||
在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。
|
||
|
||
而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。
|
||
|
||
|
||
| | 共享锁（S）| 排他锁（X）| 意向共享锁（IS）| 意向排他锁（IX）|
|
||
共享锁（S） | 兼容 | 冲突 | 兼容 |冲突
|
||
排他锁（X） | 冲突 | 冲突 | 冲突 |冲突
|
||
意向共享锁（IS） | 兼容 | 冲突 | 兼容 |兼容
|
||
意向排他锁（IX） | 冲突 | 冲突 | 兼容 |兼容
|
||
|
||
|
||
|
||
参考地址：http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html
|
||
|
||
MyISAM 表锁优化建议：
|
||
1、缩短锁定时间
|
||
2、分离能并行的操作
|
||
3、合理利用读写优先级
|
||
|
||
|
||
## 乐观锁，悲观锁
|
||
|
||
悲观锁:它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并发访问性不好。
|
||
乐观锁（ Optimistic Locking ） :相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则则拒绝更新并返回用户错误的信息，让用户决定如何去做。乐观锁由程序实现，不会存在死锁问题。它适用的场景也相对乐观。但乐观锁不能解决脏读的问题
|
||
|
||
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。[1]
|
||
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。[1] 乐观锁不能解决脏读的问题。
|
||
|
||
## 事务隔离机制
|
||
事务隔离级别：
|
||
- 未提交读(READ UNCOMMITTED):事务中的修改，即使未提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也称为脏读。
|
||
- 提交读(READ COMMITTED):一个事物从开始到提交之前，所做的任何修改对其他事物都是不可见的，这个级别有时候叫做不可重复读。这个级别上两次执行同样的查询会得到不一样的结果。
|
||
- 可重复读(REPEATABLE READ):解决了脏读问题，该级别保证了在同一个事务中多次读同样记录的结果是一致的，理论上无法解决幻读问题。幻读就是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入新的记录，当之前的事物再次读取该范围的记录时会产生幻行。
|
||
- 可串行化(SERIZLIZABLE):它通过强制事务串行执行，避免了前面说的幻读的问题。
|
||
|
||
脏读 不可重复读 幻读可能性 加锁读
|
||
未提交读 YES YES YES NO
|
||
提交读 NO YES YES NO
|
||
可重复读 NO NO YES NO
|
||
可串行化 NO NO NO YES
|
||
|
||
|
||
## 脏读、不可重复读和幻读
|
||
1. 脏读: 事务T1更新了一行记录内容，但并没有提交修改。事务T2读取更新后的行，然后T1执行回滚操作。读取了刚才所做的修改。现在T2读取的行就无效了。（一个事务读取了另一个事务未提交的数据）
|
||
2. 不可重复读：事务T1读取了一行记录，紧接着T2修改了T1刚才读取的那一行记录，然后T1又再次读取这行记录，发现与刚才读取的结果不同。
|
||
3. 幻读：事务T1读取一个结果集，然后T2事务在T1结果集范围内插入一行记录。然后T1再次对表进行检索，发现多了T2插入的数据。
|
||
|
||
|
||
|
||
## 数据库事务属性
|
||
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
|
||
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
|
||
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
|
||
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
|
||
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
|
||
|
||
## 数据库事务的几种粒度；
|
||
|
||
## 是否了解数据库的索引是如何实现的
|
||
### MyISAM索引实现
|
||
MyISAM索引使用了B+Tree作为索引结构，叶子结点的data域存放的是数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。主索引和辅助索引的存储结构没有任何区别。
|
||
### InnoDB索引实现
|
||
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
|
||
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据。
|
||
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。
|
||
|
||
### Memory索引实现
|
||
Memory索引适用于需要快速访问数据的场景，显示支持哈希索引。内部基于哈希表数据结构实现，只包含哈希值和行指针，对于每一行数据，存储引擎都会对所有的引擎列计算一个哈希码，在哈希表对应位置存放该行数据的指针或地址。为了解决多个hash冲突问题，哈希索引采用了链地址法来解决冲突问题。所以采用链表数组作为存储结构。这种索引结构十分紧凑，且具有很快的查询速度。但也存在一些问题，
|
||
1. 哈希表数据不是按照索引顺序存储的，所以无法用于排序。
|
||
2. 只能支持等值比较查询。
|
||
3. 存在冲突情况下查询速度变慢。
|
||
https://msdn.microsoft.com/zh-cn/library/dn133190.aspx
|
||
|
||
## 数据库连接池原理
|
||
### 背景
|
||
传统的数据库连接方式是，用户每次请求都要向数据库获取连接，而数据库连接的创建和关闭需要一定的开销。频繁的建立、关闭数据库，会极大的降低系统的性能，增大系统的开销，甚至成为系统的瓶颈。另外使用这种传统的模式，还必须管理数据库的每一个连接，以确保他们能正确关闭，如果出现程序异常而导致某些连接未能关闭。同时无节制的创建连接极易导致数据库服务器内存溢出。
|
||
|
||
### 原理
|
||
数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。以及一套连接使用、分配、管理策略，使得该连接池中的连接可以得到高效、安全的复用，避免了数据库连接频繁建立、关闭的开销。我们可以通过设定连接池最大连接数来防止系统无尽的与数据库连接。
|
||
|
||
### 开源java连接池:
|
||
现在很多Web服务器(Weblogic, WebSphere, Tomcat)都提供了DataSoruce的实现，即连接池的实现。通常我们把DataSource的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实现。
|
||
1.C3P0 :是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 池的DataSources 对象。参考网站: http://sourceforge.net/projects/c30/
|
||
2.Proxool :是一个Java SQL Driver驱动程序，提供了对你选择的其它类型的驱动程序的连接池封装。可以非常简单的移植到现存的代码中。完全可配置。快速，成熟，健壮。可以透明地为你现存的JDBC驱动程序增加连接池功能。 参考网站: http://proxool.sourceforge.net
|
||
3.Jakarta DBCP :是一个依赖Jakarta commons-pool对象池机制的数据库连接池.DBCP可以直接的在应用程序用使用。参考网站: http://jakarta.apache.org/commons/dbcp/
|
||
|
||
原理: http://www.uml.org.cn/sjjm/201004153.asp
|
||
实现: http://www.cnblogs.com/lihuiyy/archive/2012/02/14/2351768.html
|
||
|
||
|
||
连接池使用什么数据结构实现
|
||
链表
|
||
|
||
实现连接池: http://www.cnblogs.com/lihuiyy/archive/2012/02/14/2351768.html
|
||
四个表 记录成绩，每个大约十万条记录，如何找到成绩最好的同学
|
||
servlet的一些相关问题
|
||
webservice相关
|
||
|
||
## mysql有那些存储引擎，分别有什么特点
|
||
|
||
|
||
个人公众号(欢迎关注)：<br>
|
||
|
||
|
||
|
||
|
||
|