(👆👆 👆点击上面公众号快速关注👆👆👆 )
MySQL常用的存储引擎有哪些?区别是什么?答:常用的存储引擎是Innodb、MyISAM。
Innodb
1、行锁,适合高并发。但是容易产生死锁。
2、MySQL5.6以后才支持fulltext索引
3、文件结构是2个文件组成
.frm 表的定义
.idb 存储表的索引和数据。InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB 4、支持事物
MyISAM
1、表锁,不适合高并发状态
2、适合亿级的统计行数,select count(*),这个函数结果是直接存在一个表里面的。是直接读取的,但是如果包含where就和innodb一样了
3、表的存储文件是由表名开头的3个文件组成
.frm 存储表的定义
.MYD存储数据库表数据
.MYI存储数据库表索引
4、不支持事物
Innodb和MyISAM使用场景是什么?怎么去选择?
答:
MyISAM
-
Read/Write>100/1
-
并发不高
-
数据量小
Innodb
-
Read/Write比较好,写的频繁
-
表的数量超过1000万,高并发
存储引擎的选择方法
-
在数据库系统变量里面找到读写的比例,利用show global status 得到系统当前状态变量,在这些变量中,Com_XXX 的值 表示 XXX语句的执行次数,例如:Com_select 表示select语句的执行次数。
-
理想的读写比是100:1,如果读写比达到10:1 ,就认为是以写为主的数据库了。一般情况下这个值在30:1左右。
性能分析方法与工具有哪些?
1、desc 或者 explain
desc sql_statement 或者 explain sql_statement;查询执行计划;
例如:
desc select * from table3 where id = 1 或者 explain select * from table3 where id = 1 ;explain 和 desc 的结果是一样的。结果如下图所示:
图1
结果字段解析:
id:查询的序列号;
select_type: select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。
table:table3是查询的表名;
type:对表的访问方式。all 代表全表扫描。const代表读常量、并且最多只有一条记录匹配,由于是常量,所以实际上只需要读一次。eq_ref代表最多只会有一条记录匹配,一般通过主键或者唯一键索引来访问。fulltext 代表全文索引。index代表全索引扫描;index_merge代表查询中同时使用两个(或更多)索引,然后对索引结果进行合并之后,在读取表数据。range代表索引范围扫描。ref Join代表语句中被驱动表索引引用查询;
possible_keys:可能用到的索引,如果没有为null;
key 实际用到的索引名称;
key_len 被选中使用索引的索引键长度;
ref 通常列出常量 const ;
rows 估算出来的结果集条数。
extra 额外信息;
2、profiling
首先链接到数据库 并且选择数据库 use database_name ;
步骤如下:
<1> set profiling = 1;开启profiling。
<2> 执行一个查询语句。
<3>show profiles。
结果如下图所示其中 query_id 表示查询语句的标识ID ;Duration 表示查询花费的时间;query 表示本次查询的语句。
图2
<4>show profile 参数 for query query_id。
参数可以是cpu 或者block io 多个参数用逗号分割;query_id 是图1中查询语句的标识;
如下图:
例如 show profile cpu,block io for query 2;
结果如图所示;
图3
显示出来本次查询的CPU、IO消耗,为优化提供依据。
3、慢查询日志定位
在数据库配置中有 log_slow_queries = /var/log/mysql/mysql-slow.log
优化建议
-
避免全表扫描
(1)建立索引的时候,应该注意数据的重复量,大量数据重复,查询不会用到索引。
(2)where 和 order by 涉及到的列上建立索引。
(3)避免在where使用!=、<>、or,否则会放弃索引,进行全表扫描。
(4)or连接的两个条件涉及到的列,如果全部建立了索引,那么查询用到了索引,否则为全表扫描。
(5)%的乱用,where条件的第一个字符如果为%,就会退化为全表扫描。
(6)如果不能避免第一个字符为%,则使用全文索引。
(7)避免在where中使用表达式。
-
优化查询结果
(1)避免使用子查询,因为子查询会在内存中建立一个临时表,而关联查询(join)不会。
(2)组合索引要注意最左前缀原则:如果查询条件中,不包含组合索引中最左侧的列,不会用到索引,如果包含了组合索引中最左侧的列,不管其他列怎样组合都会用到索引
-
数据库对象优化
(1)垂直切分
(2)水平切分
(👇更过内容可点击二维码关注查看哦👇)