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语句的问题：-explainId:查询序列号，值越大优先级越高Select_type:查询类型，simple：简单Table:查询的表Type：all表示全表扫描，效率低Possible_keys：对SQL可用的索引Key:索引Key_len:Row:1.3mysql的变量和参数：Mysqlshow variables;Mysqlshow variables like ‘long_query_time’;//慢查询时间索引的详解2.1 索引创建 2.1.1 主键索引的创建主键索引的创建有两种形式