High Performance mysql笔记—Schema设计优化及索引原理[转]
前言High Performance mysql深入剖析了mysql架构原理，以及sql语句执行原理，并结合作者多年实际经验提出如何高性能设计及使用mysql（不像某数据库教科书籍简单介绍几个sql语句）。本书颠覆了我对与对mysql的认识。特别对于目前海量用户互联网中对于高并发，低延迟应用场景如何设计和使用mysql提出了技巧和方法。有志于从事互联网后台开发的童鞋值得好好研究。 --by 姜跃 jiangyue.hust@gmail.com
一．索引类型：
1．B-Tree索引
B树是一种适合磁盘等慢速设备的索引结构，能够以较少磁盘读取次数查找数据。以下是mysql 中InnoDB引擎B+树结构。
能用B+树查询的操作
1) Match the full value
2) Match a leftmost prefix
匹配最左前缀索引。比如一个索引（a,b,c）查找中（a），（a，b）（a,b,c）都会用到索引，但（b,c）将不会用到索引
3) Match a column prefix
匹配索引column’s value的前缀，如查找以J开头的last name
4) Match a range of values
查找在一定范围的值，比如查找last name在Allen和barrymore
5) Match one part exactly and match a range on another par
例如查找last name是Allen ,first name以K开头的人。
6) Index-only queries
2．hash indexes
Hash索引只支持通过column值查询某一特定行不支持范围查询。在mysql中只有Memory storage engine支持hash indexes。hash indexes的缺点 ：不能利用hash indexes排序。不支持partial key matching。只支持=, IN(), and <=>而不支持> ,<
Hash indexes 只有在特定的场景才能发挥高效率，比如通过key查value，类似key value系统。
3．Spatial (R-Tree) indexes
MyISAM支持R-Tree索引，不需要最左前缀匹配。主要用户地理位置相关查询。有个GEOMETRY数据类型采用R-tree索引。
二．索引带来的负面影响
- 更大的数据量，建立索引也需要占用空间
- 更新速度会减慢（涉及到B+树的一些操作，如果有多个索引则可能要该表多个索引结构），一般会减小1/3 ~1/2
因此只有在频繁查询，并且更新操作上较少的数据上建立索引。可以通过profile，日志和实验分析获取。
三Mysql InnoDB 和MyISAM索引结构
Mysql InnoDB主键索引叶节点中存放indexes key以及row data。Secondary key中叶节点存放key 以及主键row data所在的位置。MyISAM的主键索引和Secondary 索引叶节点存放的都是row data虽在的位置。
MyISAM数据存放结构图。MyISAM数据按行产生的时间按顺序存放。
InnoDB数据存放结构。按照主键值顺序以链表的形式存放。
四．索引优化策略：
1.Isolate the Column
在Mysql中索引项不能为一个表达式如：
Select actor_id from sakila.actor where actor_id + 1 = 5;该语句将不采用索引
Select actor_id from sakila.actor where actor_id = 5 -1;该语句能够采用索引
- Clustered indexes
在Clustered indexes中一行数据（rows）存放在B+树索引的叶节点中。所有相邻的key的值是相邻存放所有叫Clustered。在mysql中InnoDB中主键索引采用Clustered indexes
Clustered indexes优势：
1) 相关数据放在一起，在范围查询的时候能够快速读取出数据，因为是顺序读取
2) 数据访问速度块。在B+树种索引和数据是放在叶节点中。能够在I/O-bound服务较大幅度提升速度。
Clustered indexes劣势：
1) 插入数据的速度依赖于插入主键的顺序。如果按照顺序插入将会减少B+树节点的移动。
2) Full table scans的时候会比较慢，因为要同时读取key以及row data，而covering indexes只用读取key
3) 插入新数据或数据更新的时候，有可能造成page splits。当叶节点的数据满了的时候，会把叶节点分裂成两个页，同时会造成较多的数据移动。
使用InnoDB进行插入时，按主键循序插入能够带来更多的效率，顺序插入能够减少B+树数据的移动。
userinfo是按主键顺序插入，userinfo_uuid随机顺序插入。Userinfo要快于userinfo_uuid表插入。
Covering indexes叶节点中存放的是indexes的key所对于的row data所在位置的地址。Covering indexes在查询indexes而不是整个row data时能够取得更好的性能.MyISAM采用覆盖索引，及InnoDB Secondary indexes采用。
1) index使用更小的内存
2) indexes 排序只需要读取indexes value而不是整个row data，只需要很少的I/O操作。
3) InnoDB tables 的secondary indexes采用Covering indexes，在它的叶子节点中存放的是primary key所在row的地址。
3.Using index scans for sorts
Mysql采用两种方式进行排序，第一种方式采用filesort第二种方式直接采用已经排好序的indexes。采用index方式速度较快，因为在index中顺序已经排列好，读取结果的时候只要按索引顺序读取即可。而采用filesort方式要扫所有行并排序，这样速度较慢。
只有在order by子句与索引顺序一致的时候mysql才会采用index方式进行排序。Order by与利用索查找数据一样，也是适用最左前缀原理。当在排序语句中有where子句和join子句的时候，这些子句的列和order by的列组合，适用最左前缀原理。例如
(rental_date, inventory_id, customer_id):为索引
CREATE TABLE rental (
...
PRIMARY KEY (rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);
这句将会采用索引排序
mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
-> WHERE rental_date = '2005-05-25'
-> ORDER BY inventory_id, customer_id/G
这句将不采用索引排序
EXPLAIN SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
4.Packed (Prefix-Compressed) Indexes
MyISAM采用压缩索引前缀减少索引大小。MyISAM对索引值进行排序，以增量存储索引。如第一个索引值是” perform”第二个是” performance”第二个将会存储”7,ance”。在一个block中的索引值只能顺序查找，不能采用二分查找。因此使用时候某些操作比较慢。如顺序扫描比较快，但像order by desc会比较慢。
五．Normalization and Denormalization(范式和反范式设计)
在数据库系统的教科书中一般教导我们设计数据库要求符合第几范式。然而那些技术以及规范是针对于上世纪的企业级MIS系统需求所设计的，如存储空间小，并发访问低，数据模型较为复杂。然而在互联网海量用户，高并发，低延迟的要求下，采用范式设计数据库表已经不能满足需求。
Normalization pros
- 更新操作快
- 重复数据少
Cons
- 在retrieval上要更多的连接查询（join），而连接查询是很昂贵的操作，并且有时候会使索引策略失效
Denormalization Pros
- 所有操作都在一个表中，避免了连接查询，并且在一个表中能够更有效的使用索引策略
Cons
- 重复数据多，在对表的维护上较为复杂。
Normalization实例
mysql> SELECT message_text, user_name
-> FROM message
-> INNER JOIN user ON message.user_id=user.id
-> WHERE user.account_type='premium'
-> ORDER BY message.published DESC LIMIT 10;
该语句将在查找message_text数据时，将对user表进行检查判断是否为’ 'premium'’类型用户。然后对所有为premium的用户进行filesort排序。Filesort（将对其进行归并排序）。
采用Denormalization设计，则会把(account_type, published),放在一起并建立索引。该语句将采用索引进行排序速度非常快。
mysql> SELECT message_text,user_name
-> FROM user_messages
-> WHERE account_type='premium'
-> ORDER BY published DESC
-> LIMIT 10;
在设计中采Normalized 和Denormalized 混合的方式。对于存在性能瓶颈，查询非常平凡，但更新较少的表采用Denormalized方式设计。Denormalized 有以下方式：冗余，Cache table，summary tables
冗余方式。比如有两个表
user(user_id , user_name, user_photo…),
message(message_id ,user_name,user_photo)，
在message表中冗余存放user_name,user_photo而不是user_id，是为了减少连接查询，加快读取速度。但是采用这种方式，当user_name, user_photo有变动的时候，要同时更改几个表，增加更新的代价和维护代价。
在排序中也可以利用冗余来加速，比如为了在messages表中以作者名排序，可以在messages中加入作者名（冗余），并在作者名那里建立索引，这样可以以很快的速度进行排序。
Cache tables 是一个独立的表，用于缓存从多个表中经过复杂的查询获得的查询结果。
summary tables用户存放统计的数据，如使用group by 查询。有时候会建立一些独立的cache tables，用于优化获取数据。这种方式会在数据的准确性上有所损失但是，能够获取较高的性能。比如在一个发帖量巨大的网站上要统计24小时发帖数量。可以建立一个统计表，表里统计每小时发帖数据。要统计某个时间段的数据，只要对这个统计表进行统计即可，而不要对巨大的统计表进行full table scan。这种方式存在一定不准确性，为了更加准确的统计，可以在23个整数时间段对统计表进行统计，以及对帖子表中前后非整数时间段进行扫描统计即可，而不用full table scan。
CREATE TABLE msg_per_hr (
hr DATETIME NOT NULL,
cnt INT UNSIGNED NOT NULL,
PRIMARY KEY(hr)
);
mysql> SELECT SUM(cnt) FROM msg_per_hr
-> WHERE hr BETWEEN
-> CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
-> AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;
mysql> SELECT COUNT(*) FROM message
-> WHERE posted >= NOW() - INTERVAL 24 HOUR
-> AND posted < CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR;
mysql> SELECT COUNT(*) FROM message
-> WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');
使用cache tables和summary table要决定实时维护数据更新还是定时维护数据更新。这取决于应用场景。但是定时维护会节省更多的资源。在重建summary tables和cache tables。在这些表中的数据经常不能访问。为了解决这个问题可以采用”shadow table”，在后台”shadow table”中重建后，通过重命名交换shadow table和原来的表。
Counter tables是一种summary table专门用于统计数据，当一个表的数据量巨大的时候可以需要做某些统计的时候可以采用Counter tables。
例如：
mysql> CREATE TABLE hit_counter (
-> cnt int unsigned not null
-> ) ENGINE=InnoDB;
用cnt统计数据。为了减少更新时候锁的竞争添加一个slot字段，可以有多个客户端对其更新。统计总量的时候可对表求和。
mysql> CREATE TABLE hit_counter (
-> slot tinyint unsigned not null primary key,
-> cnt int unsigned not null
-> ) ENGINE=InnoDB;
mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
经常采用的方式的是以日期+slot如：
CREATE TABLE daily_hit_counter (
-> day date not null,
-> slot tinyint unsigned not null,
-> cnt int unsigned not null,
-> primary key(day, slot)
-> ) ENGINE=InnoDB;
为了减少条数，定时的合并slot
mysql> UPDATE daily_hit_counter as c
-> INNER JOIN (
-> SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
-> FROM daily_hit_counter
-> GROUP BY day
-> ) AS x USING(day)
-> SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
-> c.slot = IF(c.slot = x.mslot, 0, c.slot);
mysql> DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;
六．存储引擎对比
MyISAM
- Table locks
- No automated data recovery
- No transactions
- Only indexes are cached in memory
- Compact storage
MyISAM存储引擎列式紧凑连续排列的，因此在全表扫描非常快。
The Memory Storage Engine
- Table locks
- No dynamic rows
不支持变长数据如BLOB TEXT VARCHAR
- Hash indexes are the default index type
- No index statistics
- Content is lost on restart
重启后数据丢失
The InnoDB Storage Engine
- Transactional
- Foreign keys
- Row-level locks（行锁）
标准的select不使用任何的锁 在并发性上特别好
- Clustering by primary key
InnoDB主键以及一行的值存放在b+树的叶节点中。
- All indexes contain the primary key columns
B+树的索引值叶节点包含主键所在行的位置。
- Slow data load
InnoDB进行排序和扫描的时候会读取indexs以及row data，当表很大的时候读取速度会慢，以及会占用更多的内存。而MyISAM只需要读取出indexs。
- Unpacked indexes
Indexes没有压缩，比MyISAM的索引占得空间大
- No cached COU
NT(*) value
执行没有where子句的sql会进行全表扫描而MyISAM不会。
转自：http://blog.csdn.net/jiangyue_hust/article/details/5989885
