mysql数据库优化

根据韩顺平视频讲解

一、关系型数据库优化

1、数据库（表）设计合理，要符合3规范。

2、 sql语句的优化

3、数据的配置

4、适当的硬件和操作系统

数据的3规范模式（3NF）

1NF 就是具有原子性，不可分割（只要使用关系型数据库，就自动符合）

2NF 在满足1NF的基础上，在考虑此点。对记录的唯一性约束，同一张表不可能出现完全相同的记录。

这一点可以通过主键进行控制。

3NF 在满足2NF的基础上，对字段冗余性的约束，即不能由其他字段派生出来。字段信息可以通过关联的关系进行派生即可。有时需要降低范式标准。

通常通过外键的形式来解决

逆范式：

（相册功能对应的数据库设计）

每次图片更新 相册点击次数顺便更新一次

不合理的逆范式

冗余规则，1对多的关系中，尽量把逆范式的内容放在1这边。

sql 优化的一般步骤

1、通过show status命令 了解各种sql 的执行频率。

2、定位执行频率较低的sql语句，重点是在select 语句。

3、通过explain 分析低效率的sql 语句执行情况。

4、确定问题并采取相应措施。

sql语言

ddl（数据定义语言） [create alter drop]

ddml（数据操作语言）[inert rollback update]

select

dtl(数据事务语言)[commit rollback savepoint]

dcl(数据控制语句) [grant revoke]

show status命令

该命令显示mysql数据库当前状态，主要关心以com开头的命令。

show status like ‘com%’;

显示局部// 控制台的命令使用次数

show status like ‘com%’;

相当于 

show session  status like ‘com%’;

全局// 数据库从启动到查询的次数

show global status like ‘com%’;

例如：命令select 就是 Com_select

显示连接数据库的次数

show status like ‘ Connection’; 

Uptime服务器工作时间（秒）

 Show_queries：慢查询次数（默认10秒）

优化的重点是 慢查询

show variables like ‘long_query_time’;

添加索引，使查询的速度加快

1、但是以牺牲 增删改（dml语句）为代价的

2、索引 信息本身也是存放在 *.myi文件里的

需要借助explain，可以对 sql语句进行分析，预测要不要用到索引和效率。

type不应该为 all类型的

项目需要分页不应该查询 全表 所以

all

 select * from account;

完整的表扫描 通常不好

system

表仅有一行，就是const联接类型的特例

 const

表最多有一个匹配

possible_key: 表示 可能用到的索引

key_len: 索引的长度

row:表示从多少行里把其中取出来的

 using  filesort  

表示通过文件进行排序 这种应该避免，无法利用索引进行排序。

using temporary

必须使用临时表，常见是order by group by 

 using where 

不要读取表中信息，仅仅通过索引来获取数据。

加索引

1、较频繁查询的条件字段适合创建索引

2、唯一性太差的字段不适合

3、更新非常频繁的字段不适合加索引

4、不会出现 where的字段不适合加索引

BTREE  二叉树

全文索引  5.1版本时只支持 MyISAM

 sphinx+中文分析 coreseek

  复合索引

注意：索引不起作用的情况

1、模糊查询时 like ‘%**’; %放在前面时不起作用。

应该是  like ‘**%’;

或者是 like ‘*%*’;

2、如果条件中有 “or”，即使条件带索引也不会起作用。

3、对于多列索引，不是使用的第一部分，则不会使用索引。

4、列类型是字符串类型，那么一定要将数据引用起来用到索引，否则不会用到索引。例如;select * from account where name = 刘汪洋；不用索引

应该改成：select * from account where name = ‘刘汪洋’；

查看索引的使用情况：

show status like ‘Hander_read%’

Hander_read_key ：这个值越高越好，越高表明，索引查询的次数高；

Hander_read_rnd_next ：这个值越高说明查询效率低。

大量插入数据

对于 MyISAM 

//禁用索引

alter table table_name disable keys;

//导入数据

loading data;

//启用索引

alter table table_name enable keys;

相对于InnoDB

1，要将导入的数据按照主键的排序。

2， set unique_checks= 0,关闭唯一校验。

3，set  autocommit ,关闭自动提交。

MyISAM和相对于InnoDB

1、MyISAM不支持外键，InnoDB支持

2、MyISAM不支持事务，InnoDB支持

3、对信息的处理方式不同

InnoDB在图示位置存放

删除后数据 自动释放

MyISAM

创建的表  对应三个文件  数据放在其中*.MYD。

删除后 空间不释放 容易把 磁盘占满！！

需要定时清空

optimize table 表名

常见的sql优化手法

1、使用group by ，由于文件排序比较慢，group  by 默认排序，在不需要排序是 禁用

3、多用  join 少用子查询  ，join不再内存中创建临时表。

4、使用or时加索引

  select * from table_name where name= ‘张三’ or name =’李四’；

自己复制自己批量插入到表中 

 insert into 表名 select * from 表名

----------------------参见《索引》--------------------begin

索引

注意:

1、不要过度索引

2、索引条件列（where后面最频繁的条件列）

3、尽量索引散列值，过于集中的值不要索引。

索引类型

1、普通索引（index）：仅仅是加快查询速度

2、唯一索引(unique index)：行上的值不能重复

3、主键索引(primary key)：主键不能索引

主键索引必定是唯一的，唯一索引不一定是主键，

一张表上只能一个主键，可以有一个或者多个唯一索引。

4、全文索引(fulltext index)：在mysql 默认情况下对于中文作用不大。

查看 一张表上的索引

show index from 表名 (/G可以横着显示);

show indexes from表名 (/G可以横着显示);

show keys from表名 (/G可以横着显示);

索引不知道名称默认以索引的列名作索引

建立索引

alter table 表名 add index/unique/fulltext [索引名](列名) 

[索引名]可以不写 ，不写默认与列名相同。

alter table 表名 add primary key （列名）

创建索引示例：

为 tel列 创建普通索引

为email列 添加唯一索引

为intro列添加全文索引

为id列添加主键

删除索引

alter  table drop index 索引名

或者是

drop index  索引名  on 表名

示例：

删除email（唯一）索引

删除主键索引

全文索引使用

查看匹配度

select 列名, match (索引) against (‘ 索引词’) from表名;

新发现 as  支持 汉字

全文停止字：

全文索引，不针对非常频繁的词，做索引。

针对汉语意义不大，因为因为英文单词可以依赖空格等标点来判断单词界限，进而对单词进行索引，而中文mysql无法识别单词界限。

以下表列出了默认的全文停止字 ：

大数据量时，先把索引去掉，导入后，统一加上索引。

索引加快查询速度，降低增删改的速度。

额外：

show create table  表名

 可以查看  创建表的语句

发现问题

使用全文索引时加上引擎

--------------------------------------------------------------end