찾다
백엔드 개발PHP 튜토리얼MySQL 数据库优化有哪些方式?

MySQL 数据库优化有哪些方式?

回复内容:

MySQL 数据库优化有哪些方式?

写在前面的话:总是在灾难发生后,才想起容灾的重要性

其实数据库优化有很多种方式,不过只有在具体的场景中才会发挥它的最大价值,这是我对前人的优化做的一些总结,希望对你有帮助,以下优化针对mysql

设计原则

1、不在数据库做运算:cpu计算务必移至业务层
2、控制单表数据量:单表记录控制在1000w
3、控制列数量:字段数控制在20以内
4、平衡范式与冗余:为提高效率牺牲范式设计,冗余数据
5、拒绝3B:拒绝大sql,大事务,大批量
6、表字符集使用UTF8
7、使用INNODB存储引擎

数据表设计

1、尽可能地使用最有效(最小)的数据类型

<code>tinyint(1Byte)
smallint(2Byte)
mediumint(3Byte)
int(4Byte)
bigint(8Byte)
bad case:int(1)/int(11)</code>

2、不要将数字存储为字符串,字符转化为数字,用int存储ip而非char(15)
3、优先使用enum或set,sex enum (‘F’, ‘M’)
4,避免使用NULL字段

<code>NULL字段很难查询优化
NULL字段的索引需要额外空间
NULL字段的复合索引无效
bad case:`name` char(32) default null`age` int not null
good case:`age` int not null default 0</code>

5,少用text/blob,varchar的性能会比text高很多;实在避免不了blob,请拆表

6、不在数据库里存图片

7、对于MyISAM表,如果没有任何变长列(VARCHAR、TEXT或BLOB列),使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed,也可以提示想使用固定长度的行

8、使用sample character set,例如latin1。尽量少使用utf-8,因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1,例如mail,url等

9、精确度与空间的转换。在存储相同数值范围的数据时,浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4 字节存储 数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围,DECIMAL类型的数据将会转换成DOUBLE类型

10、库名表名字段名必须有固定的命名长度,12个字符以内;库名、表名、字段名禁⽌止超过32个字符。须见名之意;库名、表名、字段名禁⽌止使⽤用MySQL保留字;临时库、表名必须以tmp为前缀,并以⽇日期为后缀; 备份库、表必须以bak为前缀,并以日期为后缀

11、InnoDB表行记录物理长度不超过8KB,InnoDB的data page默认是16KB,基于B+Tree的特点,一个data page中需要至少存储2条记录。因此,当实际存储长度超过8KB(尤其是TEXT/BLOB列)的大列(large column)时会引起“page-overflow存储”,类似ORACLE中的“行迁移”,因此,如果必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储,如果不太频繁,可以考虑继续保留在主表中,如果将 innodbpagesize 选项修改成 8KB,那么行记录物理长度建议不超过4KB

索引类

1、谨慎合理使用索引

<code>改善查询、减慢更新
索引一定不是越多越好(能不加就不加,要加的一定得加)
覆盖记录条数过多不适合建索引,例如“性别”</code>

2、字符字段必须建前缀索引

3、不在索引做列运算,bad case:select id where age +1 = 10;

4、innodb主键推荐使用自增列

<code>主键建立聚簇索引
主键不应该被修改
字符串不应该做主键
如果不指定主键,innodb会使用唯一且非空值索引代替</code>

5、不用外键,请由程序保证约束

6、避免在已有索引的前缀上建立索引。例如:如果存在index(a,b)则去掉index(a)

7、控制单个索引的长度。使用key(name(8))在数据的前面几个字符建立索引

8、要选择性的使用索引。在变化很少的列上使用索引并不是很好,例如性别列

9、Optimize table可以压缩和排序index,注意不要频繁运行

10、Analyze table可以更新数据

11、索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,count(distinct(username))/count(*) 就是索引选择性,高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,唯一索引的选择性最佳,值为1

12、不要用重复或多余索引,对于INNODB引擎的索引来说,每次修改数据都要把主键索引,辅助索引中相应索引值修改,这可能会出现大量数 据迁移,分页,以及碎片的出现

13、超过20个长度的字符串列,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出, 一般略大于平均长度一点就可以了

14、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a),就可以删除 idx2 索引了

sql语句设计类

1、sql语句尽可能简单,一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库(充分利用QUERY CACHE和充分利用多核CPU)

2、简单的事务,事务时间尽可能短,bad case:上传图片事务

3、避免使用trig/func,触发器、函数不用,客户端程序取而代之

4、不用select *,消耗cpu,io,内存,带宽,这种程序不具有扩展性

5、OR改写为IN()

<code>or的效率是n级别
in的消息时log(n)级别
in的个数建议控制在200以内
select id from t where phone=’159′ or phone=’136′ =>select id from t where phone in (’159′, ’136′);</code>

6、OR改写为UNION

<code>mysql的索引合并很弱智
select id from t where phone = '159' or name = 'john';
=>
select id from t where phone='159' union  select id from t where name='jonh';</code>

7、避免负向%,如not in/like

8、慎用count(*)

9、limit高效分页

<code>limit越大,效率越低
select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;</code>

10、使用union all替代union,union有去重开销

11、少用连接join

12、使用group by,分组、自动排序

13、请使用同类型比较

14、使用load data导数据,load data比insert快约20倍

15、对数据的更新要打散后批量更新,不要一次更新太多数据

16、使用性能分析工具

<code>Sql explain  /  showprofile   /    mysqlsla</code>

17、使用--log-slow-queries –long-query-time=2查看查询比较慢的语句。然后使用explain分析查询,做出优化

<code>show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)</code>

optimize 数据在插入,更新,删除的时候难免一些数据迁移,分页,之后就出现一些碎片,久而久之碎片积累起来影响性能, 这就需要DBA定期的优化数据库减少碎片,这就通过optimize命令。如对MyISAM表操作:optimize table 表名

18、禁止在数据库中跑大查询

19、使⽤预编译语句,只传参数,比传递SQL语句更高效;一次解析,多次使用;降低SQL注入概率

20、禁止使⽤order by rand()

21、禁⽌单条SQL语句同时更新多个表

22、避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)

23、SQL语句要求所有研发,SQL关键字全部是大写,每个词只允许有一个空格

24、能不用NOT IN就不用NOTIN,坑太多了。。会把空和NULL给查出来

留一个思考题吧,性能状态关键指标该怎么计算?

QPS,Queries Per Second:每秒查询数,一台数据库每秒能够处理的查询次数
TPS,Transactions Per Second:每秒处理事务数

注意
1、哪怕是基于索引的条件过滤,如果优化器意识到总共需要扫描的数据量超过30%时(ORACLE里貌似是20%,MySQL目前是30%,没准以后会调整),就会直接改变执行计划为全表扫描,不再使用索引

2、多表JOIN时,要把过滤性最大(不一定是数据量最小哦,而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表。此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序

3、绝大多数情况下,排序的代价通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧

4、利用 pt-query-digest 定期分析slow query log,并结合 Box Anemometer 构建slow query log分析及优化系统

优化大致可以分为以下方面,按照执行难易程度和对当前项目影响排序:
1. MySQL参数优化:可以通过show variables;命令和show status;命令组合来综合分析,可调整的项目根据使用的存储引擎和项目瓶颈具体情况千差万别,需要具体问题具体分析,如果想从这方面入手,建议把问题提得更具体一点;
2. SQL查询优化和索引优化:你可以打开慢日志记录,将需要消耗太多时间的查询记录下来,然后分析相应的SQL语句是否写的不合理,不合理就改了;再到数据库中查表结构,看是否索引设置不合理(一般where语句中的常用字段和排序字段应该加上合适的索引);
3. 增加缓存层:可考虑在MySQL与应用层中间加一个缓存层,如APC、Memcached、Redis等等,将经常使用而更新较少的数据放到缓存层中,可以很好的减轻数据库压力;
4. 优化表结构:首先这个代价稍大,可能要重新灌数据之类的,代码修改可能也会比较多,看之前的封装性好不好了。主要是根据业务需要,看是否之前的表结构有不合理的地方,比如你使用了很多但是又无法排除的join查询;
5. 分库、分表、主从分离:分库是把数据库从1个逻辑库拆分到多个逻辑库,或从1个服务器拆分到多个服务器,分表是将一个表拆分为多个表,甚至是多个物理服务器的不同表;主从分离是将读、写完全分离到不同的数据库服务器;这个方案跟4一样,也是代价比较大,但是可持续性很好,项目到达一定的数量级,必须走这一步;
6. 自己定制MySQL:开源的,可以根据自己特殊业务需要定制,太高端了点点,总之有这种可能,没搞过...

1.不要 select * 按需查询 2.重构查询,根据需要控制索引的使用。因为MySql在一个where子句中只能匹配一条索引(一般来说就是第一个条件),所以在查询中要尽量的缩小条件的范围,尽量使用 = 而不是> \

一本书都写不完,别寄希望让别人直接告诉你答案,找本书好好读几遍。

高性能MySQL 中文 第3版

这本书有很多地方都是讲优化的,楼主可以去看看,电子版的也有,我就不给链接了:)

最基础的也是最常用的 explain

太多了。我就给你说影响速度最大的几个原因吧。 1.数据库选用 2.数据库的设计 3.sql语句的拼写 细说2和3.首先设计肯定很重要,这个不能教你。你自己多问那些行家。一般情况下,不属于同一个范畴的东西不要放在一张表中,除非他们经常同时被查询,而且多的一方的数量很少,比如3个以内。 如果说是一个多的时候,多的一方个数比较多,那么,肯定不能融合成一张表。 其次,索引什么的必须的吧,还有就是范围的优先缩小,比如说where后面把结果集小的放前面,大的放后面,就好比说你从100个人,80个男的,20个女的,其中有60个人都比较高。叫你找10个比较高的女人。你说你是去女的中找10个高的,还是去所有人中先找高的,然后再从里面选10个女的。 如果是大数据,除开索引外,那么分库,分表肯定就是很必要的。 多看书,多看博客,这种问题没人帮得了你,太多了,在实际项目中再学吧。

성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
php怎么把负数转为正整数php怎么把负数转为正整数Apr 19, 2022 pm 08:59 PM

php把负数转为正整数的方法:1、使用abs()函数将负数转为正数,使用intval()函数对正数取整,转为正整数,语法“intval(abs($number))”;2、利用“~”位运算符将负数取反加一,语法“~$number + 1”。

php怎么实现几秒后执行一个函数php怎么实现几秒后执行一个函数Apr 24, 2022 pm 01:12 PM

实现方法:1、使用“sleep(延迟秒数)”语句,可延迟执行函数若干秒;2、使用“time_nanosleep(延迟秒数,延迟纳秒数)”语句,可延迟执行函数若干秒和纳秒;3、使用“time_sleep_until(time()+7)”语句。

php怎么除以100保留两位小数php怎么除以100保留两位小数Apr 22, 2022 pm 06:23 PM

php除以100保留两位小数的方法:1、利用“/”运算符进行除法运算,语法“数值 / 100”;2、使用“number_format(除法结果, 2)”或“sprintf("%.2f",除法结果)”语句进行四舍五入的处理值,并保留两位小数。

php怎么根据年月日判断是一年的第几天php怎么根据年月日判断是一年的第几天Apr 22, 2022 pm 05:02 PM

判断方法:1、使用“strtotime("年-月-日")”语句将给定的年月日转换为时间戳格式;2、用“date("z",时间戳)+1”语句计算指定时间戳是一年的第几天。date()返回的天数是从0开始计算的,因此真实天数需要在此基础上加1。

php怎么替换nbsp空格符php怎么替换nbsp空格符Apr 24, 2022 pm 02:55 PM

方法:1、用“str_replace("&nbsp;","其他字符",$str)”语句,可将nbsp符替换为其他字符;2、用“preg_replace("/(\s|\&nbsp\;||\xc2\xa0)/","其他字符",$str)”语句。

php怎么判断有没有小数点php怎么判断有没有小数点Apr 20, 2022 pm 08:12 PM

php判断有没有小数点的方法:1、使用“strpos(数字字符串,'.')”语法,如果返回小数点在字符串中第一次出现的位置,则有小数点;2、使用“strrpos(数字字符串,'.')”语句,如果返回小数点在字符串中最后一次出现的位置,则有。

php怎么设置implode没有分隔符php怎么设置implode没有分隔符Apr 18, 2022 pm 05:39 PM

在PHP中,可以利用implode()函数的第一个参数来设置没有分隔符,该函数的第一个参数用于规定数组元素之间放置的内容,默认是空字符串,也可将第一个参数设置为空,语法为“implode(数组)”或者“implode("",数组)”。

php怎么读取字符串后几个字符php怎么读取字符串后几个字符Apr 22, 2022 pm 08:31 PM

在php中,可以使用substr()函数来读取字符串后几个字符,只需要将该函数的第二个参数设置为负值,第三个参数省略即可;语法为“substr(字符串,-n)”,表示读取从字符串结尾处向前数第n个字符开始,直到字符串结尾的全部字符。

See all articles

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

뜨거운 도구

에디트플러스 중국어 크랙 버전

에디트플러스 중국어 크랙 버전

작은 크기, 구문 강조, 코드 프롬프트 기능을 지원하지 않음

Dreamweaver Mac版

Dreamweaver Mac版

시각적 웹 개발 도구

ZendStudio 13.5.1 맥

ZendStudio 13.5.1 맥

강력한 PHP 통합 개발 환경

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

mPDF

mPDF

mPDF는 UTF-8로 인코딩된 HTML에서 PDF 파일을 생성할 수 있는 PHP 라이브러리입니다. 원저자인 Ian Back은 자신의 웹 사이트에서 "즉시" PDF 파일을 출력하고 다양한 언어를 처리하기 위해 mPDF를 작성했습니다. HTML2FPDF와 같은 원본 스크립트보다 유니코드 글꼴을 사용할 때 속도가 느리고 더 큰 파일을 생성하지만 CSS 스타일 등을 지원하고 많은 개선 사항이 있습니다. RTL(아랍어, 히브리어), CJK(중국어, 일본어, 한국어)를 포함한 거의 모든 언어를 지원합니다. 중첩된 블록 수준 요소(예: P, DIV)를 지원합니다.