mysql 数据库优化技巧

mysql 数据库优化 
包括 
a.表的设计合理化（符合3NF） 
b.添加适当索引（index[4种：普通索引 主键索引 唯一索引unique  全文索引]） 
c.分表技术（水平分割，垂直分割） 
d.读写[写：update/delete/add]分离 
e.存储过程[模块化编程 可以提高速度] 


数据库的三层结构 orale MySQL db2 sql server 
php程序通过dbms(数据库管理系统)操作数据库文件，数据库执行相关操作返回给dbms，然后再返回给PHP 
dbms 首先编译PHP代码，然后执行操作，然后缓存结果，但是编译很费时间 
直接编译耗时严重，所以可以直接编程写存储过程(分页存储/触发器) 
PHP中执行 call proc('参数')调用存储过程 利于模块化编程 
f.对MySQL配置优化[配置最大并发数 my.ini] 
# 最大并发数 
# 一般网站调整到 1000 左右 
max_connections = 100  
# 调整缓存大小 


g.MySQL 服务器硬件升级 
h.定时的去清除不需要的数据，并且定时进行碎片整理(尤其对搜索引擎是MyISAM) 


数据库设计的三范式 3NF 
表的范式，是首先符合 1NF 
才能满足2NF 
进一步满足3NF 
1NF： 
    是对属性的原子性约束，要求属性（列）具有原子性，不可再分解 
    只要是关系型数据库都满足1NF 
     
    数据库的分类： 
        关系型数据库   ：MySQL/Oracle/db2/informix/sysbase/sql server 
        非关系型数据库 ：特点是面向对象或者集合的 
        NoSql数据库    ：MongoDB(特点是面向文档) 
2NF： 
    是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性 
3NF： 
    是对字段冗余性的约束，要求字段没有冗余，即 表中不要有冗余数据 
    也就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放 


反 3NF 
实际上一定的冗余是允许的 就是反 3NF 
在表的 1对N 情况下，为了提高效率，可能会在 1 这表中设计字段 提速 
譬如 相册的浏览量  
相册 字段 id name views 
相片 字段 id name path views 
这里的views字段，在两个表中都有，可以避免显示相册浏览量的时候再去查询计算相册内相片的浏览量总和 
以此冗余提高查询效率 


SQL优化的一般步骤 
① 通过 show status 命令了解各种SQL的执行频率 
② 定位执行效率较低的SQL语句 （重点select） 
③ 通过 explain 分析低效率的SQL语句的执行情况 
④ 确定问题并采取相应的优化措施 


SQL语句本身的优化 
问题：如果从一个大型项目中，迅速的定位执行速度慢的语句 
① 首先了解MySQL数据库的一些运行状态如何查询 show status 
    比如想知道当前MySQL运行时间，一共执行了多少次select/update/delete 当前连接 等等 
    常用的 
    show status like 'uptime' 当前MySQL运行时间 
    show status like 'com_select' 当前MySQL执行了多少次查询 
    show status like 'com_insert' 当前MySQL执行了多少次添加 
    show status like 'com_update' 当前MySQL执行了多少次更新 
    show status like 'com_delete' 当前MySQL执行了多少次删除 
    show status 语法： 
    show [session|global] status like ''; 
    如果不写 [session|global] 表示默认是 session 指取出当前窗口的执行情况 
    如果想看所有(mysql启动到现在)的情况 加上 global 
    show global status like 'com_insert'; 
    show status like 'connections';试图连接MySQL的连接数 


    显示慢查询次数 
    show status like 'slow_queries'; 


② 如何去定位慢查询 
    构建一个大表(400万数据)->存储过程构建 


    默认情况下，MySQL认为 10秒钟 才是一个慢查询 


    修改MySQL的慢查询时间 
    // 显示慢查询时间 
    show variables like 'long_query_time'; 
    // 修改慢查询时间 
    set long_query_time=2;// 即修改慢查询时间为2秒 


    构建大表->大表中记录有要求，记录是不同才有用，否则测试效果与真实的相差很大 


    为了存储过程能正常执行，需要修改命令执行结束符修改一下 
    语法 delimiter $$ $$表示修改后的结束符 


    当你想使用函数时，可以指向一个dual表，这个表是亚元表，就是个空表 
    select rand_string(6) from dual; 


③ 这时我们如果出现一条语句执行时间超过1秒，就会被统计到 
    call insert_emp 执行存储过程这个也会被记录 


④ 如果把慢查询的 SQL 语句记录到我们的一个日志中 
    在默认情况下，我们的MySQL不会记录慢查询，需要启动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 文件中记录的位置 即 datadir设置的目录 


⑤ 测试，可以看到在日志中就记录下我们的MySQL慢sql语句 


优化问题 
通过 explain 语句可以分析，MySQL如何执行你的SQL语句 


添加索引 
四种索引 主键索引 唯一索引 全文索引 普通索引 


1.添加 
1.1 主键索引的添加 
    当一张表中，把某个列设为主键的时候，则该列就是主键索引 
    如果你创建表时，没有指定主键索引，也可以在创建表后添加索引 
    语句：alter table 表名 add primary key (列名); 


1.2 普通索引 
    一般来说，普通索引的创建，是先创建表，然后再创建索引 
    语句：create index 索引名 on 表名 (列名); 


1.3 全文索引 
    全文索引：主要是针对文件，文本的索引，比如文章 
    全文索引针对MyISAM有用 
    如何使用全文索引 
    错误用法： 
    select * from articles where body like '%mysql%'; 
    这种方法是不会用到全文索引的 
    正确用法： 
    // title,body是全文索引，匹配database的句子 
    select * from articles where match(title,body) against('database'); 
    说明： 
    1.在MySQL中fulltext索引只针对myISAM生效 
    2.针对英文生效，对中文需要sphinx(coreseek)技术处理 
    3.使用方法是match(字段名) against('关键字') 
    4.全文索引有一个停止词概念： 
        因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符， 
        就不会创建，这些词，称为 停止词。 


1.4 唯一索引 
    当表的某列被指定为unique约束，这列就是一个唯一索引 
    唯一索引的列可以为null，并且可以有多个 
    在创建表后，再去创建唯一索引 
    创建语法：create unique index 索引名 on 表名 (列名); 
2.查询 
    ① desc 表名 该方法缺点：不能够显示索引的名字 
    ② show index(es) from 表名 
    ③ show keys from 表名 
3.删除 
    语法：alter table 表名 drop index 索引名; 
    主键索引删除：alter table 表名 drop primary key; 
4.修改 
    先删除，再重新创建 


索引注意事项： 
    索引占用磁盘空间 
    对dml(insert/update/delete)语句效率有影响 


在哪些列上适合添加索引？ 
    较频繁的作为查询条件字段创建索引 
    例如 select * from emp where empno=1; 
    唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件 
    例如 select * from emp where sex='男'; 
    更新非常频繁的字段不适合创建索引 
    例如 select * from emp where logincount=1; 
    不会出现在where子句中字段不该创建索引 


总结：满足以下条件的字段，才能创建索引 
a.肯定在where条件中经常使用的 
b.该字段的内容不是唯一的几个值 
c.字段内容变化不能太频繁 


使用索引的注意事项 
alter table dept add index myind (dname,loc); // dname就是左边的列，loc是右边的列 
下列情况有可能使用到索引 
a.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用 
explain select * from dept where dname='aaa'; 
b.对于使用like的查询，查询条件如果是'%aaa'则不会使用到索引，'aaa%'会使用到索引 
下列情况不会使用索引 
a.如果条件中有or，即使其中有条件带索引也不会使用 
换言之，就是要求使用的所有字段都创建索引，建议：尽量避免使用or关键字 
b.对于多列索引，不是使用的第一部分，则不会使用索引 
explain select * from dept where loc='aaa';// 多列索引时，loc为右边列，索引不会使用到 
c.like查询是以%开头 
如果一定要使用，则使用全文索引去查询 
d.如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引 
e.如果MySQL估计使用全表扫描要比使用索引块，则不使用索引 


explain select * from dept where loc='aaa'; 
explain 语句详解： 
告诉我们MySQL将使用怎样的执行计划来优化query 


id:1                    查询序列号 
select_type:SIMPLE      查询类型 
table:dept              查询的表名 
type:ALL                扫描的方式 all表示全表扫描 
possible_keys:null      可能使用到的索引 
key:null                实际使用的索引 
key_len:null 
ref:null 
rows:10                 该SQL语句扫描了多少行，可能得到结果数 
Extra:Using where       SQL语句的额外信息，比如排序方式filesort等等 


select_type 类型 
primary  : 子查询中最外层查询 
subquery : 子查询内层第一个select，结果不依赖于外部查询 
dependent subquery : 子查询内层第一个select，依赖于外部查询 
union　: union语句中第二个select开始后面所有select 
simple : 简单模式 
union result : union中合并结果 


type 类型 
all : 完整的表扫描 通常不好 
system : 表仅有一行(=系统表) 这是const联接类型的一个特例 
const : 表最多有一个匹配行 


extra 类型 
no table : query语句中使用 from dual 或不含任何from子句 
Using filesort : 当query中包含 order by 操作，而且无法利用索引完成排序 
impossible WHERE noticed after reading const tables:Mysql query optimizer  
通过收集统计信息不可能存在结果 
Using temporary : 某些操作必须使用临时表，常见 group by ,order by 
Using where : 不用读取表中所有信息，仅通过索引就可以获取所需数据 


explain可以帮助我们在不真正执行某个SQL语句时，就知道MySQL怎样执行，利于我们去分析SQL指令 


查看索引的使用情况 
show status like 'Handler_read%'; 
handler_read_key:这个值越高越好，代表使用索引查询到的次数 
handler_read_rnd_next:这个值越高，说明查询低效 


SQL语句的小技巧 
① 优化 group by 语句 
默认情况下，MySQL对所有的group by col1,col2 进行排序，这与在查询中指定 order by col1,col2 类似 
如果查询中包括 group by 但用户想尽量避免排序结果的消耗，则可以使用 order by null 禁止排序 


② 有些情况下，可以使用连接来替代子查询 
    因为使用 join MySQL不需要在内存中创建临时表 


③ 如果想要在含有 or 的查询语句中利用索引，则 or 之间的每个条件列都必须用到索引， 
    如果没有索引，则应该考虑增加索引。 


如何选择MySQL的存储引擎 
1.myISAM: 
    如果表对事务要求不高，同时是以查询和添加为主的。 
    比如 BBS中的发帖表，回复表 
2.InnoDB: 
    对事务要求高，保存的数据都是重要数据 
    比如 订单表，账户表 
3.Memory: 
    数据变化频繁，不需要入库同时又经常查询和修改 


myISAM 与 InnoDB 主要区别 
1.myisam 批量插入速度快，InnoDB慢，myisam插入数据时不排序 
2.InnoDB支持事务 
3.myisam支持全文索引 
4.锁机制，myisam是表锁，InnoDB是行锁 
5.myisam不支持外键，InnoDB支持外键 


外键 
classes表 
create table classes( 
        id int unsigned not null auto_increment primary key, 
        name varchar(64) not null 
    )engine=innoDB; 
insert into classes values (1,'aaa'); 


stu表 
create table student( 
        id int unsigned not null auto_increment primary key, 
        name varchar(64) not null, 
        classid int unsigned not null, 
        foreign key (classid) references classes(id) /* 外键 */ 
    )engine=innoDB; 
当设置了外键的时候，企图添加一个外键没有的数据，会报错，无法插入数据 
insert into student values (1,'hello',1); 这个是正确的  
insert into student values (1,'hello',2); 当classes表中id=2不存在时，这个是错误的  


在PHP开发中，通常不设置外键，通常在程序中保持数据的一致性。 


选择合适的数据类型 


① 在精度要求高的应用中，建议使用定点数来存储数值，以保证数据的准确性。deciaml精度比float高，尽量使用 
② 对于存储引擎是myisam的数据库，如果经常做删除和修改记录的操作， 
    要定时执行optimize table table_name;功能对表进行碎片整理。 
③ 日期类型要根据实际需要选择能够满足应用的最小存储的早期类型 
    create table bbs (id int unsigned not null ,con varchar(1024) , pub_time int ); 


php备份数据库 


① 手动备份数据库(表)的方法 


    cmd控制台 
    mysqldump -uroot -proot 数据库[表名1 表名2 ...] > 文件路径 
    例如 把 temp 数据库备份到 d:/temp.bak  
    mysqldump -uroot -proot temp > d:/temp.bak 
    如果希望备份数据库中某几张表 
    mysqldump -uroot -proot temp dept > d:/temp.dept.bak 


    如何使用备份文件恢复数据 
    在MySQL控制台 
    source d:/temp.dept.bak 


② 使用定时器自动完成 


    a.把备份数据库的指令，写入到bat文件，然后通过任务 crontab 
    mytask.bat 内容是 
    d:/myweb/mysql/bin/mysqldump -uroot [email protected] bigtest dept > d:/bigtest.dept.bak 
    注意事项：如果mysqldump.exe 文件路径有空格，则一定要使用双引号包起来 


    把 mytask.bat 做成一个任务，并定时调用 例如 每天 2：00 调用一次 


    windows下：打开控制面板--任务计划--添加任务计划--下一步--浏览--找到mytask.bat--选择执行任务时间 
                --下一步--起始时间--下一步--输入密码--下一步--完成 


    现在的问题是，每次都是覆盖原来的备份文件，不利于分时段备份，解决这种问题 
    可以采用如下方式解决： 
    b.建立一个 mytask.php 文件 
    内容是 
    <?php 
        date_default_timezone_set('PRC'); 
        $bakfilename = date('YmdHis',time()); 


        $command = "d:\myweb\mysql\bin\mysqldump -uroot [email protected] bigtest dept > f:\\{$bakfilename}.bak"; 


        exec($command); 
    ?> 
    建立一个bat文件 mytask2.bat,内容是 
    d:\myweb\php\php.exe d:\myweb\apache\htdocs\mytask.php 
    该方法是利用PHP自身的php.exe执行PHP文件 
    然后将mytask2.bat做成一个任务，定时的去执行 
    linux 下 使用 crontab命令 
    crontab 0 0 0 0 0 mytask.sh 


mysql中当前时间戳函数 unix_timestamp(); 


案例 定时发送邮件 
1.怎样可以定时的去检索哪些邮件该发送： 
    只能每隔一定时间就看看哪些邮件该发送 mailtask.php 


    在PHP中，有一个函数mail，用于发送邮件，实际中通过phpmailer进行发送邮件 


    要正确使用phpmailer发送邮件，需要满足如下条件 
    a.本身机器是可以联网的 
    b.需要搭建自己的 SMTP 邮件服务器 




表的分割 


1.水平分割 
案例 大数据量的用户表 
三张表：qqlogin0,qqlogin1,qqlogin2 
将用户id%3,按结果放入不同的表当中 
create table qqlogin0( 
        id int unsigned not null primary key,/* 这个id不能设置自增长 */ 
        name varchar(32) not null default '', 
        pwd varchar(32) not null default '' 
    )engine=myisam default charset=utf8; 


create table qqlogin1( 
        id int unsigned not null primary key,/* 这个id不能设置自增长 */ 
        name varchar(32) not null default '', 
        pwd varchar(32) not null default '' 
    )engine=myisam default charset=utf8; 


create table qqlogin2( 
        id int unsigned not null primary key,/* 这个id不能设置自增长 */ 
        name varchar(32) not null default '', 
        pwd varchar(32) not null default '' 
    )engine=myisam default charset=utf8; 


开发 addUser.php ，因为在添加用户时，各个用户id应该确认下，通常我们使用一个辅助表 uuid 表， 
它可以帮助我们生成一个编号 


uuid表： 
create table uuid ( 
        id int unsigned not null auto_increment primary key 
    )engine=myisam default charset=utf8; 


分享一句话： 
我们在提供检索时，应该根据业务的需求，找到分表的标准，并在检索页面约束用户的检索方式，而且要配合分页 
如果有大表检索的需求，也是少数的。 


添加用户时：addUser.php 
<?php 
$conn = mysql_connect('localhost','root','[email protected]'); 
if (!$conn)  
{ 
    die('mysql connect error'); 
} 
mysql_select_db('temp',$conn); 


$sql = "insert into uuid values (null)"; 
$res = mysql_query($sql,$conn); 


if ($res)  
{ 
    $uuid = mysql_insert_id(); 
    $tablename = 'qqlogin'.$uuid%4; 
    $sql = "insert into $tablename values ($uuid,'abc','abc')"; 
    $res = mysql_query($sql,$conn); 
    if ($res)  
    { 
        echo 'insert success'; 
    }else  
    { 
        echo 'insert user error'; 
    } 
}else  
{ 
    die('insert error'); 
} 
?> 


查询用户时，checkUser.php 
<?php 
$conn = mysql_connect('localhost','root','[email protected]'); 
if (!$conn)  
{ 
    die('mysql connect error'); 
} 
mysql_select_db('temp',$conn); 


$qqid = intval($_GET['id']); 
$tablename = 'qqlogin'.$qqid%4; 
$sql = "select * from $tablename where id='$qqid'"; 
$res = mysql_query($sql,$conn); 


if ($res)  
{ 
    $row = mysql_fetch_assoc($res); 
    print_r($row);  
}else  
{ 
    die('no user'); 
} 
?> 


2.垂直分割 
案例 学生答题系统 


考试结果表 
id  stuno   questionid       answer(text)      grade 
1   1       20               [结果。。。]      30 


问题表 
id     question 
20     请写一篇散文 


需求: 
查处1号学生20题得分情况，但answer字段内容非常大，对查询速度有影响 
解决：把answer(对查询速度影响较大的字段)单独的提出来，放到另外一张表 


回答表 answer 
id answer 
1  结果。。。 


相应的修改考试结果表 
id  stuno  questionid grade 
1   1      20         30 


总结：把某个表的某些字段，这些字段，在查询时，并不实时关心，但数据量很大， 
我们建议大家可以 把这些字段单独的放到另外一张表，从而提高效率。但是不要忘记关联关系 




表的字段定义原则是保小不保大，尽量节省空间 


查看索引 
mysql> show index from tblname; 
mysql> show keys from tblname; 
· Table 
表的名称。 
· Non_unique 
如果索引不能包括重复词，则为0。如果可以，则为1。 
· Key_name 
索引的名称。 
· Seq_in_index 
索引中的列序列号，从1开始。 
· Column_name 
列名称。 
· Collation 
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。 
· Cardinality 
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。 
· Sub_part 
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 
· Packed 
指示关键字如何被压缩。如果没有被压缩，则为NULL。 
· Null 
如果列含有NULL，则含有YES。如果没有，则该列含有NO。 
· Index_type 
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。 
· Comment 


关于网站的图片和视频的存放： 
我们的数据表中，一般只是存放图片或者视频的路径，真正的资源是放在文件系统上的，往往会配合独立的服务器 


优化MySQL的配置 
my.ini 
port=3306 默认端口是3306， 
如果想修改端口 port=3309,在mysql_connect('localhost:3309','root','root');要注意 


修改最大连接数 
max_connections=100 最大连接数可以修改到2000，再大没有用 


query_cache_size=15M 这个是查询缓存的大小 
innodb参数也可以调大以下两个参数 
innodb_additional_mem_pool_size=64M 
innodb_buffer_pool_size=1G 
myisam需要调整 key_buffer_size 
调整参数还要看状态，用 show status 可以看到当前状态，以决定该调整哪些参数 


如果你的机器内存超过4G，则应当采用64位操作系统和64位MySQL5.5.19 


读写分离 
如果数据库压力很大，一台机器支撑不了，可以用MySQL复制实现多台机器同步，将数据库压力分散 


增量备份 
定义：MySQL数据库会以二进制的形式，把用户对MySQL数据库的操作，记录到文件 
      当用户希望恢复的时候，可以使用备份文件进行恢复。 
增量备份会记录 dml语句，创建表的语句，但不会记录select语句 
记录的是 a.操作语句本身 b.操作的时间 c.操作的位置 position 


案例：如何进行增量备份和恢复 
步骤： 
1.配置my.ini 文件 或者 my.conf 启用二进制备份 
在my.ini 中增加一句话 
#指定备份文件放在哪个目录下 
log-bin="d:/backup/mylog" 


2.重启MySQL得到文件  
    d:/backup/mylog.index 索引文件 有哪里增量备份文件 
    d:/backup/mylog.000001 存放用户对数据库操作的文件 


3.当我们进行操作(除了select) 
    可以使用 mysql/bin/mysqlbinlog 程序来查看备份文件的内容 
    进入到 cmd 控制台 cmd>mysqlbinlog 备份文件路径 
    在这里 end_log_pos 526表示执行某个命令在文件中的位置，可以根据这个位置恢复相应的数据 
    set timestamp=xxxxxxx 这个表示命令执行时间 


    MySQL把每一个操作的时间记录下来，同时分配了一个位置position 
    我们可以根据时间或者位置来恢复 




    a.根据时间点恢复 
        在 mylog.000001 文件开始 到 2013-05-15 14:25:00 结束 
        mysqlbinlog --stop-datetime="2013-05-15 14:25:00" 
        d:/backup/mylog.000001 | mysql -uroot -p 


        在 mylog.000001 文件 2013-05-15 14：25：00 开始到文件结束 
        mysqlbinlog --start-datetime="2013-05-15 14:25:00"  
        d:/backup/mylog.000001 | mysql -uroot -p 


        恢复某个时间段数据 
        mysqlbinlog --start-datetime="2013-05-15 14:24:00" --stop-datetime="2013-05-15 14:25:00"  
        d:\backup\mylog.000001 | mysql -uroot -p 


    b.根据位置恢复 
        在 mylog.000001 文件开始->21114 
        mysqlbinlog --stop-position="21114" d:/backup/mylog.000001 | mysql -uroot -p 
         
        在mylog.000001 文件2111开始->最后 
        mysqlbinlog --start-position="2111" d:/backup/mylog.000001 | mysql -uroot -p 


        在mylog.000001 文件 751->1195 之间 
        mysqlbinlog --start-position="751" --stop-position="1195" | mysql -uroot -p 


4.如何在工作中将全备份和增量备份配合使用 
    方案：每周一做一个全备份：mysqldump， 
          然后启用增量备份，把过期时间设为>=7，最好设大一点 
          如果出现数据库崩溃，可以通过时间或者位置恢复 需要去看增量日志文件