mysql优化笔记——为大型网站加速

对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方面的工作：

① 数据库设计

表设计要符合3NF 3范式（规范的模式），有时候我们也需要反3范式

1NF：具有原子性，不可再分隔

2NF：在符合1NF的基础上，要符合2NF，只要表的记录满足唯一性，也就是说同一张表不可能出现完全相等的数据，设置主键（联合主键）

3NF：要求字段没有冗余，字段信息可以通过外键关联的关系派生出来

反3范式的例子：相册和图片的点击次数统计 原则：将表记录多的字段冗余到表记录少的表中

（1-多 将反3范式放在1的一方）

② sql语句优化

• sql语句有几类？

DDL（数据定义语言）【create alter drop】

DML（数据操作语言）【insert delete update 】

select

DTL（数据事务语句）【commit rollback savepoint】

DCL（数据控制语句）【grant revoke】

SQL优化的一般步骤

通过show status命令了解各种SQL的执行频率。

u  show status 命令

该命令可以显示你的mysql数据库的当前状态 . 我们主要关心的是 “ com ” 开头的指令

show status like ‘ Com% ’   <=> show session  status like ‘ Com% ’   //显示当前控制台的情况

show global  status like ‘ Com% ’  ; //显示数据库从启动到 查询的次数

u show variables

这里我们优化的重点是在 慢查询. (在默认情况下是 10 ) mysql5.5.19

显示查看慢查询的情况

show variables like ‘ long_query_time ’

需求：如何在一个项目中，找到慢查询的select , mysql数据库支持把慢查询语句，记录到日志中，程序员分析 . ( 但是注意，默认情况下不启动 .)

步骤:

1.  要这样启动mysql

 

进入到 mysql安装目录

2.  启动 xx>bin\mysqld.exe – slow-query-log   这点注意

 

测试 ,比如我们把

select * from emp where empno=34678 ；

用了1.5秒，我现在优化 .

 

快速体验: 在 emp 表的 empno 建立索引 .

alter table emp add primary key(empno);

//删除主键索引

alter table emp drop primary key

 

然后，再查速度变快.

l 索引的原理

 

显示连接数据库次数

show status like  'Connections';

定位执行效率较低的SQL语句-（重点select）

通过explain分析低效率的SQL语句的执行情况

确定问题并采取相应的优化措施

 

快速体验: 在 emp 表的 empno 建立索引 .

alter table emp add primary key(empno);

//删除主键索引

alter table emp drop primary key

 

然后，再查速度变快.

   l  索引的原理

 

介绍一款非常重要工具 explain, 这个分析工具可以对 sql 语句进行分析 , 可以预测你的 sql 执行的效率 .

他的基本用法是:

explain sql语句 \G

//根据返回的信息，我们可知 , 该 sql 语句是否使用索引，从多少记录中取出 , 可以看到排序的方式 .

l  在什么列上添加索引比较合适

①  在经常查询的列上加索引.

②  列的数据，内容就只有少数几个值,不太适合加索引 .

③  内容频繁变化，不合适加索引

l  索引的种类

①  主键索引 (把某列设为主键，则就是主键索引 )

②  唯一索引(unique) （即该列具有唯一性，同时又是索引）

③  index （普通索引）

④  全文索引(FULLTEXT) 

（只有MyISAM存储引擎支持）sphinx + 中文分词 coreseek

①  复合索引(多列和在一起 )

create index myind on 表名 ( 列 1, 列 2);

l  如何创建索引

 

如果创建unique / 普通 /fulltext 索引

1. create [unique|FULLTEXT] index 索引名 on 表名 ( 列名 ...)

2. alter table 表名 add index 索引名 ( 列名 ...)

//如果要添加主键索引

alter table 表名 add primary key ( 列 ...)

l 删除索引

1.  drop index 索引名 on 表名

2.  alter table 表名 drop index index_name;

3.  alter table 表名 drop primary key

下列几种情况下有可能使用到索引：

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

2，对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引 aaa%’ 会使用到索引。

下列的表将不使用索引：

1，如果条件中有or，即使其中有条件带索引也不会使用，or之间的每个条件列都必须用到到索引才能使用索引。

2，对于多列索引，不是使用的第一部分，则不会使用索引。

3，like查询是以%开头

4，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。

5，如果mysql估计使用全表扫描要比使用索引快，则不使用索引

l  如何检测你的索引是否有效

 

结论:

Handler_read_key 越大越好

Handler_read_rnd_next 越小越好

l  MyISAM 和 Innodb 区别是什么

1. MyISAM 不支持外键 , Innodb 支持

2. MyISAM 不支持事务 , 不支持外键 .

3. 对数据信息的存储处理方式不同.（如果存储引擎是 MyISAM 的，则创建一张表，对于三个文件 .., 如果是 Innodb 则只有一张文件 *.frm, 数据存放到 ibdata1 ）

对于 MyISAM 数据库，需要定时清理

optimize table 表名

l  常见的sql优化手法

1.  使用order by null  禁用排序

比如 select * from dept group by ename order by null

2.有些情况下，可以使用连接来替代子查询。因为使用join，

MySQL不需要在内存中创建临时表。

3.如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，

如果没有索引，则应该考虑增加索引(与环境相关 讲解)

select * from 表名 where 条件1=‘’or 条件2=‘tt’

1.  在精度要求高的应用中，建议使用定点数(decimal)来存储数值，以保证结果的准确性

 

1000000.32 万

create table sal(t1 float(10,2));

create table sal2(t1 decimal(10,2));

 

问?在 php 中 ,int 如果是一个有符号数，最大值 . int- 4*8=32   2 31 -1

l  表的水平划分

l  垂直分割表

l  读写分离

 

  

如果你的数据库的存储引擎是MyISAM的，则当创建一个表，后三个文件 . *.frm 记录表结构 . *.myd 数据   *.myi 这个是索引 .

 

mysql5.5.19的版本，他的数据库文件，默认放在 （看 my.ini 文件中的配置 . ）

③ 数据库参数配置 (缓存设大 )

最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

　　innodb_additional_mem_pool_size = 64M

　　innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size

当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数

④ 恰当的硬件资源和操作系统 (读写分离 .)

如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql

读写分离

如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据 库的压力分散。

这个顺序也表现了这四个工作对性能影响的大小

软件的32位和64位的差别？

64位的地址比32的地址要大，这样分到的内存空间越大发挥的威力越大