只是一些 tips, 用于记忆和参照使用.
万事不是绝对,只适用与大多数场合.
3.1 数据类型
数据类型越小越好:
比如 char 对比 varchar, 使用了更少的磁盘空间、内存和CPU缓存.
越简单越好
int 比 字符串 好,datatime 比 字符串好, 整数比 ip地址字符串好.
尽量避免 NULL
可空查询会把索引变得更复杂.
timestamp 使用的空间 只有 datetime 的一半,
3.1.1 整数
1. 选择合适的位数长度.
2. 可选属性 unsigned.
3.1.2 实数
float 和 double 对于 decimal 来说,更小,精准度更高,范围更大,但运算时,这两个会造成丢失精度.
3.1.3 字符串
1. varchar 会清除末尾空格, char 不会.
2. varchar 需要使用1或2个字节来存储实际长度. 当 <= 255 时,用1个字节.
3. varchar(n) n 不同时,虽然保存相同的字符串占用的空间一样, 但 n 越小消耗的内存越小.
4. blob,text 类型会按照 max_sort_length 前若干个字节进行排序.
5. enum 代替字符串,用法等同于 java, 实际也是数字.可以
select e+0,e from enum_test
来查看具体的数值.
3.1.4 日期和时间
1. mysql 最细精度为秒.
2. datetime 实际是 YYYYMMDDHHMMSS 整数, 与时区无关.
3. timestamp 只能表示从 1970 - 2038, 只占用 4 字节.
4. from_unixtime() 和 unix_timestamp()
5. timestamp 依赖时区, MySQL 服务器 ,操作系统,客户端连接都有时区设置.
3.1.6 标识符
1. 最好使用整数做标识符.
2. 字符串做标识符会比较慢, 在压缩的 MyISAM 引擎下要慢6倍.
3. 性能上 int > uuid > hashcode
3.1.7 特殊数据
使用无符号整数保存 ip地址 , inet_aton() inet_ntoa()
3.2 索引基础
1. column 的类型因为 Hibernate 等 ORM 框架使得对开发人员透明,推荐不要自动生成表,要写script .
2. Column 创建的顺序也很重要.
3. 索引多列数据,MySQL 只能搜索索引最左前缀.
3.2.1 索引类型
1. B-Tree 索引. 只能全索引或索引最左前缀.
2. Hash 索引. 需要自己维护 Hash code 列. 可以有个 trigger 来 insert 和 update hash 索引列.
3. 空间索引 R-Tree, MyISAM 支持 R-Tree.
4. 全文索引.
3.3 索引策略
3.3.1 隔离列
"隔离"列意味着索引列不是表达式的一部分,也没有位于函数中.
3.3.2 前缀索引
如果一列数据太长,用hash索引未必是最好的选择.
可以使用前缀索引. 及取前 N 个长度的字符作为索引.
N 的选择原则1:
-- 全列的选择性
select count(distinct city)/count(*) from sakila.city;
-- N长度前缀的选择性
select count(distinct left(city,N))/count(*) from sakila.city;
-- create index sql
alter table sakila.city add key (city(7))
3.4 索引研究
3.4.1 多种过滤条件
in 语句可以引用索引.
一个索引从左开始,直至第一个范围查询,比如: ">" 和 "<" 这类,还有 between.
为了最左前缀匹配,可以把 sex 这种放在索引最左边,即使没有根据sex 去查询,也可以把 sex in ('male','female') 放在 sql 里.
应该把范围条件放在索引的最右边,比如 age.
大量的in组合也会有问题, 比如 a in (1,2,3) and b in (4,5,6) and c in (7,8,9)
那么就认为这是 3 * 3 * 3 种组合.如果上千,那么会非常慢.
3.4.2 避免多个范围条件
避免这样的查询
select * from customer where age > ? and salary > ?
无法为两个范围条件使用索引,这种情况可以根据业务逻辑将其中一列转换为可以用 in 查找,
比如 salary 大于 1w 小于 2w 的会有另外一列保存为 1, 可以创建一个 (salary_mapping_column,age) 这样的索引.
3.4.3 优化排序
1. 为低选择性列排序加索引, 例如 (sex,rating).可以优化下面这个 SQL, 有 order by 和 limit 没有索引会很慢.
select * from profiles where sex='M' order by rating limit 10
2. 即使有索引,这样也会很慢
select * from profiles where sex='M' order by rating limit 10000,10
这样的 SQL 可以优化为:
select * from profiles inner join(
select pk from profiles where x.sex ='M' order by rating limit 100000, 10
) as x using(pk)
3.7 加速 alter table
3.7.1 只修改 .frm
移除列的 auto_increment
添加\移除或更改 ENUM 和 SET 常量.
3.8 对存储引擎说明
3.8.1 MyISAM
表锁, 不支持自动数据恢复, 只有索引被缓存在内存中, 紧密存储.
3.8.2 Memory
表锁, 不支持动态行,哈希索引是默认索引类型, 没有索引统计, 重启后丢失数据.
3.8.3 InnoDB
事务性, 外键, 行级锁, 多版本, 按主键聚集, 所有索引包含主键列, 优化缓存, 未压缩的索引, 数据装载缓慢, 阻塞 AUTO_INCREMENT, 没有缓存 count(*)
分享到:
相关推荐
第3章:架构优化和索引 80 第4章:查询性能优化 152 第5章:MySQL高级特性 204 第6章:优化服务器设置 265 第7章:操作系统和硬件优化 305 第8章:复制 343 第9章:伸缩性与高可用性 409 第10章:应用层面的优化 457...
《高性能MySQL》是数据库管理员、开发人员和系统架构师必备的一本经典著作,特别是对于处理大规模数据和高并发场景的企业来说,这本书提供了丰富的知识和实践经验。该书的第三版不仅涵盖了MySQL的基础操作,还深入...
总结,MySQL性能优化与架构设计涵盖了许多方面,包括查询优化、索引策略、数据库设计、缓存利用、并行处理、架构设计、数据分布以及监控与调优工具的使用。理解和掌握这些知识点,能够帮助我们构建高效、稳定的...
第1章 mysql 架构与历史 1 第2章 mysql 基准测试 35 第3章 服务器性能剖析 67 第4章 schema 与数据类型优化 111 第5章 创建高性能的索引 141 第6章 查询性能优化 195 第7章 mysql 高级特性 259 第8章 优化服务器设置...
《高性能MySQL(第3版)》是一本由Baron Scbwartz等作者编著的经典数据库技术书籍,专注于探讨如何在实际应用中优化和管理MySQL数据库系统以实现高性能。这本书是MySQL管理员、开发人员和DBA的必备参考资料,涵盖了...
- 特性:开源、免费、跨平台、高性能、易于使用和扩展。 - 架构:MySQL采用客户端-服务器模型,包括数据库服务器、客户端应用和网络通信层。 2. MySQL安装与配置: - 安装过程:介绍Windows、Linux和Mac OS等...
其中提到的《高性能MySQL(第2版)》是一本专注于MySQL数据库优化和管理的专业书籍。下面将针对该书籍以及MySQL相关的高性能知识点进行详细阐述。 ### 1. MySQL概述 MySQL是一种广泛使用的开源关系型数据库管理...
[[高性能MySQL(第3版)].Baron.Scbwartz等.扫描版[电子书.pdf,主要内容包括:第一章mysql架构与历史;第二章mysql基准与测试;第三章服务器性能剖析;第四章schema与数据类型优化;第五章创建高性能索引;第六章...
### 高性能MySQL第三版知识点概述 #### 一、MySQL基础知识与优化 1. **数据库设计原则**:书中首先介绍了数据库设计的基本原则,包括规范化理论的应用、如何避免数据冗余等,这对于构建高效稳定的数据库至关重要。...
第5章创建高性能的索引141 5.1索引基础141 5.1.1索引的类型142 5.2索引的优点152 5.3高性能的索引策略153 5.3.1独立的列153 5.3.2前缀索引和索引选择性153 5.3.3多列索引157 5.3.4选择合适的索引列顺序159 ...
第5章 创建高性能的索引 141 第6章 查询性能优化 195 第7章 mysql 高级特性 259 第8章 优化服务器设置 325 第9章 操作系统和硬件优化 377 第10章 复制 433 第11章 可扩展的mysql 501 第12章 高可用性 543 第13章 ...
第3章:MySQL硬件优化与监控 第4章:MySQL基准测试 第5章:MySQL Schema设计与索引 第6章:SQL优化与高级特性 第7章:MySQL备份 第8章:MySQL备份与恢复深入 第9章:MySQL的复制 第10章:MySQL高可用及可扩展架构 第...
【MySQL面试题】在面试MySQL相关的职位时,面试官可能会问到一系列关于数据库基础、SQL语法、事务处理、索引优化以及性能调优的问题。以下是一些可能的面试重点: 1. **数据库基本概念**: - 数据库是用于存储和...
《高性能MySQL》是数据库管理员、开发人员和系统架构师必备的一本经典著作,它深入探讨了MySQL的各个方面,包括性能优化、高可用性、备份、恢复、复制和安全性等。这本书的第三版更是包含了最新的MySQL技术和最佳...
《高性能MySQL》是一本深入探讨MySQL数据库系统优化与管理的经典著作。这本书涵盖了MySQL的架构、历史、性能调优以及基准测试等多个重要主题,对于数据库管理员、开发人员以及对数据库性能有高要求的技术人员来说,...
### 高性能MySQL第三版知识点概述 #### 一、书籍简介 《高性能MySQL》第三版是一本关于MySQL数据库优化的经典著作。本书不仅深入探讨了MySQL的内部机制与使用技巧,还涉及了大量的性能优化方法和实战案例,对于提高...
- 第三方工具:如HAProxy和MaxScale,提供更高级别的负载均衡和故障切换功能。 4. **性能优化** - 查询优化:索引设计、查询语句优化、存储引擎选择等,提高查询效率。 - 缓存策略:如使用Memcached或Redis缓存...
《高性能MySQL(第三版)》是一本专门针对MySQL数据库系统优化与调优的权威参考书籍。本书由Baron Schwartz、Peter Zaitsev和Vadim Tkachenko三位数据库领域的专家共同撰写。第三版在前两版的基础上进行了全面的更新...