⾼性能Mysql（第三版）
⽂章⽬录
1. Mysql架构和历史
1. MyISAM只是将数据写到内存中，等待操作系统定期将数据刷出到磁盘上。所以，⽆法保证断电，数据不丢失。
2. infobright⽤于Mysql数据量过⼤的时候，作为数据仓库。
3. 修改表的引擎，会丢失表原引擎所有的特性。
4. Mysqldump进⾏数据导⼊的时候，需要注意mysqlDump默认会在create table语句之间加载drop table，不注意这点，会导致数据
库中原表的数据丢失。
5. Mysql5.5开始InnoDB才成为了Mysql的默认引擎。
2. 服务器性能剖析
1. 性能分析⼯具：
1. NewRelic。
2. xhprof。
2. Mysql 5.0之后，慢查询⽇志可以显⽰毫秒级别。
3. Schema与数据类型优化
1. 整型⽐字符串类型代价更⼩。因为字符串需要字符集和校对规则。
2. 时间类型：
1. DateTime：存储时间和⽇期，精确到秒。 <1001年 - 9999年>
2. TimeSamp：存储时间和⽇期，精确到秒。只是DateTime⼀半的存储空间，并且会随着时区变化，具有特殊的⾃动更新能⼒。⽽且
允许的时间范围更⼩⼀些。<1970年1⽉1⽇ - 2038年>
3. 整型类型：
1. 数据范围：-2^(n-1) ~ 2^(n-1) - 1, N为空间位数。 且可以选择unsigned属性，表⽰不能为负数，正数的上限提⾼⼀倍。 对于指定
整数长度是⽆⽤的，并不能限制存储的数值范围，只是控制了⼀些数据库的交互⼯具显⽰的数据位数。在存储和计算上，Int(1) 和Int(20)是⼀致的。
1. tinyInt： 8位空间。
2. smallInt：16位。
3. MediumInt： 24位。
4. Int： 32位。
5. BigInt： 64位。
2. 实数类型：对于较⼩的数据使⽤decimal存储，对于数据量较⼤的时候，可以考虑使⽤bigInt代替Decimal，后⾯的⼩数乘以相应的倍
数即可。
3. 字符串类型：对于varchar可以变长的存储数据，不会清除数据末尾的空字符串；char则是固定长度，会⾃动清除数据末尾的空字符
串。 MYISAM默认对字符串进⾏压缩索引，这会导致查询慢得多，导致性能⼤幅度下降。
4. 索引
1. Where条件列使⽤了索引时：先在索引上按值进⾏查，然后返回包含该值的所在⾏。
2. 主键索引和⾮主键索引是有区别的，主键索引存放的值是整⾏字段的数据，⽽⾮主键索引上存放的值不是整⾏字段的数据，⽽且存放
主键字段的值。
2. 最左前缀：
当使⽤复合索引的时候，只有按照索引键顺序使⽤，才能使⽤到索引。跳过最左键则⽆法使⽤到索引。
3. 覆盖索引：Select 的列，是索引中定制的列。
4. 索引的作⽤
1. 让服务器快速定位到表指定的位置。
2. 减少了服务器需要扫描的数据量。
3. 避免排序和临时表的产⽣。
4. 将随机I/O转换为顺序I/O。
5. 前缀索引
1. 定义： 对于Blob和Text，或是很长的varchar类型的列，若是使⽤普通索引，因为数据量很⼤，会导致索引空间剧烈增长，所以，为
了减⼩索引空间，因此，索引前缀就好。⽽且对于mysql对于这三种类型，不允许索引这些列的全部长度。
2. 计算最合适的前缀长度：COUNT(DISTINCT LEFT(name, 1)) / count(*) AS pre1。修改长度。
3. 实现：Alter TABLE user ADD KEY (name(8)); 8：是对于name这列设置的索引前缀长度。
6. 合并索引
1. 在mysql5.0之前，是⽆法把多个索引同时使⽤，只能使⽤让单个索引起效。⽽mysql 5.0之后，则当创建的索引存在问题，mysql会
进⾏索引优化，把可以⽤到的索引，合并成⼀个索引使⽤。
7. 聚簇索引
1. 定义： 不是索引类型，⽽是⼀种数据存储⽅式。在InnoDB中的聚簇索引实际上在同⼀个结构保存了⼀个B-Tree索引和数据⾏。索引
节点对应的数据实际存放在索引的叶⼦页上。 因为⽆法同时把数据⾏存放在两个不同的地⽅，⼀个表中只能存在⼀个聚簇索引。
2. InnoDB通过主键聚集数据。
3. optimize table： 你删除数据时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。⽽是空在那⾥，⽽是等待新的
mysql下载32位
数据来弥补这个空缺，这样就有⼀个缺少，如果⼀时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写⽐较频烦的表，要定期进⾏optimize，⼀个⽉⼀次，看实际情况⽽定了。
8. 覆盖索引
1. 定义：select中字段，都在索引中存在，便不需要再回表查询数据。将⼤⼤提⾼性能。
9. 使⽤索引扫描来做排序
1. mysql⽣成有序的结果，只能通过排序操作和按索引顺序扫描。
2. 若是explain之后的type为index，则说明使⽤了索引顺序扫描的⽅法排序。
3. mysql可以使⽤同⼀个索引既满⾜排序，⼜满⾜查⾏数据。
4. 实现：
1. 只有当索引的列顺序和ORDER BY⼦句的顺序完全⼀致，并且所有的排序⽅向（倒序或正序）都⼀样时，mysql才会使⽤索引
来对结果做排序。
2. 若是查询需要关联多张表，则只有当ORDER BY⼦句引⽤的字段全部为第⼀个表时，才能使⽤索引进⾏排序。ORDER BY也得
满⾜索引的最左前缀。
3. 若是索引的第⼀个列是常数，则ORDER BY可以不需要满⾜最左前缀。eg: where name = ‘Alex’ order by age。
index(name, age)。 但是，若是拍序列的顺序⽅向和索引顺序不⼀致也⽆法使⽤到索引。
10. 压缩(前缀压缩)索引
1. 定义：通过压缩前缀，减⼩索引的存储空间，让更多的索引可以放⼊内存中。默认只能压缩字符串，整数需要通过参数设置。
2. 计算⽅法：先完全保存索引块中的第⼀个值，然后将其他值和第⼀个值进⾏⽐较得到相同的前缀的字节数和剩余不同的后缀部分，把
这部分呢存储起来即可。eg: 1. perform 2. performance, 存储的时候，存储的数据：7，ance。
11. 冗余和重复索引
1. 定义：重复索引指的是在相同的列上按照相同的顺序创建相同类型的索引。
12. 索引和锁
1. 在mysql5.1之后，InnoDB可以在服务端过滤掉⾏后就释放锁，但是在mysql早期版本中，只有当事务提交之后，才会释放锁。13. 优化排序
1. Distinct：执⾏之后会产⽣⼀个中间表，会降低性能，出现Using temporary。
2. Using fileSort: 因为排序的数据过⼤，在⽂件中进⾏排序了，增加索引，排序字段在索引中。
5. 查询优化
1. using(Field): field必须是两个表中⼀样的名称，才能使⽤。相当于ON的功能。
select t1.id, t1.name
from test t1
inner join test2 using (id)
等同于
inner join test2 t2 ON t2.id = t1.id
2. 连表查询的时候，关联表拿出来的字段最好放在索引中，可是使⽤索引数据，不需要返回到数据⾏获取数据。
3. 延迟关联：获取需要访问的记录后再根据关联列返回原表查询需要的所有列。这个技术可以⽤于优化关联查询中的limit⼦句。
问题：查询多少页的多少条数据
1.常规查询
SELECT id,name,age
FROM `user`
LIMIT 50, 5
2.延迟关联
SELECT  id, name, age
FRO `user`
INNER JOIN (  // 先查询出需要的id，因为id是主键存在索引，可以直接从索引中，获取到需要的id。 SELECT id // 再使⽤inner join，根据id，查询需要的其余列。
FROM `user`
LIMIT 50, 5
) as user1 USING(id)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。