MySQL-DB的优化技术
MySQL-DB的优化技术
对mysql优化时一个综合性的技术，主要包括
a. 表的设计合理化（符合3NF）
b. 添加适当索引（index）
c. 分表技术（水平分割，垂直分割）
d. 读写分离
e. 存储过程（模块化编程，可以提高速度）
f. 对mysql配置优化（my.ini）
max_connections=1000（一般网站）调整缓存大小
g. mysql服务器硬件升级
h. 定时的去清除不要的数据，定时进行碎片整理（MyISAM）
什么样的表才是符合3NF（范式）
表的范式，是首先符合1NF，才能满足2NF，进一步满足3NF。
1NF：即表的列具有原子性，不可再分解，即列的信息不能分解，只有数据库是关系性数据库就自动满足1NF。
2NF：表中的一条记录是唯一的，就满足2NF，通常我们设计一个主键来实现。
3NF：即表中不要有冗余数据，就是说，表的信息，如果能够被推导出来，有时提高效率，增加冗余字段，采用反3NF更加合理。
添加适当的索引
2.1 sql语句本身的优化
如何从一个大项目中，迅速定位执行速度慢的语句？
-
了解mysqlDB运行的状态如何查询（想知道mysql运行的时间／一共执行了多少次select/update/delete，当前连接数）
-show status
-show status like “uptime”
-show status like “com_select”
-show [session| global] status like “com_insert” 类推：update，delete
-show status like ‘connections’;
-netstat –anp 查找端口
-show status like “slow_queries”;-慢查询 -
如何去定位慢查询
构建一个大表（400万）->存储过程构建
默认情况下，mysql认为10秒才是一个慢查询
修改mysql的慢查询.
-show variables like ‘long_query_time’;//显示当前慢查询的时间
set long_query_time = 1//修改慢查询的时间
构建大表：
为了存储过程能够正常执行，我们需要把命令执行结束符修改下
delimiter $$ -
在默认情况下mysql不记录慢查询日志，需要在启动的时候指定
bin\mysqld.exe –safe-mode –slow-query-log[mysql5.5可以在my.ini指定]
bin\mysqld.exe –log-slow-queries=d:/abc.log[低版本mysql5.0可以在my.ini指定]
先关闭mysql，再重新启动。如果启用了慢查询日志，默认把文件放在my.ini文件中记录的位置。
2.2优化问题
通过explain语句可以分析mysql如何执行sql语句。
添加索引-四种（主键索引、唯一索引、全文索引、普通索引）
1.主键索引
1.1添加索引
当一张表，把某个列设为主键的时候，则该列就是主键索引。主键会自动索引。
如果创建表时，没有指定主键索引，也可以在创建表后添加：
alter table 表名 add primary key（列名）
1.2查询索引
desc 表名（缺点：不能显示索引名）
show index(es) from 表名
show keys from 表名
2.全文索引
全文索引，主要是针对文件，文本的检索，比如文章，全文索引针对MyISAM有用。
创造表时指定索引类型engine=myisam charset utf8
错误用法：
select * from articles where body like ‘%mysql%’;【不会使用到全文索引】
证明：
explain select * from articles where body like ‘%mysql%’
正确的用法是：
select * from articles where match(title,body) against （‘database’）【可以】
说明：
1.在mysql中fulltext 索引只针对myisam生效
2.mysql自己提供的fulltext，针对英文生效->sphinx(coreseek)技术处理中文
3.使用方法是match(字段名）against（'关键字’）
4.全文索引一个叫停止词，因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词。a an the 等。
3. 唯一索引
1.当表的某列被指定为unique约束时，这列就是一个唯一索引（索引内的值不允许重复）。
create table ddd(id int primary key auto_imcrement,name varchar(32) unique);
这时name列就是一个唯一索引。
unique 字段可以为NULL,并可以有多个，但是如果有具体的内容，则不能重复
主键字段，不能为NULL，也不能重发
2.在创建表后，再去创建唯一索引
create table eee(id int primary key auto_increment ,name varchar(32));
create unique index 索引名 on 表名（列名）
3.复合索引
3.1创建
create index 索引名 on 表（列1，列2）
3.2删除
alter table 表名 drop index 索引名；
如果删除主键索引：
alter table 表名 drop primary key
3.3修改
先删除，再重新创建
索引使用的注意事项。
2.3索引的代价
1.磁盘占用
2.对dml(update delete insert)语句的效率影响，变慢，因为要维护索引文件
存储引擎 允许的索引类型
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH,BTREE
2.4在哪些列上适合添加索引
1．较频繁的作为查询条件字段应该创建索引
Select * from emp where eno = 1
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
Select * from emp where sex=‘男’
3. 更新非常频繁的字段不适合创建索引
Select * from emp where logincount = 1
4. 不会出现在where子句中字段不该创建索引
总结：满足以下条件，才应该创建索引：
a. 肯定在where条经常使用。
b. 该字段内容不是唯一的几个值（sex）
c. 字段内容不是频繁变化（update）
2.5使用索引的注意事项
- 对于创建多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。右边的就不会使用索引。
Explain可以帮助我们在不真正执行某个sql语句时，就执行mysql怎样执行，这样利用我们去分析sql指令。
select_type
PRIMARY:子查询中最外层查询
SUBQUERY:子查询内层第一个SELECT，结果不依赖于外部查询
DEPENDENT SUBQUERY:子查询内层第一个SELECT，依赖于外部查询。
UNION:UNION语句中第二个SELECT开始后面所有的SELECT。
SIMPLE
UNION RESULT UNION中合并结果
-
对于使用like的查询，查询如果是‘%aaa%’，不会使用到索引。‘aa%’会使用到索引。比如：explain select * from dept where dname like ‘%aaa’\G 不能使用索引，即，在like查询时，关键字最前面，不能使用%或者_这样的字符，如果一定要前面有变化的值，则考虑使用全文索引->sphinx。
-
下列的表将不使用索引：
a. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段都必须建立索引。建议不要使用or
b. 对于多列索引，不是使用的第一部分，则不会使用索引。
c. like查询是以%开头
d. 如果列类型是字符串，那一定要在条件中将数据使用引号引起来。否则不使用索引。（添加时，字符串必须”），也就是，如果列是字符串类型，就一定要用“”把查询内容包括起来。
e. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
f. 不同字符集的字段关联查询不能使用索引，会直接报错。 -
如何查看索引使用情况：
Show status like ‘handler_read”;这个值越高说明索引使用率越高。而handler_read_rnc_next:这个值越高，说明查询低效。
2.6 SQL语句的小技巧
a. 在使用group by 分组查询时，默认分组后，还会排序，可能会降低速度。在group by后面增加order by null就可以防止排序。
b. 有些情况下，可以使用连接来替换子查询，因为使用join，Mysql不需要在内存中创建临时表。
Select * from dept,emp where dept.deptno=emp.deptno;【简单处理方式】
Select * from dept left join emp on dept.deptno= emp.deptno:【左外连接，更好】
2.7如何选择mysql的存储引擎
Myisam存储：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎。比如：bbs中的发帖表，回复表。
INNODB存储：对事务要求高，保存的数据都是重要数据，我们建议使用INNODB，比如订单表，账号表
问MyISAM和INNODB的区别
-
事务安全
-
查询和添加速度
-
支持全文检索
-
锁机制
-
外键 MyISAM不支持外键，INNODB支持外键。（在php开发中，通常不设置外键，通常是在程序中保证数据的一致，而在java中，建议是设置外键的）
-
如果你的数据库的存储引擎是myisam，请一定记住要定时进行碎片整理。
通过optimize table test100（表名）。或者mysql_query(“optimize tables $表名”);
2.8 PHP定时完成数据库的备份
a． 手动备份数据库（表的）方法 mysqldump –u root –proot 数据库名字[表名1 表名2 ]>文件路径
b． 如何使用备份文件恢复数据：在mysql控制台：source d:\temp.dept.bak
c． 使用定时器自动完成
把备份数据库的指令，写入到bat文件，然后通过任务调度器crontab.定时调用bat文件。
如果mysqdump文件路径中有空格，则一定要使用“”包括。把mytask.bat做成一个任务，并定时调用，在2:00调用一次。
控制面板-任务计划中添加任务计划。
3.分表技术
1.水平分割
a. 表的分割技术
分表or 使用内存缓存（但数据量超大不行）
分表规则:
qqlogin0,qqlogin1,qqlogin2：根据用户id%3=值，根据值来取出这个用户在哪个表，$tbname=’qqlogin’.%id%3;然后查询tbname这张表。
b．我们在提供检索时，应该业务的需求，找到分表的标准，并在检索页面，约束用户检索的方式->分页，如果有大表检索的需求也是少数。
2.垂直分割
把某个表的某些字段，这些字段，在查询时，并不是已经关系，但是数据量大，建议把这些字段单独放到另外一个表中，提高效率。
a. 表的字段定义是保小不保大。Tinyint(1),tinyint(2):0前痛 01
注意：主键最好是不含业务逻辑。
关于网站的图片和视频存放，我们的数据库中，一般只是存放图片或者视频的路径。资源是放在文件系统（往往配合独立的服务器）
b. 如何优化mysql配置文件。
端口号
最大连接数
缓存
my.inif
max_connections = 1000（这里最大2000）
query_cache_size
最重要的参数是内存，我们主要用的是innodb引擎，所以，下面两个参数调的很大
innodb_additional_mem_pool_size =64M
innodb_buffer_pool_size = 1G
对于myisam，需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定调整哪些参数。
4.读写分离
读写分离amoeba（新浪）mysql-proxy，或者直接使用shardingjdbc或者数据库的读写分离。
5. mysql的增量备份
问题提出：
增量备份的定义：mysql数据库会以二进制的形式，自动把用户对mysql数据库的操作记录到文件中。当用户希望恢复到时候可以使用备份文件进行恢复。
增量备份会记录（dml语句，创建表的语句，不会记录select）
记录的（a. 操作语句本身 b. 操作的时间 c.position）一个机器上可以安装两个mysql，但是同版本的不能安装。
步骤：
（1）这里指定把备份文件放到哪个目录。
log-bin=d:/binlog/log/mysqllog
（2）启动mysql得到文件
d:/binlog/mylog/mylog.index 索引文件，有哪些增量备份文件。
d:/binlog/mylog/mylog.000001存放数据库操作的文件。
（3）可以使用mysqlbinlog程序来查看备份文件的内容。当我们进行除select操作。
进入到cmd控制台
cmd>mysqlbinlogg 备份文件路径。
（4） 恢复（一定要看日志文件）方式有两种：a. 时间点;b.位置。
a.时间点:mysqlbinlog –stop-position=”3862” d:\binlog\mylog.000001 | mysql –uroot –proot
时间段: mysqlbinlog –start-datetime=”2013-01-12 18:20:21”-stop-datetime=”3862” d:\binlog\mylog.000001 | mysql –uroot –proot
b. 位置:mysqlbinlog –start-datatime=”2013-01-12 18:20:21”
d:\binlog\mylog.000001 | mysql –uroot –proot
c. 设置日志过期时间：my.cnf下，[mysqld]下添加expire_logs_day=3
（5）如何在工作中将全备份和增量备份配合使用
方案：每周一做一个全备份。Mysqldump，启用增量备份，把过期时间设为大于等于7。
如果出现数据库奔溃，就可以通过时间或者位置恢复。（需要去看日志文件）