数据库调优
MySQL 调优分析 explain；show status 查看服务器状态信息
SQL 语句 的优化
分析：确认程序是否存在查询不需要的记录；mysql 是否在扫描额外记录
-
查询不需要的记录：使用 select 语句查询大量结果，然后再获取前 N 行（如新闻网站，取 100 条记录，只显示前面的 10 条），这时可以使用 limit（limit 1，10；从 1 开始 10 行）
-
总是使用 SELECT *，对 I/O、内存消耗较大，不必要时不要这样。
-
子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);一种简单的优化就是用 inner join 的方法来代替子查询，查询语句改为：
Select* from A inner join B using(uuid) where b.uuid>=3000; -
尽量少排序，排序操作会消耗较多的 CPU 资源（可以使用索引）
-
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3 -
切分查询，将大查询切分成小查询，每个查询功能一样，只完成一小部分，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、耗尽系统资源、阻塞很多小的但重要的查询。
索引的优化
- 建立索引加快查询性能，优先在经常搜索的字段上建立索引（where）；WHERE 子句的查询条件里使用了比较操作符 LIKE 前置通配符%（如：LIKE “%ABC”），因为‘%’代表任何字符，%xxx 不知道怎么去索引的，所以使用不了索引。只要列中包含有 NULL 值都将不会被包含在索引中（复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。）
- 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20;
可以这样查询：
select id from t where num=10;
union all
select id from t where num=20
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。不是同一个表也可以union,，条件是只要列数相同，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。UNION ALL 允许重复
-
in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3) -
索引列不能是表达式的一部分，也不能是函数的参数。下列索引列在表达式中因此使用全表扫描
select * from user where age + 1 =20; -
应尽量避免在 where 子句中使用
!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描 -
在多个列上建立独立的单列索引大部分情况下不能提高查询性能，可以使用复合索引（多列索引），key(name,age,sex)
多列索引有一个特点，即最左前缀（Leftmost Prefixing）（索引必须从最左边开始比对）。假如有一个多列索引为key(firstname，lastname ，age)，当搜索条件是以下各种列的组合和顺序时，MySQL 将使用该多列索引：
firstname，lastname，age
firstname，lastname
firstname
lastname，firstname（应该也可以，有优化器的存在）也就是说，相当于还建立了 key(firstname lastname)和 key(firstname)。但是只搜 lastname用不到索引
-
由于复合索引最左前缀匹配，将搜索次数多的列放到最前列（建立 B+树时从最左字段开始排序，第一个字段相同才排第二个字段，因为查找树需要一定的顺序）
-
尽量选择小而简单的数据类型做索引，减少磁盘空间
-
有时候需要索引很长的字符列，这会让索引变得大且慢。一个方式是使用哈希索引，另一个是使用前缀索引，即索引开始的部分字符串，这样可以节约索引空间，提高效率。但这样会降低索引的选择性（不重复的索引值/记录总数）。索引的选择性越高则查询效率越高，唯一索引的选择性是 1，性能是最好的。一般情况，某个前缀的选择性也是足够高的
数据库表结构的优化（第四章）
- 选择合适的数据类型
数据类型越小越好：尽量使用可以正确存储数据的最小数据类型（tinyint，占用更少的磁盘、内存、cpu 缓存）；tinyint 占 1 字节，int 占 4 字节 - 简单就好：如整型比字符操作代价更低（用整型存储 ip 地址）
- 避免 NULL：最好指定列为 NOT NULL，因为 NULL 更难优化，使用的索引更复杂
- 不要设计太多列：减少不必要的列
- 适度冗余，减少 join 关联查询
- 适当拆分，水平拆分、垂直拆分
- 选择合适的字符编码：如果我们可以确定不需要存放多种语言，就没必要非得使用 UTF8 或者其他 UNICODE 字符类型，这回造成大量的存储空间浪费
MySQL 配置文件 优化
mysql 的配置文件名为 my.cnf（window 为 my.ini），不同情况下配置文件参数设置也不相同，应该根据具体场景调优。
InnoDB 中最重要的选项是：
- innodb_buffer_pool_size：缓存用户表（实际数据 row）及索引数据的最主要缓存空间，对 Innodb 整体性能影响也最大，默认为 8MB，建议设为内存的 70%~80%（MyISAM 只缓存索引）
- innodb_log_file_size：日志文件大小，默认为 48MB，应该调大，至少有几百 MB
硬件的优化
CPU 的选择
最好的选择是核心数多并且主频高的。但是有时考虑成本问题，可以参考以下情况：
- 如果不是密集型的查询，优先选频率高的，而不是数量多的
- 如果是密集型的、高并发的查询，比如秒杀等活动，优先选更多的 cpu。因为一条 sql 语句只能在一个 cpu 上执行
内存的选择
内存并不是容量越大，性能提升越明显。如果内存大小已经超过了总数据量的大小，那么即使再增加内存，系能提升也不会特别明显。
内存频率选择 cpu 支持的最高的频率，品牌、型号、规格等要一致。
磁盘配置和选择
各种磁盘性能比较：
PCIe > SSD > Raid10 > 磁盘 > 网络存储
各种磁盘的特点和应用：
- 传统硬盘：需要考虑存储容量、传输速度、访问时间、主轴转速、物理尺寸等参数
- raid 增加传统硬盘性能：主服务器建议用 raid10，从服务器可以 raid0（raid0：数据等量放置在 2 块磁盘中，raid1：让同一份数据完整保存在两块磁盘）
- SSD 或者 PCIe 卡（FusionIO）：缺点比传统硬盘更容易坏
- SSD 应用场景：适用于存在大量随机 I/O 场景（SSD 随机 I/O 快）、适用于解决单线程负载的 I/O 瓶颈（用在从服务器上，适用于读的场景，频繁写会减少使用寿命）
- 网络存储场景（NAS、SAN）：数据库备份
操作系统
合理配置操作系统参数，选择合适的文件系统
加缓存 redis
先读写分离，再垂直拆分，再水平拆分
分库：根据业务逻辑垂直拆分
分表：
- 纵向分表（常见为忙闲分表）
- 横向切表
- 等分切表，如哈希切表或其他基于对某数字取余的切表（USERID 奇数、偶数）。等分切表的优点是负 载很方便的分布到不同服务器；缺点是当容量继续增加时无法方便的扩容，需要重新进行数据的切分或转表。而且一些关键主键不易处理。
- 递增切表，比如每 1kw 用户开一个新表，优点是可以适应数据的自增趋势； 缺点是往往新数据负载高，压力分配不平均。
扫一扫
846
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
