获取一年当中某一个月全部日期

LinuxA128_huanggr 回复了问题 • 2 人关注 • 2 个回复 • 581 次浏览 • 2016-01-04 16:03 • 来自相关话题

【俱乐部活动】敲开数据库的门,打开云计算的窗

activityzhangdh 发表了文章 • 2 个评论 • 522 次浏览 • 2016-01-01 15:28 • 来自相关话题

》活动介绍
萤火虫俱乐部 是一个开放的互联网技术交流平台,专注于互联网后端技术研究,旨在推广互联网技术在国内的传播。
官网:http://fireflyclub.org 
 
》奖品预告
到场参会人员签到时,可领取手机支架一个。 
现场互动环节,会有小礼品等你抽取哦(我不会告诉你们是精美水杯和U盘滴)

》活动报名
扫二维码报名:




报名链接:http://ceph2015.mikecrm.com/f.php?t=TCGg1G
》联系方式电话:18612720739
微信:zhangdh1113
邮箱:zhangdonghongemail@163.com
俱乐部QQ群:464035083》活动信息时间: 2016年01月10日(周日)9:30 ~ 17:00
地址:北京·朝阳·北辰泰岳大厦1902







》活动流程







》嘉宾介绍

1. 强昌金 ,去哪儿网DBA




嘉宾简介:曾任陌陌 MySQL DBA,负责MySQL生产环境数据库和Redis运维;现任去哪儿网DBA,负责MySQL数据库和Redis运维以及数据库管理平台的开发。演讲主题:《Redis应用与运维实践》
内容摘要:1. Redis概述
2. Redis应用注意事项
3. Redis运维踩过的坑
4. Redis cluster介绍2. 卓汝林,小米DBA




嘉宾简介:曾就职于HP、58;13年加入小米,主要负责MIUI、小米互娱和生态的数据库和缓存运营管理,目前专注于数据库和缓存技术,及其自动化运维系统的设计和开发。演讲主题:《MySQL开发运营规范和平台》
内容摘要:介绍小米的MySQL数据库开发运营规范,以及相关运营管理工具和平台。3. 黄湘龙,北京泰岳树人科技有限公司高级讲师




嘉宾简介:曾任新媒传信架构师、高级专家、海外解决方案顾问,原北京左岸风暴科技有限公司CTO。在飞信核心平台任职7年,带领飞信服务器侧核心开发团队支撑飞信产品迭代同时,保障亿级用户、数百万在线系统的平稳运行,全程参与飞信架构设计与核心代码编写,对承载亿级用户的高可用应用云平台架构有深刻理解。
组建、带领开发团队打造中东陌生人交友App,从开始写第一行代码到产品商用,全程主导产品的开发,包括通信协议设计、各平台协议栈实现、数据库设计、服务器高可用架构等。深度理解互联网应用开发技术,包括高效的二级制协议栈,服务器端高可用框架设计,面向海量数据的JAVA应用开发,WEB前端开发,JAVA性能调优等。演讲主题:《飞信数据库访问组件演进历程》
内容摘要:1.分享飞信服务器端对数据库访问架构经历的三次比较大的演进。
2.分享三次架构演讲过程中遇到的问题以及如何通过架构演讲解决问题。4. 李占国,大唐电信IaaS云联合创始人




嘉宾简介:在互联网行业已打拼了7年,有着丰富的网络、系统、云计算方面的运维及架构设计经验。
2008年就职于中国电信吉林分公司,主要负责吉林电信核心网络的运维和优化,参与过中国电信骨干核心设备思科12580、NE5000E等高端设备的割接,对ospf、bgp、is-is、mpls等路由协议有丰富的了解。
2011年就职于首都在线,主要负责首都在线基于hyper-v、VMvare等云平台架构的设计,当时的架构设计一直延续至今,首都在线云业务每年有着过亿的营收额。
2013年就职世纪佳缘运维主管,主要负责IDC机房网络架构、系统、openstack云平台、ceph平台运维工作,2013年发起部署上线openstack云平台、ceph存储平台,在世纪佳缘的两年积累了丰富的openstack、ceph使用及运维经验。
2015年在大唐电信内部创业,属于大唐电信iaas云联合创始人之一,大唐电信iaas云基于openstack开发,主要业务模块有2C公有云、2B公有云、企业级私有云,大唐电信iaas云在openstack云开发行业处于领先水平。




演讲主题:《大唐电信IaaS平台Openstack应用实战》
内容摘要:1.大唐电信IaaS平台Openstack的使用实践
2.大唐电信IaaS平台Openstack的网络架构5. 王磊,时速云联合创始人兼CTO




嘉宾简介:目前主要负责时速云容器即服务平台的技术架构、设计和开发工作。原IBM中国开发实验室资深软件工程师,曾负责IBM服务器产品、BPM、WAS、Bluemix等产品的开发工作。演讲主题:《微服务架构和容器云平台》
内容摘要:1.分享微服务的常用设计模式
2.结合 Kubernetes 和时速云平台,看微服务设计模式在实际架构设计中的运用6. 张冬洪,3w教育讲师,Ceph中国社区联合创始人兼沙龙活动负责人




嘉宾简介:曾先后在私营企业,外企和上市公司担任系统工程师和DBA工作,拥有多年互联网运维经验、在大数据、虚拟化、云计算和自动化运维方面积累了丰富一线实战经验。曾在外企负责AWS生产环境的全部server,以及负责世纪佳缘基于Openstack私有云和Ceph分布式存储项目。演讲主题:《Ceph在世纪佳缘的应用》
内容摘要:1.介绍佳缘Ceph的部署情况和应用场景
2.介绍佳缘Ceph文件存储遇到的问题和解决方案
3.介绍Ceph和Openstack的结合
4.介绍Calamari监控Ceph7. 陈晓,大唐电信IaaS云平台架构师




嘉宾简介:大唐IAAS云平台核心成员,毕业后有5年的计算机软件设计、开发、架构经验。分别于南开大学和中国科学院研究生院取得本科和硕士专业学位。曾就职于IBM中国研发中心、世纪互联、大唐等公司,从D版本开始参加OpenStack的开发,是OpenStack 中国早期社区贡献者、云平台实践者,社区贡献代码有两千余行,内部贡献代码几万行,对计算、存储、网络、消息队列、监控等OpenStack各模块架构和代码实现有深入理解。在长期就职于IBM期间参与社区开发、IBM OpenStack 企业版 、IBM GCG LBS System X的OpenStack 集成与IBM OpenStack Chef Installer等的开发实践;在世纪互联期间参与OpenStack公有云的落地实践,负责后端整体开发; 在大唐电信内部创业,作为开发架构师,大唐电信IAAS云有2C公有云、2B公有云、企业级私有云,在OpenStack云开发行业现处于领先水平。演讲主题:《漫谈Openstack开发那点事》
内容摘要:1.openstack 开发相关基础技能—git、python、linux等
2.openstack代码实现架构
3.openstack开发基础
4.openstack相关模块的二次开发
5.openstack社区是如何工作的、应如何参与社区》活动赞助
主办方:萤火虫俱乐部
场地&礼品赞助:北京泰岳树人科技有限公司
 
扫描下方QQ群二维码加入萤火虫俱乐部,和大神们一起交流吧~




扫描下方二维码关注俱乐部订阅号,第一时间获知活动信息




 
Thank You! 查看全部
》活动介绍
萤火虫俱乐部 是一个开放的互联网技术交流平台,专注于互联网后端技术研究,旨在推广互联网技术在国内的传播。
官网:http://fireflyclub.org 
 
》奖品预告
到场参会人员签到时,可领取手机支架一个。 
现场互动环节,会有小礼品等你抽取哦(我不会告诉你们是精美水杯和U盘滴)

》活动报名
扫二维码报名:
报名.png

报名链接:http://ceph2015.mikecrm.com/f.php?t=TCGg1G
》联系方式
电话:18612720739
微信:zhangdh1113
邮箱:zhangdonghongemail@163.com
俱乐部QQ群:464035083
》活动信息
时间: 2016年01月10日(周日)9:30 ~ 17:00
地址:北京·朝阳·北辰泰岳大厦1902



地址.jpg

》活动流程
1.jpg
2.jpg

》嘉宾介绍

1. 强昌金 ,去哪儿网DBA
强昌金_new.png

嘉宾简介:
曾任陌陌 MySQL DBA,负责MySQL生产环境数据库和Redis运维;现任去哪儿网DBA,负责MySQL数据库和Redis运维以及数据库管理平台的开发。
演讲主题:《Redis应用与运维实践》
内容摘要:
1. Redis概述
2. Redis应用注意事项
3. Redis运维踩过的坑
4. Redis cluster介绍
2. 卓汝林,小米DBA
卓汝林.png

嘉宾简介:
曾就职于HP、58;13年加入小米,主要负责MIUI、小米互娱和生态的数据库和缓存运营管理,目前专注于数据库和缓存技术,及其自动化运维系统的设计和开发。
演讲主题:《MySQL开发运营规范和平台》
内容摘要:
介绍小米的MySQL数据库开发运营规范,以及相关运营管理工具和平台。
3. 黄湘龙,北京泰岳树人科技有限公司高级讲师
黄湘龙.png

嘉宾简介:
曾任新媒传信架构师、高级专家、海外解决方案顾问,原北京左岸风暴科技有限公司CTO。在飞信核心平台任职7年,带领飞信服务器侧核心开发团队支撑飞信产品迭代同时,保障亿级用户、数百万在线系统的平稳运行,全程参与飞信架构设计与核心代码编写,对承载亿级用户的高可用应用云平台架构有深刻理解。
组建、带领开发团队打造中东陌生人交友App,从开始写第一行代码到产品商用,全程主导产品的开发,包括通信协议设计、各平台协议栈实现、数据库设计、服务器高可用架构等。深度理解互联网应用开发技术,包括高效的二级制协议栈,服务器端高可用框架设计,面向海量数据的JAVA应用开发,WEB前端开发,JAVA性能调优等。
演讲主题:《飞信数据库访问组件演进历程》
内容摘要:
1.分享飞信服务器端对数据库访问架构经历的三次比较大的演进。
2.分享三次架构演讲过程中遇到的问题以及如何通过架构演讲解决问题。
4. 李占国,大唐电信IaaS云联合创始人
李占国.png

嘉宾简介:
在互联网行业已打拼了7年,有着丰富的网络、系统、云计算方面的运维及架构设计经验。
2008年就职于中国电信吉林分公司,主要负责吉林电信核心网络的运维和优化,参与过中国电信骨干核心设备思科12580、NE5000E等高端设备的割接,对ospf、bgp、is-is、mpls等路由协议有丰富的了解。
2011年就职于首都在线,主要负责首都在线基于hyper-v、VMvare等云平台架构的设计,当时的架构设计一直延续至今,首都在线云业务每年有着过亿的营收额。
2013年就职世纪佳缘运维主管,主要负责IDC机房网络架构、系统、openstack云平台、ceph平台运维工作,2013年发起部署上线openstack云平台、ceph存储平台,在世纪佳缘的两年积累了丰富的openstack、ceph使用及运维经验。
2015年在大唐电信内部创业,属于大唐电信iaas云联合创始人之一,大唐电信iaas云基于openstack开发,主要业务模块有2C公有云、2B公有云、企业级私有云,大唐电信iaas云在openstack云开发行业处于领先水平。




演讲主题:《大唐电信IaaS平台Openstack应用实战》
内容摘要:
1.大唐电信IaaS平台Openstack的使用实践
2.大唐电信IaaS平台Openstack的网络架构
5. 王磊,时速云联合创始人兼CTO
王磊.png

嘉宾简介:
目前主要负责时速云容器即服务平台的技术架构、设计和开发工作。原IBM中国开发实验室资深软件工程师,曾负责IBM服务器产品、BPM、WAS、Bluemix等产品的开发工作。
演讲主题:《微服务架构和容器云平台》
内容摘要:
1.分享微服务的常用设计模式
2.结合 Kubernetes 和时速云平台,看微服务设计模式在实际架构设计中的运用
6. 张冬洪,3w教育讲师,Ceph中国社区联合创始人兼沙龙活动负责人
张冬洪.png

嘉宾简介:
曾先后在私营企业,外企和上市公司担任系统工程师和DBA工作,拥有多年互联网运维经验、在大数据、虚拟化、云计算和自动化运维方面积累了丰富一线实战经验。曾在外企负责AWS生产环境的全部server,以及负责世纪佳缘基于Openstack私有云和Ceph分布式存储项目。
演讲主题:《Ceph在世纪佳缘的应用》
内容摘要:
1.介绍佳缘Ceph的部署情况和应用场景
2.介绍佳缘Ceph文件存储遇到的问题和解决方案
3.介绍Ceph和Openstack的结合
4.介绍Calamari监控Ceph
7. 陈晓,大唐电信IaaS云平台架构师
陈晓.png

嘉宾简介:
大唐IAAS云平台核心成员,毕业后有5年的计算机软件设计、开发、架构经验。分别于南开大学和中国科学院研究生院取得本科和硕士专业学位。曾就职于IBM中国研发中心、世纪互联、大唐等公司,从D版本开始参加OpenStack的开发,是OpenStack 中国早期社区贡献者、云平台实践者,社区贡献代码有两千余行,内部贡献代码几万行,对计算、存储、网络、消息队列、监控等OpenStack各模块架构和代码实现有深入理解。在长期就职于IBM期间参与社区开发、IBM OpenStack 企业版 、IBM GCG LBS System X的OpenStack 集成与IBM OpenStack Chef Installer等的开发实践;在世纪互联期间参与OpenStack公有云的落地实践,负责后端整体开发; 在大唐电信内部创业,作为开发架构师,大唐电信IAAS云有2C公有云、2B公有云、企业级私有云,在OpenStack云开发行业现处于领先水平。
演讲主题:《漫谈Openstack开发那点事》
内容摘要:
1.openstack 开发相关基础技能—git、python、linux等
2.openstack代码实现架构
3.openstack开发基础
4.openstack相关模块的二次开发
5.openstack社区是如何工作的、应如何参与社区
活动赞助
主办方:萤火虫俱乐部
场地&礼品赞助:北京泰岳树人科技有限公司
 
扫描下方QQ群二维码加入萤火虫俱乐部,和大神们一起交流吧~
QQ群.png

扫描下方二维码关注俱乐部订阅号,第一时间获知活动信息
微信订阅号.jpg

 
Thank You!

开发人员应该知道的索引原理

MySQLFred 发表了文章 • 4 个评论 • 1126 次浏览 • 2015-12-30 15:55 • 来自相关话题

作者:黄湘龙
架构师交流群(312254004)
欢迎非商业转载,商业使用请联系我 
 

索引是有效使用数据库的基础,但你的数据量很小的时候,或许通过扫描整表来存取数据的性能还能接受,但当数据量极大时,当访问量极大时,就一定需要通过索引的辅助才能有效地存取数据。一般索引建立的好坏是性能好坏的成功关键。

1.InnoDb数据与索引存储细节

使用InnoDb作为数据引擎的Mysql和有聚集索引的SqlServer的数据存储结构有点类似,虽然在物理层面,他们都存储在Page上,但在逻辑上面,我们可以把数据分为三块:数据区域,索引区域,主键区域,他们通过主键的值作为关联,配合工作。默认配置下,一个Page的大小为16K。

一个表数据空间中的索引数据区域中有很多索引,每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这种索引也叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

 表数据都以Row的形式放在一个一个大小为16K的Page中,在每个数据Page中有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。






2.索引优化检索的原理

索引是数据库的灵魂,如果没有索引,数据库也就是一堆文本文件,存在的意义并不大。索引能让数据库成几何倍数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引(也就是主键)和普通索引。上节我们已经讲解了这两种索引的存储结构,现在我们仔细讲解下索引是如何工作的。

聚集索引和普通索引都有可能由多个字段组成,我们又称这种索引为复合索引,1.2.3将为大家解析这种索引的性能情况.

2.1聚集索引
从上节我们知晓,Innodb的所有数据是按照聚集索引排序的,聚集索引这种存储方式使得按主键的搜索十分高效,如果我们SQL语句的选择条件中有聚集索引,数据库会优先使用聚集索引来进行检索工作。
数据库引擎根据条件中的主键的值,迅速在B+Tree中找到主键对应的叶节点,然后把叶节点所在的Page数据库读取到内存,返回给用户,如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下:

select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;



我们可以看到,数据库读从磁盘取了两个Page就把809Bytes的数据提取出来返回给客户端。
下面我们试一下,如果试一下选取条件没有包含主键和索引的情况:

select * from `UP_User` where bigPortrait = '5F29E883BFA8903B';

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25


可以看到如果使用主键作为检索条件,检索时间花了0.3ms,只读取了两个Page,而不使用主键作为检索条件,检索时间花了2.8ms,读取了25个Page,全局扫描才把这条记录给找出来。这还是一个只有1000多行的表,如果更大的数据量,对比更加强烈。

 对于这两个Page,一个是主键B+Tree的数据,一个是10000094这条数据所在的数据页。


2.2普通索引
我们用普通索引作为检索条件来搜索数据需要检索两遍索引:首先检索普通索引获得主键,然后用主键到主索引中检索获得记录。如上图红色线条的流向。
下面我用一个例子来看看数据库的表现:

 select * from UP_User where userName = 'fred';# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4


 我们可以看到数据库用了0.4ms来检索这条数据,读取了4个page,比用主键作为检索条件多用了0.1ms,多读取了两个Page,而这两个Page就是userName这个普通索引的B+Tree所在的数据页。


2.3复合索引

聚集索引和普通索引都有可能由多个字段组成,多个字段组成的索引的叶节点的Key由多个字段的值按照顺序拼接而成。这种索引的存储结构是这样的,首先按照第一个字段建立一棵B+Tree,叶节点的Key是第一个字段的值,叶节点的value又是一棵小的B+Tree,逐级递减。对于这样的索引,用排在第一的字段作为检索条件才有效提高检索效率。排在后面的字段只能在排在他前面的字段都在检索条件中的时候才能起辅助效果。下面我们用例子来说明这种情况。

我们在UP_User表上建立一个用来测试的复合索引,建立在 (`nickname`,`regTime`)两个字段上,下面我们测试下检索性能:

 select * from UP_User where nickName='fredlong'; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4 


我们看到索引起作用了,和普通索引的效果一样都用了0.43ms,读取了四个Page就完成了任务。

 select * from UP_User where regTime = '2015-04-27 09:53:02'; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26 


从这次选择的执行情况来看,虽然regTime在刚才建立的复合索引中,还是做了全局扫描。因为这个复合索引排在regTime字段前面的nickname字段没有出现在选择条件中,所以这个索引我们没用用到。
那么我们什么情况下会用到复合索引呢。我一般在两种情况下会用到:
 需要复合索引来排重的时候。 用索引的第一个字段选取出来的结果不够精准,需要第二个字段做进一步的性能优化。

 我基本上没有建立过三个以上的字段做复合索引,如果出现这种情况,我觉的你的表设计可能出现了大而全的问题,需要在表设计层面调优,而不是通过增加复杂的索引调优。所有复杂的东西都是大概率有问题的。

3.批量选择的效率
我们业务经常这样的诉求,选择某个用户发送的所有消息,这个帖子所有回复内容,所有昨天注册的用户的UserId等。这样的诉求需要从数据库中取出一批数据,而不是一条数据,数据库对这种请求的处理逻辑稍微复杂一点,下面我们来分析一下各种情况。
3.1根据主键批量检索数据
我们有一张表PW_Like,专门存储Feed的所有赞(Like),该表使用feedId和userId做联合主键,其中feedId为排在第一位的字段。该表一共有19个Page的数据。下面我们选取feedId为11593所有的赞:

 select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084 Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2 


我们花了0.47ms取出了58条数据,但一共只读取了2个Page的数据,其中一个Page还是这个表的主键。说明这58条数据都存储在同一个Page中。这种情况,对于数据库来说,是效率最高的情况。

3.2根据普通索引批量检索数据
还是刚才那个表,我们除了主键以外,还在userId上建立了索引,因为有时候需要查询某个用户点过的赞。那么我们来看看只通过索引,不通过主键来检索批量数据时候,数据库的效率情况。

 select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062 Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15 


我们可以看到的结果是,虽然我们只取出了27条数据,但是我们读取了15个数据Page,花了2.8毫秒,虽然没有进行全局扫描,但基本上也把一般的数据块读取出来了。因为PW_Like中的数据由于是按照feedId物理排序的,所以这27条数据分别分布在13个Page中(有两个Page是索引和主键),所以数据库需要把这13个Page全部从磁盘中读取出来,哪怕某一个Page(16K)上只有一条数据(15Bytes),也需要把这个数据Page读出来才能取出所有的目标Row。

通过普通索引来检索批量数据,效率明显比通过主键来检索要低得多。因为数据是分散的,所以需要分散地读取数据Page进行拼接才能完成任务。但是索引对主键而言还是非常有必要的补充,比如上面这个例子,当用户量达到100万的时候,检索某一个用户点的所有的赞的成本也只是大概读取15个Page,花2ms左右。

3.2检索一段时间范围内的数据
选取一定范围内的数据是我们经常要遇到的问题。在海量数据的表中检索一定范围内的数据,很容易引起性能问题。我们遇到的最常见的需求有以下几种:

选取一段时间内注册的用户信息

这种时候,时间肯定不会是用户表的主键,如果直接用时间作为选择条件来检索,效率会非常差,如何解决这种问题呢?我采取的办法是,把注册时间作为用户表的索引,每次先把需要检索的时间的两端的userId都差出来,然后用这两个userId做选择条件做第三次查询就是我们想要的数据了。这三次检索我们只需要读取大约10个Page就能解决问题。这种方法看起来很麻烦,但是是表的数据量达到亿级的时候的唯一解决方案。
选取一段时间内的日志
日志表是我们最常见的表,如何设计好是经常聊到的话题。日志表之所以不好设计是因为大家都希望用时间作为主键,这样检索一段时间内的日志将非常方便。但是用时间作为主键有个非常大的弊端,当日志插入速度很快的时候,就会出现因为主键重复而引起冲突。
对于这种情况,我一般把日志生成时间和一个自增的Id作为日志表的联合主键,把时间作为第一个字段,这样即避免了日志插入过快引起的主键唯一性冲突,又能便捷地根据时间做检索工作,非常方便。下面是这种日志表的检索的例子,大家可以看到性能非常好。还有就是日志表最好是每天一个表,这样能更便利地管理和检索。

select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05";
# Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
# Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 3


4.批量选择的效率
我们在批量检索数据的时候,对选择的结果在大多数情况下,都需要数据是排好序的。排序的性能也是日常需要注意到的,下面我们分三种情况来分析下数据库是如何排序的。
对于ORDER BY 主键这种情况,数据库是非常乐于见到的,基本上不会有额外性能的损耗,因为数据本来就是按照主键顺序存储的,取出来直接返回即可。
下面的所有关于排序的例子是在UP_MessageHistory表上做的实验,这个表一共有195个page,35417行数据,主键建立在字段id上,在sendUserId和destUserId上都建立了索引。
首先我们先做一个没有排序的检索:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125  


然后我们在相同的条件下对id进行排序: 

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 

 
从上面的数据可以看出,性能和没有加order by差不多,基本没有额外的性能损耗。接下来我们对索引进行排序:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125 



接下来我们用普通字符串字段做排序再看看:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 



然后我们再用普通的数字类型字段排序看看情况:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime; 
# Query_time: 0.018522 Lock_time: 0.000107 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 95709 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 


 针对以上的实验结果,我们可以得出以下结论:
针对主键做排序操作不会有性能损耗;针对不在选择条件中的索引字段做排序操作,索引不会起优化排序的作用;针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。

下面我们再研究下用选择条件中的索引字段排序,数据库是否会优化排序算法,我们任然用UP_User表来研究。

 select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 


然后我们用选择条件中的索引字段做排序:

 select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17 



然后我们用选择条件中的非索引数值字段做排序:

 select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 


从上面三条查询语句的执行时间来分析,使用索引字段排序和不排序花的时间差不多,比使用普通字段排序花的时间少一些,因此我们可以得出第四条结论:

针对在选择条件中的索引字段做排序操作,索引会起优化排序的作用。

5.索引维护

在前面我们可以看到所有的主键和索引都是排好序的,那么排序这件事情就需要销号资源,每次有新的数据插入,或者老的数据的数值发生变更,排序就需要调整,这里面是需要损耗性能的,下面我们分析一下。

自增型字段作为主键时,数据库对主键的维护成本非常低:
每次新增加的值都是一个最大的值,追加到最后即可,其他数据不需要挪动;这种数据一般不做修改。






  
使用业务型字段作为主键时,主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。
 





 
 
因为innodb主键和数据是在放在一块的,每次挪动主键,也需要挪动数据,维护的成本会比较搞,对于需要频繁写入的表,不建议使用业务字段作为主键的。
由于主键是所有索引的叶节点的值,也是数据排序的依据,如果主键的值被修改,那么需要修改所有相关索引,并且需要修改整个主键B+Tree的排序,损耗会非常大。避免频繁更新主键可以避免以上提到的问题。

 update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11 从上面的数据可以看出来,索然SQL语句只修改了一条数据,却影响了11个Page。
 

 

相对于主键而已,索引就轻很多,它的叶节点的值是主键,很轻,维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低,维护8个就不一定低了,这种事需要均衡地对待。

 
6.索引设计原则

索引其实是一把双刃剑,用好了事半功倍,没用好,事倍功半。

主键的字段无特殊情况,一定要使用数值类型的,排序时占用计算资源少,存储时占用空间也少。若主键的字段值很大,则整个数据表的各种索引也会变得没有效率,因为所有的索引的叶节点的值都是主键。

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。 查看全部
作者:黄湘龙
架构师交流群(312254004)
欢迎非商业转载,商业使用请联系我 
 

索引是有效使用数据库的基础,但你的数据量很小的时候,或许通过扫描整表来存取数据的性能还能接受,但当数据量极大时,当访问量极大时,就一定需要通过索引的辅助才能有效地存取数据。一般索引建立的好坏是性能好坏的成功关键。

1.InnoDb数据与索引存储细节

使用InnoDb作为数据引擎的Mysql和有聚集索引的SqlServer的数据存储结构有点类似,虽然在物理层面,他们都存储在Page上,但在逻辑上面,我们可以把数据分为三块:数据区域,索引区域,主键区域,他们通过主键的值作为关联,配合工作。默认配置下,一个Page的大小为16K。

一个表数据空间中的索引数据区域中有很多索引,每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这种索引也叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

 表数据都以Row的形式放在一个一个大小为16K的Page中,在每个数据Page中有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。

存储结构.jpg


2.索引优化检索的原理

索引是数据库的灵魂,如果没有索引,数据库也就是一堆文本文件,存在的意义并不大。索引能让数据库成几何倍数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引(也就是主键)和普通索引。上节我们已经讲解了这两种索引的存储结构,现在我们仔细讲解下索引是如何工作的。

聚集索引和普通索引都有可能由多个字段组成,我们又称这种索引为复合索引,1.2.3将为大家解析这种索引的性能情况.

2.1聚集索引
从上节我们知晓,Innodb的所有数据是按照聚集索引排序的,聚集索引这种存储方式使得按主键的搜索十分高效,如果我们SQL语句的选择条件中有聚集索引,数据库会优先使用聚集索引来进行检索工作。
数据库引擎根据条件中的主键的值,迅速在B+Tree中找到主键对应的叶节点,然后把叶节点所在的Page数据库读取到内存,返回给用户,如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下:


select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;




我们可以看到,数据库读从磁盘取了两个Page就把809Bytes的数据提取出来返回给客户端。
下面我们试一下,如果试一下选取条件没有包含主键和索引的情况:


select * from `UP_User` where bigPortrait = '5F29E883BFA8903B';

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25



可以看到如果使用主键作为检索条件,检索时间花了0.3ms,只读取了两个Page,而不使用主键作为检索条件,检索时间花了2.8ms,读取了25个Page,全局扫描才把这条记录给找出来。这还是一个只有1000多行的表,如果更大的数据量,对比更加强烈。

 对于这两个Page,一个是主键B+Tree的数据,一个是10000094这条数据所在的数据页。


2.2普通索引
我们用普通索引作为检索条件来搜索数据需要检索两遍索引:首先检索普通索引获得主键,然后用主键到主索引中检索获得记录。如上图红色线条的流向。
下面我用一个例子来看看数据库的表现:


 select * from UP_User where userName = 'fred';# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4



 我们可以看到数据库用了0.4ms来检索这条数据,读取了4个page,比用主键作为检索条件多用了0.1ms,多读取了两个Page,而这两个Page就是userName这个普通索引的B+Tree所在的数据页。


2.3复合索引

聚集索引和普通索引都有可能由多个字段组成,多个字段组成的索引的叶节点的Key由多个字段的值按照顺序拼接而成。这种索引的存储结构是这样的,首先按照第一个字段建立一棵B+Tree,叶节点的Key是第一个字段的值,叶节点的value又是一棵小的B+Tree,逐级递减。对于这样的索引,用排在第一的字段作为检索条件才有效提高检索效率。排在后面的字段只能在排在他前面的字段都在检索条件中的时候才能起辅助效果。下面我们用例子来说明这种情况。

我们在UP_User表上建立一个用来测试的复合索引,建立在 (`nickname`,`regTime`)两个字段上,下面我们测试下检索性能:


 select * from UP_User where nickName='fredlong'; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4 



我们看到索引起作用了,和普通索引的效果一样都用了0.43ms,读取了四个Page就完成了任务。


 select * from UP_User where regTime = '2015-04-27 09:53:02'; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26 



从这次选择的执行情况来看,虽然regTime在刚才建立的复合索引中,还是做了全局扫描。因为这个复合索引排在regTime字段前面的nickname字段没有出现在选择条件中,所以这个索引我们没用用到。
那么我们什么情况下会用到复合索引呢。我一般在两种情况下会用到:
  1.  需要复合索引来排重的时候。
  2.  用索引的第一个字段选取出来的结果不够精准,需要第二个字段做进一步的性能优化。


 我基本上没有建立过三个以上的字段做复合索引,如果出现这种情况,我觉的你的表设计可能出现了大而全的问题,需要在表设计层面调优,而不是通过增加复杂的索引调优。所有复杂的东西都是大概率有问题的。

3.批量选择的效率
我们业务经常这样的诉求,选择某个用户发送的所有消息,这个帖子所有回复内容,所有昨天注册的用户的UserId等。这样的诉求需要从数据库中取出一批数据,而不是一条数据,数据库对这种请求的处理逻辑稍微复杂一点,下面我们来分析一下各种情况。
3.1根据主键批量检索数据
我们有一张表PW_Like,专门存储Feed的所有赞(Like),该表使用feedId和userId做联合主键,其中feedId为排在第一位的字段。该表一共有19个Page的数据。下面我们选取feedId为11593所有的赞:


 select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084 Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2 



我们花了0.47ms取出了58条数据,但一共只读取了2个Page的数据,其中一个Page还是这个表的主键。说明这58条数据都存储在同一个Page中。这种情况,对于数据库来说,是效率最高的情况。

3.2根据普通索引批量检索数据
还是刚才那个表,我们除了主键以外,还在userId上建立了索引,因为有时候需要查询某个用户点过的赞。那么我们来看看只通过索引,不通过主键来检索批量数据时候,数据库的效率情况。


 select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062 Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15 



我们可以看到的结果是,虽然我们只取出了27条数据,但是我们读取了15个数据Page,花了2.8毫秒,虽然没有进行全局扫描,但基本上也把一般的数据块读取出来了。因为PW_Like中的数据由于是按照feedId物理排序的,所以这27条数据分别分布在13个Page中(有两个Page是索引和主键),所以数据库需要把这13个Page全部从磁盘中读取出来,哪怕某一个Page(16K)上只有一条数据(15Bytes),也需要把这个数据Page读出来才能取出所有的目标Row。

通过普通索引来检索批量数据,效率明显比通过主键来检索要低得多。因为数据是分散的,所以需要分散地读取数据Page进行拼接才能完成任务。但是索引对主键而言还是非常有必要的补充,比如上面这个例子,当用户量达到100万的时候,检索某一个用户点的所有的赞的成本也只是大概读取15个Page,花2ms左右。

3.2检索一段时间范围内的数据
选取一定范围内的数据是我们经常要遇到的问题。在海量数据的表中检索一定范围内的数据,很容易引起性能问题。我们遇到的最常见的需求有以下几种:

选取一段时间内注册的用户信息

这种时候,时间肯定不会是用户表的主键,如果直接用时间作为选择条件来检索,效率会非常差,如何解决这种问题呢?我采取的办法是,把注册时间作为用户表的索引,每次先把需要检索的时间的两端的userId都差出来,然后用这两个userId做选择条件做第三次查询就是我们想要的数据了。这三次检索我们只需要读取大约10个Page就能解决问题。这种方法看起来很麻烦,但是是表的数据量达到亿级的时候的唯一解决方案。
选取一段时间内的日志
日志表是我们最常见的表,如何设计好是经常聊到的话题。日志表之所以不好设计是因为大家都希望用时间作为主键,这样检索一段时间内的日志将非常方便。但是用时间作为主键有个非常大的弊端,当日志插入速度很快的时候,就会出现因为主键重复而引起冲突。
对于这种情况,我一般把日志生成时间和一个自增的Id作为日志表的联合主键,把时间作为第一个字段,这样即避免了日志插入过快引起的主键唯一性冲突,又能便捷地根据时间做检索工作,非常方便。下面是这种日志表的检索的例子,大家可以看到性能非常好。还有就是日志表最好是每天一个表,这样能更便利地管理和检索。


select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05";
# Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
# Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 3



4.批量选择的效率
我们在批量检索数据的时候,对选择的结果在大多数情况下,都需要数据是排好序的。排序的性能也是日常需要注意到的,下面我们分三种情况来分析下数据库是如何排序的。
对于ORDER BY 主键这种情况,数据库是非常乐于见到的,基本上不会有额外性能的损耗,因为数据本来就是按照主键顺序存储的,取出来直接返回即可。
下面的所有关于排序的例子是在UP_MessageHistory表上做的实验,这个表一共有195个page,35417行数据,主键建立在字段id上,在sendUserId和destUserId上都建立了索引。
首先我们先做一个没有排序的检索:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125  



然后我们在相同的条件下对id进行排序: 


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 


 
从上面的数据可以看出,性能和没有加order by差不多,基本没有额外的性能损耗。接下来我们对索引进行排序:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125 




接下来我们用普通字符串字段做排序再看看:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 




然后我们再用普通的数字类型字段排序看看情况:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime; 
# Query_time: 0.018522 Lock_time: 0.000107 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 95709 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 



 针对以上的实验结果,我们可以得出以下结论:
  1. 针对主键做排序操作不会有性能损耗;
  2. 针对不在选择条件中的索引字段做排序操作,索引不会起优化排序的作用;
  3. 针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。


下面我们再研究下用选择条件中的索引字段排序,数据库是否会优化排序算法,我们任然用UP_User表来研究。


 select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 



然后我们用选择条件中的索引字段做排序:


 select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17 




然后我们用选择条件中的非索引数值字段做排序:


 select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 



从上面三条查询语句的执行时间来分析,使用索引字段排序和不排序花的时间差不多,比使用普通字段排序花的时间少一些,因此我们可以得出第四条结论:

针对在选择条件中的索引字段做排序操作,索引会起优化排序的作用。

5.索引维护

在前面我们可以看到所有的主键和索引都是排好序的,那么排序这件事情就需要销号资源,每次有新的数据插入,或者老的数据的数值发生变更,排序就需要调整,这里面是需要损耗性能的,下面我们分析一下。

自增型字段作为主键时,数据库对主键的维护成本非常低:
  1. 每次新增加的值都是一个最大的值,追加到最后即可,其他数据不需要挪动;
  2. 这种数据一般不做修改。



聚集索引索引维护.jpg

  
使用业务型字段作为主键时,主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。
 

聚集索引索引维护2.jpg

 
 
因为innodb主键和数据是在放在一块的,每次挪动主键,也需要挪动数据,维护的成本会比较搞,对于需要频繁写入的表,不建议使用业务字段作为主键的。
由于主键是所有索引的叶节点的值,也是数据排序的依据,如果主键的值被修改,那么需要修改所有相关索引,并且需要修改整个主键B+Tree的排序,损耗会非常大。避免频繁更新主键可以避免以上提到的问题。


 update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11 从上面的数据可以看出来,索然SQL语句只修改了一条数据,却影响了11个Page。
 


 

相对于主键而已,索引就轻很多,它的叶节点的值是主键,很轻,维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低,维护8个就不一定低了,这种事需要均衡地对待。

 
6.索引设计原则

索引其实是一把双刃剑,用好了事半功倍,没用好,事倍功半。

主键的字段无特殊情况,一定要使用数值类型的,排序时占用计算资源少,存储时占用空间也少。若主键的字段值很大,则整个数据表的各种索引也会变得没有效率,因为所有的索引的叶节点的值都是主键。

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

fio 测试时候 使用 混合模式 报错 fio: io scheduler noop not found

MySQLwubx 回复了问题 • 2 人关注 • 1 个回复 • 1209 次浏览 • 2015-12-30 11:29 • 来自相关话题

【求助】Memcached工作原理是啥?如何安装配置?

回复

Redis/memcachedhank zhang 发起了问题 • 1 人关注 • 0 个回复 • 707 次浏览 • 2015-12-30 01:09 • 来自相关话题

【求助】Redis cluster怎么安装配置?

回复

Redis/memcachedhank zhang 发起了问题 • 1 人关注 • 0 个回复 • 794 次浏览 • 2015-12-30 01:07 • 来自相关话题

MySQL 5.7 传统复制到GTID在线切换

MySQLwubx 发表了文章 • 2 个评论 • 919 次浏览 • 2015-12-29 10:13 • 来自相关话题

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:
     1. 要求MySQL 5.7.6及以后版本。
    2. 所有组中成员gtid_mode 为off状态。


实现步骤:
1. 所有的Server执行 
  set @@global.enforce_gtid_consistency = warn;
    
   特别注意: 这一步是关建的一步使用不能出现警告。

2.所有的server上执行:
    set @@global.enforce_gtid_consistency = on;

3.所有的Server上执行(不关心最先最后,但要执行完):
    set @@global.gtid_mode = off_permissive;

4. 执行:
    set @@global.gtid_mode=on_permissive;

    实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。


5. 确认传统的binlog复制完毕,该值为0
    show status like 'ongoing_anonymous_transaction_count';

  需要所有的节点都确认为0.

6.  所有节点进行判断 show status like 'ongoing_anonymous_transaction_count'; 为零
    所有的节点也可以执行一下: flush logs; 用于切换一下日志。

7.  所有的节点启用gtid_mode
    set @@global.gtid_mode=on

8.  把gtid_mode = on相关配置写入配置文件
    gtid_mode=on
    enforce_gtid_consistency=on

9. 启用Gtid的自动查找节点复制:
stop slave;
change master to master_auto_position=1;
start slave;

完毕。Good Luck。
     查看全部
作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:
     1. 要求MySQL 5.7.6及以后版本。
    2. 所有组中成员gtid_mode 为off状态。


实现步骤:
1. 所有的Server执行 
  set @@global.enforce_gtid_consistency = warn;
    
   特别注意: 这一步是关建的一步使用不能出现警告。

2.所有的server上执行:
    set @@global.enforce_gtid_consistency = on;

3.所有的Server上执行(不关心最先最后,但要执行完):
    set @@global.gtid_mode = off_permissive;

4. 执行:
    set @@global.gtid_mode=on_permissive;

    实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。


5. 确认传统的binlog复制完毕,该值为0
    show status like 'ongoing_anonymous_transaction_count';

  需要所有的节点都确认为0.

6.  所有节点进行判断 show status like 'ongoing_anonymous_transaction_count'; 为零
    所有的节点也可以执行一下: flush logs; 用于切换一下日志。

7.  所有的节点启用gtid_mode
    set @@global.gtid_mode=on

8.  把gtid_mode = on相关配置写入配置文件
    gtid_mode=on
    enforce_gtid_consistency=on

9. 启用Gtid的自动查找节点复制:
stop slave;
change master to master_auto_position=1;
start slave;

完毕。Good Luck。
    


performance_schema库被删除怎么办

MySQLwubx 回复了问题 • 2 人关注 • 3 个回复 • 628 次浏览 • 2015-12-24 19:44 • 来自相关话题

一个是执行语句突然报个lost,还有一个是#sql-ib928已存在

MySQLwubx 回复了问题 • 2 人关注 • 1 个回复 • 536 次浏览 • 2015-12-24 00:11 • 来自相关话题

3分钟搞定MySQL5.7安装

MySQLzhangdh 发表了文章 • 4 个评论 • 1071 次浏览 • 2015-12-22 19:49 • 来自相关话题

1.下载MySQL5.7二进制版本mkdir /opt/mysql
cd /opt/mysql
wget [url]http://downloads.mysql.com/arc ... ar.gz[/url]2.解压MySQL5.7到/opt/mysql下tar zxf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz3. 创建一个软连接,方便升级cd /usr/local ln -s /opt/mysql/mysql-5.7.9-linux-glibc2.5-x86_64 mysql4. 创建MySQl启动用户和组groupadd mysql
useradd -d /usr/local/mysql -g mysql -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql5. 创建基本目录mkdir -p /data/mysql/mysql3307/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql33076. 创建my.cnf配置文件(见附件)vim /data/mysql/mysql3307/my.cnf7. 初始化数据库
·注意MySQL5.7初始化已经融合到bin/mysqld命令中,通过“--initialize”来完成初始化,更多详细的命令,可以执行下面的命令来查看·/usr/local/mysql/bin/mysqld --verbose --help|less本例中数据库初始化命令如下(它将没有任何的输出,但是你可以从日志进行查看):/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --initialize8. 设置环境变量echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile9. 启动MySQL/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &10. 修改MySQL的root用户密码
初始完后需要用户自行更改root用户密码,否则执行“mysql -S /tmp/mysql3307.sock -u root -p”将报下面的错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这时候我们需要关闭MySQL数据库,执行下面的命令更改MySQL root的初始化密码:/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
mysql -S /tmp/mysql3307.sock

>update mysql.user set authentication_string=password('3wedu.net') where user='root' and host='localhost';
>flush privileges;
>\q关闭MySQL数据库,然后使用mysqld启动数据库/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &启动完毕后,如果直接执行“/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p”,将报下面的错误:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
这时候需要执行下面的命令对数据库进行密码更改/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p --connect-expired-password

>set password=password('3wedu.net');
>flush privileges; >\q更改完成后,就可以使用下面的命令直接登录了/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p11. 附件:my.cnf
#my.cnf
[client]
port            = 3307
socket          = /tmp/mysql3307.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = zhangdh
password   = zhangdh1113
log        = /data/mysql/mysql3307/logs/multi.log
#tcp-ip

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
port = 3307
socket = /tmp/mysql3307.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3307/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = row
server-id = 2003307
log-bin = /data/mysql/mysql3307/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#[mysqld3306]
#port            = 3306
#datadir = /data/mysql/mysql_3306/data
#socket = /tmp/mysql_3306.sock
#tmpdir=/data/mysql/mysql_3306/tmp
#server-id = 2143306
#log-bin = /data/mysql/mysql_3306/logs/mysql-bin

#[mysqld3316]
#port            = 3316
#datadir = /data/mysql/mysql_3316/data
#socket = /tmp/mysql_3316.sock
#tmpdir=/data/mysql/mysql_3316/tmp
#server-id = 2143316
#log-bin = /data/mysql/mysql_3316/logs/mysql-bin

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535 查看全部
1.下载MySQL5.7二进制版本
mkdir /opt/mysql
cd /opt/mysql
wget [url]http://downloads.mysql.com/arc ... ar.gz[/url]
2.解压MySQL5.7到/opt/mysql下
tar zxf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
3. 创建一个软连接,方便升级
cd /usr/local ln -s /opt/mysql/mysql-5.7.9-linux-glibc2.5-x86_64 mysql
4. 创建MySQl启动用户和组
groupadd mysql
useradd -d /usr/local/mysql -g mysql -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql
5. 创建基本目录
mkdir -p /data/mysql/mysql3307/{data,logs,tmp} 
chown -R mysql:mysql /data/mysql/mysql3307
6. 创建my.cnf配置文件(见附件)
vim /data/mysql/mysql3307/my.cnf
7. 初始化数据库
·注意MySQL5.7初始化已经融合到bin/mysqld命令中,通过“--initialize”来完成初始化,更多详细的命令,可以执行下面的命令来查看·
/usr/local/mysql/bin/mysqld --verbose --help|less
本例中数据库初始化命令如下(它将没有任何的输出,但是你可以从日志进行查看):
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --initialize
8. 设置环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
9. 启动MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &
10. 修改MySQL的root用户密码
初始完后需要用户自行更改root用户密码,否则执行“mysql -S /tmp/mysql3307.sock -u root -p”将报下面的错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这时候我们需要关闭MySQL数据库,执行下面的命令更改MySQL root的初始化密码:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
mysql -S /tmp/mysql3307.sock

>update mysql.user set authentication_string=password('3wedu.net') where user='root' and host='localhost';
>flush privileges;
>\q
关闭MySQL数据库,然后使用mysqld启动数据库
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &
启动完毕后,如果直接执行“/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p”,将报下面的错误:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
这时候需要执行下面的命令对数据库进行密码更改
/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p --connect-expired-password

>set password=password('3wedu.net');
>flush privileges; >\q
更改完成后,就可以使用下面的命令直接登录了
/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p
11. 附件:my.cnf
#my.cnf
[client]
port            = 3307
socket          = /tmp/mysql3307.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = zhangdh
password   = zhangdh1113
log        = /data/mysql/mysql3307/logs/multi.log
#tcp-ip

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
port = 3307
socket = /tmp/mysql3307.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3307/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = row
server-id = 2003307
log-bin = /data/mysql/mysql3307/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#[mysqld3306]
#port            = 3306
#datadir = /data/mysql/mysql_3306/data
#socket = /tmp/mysql_3306.sock
#tmpdir=/data/mysql/mysql_3306/tmp
#server-id = 2143306
#log-bin = /data/mysql/mysql_3306/logs/mysql-bin

#[mysqld3316]
#port            = 3316
#datadir = /data/mysql/mysql_3316/data
#socket = /tmp/mysql_3316.sock
#tmpdir=/data/mysql/mysql_3316/tmp
#server-id = 2143316
#log-bin = /data/mysql/mysql_3316/logs/mysql-bin

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535