sql必会基础4

091 数据库死锁概念

多数情况下，可以认为如果一个资源被锁定，它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。简单的说，进程A等待进程B释放他的资源，B又等待A释放他的资源，这样就互相等待就形成死锁。

虽然进程在运行过程中，可能发生死锁，但死锁的发生也必须具备一定的条件，死锁的发生必须具备以下四个必要条件。

1）互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。

2）请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。

3）不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。

4）环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。

（2）避免事务中的用户交互。

（3）保持事务简短并在一个批处理中。

（4）使用低隔离级别。

（5）使用绑定连接。

092 数据库有几种数据保护方式（AAA）

实现数据库安全性控制的常用方法和技术有：用户标识和鉴别；存取控制；视图机制；审计；数据加密；

093 union和union all 的区别以及使用

Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）；

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式。

094 mysql的备份命令是什么

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=... -C databasename

095 在mysql服务器运行缓慢的情况下输入什么命令能缓解服务器压力

第一步 检查系统的状态

通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲，这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外，还应观注那些占用系统资源(cpu、内存)的进程。

1.1　使用sar来检查操作系统是否存在IO问题

1.2　使用vmstat监控内存 cpu资源

1.3　磁盘IO问题，处理方式：做raid10提高性能

1.4　网络问题，telnet一下MySQL对外开放的端口，如果不通的话，看看防火墙是否正确设置了。另外，看看MySQL是不是开启了skip-networking的选项，如果开启请关闭。

第二步 检查mysql参数

2.1　max_connect_errors

2.2　connect_timeout

2.3　skip-name-resolve

2.4　slave-net-timeout=seconds

2.5　master-connect-retry

第三步 检查mysql 相关状态值

3.1　关注连接数

3.2　关注下系统锁情况

3.3　关注慢查询（slow query）日志

096 怎么导出表结构?

1.导出整个数据库

mysqldump -u用户名 -p密码 数据库名 > 导出的文件名

C:\Users\jack> mysqldump -uroot -pmysql sva_rec > e:\sva_rec.sql

2.导出一个表，包括表结构和数据

mysqldump -u用户名 -p 密码 数据库名 表名> 导出的文件名

C:\Users\jack> mysqldump -uroot -pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql

3.导出一个数据库结构

C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec > e:\sva_rec.sql

4.导出一个表，只有表结构

mysqldump -u用户名 -p 密码 -d数据库名 表名> 导出的文件名

C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec date_rec_drv> e:\date_rec_drv.sql

5.导入数据库

常用source 命令

进入mysql数据库控制台，

如mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql

097 正常登入MYSQL后使用什么命令查看其进程是否正常

输入show processlist;

如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。

098 mysql远程连接命令

一、MySQL 连接本地数据库，用户名为“root”，密码“123”（注意：“-p”和“123” 之间不能有空格）

C:>mysql -h localhost -u root -p123

二、MySQL 连接远程数据库（192.168.0.201），端口“3306”，用户名为“root”，密码“123”

C:>mysql -h 192.168.0.201 -P 3306 -u root -p123

099 mysql主从用什么方式传输日志

MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

100 数据库的备份方式

1、完全备份，这是大多数人常用的方式，它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，一般推荐一周做一次完全备份。

2、事务日志备份，事务日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。为了使数据库具有鲁棒性，推荐每小时甚至更频繁的备份事务日志。

3、差异备份也叫增量备份。它是只备份数据库一部分的另一种方法，它不使用事务日志，相反，它使用整个数据库的一种新映象。它比最初的完全备份小，因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。

4、文件备份，数据库可以由硬盘上的许多文件构成。如果这个数据库非常大，并且一个晚上也不能将它备份完，那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储，所以这种备份不是很常用。

101 查看mysql数据库是否支持innodb

查看mysql的存储引擎：show plugins;

如何在mysql某个表中随机抽取10条记录

1.通过MYSQL内置的函数来操作，具体SQL代码如下：

SELECT * FROM tablename ORDER BY RAND() LIMIT 10

2.不要将大量的工作给数据库去做，这样会导致数据库在某一集中并发时间内锁死并阻塞。建议通过PHP随机生成一下1-X(总行数)之间的数字，然后将这10个随机数字作为查询条件，具体语句如：

SELECT * FROM tablename where ID in (2,8,4,11,12,9,3,1,33)

可能你还要进行重复排除，并且需要在程序中将10个值串联并连接进入SQL语句中。

102 如何查看连接mysql的当前用户。

show full processlist，在user字段中查看有哪些用户

103 写出mysql怎么修改密码?

方法一: (适用于管理员或者有全局权限的用户重设其它用户的密码)

进入命令行模式

mysql -u root -p

mysql>use mysql;

mysql> UPDATE user SET password=PASSWORD(“new password”) WHERE user=’username’;

mysql> FLUSH PRIVILEGES;

mysql> quit;

方法二:

mysql -u root -p

mysql>use mysql;

mysql> SET PASSWORD FOR username=PASSWORD(‘new password’);

mysql> QUIT

方法三:

mysqladmin -u root “old password” “new password”

注：new password请输入你想要设置的密码。

104 MySQL怎么修复损坏的表?

有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。

105 简单叙述一下MYSQL的优化（重点）

1.数据库的设计：尽量把数据库设计的更小的占磁盘空间.

1) 尽可能使用更小的整数类型.(mediumint就比int更合适).

2) 尽可能的定义字段为not null,除非这个字段需要null.

3) 如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.

4) 表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.

5) 只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。

6) 所有数据都得在保存到数据库前进行处理。

7) 所有字段都得有默认值。

8) 在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。

2.系统的用途

1) 尽量使用长连接.

2) explain复杂的SQL语句。

3) 如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.

4) LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.

URL：http://www.bianceng.cn/database/MySQL/201610/50458.htm

5) 如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.

6) 能使用STORE PROCEDURE 或者 USER FUNCTION的时候.

7) 在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据，这比单纯的insert快好多.

8) 经常OPTIMIZE TABLE 来整理碎片.

9) 还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

3.系统的瓶颈

1) 磁盘搜索。并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.

2) 磁盘读写(IO)。可以从多个媒介中并行的读取数据。

3) CPU周期。数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。

4) 内存带宽。当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.

106 如何确定有哪些存储引擎可用？

mysql> show engines; 显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎。

107 MYSQL数据库设计数据类型选择需要注意哪些地方?（重点）

VARCHAR和CHAR类型，varchar是变长的，需要额外的1-2个字节存储，能节约空间，可能会对性能有帮助。但由于是变长，可能发生碎片，如更新数据；

使用ENUM代替字符串类型，数据实际存储为整型。

字符串类型

要尽可能地避免使用字符串来做标识符，因为它们占用了很多空间并且通常比整数类型要慢。特别注意不要在MYISAM表上使用字符串标识符。MYISAM默认情况下为字符串使用了压缩索引（Packed Index），这使查找更为缓慢。据测试，使用了压缩索引的MYISAM表性能要慢6倍。

还要特别注意完全‘随机’的字符串，例如由MD5（）、SHA1（）、UUID（）产生的。它们产生的每一个新值都会被任意地保存在很大的空间范围内，这会减慢INSERT及一些SELECT查询。

1）它们会减慢INSERT查询，因为插入的值会被随机地放入索引中。这会导致分页、随机磁盘访问及聚集存储引擎上的聚集索引碎片。

2)它们会减慢SELECT查询，因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。

3）随机值导致缓存对所有类型的查询性能都很差，因为它们会使缓存赖以工作的访问局部性失效。如果整个数据集都变得同样“热”的时候，那么把特定部分的数据缓存到内存中就没有任何的优势了。并且如果工作集不能被装入内存中，缓存就会进行很多刷写的工作，并且会导致很多缓存未命中。

如果保存UUID值，就应该移除其中的短横线，更好的办法是使用UHEX（）把UUID值转化为16字节的数字，并把它保存在BINARY（16）列中。

108 mysql、oracle默认端口号

3306、1521

109 innodb的事务与日志的实现方式。

（1）有多少种日志

错误日志：记录出错信息，也记录一些警告信息或者正确的信息

慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

二进制日志：记录对数据库执行更改的所有操作

查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

（2）日志的存放形式

（3）事务是如何通过日志来实现的，说得越深入越好。

在Innodb存储引擎中，事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现 的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存 储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过 innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方 式”， innodb通过此方式来保证事务的完整性。也就意味着磁盘上存储的数据页和内存缓冲池上面的页是不同步 的，是先写入redo log，然后写入data file，因此是一种异步的方式。

隔离性： 通过锁实现

原子性、一致性和持久性是通过redo和undo来完成的。