mysql优化

范式

第一范式 ----- 遵循原子性

 原子性：原子性即操作不可再分。

第二范式----- 唯一性  

 数据库中的数据要有唯一性。

第三范式 ----- 减少冗余  

 冗余：重复的数据 ， 减少冗余 尽量的不要出现重复的数据，并不能完全消除冗余。

数据库表的设计

1 对 1 ： 一张表 二张表 （user_login user_info ）

1对N ： 二张表 通过外键维护关系。外键添加到多的一方（student grade ）

N对N ： 三张表 通过中间表维护关系。 （student teacher）

 SQL优化

 慢SQL

 优化SQL的第一件事情就是定位慢查询。

slow_query_log 慢查询开启状态

slow_query_log_file 慢查询日志存放的位置（这个目录需要 MySQL 的运行帐号的可写权限， 一般设置为 MySQL 的数据存放目录）

long_query_time 查询超过多少秒才记录

 查看慢查询相关参数

show variables like 'slow_query%';

 show variables like 'long_query_time';

 .设置慢查询参数

 方法一：全局变量设置(临时性的设置，mysql服务器重启会失效)

将 slow_query_log 全局变量设置为“ON”状态 

set global slow_query_log='ON';

set global slow_query_log_file='/var/lib/mysql/linux-base-slow.log';

set global long_query_time=1;

 方法二：配置文件设置

 修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]

slow_query_log = ON

slow_query_log_file = /var/lib/mysql/linux-base-slow.log

long_query_time = 1

 重启MySQL服务

 service mysqld restart

 我们可以通过这个SQL语句，模拟慢查询

SELECT SLEEP(15)

 在日志文件中记录的慢查询sql

 索引

 索引是帮助数据库高效获取数据的排好序的数据结构

索引分类：主键索引、唯一索引、普通索引、全文索引、组合索引  

 主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值

 唯一索引：用来建立索引的列的值必须是唯一的，允许空值

 普通索引：用表中的普通列构建的索引，没有任何限制

 全文索引：用大文本对象的列构建的索引

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值  

 CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

         [index_type]

        ON tbl_name (key_part,...)

        [index_option]

        [algorithm_option | lock_option] ...

key_part:

        col_name [(length)] [ASC | DESC]

 执行计划explain

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句

的。分析你的查询语句或是表结构的性能瓶颈。 通过 explain 我们可以获得以下信息：

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

explain select * from tb_test where c1= 'A0100'

explain select * from tb_test where c2= 'A0100'

 索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持

多种索引类型，如 BTree索引，B+Tree索引，哈希索引，全文索引 等等

 哈希索引：

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，

然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速

度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持

范围查找和排序的功能。

 全文索引：

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常

的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那

么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE

%word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时

FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据

据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如

果没有同时出现using where，表名索引用来读取数据而非执行查询动作。

4、Using where :表明使用where过滤

5、using join buffer:使用了连接缓存

6、impossible where:where子句的值总是false，不能用来获取任何元组

7、select tables optimized away：在没有group by子句的情况下，基于索

引优化Min、max操作或者对于MyISAM存储引擎优化count（

*），不必等到

执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值

的动作。

这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER

或者CREATE INDEX来添加

 BTree索引

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

每个叶子结点的高度一样，等于h；

每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结 点两端一定是key；

叶子结点指针都为null；

非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的 数据；

 B+Tree索引

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非叶子结点不存储数据，只存储键值；

B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data

数据的物理地址；

B+Tree的每个非叶子节点由n个键值key和n个指针point组成

**带顺序索引的B+TREE****

很多存储引擎在B+Tree的基础上进行了优化， 添加了指向相邻叶节点的指针 ，形成了带有顺序

访问指针的B+Tree，这样做是为了 提高区间查找的效率 ，只要找到第一个值那么就可以顺序的

查找后面的值。

聚簇索引和非聚簇索引

 MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只

是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物

理地址。

非聚簇索引的数据表和索引表是分开存储的。

非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。

插入顺序不受键值影响。

只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)

 InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是

键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁

盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的

顺序插入，否则会频繁的引起页分裂，严重影响性能。

在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。