MySQL优化笔记

对MySQL进行优化主要包括一下8个方面
1.表的设计符合3NF
2.适当添加索引
3.分表技术
4.读写分离
5.存储过程
6.配置参数优化
7.升级服务器硬件
8.定时清除冗余数据、定时清理碎片

1.1
1NF.表的列具有原子性,不可再分解,关系型数据库自动满足1NF.
数据库分类:
关系型数据库,有行列概念.
菲关系型数据库,面向对象或者集合.
NoSql,如MongoDB(面向文档).
2NF.表中的记录都是唯一的,可通过主键实现.(主键最好业务无关)
3NF,表中不要有冗余数据,即表中的字段不应被推导出来.
反3NF,有时候为了提高运行效率,允许适当的冗余.

2.1
Sql语句本身的优化
命令:shwo status;查询数据库运行状态.
命令:show status like ‘uptime’;查询运行时间.
命令:show status like ‘com_%’;查询操作数量.
命令:show [global|session] status like ‘com_update’;默认session,统计当前会话update操作数,若为global则统计从数据库启动到现在的update操作数.
命令:show status like ‘connections’;统计连接数.
命令:show status like ‘slow_queries’;统计慢查询数量.
命令:show variables like ‘long_query_time’;查询参数慢查询时间.默认10秒.
命令:set long_query_time=1;设置慢查询时间为1S.

慢查询实验：

-- 缩写loc地点
DROP TABLE IF EXISTS tdept;
CREATE TABLE tdept(
    fno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '编号',
    fname VARCHAR(20) NOT NULL DEFAULT '' COMMENT '名称',
    floc VARCHAR(13) NOT NULL DEFAULT '' COMMENT '地点'
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT '部门';

INSERT INTO tdept VALUES(100,'财务部','南京');
INSERT INTO tdept VALUES(200,'开发部','广州');
INSERT INTO tdept VALUES(300,'销售部','武汉');

DROP TABLE IF EXISTS temp;
CREATE TABLE temp(
    fno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '编号',
    fname VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
    fjob VARCHAR(9) NOT NULL DEFAULT '' COMMENT '职位',
    fmgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级编号',
    fhiredate DATE NOT NULL COMMENT '入职时间',
    fsal DECIMAL(7,2) NOT NULL COMMENT '薪水',
    fcomm DECIMAL(7,2) NOT NULL COMMENT '红利',
    fdeptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号'
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT '员工';

DROP TABLE IF EXISTS tsalgrade;
CREATE TABLE tsalgrade(
    fgrade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '等级',
    flosal DECIMAL(7,2) NOT NULL COMMENT '最低薪水',
    fhisal DECIMAL(7,2) NOT NULL COMMENT '最高薪水'
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT '薪水等级';

INSERT INTO tsalgrade VALUES (1,700,1200);
INSERT INTO tsalgrade VALUES (2,1201,1400);
INSERT INTO tsalgrade VALUES (3,1401,2000);
INSERT INTO tsalgrade VALUES (4,2001,3000);
INSERT INTO tsalgrade VALUES (5,3001,9999);
-- (FLOOR(RAND()*2)*100 + 100)
DROP FUNCTION IF EXISTS rand_deptno;
DELIMITER $$
CREATE FUNCTION rand_deptno()
RETURNS INT(5)
BEGIN
    DECLARE deptno INT(5) DEFAULT 100;
    SET deptno = deptno + FLOOR(RAND()*3)*100;
    RETURN deptno;
END $$
DELIMITER ;

-- 输入随机字符串长度输出随机字符串
DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i<n DO
        SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS insert_temp;
DELIMITER $$
CREATE PROCEDURE insert_temp(IN start_num INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO temp VALUES((start_num+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,3000,rand_deptno());
    UNTIL i = max_num END REPEAT;
    COMMIT;
    SET autocommit = 1;
END $$
DELIMITER ;

-- 10万-400万
CALL insert_temp(100000,4000000);

如上,测试数据准备完毕.
指定记录慢查询命令,set slow_query_log=on;[5.6版本]
指定记录慢查询命令,bin\mysqld.exe –save-mode –slow-query-log [5.5版本以上]
日志文件存放位置参考my.ini[datadir]
指定记录慢查询命令,bin\mysqld.exe -log-slow-query=d:/slow_query.log [5.0版本]
查看慢查询日志,定位问题

# Time: 180508 21:58:18
# User@Host: root[root] @ localhost [::1]  Id:     1
# Query_time: 1.035705  Lock_time: 0.000500 Rows_sent: 0  Rows_examined: 4000000
SET timestamp=1525787898;
select * from temp where fno = '2000000';

3.1
索引优化
建表语句中指定索引或者建表后添加索引.
主键索引：
建立主键约束即可.
普通索引:
create index index_name on tdept(fno);
全文索引(myisam):
create table tarticle(
ftitle varchar(20),
fbody text,
FULLTEXT(ftitle,fbody)
);
使用全文索引:
select * from tarticle where match(ftitle,fbody) against (‘keyword’);
使用explain分析执行语句;
explain select * from tarticle where match(ftitle,fbody) against (‘keyword’);
全文索引注意:
在mysql中fulltext索引只针对myisam
mysql提供的fulltext索引针对英文>需要sphinx处理中文
使用方法关键字match against
全文索引中的停止词指的是不应无穷的创建索引，因此，对于一些常用词或者字符，没必要创建索引，就不会创建索引
唯一索引:
建立唯一约束即可.
主键约束与唯一约束的区别:主键是非空且不可重复,唯一是可为null，可存在多个null，当内容非null时不可重复.

相关索引命令:
desc tdept;
show indexes from tdept;
show keys from tdept;
alter table tdept drop index index_name;
alter table tdept dtop primary key(fno);

创建索引后搜索速度变快的原理:全表扫描VS二叉树算法.
使用索引的代价：
占用磁盘空间.
dml操作变慢.
是否添加索引？经常作为查询条件yes,重复性很高no,经常更新no.

其他注意事项
1.创建复合索引后，通常只要使用最左边的列就会使用索引.
2.模糊查询通配符不能出现在最左边，如like ‘%keyword’不会使用索引.
3.如果条件中有or，那么只要有一个条件没建立索引，就不会使用索引，即最好全部条件都建立索引，或者尽量避免使用or.
4.如果列类型是字符串，则应当用引号括起来，即避免隐式数值字符串互转.
5.若mysql估计出全表扫描速度比使用索引更快(比如表数据仅有一条?)，则不使用索引.

学会使用explain 分析语句.
explain select * from tdept order by fno \G

查看索引使用情况
show status like ‘Handler_read%’\G
Handler_read_key,值越高越好，表示使用索引查询到的次数.
Handler_read_rnd_next,值越高，表示查询越低效.

sql小技巧
group by语句后面增加order by null防止默认排序.
某些情况下使用连接查询代替子查询避免内存中创建临时表.

常用存储引擎特点
myisam不支持事务,添加、查询速度快,支持全文索引,使用表锁，不支持外键
innodb支持事务,添加、查询速度慢,不支持全文索引,使用行锁,支持外键
memory不支持事务，速度快,不支持全文索引,使用表锁,不支持外键，不入库

若使用myisam需要定时进行碎片整理，因为删除表数据后磁盘文件不会自动压缩.

备份恢复
mysqldump -u root -pyourpass dbname [tablename1 tablename2] > filepath
source filepath
学习使用定时任务(windows .bat/linux crontab .shell)

分表技术
水平分割:一张数据量极大的表按照某种规则分割为多张结构完全一样的表,数据操作需要根据某种规则定位到目标表,但是需要限制查询，即不应再让用户随意的查询[全部数据]，另外最好查询前必须输入条件做限制.
垂直分割:将信息量大但是很少查询到的字段转移到单独的其他表中，提高查询效率.

存储过程相比sql语句，少了一个编译的过程（编译-执行-缓存），所以效率更高！

my.ini中配置最大连接数、查询缓存大小提高性能.