韩顺平mysql优化笔记.docx

1、韩顺平mysql优化笔记 Mysql数据库的优化技术对mysql优化时一个综合性的技术，主要包括 a: 表的设计合理化(符合3NF)b: 添加适当索引(index) 四种: 普通索引、主键索引、唯一索引unique、全文索引c: 分表技术(水平分割、垂直分割)d: 读写写: update/delete/add分离e: 存储过程 模块化编程，可以提高速度f: 对mysql配置优化 配置最大并发数my.ini, 调整缓存大小，默认为100，max_connection=1000 g: mysql服务器硬件升级h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) 什么样的表才是符合3NF

2、 (范式)表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF 数据库的分类关系型数据库: mysql/oracle/db2/informix/sysbase/sql server非关系型数据库: (特点: 面向对象或者集合)NoSql数据库: MongoDB(特点是面向文档)2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现3NF: 即表中不要有冗余数据

3、, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF:反3NF : 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。 案例 ： Sql语句本身的优化问题是： 如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)1 首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/del

4、ete. / 当前连接)show status常用的:show status like uptime ; （mysql数据库启动了多长时间）show stauts like com_select show stauts like com_insert .类推 update delete（显示数据库的查询，更新，添加，删除的次数） show session|global status like . 如果你不写 session|global 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)/显示到mysql数据库的连接数show s

5、tatus like connections; /显示慢查询次数show status like slow_queries;2 如何去定位慢查询构建一个大表(400 万)-存储过程构建默认情况下，mysql认为10秒才是一个慢查询. 修改mysql的慢查询.show variables like long_query_time ; /可以显示当前慢查询时间（默认当前的session的慢查询时间）mysql show global variables like long_query_time;+-+-+| Variable_name | Value |+-+-+| long_query_time

6、 | 10.000000 |（但是重启mysql之后，long_query_time依然是my.ini中的值）set long_query_time=1 ;/可以修改慢查询时间构建大表-大表中记录有要求, 记录是不同才有用，否则测试效果和真实的相差大.创建:CREATE TABLE dept( /*部门表*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/dname VARCHAR(20) NOT NULL DEFAULT , /*名称*/loc VARCHAR(13) NOT NULL DEFAULT /*地点*/) ENGINE=My

7、ISAM DEFAULT CHARSET=utf8 ;CREATE TABLE emp(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ename VARCHAR(20) NOT NULL DEFAULT , /*名字*/job VARCHAR(9) NOT NULL DEFAULT ,/*工作*/mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/hiredate DATE NOT NULL,/*入职时间*/sal DECIMAL(7,2) NOT NULL,/*薪水*/comm DECIM

8、AL(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 VAL

9、UES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);为了存储过程能够正常执行，我们需要把命令执行结束符修改delimiter $set global log_bin_trust_function_creators=TRUE;#This function has none of DE

10、TERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法create function rand_string(n INT) returns varchar(255) #该函数会返回一个字符串begin #chars_str定义一个变量 chars_str,类型是 varchar(100),默认值abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; declare chars_str varchar(100) default abcdefghijklmnopqrstuvwxyzABCDE

11、FJHIJKLMNOPQRSTUVWXYZ; declare return_str varchar(255) default ; declare i int default 0; while i explain select * from articles where body like mysql%G* 1. row * id: 1 select_type: SIMPLE table: articles type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where1 row

12、in set (0.00 sec)证明:explain select * from articles where body like %mysql%正确的用法是:select * from articles where match(title,body) against(database); 【可以】mysql explain select * from articles where match(title,body) against ( mysql)G* 1. row * id: 1 select_type: SIMPLE table: articles type: fulltextposs

13、ible_keys: title key: title key_len: 0 ref: NULL rows: 1 Extra: Using where1 row in set (0.00 sec) 说明:1. 在mysql中fulltext 索引只针对 myisam生效2. mysql自己提供的fulltext针对英文生效-sphinx (coreseek) 技术处理中文3. 使用方法是 match(字段名.) against(关键字)4. 全文索引：停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the

14、）mysql select match(title,body) against (database) from articles;（输出的是每行和database的匹配度）+-+| match(title,body) against (database) |+-+| 0.6554583311080933 | 0 | 0 | 0 | 0.6626645922660828 | 0 |+-+6 rows in set (0.01 sec)1.4唯一索引当表的某列被指定为unique约束时，这列就是一个唯一索引create table ddd(id int primary key auto_incre

15、ment , name varchar(32) unique);这时, name 列就是一个唯一索引.unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，但是不能存有重复的空字符串.主键字段，不能为NULL,也不能重复.在创建表后，再去创建唯一索引create table eee(id int primary key auto_increment, name varchar(32);create unique index 索引名 on 表名 (列表.);2. 查询索引desc 表名 【该方法的缺点是：不能够显示索引名.】show index(es) from

16、表名show keys from 表名3. 删除alter table 表名 drop index 索引名; 如果删除主键索引。alter table 表名 drop primary key 这里有一个小问题4. 修改先删除，再重新创建. 为什么创建索引后，速度就会变快?原理示意图:. 索引使用的注意事项索引的代价:1. 占用磁盘空间2. 对dml操作有影响，变慢 在哪些列上适合添加索引?总结: 满足以下条件的字段，才应该创建索引.a: 肯定在where条件经常使用 b: 该字段的内容不是唯一的几个值(sex) c: 字段内容不是频繁变化. 使用索引的注意事项把dept表中，我增加几个部门:a

17、lter table dept add index my_ind (dname,loc); / dname 左边的列,loc就是右边的列说明，如果我们的表中有复合索引(索引作用在多列上)， 此时我们注意:1， 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。explain select * from dept where loc=aaaG 就不会使用到索引2，对于使用like的查询，查询如果是 %aaa 不会使用到索引 aaa% 会使用到索引。比如: explain select * from dept where dname like %aaaG不能使用索引，即，在li

18、ke查询时，关键的 关键字 , 最前面，不能使用 % 或者 _这样的字符.， 如果一定要前面有变化的值，则考虑使用 全文索引-sphinx.3. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字select * from dept where dname=xxx or loc=xx or deptno=454. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须), 也就是，如果列是字符串类型，就一定要用 把他包括起来.5. 如果mysql估计使用全表扫描要比使

19、用索引快，则不使用索引。explain 可以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令. 如何查看索引使用的情况:show status like Handler_read%;大家可以注意：handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。 handler_read_rnd_next:这个值越高，说明查询低效。 sql语句的小技巧1. 在使用group by 分组查询是，默认分组后，还会排序，可能会降低速度.比如: 在group by 后面增加 order by null 就可以防止排序.2. 有些情况下，可以使用

20、连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。select * from dept, emp where dept.deptno=emp.deptno; 简单处理方式select * from dept left join emp on dept.deptno=emp.deptno; 左外连接，更ok! 如何选择mysql的存储引擎在开发中，我们经常使用的存储引擎 myisam / innodb/ memorymyisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.INNODB 存

21、储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.问 MyISAM 和 INNODB的区别1. 事务安全（MyISAM不支持事务和外键，INNODB支持事务和外键）2. 查询和添加速度（MyISAM批量插入速度快）3. 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）4. 锁机制（MyISAM是表锁，innodb是行锁）5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用

22、memory, 速度极快. （如果mysql重启的话，数据就不存在了） 如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理举例说明: create table test100(id int unsigned ,name varchar(32)engine=myisam;insert into test100 values(1,aaaaa);insert into test100 values(2,bbbb);insert into test100 values(3,ccccc);我们应该定义对myisam进行整理optimize table test100;mysql_query(“optimize tables $表名”);技术就是窗户纸.-经常和技术好的人交流 PHP定时完成数据库的备份 1 手动备份数据库(表的)方法cmd控制台:mysqldump u root proot 数据库 表名1 表名2.