mysql总结

MySQL Query Optimizer基本工作原理

当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

Query语句优化基本思路和原则

1. 优化更需要优化的Query。
2. 从Explain入手（分析sql的执行计划，是否命中索引）。
3. 多使用Profile；（诊断sql的多种资源的消耗情况，CPU、IO等）。
4. 永远用小结果集驱动大的结果集（通过这个原则来减少嵌套循环中的循环次数，以减少IO总量及CPU运算的次数）。
5. 尽可能在索引中完成排序（利用索引的有序性）。
6. 只取自己需要的Columns（网络传输）。
7. 尽可能避免复杂的Join和子查询（对于大表，通过冗余实现单表查询）。

索引类型

1. B-Tree索引（重要）：
   
   • innodb的索引有主键索引（即聚簇索引）和二级索引。主键索引上面存放着索引信息和实际的数据行，二级索引存放的是索引信息和指向的主键值。
   • myisam的索引都一样，存放的都是索引信息和指向数据行的位置。
2. Hash索引：只能支持等值查询。
3. Fulltext索引：只有myisam支持。
4. R-Tree索引

索引好处

1. 能够提高数据检索的效率，降低数据库的IO成本。
2. 降低数据的排序成本
   
   • 因为每个索引中的数据都是按照索引键键值进行排序后存放的。
   • 分组操作没办法直接利用索引完成。但是分组操作是须要先进行排序然后分组的，所以命中索引可以避免进行排序，直接进行分组，降低CPU资源的消耗。

判断是否需要创建索引

1. 较频繁的作为查询条件的字段应该建索引。
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件（如果一个键值会返回占整个表比例很大的记录时，由于根据索引扫描产生的都是随机IO，其效率比进行全表扫描的顺序IO效率低很多，即使不会出现重复IO的读取，同样会造成整体IO性能的下降）。
3. 更新非常频繁的字段不适合创建索引。

索引失效的情况

1. 使用不等于（!=或者<>）的时候。
2. 过滤字段使用了函数运算（如abs（column））。
3. Join语句中Join条件字段类型不一致的时候。
4. 使用LIKE操作的时候如果条件以通配符开始（如’%abc…’）时。
5. MySQL目前不支持函数索引。
6. 使用非等值查询的时候，MySQL无法使用Hash索引。

索引的注意事项

1. 索引不会包含有NULL值的列。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2. 使用短索引。对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3. 索引列排序。MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4. 最左前缀：顾名思义，就是最左优先，假如用三个字段创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

mysql的join算法：Nested Loop Join

原理就是先根据where里面驱动表的条件，过滤出驱动表的基础数据，然后根据过滤后的结果集与第二张表的关联字段作为条件，一条一条地到第二张表中查询数据，最后合并起来。如果还有第三张表参与，则把前两个表的join结果集作为循环基础数据，根据关联字段，再一次到第三个表中查询数据，如此往复。

join语句的优化

尽可能减少Join语句中Nested Loop的循环总次数（让驱动表的结果集尽可能地小），保证Join语句中被驱动表的Join条件字段已经被索引（即关联字段要建索引）。当无法保证被驱动表的Join条件字段被索引且内存资源充足时，不要太吝惜Join Buffer的设置（在Join是All、Index、range或index_merge类型的特殊情况下，Join Buffer才能派上用场）。

GROUP BY优化

MySQL在进行GROUP BY操作时要想利用索引，必须满足GROUP BY的字段同时存放于同一个索引中，且该索引是一个有序索引。不仅如此，是否能够利用索引来实现GROUP BY还与使用的聚合函数有关系。

表设计的注意点

1. 适当冗余。
2. 大字段垂直分拆（如一些备注字段，比较大，在列表又不会用到的字段）。
3. 大表水平拆分（像客户跟进，根据客户ID进行拆分）。
4. 访问压力大的表垂直拆分（像客户和订单模块，可以独立出去）。
5. 对于数字字段，可以通过乘以一个固定的整数来转换成整数存放，而不用浮点数。
6. 对于时间字段，若不用存储1970年以前的时间，则可以都用TIMESTAMP，只占4个字节，其他都占8个字节。

mysql字段为什么尽量不要设置为Null

1. 所有使用NULL值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。
2. NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。
3. NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp。
4. NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错。
5. Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位。

mysql主从切换过程中保证数据一致性方案

1. 在Master的TPS较高的情况下，主从同步的延迟肯定是非常大的，因此为了避免数据库读写分离后应用层无法从Slave拉到实时数据，通常可以在写入Master之前也将数据落到缓存中。
2. 在TPS不高的情况下，可以开启mysql5.5版本后开启的半同步复制功能。当事务提交到Master后，Master会等待Slave的回应，待Slave回应收到Binlog后，Master才会响应请求方已经完成事务。但是当Master实例宕机后，Slave成为新的Master时，主从数据库之间数据肯定还是会出现不一致，这时为了避免手动比对Binlog来确保主从数据的一致性，可以是用mysql5.6版本开始提供的GRID（全局事务ID）特性。由于新Master是之前的Slave，而宕机后的Master在重启后可以作为Slave存在，可以依靠GTID特性来保证主从之间数据的最终一致性。

mysql单表在500W数据量的读写性能

1. 读会受到慢慢变慢，即使有索引。
2. 写是顺序写，跟表数据一般没什么关系，主要是跟表的索引多少有关，所以还是差别不大。

大数据量分页查询方法

大数据量分页查询方法

mysql事务隔离级别和锁的关系

MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

mysql共享锁和排他锁

mysql共享锁与排他锁