金木水火土查询表第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
EXCEPT/ALL：在table1中但不在table2中的行并消除重复行，和ALL一起使用时，不消除重复行。
INTERSECT/ALL：包括table1和table2中都有的行并消除重复行，和ALL一起使用时，不消除重复行。
CHAR：指定长度，如果一个数据实际长度比设定长度短，那么它将按照设定（最长）长度储存，不足部分，填补空格。适用于固定长度的字段，如性别、手机号等。
VARCHAE：指定最大长度，但该长度可变，即如果数据的实际长度比设定长度短，那么它将按照实际长度储存，而不占用剩余的空间。 适用于非固定长度的字段，如姓名、城市名等。
原子性（Atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
悲观锁：对数据被修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。（悲观锁的实现，往往依靠数据库提供的锁机制）
即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来 实现。 读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提 交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据 版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据
脏读：一个事务读取到了另外一个事务没有提交的数据；(修改的数据还未提交就被另一个事务使用这个数据)
不可重复读：在同一事务中，两次读取同一数据，得到内容不同；（两次读取同一数据之间，另一个事务对数据进行了修改）
虚度：同一事务中，用同样的操作读取两次，得到的记录数不相同；(两次相同的操作之间，另一个事务对数据进行了新增或删除)
1.多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读
2.当MVCC数据库更新一条数据时，不会直接重写原始的数据，而是修改新创建的数据副本。因此会有多个版本的数据被保存下来。每个事务看到的数据版本依赖于隔离级别的实现。MVCC里最通用的隔离级别的实现就是快照隔离。在快照隔离的情况下，事务只会获取到数据的在事务开始前的状态。
概念解释 MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方案，特点是异步复制。 MySQL Replication 就是从服务器拉取主服务器上的二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来数据的一致性。 主从复制原理 1.master(主)在执行sql之后，记录二进制log文件（bin-log）。 2.slave(从)连接master，并从master获取binlog，存于本地relay-log中，然后从上次记住的起执行SQL语句，一旦遇到错误则停止同步。 简记：MySQL的主从复制，实际上就是Master记录自己的执行日志binlog，然后发送给Slave，Slave解析日志并执行，来实现数据复制。 主服务器流程分析 首先bin-log日志文件加锁，然后读取更新的操作，读取完毕以后将锁掉，最后将读取的记录发送给从服务器。 从服务器流程分析 在一次主从复制过程中需要用到三个线程：Binlog dump 线程、Slave I/O 线程和Slave SQL线程，其中Binlog dump 线程在主服务器，剩下的两个线程是在从服务器工作的。 这两个线程在从服务器的工作流程如下图所示： 如何提高Mysql主从复制的效率？ 1.master 端 master端有2个参数可以控制。 Binlog_Do_DB : 设定哪些数据库需要记录Binlog。 Binlog_Ignore_DB : 设定哪些数据库不要记录Binlog。 2.slave 端 slave端有6个参数可以控制。 Replicate_Do_DB : 设定须要复制的数据库，多个DB用逗号分隔。 Replicate_Ignore_DB : 设定可以忽略的数据库。 Replicate_Do_Table : 设定须要复制的Table。 Replicate_Ignore_Table : 设定可以忽略的Table。 Replicate_Wild_Do_Table : 功能同Replicate_Do_Table，但可以带通配符来进行设置。 Replicate_Wild_Ignore_Table : 功能同Replicate_Ig-nore_Table，可带通配符设置。
分表 对于访问极为频繁且数据量巨大的单表来说，我们首先要做的就是减少单表的记录条数，以便减少数据查询所需要的时间，提高数据库的吞吐，这就是所谓的分表！ 将原有的单表分为256个表 算法 ：user_id%256 拆分后表的数量一般为2的n次方，就是拆分成256张表的由来！ 分库 分表能够解决单表数据量过大带来的查询效率下降的问题，但是，却无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问，对数据库进行拆分，从而提高数据库写入能力，这就是所谓的分库! 将原有的单库分为256个库 算法 ：user_id%256 分库分表 有时数据库可能既面临着高并发访问的压力，又需要面对海量数据的存储问题，这时需要对数据库既采用分表策略，又采用分库策略，以便同时扩展系统的并发处理能力，以及提升单表的查询性能，这就是所谓的分库分表。 一种分库分表的由策略如下： 1. 中间变量 = user_id % (分库数量 * 每个库的表数量) 2. 库 = 取整数 (中间变量 / 每个库的表数量) 3. 表 = 中间变量 % 每个库的表数量 举例： 假设将原来的单库单表order拆分成256个库，每个库包含1024个表，那么按照前面所提到的由策略，对于user_id=262145 的访问，由的计算过程如下： 1. 中间变量 = 262145 % (256 * 1024) = 1 2. 库 = 取整 (1/1024) = 0 3. 表 = 1 % 1024 = 1 这就意味着，对于user_id=262145 的订单记录的查询和修改，将被由到第0个库的第1个order_1表中执行！！！
1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。 如：select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是 varchar这样的变长字段， null 不占用空间。
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num = 0 3. 应尽量避免在 where 子句中使用 != 或 操作符，否则将引擎放弃使用索引而进行全表扫描。 4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。 如：select id from t where num=10 or Name = admin可以这样查询：select id from t where num = 10union allselect id from t where Name = admin 5. in 和 not in 也要慎用，否则会导致全表扫描。 如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num) 6. 下面的查询也将导致全表扫描 select id from t where name like %abc% 若要提高效率，可以考虑全文检索。 7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num = @num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num = @num 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。 如：select id from t where num/2 = 100应改为：select id from t where num = 100*2 9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。 如：select id from t where substring(name,1,3) = abc -name以abc开头的idselect id from t where datediff(day,createdate,2005-11-30) = 0 -2005-11-30 --生成的id应改为:select id from t where name like abc%select id from t where createdate = 2005-11-30 and createdate 2005-12-1 10. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 12. 不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t() 13. Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。 14. 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。 15. select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。 16. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。 17. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。 18. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。 19. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 20. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。 21. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。 22. 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。 23. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 24. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 25. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 26. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。 27. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。 28. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 29. 尽量避免大事务操作，提高系统并发能力。
聚集索引 聚集索引就是存放的物理顺序和列中的顺序一样。一般设置主键索引就为聚集索引。 一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是平衡树结构，换句话说，就是整个表就变成了一个索引，也就是所谓的聚集索引。 这就是为什么一个表只能有一个主键， 一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成索引（平衡树）的格式放置。 上图就是带有主键的表（聚集索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句： select * from table where id = 1256 首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 这里不平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。如下图 然而， 事物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。 非聚集索引 讲完聚集索引 ， 接下来聊一下非聚集索引， 也就是我们平时经常提起和使用的常规索引。 非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个的索引结构，每个索引（非聚集索引）互相之间不存在关联。如下图 每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。 非聚集索引和聚集索引的区别在于： 通过聚集索引可以一次查到需要查找的数据， 而通过非聚集索引第一次只能查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。 聚集索引一张表只能有一个，而非聚集索引一张表可以有多个。
数据库索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。
考虑到磁盘IO常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
B树类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）。
1.由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录常迅速的，不会对性造成太大的影响。
为了尽量减少I/O操作，磁盘读取每次都会预读，大小通常为页的整数倍。即使只需要读取一个字节，磁盘也会读取一页的数据(通常为4K)放入内存，内存与磁盘以页为单位交换数据。因为局部性原理认为，通常一个数据被用到，其附近的数据也会立马被用到。
B树：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。
B+树：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。
由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的，这样就降低了数据的速度。
索引是建立在数据库表中的某些列的。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。
5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的速度和增大了空间需求。
2.对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
数据库引擎MyISAM和InnoDBMyISAMInnoDB事务支持 不支持支持锁的粒度 表锁行锁存储容量 没有上限64TB哈希索引 不支持支持全文索引 支持以前不支持，现在支持外键 不支持支持
InnoDB事务的存储引擎；适合用于频繁查询的应用；表锁，不会出现死锁；适合小数据，小并发
InnoDB是支持事务的存储引擎；合于插入和更新操作比较多的应用；设计合理的话是行锁（最大区别就在锁的级别上）；适合大数据，大并发。
MyISAM表的数据文件和索引文件是自动分开的；InnoDB的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。
2.InnoDB寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比InnoDB要快
XSS（Cross Site Script，跨站脚本）是向网页中注入恶意脚本在用户浏览网页时在用户浏览器中执意脚本的方式。
SQL注入是注入最常见的形式，当服务器使用请求参数构造SQL语句时，恶意的SQL被嵌入到SQL中交给数据库执行；
CSRF（Cross Site Request Forgery，跨站请求伪造）是者通过跨站请求，以的用户身份进行非法操作（如转账或发帖等）。
网友评论 ()条 查看