mysql数据库优化技术,MySQL数据库的优化技术详解

对MySQL优化是一个综合性的技术，主要包括:

1.表的设计合理化(符合3NF4)

2.添加适当的索引(index)[四种：普通索引、主键索引、唯一索引unique、全文索引]

3.分表技术(水平分割、垂直分割)

4.读写分离技术

5.存储过程[模块化编程，可以提高速度]

6.对MySQL配置优化[配置最大并发数]、调整缓存的大小

1.表的设计合理化(符合3NF4)

什么样的表才是符合3NF(范式)

表的范式，是首先符合1NF，才能满足2NF,进一步满足3NF

1NF.表的列具有原子性，不可再分解,即列的信息，不可分解，只要数据库是关系型数据库，就自动的满足1NF

2NF.表中的记录是唯一的,就满足2NF,通常我们设计一个主键来实现

3NF.即表中不要有冗余数据，就是说，表的信息，如果能够被推倒出来，就不能单独的设计一个字段来存放.

2.添加适当的索引(index)[四种：普通索引、主键索引、唯一索引unique、全文索引].

添加索引：

四种索引(主键索引/唯一索引/全文索引/普通索引)

1.添加：

1.1主键索引添加

当一张表，把某个列设为主键的时候，则该列就是主键索引

create table tb1_name (

id int unsigned not null primary key auto_increment,

name varchar(32) not null default '');

这时id列就是主键索引

1.2 普通索引

一般来说，普通索引的创建是先创建表，然后在创建普通索引.

create table tb2_name (

id int unsigned,

name varchar(32),

)

create index 索引名 on 表(列);

1.3 全文索引

全文索引主要是针对对文本的检索，如：文章

全文索引只针对MyISAM有用.

create table articles(

id int unsigned auto_increment not null primary key,

title varchar(200),

body text,

fulltext(title,body)

)engine=myisqm charset utf8;

如何使用全文索引：

错误用法：

select *　from articles where body like '%mysql%';[不会用到全文索引]

证明:

explain select *　from articles where body like '%mysql%';

正确用法：

select * from articles where match(title,body) against('database');

说明：

1.在MySQL中fulltext(全文索引) 只针对MyISAM生效.

2.使用方法是 match(字段名) against('关键字')

1.4 唯一索引

当表的某列被指定为unique约束时，这列就是一个唯一索引.

1.>unique 字段可以为NULL，并可以有多个NULL,但是如果是具体内容，则不能重复。

主键字段，不能为NULL，也不能重复。

create table tb4_name(id int primary key auto_increment,name varchar(32) unique);

这时，name列就是一个唯一索引

2.> 在创建表后，再去创建唯一索引.

create table tb4_name(id int primary key auto_increment,name varchar(32));

create unique index 索引名 on 表名(列名);

2.查询索引：

2.1 desc 表名[该方法的缺点，不能够显示索引名]

mysql> desc tb1_name;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(32) | NO | | | |

+-------+------------------+------+-----+---------+----------------+

2 rows in set (0.03 sec)

2.2 show index from tb1_name\G

mysql> show index from tb1_name\G

*************************** 1. row ***************************

Table: tb1_name

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.05 sec)

mysql>

创建表时如果没有指定主键索引，也可以在创建表后，再添加

alter table 表名 add primary key(列名);

3.删除

alter table 表名 drop index 索引名

如果删除主键索引

mysql> alter table 表名 drop primary key;

4.修改

先删除，在创建索引

为什么创建索引后查询速度就会变快呢？

采用二叉树算法(BTREE).

索引使用的注意事项：

索引的代价：

1.磁盘占用

2.对dml(update delete insert)语句的效率影响.

哪些列上适合添加索引?

1.在较频繁的作为查询条件字段应该创建索引

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.

更新非常频繁的字段不适合创建索引

2.不会出现在where子句中字段不该创建索引

总结：

满足一下条件的字段，才应该创建索引：

a.肯定在 where 条件经常使用

b.该字段的内容不是唯一的几个值(sex)

c.字段内容不是频繁变化的.

SQL语句优化-explain分析问题

Explain select * from emp where ename=“zrlcHd”;

会产生如下信息：

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描出的行数(估算的行数)

Extra:执行情况的描述和说明

如何选择MySQL的存储引擎:

MyISAM存储引擎

如果表对事物要求不高，同时是以查询和添加为主的，我们考虑使用MyISAM引擎.

比如：bbs中的发帖表，

对于存储引擎是MyISAM的数据库，如果经常要做删除和修改记录的操作,要定时执行

optimize table 表名，对表进行碎片整理.

INNODB存储引擎：

对事物要求高，保存的数据都比较重要，我们建议使用innodb,如订单、账户表.

Memory存储引擎：

数据变化频繁，不需要入库，同时又频繁查询和修改，考虑使用memory，memory的数据都存入内存中，

重启数据库后数据就会消失，类似redis和、memcached.

MyISAM和INNODB区别：

1.事物安全(MyISAM不支持事务，INNODB支持事务回滚)

2.查询和添加速度(MyISAM快，INNODB略慢)

3.支持全文索引(MyISAM支持，INNODB不支持)

4.锁机制(MyISAM为表锁，INNODB为行级锁)

5.MyISAM不支持外键，INNODB支持.

表的分割技术：

当一个表海量数据，怎么办：

使用分表技术

水平分割

垂直分割

把某个表的某些字段，这些字段，在查询时，并不是特别关心的，但是数据量很大，

我们可以把这些字段单独放到另外一张表，从而提高效率.

数据库参数配置:

my.cnf常用参数：

#最大并发连接数

max_connections=1000

#查询缓存大小

query_cache_size=20M

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size

当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

在my.ini修改端口3306，默认存储引擎和最大连接数