mysql数据库优化技术(1)

对mysql优化是一个综合性的技术，主要包括：

a、表的设计合理化（符合3NF）；

b、添加适当的索引（index）[四种]：普通索引、主键索引、唯一索引（unique）、全文索引；

c、分表技术（水平分割、垂直分割）；

d、读写分离；

e、存储过程（模块化编程，可以提高速度）；

f、对mysql配置优化：配置最大并发数（my.ini文件：max_connections最大并发数，一般网站应设置到1000左右，太大的话内存会受不了）、调整缓存大小；

g、mysql服务器硬件的升级；

h、定时去清除不需要的数据，定时进行碎片整理（尤其是MyISAM存储引擎）。

Ø 数据库三层结构：PHP程序→dbms(数据库管理系统，我们平常说的数据库其实是这个)→数据库（就是文件）。

Ø PHP程序发送sql语句，dbms进行编译后，再执行，对从数据库中返回的数据进行缓存，所以第二次sql请求时速度会变快。但是使用sql语句去操作，编译会很耗时，我们可以事先把一些经常用的代码在数据库中进行编码形成二进制，再直接调用，这个过程就是存储过程。

Ø 符合3NF(范式)的表：表的范式，首先符合1NF，才能满足2NF，进一步满足3NF。

Ø 1NF：即表的列具有原子性，不可再分解，即列的信息，不能再分解。只要数据库是关系型数据库（mysql、Oracle、db2、sql server、informix、sysbase），就自动满足1NF。

Ø 2NF：表的记录是唯一的，满足2NF，通常我们通过设计一个主键来实现。（主键：一般来讲不含业务逻辑，一般是自增的。因为主键不含业务逻辑，数据较稳定）

Ø 3NF：即表中不要有冗余数据，就是说，表的信息如果能推导出来，就不应该单独的设计一个字段来存放。下图不符合3NF：

Ø 反3NF：（相册表的浏览次数是对应photo表的图片浏览次数之和，为了提升响应速度，在每次浏览图片增加图片浏览次数的同时，相册表也同时添加浏览次数。虽然相册的浏览次数可以通过photo表推导出来，但是如果图片太多，双表查询时速度就会慢，通过设计字段views就可以解决，所以必要的数据冗余也是允许的）

Ø SQL语句优化：如何从一个大型项目中快速定位执行速度慢的语句（定位慢查询）？

Ø 常用语句：show status：show status like ‘uptime’ 查看MySQL启用多长时间；show [session|global] status like ‘com_select’  show status like ‘com_update’ (默认参数是session会话，指取出当前窗口的执行，global取出从mysql启动到现在的执行次数) 查看对应语句执行了多少次(存储引擎的选择偏向于参考哪个操作执行的多)；show status like ‘connections’ 查看试图连接mysql服务器的次数；show status like ‘slow_queries’(显示慢查询次数)、

Ø 慢查询(默认情况，mysql认为10秒是一个慢查询)优化：定位慢查询（构建一个大表->存储过程；修改mysql的慢查询：显示慢查询值show variables like ‘long_query_time’，修改值set long_query_time=1）

Ø 把慢查询的sql语句记录到我们的一个日志中（默认下mysql不会记录慢查询，需要在mysql启动时指定记录慢查询才行）。如果启用了慢查询记录日志，默认把这个文件放在my.ini文件记录的位置，如：datadir=d:/wamp/bin/mysql/mysql5.6.12/data(这个地址不要轻易去修改)

Ø 数据库中可以有多个数据对象：表、存储过程、视图、函数、触发器

Ø dual亚元表，即一个空表。select rand_string(6) from dual;

优化问题：

Ø 通过explain语句可以分析，mysql如何执行sql语句

Ø 建立适当索引：

Ø 1、添加索引：

Ø 主键索引的添加：当一张表，把某个列设为主键的时候，该列就是主键索引。创建表后再添加索引：alter table 表名 add primary key (列名)。建立索引是有开销的。不能为空，也不能重复。

Ø 为什么创建主键索引后速度会变快：没建立索引之前，dbms是按照给定的条件（如id=2)一个一个的顺序去查找。而建立索引后，可以利用二叉树算法（或哈希算法），建立索引文件。二叉树(BTREE)的效率log2N

Ø 普通索引的添加：普通索引的创建时，先建表，再创建普通索引。create table aaa....，create index 索引名 on 表(列)

Ø 全文索引的添加：全文索引主要针对文本的检索，全文索引只对MyISAM有效，目前只针对英文有效（sphinx（coreseek）技术处理中文），对停用词不建索引。

  CREATE TABLE articles (

   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

   title VARCHAR(200),

   body TEXT,

   FULLTEXT (title,body)

   )engine=myisam charset utf8;

Ø 使用全文索引：错误用法 select * from articles where body like ‘%mysql%’;(不会使用全文索引)；正确用法：select * from articles where match(title,body) against (‘mysql’)

Ø 唯一索引的添加：当表的某列被指定为unique约束时，这列就是一个唯一索引（也采用了二叉树的算法）。唯一索引是不能重复的，但是可以为空（NULL可以有多个，’’空字符串只能有一个）；创建表后再添加唯一索引：create unique index 索引名 on 表名(列名)

Ø 复合索引：索引作用在多列上。alter table 表名 add index 索引名 (列名1，列名2...)

Ø explain：了解sql语句的执行情况

Ø 2、查询索引：desc 表名（该方法的缺点是不能够显示索引的名字）、show index(es) from 表名(\G)、show keys from 表名(\G)

Ø D:\wamp\bin\mysql\mysql5.6.12\data\可以看到这个目录中，一个数据库有一个文件夹，使用InnoDB建立的表由三个文件构成：

使用MyASIN引擎建立的表一般只有.frm文件，而起数据反正上以及目录的。

.frm表示表的结构、.MYD表示表的数据、MYI表示表的索引.建立索引后.MYI文件会 变大。

Ø 3、删除索引：alter table 表名 drop index 索引名；如果删除的是主键索引，还可以：alter table 表名 drop primary key;

Ø 4、修改索引：一般是先删除，再重新创建。

Ø 使用索引的注意事项：磁盘占用；对dml(update delete insert)语句的效率影响，变慢：因为在增删改的时候，索引文件会更新（如删除一个记录，对应二叉树也应该删除对应记录），即使这样使用索引利大于弊，大部分网站查询多于增删改。

Ø 哪些列上适合添加索引：肯定在where中经常使用；该字段的内容不是唯一的几个值（sex）；字段内容不是频繁变化的。

Ø 使用索引时的注意事项：

Ø 1、对于创建的多列索引，只要查询条件使用了最左边的列，索引一般会被使用。

alter table dept add index myind (dnam,loc)//dnam是左边的列，loc是右边的列。

Ø 2、对于使用like的查询，查询如果是’%aaa’(%aaa%、_aaa)不会使用到索引，’aaa%’会使用到索引(即，在like查询时，关键字的首个字符是确定的，不能使用%或_，如果前面有变化，则考虑全文索引)

Ø 3、如果条件中有or，所有使用到的字段都要建立索引（复合索引右边的列也要），建议尽量避免使用or

Ø 4、如果列类型是字符串，那一定要在条件中将值使用单引号引用起来，否则不使用索引。（值如果是字符串，不使用单引号，直接报错，如果是数字，不使用单引号不会报错，因为会自动转为字符串，但是无法使用索引）

Ø 5、mysql会估计全表扫描比使用索引还快，则不使用索引。

Ø 查看索引使用的情况：show status like ‘handler_read%’

Ø 把一张表的数据导入到另一张表中，建议先禁用索引，要不然在导入数据的同时也会建立索引（不是重点）

Ø group by分组查询时，默认分组后，还会自动排序（filesort），可能会降低速度。在group by后增加order by null 防止排序。

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

简单连接查询：select * from dept,emp where dept.deptno=emp.deptno;

左外连接：select * from dept left join emp on dept.deptno=emp.deptno;

Ø 如何选择MySQL的存储引擎：

Ø myisam：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam。比如bbs中的发帖表、回复表。

Ø InnoDB：对事务要求高，保存的数据都是重要数据，建议使用InnoDB。比如订单表、账户表。

Ø Memory：比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，考虑使用。

myiasm数据查添加比InnoDB快，因为myisam直接在表尾插入，而InnoDB要先对数据进行事务安全的校验，并进行一个适当的排序。

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

Ø 如果数据库存储引擎是myisam，一定要定时进行碎片整理（要不然删除的数据永远不会删除）：optimize table tablename