如何进行SQL优化?
-
选择正确的存储引擎
-
以 MySQL为例,包括有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。
-
MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。支持全文索引
-
另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
-
InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。但是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。不支持全文索引
-
优化字段的数据类型
-
记住一个原则,越小的列会越快。如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。
-
如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然,你也需要留够足够的扩展空间。
-
为搜索字段添加索引
-
索引并不一定就是给主键或是唯一的字段。
-
如果在你的表中,有某个字段你总要会经常用来做搜索,那么最好是为其建立索引。
-
避免使用Select *
-
从数据库里读出越多的数据,那么查询就会变得越慢。
-
并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。即使你要查询数据表的所有字段,也尽量不要用*通配符
-
善用内置提供的字段排除定义也许能给带来更多的便利。
-
使用 ENUM 而不是 VARCHAR
-
ENUM 类型是非常快和紧凑的。
-
在实际上,其保存的是 TINYINT,但其外表上显示为字符串。
-
这样一来,用这个字段来做一些选项列表变得相当的完美。
-
例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。
-
尽可能的使用 NOT NULL
-
除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。
-
NULL其实需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。
-
这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。
-
固定长度的表会更快
-
如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。
-
例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。
-
固定长度的表会提高性能,MySQL搜寻得会更快一些
-
因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。
-
而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。
-
并且,固定长度的表也更容易被缓存和重建。
-
唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。
-
优化数据库的方法
- 选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如’省份’、’性别’最好适用ENUM
- 使用连接(JOIN)来代替子查询
- 适用联合(UNION)来代替手动创建的临时表
- 事务处理
- 锁定表、优化事务处理
- 适用外键,优化锁定表
- 建立索引
- 优化查询语句
-
数据库中的事务是什么?
- 事务(transaction)是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功,则认为事务成功,即使只有一个操作失败,事务也不成功。
- 如果所有操作完成,事务则提交,其修改将作用于所有其他数据库进程。
- 如果一个操作失败,则事务将回滚,该事务所有操作的影响都将取消。
-
四大特性,原子性、隔离性、一致性、持久性。
- 原子性:即不可分割性,事务要么全部被执行,要么就全部不被执行。
- 一致性:事务的执行使得数据库从一种正确状态转换成另一种正确状态
- 隔离性:在事务正确提交之前,不允许把该事务对数据的任何改变提供给任何其他事务
- 持久性:事务正确提交后,其结果将永久保存在数据库中,即使在事务提交后有了其他故障,事务的处理结果也会得到保存。
-
索引的目的是什么?
-
快速访问数据表中的特定信息,提高检索速度
-
创建唯一性索引,保证数据库表中每一行数据的唯一性。
-
加速表和表之间的连接
-
使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间
-
索引对数据库系统的负面影响是什么?
-
负面影响:
-
创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;
-
索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;当
-
对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。
-
为数据表建立索引的原则有哪些?
-
在最频繁使用的、用以缩小查询范围的字段上建立索引。
-
在频繁使用的、需要排序的字段上建立索引
-
什么情况下不宜建立索引?
-
对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。
-
对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等
-
简述在MySQL数据库中MyISAM和InnoDB的区别
-
区别于其他数据库的最重要的特点就是其插件式的表存储引擎。切记:存储引擎是基于表的,而不是数据库。
-
InnoDB与MyISAM的区别:
-
InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing,在线事务处理)方面的应用,是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎,已经停止开发)。
-
特点:
-
行锁设计、支持外键,支持事务,支持并发,锁粒度是支持mvcc得行级锁;
-
MyISAM存储引擎: 是MySQL官方提供的存储引擎,主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。
-
特点:
-
不支持事务,锁粒度是支持并发插入得表级锁,支持表所和全文索引。操作速度快,不能读写操作太频繁;
-
SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)四个部分。
-
数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index等
-
数据操纵:Select ,insert,update,delete,
-
数据控制:grant,revoke
-
数据查询:select
-
什么是存储过程?用什么来调用?
-
存储过程是一个预编译的SQL语句
-
优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。
-
如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。
-
可以用一个命令对象来调用存储过程。
-
三个范式?
-
https://www.zhihu.com/question/24696366/answer/29189700
-
函数依赖:
-
我们可以这么理解(但并不是特别严格的定义):若在一张表中,在属性(或属性组)X的值确定的情况下,必定能确定属性Y的值,那么就可以说Y函数依赖于X
-
假如 Y 函数依赖于 X,但同时 Y 并不完全函数依赖于 X,那么我们就称 Y 部分函数依赖于 X。
-
例如:
(学号,课名) → 姓名 //依赖于学号但是不依赖于课名。部分依赖 -
传递函数依赖
假如 Z 函数依赖于 Y,且 Y 函数依赖于 X ,那么我们就称 Z 传递函数依赖于 X -
码:
-
假如当 K 确定的情况下,该表除 K 之外的所有属性的值也就随之确定,那么 K 就是码。一张表中可以有多个码
-
非主属性
-
包含在任何一个码中的属性成为主属性,反之就是非主属性。
-
第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解(每一列都不可再分);
-
第二范式:2NF不存在非主属性对于码的部分函数依赖;
-
第三范式:3NF是对字段冗余性的约束,即不存在非主属性对于码的传递函数依赖。
- 脏读
- 事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
- 读了未提交:即一个事务要等另一个事务提交后才能读数据
- 不可重复读
- 事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果因此本事务先后两次读到的数据结果会不一致;
- 幻读
-
varchar(100)和varchar(200)的区别:
- varchar(100)最多存放100个字符,varchar(200)最多存放200个字符,varchar(100)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样)
-
varchar(20)和int(20)中的20含义一样吗:
- 不一样,前者表示最多存放20个字符,后者表示最多显示20个字符,但是存储空间还是占4字节存储,存储范围不变;