一、MySQL优化概述

  1. 页面静态化，memcache是通过减少对mysql操作来提升访问速度。
  3. 但是一个网站总是要操作数据库，如何提升对mysql的操作速度。

方针：

  1. 存储层：数据表”存储引擎”选取、字段类型选取、逆范式(三范式)

  2. 设计层：索引、分区/分表、存储过程、sql语句的优化

  3. 架构层：分布式部署(集群)(读写分离)，需要增加硬件

  4. sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行

二、存储引擎的选择

1、存储引擎介绍

  1. 熟悉的存储引擎：Myisam、InnoDB、memory

1、什么是存储引擎

数据表存储数据的一种格式。数据存储在不同的格式里边，改格式体现的特性也是不一样的。

例如：

  1. InnoDB存储引擎的特性有支持事务、支持行级锁。mysiam支持的特性有压缩机制等。

mysql中的数据是通过各种不同的技术(格式)存储在文件(或者内存)中的。技术和本身的特性就称为“存储引擎”。

2、存储引擎的理解

现实生活中，楼房、平房就是具体存储人的存储引擎。楼房、平房有自己独特的技术特性。

例如楼房有楼梯、电梯、平房可以自己打井喝水等。

3、存储引擎所处的位置

存储引擎，处于MySql服务器的最底层，直接存储数据，导致上层的操作，依赖于存储引擎的选择。

客户端-》网络连接层-》业务逻辑层（编译，优化，执行SQL）-》存储引擎层

存储引擎 - 图1

  1. 查看当前mysql支持的存储引擎列表：show engines

存储引擎 - 图2

4、常用存储引擎

  1. Myisam：表锁，全文索引
  2. Innodb：行(记录)锁，事务（回滚），外键
  3. Memory：内存存储引擎，速度快、数据容易丢失

2、innodb存储引擎

  1. >=5.5 版本中默认的存储引擎，MySql推荐使用的存储引擎。提供事务，行级锁定，存储引擎。
  3. 事务安全型存储引擎，更加注重数据的完整性和安全性。

1、存储格式

innodb存储引擎 每个数据表有单独的“结构文件” ——*.frm

数据，索引集中存储，存储于同一个表空间文件中——ibdata1。

ibdata1就是InnoDB表的共享存储空间，默认innodb所有表的数据都在一个ibdata1里。

例：

  1. -- 创建innodb表
  2. create table t1(id int,name varchar(32)) engine innodb charset utf8;

.frm表结构文件。

存储引擎 - 图3

innodb表空间文件：存储innodb的数据和索引。

存储引擎 - 图4

默认，所有的 innodb表的数据和索引在同一个表空间文件中，

通过配置可以达到每个innodb的表对应一个表空间文件。

show variables like ‘innodb_file_per_table%’

存储引擎 - 图5

例：

  1. -- 开启该配置
  2. set  global  innodb_file_per_table=1;

存储引擎 - 图6

  1. 创建一个innodbd的表进行测试使用。
  1. create table t2(id int,name varchar(32)) engine innodb charset utf8;

查看表对应的文件自己独立的“数据/索引”文件

存储引擎 - 图7

系统配置参数innodb_file_per_table后期无论发生任何变化，t2都有自己独立的“数据/索引”文件。

注意

  1. 相比较之下，使用独占表空间的效率以及性能会更高一点。
  3. innodb数据表不能直接进行文件的复制/粘贴进行备份还原，可以使用如下指令
  1. mysqldump  -uroot -p密码  数据库名字 > f:/文件名称.sql  [备份]
  3. mysql -uroot  -p密码 数据库   <  f:/文件名称.sql  [还原]

2、数据是按照主键顺序存储

在innodb数据表，数据的写入顺序 与 存储的顺序不一致，需要按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。

  1. create table t3(
  2. id int primary key auto_increment,
  3. name varchar(32) not null
  4. )engine innodb charset utf8;
  5. -- 给innodb数据表写入4条记录信息(主键id值顺序不同)
  6. insert into t3 values(223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');

插入时做排序工作，效率低。

3、并发处理

擅长处理并发的。

行级锁定(row-level locking)，实现了行级锁定，在一定情况下，可以选择行级锁来提升并发性，也支持表级锁定，innodb根据操作选择。

锁机制

  1. 当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能相互影响），通过加锁来处理

操作方面：

  1. **_读锁：_**读操作时增加的锁，也叫共享锁，S-lock。特征是所有人都只可以读，只有释放锁之后才可以写。
  3. **_写锁：_**写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征，只有锁表的客户可以操作（读写）这个表，其他客户读都不能读。

锁定粒度（范围）

  1. **_表级锁：_**开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam和innodb都支持。
  3. **_行级锁：_**开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb支持

存储引擎 - 图8

3、Mysiam存储引擎

<=5.5mysql默认的存储引擎。

（ISAM——索引顺序访问方法）是Indexed Sequential Access Method(索引顺序存取方法)的缩写

它是一种索引机制，用于高效访问文件中的数据行，擅长与处理高速读与写。

1、存储方式

数据，索引，结构分别存储于不同的文件中。

  1. create table t4(id int,name varchar(32)) engine myisam charset utf8;

存储引擎 - 图9

mysiam存储引擎数据表，每个数据表都有三个文件.frm（结构文件） .MYD(数据文件) *.MYI(索引文件)

这三个文件支持物理复制、粘贴操作(直接备份还原)。

2、数据的存储顺序为插入顺序

  1. create table t5(
  2. id int primary key auto_increment,
  3. name varchar(32) not null
  4. )engine myisam  charset utf8;
  5. insert into t5 values(2223,'刘备'),(12,'张飞'),(162,'张聊'),(1892,'网飞');

数据查询的顺序，与写入的顺序一致。

存储引擎 - 图10

数据写入时候，没有按照主键id值给予排序存储，该特点导致数据写入的速度非常快

3、并发性

mysiam的并发性较比innodb要稍逊色（mysiam不支持事务）因为数据表是“表锁”

myisam和innodb的取舍

  1. 如果表对事务的要求不高，同时是以查询和添加为主，我们考虑使用MyISAM存储引擎，比如bbs中的发帖表，回复表。
  3. 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB，比如订单表，库存表，商品表，账号表等等。

4、memory存储引擎

内存存储引擎，

特点：内部数据运行速度非常快，临时存储一些信息

缺点：服务器如果断电，重启，就会清空该存储引擎的全部数据

  1. create table t6(id int,name varchar(32)) engine memory charset utf8;

mysql服务，重启后，数据丢失。