Mysql部分

 Mysql部分

***三大范式

   第一范式：保证数据库中的表每一列都是不可以再分的原子列;

   第二范式；在满足第 1NF 的基础上，确保表中的每列都和主键相关;

   第三范式： 在满足第 2NF 基础上，属性不能传递依赖于主属性（确保每列都和主键列直接相关,而不是间接相关）。

 

***数据库的分页方式

   mysql 用 limit 进行分页，

   oracle 通过 rownum 以嵌套的方式进行分面，

   sqlserver 通过 top 关键字分页。

 

***DDL、DML、DCL 的区别

   DML(Data Manipulation Language)数据操纵语言；对数据库中的数据进行一些简单操作，如 insert,delete,update,select 等.

   DDL(Data Definition Language)数据定义语言：对数据库中的某些对象进行操作， 如 create,alter,drop,truncate,(create index)等.

   DCL(Data Control Language)数据控制语言， 如 commit、 rollback、grant、 invoke等

   DML 和 DDL 区别：

      1.DML 主要侧重于应用级开发，程序中是可以手动控制事务的开启、提交和回滚的。

      2.DDL 主要侧重于服务端管理，不需要考虑事务操作。

 

***Mysql的优越性

   mysql 是一个小型开源的关系型数据库管理系统，开发者为瑞典MySQL AB 公司，先被 SUN 收购、后来 SUN 又被 oracle 收购。Mysql 优点：

   1、相对于 oracle、db2、sqlserver 等大型数据库来说，mysql 是一个小型的关系型数据库，安全简单、配置灵活、容易移植、效率高。

   2、支持标准的 SQL 语句标准， 可以很容易的和其它数据库进行转换；

   3、可以高效处理千万级别数据，而且成本低，非常适合中小企业使用；

   4、丰富网络资源、开发论坛利于学习和员工培训。

 

***Mysql常用连接方式

   内连接(自然连接)：显示关联的两个表中相匹配的数据。

   左外连接(左边的表不加限制)：Left Join  左表是驱动表。

   右外连接(右边的表不加限制)：Right Join 右表是驱动表。

   全外连接(左右两表都不加限制)：Left Join union Right Join。

   说明：这里的全外连接又称全连接，由于 mysql 不支持 full join 之类的写法， 实际上它查询的是左连接和右连接的并集。

 

***Mysql 如何获取系统时间

   mysql 中常用取系统时间的函数有 now();sysdate();current_time()函数

 

***Mysql常用命令

   1、连接数据库 mysql -h 主机地址 -u 用户名 -p 用户密码

   2、快捷操作 show databses：列出所有的数据库；use databasesname:打开此数据库；show tables:查看当前数据库下面的所有表，然后可以对表进行 DML 操作；describe table_name：查看某表下的字段和类型的定义。

   3 、在 use databasename 状态下导入表 SOURCE d:/mysql.sql; 退出命令 quit；或者 exit

 

 

***mysql常用的性能优化

   1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

   2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

   3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

   4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

   5.%xx%和%xx 查询也将导致全表扫描，若要提高效率，可以考虑全文检索或者 xx%模糊查询。

   6.in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用between 就不要用 in 了

   7.如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。

   8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

   9.应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

   10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

   11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

   12.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update  时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

   13.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型， 这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

   14.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

   15.任何地方都不要使用 select * from t  ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

   16.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。

   17.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度;如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。

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

 

***Mysql乱码如何解决

   先分清是否是数据库乱码，如果是其它则用其它方式处理。

   1.在安装数据库的过程中将默认的拉丁文-->GBK、UTF-8、GB2312。

   2.在创建数据库时设置选择  GBK    或者  gb2312    或者  UTF-8。 3.Mysql 安装目录下的 my.ini 文件，将 "default-character-set=xxxxx" 中的 xxxxx 改成 GBK 或者 gb2312 或者 utf-8。

   4. Mysql 安装目录下的 \data\databasename( 数据库名 )\db.opt 文件打开 default-character-set=gbk default-collation=gbk_chinese_ci;  如果上面不是gbk 和 gbk_chinese_ci 则改成支持中文的 GBK 或者 gb2312 或者 utf-8。

   5.进入Mysql 的dos 命令下:进入某数据库后 show full columns from tablename ; 查看数据类型，如果不是支持中文的类型则执行 alter table tablename convert to character set gbk 。

   6.在创建数据库时(用命令创建时)create database databasename CHARACTER SET gbk;

 

***too many connection问题解决

   造成 too many connection 的直接原因是连接数被占满了，解决这种异常可以通过下面几种方案解决。

   一、扩大连接数：mysql 的最大连接数默认是 100, 这个数值对于并发连接很多 的 数 据 库 应 用 是 远 远 不 够 的 ， 可 以 通 过 编辑 编辑 my.ini 修改 max_connections=1000 增大连接数。

   二、连接数过多产生的另外一个原因是因为连接没有及时释放或者没释放， 可以从以下方面考虑。

      1、优化相关的 SQL 语句，提高执行效率，及时释放连接；

      2、养成良好的开发习惯，及时的关闭连接；适当使用数据源连接池，减少创建物理连接造成的额外开销；

      3、适当的使用缓存策略，减少与数据库的直接交互；

      4、如果有条件可以进行数据集群，增大数据库的吞吐量

     

***mysql 默认连接超时解决

   1、JDBC 连接：connection url 中加参数: autoReconnect=true jdbc.url=jdbc:mysql://ipaddress:3306/database?autoReconnect=true&amp;autoReconnectForPools=true（话说只能在 mysql5 之前的版本有效，mysql5 之后的版本我没有亲自测试）。

   2、HIBERNATE 连接：需要增加如下属性

   <property name=”connection.autoReconnect”>true</property>

   <property name=”connection.autoReconnectForPools”>true</property>

   <property name=”connection.is-connection-validation-required”>true</property>

   3、使用 c3p0 连接池：（我们的解决方式）

   <property name=”hibernate.c3p0.acquire_increment”>1</property>

   <property name=”hibernate.c3p0.idle_test_period”>0</property>

   <property name=”hibernate.c3p0.timeout”>0</property>

   <property name=”hibernate.c3p0.validate”>true</property>

   4、通过命令修改'wait_timeout'的值(需要重启 mysql 服务器)：

   use mysqlname; -- 在 mysql 库下。

   SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; -- 查看当前值。

   SET GLOBAL wait_timeout = 1814400; -- 修改(这里为 21 天单位秒)。

   说明：也可以通过修改 mysql 配置文件进行修改（windows-my.ini;linux-my.cnf）