《高性能MySQL》第四、五、六章笔记

第四章 Schema与数据类型优化

4.1 选择优化的数据类型

• 更好的通常更好

   一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用更好的磁盘、内存、CPU缓存，并且处理时需要的CPU周期也更少。但是要确保没有低估需要 存储的值的范围。

• 简单就好

   简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。

• 尽量避免NULL

    如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要-一个额外的字节。
 

4.2 MySQL schema设计中的陷阱

1. 太多的列
2. 太多的关联：如果希望查询执行得快速且并发性好，单个查询最好在12个表以为做关联。
3. 全能的枚举
4. 变相的枚举

第5章 创建高性能的索引

5.1索引基础

5.1.1索引的类型

 在MySQL中，索引是在存储引擎蹭而不是服务器层实现的。

1）B-Tree索引

B+树索引对如下类型的查询有效（假设有如下索引index_name_age（name,age））

1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配：比如where name="zhangsan" and age = 18
2. 匹配最左前缀：比如where name like "张%"
3. 匹配范围值：比如where name between "zhangsan"  and "lisi"
4. 精确匹配某一列并范围匹配另外一列：比如where name = "zhangsan" and age >18
5. 只访问索引的查询：即覆盖索引、索引覆盖

下面是一些关于B+树索引的限制：

1. 如果不是按照索引的最左列开始查找，则无法使用索引。
2. 不能跳过索引中的列。
3. 如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找。

2）哈希索引

3）空间数据索引

4）全文索引

5.2 索引的优点

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机I/O变为顺序I/O。
   

索引将相关的记录放到一起则获得一星;

如果索引中的数据顺序和查找中的排列顺序一致则获得二星;

如果索引中的列包含了查询中需要的全部列则获得“三星”。
 

5.3 高性能的索引策略

5.3.1 独立的列

         我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如.果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

select id from student where id+1=18 错误

select  id from student where id = 17 正确

5.3.2 前缀索引和索引选择性

     有时候需要索引很长的字符串，这会让索引变的大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

      索引的选择性是指，不重复的索引值(也称为基数，cardinality)和数据表的记录总数(#T) 的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性，性能也是最好的。

如下所示：看看哪截取name的前几个字符串的选择性和第一条SQL查询的结果近似，即改截取几个字符串。

//基准
SELECT count(DISTINCT name) /count(*) from student;
SELECT count(DISTINCT LEFT(name,3)) /count(*) from student;
SELECT count(DISTINCT LEFT(name,4)) /count(*) from student;
SELECT count(DISTINCT LEFT(name,5)) /count(*) from student;

5.3.4 选择合适的索引列顺序

       对于如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助，但通常不如避免随机I0和排序那么重要，考虑问题需要更全面(场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要)。
      当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性(整体基数)，也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
 

何为选择性：性别选择性低，身份证选择性高。即不重复的值占所有行的比例高为选择性高。

5.3.4 聚簇索引

        如果没有定义主键，InnoDB会选择-一个唯- -的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

       聚餐索引（一级索引）即存索引值，又存整行数据。

       非聚餐索引（二级索引）即存索引值，又存主键值。
 

5.3.6覆盖索引

   二级索引存储的是索引值和主键，其实二级索引可以覆盖主键。

5.3.7 使用索引扫描来做排序

       MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果explain出来的type列的值为“index”,则说明MySQL使用了索引扫描来做排序。

     只有当索引的列顺序和order by子句的顺序完全一致，并且所有的列的排序方法（倒叙或者正序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序，order by子句和查询型查询的限制是一样的：需要满足索引的最左前缀的要求，否则MySQL都需要执行排序操作，而无法利用索引排序。

5.3.9冗余和重复索引

       冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一一个新的索引(A, B)而不是扩展已有的索引(A)。还有一种情况是将-一个索引扩展为(A, ID)，其中ID是主键,对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

5.3.11 索引和锁

5.6 总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住:

1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立)。如果服务器从存储中读取-一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
2. 按顺序访问范围数据是很快的，这有两个原因。第一,顺序I/O不需要多次磁盘寻道，所以比随机1/O要快很多(特别是对机械硬盘)。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。
3. 索引覆盖查询是很快的。如果-一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。
    

第6章 查询性能优化

6.2 慢查询基础：优化数据访问

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

• 查询不需要的记录

   先使用SELECT语句查询大量的结果，然后获取前面的N行后关闭结果集。他们认为MySQL会执行查询，并只返回他们需要的N条数据，然后停止查询，实际情况是MySQL会查询全部的结果集，然后在全部返回给客户端。最简单有效的解决方法就是在查询后面加上limit

• 多表关联时返回全部的列

    (错误写法)select * from student left join classrom on student.cid = clasroom.cid 

    (正确写法)select student.* from student left join classrom on student.cid = clasroom.cid 

• 总是取出全部的列

  select * from student。确定要全部的学生信息吗，而不是只要name和age???

• 重复查询相同的数据

   当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能会更好。