高性能Mysql

第五章：高性能的索引策略

1.什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

2.使用索引的好处

1.索引大大减少了服务器需要扫描的数据量。

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机IO变成顺序IO

索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。

3.聚簇索引和非聚簇索引

聚簇索引：实际存储的循序结构与数据存储的物理机构是一致的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，

设置了主键，系统默认就为你加上了聚簇索引，当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，

这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样就木有问题啦。

非聚簇索：引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；

一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引

4.索引什么时候会失效

如果查询中的列不是独立的，则MySQL就不会使用索引。

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

如select id from table where id+1=5;

我们应该简化where条件的习惯，始终将索引列单独放在比较符的一侧

5.索引的一些建议

很长的字符列，可以索引开始的部分字符。

索引中数据列顺序的选择（选择性高的放在左边）

6.InnoDB和MyISAM总结和对比

InnoDB

1.支持ACID事务，支持存储过程，试图，支持行级锁定，支持外键，处理巨大数据量时拥有最大性能

应用场景

1.以insert、update为主的应用

2.在需要高性能的大型数据库中的应用。

3.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

4.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎

5.自动灾难恢复。与其他存储引擎不同，InnoDB表能够自动从灾难中恢复

6.外键约束。MySQL支持外键的存储引擎只有InnoDB

7.支持自动增加列AUTO_INCREMENT属性

MyISAM

较高的查询速度，表级锁定；BLOB和TEXT列可以被索引；不支持事务；不支持外键，支持全文引，

应用场景：

对事务完整性没有要求或者select为主的应用。

无事务要求，但对效率要求比较高的应用。

7.覆盖索引

一个包含查询所需字段的索引称为“覆盖索引”
覆盖索引的好处：
1.索引的条目通常远小于数据行的大小，所以如果只需要读取索引，就极大的减少数据访问量。
2.因为索引是按照顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少很多。
3.由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存了主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

8.使用索引扫描来做排序

mysql可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或升序，创建索引时可以指定ASC或DESC）都一样时，mysql才能使用索引来对结果做排序，
如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序，order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，

否则mysql都需要执行排序操作，而无法使用索引排序。

不能使用索引排序的查询
1.查询使用了不同的排序方向，索引列都是正序定义的。
2.排序字段不在索引列。
3.无法组成最左前缀。
4.某个列有范围查询。

9.冗余索引

冗余索引：如果创建了索引（A,B),再创建索引（A)就是冗余索引，因为这只是前一个索引的前缀索引。

第六章：查询性能的优化

1.是否向数据库请求了不需要的数据

    常见案例及解决思路：
             查询不需要的记录（考虑使用limit）
             多表关联时返回全部列（只取需要的列，避免使用select *）
             总是取出全部列（避免select *）
             重复查询相同数据（使用缓存，避免多次查询同样数据）

小插曲：关于查询缓存

Mysql query cache 默认为打开。
如果有大量的修改和查询时，由于修改造成的cache失效，会给服务器造成很大的开销，可以通过
query_cache_type{0(OFF)1(ON)2(DEMAND)}来控制缓存的开关.
何时cache
1）两个sql必须完全一致才会导致cache命中，包括大小写
2）prepared statement永远不会cache到结果，即使参数完全一样。据说在 5.1 之后会得到改善。
3）where条件中如包含了某些函数永远不会被cache, 比如current_date, now等。
4） date 之类的函数如果返回是以小时或天级别的，最好先算出来再传进去。
select * from foo where date1=current_date -- 不会被 cache
select * from foo where date1='2008-12-30' -- 被cache, 正确的做法
5) 太大的result set不会被cache (< query_cache_limit)

cache何时失效
1）一旦表数据进行任何一行的修改，基于该表相关的cache立即全部失效。
2）查询为了返回结果是否扫描了过多的数据

3.重构查询方式

   1）是否将一个复杂查询分成多个简单查询
    2）切分查询。例如：定期删除大量数据时，如果用一个大的语句一次完成，可能需要锁住很多数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但是重要的查询。
    3）分解关联查询：很多高性能应用都会对关联查询进行分解。简单的，可以对每一个表进行一次单表哈寻，然后将结果在应用汇总关联。
                          select * from tag join tag_post on tag_post.tag_id=tag.id join post ontag_post.id=post.id where tag.tag='mysql';
   分解成小查询
select * from tag where tag='mysql'
select * from tag_post where tag_id=1234
   select * from post where post.id in (123m456m789,9098)
然后通过应用来将多个结果集合并。