MySQL数据库答题.docx
文本预览下载声明
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记录慢查询。
启动时，这样启动：
Mysqlbin/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的变量和参数：
Mysqlshow variables;
Mysqlshow variables lik
显示全部