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