#mysql

April 1, 2019 | 15:12

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

基础操作

  • 登录：

    • -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. 避免大事务操作

© Aris 2020

Powered by Hugo & Kiss'Em.