UDN-企业互联网技术人气社区

板块导航

浏览  : 720
回复  : 2

[资源分享] MySQL架构优化实战系列1:数据类型与索引调优全解析

[复制链接]
瞌睡虫的头像 楼主
发表于 2016-7-4 09:49:20 | 显示全部楼层 |阅读模式
  一、数据类型优化

  数据类型

  整数

  数字类型:整数和实数

  tinyint(8)、smallint(16)、mediuint(24)、int(32)、bigint(64)数字表示对应最大存储位数,如tinyint(-127---128),tinyintunsigned表示不允许负数,则范围为(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表示小数点后面位数

  mysql5.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。

  尽量避免NULL

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

  确定类型

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

  使用enum代替字符串类型

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

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

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

  
1.webp.jpg


  
2.webp.jpg


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

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

  
3.webp.jpg


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

  
4.webp.jpg


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

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

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

  日期和时间类型

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

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

  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)")数据越大越重要。索引好比一本书,为了找到书中特定的话题,查看目录,获得页码。

  selectfruit_namefromfruitwhereid=5索引列位于id列,索引按值查找并且返回任何包含该值的行。

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

  存储引擎说明

  myisam存储引擎

  表锁:myisam表级锁

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

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

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

  紧密存储:行被仅仅保存在一起

  Innodb存储引擎

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

  外键:Innodb唯一支持外键的存储引擎createtable命令接受外键

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

  多版本:多版本并发控制

  按照主键聚集:索引按照主键聚集

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

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

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

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

  索引类型

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

  B-tree索引

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

  B-tree索引实例

  
5.webp.jpg


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

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

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

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

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

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

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

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

  first_name进行范围查询

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

  B-tree局限性

  B-tree局限性:(案例中索引顺序:last_namefirst_namedob)

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

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

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

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

  哈希索引

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

  R-tree(空间索引)

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

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

  全文索引

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

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

  全文索引用于matchagainst操作而不是普通的where子句。

  前缀索引和索引选择性

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

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

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

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

  造数据

  #复制一份与cs_area表结构

  
6.webp.jpg


  #插入1600数据

  
7.webp.jpg


  #模拟真实数据

  
8.webp.jpg


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

  
9.webp.jpg


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

  
10.webp.jpg


  #分别取345位name值计算

  
11.webp.jpg


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

  
12.webp.jpg


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

  聚集索引

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

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

  覆盖索引

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

  #满足条件:#

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

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

  #不能在索引执行like操作

  为排序使用索引扫描

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

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

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

  使用排序索引:

  
13.webp.jpg


  避免多余和重复索引

  重复索引:类型相同,以同样的顺序在同样的列创建索引,比如在表userid列添加unique(id)约束、idnotnull。

  primarykey约束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功能:支持组合条件搜索用户支持用户排序用户上次在线时间

  支持多种过滤条件

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

  优化排序

  
14.webp.jpg


  索引和表维护

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

  查找并修复表损坏

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

  repairtable命令:修复损坏的表

  myisamchk:离线修复工具

  更新索引统计

  analyzetablecs_area更新索引统计信息,便于优化器优化sql

  showindex命令检查索引的基数性

  
15.webp.jpg


  减少索引和数据碎片

  myisam引擎使用optimizetable清除碎片Innodb引擎使用altertable..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小时发布的信息精确的数量

  表周期性创建

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

  
16.webp.jpg


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

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

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

文章来源:DBAplus社群
文章作者:汤抗

发表于 2016-7-4 09:55:53 | 显示全部楼层
好帖子,感谢分享。
使用道具 举报

回复

瞌睡虫的头像 楼主
发表于 2016-7-4 17:04:15 | 显示全部楼层
jia玮_tlVj3 发表于 2016-7-4 09:55
好帖子,感谢分享。

谢谢赞赏
使用道具 举报

回复

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部