数据库初级总结

谭佳成
2018-09-24
(32)

数据表类型

1 MyISAM:非事务表,支持全文搜索fulltext,搜索速度快,记录表行数,但写入速度慢,不支持外键。多用于频繁查询,数据量要求不高的数据表中

2 InnorDB:事务表,支持外键等高级数据库功能,CPU利用率最高,不支持全文搜索,搜索速度相对较慢,不记录行数。多用于安全性要求高,数据量高的数据表中

3 Memory:内存表,数据记录在内存中,写入及查询速度最快。但由于使用内存,根本无法长期保存数据,而且可以保存的数据量也不能太高。通常用于临时记录数据,可以即用即弃的数据表中,如密钥验证记录密钥等

三大范式

  • 列不可再分

内容相似的数据列必需消除(再建一个数据表)

  • 行不可再分

数据列内容重复(城市、省份),再见数据表,用外键关联
当前表的某个字段和另外张表的主键对应,那么这个字段就是当前表的外键。

  • 表不可再分

与主键没有直接关系的数据列(商品总价)必须消除(再建一个数据表)

变长表、定长表

变长表:包含任何varchar、text等变长字段的数据表,即为变长表,大部分数据表均为此类型
定长表:不包含任何变长字段的数据表,为定长表
对于变长表,由于记录大小不同,在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE以保持性能。而定长表就没有这个问题
表中有可变长的字段,将它们转换为定长字段能够改进性能,因为定长记录易于处理

1 使用定长列涉及某种折衷。它们更快,但占用的空间更多。
char(n) 类型列的每个值总要占用n 个字节(即使空串也是如此),因为在表中存储时,值的长度不够将在右边补空格;

2 而varchar(n)类型的列所占空间较少,因为只给它们分配存储每个值所需要的空间,每个值再加一个字节用于记录其长度。因此,如果在char和varchar类型之间进行选择,需要对时间与空间作出折衷;

3 在设计表结构时如果能够使用定长数据类型尽量用定长的,因为定长表的查询、检索、更新速度都很快。必要时可以把部分关键的、承担频繁访问的表拆分,例如定长数据一个表,非定长数据一个表。例如Discuz!的cdb_members和cdb_memberfields表、cdb_forums和cdb_forumfields表等。因此规划数据结构时需要进行全局考虑

数据库设计优化

  • 任何类型的数据表,字段空间应当本着足够用,不浪费原则

  • 一般来说,日期使用int类型保存时间戳

  • 99.9%的情况设置 NOT NULL  ,因为当某张表的字段此属性相同的时候,查询速度更快。

  • 预计不会出现负数的数字字段(如:ID 年龄 发帖数量),全部设置为无符号。

  • INT型不用设置长度,因为INT型的字段,占用的字节数天生就定好了。

  • 枚举 enum ,想到我们sex字段的例子。  枚举的本质还是数字,但是不推荐。

  • 数值运算一般比字符串运算更快

  • 如果串列的值数目有限,应该利用普通整型或emum类型来获得数值运算的优越性

  • 更小的字段类型永远比更大的字段类型处理要快得多

  • 对于字符串,其处理时间与串长度直接相关。一般情况下,较小的表处理更快

  • 对于定长表,应该选择最小的类型,只要能存储所需范围的值即可。例如,如果mediumint够用,就不要选择bigint。

  • 对于可变长类型,也仍然能够节省空间。一个TEXT 类型的值用2 字节记录值的长度,而一个LONGTEXT 则用4字节记录其值的长度

实践-分表

news表:
ID(int)  标题(char(30))  时间(int)  image(char(40))  作者(char(10))
1         新闻1            56456454  ...........      sky
2         新闻2           86786868   。。。。。。     奥宝马

新闻详细信息表:
ID(int)  内容(text) 描述(varchar)
1  ....... .....
2         .......       .....

分表的好处:
1.减轻了news表的体量,查询速度加快。
2.news表变成了定长表,查询速度加快。
3.暗合了我们的业务逻辑,减轻了整个数据库和服务器的压力。
--- 一条新闻的出现地点:首页(标题) 列表页(标题) 详细页(标题 描述  内容...)

对应关系

  • 一对一

  • 一对多

  • 多对多(建一张中间表来表达他们的关系)

索引类型

1 PRIMARY(pk):主键,在所有类型的索引速度最快。每个数据表最多有一个,涉及到主键的字段(组合)值必须唯一。建议每个数据表都建立一个主键,该主键就是id

2 UNIQUE:唯一索引,速度仅次于主键,涉及到的字段(组合)值必须唯一。但一个数据表中可以存在多个

3 INDEX:普通索引,速度在三者中最慢,但是几乎没有任何限制

4 ulltext:全文索引,主要用于模糊搜索。表类型必须是myisam,涉及字段类型必须是字符型

5 spaital:地理信息索引,主要用于地理信息搜索。涉及字段类型必须是地理信息型

索引

1 只要搜索条件中,包含涉及到索引的字段(组合),sql语句会自动的选用索引

2 语句使用了索引,是查找索引,而非查找数据表

3 索引字段是字符的话,根据首字母排序,若首字母相同,则按照第二字母排序,以此类推

4 每条sql语句,最多执行一个索引

5 mysql选用索引:首先寻找与搜素条件最匹配的索引,若没有,按照搜索条件的顺序选用最匹配的索引

6 建立索引组合时,根据组建索引是字段的顺序的先后来排序,建议大家把筛选出更多数据的字段排在前面

7 不要为每个字段都建立索引

8 数量太多。理论上的组合,数据表的字段数n,索引数2的n次方-1,还没算上组合排序上的不同。

9 索引能大大的加快查询速度,同时会大大的降低写入的速度

10 建立索引的准则:经常被用于搜索的字段(组合)

11 索引涉及字段的值重复率越高,索引效率越低

查询优化

1 任何sql语句的where条件,都必须涉及到索引。而且最好是主键,其次是唯一,再次是普通(通常是先做好sql语句,才根据sql建立索引)

2 注意查询语句中的一些关键字的使用 '= and' > '< > or like in‘ > '!= not in not like',因为越往后的涉及行数就会越多

3 尽量避免使用left join(关联查询)、group by等的关键字

4 在复杂的查询语句,查询语句的速度取决最慢的一条

5 涉及到group by,order by的字段必须设置索引

6 (mysql)设置的查询条件,必须先使用筛选内容多的字段,依次往后

7 越简单的语句通常效率就越高,不要为了完成某些功能而去把sql写得过于复杂,宁愿写多几条简单的

explain语句(分析查询语句的效率)

  • select_type:查询类型
    SIMPLE:最简单的sql语句,最常见。大部分有效率的语句,类型都是SIMPLE
    PRIMARY:查询的数据表是一个动态表,数据表示有一条sql语句生成的结果集构建出的临时表,该表无法使用索引如下

    select * from(select * from news) n,通过n得到的结果集来查询,那n就是动态表
    UNION:使用union关键字,用于合并两个或多个SELECT语句的结果集(一般少用)
    SUBQUERY:子句查询,条件使用动态结果集,如下:
    explain select * from t0 where id = (select id from t0 where id = 90)
    DEPENDENT SUBQUERY:子句查询,关键字使用in

  • type:查询类型,包括语句使用索引的情况(以下排序速度从上到下)
    system:理论上最快的,使用了主键,全表只有一行
    const:在实际中最快的类型,使用了主键索引,而且返回的行数只有一行
    eq_ref:使用了唯一索引,而且返回一行
    ref:使用了普通索引,返回少说的行(where 条件使用的是=add等关键字),但是最终还要看rows的数量
    ref_or_null:同上,但是字段允许为空
    unique_subquery:自居查询中使用了索引,通过与DEPENDENT SUBQUERY等搜索一起出来
    range:范围搜索,使用了索引,返回结果集是一个范围(在where子句中使用><in等关键字),在返回函数不高的前提下,可以使用
    index:基本同下,但是或许有用到索引,不过索引使用的地方不在where条件中,如果他不是一个全表搜索,name可以接受

    explain select * from t0 order by id asc limit 10000 使用了index,但是只有1w行,可以接受
    all:全变搜索,并且没有用到索引

  • key与possible_keys
    key:sql语句使用的索引
    possible_keys:sql语句可以或可以使用的索引->若存在可以使用的索引,而且其他的索引比现在所使用索引更有效率,可以通过FORCEINDEX(索引名)强制使用索引
    key_len:索引涉及字段(组合)每行的长度(大小)(例如字段是int,长度就是4)

  • rows:sql语句查询的行数,该数字越少表示语句越有效率

  • extra:在查询中额外使用的工具或状态(有好有不好)
    Using index:仅使用索引完成,没有使用实体表,效率极高。注意下面语句select中字段的写法

    explain selectid from t0 where id < 1000
    Using where:使用条件(where,limit)限制了返回条数
    Using filsort:必须优化,表示在查询中使用数据库的排序工具,效率极差。通过出现在排序的字段没有使用索引
    Using temporary:必须优化,语句运行使用了临时表,通常出现在关联查询的排序中,并且很多时候与Using filesort一起出现。有时会出现在group by中

group by

1.select category_id from news group by category_id

2.在sql中,group by必须写在select的字段中,而且要对应好,不要加上多余字段

3.group by的字段可以有多个,把字段组合的所有全部输出

4.having与where,在一般情况下,where与having可以互动,但having还可以实现对group by的约束,如下

select category_id,count(category_id) from news group by category_id having count(category_id)>2

5.group by的很多功能都可以让php来实现

数据库其他优化

  • 尽量不要使用rand(),curdata()等数据库自带函数,因为即使是得到值是一样,但是sql不会缓存相关语句的结果。

  • 对于涉及行数过多的sql语句,可以使用limit关键限制行数,因为行数越少,查询越快,而且过多行数其实没有实际应用意义

  • 不要使用order by rand(),能在php实现的效果,不要放在mysql做,如使用array_rand让数据随机排

  • 避免使用select *,会出现搜索出多余字段的情况,影响效率。从效率上来讲,只需写上需要的字段,但是会影响程序灵活性。

  • 把ip地址保存为数字,使用方法ip2long及long2ip,该数字需要保存负数

  • 垂直分割:分表,放置数据沉余

  • 水平分割:数据表数据量越大,查询就越慢。因此可以把一些数据量非常大的数据,通过一些规则(例如是学号的开头),变成N个表来保存

  • 拆分复杂的语句,或者批量删除与插入=>宁愿拆分几句来写,不要写一句复杂

  • 不要使用永久连接

mysql建模工具,workbench

  • PK 主键

  • NN 非空

  • UQ 唯一索引

  • BIN 二进制列(不能用来查询,一般不用)

  • UN 无符号(非负)

  • ZF 补零(不用了,为了以前的dos界面看起来更整齐)

  • AI 自动增长

  • Default 默认值


如无说明,本站文章均为原创,转载或引用注明来源:https://93jc.cn/article/171.html