一、什么影响了数据库查询速度
1、影响数据库查询速度的四个因素
2、风险分析
QPS:Queries Per Second意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
TPS:是TransactionsPerSecond的缩写,也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时,收到服务器响应后结束计时,以此来计算使用的时间和完成的事务个数。
Tips:最好不要在主库上数据库备份,大型活动前取消这样的计划。
3、网卡流量:如何避免无法连接数据库的情况
4、大表带来的问题
1)大表的特点
2)大表的危害
①慢查询:很难在短时间内过滤出需要的数据
查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘io -> 降低磁盘效率
②对DDL影响:
建立索引需要很长时间:
修改表结构需要长时间的锁表:会造成长时间的主从延迟('480秒延迟')
3)如何处理数据库上的大表
分库分表把一张大表分成多个小表。
难点:
5、大事务带来的问题
1) 什么是事务
2)事务的ACID属性
①原子性(atomicity):全部成功,全部回滚失败。银行存取款。
②一致性(consistent):银行转账的总金额不变。
③隔离性(isolation):
隔离性等级:
查看系统的事务隔离级别:show variables like '%iso%';
开启一个新事务:begin;
提交一个事务:commit;
修改事物的隔离级别:set session tx_isolation='read-committed';
④持久性(DURABILITY):从数据库的角度的持久性,磁盘损坏就不行了
redo log机制保证事务更新的一致性和持久性。
3)大事务
运行时间长,操作数据比较多的事务。
风险:锁定数据太多,回滚时间长,执行时间长。
解决思路:
二、什么影响了MySQL性能
1、影响性能的几个方面
2、MySQL体系结构
分三层:客户端->服务层->存储引擎
3、InnoDB存储引擎
MySQL5.5及之后版本默认的存储引擎:InnoDB。
1)InnoDB使用表空间进行数据存储
show variables like 'innodb_file_per_table
.frm :是服务器层面产生的文件,类似服务器层的数据字典,记录表结构。
2)(MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)独立表空间
强烈建立对Innodb使用独立表空间,优化什么的会更方便可控。
3)系统表空间的表转移到独立表空间中的方法
或者Alter table同样可以的转移,但是无法回收系统表空间中占用的空间。
4、InnoDB存储引擎的特性
1)特性一:事务性存储引擎及两个特殊日志类型:Redo Log和Undo Log
Redo Log:实现事务的持久性(已提交的事务)。
Undo Log:未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。
Undo日志记录某数据被修改前的值,可以用来在事务失败时进行rollback;Redo日志记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。
2)特性二:支持行级锁
5、什么是锁
1)锁
2)锁类型
3)锁的粒度
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关。
将table_name加表级锁命令:lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作,直到写锁被释放:unlock tables;
4)阻塞和死锁
6、如何选择正确的存储引擎
参考条件:
总结:Innodb大法好。
注意:尽量别使用混合存储引擎,比如回滚会出问题在线热备问题。
7、配置参数
1)内存配置相关参数
sort_buffer_size #定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存);
join_buffer_size #定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲;
read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数;
read_rnd_buffer_size #索引缓冲区大小,MySQL有查询需要时会为其分配内存,只会分配需要的大小。
注意:以上四个参数是为一个线程分配的,如果有100个连接,那么需要×100。
2)如何为缓存池分配内存
Innodb_buffer_pool_size,定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb 关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中;
总内存-(每个线程所需要的内存*连接数)-系统保留内存
key_buffer_size,定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间;
select sum(index_length) from information_schema.talbes where engine='myisam'
注意:即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表。
max_connections控制允许的最大连接数,一般2000更大。不要使用外键约束保证数据的完整性。
8、性能优化顺序
从上到下:
三、数据库结构优化
1、数据库结构优化目的
总结:要避免异常,需要对数据库结构进行范式化设计。
2、数据库结构设计步骤
3、数据库范式设计与反范式化
可参考“数据库逻辑设计之三大范式通俗理解,一看就懂,书上说的太晦涩”
https://segmentfault.com/a/1190000013695030
4、物理设计
可参考“MySQL中字段类型与合理的选择字段类型;int(11)最大长度是多少?,varchar最大长度是多少”
https://segmentfault.com/a/1190000010012140
四、高可用架构设计
1、读写分离
可参考:
MaxScale:实现MySQL读写分离与负载均衡的中间件利器
五、数据库索引优化
1、两种主要数据结构:B-tree和Hash
1)B-tree结构
B-tree索引的限制:
2)Hash结构
Hash索引的限制:
3)MySQL常见索引和各种索引区别
PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
2、使用索引好处和索引缺陷
1)为什么要使用索引
2)索引不是越多越好
索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,而有500页是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。
3、索引优化策略
1)索引列上不能使用表达式和函数
2)前缀索引和索引列的选择性
Innodb索引列最大宽度为667个字节(utf-8 差不多255个字符),MyIsam索引类宽度最大为1000个字节,于是出现前缀索引,索引的选择性。
对于列的值较长,比如BLOB、TEXT、VARCHAR,就必须建立前缀索引,即将值的前一部分作为索引。这样既可以节约空间,又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY,也无法使用前缀索引做覆盖扫描。
语法: ALTER TABLE table_name ADD KEY(column_name(prefix_length))
如何选择索引列的顺序:
3)组合/联合索引策略
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
可参考“最左前缀原理与相关优化”
http://www.uml.org.cn/sjjm/201107145.asp#nav-4-2
4)覆盖索引策略
跟组合索引有点类似,如果索引包含所有满足查询需要的数据的索引则成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作。即索引的叶子节点上面包含了他们索引的数据(hash索引不可以)。
判断标准:使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。
优点:
无法使用覆盖索引的情况:
可参考“mysql高效索引之覆盖索引”
https://www.cnblogs.com/chenpingzhao/p/4776981.html
5)SQL索引优化总结口诀(套路重点)
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上不计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写 *;
不等空值还有or,索引失效要少用;
字符单引不可丢,SQL高级也不难 ;
可参考“MySQL高级-索引优化”
https://www.cnblogs.com/zhaobingqing/p/7071331.html
4、使用索引来优化查询
1)利用索引排序
5、索引的维护和优化
1)删除重复索引
注:主键约束相当于(唯一约束 + 非空约束)
一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示:Multiple primary key defined!!!
2)删除冗余索引
检查工具:pt-duplicate-key-checker
可参考“MySQL索引背后的数据结构及算法原理
explain查询计划”
http://www.uml.org.cn/sjjm/201107145.asp#nav-4-2
六、SQL查询优化
1、获取有性能问题SQL的三种方式
1)慢查日志分析工具
相关配置参数:
slow_query_log # 启动停止记录慢查日志,慢查询日志默认是没有开启的可以在配置文件中开启(on)
slow_query_log_file # 指定慢查日志的存储路径及文件,日志存储和数据从存储应该分开存储
long_query_time # 指定记录慢查询日志SQL执行时间的阀值默认值为10秒通常,对于一个繁忙的系统来说,改为0.001秒(1毫秒)比较合适
log_queries_not_using_indexes #是否记录未使用索引的SQL
常用工具:mysqldumpslow和pt-query-digest
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log
2)实时获取有性能问题的SQL(推荐)
SELECT id,user,host,DB,command,time,state,info
FROM information_schema.processlist
WHERE TIME>=60
查询当前服务器执行超过60s的SQL,可以通过脚本周期性的来执行这条SQL,就能查出有问题的SQL。
2、SQL的解析预处理及生成执行计划
1)查询过程描述
上图原图连接:
https://processon.com/chart_image/5aab1fcbe4b0f68cc019053a.png?_=1521169284173
通过上图可以清晰的了解到MySQL查询执行的大致过程:
2)查询缓存对性能的影响(建议关闭缓存)
第一阶段:
相关配置参数:
query_cache_type # 设置查询缓存是否可用
query_cache_size # 设置查询缓存的内存大小
query_cache_limit # 设置查询缓存可用的存储最大值(加上sql_no_cache可以提高效率)
query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单
缓存查找是利用对大小写敏感的哈希查找来实现的,Hash查找只能进行全值查找(sql完全一致),如果缓存命中,检查用户权限,如果权限允许,直接返回,查询不被解析,也不会生成查询计划。
在一个读写比较频繁的系统中,建议关闭缓存,因为缓存更新会加锁。将query_cache_type设置为off,query_cache_size设置为0。
3)第二阶段:MySQL依照执行计划和存储引擎进行交互
这个阶段包括了多个子过程:
一条查询可以有多种查询方式,查询优化器会对每一种查询方式的(存储引擎)统计信息进行比较,找到成本最低的查询方式,这也就是索引不能太多的原因。
3、会造成MySQL生成错误的执行计划的原因
4、MySQL优化器可优化的SQL类型
查询优化器:对查询进行优化并查询MySQL认为的成本最低的执行计划。为了生成最优的执行计划,查询优化器会对一些查询进行改写。
可以优化的SQL类型:
5、查询处理各个阶段所需要的时间
1)使用profile(目前已经不推荐使用了)
set profiling = 1; #启动profile,这是一个session级的配制执行查询
show profiles; # 查询每一个查询所消耗的总时间的信息
show profiles for query N; # 查询的每个阶段所消耗的时间
2)performance_schema是5.5引入的一个性能分析引擎(5.5版本时期开销比较大)
启动监控和历史记录表:use performance_schema
update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%';
update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';
6、特定SQL的查询优化
1)大表的数据修改
2)大表的结构修改
利用主从复制,先对从服务器进入修改,然后主从切换。
添加一个新表(修改后的结构),老表数据导入新表,老表建立触发器,修改数据同步到新表,老表加一个排它锁(重命名),新表重命名,删除老表。
修改语句这个样子:
alter table sbtest4 modify c varchar(150) not null default ''
利用工具修改:
3)优化not in 和 <> 查询
子查询改写为关联查询:
七、分库分表
1、分库分表的几种方式
分担读负载 可通过 一主多从,升级硬件来解决。
1)把一个实例中的多个数据库拆分到不同实例(集群)
拆分简单,不允许跨库。但并不能减少写负载。
2)把一个库中的表分离到不同的数据库中
该方式只能在一定时间内减少写压力。
以上两种方式只能暂时解决读写性能问题。
3)数据库分片
对一个库中的相关表进行水平拆分到不同实例的数据库中:
如何选择分区键
分片中如何生成全局唯一ID
可参考“表的垂直拆分和水平拆分”
https://www.kancloud.cn/thinkphp/mysql-design-optimalize/39326