您现在的位置是:技术博客 > 数据库数据库 MYSQL优化 Lucas2023-02-28 20:37【代码】427人已围观 简介Mysql优化 数据库优化可以从架构优化,硬件优化,DB 优化,SQL 优化四个维度入手。 此上而下,位置越靠前优化越明显,对数据库的性能提升越高。我们常说的 SQL 优化反而是对性能提高最小的优化。 这里先从最细的SQL优化来说。 ### 1、字段 #### 1.1、类型设置 选择数据类型只要遵循小而简单的原则就好,越小的数据类型通常会更快,占用更少的磁盘、内存,处理时需要的CPU周期也更少。 从快到慢排序如下: 第一:tinyint,smallint,mediumint,int,bigint 第二:char,varchar 第三:NULL 举几个正确的例子: (1)“省份”或者“性别”,可以设置为ENUM(枚举)类型。因为在MySQL中,ENUM类型被当做数值型数据来处理,而数值型数据被处理起来的速度要比文本类型要快得多。 (2)“房屋描述”或者“产品详情”尽量使用varchar来代替text。但是当varchar大于某些数值的时候,其会自动转换为text,大概规则如下: 大于varchar(255)变为 tinytext,大于varchar(500)变为 text,大于varchar(20000)变为 mediumtext。 再举几个错误的例子 #### 1.2、宽度设置 (1)已知存储的是定长字符数据,尽量使用char(长度),例如:身份证号可以设为char(18),手机号,可以设置为char(11),真实姓名char(4)。 (2)年龄,可以设为tinyint(3) unsigned,长度(0 ~ 255) (3)金额,可以设为decimal(10,2) unsigned,长度(0 ~ 99999999) (4)时间,可以设尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。也可以使用int(10)来存储,优点是存储小,比较方便,缺点是维护时不够直观,读取时通常需要转化。 timestamp 记录经常变化的更新 / 创建 / 发布 / 日志时间 / 购买时间 / 登录时间 / 注册时间等,时区自动处理,比如说做海外购或者业务可能拓展到海外。 datetime 记录固定时间如服务器执行计划任务时间 / 健身锻炼计划时间等,在任何时区都是需要一个固定的时间要做某个事情。超出 timestamp 的时间,如果需要时区必须记得时区处理。 #### 1.3、非NULL设置 在可能的情况下,尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。 设置null的劣势: (1)不好比较,null是一种类型,比较时只能用专门的is null 和 is not null来比较碰到运算符,一律返回null。 (2)效率不高,影响提高索引效果. 因此,在建表时可以使用 not null default, ‘’或0来实现。 ### 2、索引 索引是提高数据库性能的常用方法,用索引检索特定的行,速度相较没有索引快很多,尤其是在查询语句当中包含有MAX(),MIN()和ORDER BY这些命令的时候,性能提高更为明显。 该对那些字段进行索引? #### 2.1、唯一性索引 唯一性索引指的是保证数据在表中的唯一性,唯一性约束可以放在一个或者多个列上。 举几个例子如下: (1)给合同表的合同编号创建唯一索引,以保证合同编号不会重复:alter table table_contract add unique key `contract_number` (`contract_number`); (2)给管理员的所属单位和管理员登录账号创建一个唯一索引,以保证同一个单位登录的用户名必须是唯一的alter table table_admin add unique key `inst_admin_name` (`inst_id`,`name`); ### 2.2、普通索引 普通索引是 MySQL 中最基本的索引类型,它没有任何限制,唯一的作用就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。 一般来说,索引应该建立在那些将用于join,where判断和order by排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引,对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况。 实际案例如下: ``` SELECT `con`.contract_number, `h`.`house_number`, `h`.`address`, `h`.`house_door`, `h`.`inst_id`, `h`.`use_id`, `t`.`tenant_type`, `t`.`tenant_number` FROM `zhwf_deal_contract` `con` LEFT JOIN `zhwf_archive_house` `h` ON `con`.`house_id` = `h`.`house_id` LEFT JOIN `zhwf_archive_tenant` `t` ON `con`.`tenant_id` = `t`.`tenant_id` LEFT JOIN `zhwf_archive_tenant_sign_record` `r` ON `con`.`sign_record_id` = `r`.`sign_record_id` WHERE ( `h`.`inst_id` IN (1, 2, 3, 4, 5, 6, 7, 9, 10, 12, 13) AND `contract_sign_img` <> '' AND `contract_status` <> 3 AND `change_status` = 1 ) AND `con`.`delete_time` = 0 ORDER BY `con`.`contract_id` DESC LIMIT 0, 10; alter table `kj_data`.`zhwf_deal_contract` add index index_0( `contract_sign_img`, `contract_status`, `delete_time`, `change_status`); ``` #### ### 2.3、主键索引 主键索引是一种特殊的唯一索引,不允许值重复或者值为空,创建主键索引通常使用 PRIMARY KEY 关键字。理论上讲每张表都必须创建主键。单用主键查询的速度是最快的。 #### 2.4、全文索引 全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。 案例如下: CREATE FULLTEXT INDEX index_user ON tb_user(name); 给用户表的姓名字段创建全文索引,name 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。 ### 3、查询语句 #### 3.1、避免使用select * 使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。 避免回表查询:使用覆盖索引(也是普通索引),所谓覆盖索引就是指索引中包含了查询中的所有字段。 #### 3.2、避免复杂SQL语句 提升可阅读性;避免慢查询的概率;可以转换成多个短查询,用业务端处理。 #### 3.3、多表关联查询 在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。 #### 3.4、多表使用表的别名 当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。 #### 3.5、where替换HAVING 避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。where和having的区别:where后面不能使用组函数。 #### 3.6、union all代替union union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据 #### 3.7、尽量少用or or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。 #### 3.8、union all代替union 在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。 #### 3.9、分段查询 在MySQL中,在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。 这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。扫描的行数成百万级以上的时候就可以使用分段查询。 #### 3.10、注意范围查询语句 在MySQL中,对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。 #### 3.11、不建议使用%前缀模糊查询 在LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。 #### 3.12、IN包含的值不应过多 在MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;再或者使用连接来替换。 #### 3.13、分组统计可以禁止排序 默认情况下,MySQL对所有GROUP BY col1,col2…的字段进行排序。如果查询包括GROUP BY,想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序。 #### 3.14、避免随机取记录 MySQL不支持函数索引,会导致全表扫描。 转载:感谢您对Lucas个人博客网站平台的认可,非常欢迎各位朋友分享到个人站长或者朋友圈,但转载请说明文章出处“来源Lucas个人博客”。 很赞哦! ( 0 ) 上一篇:Mysql用户及权限 下一篇:mysql重置主键id 相关文章 常用的SQL函数 页面可视化数据表结构 存储过程函数 mysql连表查询 点击排行 生活不止眼前的苟且,还有诗和远方 十年一觉电影梦 奥地利基茨比厄尔 禅修治愈身心 自律成就自我 零边际成本社会 Modern PHP 鸟哥的Linux私房菜 本栏推荐 要技术,更要有创意 定时任务 Curl无法发送https请求 Lnmp环境搭建 常用的SQL函数 Windows的cmd指令 ueditor工具栏浮动bug 有趣的js插件 标签云 git laravel swoole javascript vue ajax html css sql linux docker flask django nginx apache thinkphp markdown sublime wechat layui photoshop nodejs mysql windows composer java maven springboot mybatis IDE 猜你喜欢 Swoole 基础篇一(初识) PHP进程管理器 正则的快速上手 Sublime代码格式化 Lnmp环境搭建 高并发库存防控超量 Linux之top命令 常用的SQL函数 站点信息 建站时间:2018-05-01 在线人数:1人 文章统计:263篇 总浏览量:222513次 统计数据:百度统计 个人信息:扫描二维码查看