Mysql优化专题 III

一、索引的概述

索引,它们包含着对数据表里所有记录的引用指针。更通俗的说,索引就相当于目录。

它的优缺点有哪些呢?

可以大大加快查询速度,这也是创建索引的最主要原因,通过索引可以在查询的过程中,使用优化隐藏器,提高系统的性能。

也有缺点,索引需要额外的维护成本;因为索引是单独存在的文件,对数据的增加,删除,修改,这些都需要额外的IO,会降低增删改的效率。

二、索引的基本使用

1)、创建索引:(三种方式)

第一种

第二种,使用ALTER TABLE命令去增加索引:ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。

索引名index_name可自己命名,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

第三种方式:使用CREATE INDEX命令创建

CREATE INDEX可对表增加普通索引或UNIQUE索引。(但是,不能创建PRIMARY KEY索引)

三、索引的基本原理

索引用来快速的寻找那些有特定值的记录,如果没有索引,一般查询会遍历整张表。

索引的原理很简单,就是把无序的数据变成有序的查询。

把创建了索引的列的内容进行排序;对排序的结果生成倒排表;在倒排表内容上拼上数据地址链;在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据。

四、创建索引 的原则

索引虽好,但是不能滥用,总是有原则要遵守的

1) 最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2)较频繁作为查询条件的字段才去创建索引

3)更新频繁字段不适合创建索引

4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)

5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

6)定义有外键的数据列一定要建立索引。

7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

8)对于定义为text、image和bit的数据类型的列不要建立索引。

五、表的设计及优化

1)、 先来说说数据库三范式

A、属性具有原子性

B、记录的唯一性约束

C、属性冗余性的约束

第三范式是经常被打破的,且不可避免,其实就是在数据冗余和处理速度之间找到平衡点。

2)、合适的字段属性

A、数值型字段的比较要比要比字符串的比较效率高得多。

B、建议不要使用Dubble,不仅是存储长度的问题,更会有精确度的问题。

C、char 是固定长度,所以他的处理速度要比varchar要快,但是会浪费存储空间,不能在行尾保存空格,InnoDB建议使用varchar,因为在其中,内部行存储格式没有区分固定长度和可变长度。

D、尽量不要使用null,可以用not null +DEFAULT代替

E、text与blob区别:blob保存二进制数据;text保存字符数据,有字符集。text和blob不能有默认值。

实际场景:text与blob主要区别是text用来保存字符数据(如文章,日记等),blob用来保存二进制数据(如照片等)。blob与text在执行了大量删除操作时候,有性能问题(产生大量的“空洞“),为提高性能建议定期optimize table 对这类表进行碎片整理。

F、自增字段使用要注意,不利于数据迁移。

G、尽量将字段定义为 NOT NULL 由于Mysql中含有NULL的很难进行查询优化,NULL值会使索引以及索引的统计信息变得很复杂,可以使用0或者空字符串来代替。

H、时间类型尽量使用 TIMESTAMP 因为其存储空间只需要 DATETIME 的一半,且日期类型中只有它能够和实际时区相对应。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。

3)索引

4)表的拆分(大表拆小表)

A、垂直拆分(就是将之前很多列的表拆成多张表)

注意:垂直拆分应该在数据库设计之初就执行的,然后查询的时候用 join关联起来就好,拆分原则:把不常用的字段放在一张表; 把text blob等大字段的放在附表; 经常组合查询的列放在一张表。

当然了,也会有缺点:会有冗余字段; 必须join操作。

B、水平拆分(如果你发现某个表的记录太多了,例如超过一千万条,则要对该表进行水平分割。水平分割的做法是,以该表主键的某个值为界线,将该表的记录水平分割为两个表。)

当然了,我们还可以用增量法,如流水这类的不会改变的数据,我们用增量查询。

1、创建一个日充值表,记录下吗,每天的充值总额。

2、每天用定时器对当前的充值纪录进行结算

3、创建每月充值表,每月最后一天用定时器计算总额。

4、若要查询总额,则从月报表中汇总,再从日报表查询当天之前的数据汇总,再加上今天的使用当天流水表记录今天的流水,三张表加起来,汇总。这样子效率是极好的!

5)“三少”原则

数据库的表越少越好

表的字段越少越好

字段中组合主键,组合索引越少越好

当然了,这里的少是相对的,是减少数据冗余的重要设计理念。

六、视图

视图就是基于SQL 查询的结果集的可视化的表。

优点:

简化查询语句,通过定义视图,使数据库看起来结果简单,清晰。

权限控制,视图里只有部分数据列

大数据分表的时候,比如某张表的数据有100万条,那么可以将这张表分成四个视图。按照对id取余计算。

用户能以多种角度看待同一数据。

对重构数据库提供了一定的逻辑独立性。

缺点:

性能差,把视图查询转化为对基本表的查询,如果是个很复杂的表呢?即使这个视图很简单。

修改限制,当用户视图修改视图时,数据库必须把它转化为对基本表的修改,对于简单的视图来说是可以的,复杂的会很难 。

使用场景:

需要权限控制的时候; 如果某个查询结果出现的非常频繁的时候,使用视图会很简单的; 关键信息来源于多个复杂的表。

在这里,我们要注意:增删改最终都是对基础表的操作,虽然视图中可以更新数据,但是有很多限制,一般情况下,最好将视图作为查询数据的虚拟表,不要通过视图更新数据,因为在更新的时候如果没有考虑到很多的限制会更新失败。

视图使用注意点:

(1)修改操作时要非常非常小心,不然不经意间你已经修改了基本表里的多条数据;

(2)视图中的查询语句性能要调到最优;更多的是禁止修改视图

不可更改的情况如下:视图中含有以下的都不可被修改了。

一)聚合函数(SUM(), MIN(), MAX(), COUNT()等)。

(二)DISTINCT。如下错误。

(三)GROUP BY

(四)HAVING

(五)UNION或UNION ALL

(六)位于选择列表中的子查询

(八)FROM子句中的不可更新视图

(九)WHERE子句中的子查询,引用FROM子句中的表。

(十)ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。

感兴趣的同学可以收藏,点赞啊,一起加油吧!!

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180328G08TXG00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券