记录学习笔记、分享资源工具、交流技术思想、提升工作效率

mysql优化

后端 xiaomudk 4年前 (2017-07-26) 2366次浏览 0个评论
文章目录[隐藏]

mysql优化

InnoDB索引介绍

InnoDB表数据文件本身就是按照B+Tree树组织的一个索引结构。
  1. 聚簇索引:是按照每张表的主键构建一颗B+Tree,同时叶子节点存放的就是整张表的行记录。一般建表会用一个自增主键做聚簇索引,没有的话MySQL会默认创建
  2. 辅助索引:非主键索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值(聚簇索引)找到数据行的数据页,再通过数据页中的Page Directory找到数据行。

mysql索引挑选依据

  1. 为用于搜索、排序或分组的列创建索引,而对于用作输出显示的列则不用创建索引。

  2. 认真考虑数据列的基数。
    基数是指它所容纳的所有非重复值的个数

  3. 索引短小值。尽量选择较小的数据类型。

  4. 索引字符串值的前缀。想要对字符串进行索引,应当尽可能指定前缀长度。

  5. 利用最左前缀

  6. 不要建立过多的索引

查询优化程序的工作原理

  1. 分析表
    生成关于键值分布情况的统计数据,它可以帮助优化程序更准确的评估索引效率。
    analyse table

  2. 使用explain 验证优化程序的操作。可以了解到各个索引是否会被用到。

  3. 在必要的时候给予优化程序必要的提示

  4. 比较拥有相同数据类型的列。如果数据类型相同,那么与类型不同的情况相比,查询性能会提高很多。

  5. 让索引在比较表达式中单独出现。

  6. 不要在like模式的开始位置使用通配符。

  7. 利用优化程序的长处。连接>子查询

  8. 测试查询的各种替代形式,并且多次运行他们,因为第一次查询所需要的输入程序可能已经缓存在内存当中。

  9. 避免使用过多的自动类型转换,比如where str_col = 4;可能阻止使用索引

    关键字:查询过程的查询条件、比较条件、分组、连接

选择利于高效查询的数据类型

对数据类型的选择会在很多方面影响查询性能。

  1. 多用数字类型,,少用字符串运算,数字运算比字符串运算更快。如果字符串类型的取值有限,可以选择enum或者set类型。
    例子:把ip地址转成无符号整型,使用inet_aton 和inet_ntoa函数可以帮我们完成两种类型的转换。

  2. 当较小类型够用时,就 不用较大类型。mysql处理较小类型比较大类型快。尤其是对字符串。处理时间和字符串长度有关,较小类型有另外一个好处,能够让表变得更小,从而减少在磁盘读写方面的开销。

  3. 如果数据列为not null,那么mysql对他的处理速度也会更快。这是因为在查询时候,不再需要检查该列的值是否为null。

  4. 考虑使用enum列,如果字符串列的基数低(差异值的个数很少),enum的处理速度快,因为它内部是数字。

  5. 整理表碎片 ,定期使用optimize table,可以消除碎片(尤其是包含可变长度列的表)

  6. 把数据压缩到blob或text列。使用blob和text列来存储那些可以在应用程序里被压缩和解压的数据。

  7. 使用合成索引。合成索引列有时很有用。先根据表里其他列计算出一个散列值,把它存储到一个单独的列里,然后通过索索散列值来检索行。只适用于精确匹配
    关键字:数字、较小范围数据类型、不允许null、碎片整理、数据压缩、合成索引

选择利于高效存储的数据类型(宏观方向)

行格式可选,默认compact 这种表占据的空间存储少,从而减少了i/0次数
对于带有长blob或text值的表,dynamic行格式最有效

高效加载数据

1.把数据从缓存刷新到磁盘的次数越少,那么数据加载的速度就越快
2.表的索引越少,加载的速度越快。
3.与长sql语句相比,短语句的数据加载速度更快。

调度、锁定、并发也会影响速度

  1. innodb实行行锁只有在写入操作时会用行锁
  2. myisam、merge、memory实行表锁执行lock table 和unlock table语句可以显示的获取解除。
  3. 写入操作时,表被锁定,且互斥,不允许读
  4. 读取操作时,表被锁定,但不互斥,允许读

优缺点

  1. 当有很多更新操作时,innodb表可以提供更好的并发性,锁定级别时row,可以减少锁定竞争。

  2. myisam表的检索速度极快,但是,在有多个检索和更改操作混杂在一起的环境里,特别时当检索需要运行较长时间时,使用表级锁定可能会引发问题,在这种情况下,更新操作可能需要等待很长时间才能得到处理。

如何避免回表查询

建立联合索引,覆盖需要查询的字段,这样在辅助索引里就有需要查询字段的值。

本网站采用知识共享署名-相同方式共享 4.0 国际许可协议进行授权
转载请注明原文链接:mysql优化
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址