【原】MYSQL面试常考知识点总结

数据库的三范式

     第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

     第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；  

     第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

SQL执行顺序

     关键字：select、from、join、on、where、group by、having、order by、distinct执行顺序

     from > join > on > where > group by > having > select > distinct > order by

UNION/ALL、EXCEPT/ALL和INTERSECT/ALL

     UNION/ALL：组合多个结果表，并消去表中重复行，和ALL一起使用时，不消除重复行。

     EXCEPT/ALL：在table1中但不在table2中的行并消除重复行，和ALL一起使用时，不消除重复行。

     INTERSECT/ALL：包括table1和table2中都有的行并消除重复行，和ALL一起使用时，不消除重复行。

内连接、外连接、笛卡儿积(交叉连接)

     内连接：结果仅包含符合连接条件的两表中的行

     外连接：结果包含符合条件的行，同时包含不符合条件的行（分为左外连接、右外连接和全外连接）

     左外连接：左表全部行+右表匹配的行，如果左表中某行在右表中没有匹配的行，则显示NULL。

     右外连接：右表全部行+左表匹配的行。

     全外连接：全外连接：不管匹配不匹配，全部显示出来。

     交叉连接：返回左表中所有行与右表中所有行的组合，也称笛卡尔积。

VARCHAE和CHAR的区别

     CHAR：指定长度，如果一个数据实际长度比设定长度短，那么它将按照设定（最长）长度储存，不足部分，填补空格。适用于固定长度的字段，如性别、手机号等。

     VARCHAE：指定最大长度，但该长度可变，即如果数据的实际长度比设定长度短，那么它将按照实际长度储存，而不占用剩余的空间。 适用于非固定长度的字段，如姓名、城市名等。

DROP，DELETE与TRUNCATE的区别

     DROP直接删掉表 。 

     TRUNCATE删除表中数据，再插入时自增长id又从1开始 。 

     DELETE删除表中数据，可以加WHERE字句。

数据库事务(ACID)

     原子性（Atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

     一致性（Consistency）数据库总是从一个一致性的状态转换到另一个一致性的状态。

     隔离性（Isolation）一个事务所做的修改在最终提交以前，对其他事务是不可见的。

     持久性（Durability）一旦事务提交，则其所做的修改不会永久保存到数据库。

数据库的乐观锁和悲观锁

     悲观锁：对数据被外界修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。（悲观锁的实现，往往依靠数据库提供的锁机制）

     乐观锁：大多是基于数据版本 （ Version ）记录机制实现。

     即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来 实现。 读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提 交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据 版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据

脏读、虚读和不可重复读

     脏读：一个事务读取到了另外一个事务没有提交的数据；(修改的数据还未提交就被另一个事务使用这个数据)

     不可重复读：在同一事务中，两次读取同一数据，得到内容不同；（两次读取同一数据之间，另一个事务对数据进行了修改）

     虚度：同一事务中，用同样的操作读取两次，得到的记录数不相同；(两次相同的操作之间，另一个事务对数据进行了新增或删除)

MySQL中的四种事务隔离级别

     序列化 (Serializable)：可避免脏读、不可重复读、幻读的发生。

     可重复读 (Repeatable read)：可避免脏读、不可重复读的发生。

     读已提交(Read committed)：可避免脏读的发生。

     读未提交 (Read uncommitted)：最低级别，任何情况都无法保证。

MVCC(多版本并发控制)

     MVCC最大的好处：读不加锁，读写不冲突。

     1.多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读

     2.当MVCC数据库更新一条数据时，不会直接重写原始的数据，而是修改新创建的数据副本。因此会有多个版本的数据被保存下来。每个事务看到的数据版本依赖于隔离级别的实现。MVCC里最通用的隔离级别的实现就是快照隔离。在快照隔离的情况下，事务只会获取到数据的在事务开始前的状态。

MYSQL主从复制

概念解释     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表中执行！！！

数据库SQL优化

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 = 03. 应尽量避免在 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*29. 应尽量避免在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. 尽量避免大事务操作，提高系统并发能力。

30. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

聚集索引和非聚集索引

聚集索引     聚集索引就是存放的物理顺序和列中的顺序一样。一般设置主键索引就为聚集索引。     一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是平衡树结构，换句话说，就是整个表就变成了一个索引，也就是所谓的聚集索引。 这就是为什么一个表只能有一个主键， 一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成索引（平衡树）的格式放置。     上图就是带有主键的表（聚集索引）的结构图。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句：select * from table where id = 1256     首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。如下图     然而， 事物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。非聚集索引    讲完聚集索引 ， 接下来聊一下非聚集索引， 也就是我们平时经常提起和使用的常规索引。    非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图     每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。  非聚集索引和聚集索引的区别在于：     通过聚集索引可以一次查到需要查找的数据， 而通过非聚集索引第一次只能查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。     聚集索引一张表只能有一个，而非聚集索引一张表可以有多个。

索引是什么？有什么作用以及优缺点？

索引是什么？

     数据库索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

索引原理

     索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。

磁盘IO与预读

     考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的两大类型Hash索引和B树索引

     Hash类型的索引：查询单条快，范围查询慢。

     B树类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）。

  不同的存储引擎支持的索引类型也不一样

     InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引。

     MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引。

     Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引。

Hash索引的限制

     1.由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。

     2.不能使用hash索引排序。

     3.Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。

     4.Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

索引数据结构为什么采用B+树

     为了尽量减少I/O操作，磁盘读取每次都会预读，大小通常为页的整数倍。即使只需要读取一个字节，磁盘也会读取一页的数据(通常为4K)放入内存，内存与磁盘以页为单位交换数据。因为局部性原理认为，通常一个数据被用到，其附近的数据也会立马被用到。

     B树：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。

     B+树：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。

     由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。

     B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

索引的优缺点

   优点：

     1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

     2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

     3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

     4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

     5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

   缺点：

     1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

     2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

     3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

哪些字段适合建索引

     1.在经常需要搜索的列上，可以加快搜索的速度；

     2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

     3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

     4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

     5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

     6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

哪些字段不适合建索引

     1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

     2.对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

     3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

     4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

数据库引擎MyISAM和InnoDB
MyISAMInnoDB事务支持   不支持支持锁的粒度   表锁行锁存储容量   没有上限64TB哈希索引   不支持支持全文索引   支持以前不支持，现在支持外键   不支持支持

     InnoDB是非事务的存储引擎；适合用于频繁查询的应用；表锁，不会出现死锁；适合小数据，小并发

     InnoDB是支持事务的存储引擎；合于插入和更新操作比较多的应用；设计合理的话是行锁（最大区别就在锁的级别上）；适合大数据，大并发。

     MyISAM表的数据文件和索引文件是自动分开的；InnoDB的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

为什么MyISAM会比InnoDB的查询速度快

     1.InnoDB要缓存数据块，而MyISAM只缓存索引块

     2.InnoDB寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比InnoDB要快

     3.InnoDB还需要维护MVCC一致

慢查询日志

慢查询日志概念     MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。慢查询日志相关参数slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.logslow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.loglong_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。慢查询日志配置     默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启。mysql> show variables like '%slow_query_log%';+---------------------+------------------------------------------+| Variable_name       | Value                                    |+---------------------+------------------------------------------+| slow_query_log      | OFF                                      || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------+------------------------------------------+2 rows in set (0.00 sec) mysql> set global slow_query_log=1;Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%slow_query_log%';+---------------------+------------------------------------------+| Variable_name       | Value                                    |+---------------------+------------------------------------------+| slow_query_log      | ON                                       || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------+------------------------------------------+2 rows in set (0.00 sec)#使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）     修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。slow_query_log =1slow_query_log_file=/usr/local/mysql/data/localhost-slow.logmysql> show variables like 'slow_query%';+---------------------+---------------------+| Variable_name       | Value               |+---------------------+---------------------+| slow_query_log      | ON                  || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------+---------------------+2 rows in set (0.00 sec) mysql> #慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log日志分析工具mysqldumpslow     在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow得到返回记录集最多的10个SQL。mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log 得到访问次数最多的10个SQLmysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log 得到按照时间排序的前10条里面含有左连接的查询语句。mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

XSS、SQL和CSRF攻击

     XSS（Cross Site Script，跨站脚本攻击）是向网页中注入恶意脚本在用户浏览网页时在用户浏览器中执行恶意脚本的攻击方式。

        防止方法：做数据校验、做标签转换，如：空格 转化为  。

     SQL注入攻击是注入攻击最常见的形式，当服务器使用请求参数构造SQL语句时，恶意的SQL被嵌入到SQL中交给数据库执行；

        防止方法：输入校验、sql不要动态拼接，用参数化的sql。

     CSRF攻击（Cross Site Request Forgery，跨站请求伪造）是攻击者通过跨站请求，以合法的用户身份进行非法操作（如转账或发帖等）。

     CSRF的原理是利用浏览器的Cookie或服务器的Session，盗取用户身份；

        防止方法：使用post代替get，因为post不会被缓存；每次清除掉cookie。