高性能mysql读书笔记之二–架构优化和索引
2010年05月5日 发表评论
第三章 架构优化和索引
第三章的主要是说合理使用不同的数据类型和索引。主要需要注意的内容有如下：
1. 通用原则
1.1. 数据类型更小通常更好。
1.2. 数据类型越简单越好
1.3. 尽量避免使用NULL,要是有必要用NULL，那也可考虑使用0来进行代替。 创建表的时候定义好not null default 0。
1.4. DATETIME和TIMESTAMP都能保持同样数据类型：日期和时间，精度为妙。而且TIMESTAMP使用的空间只是DATETIME的一半，TIMESTAMP使用4个字节，DATETIME使用8个字节。而且TIMESTAMP还能保存时区，拥有特殊的自动更新能力，但是TIMESTAMP的范围要比DATETIME要小的多。TIMESTAMP类型只能保存1970年1月1日零点到2038年。而DATETIME却能保存1001年到9999年。
2. VARCHAR和CHAR
2.1. 大家都知道VARCHAR是可变长度的，而CHAR是定长的。
2.2. 使用VARCHAR(5)和VARCHAR（200）保存’hello’占用的空间都是一样的。但是VARCHAR(5)只会使用较小的内存空间，因为MySQL通常会分配固定大小的内存块来保存值。
3. 索引对于查询的性能影响是非常大的。下面先介绍下索引类型。
3.1. B-TREE索引，大部分MySQL存储引擎都支持B-TREE。除了ARCHIVE直到5.1才支持。
3.1.1. 存储引擎使用了不同的方式把索引保存在磁盘上，它们会影响一定的性能。例如MyISAM使用前缀压缩以减少索引，而InnoDB不会压缩索引，因为它不能把压缩索引用于某些优化。
3.1.2. B-TREE通常意味着存储是有序的。
3.1.3. B-TREE索引能够很好的用户全键值，键值范围或者键前缀进行查找（最左前缀）。
3.1.3.1. 匹配全名，如 where name=’timo’
3.1.3.2. 匹配最左前缀， 如 where name like ‘tim%’
3.1.3.3. 匹配范围值，如 where name between ‘tim’ and ‘timo’
3.1.3.4. 精确匹配一部分并且匹配某个范围内的另一部分，如 where name=’timo’ and age between 25 and 30
3.1.3.5. B-TREE索引通常能支持至访问索引的查询，它不会访问数据行
3.1.4. B-TREE索引的局限性
3.1.4.1. 假设有如下的索引 key(first_name, last_name, age)
3.1.4.2. 如果查找没有从索引的最左边开始，它就没有什么用处。比如where first_name like ‘%mo’ 这样的查找是不走索引的。
3.1.4.3. 不能跳过索引中的列, 如查找 where first_name=’timo’ and age = 25, 如果建立的是上面这样的联合索引，又跨了last_name，那就不会走索引了。
3.1.4.4. 存储引擎不能优化访问任何在一个范围条件右边的列。如查找
where first_name=’timo’ and last_name like ‘s%’ and age=25。访问就只能使用索引的前2列，因为like是范围条件。
3.1.4.5. 一些局限并不是B-TREE固有的，而是MySQL查询优化器和存储引擎使用索引的方式造成的。
3.2. 哈希索引(hash index)
3.2.1. 它值对使用索引中的每一列的精确查找有用。所以很少用，在MySQL中是有Memory存储引擎支持显式的哈希索引。
3.2.2. 由于hash index是给每个键值建立一个哈希表，所以它的查找速度是非常快的，但是也会有很多局限性。
3.2.2.1. 因为索引只包含了哈希码和行指针，而不是指本身，MySQL不能使用索引中的值来避免读取行。
3.2.2.2. 不能进行排序
3.2.2.3. 不支持部分键匹配
3.2.2.4. 只支持使用 =, in() 和<=>的相等比较。
3.2.2.5. 发生碰撞的时候存储引擎必须访问链表中的每个指针，然后逐行进行数据比较，以确定正确的数据。
3.2.2.6. 如果有很多碰撞，一些索引维护操作就会很慢。
3.3. 空间（R-TREE）索引
3.3.1. 只有MyISAM支持，可以使用GEOMETRY这样的地理空间数据类型，必须使用MySQL GIS函数进行查找。
3.4. 全文索引
3.4.1. 全文索引只有MyISAM支持。是从文本中直接找关键字，而不是从索引中进行比较。全文索引用户MATCH AGAINST操作。
3.5. 高性能索引策略
3.5.1. 隔离列
3.5.1.1. 下面2条语句是不会使用索引的
1 |
where actor_id + 1 = 5
|
2 |
|
3 |
where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10
|
3.5.1.2. 下面2条是针对上面2条进行修改使用索引的
1 |
where actor_id = 4
|
2 |
|
3 |
where date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY)
|
3.5.2. 前缀索引和索引选择性
3.5.2.1. 当某一列特别长的情况下，如果给全部长度建索引，那样会增加索引的大小，而只做很短的前缀索引，虽然节约了空间，但是会增加选择性。所以建前缀索引必须让选择性接近于全部长度的选择性。
3.5.2.2. 平均来说前缀的选择性能接近于0.31就可以了。
1 |
select count(distinct city)/count(*) from db_name.table_name; select count(distinct left(city, 3))/count(*) AS sel3, count(distinct left(city, 4))/count(*) AS sel4 from db_name.table_name;
|
3.5.2.3. 只看平均选择率在特殊情况是不够的，比如在数据分布非常不均的情况下。
3.5.2.4. Alter table db_name.table_name add KEY (city(7)) 这句就是只对city这一列的前7个字母进行索引。
3.5.3. 聚集索引（clustered indexes）
3.5.3.1. 在InnoDB中聚集索引实际上在同样的结构中保存了B-TREE索引和数据行。聚集的含义就是指实际的数据行和相关的键值保存在一起。每个表只能有一个聚集索引，因为不可能一次把行保存在两个地方
3.5.3.2. 在MySQL中只有SolidDB和InnoDB是支持聚集索引的。
3.5.3.3. InnoDB是按照主键（Primary Key）列进行聚集。如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。
3.5.3.4. 聚集索引有助于性能，但是也会导致严重的性能问题。总的来说它有如下的优点：由于把索引和数据都保存在一棵B-TREE中，因此查找数据会比通常的要快。
3.5.3.5. 聚集索引也有如下的缺点：会导致I/O密集，插入速度慢，更新索引列慢，插入新行会进行分页，这样导致占用更多的磁盘空间。第二索引会比预想的大，第二索引访问需要两次索引查找。
3.5.3.6. 在InnoDB中是根据主键来进行顺序插入的（这个跟InnoDB的数据布局有关），所以主键最好是一个自增的值，与应用程序无关。
3.5.4. 覆盖索引（covering indexes）
3.5.4.1. 包含（或者覆盖）了所有满足查询需要的数据的索引叫覆盖索引。
3.5.4.2. 索引记录通常远小于全行大小，因此只读取索引就能极大的减少数据访问量（这个跟聚集索引的优点一样）
3.5.4.3. 索引是按照索引值进行排序的。
3.5.4.4. 大部分存储引擎缓存索引比缓存数据更好（除了Falcon）。
3.5.4.5. 对于InnoDB覆盖了查询的第二索引在主键中避免了另外一次索引查找。
3.5.4.6. 覆盖索引必须保存它包含列的数据。
3.5.4.7. 当发起一个索引覆盖的查询，用EXPLAIN会在extra列显示Using Index
3.5.5. 为排序使用索引扫描
3.5.5.1. MySQL有两种产生排序结果的方式，使用文件排序（Filesort）和扫描有序的索引。如果EXPLAIN的输入type列的值是index。那说明MySQL会扫描索引。
3.5.5.2. 只有当索引的顺序和order by字句中的顺序完全一致，并且所有列排序的方向（升序或降序）一样才可以。如果查询连接多个表，只有在order by 字句的所有列引用的是第一个表才可以。
3.5.5.3. 假设有如下表：
01 |
CREATE TABLE rental (
|
02 |
|
03 |
………… |
04 |
|
05 |
PRIMARY KEY (rental_id),
|
06 |
|
07 |
UNIQUE KEY rental_date (rental_date, inventory_id, customer_id),
|
08 |
|
09 |
KEY idx_fx_inventory_id (inventory_id),
|
10 |
|
11 |
KEY idx_fx_customer_id (customer_id),
|
12 |
|
13 |
KEY idx_fx_staff_id (staff_id),
|
14 |
|
15 |
………… |
16 |
|
17 |
) |
3.5.5.3.1. 下面的这几个语句是使用到索引的。
1 |
WHERE rental_date='2010-05-02' ORDER BY inventory_id desc;
|
2 |
|
3 |
WHERE rental_date > '2010-05-02' ORDER BY rental_date, inventory_id; 在where字句是范围的时候需要用最左前缀索引进行排序。
|
3.5.5.3.2. 而下面的都是不能使用索引进行排序的。
1 |
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
|
2 |
|
3 |
... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
|
4 |
|
5 |
... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id; 原因是第一句里用到staff_id不在索引的列中，第二句是没能形成索引的最左前缀，第三句是由于在索引的第一列有范围条件那MySQL就不会使用余下的索引。还有多个where等于条件也不会使用索引排序。
|
3.5.6. 压缩（前缀压缩）索引
3.5.6.1. 这个只在MyISAM存储引擎才有效
3.5.6.2. 压缩完成会块的占用空间会较小，但是某些操作会变慢。排序如果是顺序没有问题，但是倒序会非常的慢。
3.5.6.3. CPU的负载会比平常多几倍。
3.5.6.4. 压缩后索引大小也许只有原来的1/10
3.5.6.5. 可以在使用CREATE TABLE命令的时候用PACK_KEYS来控制索引压缩的方式。
3.5.7. 多余和重复索引
3.5.7.1. 以前我一直觉得多建索引只是多占用系统的磁盘大小，而看了这里感觉自己错了，还是需要注意不能随便建索引。
3.5.7.2. MySQL本身不会提醒你创建了重复的索引。这样MySQL不得不单独维护每一个索引，并且查询优化器在优化查询的时候会逐个考虑它们，这样就会严重影响性能。
3.5.7.3. 看下面这个例子： CREATE TABLE test (ID INT NOT NULL PRIMARY KEY, UNIQUE(ID) INDEX(ID)); 我自己以前也有过这样的写法，其实这样对于ID这一列创建了3个相同的索引。事实上MySQL利用索引实现了UNIQUE约束和PRIMARY KEY约束。所以通常不需要这样做，除非你要在同一列上有不同的索引满足不同类型的查询（比如KEY和FULLTEXT KEY）
3.5.7.4. 多余索引。有几个例子，比如建立了（A,B）2列这样的索引，那就没有必要建（A）索引了，但是还是有必要建立（B）索引。同样建立了(A,B)如果有需要还是可以建立（B,A）索引的。
3.5.7.5. 同时在大部分情况下，为了避免它，应该扩展索已有索引，而不是添加新索引。
3.5.8. 索引和锁定（Indexes and Locking）
3.5.8.1. 索引可以让查询锁定更少的行，因为在InnoDB只有在事务提交后才会给行解锁。
3.5.8.2. InnoDB进行行锁定还是有一些开销的，锁定超过需要的行会增加锁竞争和减少并发。
3.5.8.3. 下面是一个例子
1 |
Set autocommit=0
|
2 |
|
3 |
SELECT actor_id FROM table_name WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;
|
该查询实际以独占的方式锁定了1到4行，InnoDB锁定第一行的原因是“从索引的开头开始，并且提取所有行直到 actor_id < 5不成立”。如果没有索引，MySQL不管是否需要行，都会进行全表扫描并且锁定每一行（MySQL5.1中 READ COMMITTED事务隔离级别也有这个问题）。
InnoDB能在第二索引上放置共享（读）锁，但是独占（写）锁要求访问主键。这消除了使用覆盖索引的可能性，并且能导致SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定要慢很多。
3.5.9. 索引策略总结
3.5.9.1. 为任何耗时很长的查询添加索引。
3.5.9.2. 在任何可能的地方，都要试着扩展索引，而不是新增索引。如果不知道查询的分布，就要尽可能地使索引变得更有选择性。
3.6. 索引实例研究
3.6.1. 这个主要是一个婚恋网站的维护，比较简单。
3.7. 索引和表维护
3.7.1. 维护的目的是为了减少碎片
3.7.2. 查找并修复表损坏
3.7.2.1. 这个一般MyISAM引擎会由于服务器崩溃导致表损坏，可以使用myisamchk来进行修复，修复的方法有很多。至于InnoDB我自己是从来没有碰到过表损坏的问题出现。
3.7.3. 更新索引统计
3.7.3.1. MySQL查询优化器在决定如何使用索引的时候会调用两个API，以了解索引如何分布，一个是调用records_in_range(),它接受范围结束点并且返回范围内记录的数量。第二个info(),它返回不同类型的数据。
3.7.3.2. 查询优化器的开销指标是查询会访问多少数据。如果统计永远没有产生，或者过时了，优化器就会做出不好的决定。可以运行ANALYZE TABLE来解决这个问题。
3.7.3.3. InnoDB是在第一次打开表的时候利用随机索引进行估计。InnoDB上的ANALYZE TABLE命令就使用了随机索引，同样，ANALYZE TABLE在InnoDB不是阻塞性的，并且开销也不大，因此可以在不大影响服务器的情况下在线更新统计。
3.7.3.4. 我们可以使用SHOW INDEX FROM table_name命令来检查索引的基数性。需要特别注意Cardinality列，它显示了存储引擎估计的索引中唯一值的数量。在MySQL5.0以及以上版本可以通过INFORMATION_SCHEMA.STATISTICS表来得到这些数据。
3.7.4. 减少索引和数据碎片
3.7.4.1. B-TREE索引会有它的形成机制，故会形成碎片并降低了性能。
3.7.4.2. 表的数据存储也能变的碎片化。有两种碎片分别为
3.7.4.2.1. 行碎片（row fragmentation）:当行存储在多个地方的多个片段中就会产生这种碎片。
3.7.4.2.2. 内部行碎片（intra-row fragmentation）：当逻辑上顺序的页面或行在磁盘上没有被顺序的存储时就会产生这种碎片。它影响了全表扫描和聚集索引这样的操作。
3.7.4.3. 为了消除数据碎片可以运行OPTIMIZE TABLE来转储或者重新加载数据。对于不支持OPTIMIZE TABLE的存储引擎可以用ALTER TABLE重新建立表。
3.8. 范式化和非范式化（Normalization and Denormalization）
3.8.1. 这个属于数据库设计的范畴，第一范式，第二范式，第三范式，BCNF
3.8.2. 范式化架构的利弊：这个基本上就是BCNF的好处，当写入负载大的时候，使用范式化架构师比较好的。它的优缺点如下：
3.8.2.1. 范式化更新比非范式化更新快
3.8.2.2. 当数据被很少的范式化后，就很少或者非常少的重复数据，因此改动的数据会变少
3.8.2.3. 范式化表通常较小，容易被装载到内存中并且性能更好
3.8.2.4. 由于缺少冗余数据，在取得数据的时候会较少采用DISTINCT或者GROUP BY，这样有时候要查数据会连接多个表。这样就非常难建立索引策略。
3.8.3. 非范式化架构的利弊：非范式化架构由于所有数据都在一个表里面，避免了连接，所以性能不错。由于不需要连接表，对于大多数查询，甚至不使用索引，这会比连接快的多，因为它避免了随机I/0。所以非范式化架构用在查询非常多的情况是非常好的。
3.8.4. 结合范式化和非范式化：在真实环境中很少会全部范式化和非范式化，通常都是结合者2中方案的，也许是采用部分范式化，缓存表以及其它技巧。
3.8.4.1. 非范式化最常见的技巧是复制，缓存，把一个表中部分列选到另外一个表中，在MySQL5.0以及以上版本，可以使用触发器来更新缓存的值。
3.8.5. 缓存和汇总表
3.8.5.1. 有时需构造完全不同的汇总或者缓存表，为获取数据进行特别的调优。
3.8.5.2. 缓存表的含义就是能够容易的获得数据，如果这样更慢的话就会通过scheme来获取数据。缓存表对于优化搜索和获取数据的查询是有用的。
3.8.5.3. 汇总表的意思就是我们可以通过GROUP BY来汇总查询数据。
3.8.5.4. 当使用缓存和汇总表的时候，你不得不决定是否要进行实施数据维护或周期性重建。不过可以通过使用影子表来解决这个问题。
1 |
DROP TABLE IF EXISTS my_summary_new, my_summary_old;
|
2 |
|
3 |
CREATE TABLE my_summary_new LIKE my_summary;
|
4 |
|
5 |
RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;
|
3.9. 加速ALTER TABLE
3.9.1. 这个是有技巧的，不是所有的ALTER TABLE操作都会导致重建表。
下面这个例子是想把这一列默认值变为5，但是第一句它实际上会执行1000次读取和1000次插入，这样就会非常慢。而第二句只修改默认值，列的默认值实际保存在表的.frm文件中，所以可以不不接触表而更改它。任何MODIFY COLUMN操作都会导致表重建。
1 |
ALTER TABLE table_name MODIFY COLUMN column_name TINYINT(3) NOT NULL DEFAULT 5;
|
2 |
|
3 |
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 5；
|
3.9.2. 如果愿意承担一定风险，可以让MySQL做其它类型的修改时也不重建表。
3.9.2.1. 以下操作可以不重建表：移除（但不是添加）列的AUTO_INCREMENT属性
3.9.2.2. 添加，移除或更改ENUM和SET常量。
3.9.2.3. 官方说这是不受支持的技巧，使用后果自负。
2010-05-05
相关推荐
`来查看当前MySQL服务器的版本信息。 2. **如何优化慢查询？** 首先，使用slow_query_log参数开启慢查询日志记录，然后根据日志分析慢查询的原因，通常涉及查询语句优化、索引调整或表结构优化。 3. **如何解决高...
MySQL是世界上最受欢迎的关系型数据库管理系统（RDBMS）之一，尤其在...以上是对大牛讲解的MySQL介绍及性能优化PPT的主要内容概述，具体细节和实例将在PPT中详细展开，帮助读者全面掌握MySQL的核心知识和优化技巧。
保持所有组件（操作系统、Apache、MySQL、PHP）的最新状态，确保安全性和性能优化。 通过上述优化策略，可以有效地提升LAMP服务器的性能，应对高并发访问和大数据量处理，确保Web应用程序的稳定高效运行。在实施...
总之，Linux下的数据库编程涉及了对MySQL服务器的管理和C语言接口的运用，这对于任何需要在Linux环境中处理数据的开发者来说都是不可或缺的技能。通过深入学习和实践，你将能够熟练地在Linux系统中使用MySQL数据库，...
`mysql-standard-4.1.22-pc-linux-gnu-i686.tar.gz` 是一个针对Linux平台的MySQL服务器版本的压缩包，适用于Intel x86架构。这个特定的版本4.1.22是MySQL历史上的一个稳定版本，提供了多种功能和改进。 一、MySQL...
优化这些设置能提升MySQL服务器的性能和稳定性。 安全性是数据库管理的重要方面。我们应定期备份数据，使用强密码策略，并限制对数据库的远程访问。此外，可以启用日志记录以监控数据库活动，及时发现异常行为。 ...
- 提供了性能优化和增强，包括更快的查询处理和更高的并发能力。 - 引入了更好的安全性特性，如增强的密码策略和默认的加密连接。 - 包含了新的数据类型、函数和操作，例如 JSON 字段支持和窗口函数。 - 提供了...
在所有这些安装过程中，安全性和性能优化是关键考虑因素。例如，调整MySQL配置文件`my.cnf`中的参数，如`innodb_buffer_pool_size`、`max_connections`等，以适应系统的硬件资源和预期负载。同时，确保数据目录有...
在处理高并发访问、大数据量和资源消耗大的应用时，对LAMP服务器进行性能优化显得至关重要。通过优化各个组件，可以提升服务器响应速度，降低资源消耗，从而提高整体系统的稳定性和效率。 **1. Linux操作系统优化**...
总结来说，LAMP平台的优化涉及多个层面，包括操作系统、Web服务器、数据库和编程语言的配置调整，以及硬件升级和策略优化。每个环节的微小改进都可能带来整体性能的显著提升。通过持续监控、测试和调整，可以构建出...
MariaDB是MySQL的一个分支，它在保持向后兼容性的基础上，提供了更多性能优化和功能增强。以下是关于MySQL集群和MariaDB集群搭建的知识点详解： 1. **MySQL/MariaDB集群概念**：MySQL集群是一种高可用性和高性能的...
在构建高性能的Web服务器时，Linux、Nginx、MySQL和PHP（LNMP）的组合是一种常见的选择。这种架构能够提供高效、稳定且可扩展的服务，尤其适合处理高流量的PHP应用。以下是对标题和描述中涉及的知识点的详细解释： ...
这个版本在当时是MySQL的一个稳定版本，提供了许多功能和性能改进。 MySQL-client是用于与MySQL服务器进行交互的工具集，包括mysql命令行客户端、mysqladmin、mysqldump等。这些工具使得用户能够连接到MySQL服务器...
* 服务器数量：根据网站的流量和性能需求，选择合适的服务器数量。 * 负载均衡：使用负载均衡技术，将流量分布到多个服务器上。 * 数据库优化：优化数据库的性能，使用索引、缓存和队列等技术。 二、服务器优化 ...
客户端端口则提供了与MySQL服务器通信的工具，包括命令行客户端和图形化界面工具。这些工具允许用户执行SQL查询、管理数据库对象（如表、视图、索引）以及管理用户账户和权限。常见的MySQL客户端工具有mysql命令行...
SQL的优化技巧，如索引创建、查询优化和存储过程的使用，对于提升数据库性能有着显著影响。 数据库设计是另一个关键环节，包括范式理论的应用（第一范式到第五范式），以及如何避免数据冗余和更新异常。在Linux环境...
为了优化Apache的性能和安全性，可以安装一些额外的扩展，如Apache的手册、SSL支持、Perl模块以及MySQL认证模块： ``` sudo yum -y install httpd-manual mod_ssl mod_perl mod_auth_mysql ``` 7. **重启服务**...
### MySQL 讨论：原理+优化+技巧 #### MySQL 简介 MySQL是一种流行的开源关系型数据库管理系统，因其快速、稳定、易于使用且免费开放源代码的特点而被广泛采用。MySQL支持多种操作系统，包括但不限于Linux、...