常用mysql优化_MySql 常用优化

MySql 常用优化

在上一篇博客中简单的介绍了下 MySql 的索引, 在本篇博客中将进一步介绍 MySql 的索引以及常用的 Mysql 优化

一常见的树结构:

二叉树: 每个父节点大于左孩子节点, 小于右孩子节点

平衡二叉树: 二叉树的基础上, 每个节点的子树高度差不大于 1

BTree: 是一种平衡多路搜索树, 另外并保证了每个叶子结点到根节点的距离相同, 每个节点保存了 data

B+Tree: 非叶子结点只存放 key, 叶子节点存储 key,data. 叶子节点可以包含一个指针指向另一个叶子节点以加速顺序存取

二 MySql 存储引擎

InnoDB 存储引擎 当前 MySQL 存储引擎中的主流, InnoDB 存储引擎支持事务支持行锁支持非锁定读支持外键

MyISAM 存储引擎 MyISAM 不支持事务, 不支持行级锁, 支持表锁(效率低), 支持全文索引, 最大的缺陷是崩溃后无法安全恢复

在 InnoDB 和 MyISAM 中索引都采用了 B+Tree 结构, 但是实现方式并不相同:

在 MyISAM 中叶子节点的 data 域并不存放数据而是存放数据记录的地址, 所以 MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引, 如果指定的 Key 存在, 则取出其 data 域的值, 然后以 data 域的值为地址, 读取相应数据记录

在 InnoDB 中有聚集索引和非聚集索引(辅助索引):

聚集索引: 非叶子结点存放的是 < key,point>,point 就是指向下一层的指针 叶子结点保存了这一行的信息, 因此通过主键索引可以快速获取数据 InnoDB 中通常主键就是一个聚集索引准确来说聚集索引并不是某种单独的索引类型, 而是一种数据存储方式就是指在同一个结构中保存了 B+tree 索引以及数据行 innoDB 中, 用户如果没有设置主键索引, 会随机选择一个唯一的非空索引替代, 如果没有这样的索引, 会隐式的定义一个主键作为隐式的聚集索引

非聚集索引: 非聚集索引的叶子结点并没有存放数据, 而是存储相应行数据的聚集索引键, 即主键当通过非聚集助索引来查询数据时, InnoDB 存储引擎会遍历非聚集索引找到主键, 然后再通过主键在聚集索引中找到完整的行记录数据

三总结

使用 B+Tree 作为索引结构的原因:

B-Tree 每个节点中不仅包含数据的 key 值, 还有 data 值而每一个页的存储空间是有限的, 如果 data 数据较大时将会导致每个节点 (即一个页) 能存储的 key 的数量很小, 当存储的数据量很大时同样会导致 B-Tree 的深度较大, 增大查询时的磁盘 I/O 次数, 进而影响查询效率在 B+Tree 中, 所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上, 而非叶子节点上只存储 key 值信息, 这样可以大大加大每个节点存储的 key 值数量, 降低 B+Tree 的高度

四 MySql 常用优化

索引失效的情况

以 % 开头的 like 查询

(not , not in, not like, <>, != ,!>,!

如果条件中有 or, 即使其中有部分条件带索引也不会使用

where 子句里对索引列上有数学运算或者使用函数, 用不上索引

索引列的数据类型存在隐形转换则用不上索引比如字符串, 那一定要在条件中将数据使用引号引用起来

sql 优化

分解关联查询: 将关联 (join) 放在应用中处理, 执行简单的 sql, 好处是: 分解后的 sql 通常由于简单固定, 能更好的使用 mysql 缓存还可以可以减少锁的竞争

SELECT 子句中避免使用 * 号 , 它要通过查询数据字典完成的, 意味着将耗费更多的时间, 而且 SQL 语句也不够直观

关于 Limit 在使用 Limit 2000,10 这种操作的时候, mysql 会扫描偏移量 (2000 条无效查询) 数据, 而只取后 10 条, 尽量想办法规避

通常情况下, 使用一个性能好的 sql 代替使用多个 sql 除非这个 sql 过长效率低下或者对于 delete 这种语句, 过长的 delete 会导致太多的数据被锁定, 耗尽资源, 阻塞其他 sql

WHERE 子句中的连接顺序 数据库采用自右而左的顺序解析 WHERE 子句, 所以那些可以过滤掉最大数量记录的条件最好写在 WHERE 子句的最右

选择最有效率的表名顺序 数据库的解析器按照从右到左的顺序处理 FROM 子句中的表名, FROM 子句中写在最后的表将被最先处理 在 FROM 子句中包含多个表的情况下: 如果是完全无关系的话, 将记录和列名最少的表写在最后如果是有关系的话, 将引用最多的表, 放在最后

删除全表数据用 TRUNCATE 替代 DELETE 这里仅仅是: DELETE 是一条一条记录的删除, 而 Truncate 是将整个表删除, 保留表结构, 这样比 DELETE 快

多使用内部函数提高 SQL 效率

使用表或列的别名, 使用简短的别名也能稍微提高一些 SQL 的性能毕竟要扫描的字符长度变少了

用>= 替代> , 低效:> 3 首先定位到 = 3 的记录并且扫描到第一个大于 3 的记录高效:>= 4 直接跳到第一个等于 4 的记录

用 IN 替代 OR

数据库结构优化

表结构优化: 字段尽量使用非空约束, 因为在 MySql 中含有空值的列很难进行查询优化, NUll 值会使索引以及索引的统计信息变得很复杂

数值类型的比较比字符串类型的比较效率要高得多,

尽量使用 TIMESTAMP 而非 DATETIME(查询效率)

单表不要有太多的字段, 建议在 20 以内

合理加入冗余字段

垂直表拆分 水平表拆分

来源: https://www.thinksaas.cn/group/topic/839165/