高性能mysql读书笔记之二--架构优化和索引
第三章 架构优化和索引
第三章的主要是说合理使用不同的数据类型和索引。主要需要注意的内容有如下：
- 通用原则
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年。 - VARCHAR和CHAR
2.1. 大家都知道VARCHAR是可变长度的，而CHAR是定长的。
2.2. 使用VARCHAR(5)和VARCHAR（200）保存’hello’占用的空间都是一样的。但是VARCHAR(5)只会使用较小的内存空间，因为MySQL通常会分配固定大小的内存块来保存值。 - 索引对于查询的性能影响是非常大的。下面先介绍下索引类型。
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条语句是不会使用索引的sql 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;1
2
3
4
5
6
7
8
9where actor_id + 1 = 5 where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10
```
3.5.1.2. 下面2条是针对上面2条进行修改使用索引的
```sql
where actor_id = 4 where date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY)
```
3.5.2. 前缀索引和索引选择性
3.5.2.1. 当某一列特别长的情况下，如果给全部长度建索引，那样会增加索引的大小，而只做很短的前缀索引，虽然节约了空间，但是会增加选择性。所以建前缀索引必须让选择性接近于全部长度的选择性。
3.5.2.2. 平均来说前缀的选择性能接近于0.31就可以了。
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. 假设有如下表：
```sql
CREATE TABLE rental ( ………… PRIMARY KEY (rental_id), UNIQUE KEY rental_date (rental_date, inventory_id, customer_id), KEY idx_fx_inventory_id (inventory_id), KEY idx_fx_customer_id (customer_id), KEY idx_fx_staff_id (staff_id), ………… )
3.5.5.3.1. 下面的这几个语句是使用到索引的。
WHERE rental_date='2010-05-02' ORDER BY inventory_id desc; WHERE rental_date > '2010-05-02' ORDER BY rental_date, inventory_id; 在where字句是范围的时候需要用最左前缀索引进行排序。