建表原则

  1. 定长与变长相分离
  2. 常用字段和不常用字段要分离
  3. 1对多,需要关联统计的字段上,添加冗余字段(空间和时间上的转换)

列选择原则

  1. 字段类型优先级 整形 > date,time > enum,char > varchar > blob,text

    time 定长,运算快,节省空间,考虑时区,写sql时不方便 where > ‘2005-10-12’;
    enum 能起到约束值的目的,内部用整形来存储
    char 定长 需要考虑字符集和(排序校对集)
    varchar 不定长 要考虑字符集的转换与排序时校对集,速度慢
    text/Blob 无法使用内存临时表(排序等操作只能在磁盘上进行)

  2. 够用就行,不要慷慨

    原因:大的字段浪费内存,影响速度
    以年龄为例,tinyint unsigned not null 可以存储255岁,足够使用 ,用int浪费了3个字节
    以varchar(10) 和 varchar(300) 存储的内容相同,但是在表的联查上varchar(300)要花费更多的内存。

  3. 尽量避免使用NULL

    原因:NULL 不利于索引 要用特殊字节来标注

索引

索引的类型

索引类型分为bTree索引和hash索引

  1. btree 索引原理 二叉树
  2. hash索引 在memory里,默认是hash索引,hash索引的理论上时间复杂度为O(1)。(拉链算法)
btree索引的误区
  1. where 条件常用列上都加上索引(错误)
  2. 在多列上建立索引发挥作用,需要满足左前缀要求

以index(a,b,c)为例(注意和顺序有关)

语句 索引是否发挥作用
where a =3 是 只使用了a列
where a=3 and b=5 是 使用了a,b列
where a=3 and b=5 and c=5 是 a,b,c均使用
where c=3 where b=4
where a=3 and c=4 a列能发挥索引,c不能
where a=3 and b>10 and c=7 a能利用,b能利用,c不能利用

聚簇索引和非聚簇索引

  1. 非聚簇索引
    • Myisam 索引与数据的关系
    • Myisam 索引指向行所在磁盘的位置
    • 数据都有自己的地址
    • 数据和索引相互独立
  2. 聚簇索引
    • 主键索引 既存索引值,又在叶子中存储行的数据
    • 如果没有主键(primary key),则会Unique key做主键
    • 如果没有unique,则系统生成一个内部的rowid做主键
    • 像innodb中,主键的索引结构中既存储了主键值,又存储了行数据的这样的结构c称为“聚簇索引”
  3. 聚簇索引和非聚簇索引的优缺点

    优势:根据主键查询条目比较少,不用回行(数据就在主键节点下)
    劣势: 如果碰到不规则数据插入时会造成频繁的页分裂。
    ####索引覆盖
    索引覆盖是指查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘在查找数据,这样查询速度非常快

理想的索引

  1. 查询频繁
  2. 区分度高
  3. 长度小
  4. 尽可能覆盖常用字段

SQL语句优化

提高查询速度的方法

  1. 查询的快,联合索引的顺序,区分度,长度
  2. 取得快,采用索引覆盖
  3. 传输的少,更少的行和列

切分查询: 按数据拆分多次
例如:插入10000行数据,每1000条为单位插入

分解查询
将逻辑把多表连接查询分解成多个简单的sql

sql语句的优化思路

不查 -> 少查 -> 高效的查

  1. 不查,通过业务逻辑来计算
    2.少查 尽量精准数据,少取行
    3.必须要查 尽量在索引上查询

explain 的列分析

id 代表select语句的编号

select_type类型

  1. 简单查询 SIMPLE
  2. 子查询派生子查询
  3. PRIMARY 主语句
  4. subquery 非from子查询
  5. derived from子查询
  6. union
  7. union result

table 表名
key
使用的索引列
type
查数据过程的重要依据
可选项

  1. all 意味着从表的第一行,往后逐行做全表扫描
  2. index 扫描所有的索引节点
  3. range 查询
  4. const
  5. ref 通过索引列,可以直接引用某行数据
    ref
    连接查询 表之间的引用关系
    rows
    估计要扫描多少行
    extra
    index:是指用到了索引覆盖效率非常高
    using where 是光靠索引定位不了还得where判断一下
    using temporary 是指用到了临时表
    using filesort 文件排序
    注:如果取出的列含有text,filesort会发生在磁盘上