MySQL数据库解析

MySQL数据库优化常见方法：

1.表的设计要合理（满足3NF，即第三范式）

2.适当创建索引（主键索引、唯一索引、普通索引、全文索引、空间索引）

3.对SQL语句优化-》定位慢查询（explain）

4.使用分表技术（水平分表，垂直分表），分区技术

5.配置读写分离

6.创建适当存储过程、函数、触发器

7.对my.ini优化，优化配置

8.软件硬件升级

构建海量表，定位慢查询

为了讲解优化需要构建海量表（8000000条数据），而且每条数据不一样，使用存储过程完成该过程。

1.构建还量表：

（1）创建一个测试数据库：

create database testdb;

(2) 创建表：

Create table dept(/*部门表*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

dname VARCHAR(20) NOT NULL DEFAULT “”,

loc VARCHAR(13) NOT NULL DEFAULT “”

)ENGINE=MyISAM DEFAULT CHARSET=UTF8;

CREATE TABLE emp(/*雇员表*/

Empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/

Ename VARCHAR(20) NOT NULL DEFAULT “”,/*名字*/

Job VARCHAR(9) NOT NULL DEFALUT “”,/*工作*/

Mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/

Hiredate DATE NOT NULL,/*入职时间*/

Sal DECIMAL(7,2) NOT NULL,/*薪水*/

Comm DECIMAL(7,2) NOT NULL,/*红利*/

Deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/

)ENGINE=MyISAM DEFAULT CHARSET=UTF8;

CREATE TABLE salgrade(/*工资级别表*/

Grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

Losal DECIMAL(17,2) NOT NULL,

Hisal DECIMAL(17,2) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=UTF8;

#测试数据

INSERT INTO salgrade VALUES(1,700,1200);

INSERT INTO salgrade VALUES(2,1200,1400);

INSERT INTO salgrade VALUES(3,1401,2000);

INSERT INTO salgrade VALUES(4,1540,1700);

INSERT INTO salgrade VALUES(5,1401,2000);

INSERT INTO salgrade VALUES(6,2001,3000);

1.1使用索引：

一个表（存储引擎是MyISAM），对应三个文件，xx.frm结构文件，xx.myd数据文件，xx.myi索引文件

●给emp表添加主键索引

alert table emp add primary key (empno);

没加索引之前

加了索引之后

1.2如何定位慢查询（slow query）

介绍：默认情况下，mysql是不会记录慢查询的，所以我们在测试时可以指定mysql 记录慢查询。

●启动时，这样启动：

Mysql>bin/mysqld.exe --safe-mode --slow-query-log

也可以在mysql中使用：

Set global show_query_log = ‘ON’;//开启慢查询

Set long_query_time = 1;//设置慢查询时间为1秒

说明：query_time：是查询时间（如果大的话加索引）

Lock_time：是等待时间（如果大的话读写分离）

1.3如何分析一个SQL语句的问题：-explain

Id:查询序列号，值越大优先级越高

Select_type:查询类型，simple：简单

Table:查询的表

Type：all表示全表扫描，效率低

Possible_keys：对SQL可用的索引

Key:索引

Key_len:

Row:

1.3mysql的变量和参数：

Mysql>show variables;

Mysql>show variables like ‘long_query_time’;//慢查询时间

第2章索引的详解

2.1 索引创建

2.1.1 主键索引的创建

主键索引的创建有两种形式：

1.在创建表的时候，直接指定某列或某几列为主键，这时就有主键索引

2.添加表后再指定主键索引

直接创建主键索引

●先创建表，再指定主键

增加主键：

ALTER TABLE 表名ADD PRIMARY KEY(列名1, 列名2,...)

●主键索引的特点：

1) 一个表最多只能有一个主键

2) 一个主键可以指向多列（复合主键）

3) 主键索引的效率最高，因此我们应该给id,一般id是自增

4) 主键索引列是不能重复，也不能为null

2.1.2 唯一索引的创建

●直接在创建表的时候，指定某列或某几列为唯一索引

●把表创建好后，再指定某列或某几列为唯一索引

第一种方式：create unique index index_name ON tableName(col_name)

第二种方式：alter table tableName add unique (col_name)

为ddd表增加一个字段

说明：使用create unique index 指令，必须指定索引名

说明：使用alter table指令，可以指定索引名，也可以不指定索引名

⏹唯一索引的特点

1)一个表可以有多个唯一索引

2)唯一索引列的数据不能重复，但是如果没有指定not null，唯一索引是可以为null，而且可以有多个但是不能为‘’