mysql优化
InnoDB索引介绍
InnoDB表数据文件本身就是按照B+Tree树组织的一个索引结构。
- 聚簇索引：是按照每张表的主键构建一颗B+Tree，同时叶子节点存放的就是整张表的行记录。一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建
- 辅助索引：非主键索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值（聚簇索引）找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
mysql索引挑选依据
- 为用于搜索、排序或分组的列创建索引，而对于用作输出显示的列则不用创建索引。
-
认真考虑数据列的基数。 基数是指它所容纳的所有非重复值的个数
- 索引短小值。尽量选择较小的数据类型。
- 索引字符串值的前缀。想要对字符串进行索引，应当尽可能指定前缀长度。
- 利用最左前缀
- 不要建立过多的索引
查询优化程序的工作原理
-
分析表 生成关于键值分布情况的统计数据，它可以帮助优化程序更准确的评估索引效率。 analyse table
- 使用explain 验证优化程序的操作。可以了解到各个索引是否会被用到。
- 在必要的时候给予优化程序必要的提示
- 比较拥有相同数据类型的列。如果数据类型相同，那么与类型不同的情况相比，查询性能会提高很多。
- 让索引在比较表达式中单独出现。
- 不要在like模式的开始位置使用通配符。
- 利用优化程序的长处。连接>子查询
- 测试查询的各种替代形式，并且多次运行他们，因为第一次查询所需要的输入程序可能已经缓存在内存当中。
-
避免使用过多的自动类型转换，比如where str_col = 4；可能阻止使用索引
关键字：查询过程的查询条件、比较条件、分组、连接
选择利于高效查询的数据类型
对数据类型的选择会在很多方面影响查询性能。
- 多用数字类型，，少用字符串运算，数字运算比字符串运算更快。如果字符串类型的取值有限，可以选择enum或者set类型。 例子：把ip地址转成无符号整型，使用inet_aton 和inet_ntoa函数可以帮我们完成两种类型的转换。
-
当较小类型够用时，就 不用较大类型。mysql处理较小类型比较大类型快。尤其是对字符串。处理时间和字符串长度有关，较小类型有另外一个好处，能够让表变得更小，从而减少在磁盘读写方面的开销。
- 如果数据列为not null，那么mysql对他的处理速度也会更快。这是因为在查询时候，不再需要检查该列的值是否为null。
- 考虑使用enum列，如果字符串列的基数低（差异值的个数很少），enum的处理速度快，因为它内部是数字。
- 整理表碎片 ，定期使用optimize table，可以消除碎片（尤其是包含可变长度列的表）
- 把数据压缩到blob或text列。使用blob和text列来存储那些可以在应用程序里被压缩和解压的数据。
- 使用合成索引。合成索引列有时很有用。先根据表里其他列计算出一个散列值，把它存储到一个单独的列里，然后通过索索散列值来检索行。只适用于精确匹配
关键字：数字、较小范围数据类型、不允许null、碎片整理、数据压缩、合成索引
选择利于高效存储的数据类型（宏观方向）
行格式可选，默认compact 这种表占据的空间存储少，从而减少了i/0次数 对于带有长blob或text值的表，dynamic行格式最有效
高效加载数据
1.把数据从缓存刷新到磁盘的次数越少，那么数据加载的速度就越快 2.表的索引越少，加载的速度越快。 3.与长sql语句相比，短语句的数据加载速度更快。
调度、锁定、并发也会影响速度
- innodb实行行锁只有在写入操作时会用行锁
- myisam、merge、memory实行表锁执行lock table 和unlock table语句可以显示的获取解除。
- 写入操作时，表被锁定，且互斥，不允许读
- 读取操作时，表被锁定，但不互斥，允许读
优缺点
-
当有很多更新操作时，innodb表可以提供更好的并发性，锁定级别时row，可以减少锁定竞争。
- myisam表的检索速度极快，但是，在有多个检索和更改操作混杂在一起的环境里，特别时当检索需要运行较长时间时，使用表级锁定可能会引发问题，在这种情况下，更新操作可能需要等待很长时间才能得到处理。
如何避免回表查询
建立联合索引，覆盖需要查询的字段，这样在辅助索引里就有需要查询字段的值。