[聚合文章] PHP面试题5: MYSQL相关

整理编写不易，百度、Google、翻书， 回答验证，才弄了这么多，大多数都不是简单的回答，所以转载请注明出处：

一、mysql中出现中文乱码如何解决

mysql_query('SET NAMES UTF8')

二、试述Myisam与Innodb之间的区别

1.早期mysql默认采用myisam， 5.7以后默认innodb

2.MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

3.InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

4.Innodb支持外键， MyISAM不支持

5.Inonodb的主键范围更大，是MyISAM的两倍。

6.Innodb不支持全文索引，而MyISAM支持，

7.MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。

8.没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。

三、主键、外键和索引的区别

主键 -- 唯一标识， 不能重复，不能空，用来数据完整性， 一个表只有一个

外键 -- 表的外键是另一表的主键，可重复，可空值，用于关联，用于约束一致性，一个表可以有多个

索引 -- 没有重复值，可以有空值，用于提高排序查询速度，一个表可以有多个

四、char和varchar的区别

char是固定长度，varchar是可变长度

五、什么是存储过程

存储过程是编译好的SQL语句。因为系统在调用SQL语句的时候比较浪费时间，所以预先将一些基本的SQL语句进行预编译，然后再给代码取一个名字，在需要的时候直接调用就可以。

优点是： 1.效率高； 2.降低网络通信； 3.在一定程度上确保数据安全。

六、请阐述索引

索引是对数据库中的一个或多个列值得排序，帮助数据库获取数据的数据结构。

假如我们用类比的方法，数据库中的索引就像书的目录一样，当我们想找到某个知识点，我们可以直接目录中找而不是从书页中找。但是这也是索引的一个缺点，对数据库修改的时候要修改索引导致时间变多。

索引优点： 1.加快检索速度； 2.唯一索引确保每行数据的唯一性； 3.在使用索引的过程可以优化隐藏器，提高系统西鞥能

索引缺点： 1.插入、删除、维护速度下降。

七、清阐述事务

事务是并发控制的基本单位。事务就是一系列的操作，这些操作要么执行，要么不执行。

事务具有以下特性: 

1.原子性 - 事务中的操作要么全部完成，要么失败

2.一致性 - 一个成功的事务应该将数据写入到数据库，否则就要回滚到最初的状态。

3.隔离性 - 并发访问和修改的独立

4.持久性 - 事务结束应该将事务的处理结构存储起来

事务的语句

开始事务: BEGIN TRANSCATION

提交事务：COMMIT TRANSCATION

回滚事务：ROLLBACK TRANSCATION

八、Mysql有几种事务提交方式

3种

1.自动提交

SET AUTOCOMMIT=1；

不做修改，默认就是这个

用户发出每条SQL语句，mysql都会自动开始一个事务，并且在执行后自动进行提交操作来完成这个事务。也就是说一个SQL语句就是一个事务。

2.显式提交

以BEGIN TRANSCATION命令开始一个事务。

3.隐式提交

SET AUTOCOMMIT=0；

任何DML语句都会开始一个事务，直到你使用commit或者rollback才会结束

九、如何保存事务的快照

save point transcation_point_name;

十、数据库事务并发控制中会遇到哪些问题

1.数据丢失

多个用户同时对数据库进行更改操作，有可能会发生更改丢失，其原因是一个用户的更改被另一个用户覆盖了。

2.未提交的数据读取（错读）

一个用户读取另一用户未提交的数据修改结果，导致错误产生，这个情况又叫脏数据。

3.不一致的读（不可重复读）

查询期间，用户受到干扰，两个相同的查询返回不同的结果，就出现了不一致的数据读取。

4.幻影读（假读）

当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围内，被称为幻影读或假读。

十一、数据库事务隔离级别

1.READ UNCOMMITTED 未提交的读取

限制最少，允许读取已被其他用户读取但未提交确定的数据。

优点是： 在此环境下锁处理的语句可便面并发控制所增加的开销，一般用于产生类似信息的事务中。

缺点是： 数据丢失、错读、不可重复读、幻影读都可能会碰到

2.READ COMMITTED 提交读取

比READ UNCOMMITTED更高一层，通过隐藏未提交的变化解决了读脏数据的并发事务问题， 但无法解决不可重复读和幻影读的问题。

3.REPEATABLE READ 可重复读

隔离界别在READ COMMITTED之上，在此隔离级别下，用SELECT命令读取的数据在整个命令执行过程红由DBMS锁定， 不会被更改。

会想用系统的性能

4.SERIALIZABLE 可串行化

最大限制级别，指的是将事务以一种顺序方式连接起来，可以防止一个事务影响其他事务。严重影响系统的性能

十二、如何查看Mysql系统默认事务隔离级别

select @@global.tx_isolation;

十三、如何查看MYsql当前回话的事务隔离级别

select @tx_isocation

十四、如何设置更改Mysql系统默认事务隔离级别

SET global transcation isolation level readcommitted

十五、如何设置更改Mysql当前回话的事务隔离级别

SET session transction isolation level readcommitted

十六、按对数据库的操作类型划分，MYSQL事务锁有几种

1.读锁 又叫共享锁

共享锁锁定的资源可以被其他用户读取，但不能修改，在select命令执行时，数据库通常会对对象进行共享锁锁定，一般情况下，加共享锁的数据也被读取完毕后，共享锁就会立即被释放。

2.写锁 又叫排它锁、独占锁

独占锁锁定的资源只允许进行锁定操作的程序使用，对它任何其它操作均不会被接受，执行数据更新命令时，即INSEET、UPDATE、DELETE时，数据库会自动使用写锁。但当对象上有其它锁存在时，无法对其加写锁，直到更新操作结束，写锁才能被释放。

此外MYSQL没有SQL SERVER那种更新锁

十八、MYSQL事务锁可以操作的对象有哪些

1.表级锁

MyISAM默认提供的就是表级锁，表锁由两种： 表共享锁、表独占锁

表级锁的特性：

1.对表的读操作不会阻塞其它用户对同一表的读操作，但是会阻塞对同一表的写操作；

2.对表的写操作，会阻塞其它用户对同一表的所有读与写的操作。

3.表的读操作和写操作，以及写操作之间是串行的，当一个线程获得对一个表的写锁后，只有持有锁的线程才可以对表进行更新操作，其它线程读、写都会等待，直到锁被释放为止。

2.行级锁

行级锁不是mysql自身实现的，是由mysql其它引擎实现的，例如InnoDB以及NDBCluster.

行级锁同样分为两种： 共享锁 ， 排它锁。

Innodb为了让行级锁和表级锁共存，Innodb也同事使用了意向锁的概念(表级锁), 也就有了 意向共享锁 和 意向排它锁 两种。

意向锁是Innodb自动加的，不需要用户干预，

对于INSERT、UPDATE以及DELETE，Innodb会自动给涉及数据加排它锁。

对于普通SELECT语句，Innodb不会加任何锁，事务可以通过以下语句给记录集加共享锁或排它锁：

共享锁： select * from table where .... LOCK IN SHARE MODE

排它锁： select * from table where .... FOR UPDATE

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

十九、如何加表锁

MyISAM在执行查询语句(SELECT)前，会自动给所有涉及的表加表锁，在执行更新操作前(INSERT UPDATE DELETE)前，会自动给涉及的表加写锁，这个过程并不需要用户干预。因此用户一般不需要直接用LOCK TABLE命令给MyISAM显式的加表锁。

LOCK TABLES table_name WRITE

UNLOCK TABLES;

二十、如何优化表锁

1.查询表级锁争用情况

mysql> show status like 'table%';

+----------------------------+---------+

| Variable_name              | Value   |

+----------------------------+---------+

| Table_locks_immediate      | 100     |

| Table_locks_waited         | 11      |

+----------------------------+---------+

Table_locks_immediate : 产生表级锁定的次数

Table_locks_waited ： 出现表级锁定征用而等待的次数

可以根据这两个参数来分析为什么有较多的锁定资源争用。

2.缩短锁定时间

让Query的时间尽可能的缩短：

a.尽量减少大而复杂的query，可以拆分成多个小query分部进行；

b.尽量建立足够高效的索引，让数据检索更迅速

c.尽量让MyISAM存储引擎表值存放必要的信息，控制字段类型

d.利用合适的机会优化MyISAM表数据文件

3.分离能并行的操作

打开 concurrent_insert，值可以是0|1|2

concurrent_insert=2 无论MyISAM表中有没有空洞，都允许在表尾并行插入记录；

concurrent_insert=1 如果MyISAM表中没有空洞，MyISAM允许一个进程在读表的同时，另一个进程从表尾插入记录。 这也是mysql默认设置。

concurrent_insert=0 不允许从表尾插入

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

4.合理利用读写优先级

通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置；

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

二十一、如何给InnoDB加行锁

InnoDB行锁是通过给索引上的索引项加锁实现的，只有通过索引检索数据，InnoDB才使用行级锁，否则，InnoDB使用表锁。

1.在不通过索引条件查询时，使用的是表锁，不是行锁；

2.访问不同行的激励，但是是相同的索引键，会出现锁冲突；

3.当表有多个索引的时候，不同事务可以使用不同的索引来锁定不同的行。另外，无论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁对数据加锁。

4.即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引

二十二、请说明行级锁的优缺点

优点：

1.当在许多线程中访问不同的行时只存在少量锁定冲突

2.回滚时只要少量的更改

3.可以长时间锁定单一的行

缺点:

1.比表级锁占用更多的内存

2.当在表的大部分中使用时，比表级锁定慢，因为你必须获取更多的锁；

3.如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比表级锁明显慢的多；

4.用高级别锁定，通过支持不同的类型锁定，你也可以很容易的调节应用程序，因为其成本小于行级锁定。

二十三、在哪些条件下表级锁优于行级锁

1.表的大部分数据用于读取

2.对严格的关键字进行读取或更新，你可以更新或删除可以用单一的读取的关键字来提取的一行；

3.UPDATE table_name SET column=value WHERE unique_key_col=key_value;

4.DELETE FROM table_name WHERE unique_key_col=key_value;；

5.SELECT结合并行的INSERT语句，并且只有很少的UPDATE和DELETE语句；

6.在整个表上有很多扫描及GROUP_BY查询，没有任何写操作；

二十四、表级锁和行级别锁对比有什么区别

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

二十五、什么是乐观锁、什么是悲观锁

乐观锁和悲观锁是从程序员角度看的

乐观锁 ：指在处理数据时，不需要在应用程序的代码中做任何事情，就可以直接在记录上加锁，即完全靠数据库来管理所的工作。

悲观锁： 不采用数据库的自动管理，需要程序员直接管理数据或对象上的枷锁处理，并负责获取或放弃正在使用的数据上的任何锁。

二十六、什么是死锁

死锁是在多用户或多进程状况下，为使用同一资源而产生的无法解决的争用状态。

通俗的讲，就是两个用户各占用一个资源，两人都想使用对方的资源，但同时又不愿放弃自己的资源，就一直等待对方放弃资源，如果不进行干涉，就会一直持续下去。

二十七、如何预防数据库死锁

1.尽量避免并发地执行涉及到修改数据的语句；

2.要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行；

3.预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁，如不同的过程在事务内部对对象的更新执行顺讯赢尽量保持一致

4.每个事务的时间不可太长，对程序段长的事务可以考虑将其分隔为几个事务；

二十八、delete、drop、truncate的区别

1.delete和truncate只删除表的数据不删除表的结构，drop删除结构和数据

2.速度drop>truncate>delete

3.删除部分数据时，使用delete带where条件

4.保留表结构删除所有数据时用truncate

二十九、请表述数据库的三大范式

第一范式 ： 字段具有原子性，不可再分

第二范式 ： 表中没列都和主键有关

第三范式 ： 每列都和主键直接相关，而不是间接相关

三十、SQL语句应考虑哪些安全性

1.防止SQL注入，对特殊字符串进行转义，过滤或使用预编译的SQL语句绑定变量；

2.最小权限原则，特别是不要用root账户，为不同类型的动作或组使用不同的账户;

3.当SQL运行出错时，不要把数据库返回的错误信息全部显示给用户，以防泄漏服务器和数据库相关的信息。