MySQL优化

发表于

MySQL优化着手点

  1. 存储层（数据）：存储引擎、列类型、范式规范（三范式与逆范式）
    myisam查询速度快，但不支持事务
  2. 设计层（单台服务器）：索引、缓存、分区
    索引：主键、唯一键、普通键、外键约束
    缓存：表结构与表数据没有发生变化时，MySQL会将第一次查询的结果缓存到内存中
    分区：

  3. 架构层（多台服务器）：读写分离或者分布式

  4. 语句层：更合适的sql语句
    select 具体字段与（*），黄金索引

存储引擎

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

每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术（格式），获取适合的效果。

查询存储引擎：show engines
是否支持引擎：Support：default，默认
是否支持事务：Transaction

MySQL默认支持8种存储引擎，默认是innodb引擎，
memory存储引擎是将数据存储在内存中，服务器重启会导致内存中的数据丢失

myisam引擎和innodb引擎的区别

myisam

  1. myisam数据的读写速度相对于innodb引擎会快
  2. 支持表锁
  3. 数据压缩功能
  4. 支持全文索引（只支持英文）搜索引擎：sphinx，solr+ik 分词器

.frm：数据表的结构
.MYD：数据表对应的数据
.MYI：表锁数据表的索引文件

myisam引擎特有的压缩功能

蠕虫复制
insert into address select null,sheng,shi form address

MySQL server/bin/myisampack.exe进行数据压缩
用法：myisampack数据表的地址
shell>>myisampack.exe ‘C:\Server\mysql_data\t_myisam\t_address’

解决索引失效的问题
Romember to run myisa -rq on compressed tables
用法：myisamchk.exe -rq 表地址

压缩后的数据是一个只读数据表，不能写入数据，如果要写入数据必须解压

解压缩数据表
myisamchk –unpach(两个-)

删除多余文件：flush table 表名
批量写入数据时，开关索引
alter table disable(onable) keys
典型的应用场景
使用mysql储存日志文件信息

innodb

  1. 行锁
  2. 在5.6之前是不支持全文索引
  3. 支持事务
  4. 支持外键

.frm：表示数据表的结构
在datadir指向的目录下有一个ibdata1的文件该文件储存了innodb引擎数据表信息及索引的文件。此方式也叫共享文件存储

开启innodb引擎单独文件存储

数据备份还原

  1. 备份还原myisam引擎
    myisam引擎由于数据表结构、索引、数据都是用单独的文件存储，备份还原数据只需要复制粘贴文件即可
  2. 备份还原innodb引擎
    对于innodb引擎如果是使用单独文件存储依旧不能使用复制的方式备份数据，只能借助于mysql自带的根据备份
    备份使用：mysqldump工具
    还原使用:mysql工具
    备份用法：mysqldump -u 用户名 -p 密码 对应数据库名称 > 具体存储地址
    还原的用法：mysql -u 用户名 -p 密码 对应数据库名称 < 具体存储地址

数据类型的选择

decimal 金钱
使用char访问速度优于varchar

char与varchar的区别

  1. 存储的最大字符数不一样，char最多能够存储255个字符，varchar以utf8编码最大长度65535/3-1
  2. varchar存储多少个字符，占用多少个空间
  3. 对于末尾的空格处理不一样，char会过滤末尾的空格，varchar会保留空格

ip与整形数据的转换
MySQL内置函数：
inet_aton(ip)
inet_ntoa
PHP内置函数
ip2long
long2ip

数据库三范式

数据表设计规范

  1. 第一范式：所有的字段都是原子不可再分
  2. 第二范式：所有的字段都与主键有关系
  3. 第三范式： 所有字段必须与主键有直接关系

索引

索引是针对某个字段A进行了排序然后写到了一个单独的文件（myisam），innodb在共享文件中存储。以后使用该字段查询数据时，先到索引文件中查找，然后根据物理存储地址直接找到对应的数据

索引类型

主键索引：primary key ，唯一不重复，不能为空
唯一索引：unique key， 唯一不重复，可以为空
普通索引：key
全文索引：fulltext 只支持英文，中文使用：sphinx软件、solr+ik分词器

索引的结构

非聚族其它索引（myisam）：主键与其他索引排序方式一样
簇族索引（innodb） ：二分查找法相似

在簇族索引下主键索引在最终的叶子节点储存的数据是对应的所有数据（传值）

其他索引
最终的叶子节点储存的是主键信息

执行计划

sql语句分析

  1. 语法检查
  2. 执行分析
  3. 执行sql语句
  4. 返回执行结果

explain + select 语句
ID ：执行顺序
select_type
possible_keys：可能会用到的索引
key：实际将会使用到的索引
rows：受影响的行数，将要检索多少条数据
type：执行的类型，按照从优到差的顺序排列。system > const >eq_ref > ref > index > all