1、关系型数据库的三范式
第一范式(1NF):是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。列数据的不可分割。
第二范式(2NF):要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识(主键)。
第三范式(3NF):要求一个数据库表中不包含已在其它表中已包含的非主关键字信息(外键)。
反三范式:有时候为了效率,可以设置重复或者可推导出的字段.比如:订单(总价),订单项(单价)。
2、随机取出一些数据
SELECT * FROM `table` ORDER BY RAND() LIMIT 10;
3、in 和 exit
in:确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。
exit:指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。使用exists关键字进行查询的时候,首先,我们先查询的不是子查询的内容,而是查我们的主查询的表。
区别及应用场景:in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 ,另外IN时不对NULL进行处理。in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
not in 和not exists:如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
4、数据库事务隔离级别
1)、读未提交(Read Uncommitted):一个事务能够读取到别的事务中没有提交的更新数据。事务可以读取到未提交的数据,这也被称为脏读(dirty read)。
脏读:当前事务看到了另外一个事务没有提交的更新数据。这是事务没有隔离造成的。
2)、读已提交(Read Committed):一个事务只能读取到别的事务提交的更新数据,这就是不可重复读,对应的是UPDATE。
不可重复读:当前事务先进行了一次数据读取,然后再次读取到的数据是别的事务修改成功的数据。
3)、可重复读(Repeated Read):就是在开始读取数据(事务开启)时,不再允许修改操作。保证同一事务中先后执行的多次查询将返回统一结果,不受其他事务的影响。这种隔离级别可能出现幻读,对应的是INSERT。
幻读:当前事务读第一次取到的数据比后来读取到数据条目少。
4)、序列化(Serializable):是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。不允许事务并发执行,强制事务串行执行,就是在读取的每一行数据上都加上了锁,读写相互都会阻塞。这种隔离级别最高,是最安全的,性能最低,不会出现脏读,不可重复读,幻读,丢失更新。
5、数据库表设计
1)、在创建数据表名、字段名时要通俗易懂,见名知意。
2)、在设计表,以及字段是都务必写上备注。
3)、业务表设计时必须包含日期字段,createitme和lastupdatetime。
4)、每个表的update 语句,必须包含对 UpdateTime的更新 。
5)、所有字段建议为not null , 时间类型必须为not null。
6)、必须要有主键,主键尽量用自增字段类型,推荐类型为 INT或者 BIGINT类型。
7)、需要多表 join的字段,数据类型保持绝对一致。
8)、当表的字段数较多时,将表分成两张表,一张作为条件查询表,一张作为详细内容表。
9)、不可乱用字段类型和长度。更少的存储空间;更少的磁盘IO ;更少的网络IO ;更少的MySQL计算空间;更少的APP计算空间;整形存储和运算代价比字符型小;尽量使用tinyint代替char(1)。
10)、有text字段的,最好请分表,表字段要少而精(不是mysql不适合存储text,而是在太多的情况下我们期望mysql能够更加高效的提供小数据查询/事务处理)
11)、存储ip用int unsigned来代替char(15),varchar(15)。
12)、不要修改聚集索引(主键),会带来大量的数据移动,所以一般要求使用跟业务不相关的id做一个整形自增主键。
13)、索引不是越多越好,尽量合并索引。索引加快了查询度,但是却会影响写入性能。一个表的索引应该结合这个表相关的所有SQL综合创建,尽量合并。组合索引的原则是,过滤性越好的字段越靠前。
14)、不要给选择性低的字段建单列索引,MySQL对索引的过滤性有要求,如果过滤性太低MySQL会放弃使用。
15)、不要使用外键约束。对性能损耗特别大,让应用程序去维护约束。
16)、字符类型字段尽量使用前缀索引。太长的索引不仅影响写入性能,而且使用效果也差,因此字符串类型字段一般只建前缀索引。
6、数据库索引
一条索引中包含的基本信息包括键值(即你定义索引时指定的所有字段的值)+逻辑指针(指向数据页或者另一索引页,索引页就是存储索引的空间)
1)、普通索引:这是最基本的索引,没有任何限制。
CREATE TABLE my_table(
`id` INT NOT NULL,
`user_name` VARCHAR(16) NOT NULL,
INDEX user_name_index (user_name(16))
)
CREATE INDEX user_name_index ON my_table(user_name(16));
ALTER my_table ADD INDEX [user_name_index] ON (user_name(16))
DROP INDEX [user_name_index] ON mytable;
2)、唯一索引:索引列的值必须唯一,但允许有空值。
CREATE TABLE my_table(
`id` INT NOT NULL,
`user_name` VARCHAR(16) NOT NULL,
UNIQUE user_name_index (user_name(16))
)
CREATE UNIQUE INDEX user_name_index ON my_table(user_name(16))
ALTER my_table ADD UNIQUE [user_name_index] ON (user_name(16))
3)、主键索引:是一种特殊的唯一索引,不允许有空值,且只能有一个主键。
CREATE TABLE my_table(
`id` INT NOT NULL,
`user_name` VARCHAR(16) NOT NULL,
PRIMARY KEY (`id`))
)
4)、组合索引
CREATE TABLE my_table(
`id` INT NOT NULL,
`user_name` VARCHAR(16) NOT NULL,
`city` VARCHAR(16) NOT NULL,
`age` INT NOT NULL
)
ALTER TABLE my_table ADD INDEX name_city_age (user_name(10), city, age);
相当于创建了user_name,city,age user_name,city user_name这样的三组组合索引
6)、索引的不足
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
7)、使用索引的注意事项
索引不会包含有NULL值的列:只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
使用短索引:对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
索引列排序:MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
like语句操作:一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算:将在每个行上进行运算,这将导致索引失效而进行全表扫描。
不使用NOT IN和<>操作