mysql
1、Tps与Qps
TPS: (每秒事务处理量(TransactionPerSecond))
TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME 事务提交加事务回滚的和再除以时间
QPS:每秒查询率17
QPS = QUESTIONS/UPTIME 查询的个数除以时间
2、MySqlSlap mysql压力测试工具
3、mysql架构与存储引擎
逻辑架构:连接层、服务层、引擎层、存储层
连接层:连接验证账号密码
服务层:解析sql,优化与缓存,缓存会默认缓存sql语句,
通过配置缓存结果数据(my.ini里找query_cache_type 是否开启缓存: show variables like '%query_cache_type';
查看缓存的大小:show variables like '%query_cache_size';
设置缓存的大小:SET GLOBAL query_cache_size = 134217728)
生产环境建议不开启,占用内存。推荐使用redis。
sql解析:顺序
1 FROM <left_table> 2 ON <join_condition> 3 <join_type> JOIN <right_table> 4 WHERE <where_condition> 5 GROUP BY <group_by_list> 6 HAVING <having_condition> 7 SELECT 8 DISTINCT <select_list> 9 ORDER BY <order_by_condition> 10 LIMIT <limit_number>
查看执行计划 在sql前加 explain
4、存储引擎:MyISAM、InnoDB、CSV、myarchive、memory、ferderated
myisam不支持主外键,只支持表级锁(不适合高并发), 不支持事务,只缓存索引不缓存数据,表空间小 主要关注性能 会存储count(*)直接查询出来
支持经纬度地图类型,空间函数
myisam(只读的推荐使用)
Innodb 支持主外键, 支持行级锁(适合高并发), 支持事务, 即缓存索引也缓存数据 表空间大 主要关注事务 count(*)会扫描全表
CSV,以csv格式文件存储并且可以对数据进行编辑(编辑后要执行flush table进行刷新),所有列不能为null,不支持索引
myarchive,以arz文件存储,对数据进行了压缩,只可以使用insert与select,值只允许在自增的列上加索引,(适用于日志的存储)
memory、(临时表)都在内存里存储重启mysql会清除,用temporary创建临时表另一个会话也会查不到,支持hash索引与btree索引,所有字段都是固定长度,不支持blog和text等大字段、表级锁。
ferderated、能访问远程mysql服务器的表,本地只存储表结构不存储数据,数据放在远程服务器上,
5、Innodb的系统表空间和独立表空间
独立表空间有自己独立的数据与索引的文件(.ibd文件)
系统表空间没有自己独立的数据与索引的文件(.ibd文件)
,统一放到了ibddata文件里
主要区别
1:系统表空间无法简单的收缩文件大小
2:系统表空间会产生IO瓶颈
3:独立表空间可以通过optimize table收缩文件大小
4:独立表空间可以同时向多个文件刷新数据
推荐使用独立表空间
6、范式设计与反范式设计
第一范式:数据库表中的字段只具有单一属性,单一属性的列是由基本数据类型构成的,
第二范式:表中只有一个业务主键
第三范式:数据不能存在传递关系,即每个属性都跟主键有直接关系而不是间接关系。像:a-->b-->c 属性之间含有这样的关系,是不符合第三范式的。
范式设计写sql比较复杂,影响性能。
反范式设计允许少量的冗余,空间换时间。
7、timestamp与datetime
datetime 8个字节或5个字节(5.6后是5个字节) 范围1000-01-01 --- 9999-12-31 格式 YYYY-MM-DD HH:MM:SS 与时区无关
timestamp 4个字节 范围 1970-01-01 --- 2037 YYYYMMDD HH:MM:SS 与时区有关(改变时区会自动改变时间)
8、慢查询
SHOW VARIABLES LIKE "slow_query_log";查询慢查询状态
SET GLOBAL slow_query_log = 1 ;设置慢查询状态
SET GLOBAL long_query_time = 0;设置记录日志的sql最短时间
SHOW VARIABLES LIKE "log_output"; 查询日志存放位置
C:\ProgramData\MySQL\MySQL Server 5.1\data\slow_log 文件里有日志
9、执行计划
explain+sql
ID列:描述select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
根据ID的数值结果可以分成一下三种情况
id相同:执行顺序由上至下
id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
id相同不同:同时存在
Select_type:查询的类型
table:查询的表
type:
system表里只有一行数据
const通过索引查找了一次就找到了,也就是根据索引查询只有一条记录,
eq_ref唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。主键关联查询(主键或唯一索引扫描)
ref 非唯一性索引扫描,返回匹配某个单独值得所有行
range只检索给定范围的行,使用一个索引来选择行 (between,in, <,>),
index 当查询的结果全为索引列,扫描所有索引文件,也是全表扫描的一种
all 全表扫描
key:实际使用的索引,possible_keys:可能用到的key
key_len:索引中使用的字节数,索引字段的最大长度,不是实际长度,长度越短越好。判断索引是否都用到了,char 和 varchar跟字符编码有密切关系 (latin1 占1个字节,gbk占2个,utf8占3个),允许为null 加1字节 varchar需要多加2个字节
ref:显示索引的哪一列被使用了,
rows:大致估算需要读取的行数
extra:包含不适合在其他列显示的信息,但很重要的信息
using filesort:对数据使用一个外部索引排序,而不是按照表内的索引排序进行读取。
using temporary:使用了临时表保存中间结果,常见于排序与分组
using index:使用了覆盖索引,避免访问了表的数据行
using where:使用了条件查询
using join buffer: 使用了join
impossible where:不使用条件查询 比如:where 1=2
10、策略
策略一、尽量全值匹配
策略二、最佳左前缀法则,查询从索引的最左列开始并且不跳过索引中的列
策略三、不在索引列上做任何操作
策略四、范围条件放最后,范围查询会导致范围查询之后的索引失效
策略五、覆盖索引尽量用 只查询索引的列
策略六、不等于要甚用 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描,但是覆盖索引也会用索引
策略七、Null/Not null 对索引有影响,,可以为空的字段is null可以用索引,is not null 不用索引, 但是覆盖索引都也会用索引,
策略八、like查询要当心 XXX%索引不会失效 但是覆盖索引都也会用索引,
策略九、字符类型加引号
策略十、OR改UNION效率高 用or覆盖索引都也会用索引
11、insert语句优化;
l 提交前关闭自动提交
l 尽量使用批量insert语句
l 可以使用MyISAM存储引擎
12、LOAD DATA INFLIE;
使用LOAD DATA INFLIE ,比一般的insert语句快20倍
select * into OUTFILE 'D:\\product.txt' from product_info 将表中的数据导到文件中
load data INFILE 'D:\\product.txt' into table product_info 将文件中的数据导入表中