数据库常用语句及优化思想

基础操作

  • 登录:
    • -h:主机名
    • -u:用户名
    • -p:使用密码登录
  • 管理:
    • use DB;
    • show database
    • show tables
    • show columns form 表
    • show index form 表
    • exit
  • 数据库操作:
    • 创建:CREATE DATABASE 数据库名;
    • 删除:drop database 数据库名;
  • 数据表操作:

    • 创建:CREATE TABLE table_name ('列名' 类型 属性, 列名 类型, 主键('xxx'));

      CREATE TABLE IF NOT EXISTS `runoob_tbl`(
         `runoob_id` INT UNSIGNED AUTO_INCREMENT, // 自增
         `runoob_title` VARCHAR(100) NOT NULL, // 非空
         `runoob_author` VARCHAR(40) NOT NULL,
         `submission_date` DATE,
         PRIMARY KEY ( `runoob_id` ) // 主键
      )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    • 删除:DROP TABLE table_name ;
    • 插入:INSERT INTO xxx (field1, field2,...fieldN) VALUES(field1, field2,...,fieldN)
    • 查询:

      SELECT column_name, column_name
      FROM table_name
      [WHERE Clause]
      [LIMIT N][ OFFSET M]
      • LIMIT 4读取四条;LIMIT 2, 4: 第三条起读取四条。分页LIMIT startRow,pageSize;
      • limit 4 offset 9:返回四行,从第十行开始
    • 更新:UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
    • 删除:DELETE FROM table_name WHERE age<20
    • Like:条件, %字符来表示任意字符,类似正则*'%COM'
    • UNION:连接两个查询的集合,删除重复数据。

      • SELECT A, B FROM tables WHERE XX
        UNION [ALL | DISTINCT]
        SELECT A, C FROM tables WHERE XX;
      • DISTINCT 默认就是删除重复,加不加无用。ALL 返回所有结果集,包含重复的
    • ORDER BY: ORDER BY XXX DESC,默认升序。
    • GROUP BY:按字段分组,并统计数目。GROUP BY name得到:name | count数目
    • 连接:
      • INNER JOIN(内连接, 或等值连接):获取两个表中字段匹配关系的记录。
      • LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
      • RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
    • Alter:修改数据表名或字段。
      • ALTER TABLE testalter_tbl DROP i; 删除表的 i 字段
      • ALTER TABLE testalter_tbl ADD i INT; 表添加字段,默认是末尾
      • ALTER TABLE testalter_tbl ADD i INT FIRST;第一列添加
      • ALTER TABLE testalter_tbl ADD i INT AFTER c;c 列之后添加
      • ALTER TABLE testalter_tbl MODIFY c CHAR(10);改类型
      • ALTER TABLE testalter_tbl CHANGE i j BIGINT;改 i 为 j,类型为 BIGINT
      • ALTER TABLE testalter_tbl RENAME TO alter_tbl; 改表名

InnoDB 和 MySAM

  • InnoDB 和 MySAM
  • 操作性:
    • InnoDB具有事务 ,支持四个事务隔离级别。

      • 适用 于大量 INSERT 或 UPDATE操作。
      • 不支持全文索引,新版本支持。
      • 支持外键
    • MyISAM 管理非事务表
      • 它提供高速存储和检索,以及全文搜索能力。
      • 如果应用中需要执行 大量的 SELECT 查询 ,那么 MyISAM 是更好的选择
      • 不支持事务、外键
  • 存储:
    • MyISAM 在磁盘上存储成三个文件。表定义 .frm,数据文件.MYD, 索引文件.MYI。跨平台转移麻烦
    • InnoDB:空间 数据文件 和它的 日志文件 ,InnoDB 表的大小只受限于操作系统文件的大小
  • 索引:
    • InnoDB(索引组织表):能缓存索引,也能缓存数据,必须得有主键
    • MyISAM(堆组织表):只能缓存索引。非聚类
  • InnoDB 在做 SELECT 的时候,要维护缓存数据和索引和其余的,慢一些。MyISAM 只缓存索引块
  • MyISAM 引擎使用 B +Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。
    • B+ 树搜索算法搜索索引 - 取出 data 值 - 以此为地址读取数据记录
    • 主索引要求 key 是唯一的,而辅助索引的 key 可以重复
  • InnoDB 也使用 B +Tree 作为索引结构,数据文件本身就是索引文件。
    • 叶节点 data 域就是数据记录,称之聚类索引
    • 本身要按主键聚类,所以必须要主键(设置自增主键),没有的话分裂维持特性会十分低效。没指定的话会自动选择合适的或自动生成一个隐含字段。长度为 6 的长整型
    • 辅助索引 data 存储的主键值

索引

  • 索引可以看作一张表,指向实体表记录
  • 唯一索引,保证每行数据的唯一性
  • 加大检索速度,特别是分组和排序
  • 加速表和表之间的连接速度
  • 缺点:创建和维护要耗时(增删时要动态维护),占用物理空间
  • 总结:会提高查询速度,但是 DML 会变慢(更新维护索引)
  • 语法:
    • CREATE INDEX indexName ON mytable(username(length));
    • ALTER table tableName ADD INDEX indexName(columnName)
    • DROP INDEX [indexName] ON mytable;
  • 添加主键命令:

    mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
    mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

导出数据

mysqldump -u root -p dbs_name table_name > dump.sql [--all-databases]
mysql -u root -p dbs_name < dump.sql

create database adc;
use abc;
set names ytf8;
source /xx/xx/xx/abc.sql

范式

  • 第一范式:当关系模式 R 的所有属性都不能在分解为更基本的数据单位时,称 R 是满足第一范式的,简记为 1NF。满足第一范式是关系模式规范化的最低要求,否则,将有很多基本操作在这样的关系模式中实现不了。
  • 第二范式:如果关系模式 R 满足第一范式,并且 R 得所有非主属性都完全依赖于 R 的每一个候选关键属性,称 R 满足第二范式,简记为 2NF。
  • 第三范式:设 R 是一个满足第一范式条件的关系模式,X 是 R 的任意属性集,如果 X 非传递依赖于 R 的任意一个候选关键字,称 R 满足第三范式,简记为 3NF.
  • 注:关系实质上是一张二维表,其中每一行是一个元组,每一列是一个属性
  • 第四范式:要求把同一表内的多对多关系删除。
  • 第五范式:从最终结构重新建立原始结构。
  • BC 范式(BCNF):符合 3NF,并且,主属性不依赖于主属性。若关系模式 R 属于第一范式,且每个属性都不传递依赖于键码,则 R 属于 BC 范式。

优化方法

  • 微博整理
    • null 值容易引发灾难,id is null而不是id=null
    • union代替or
  • 数据库设计
  1. 避免全表扫描,在 where 及 order by 涉及的列上建立索引
  2. 避免在 where 子句中对字段进行 null 值判断,引擎会放弃索引而使用全局扫描。可以考虑把 null 默认值设为 0
  3. 一个表的索引数最好不要超过 6 个,在一些不常用到的的列不需要索引。因为会降低插入、修改的速度
  4. 避免更新索引数据,若该列频繁更新,考虑索引的必要性
  5. 字符会一一比较,数字只需要比较一次。所以能用数值尽量不要用字符
  6. 尽可能的使用 varchar/nvarchar 代替 char/nchar,变长字段存储空间小,可以节省存储空间
  7. 尽量少使用临时表
  • SQL 语句
  1. 避免在 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
  2. 能用 between 就不要用 in,not in
  3. where 子句中使用参数,也会导致全表扫描。强制加上索引

    select id from t where num=@num
    select id from t with(index(索引名)) where num=@num
  4. 避免在 where 子句中对字段进行表达式操作:=”左边进行函数、算术运算或其他表达式运算

    select id from t where num/2=100 
    select id from t where num=100*2
  5. 具体的字段列表代替“*”,不要返回用不到的任何字段。
  6. 避免使用游标,超过一万行就考虑改写
  7. 避免大事务操作

本文链接:https://ariser.cn/index.php/archives/334/
本站文章采用 知识共享署名4.0 国际许可协议进行许可,请在转载时注明出处及本声明!