MySql优化
发布时间：2022-10-29 16:13:11 所属栏目：MySql教程 来源：
导读： 硬件层面提高速率
1.更大的内存，减少磁盘IO
2.cpu，采用多核cpu，提高Mysql的执行效率
3.采用更高转速的机械硬盘或固态硬盘，可以提高磁盘IO的效率
软件层面-单机的优化
1.更大的内存，减少磁盘IO
2.cpu，采用多核cpu，提高Mysql的执行效率
3.采用更高转速的机械硬盘或固态硬盘，可以提高磁盘IO的效率
软件层面-单机的优化
|
硬件层面提高速率 1.更大的内存，减少磁盘IO 2.cpu，采用多核cpu，提高Mysql的执行效率 3.采用更高转速的机械硬盘或固态硬盘，可以提高磁盘IO的效率 软件层面-单机的优化 mysql的逻辑结构 mysql分页优化_mysql 优化_mysql数据库优化面试 mysql逻辑结构 客户端发出一条语句到mysql服务， ①首先进过连接层，通信协议检测客户端是否兼容服务器；②线程处理，为每一个连接请求分配一个线程；③用户认证，判断用户的用户名和密码是否正确。（查询缓存已废除）④然后进入sql层由解析器针对sql语句进行解析，判断语法是否正确；⑤优化器对sql进行相应的优化，生成它认为最佳执行计划；⑥然后通过API接口访问存储引擎操作数据。 执行计划：决定我们具体是如何去获取数据或则存储数据的，优化的关键点，就是根据执行计划来修正的。 mysql的存储引擎 如何正确建表? 数据类型选择原则 tinyint(8) smallint(16) mediumint(24) int(32) bigint(64) 数据类型从小出发（正确估算，后期修改非常耗时）避免出席那NULL，一般设置为NOT NULL,列出现NULL，不利于MySql做优化建字段设int(11),不会限制值得合法范围。int(1)和int(32)没有区别。 对于"金额"字段需要精确度计算的，我们一边采用decimel（数据库转换），bigint(程序手动转换) varchar(可变长度) char(定长)适合存加密后的密码 日期类型：datetime相较于timestamp能保存更大范围的值 索引 索引是存储引擎用于快速找到记录的一种数据结构（没用索引，查数据就需要全表扫描，我们要避免全表扫描） 创建索引：create [unique|fulltext] index 索引名 on 表名(属性[长度][asc|desc]); 删除索引：drop index 索引名 on 表名； 索引底层的数据结构：B+tree 和 哈希索引 B+tree的进化过程 ①二叉树：左子树的节点小于根节点，右子树的节点大于根节点，容易出现一边倒，最严重的情况就是全表扫描 ②平衡二叉树（AVL Tree）：在满足二叉树的条件下，要求任何节点的两个子树的高度最大差为1。在AVL树中插入或则删除节点，都可能造成AVL树失去平衡，造成平衡可能会出现四种状态LL、LR、RR，RL mysql 优化_mysql分页优化_mysql数据库优化面试 LL，RR只需要进行LL单旋转、RR单旋转就能平衡 mysql数据库优化面试_mysql分页优化_mysql 优化 LL单旋转 mysql数据库优化面试_mysql分页优化_mysql 优化 RR单旋转 对于LR，则需要，先RR单旋转，变成LL，再进过LL单旋转即可。 mysql分页优化_mysql数据库优化面试_mysql 优化 RL，LL单旋转，变成RR，再经过RR单旋转即可。 mysql分页优化_mysql 优化_mysql数据库优化面试 ③多路查找树B-tree，B-Tree是为磁盘等外存储设备设计的一种平衡查找树，首先我们知道系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB； mysql数据库优化面试_mysql分页优化_mysql 优化 B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。 ④B+tree是B-tree基础上的一种优化，B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度 B+Tree相对于B-Tree有几点不同：非叶子节点只存储键值信息；所有叶子节点之间都有一个链指针；数据记录都存放在叶子节点中 mysql 优化_mysql分页优化_mysql数据库优化面试 B+Tree 聚集索引和非聚集索引： InnoDB存储引擎会将我们的主键作为聚集索引。辅助索引与聚集索引的区别在于非聚集索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的主键。当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。 哈希索引和B+Tree索引 哈希索引的查找效率更高，时间复杂度为O(1),但是因为Hash存储的无序性，导致哈希索引无法实现范围查找。所以我们采用B+tree。 索引的分类 主键索引、普通索引、唯一索引、全文索引（5.6） 高效的索引策略： 前缀索引： 前缀索引：选择字符列的前n个字符作为索引，这样可以减少索引空间大小，提高索引效率，Alter table table_name add index index_name (index_column(length))； 问题：前缀索引length取多少合适？ 通过计算Select Count(distinct left(index_column,n))/count(*) from table；来计算不同的n值对应的区分度。Mysql无法使用前缀索引做order by和group by。 联合索引（组合索引） 联合索引：mysql从左到右使用索引中的字段，一个查询可以只用到索引的一部分，但只能是最左侧部分，例如索引是key index(a,b,c),那么可以支持a|a,b|a,b,c三种组合进行查找，但是不支持b,c和a,c的查找。当最左侧字段是常量引用时，索引效率更高。区分度更高的列放在前面。 索引的使用原则 什么时候该加索引？ 什么时候不该加索引？ SQL的书写顺序 select list from left_table join right_table on <join_condtion> where <where_condition> group by <group_by_list> Having <having_condition> order by <order_by_condition> limit <limit_number> SQL的执行顺序 先确定范围，然后做数据过滤，再获取子集，再排序，最后分页 from <left_table> JOIN <right_table> ON <join_condition> 确定了范围 WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> 做数据过滤 SELECT DISTINCT <select_list> 获取子集 ORDER BY <order_by_conditon> 排序 LIMIT <limit_number> 分页 所有的查询语句都是从from开始执行的，再这个执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。首先对from前两个表执行一个笛卡尔乘积，生成一张虚拟表v1,然后将on中的逻辑表达式应用到v1中的各个行，帅选出满足逻辑表达式的行，生成虚拟表v2，如果是left outer join，则把左表过滤掉的行加进来，right outer join同理，生成虚拟表v3，如果还有其他表mysql 优化，那么将v3和第三张表重复上面步骤。得到最终的v333,然后再接着.... 慢查询定位需要优化的SQL？ MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，默认不开启，开启或多或少带来性能的影响，如果不是调优需要的话不建议开启。mysqldumpslow分析慢查询日志。 Explain产看执行计划 执行计划参数:id、selecttype、table、type(访问类型)、possible_key(预计会使用到的索引)、key(实际使用的key）、ken_len（使用索引的长度）、ref、rows（扫描的行数）、Extra, type:重要有all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的 all:全表扫描index,全表扫描，只是通过索引次序进行行数据的读取range,基于索引的范围扫描，相比较于index的全表扫描，index是由范围的。如like "R%",between and ,>,1000精确匹配某一列并范围匹配另一列，第一列全值匹配，第二列范围匹配 B+tree索引无效 不按照最左前缀原则不能跳过索引中的列，比如查询第一列和第三列，不指定第二列的话，那只能使用索引的第一列索引不能时表达式的一部分或则函数的参数。如：EXPLAIN select * from t_student where YEAR(entrance)='2018'; EXPLAIN PARTITIONS select * from t_student where YEAR(entrance)='2018'; 存在隐式转换 EXPLAIN SELECT * from t_student_score where name=123 数字转字符串 优化查询 优化子查询：尽量采用关联查询，然后给关联查询添加索引关联查询：确保关联查询的条件列上有索引优化分组和排序：确保任何Group by和order by中的表达式只涉及一个表中的列，然后为该列加上索引，这样mysql才有可能使用索引进行优化优化分页语句（重点） 优化分页语句 问题：对于limit 100000,10 MySql需要查询100010条数据，然后抛弃掉前面100000条数据，返回10条。显然这样是不可取的 思路：子查询先利用覆盖索引查询到满足条件的主键数据，再利用主键回表查找对应记录 Mysql合适放弃使用索引？ 索引的区分度不高的时候会放弃使用索引 插入优化和删除优化 插入数据尽量采用批处理的模式，减少跟数据库的交互次数。JDBC 1000 执行insert ：Insert into table values (?,?,?),(?,?,?),(?,?,?); 通过load的方式导入数据，删除索引，关闭唯一校验，导入后再添加索引，添加唯一校验 删除的时候建议先删除索引，删除完后在重新建立索引 表分区 分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样，分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。 分区字段必须是主键的一部分 常见分区类型：范围分区（根据年份），列表分区，哈希分区（尽可能分布均匀，不适合分区经常变动的需求，仅支持整数分区）、键值分区（不常用），建议：在where带上分区的字段。 多表优化：主从表（读写分离，主负责写，从） （编辑：海洋资讯信息网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐