数据库的注意点

数据库一些注意的点

MyISAM和InnoDB的差异

Mysql支持很多表类型的表（即存储引擎），如MyISAM、InnoDB、Memory、Archive、Example等，主要介绍两种MyISAM和InnoDB。

MyISAM是默认表类型，基于ISAM（Indexed Sequential Access Method索引顺序访问方法），它是存储记录和文件的标准方法，不是事务安全的，不支持外键，如果有大量的select，MyISAM比较合适。MyISAM表示独立于操作系统之外的，通俗点说就是你可以很轻松的将MyISAM表从windows移植到linux或者从linux移植到windows。

InnoDB支持事务安全，支持外键、行锁，事务是它的最大优点，如果有大量的insert和update，建议用InnoDB，特使是针对高并发和QPS（query per second）较高的情况。

1. 表锁差异

MyISAM

　　MyISAM只支持表锁，在select、insert、update、delete都会锁表，开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

InnoDB

　　InnoDB支持事务和行锁，开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

　　1> 事务的ACID属性

　　　　a) 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

　　　　b) 一致性（Consistency）：事务前后数据的完整性必须保持一致。

　　　　c) 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

　　　　d) 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

　　2> 并发事务带来的问题

　　　　a) 脏读：一个事务修改了还没提交，另一个事务就读了。

　　　　b) 不可重复读：一个事务两次读取数据之间，另一个事务修改了数据。

　　　　c) 幻读：一个事务两次读取数据之间，另一个事务新增了数据。

　　　　d) 丢失修改（更新丢失）：两个事务同时修改每个数据，一个事务的提交破坏了另一个事务的修改。例如，T1读A修改为A-1，T2也读A修改为A-1（实际上T2读A-1修改为A-2）。

　　3> 事务隔离级别

 

隔离级别	读数据一致性	并发副作用
脏读	不可重复读	幻读
未提交读（Read uncommitted）	最低级别，不读物理上损坏的数据	是	是	是
已提交读（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

　　　　查看mysql的默认事务隔离级别“show global variables like ‘tx_isolation’;”

　　4> InnoDB的行锁模式有以下几种：共享锁，排他锁，意向共享锁（表锁），意向排他锁（表锁），间隙锁。

　　　　注意：当语句没有使用索引，InnoDB不能确定操作的行，这个时候就使用的意向锁，也就是表锁。

　　5> 关于死锁

　　　　a) 什么是死锁？当两个事务都需要获得对方持有的排他锁才能完成事务，这样就导致了循环锁等待，也就是常见的死锁类型。

　　　　b) 解决死锁的方法：

　　　　　　1、数据库参数；

　　　　　　2、 应用中尽量约定程序读取表的顺序一样；

　　　　　　3、 应用中处理一个表时，尽量对处理的顺序排序；

　　　　　　4、 调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）。

2. 数据库文件差异

MyISAM

　　MyISAM属于堆表。

　　1> 在磁盘上存储有3个文件，每个文件以表名为开头。

　　　　.frm用于存储表的定义

　　　　.MYD用于存放表的数据

　　　　.MYI用于存放表的索引

　　2> 在mysql中可以创建3种MyISAM格式的表——静态、动态和压缩。格式不需要单独指定，mysql会根据表结构自动选择最合适的格式。

　　　　a) MyISAM静态：如果表的每个字段的数据类型的定义都是使用静态的（如char），mysql就会自动使用静态MyISAM格式，这种类型格式的表的性能是很高的，也就是查询更新用的时间很少，但要知道这是在牺牲空间为代价。因为每一列都要分配最大的空间，即使有部分空间没有用到，这就使得静态的表所占的空间会比较大。

　　　　b) MyISAM动态：如果表的每个字段的数据类型的定义都是使用动态的（如varchar），mysql就会自动使用动态MyISAM格式，这种类型格式的表的性能会有所下降，但是它的空间占有要比静态的少很多。

　　　　c) MyISAM压缩：如果有一张表在设计之初只赋予了它读的使命，就可以用MyISAM压缩表，在相同的配置下，它的性能是最快的。

InnoDB

　　InnoDB属于索引组织表。

　　InnoDB有两种存储方式，共享表空间存储和多表空间存储，两种存储方式的表结构和MyISAM一样，以表名开头，扩展名是.frm。

　　如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。

　　如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。

3. 索引差异

关于自动增长

MyISAM引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

关于主键

MyISAM允许没有任何索引和主键的表存在，MyISAM的索引都是保存行的地址。

InnoDB引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见），InnoDB的数据是主索引的一部分，附加索引保存的是主索引的值。

关于count()函数

MyISAM保存有表的总行数，如果select count(*) from table;会直接取出出该值。

InnoDB没有保存表的总行数，如果使用select count(*) from table;就会遍历整个表，消耗相当大，但是在加了wehre 条件后，MyISAM和InnoDB处理的方式都一样。

全文索引

MyISAM支持 FULLTEXT类型的全文索引。

InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询）。

delete from table

使用这条命令时，InnoDB不会从新建立表，而是一条一条的删除数据，在InnoDB上如果要清空保存有大量数据的表，最好不要使用这个命令。（推荐使用truncate table，不过需要用户有drop此表的权限）。

索引保存位置

MyISAM的索引以表名+.MYI文件分别保存。

InnoDB的索引和数据一起保存在表空间里。

4. 几点注意事项

1. 可以用 show create table tablename 命令看表的引擎类型。

2. 对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交。

3. 可以执行以下命令来切换非事务表到事务（数据不会丢失），InnoDB表比MyISAM表更安全：alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;

4. 默认innodb是开启自动提交的，如果你按照MyISAM的使用方法来编写代码页不会存在错误，只是性能会很低。如何在编写代码时候提高数据库性能呢？

　　　　1> 尽量将多个语句绑到一个事务中，进行提交，避免多次提交导致的数据库开销。

　　　　2> 在一个事务获得排他锁或者意向排他锁以后，如果后面还有需要处理的sql语句，在这两条或者多条sql语句之间程序应尽量少的进行逻辑运算和处理，减少锁的时间。

　　　　3> 尽量避免死锁。

　　　　4> sql语句如果有where子句一定要使用索引，尽量避免获取意向排他锁。

　　　　5> 针对开发，一般日志表之类的都是不修改的，可以用MyISAM，而其他表要用到事务，要频繁修改的可以用InnoDB。

物理外键（不建议使用）

• 方式一 在创建表的时候，增加约束 删除有外键关系的表的时候，必须要删除引用别人的表（从表），再删除被引用的表（主表）

• 方式二

  在创建完表之后添加外键约束

  alter table `表名`
  add constraint `约束名` foreign key (`作为外键的列`) references `表名` (`表中的字段`);
  12

物理外键不建议使用，避免数据可过多造成困扰。

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

• 我们想使用多张表的数据，想使用外键，可以通过程序去实现

   

drop、truncate和delete的区别

（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）表和索引所占空间。

当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，

DELETE操作不会减少表或索引所占用的空间。

drop语句将表所占用的空间全释放掉。

（3）一般而言，drop > truncate > delete

（4）应用范围。

TRUNCATE 只能对TABLE； DELETE可以是table和view

（5）TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。

（6）truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8）truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

一、delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

二、truncate

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4、truncatetable不能用于参与了索引视图的表。

三、drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

 

总结：

1、在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

去重及数据库的表达式

查询players 表中的玩家昵称，发现有重复项

SELECT NAME FROM players
1

加入去重

SELECT DISTINCT NAME FROM players
1

查询系统的版本号

SELECT VERSION()
1

利用select计算

SELECT 5*9*9 as answer
1

把players表中的id全部加1

SELECT `id`+1 as '加了一后的id' FROM players
1