文档章节

mysql优化(1)

eatnothing
 eatnothing
发布于 2016/04/26 15:34
字数 1606
阅读 87
收藏 8

mysql优化

选择优化的数据类型

  • 一般情况下,选择可以正确存储的最小数据类型
  • 尽量避免使用null,通常最好指定列为not null
  • DATETIMETIMESTAMP都可以存储相同类型的数据(时间和日期,精确到秒),然而TIMESTAMP只使用DATETIME一半的存储空间,并且会根据时区变化,具有自动更新能力,另一方面TIMESTAMP允许的时间范围较小(1970的某时的开始一直到2037nian,精度为一秒,其值作为数字显示,如果输入非法的数据那么timestamp列将会变为0)
  • DECIMAL存储比BIGINT还大的整数,且DECIMAL用于存储精确的小数,在5.0以上或更高的版本最多存储65个数字
  • VARCHAR用于存储可变长字符串,它比定长类型更节省空间,VARCHAR需要使用1个或2个额外字节记录字符串的长度,如果列的最大长度小于或等于255字节,则用一个字节表示,否则使用两个字节(innodb可以把过长的VARCHAR存储为BLOB)
  • BLOBTEXT都是为存储很大的数据设计的字符串数据类型,分别采用二进制和字符方式存储
  • 使用无符号整数来存储ip地址

索引

mysql先在索引上按值查找,然后返回bao含该值的数据行,在mysql中,索引是在存储引擎层而不是服务器层实现的。

实际上很多存储引擎使用的是b+ tree(InnoDB),既每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历

B-Tree 通常意味着所有的值都是按顺序存储的,并且每一个叶子页到跟的距离相同

B-Tree能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据取而代之是从索引的根节点开始记性搜索,根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际定义了子节点页中的值的上限和下限

B-Tree对索引列是顺序组织存储的,所以适合范围查询

何时匹配

  • 全值匹配:和索引中所有的列进行匹配
  • 匹配最左前缀:只使用索引的第一列
  • 匹配列前缀:匹配某一列的值的开头部分
  • 匹配范围值
  • 精确匹配某一行,范围匹配某一列

何时不匹配

  • 如果不是按照索引的最左列开始查找,则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询,则其右面所有列都无法使用索引优化查找

哈希索引

  • 哈希索引基于哈希表实现,只有精确匹配索引的所有列才有效,对于每一行数据,存储引擎都会对所有索引计算一个哈希码。

限制:

  • 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行
  • 哈希索引无法用来排序
  • 哈希索引不支持部分索引列匹配查找EG:在数据列(a,b)如果只查询a则无法使用索引
  • 哈希索引只支持等值比较查询,不包含任何范围查询
  • 冲突时使用链地址法解决冲突

创建自定义哈希:如果存储引擎不支持哈希索引,则可以模仿像InnoDB一样创建哈希索引

创建一个表
create table pseudohash(
id int unsigned not null auto_increment,
url varchar(255) not null,
url_crc int unsigned not null default 0,
primary key(id)
);

创建插入触发器
delimiter //  将结尾以//来结束
create trigger pseudohash_crc_ins before insert on pseudohash for each row begin set NEW.url_crc=crc32(NEW.url);end;//

创建更新触发器
create trigger pseudohash_crc_upd before update on pseudohash for each row begin set NEW.url_crc=crc32(NEW.url);end;//
delimiter ;

如何处理hash冲突
例如
select id from pseudohash where url_crc=CRC32("http://www.mysql.com") and url="http://www.mysql.com"

索引的优点:

最常见的b-tree索引,按照顺序存储数据,所以mysql可以用来做order by和 group by操作

  • 索引减少了服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 索引可以将随机i/o变为数序i/o

前缀索引是一种能使索引更小,更快的有效方法,但一方面也有其缺点,mysql无法使用前缀索引做order by 和 group by操作。对于blob和text或者很长的varchar类型的列,必须使用前缀索引,因为mysql不允许索引这些列的完整长度

多列索引:在多个列上建立独立的单列索引大部分情况下并不能提高Mysql的查询性能,mysql在5.0后引入了索引合并;在一个多列的b-tree索引中索引列的顺序意味着首先按照最左列进行排序,所以将选择性最高的列放到索引的最前列(通常有用)

聚簇索引:是一种存储方式,当表有聚簇索引时,它的数据实际上存放在索引的叶子页中

聚簇索引的优点:1)可以把相关数据保存在一起2)数据访问更快3)使用覆盖索引扫描的查询可以直接使用页节点中的主键值

覆盖索引:如果一个索引bao含所有需要查询字段的值,我们就称之为覆盖索引(mysql只能用b-tree来实现覆盖索引)

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应该避免创建这样的索引,发现后也应该立即删除

表的数据可能碎片化,有三种类型的数据碎片1)行碎片2)行间碎片3)剩余空间碎片,可以通过执行optimize table或者在导出导入来整理数据

via:高性能的mysql

© 著作权归作者所有

eatnothing
粉丝 38
博文 128
码字总数 68736
作品 0
昌平
程序员
私信 提问
MySQL基础、管理、高级、用法、架构、优化好文章收集

基础 管理 高级 1、mysql分区技术 http://my.oschina.net/liting/blog/465149 2、mysql 外键(foreign key)的详解和实例 用法 1、mysql实现用拼音搜索中文的数据库实现 http://my.oschina....

tomener
2016/04/25
149
0
经典分享MySQL的limit查询优化

以下的文章主要是对MySQL limit查询优化的具体内容的介绍,我们大家都知道MySQL数据库的优化是相当重要的。其他最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,...

五味格子
2011/05/11
0
0
Mysql:优化分析(值得深思)

Mysql优化分析 Mysql作为现在的主流关系型数据库,在优化上值得深思啊! 不能只停留在如何使用Mysql,而不去思考如何优化的问题啊。 在谈到Mysql优化方面,Mysql在这方面有专门的文档说明这个...

王木东
2018/07/13
0
0
「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)

【mysql优化专题】:本专题全文围绕mysql优化进行全方位讲解,本篇为优化入门篇,让大家知道为什么要优化,究竟在优化什么。喜欢的朋友可以关注收藏。 优化,一直是面试最常问的一个问题。因...

java进阶架构师
2017/11/16
0
0
高性能MySQL06-查询优化(慢查询)

一、分析原因 SQL语句慢查询的原因有多种,如: 1)数据方面: 需要查询的表数据量太大导致性能下降; 是否向数据库请求了不需要的数据行或数据列; MySQL是否在扫描额外的记录 2)SQL语句太...

架构师springboot
05/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

老也有错?35岁程序员是一道坎,横亘在每个技术职场人的心中

随着互联网的高速发展变革,大龄恐惧症越来越多地在技术圈被人讨论。很多程序员在工作5-10年以后,都会开始思考5年、10年甚至更久以后的自己,会是怎样一种生活工作状态,以及是否会被时代抛...

我最喜欢三大框架
17分钟前
0
0
今日头条算法原理详解全集,值得收藏!

今天,算法分发已经是信息平台、搜索引擎、浏览器、社交软件等几乎所有软件的标配,但同时,算法也开始面临质疑、挑战和误解。今日头条的推荐算法,从 2012 年 9月第一版开发运行至今,已经经...

骚年锦时
29分钟前
2
0
零拷贝:用户态视角

在Linux系统越来越多的人听说过所谓的零拷贝技术,但是我经常遇到很多对这个名词没有完全理解的人。因此,我决定写一些文章,深挖这个问题,希望能揭开这个有用的特性。在这篇文章,我们从用...

凌渡
41分钟前
0
0
以太坊中文文档翻译-区块

本文原文链接 点击这里获取Etherscan API 中文文档(完整版) 完整内容排版更好,推荐读者前往阅读。 区块(Blocks) 区块相关的 API,接口的参数说明请参考Etherscan API 约定, 文档中不单独...

Tiny熊
49分钟前
2
0
Linux 内核的一个问题

是virtio 驱动,但是没有启动 virtio-mmio virtio-mmio.0: Failed to enable 64-bit or 32-bit DMA. Trying to continue, but this might not work.....[ 1.047924] md: ... autorun......

MtrS
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部