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（使用临时表总会使视图成为不可更新的）。

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