mysql优化

date_format(date,'%Y-%m-%d') ————–>oracle中的to_char(); 
str_to_date(date,'%Y-%m-%d') ————–>oracle中的to_date();

 

IFNULL  ————–> oracle nvl

 

 

组合索引遵循最左原则(具体略)

1.数据库事务的四个特性及含义
原子性:事务要么全部被执行，要么就全部不被执行
一致性:事务的执行使得数据库从一种正确状态转换成另一种正确状态
隔离性:在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

2.对于那些数据区分度不大的列也不应该增加索引。例如人事表的性别列，只有两种取值，轮询的数据也很大，不会提高查询速度

3.SQL语句优化
1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
4）应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
　　select id from t where num=10 or num=20
　　可以这样查询：
　　select id from t where num=10
　　union all
　　select id from t where num=20
4）很多时候用 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)
5）下面的查询也将导致全表扫描：
　　select id from t where name like '%abc%'
　　若要提高效率，可以考虑全文检索。
6）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
7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
　　select id from t where num/2=100
　　应改为:
　　select id from t where num=100*2
8）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
　　select id from t where substring(name,1,3)='abc'--name以abc开头的id
　　select 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'
9）不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10）索引并不是越多越好，会降低 insert 及 update 的效率，一个表的索引数最好不要超过6个
11）数据库字段设计最好不要设计为字符型，最好为数字，例：主键设计为bigint，自增
12）任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
13）当MYSQL认为符合条件的记录在30%以上，它就不会再使用索引，因为mysql认为走索引的代价比不用索引代价大，所以优化器选择了自己认为代价最小的方式。
14）ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

4.
如果数据库中的表太多，并且项目各项业务逻辑清晰，那么垂直切分是首选。（切分列）
如果数据库的表不多，但是单表的数据量很大，应该选择水平切分。（切分数据）最简单的是使用 merge 存储引擎实现

拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

5.简述在MySQL数据库中MyISAM和InnoDB的区别
区别于其他数据库的最重要的特点就是其插件式的表存储引擎。切记：存储引擎是基于表的，而不是数据库。

InnoDB与MyISAM的区别：
IMyISAM 是非事务的存储引擎；适合用于频繁查询的应用；表锁，不会出现死锁；适合小数据，小并发。（MyISAM 支持全文索引，地理空间索引。）
innodb是支持事务的存储引擎；合于插入和更新操作比较多的应用；设计合理的话是行锁（最大区别就在锁的级别上）；适合大数据，大并发。

6.
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。
INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

CHAR的长度是固定的，而VARCHAR2的长度是可以变化的

Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用8个字节得存储空间，datatime类型与时区无关
Timestamp:以时间戳格式存储，占用4个字节，范围小1970-1-1到2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改timestamp列得值
MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。
默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。
应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

---