MySQL架构优化实战系列1：数据类型与索引调优全解析

《MySQL架构优化实战系列1：数据类型与索引调优全解析》要点：
本文介绍了MySQL架构优化实战系列1：数据类型与索引调优全解析，希望对您有用。如果有疑问，可以联系我们。

一、数据类型优化

数据类型

• 整数

数字类型：整数和实数

tinyint(8)、smallint(16)、mediuint(24)、int(32)、bigint(64) 数字表示对应最大存储位数,如 tinyint (-127 --- 128),tinyint unsigned 表示不允许负数,则范围为 (0 -- 255).

常规数据库中 int(11) 只是表示控制显示字符的个数是11个,int(1) 和 int(20) 存储和计算是一样的,即 int(1) 照样可以存储1111(4位数).

• 实数

实数有分数部分

• float 和 double 类型支持使用标准的浮点运算近似计算

• float 占用4个字节 double占用8个字节

• decimal 类型用于保存精确的小数

• decimal(18,9) 18表示小数点前后总位数 9表示小数点后面位数

• mysql 5.0版本以上 4个字节保存9位数字

• decimal(18,9) 共占用9个字节 小数点前4个字节 小数点后占1个 小数点后4个字节

• 字符串类型

varchar和char类型

• varchar保存可变长度的字符串,比固定长度类型占用更少的存储空间,只占用需要的空间.

  varchar使用额外的1到2字节存储长度,列小于255使用1字节保存长度,大于255使用2字节保存,varchar保留字符串末尾的空格.

• char是固定长度,保存char值时候 **mysql去掉任何末尾的空格**,进行比较时 空格会被填充到字符串末尾.

  很多的char列,效率高于varchar,比如 char(1)对于单字节字符集占用1字节,varchar(1)占用两字节,因为1字节保存长度.

慷慨不是明智的,分配真正需要的空间.

• Blob和Text类型

blob和text唯一区别就是blob保存二进制数据、没有字符集和排序规则.

选择优化的数据类型

• 更小通常越好

使用更少的磁盘、内存、cpu,确保不会低估保存的值,但是text有字符集和排序规则.mysql不能索引这些数据类型的完整长度,也不能为排序使用索引.

• 简单就好

比较整数的代价小于比较字符,使用mysql内建类型保存时间和日期,使用整数保存ip.

• 尽量避免

mysql难以优化可空列查询,使固定索引(整数列上的索引)编程可变大小索引；没有值可以使用 0 或者空字符串代替；把 列改为not 带来的性能提升很小.

• 确定类型

像数字、字符串、时间、直观类型可以确定,但是像 datetime 和timestamp,能保存同样的类型.timestamp使用空间只有datetime一半.可以保存时区.

• 使用enum代替字符串类型

enum列可以保存65535不同的字符串,存储在一个 "查找表"中 mysql内部存储的是列表中的位置.

内部存储的是这个字符串对应的位置,实际表中存储的还是字符串.

创建一个表fruit category字段为enum类型,包含4种不同水果：

插入4条数据,即4中不同水果.其中,最后一个菠萝(pineapple) 没有enum值 则插入了空数据.

发现字段category保存的还是字符串,其实内部已经将这些字符串关联到enum字符的位置.

支持字符串搜索和位置搜索

emu缺点在于插入数据之前,如果没有对应enum,则需要alter表结构.

enum优点在于占用更少的存储空间.

据说 enum 用于联接查询性能也比较好.

• 日期和时间类型

datetime 保存是1001年到9999年,精度是秒,存储值为 2016-05-06 22:39:40.

timestamp保存自 1970年1月1日午夜以来的秒数,和unix时间戳相同,提供4字节存储 只能表示1970年到2038年.默认timestamp值 为 NOT .

mysql中提供 from_unixtime函数把unix时间戳转换为日期

unix_timestamp把日期转换为unix时间戳

如果需要秒以下的精度保存日期和时间,可以使用bigint类型把它以毫秒的精度保存时间戳格式,或使用double保存秒的分数部分.

• 选择标识符

整数类型通常是标识符最佳选择,速度快,且能使用auto_increment,避免使用字符串做标识符,占用很多空间并且比整数类型要慢.

• 特殊类型的数据

通常使用varchar(15)保存IP地址,其实IP地址是无符号的32位整数,不是字符串,小数点仅仅为了可读性.

mysql提供了 inet_aton inet_ntoa,用于 ip地址和整数之前转换.

二、索引优化

索引基础知识

索引帮助mysql高效获取数据的数据结构,索引(mysql中叫"键(key)") 数据越大越重要.索引好比一本书,为了找到书中特定的话题,查看目录,获得页码.

select fruit_name from fruit where id = 5 索引列位于id列,索引按值查找并且返回任何包含该值的行.

如果索引了多列数据,那么列的顺序非常重要.

存储引擎说明

• myisam 存储引擎

• 表锁：myisam 表级锁

• 不支持自动恢复数据：断电之后 使用之前检查和执行可能的修复

• 不支持事务：不保证单个命令会完成,多行update 有错误 只有一些行会被更新

• 只有索引缓存在内存中：mysiam只缓存进程内部的索引

• 紧密存储：行被仅仅保存在一起

• Innodb存储引擎

• 事务性：Innodb支持事务和四种事务隔离级别

• 外键：Innodb唯一支持外键的存储引擎 create table 命令接受外键

• 行级锁：锁设定于行一级 有很好的并发性

• 多版本：多版本并发控制

• 按照主键聚集：索引按照主键聚集

• 所有的索引包含主键列：索引按照主键引用行 如果不把主键维持很短 索引就增长很大

• 优化的缓存：Innodb把数据和内存缓存到缓冲池 自动构建哈希索引

• 未压缩的索引：索引没有使用前缀压缩,阻塞auto_increment:Innodb使用表级锁产生新的auto_increment

• 没有缓存的count:myisam 会把行数保存在表中 Innodb中的count会全表或索引扫描

索引类型

索引在存储引擎实现的,而不是服务层.

• B-tree 索引

大多数谈及的索引类型就是B-tree类型,可以在create table 和其他命令使用它 myisam使用前缀压缩以减小索引,Innodb不会压缩索引,myiam索引按照行存储物理位置引用被索引的行,Innodb按照主键值引用行,B-tree数据存储是有序的,按照顺序保存了索引的列,加速了数据访问,存储引擎不会扫描整个表得到需要的数据.

• B-tree 索引实例

使用B-tree索引的查询类型,很好用于全键值、键值范围或键前缀查找,只有在超找使用了索引的最左前缀的时候才有用.

匹配全名：全键值匹配和索引中的所有列匹配

查找叫Tang Kang 出生于 1991-09-23 的人

匹配最左前缀：B-tree找到姓为tang的人

匹配列前缀： 匹配某列的值的开头部分 查找姓氏以T开头的人

匹配范围值：索引查找姓大于Tang小于zhu的人

精确匹配一部分并且匹配某个范围的另外一部分：

查找姓为Tang并且名字以字母K开头的人 精确匹配last_name列并且对

first_name进行范围查询

只访问索引的查询：B-tree支持只访问索引的查询,不会访问行

• B-tree局限性

B-tree局限性：(案例中索引顺序:last_name first_name dob )

如果查找没有送索引列的最左边开始,没有什么用处,即不能查找所有叫Kang 的人,也不能找到所有出生在某天的人,因为这些列不再索引最左边,也不能使用该索引超找某个姓氏以特定字符结尾的人.

不能跳过索引的列,即不能找到所有姓氏为Tang并且出生在某个特定日期的人,如果不定义first_name列的值,Mysql只能使用索引的第一列.

存储引擎不能优化任何在第一个范围条件右边的列,比如查询是where last_name = 'Tang' AND first_name like 'K%' AND dob='1993-09-23' 访问只能使用索引头两列.

由此可知 索引列顺序的重要性!

• 哈希索引

目前只有Memory存储引擎支持显示的哈希索引,而且Memory引擎对我来说不常用,所以我们就轻描淡写的过了吧.

• R-tree(空间索引)

Myisam支持空间索引,可以使用geometry空间数据类型.

空间索引不会要求where子句使用索引最左前缀可以全方位索引数据,可以高效使用任何数据组合查找 配合使用mercontains函数使用.

• 全文索引

fulltext是Myisam表特殊索引,从文本中找关键字不是直接和索引中的值进行比较.

全文索引可以和B-Tree索引混用,索引价值互不影响.

全文索引用于match against操作 而不是普通的where子句.

• 前缀索引和索引选择性

通常索引几个字符,而不是全部值,以节约空间并得到好的性能,同时也降低选择性.

索引选择性是不重复的索引值和全部行数的比值.高选择性的索引有好处,查找匹配过滤更多的行,唯一索引选择率为1最佳状态.

blob列、text列及很长的varchar列,必须定义前缀索引,mysql不允许索引他们的全文.

• 前缀索引和索引选择性实例

造数据

#复制一份与cs_area表结构

#插入1600数据

#模拟真实数据

#表area有name列 需要对name列前缀索引

#计算得比值接近0.9350就好了

#分别取 3 4 5位name值计算

#可知name列添加5位前缀索引就可以了

#Mysql不能在order by 或 group by查询使用前缀索引 也不能将其用作覆盖索引

• 聚集索引

聚集索引不是一种单独的索引类型,而是一种存储数据的方式.

Innodb 的聚集索引实际上同样的结构保存了B-tree索引和数据行,"聚集" 是指实际的数据行和相关的键值保存在一起,每个表只能有一个聚集索引,因此不能一次把行保存在两个地方. (由于聚集索引对我来说不常用,我们就略过啦~)

• 覆盖索引

索引支持高效查找行,mysql也能使用索引来接收列的数据.这样不用读取行数据,当发起一个被索引覆盖的查询,explain解释器的extra列看到 using index.

#满足条件：#

# select 查询的字段必须 有索引全覆盖

select last_name,first_name 其中 last_name 和first_name 必须都有索引

#不能在索引执行like操作

• 为排序使用索引扫描

mysql排序结果的方式：使用文件排序 、 扫描有序的索引

explain中的type列若为 "索引(Index)" 说明mysql扫描索引.单纯扫描索引很快,如果mysql没有使用索引覆盖查询 就不得不查找索引中发现的每一行.

mysql能有为排序和查找行使用同样的索引,如表 user 索引 (uid,birthday ) .

使用排序索引：

• 避免多余和重复索引

重复索引：类型相同,以同样的顺序在同样的列创建索引,比如在表user id列 添加 unique(id)约束 、id not .

primary key 约束 index(id),其实这些是相同的索引 !

多余索引：如存在(A)索引 应该扩展它 满足 (A,B)索引

(A,B)索引 <==> (B)

(A,B)索引 <==> (A)

(A,B) A最左前缀 (B,A) B最左前缀

• 索引实例研究

设计user表 字段：country、 state/region 、city 、sex 、age 、eye 、color 功能：支持组合条件搜索用户 支持用户排序 用户上次在线时间

• 支持多种过滤条件

不在选择性很差的列添加索引

• 优化排序

索引和表维护

表维护三个目标：查找和修复损坏、维护精确的索引统计,并减少碎片

• 查找并修复表损坏

check table 命令：确定表是否损坏,能抓到大部分表和索引错误

repair table 命令：修复损坏的表

myisamchk ：离线修复工具

• 更新索引统计

analyze table cs_area 更新索引统计信息,便于优化器优化sql

show index 命令检查索引的基数性

• 减少索引和数据碎片

myisam引擎 使用 optimize table 清除碎片 Innodb 引擎 使用 alter table .. engine = .. 重新创建索引

正则化和非正则化

• 正则化和非正则化

• 正则化数据库：每个因素只会表达一次,教师表teacher (id,school_id),学校表school

  (school_id,school_name) 优点：更新信息只变动一张表 缺点：简单的学校名称查询 需要关联表

  非正则化数据库：信息是重复的 或者 保存在多个地方

  
  

• 教师表teacher (id,school_id,school_name) 学校表school (school_id,school_name)

  
  

• 优点：便于直接统计对应学校名称的老师

  缺点：更新需要变动的表多一张

  
  

• 正则化和非正则化并用：比如需要统计用户的发帖数 可以在user表添加字段num_message 保存发帖总数 避免高密度查询统计

• 缓存和汇总表

实例：统计过去24小时发布的信息精确的数量

• 表周期性创建

周期创建可以得到没有碎片和全排序索引的高效表

注意：此法会将数据清除,只是得到一个没有碎片和高效的索引表.

计数表：比如缓存用户朋友数量、文件下载次数 通常建立一个单独的表,以保持快速维护计数器.

计划任务定期聚合函数查询,更新对应的字段.

近期热文(点击标题可阅读全文)

近期活动：

Gdevops全球敏捷运维峰会北京站

峰会官网：www.gdevops.com

《MySQL架构优化实战系列1：数据类型与索引调优全解析》是否对您有启发，欢迎查看更多与《MySQL架构优化实战系列1：数据类型与索引调优全解析》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!