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个女的。 如果是大数据,除开索引外,那么分库,分表肯定就是很必要的。 多看书,多看博客,这种问题没人帮得了你,太多了,在实际项目中再学吧。

세션 관련 XSS 공격으로부터 응용 프로그램을 보호하려면 다음 조치가 필요합니다. 1. 세션 쿠키를 보호하기 위해 Httponly 및 Secure 플래그를 설정하십시오. 2. 모든 사용자 입력에 대한 내보내기 코드. 3. 스크립트 소스를 제한하기 위해 컨텐츠 보안 정책 (CSP)을 구현하십시오. 이러한 정책을 통해 세션 관련 XSS 공격을 효과적으로 보호 할 수 있으며 사용자 데이터가 보장 될 수 있습니다.

PHP 세션 성능을 최적화하는 방법 : 1. 지연 세션 시작, 2. 데이터베이스를 사용하여 세션을 저장, 3. 세션 데이터 압축, 4. 세션 수명주기 관리 및 5. 세션 공유 구현. 이러한 전략은 높은 동시성 환경에서 응용의 효율성을 크게 향상시킬 수 있습니다.

THESESSION.GC_MAXLIFETIMESETTINGINSTTINGTINGSTINGTERMINESTERMINESTERSTINGSESSIONDATA, SETINSECONDS.1) IT'SCONFIGUDEDINPHP.INIORVIAINI_SET ()

PHP에서는 Session_Name () 함수를 사용하여 세션 이름을 구성 할 수 있습니다. 특정 단계는 다음과 같습니다. 1. Session_Name () 함수를 사용하여 Session_Name ( "my_session")과 같은 세션 이름을 설정하십시오. 2. 세션 이름을 설정 한 후 세션을 시작하여 세션을 시작하십시오. 세션 이름을 구성하면 여러 응용 프로그램 간의 세션 데이터 충돌을 피하고 보안을 향상시킬 수 있지만 세션 이름의 독창성, 보안, 길이 및 설정 타이밍에주의를 기울일 수 있습니다.

세션 ID는 로그인시, 민감한 작업 전에 및 30 분마다 정기적으로 재생되어야합니다. 1. 세션 고정 공격을 방지하기 위해 로그인 할 때 세션 ID를 재생합니다. 2. 안전성을 향상시키기 위해 민감한 작업 전에 재생성. 3. 정기적 인 재생은 장기 활용 위험을 줄이지 만 사용자 경험을 평가해야합니다.

Session_SET_COOKIE_PARAMS () 함수를 통해 PHP에서 세션 쿠키 매개 변수 설정을 달성 할 수 있습니다. 1)이 기능을 사용하여 만료 시간, 경로, 도메인 이름, 보안 플래그 등과 같은 매개 변수를 설정하십시오. 2) call session_start ()를 호출하려면 매개 변수를 발효시킵니다. 3) 사용자 로그인 상태와 같은 요구에 따라 매개 변수를 동적으로 조정합니다. 4) 보안을 향상시키기 위해 안전하고 httponly 플래그 설정에주의를 기울이십시오.

PHP에서 세션을 사용하는 주요 목적은 다른 페이지간에 사용자의 상태를 유지하는 것입니다. 1) 세션은 Session_Start () 함수를 통해 시작되어 고유 한 세션 ID를 생성하고 사용자 쿠키에 저장합니다. 2) 세션 데이터는 서버에 저장되므로 로그인 상태 및 쇼핑 카트 컨텐츠와 같은 다른 요청간에 데이터를 전달할 수 있습니다.

하위 도메인 간의 세션을 공유하는 방법? 공통 도메인 이름에 대한 세션 쿠키를 설정하여 구현. 1. 세션 쿠키 도메인을 서버 측에서 .example.com으로 설정하십시오. 2. 메모리, 데이터베이스 또는 분산 캐시와 같은 적절한 세션 저장 방법을 선택하십시오. 3. 쿠키를 통해 세션 ID를 전달하면 서버는 ID를 기반으로 세션 데이터를 검색하고 업데이트합니다.


핫 AI 도구

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

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

Undress AI Tool
무료로 이미지를 벗다

Clothoff.io
AI 옷 제거제

Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!

인기 기사

뜨거운 도구

에디트플러스 중국어 크랙 버전
작은 크기, 구문 강조, 코드 프롬프트 기능을 지원하지 않음

ZendStudio 13.5.1 맥
강력한 PHP 통합 개발 환경

DVWA
DVWA(Damn Vulnerable Web App)는 매우 취약한 PHP/MySQL 웹 애플리케이션입니다. 주요 목표는 보안 전문가가 법적 환경에서 자신의 기술과 도구를 테스트하고, 웹 개발자가 웹 응용 프로그램 보안 프로세스를 더 잘 이해할 수 있도록 돕고, 교사/학생이 교실 환경 웹 응용 프로그램에서 가르치고 배울 수 있도록 돕는 것입니다. 보안. DVWA의 목표는 다양한 난이도의 간단하고 간단한 인터페이스를 통해 가장 일반적인 웹 취약점 중 일부를 연습하는 것입니다. 이 소프트웨어는

맨티스BT
Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.

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