MySQL高级丶
文件
- frm文件：存放表结构
- myd文件：存放表数据
- myi文件：存放表索引
- 二进制日志文件log-bin
- 错误日志log-error
- 查询日志log
逻辑架构
分层可拔插（存储引擎也是可插拔的）
- 连接层
- 服务层
- 引擎层
- 存储层
存储引擎
show engines;
show variables like '%storage_engine%';
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一行记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存绅士数据 | 不仅缓存索引，还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
索引优化
索引是高效获取数据的数据结构。排序&查询
# 显示索引
show index from tbl_emp;
# 删除索引
alter table table_name drop index index_name
# 删除主键索引
alter table table_name drop primary key
#######################
# 四种索引
#######################
# 1.普通索引
alter table table_name add index index_username (username)
# 2.唯一索引 (字段不允许重复，可有多个NULL)
alter table table_name add unique index_username (username)
# 3.主键索引 (字段不允许重复,不允许NULL)
alter table table_name add primary key (username)
# 4.全文索引
alter table table_name add fulltext index_username (username)
索引分类：单值索引、唯一索引、复合索引
索引结构：BTree索引、Hash索引、full-text索引、R-Tree索引
需要建索引的情况：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 单键索引/组合索引的选择问题（高并发下倾向创建组合索引）
- 查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度（order by）
- 查询中统计或者分组字段
不要建索引的情况：
- 表记录太少（三百万）
- 经常增删改的表/频繁更新的字段不适合创建索引
- where条件里用不到的字段不要创建索引
- 数据重复且分布平均的字段（索引选择性越接近于1（比如性别字段的另一个极端），效率越高）
性能分析
- Mysql Query Optimizer查询优化器
- MySQL常见瓶颈
- CPU：CPU饱和一般发生在数据装入内存或从磁盘上读取数据的时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件性能瓶颈：top、free、iostat和vmstat查看系统的性能状态
- Explain执行计划
- 表的读取顺序（id）
- 数据读取操作的操作类型（select_type）
- 哪些索引可以使用（possible_keys）
- 哪些索引被实际使用（key）
- 表之间的引用（ref）
- 每张表有多少行被优化器查询
Explain执行计划字段解释
- id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- id相同，（table）执行顺序由上到下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id同时存在相同与不同的情况，相同的部分，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
- select_type：
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询就被标记为PRIMARY
- SUBQUERY：在select或where列表中包含了子查询
- DERIVED：在from列表中包含的子查询标记为DERIVED（衍生），MySQL会递归查询这些子查询，把结果放在临时表里
- UNION：若第二个select出现在union之后，则标记为UNION；若union包含在from子句的子查询中，外层select将被标记为DERIVED
- UNION RESULT：从UNION表获取结果的select
- table：显示这一行的数据是关于哪张表的
- type：访问类型，最好到最差：system>const>eq_ref>ref>range>index>ALL
- system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
- const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般是在where语句中出现between、<、>、in等查询，这种范围性索引扫描比全表扫描要好。
- index：遍历整个索引，比全表扫描要好点
- ALL：最差，全表扫描，考虑建索引
- possible_keys：显示可能应用在这张表中的索引，查询涉及到的字段上若存在索引，则将索引列出（不一定被查询实际使用）
- key：实际使用的索引（查询中若使用了覆盖索引，则该索引仅出现在key列表中）
- key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精度的情况下，长度越短越好。显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
- ref：显示索引的哪一列被使用了，如果可能，是一个常数，哪些列或常量被用于查找索引列上的值。
- rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的函数
- Extra：剩下的执行情况就写在这个字段里边（十分重要的额外信息）
- using filesort：文件排序，九死一生！mysql会对数据使用一个外部的索引排序，而不是按照表内的索引数据进行读取。MySQL中无法利用索引完成的排序操作称为”文件排序“
- using temporary：新建了内部的临时表，十死无生。使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于order by和group by。（复合索引的个数和顺序要严格一致）
- using index：好信号！表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。覆盖索引：查询的列与所建的复合索引吻合。
- using where
- using join buffer
- impossible where
- select table optimized away
- distinct
索引分析案例
Explain分析执行顺序
索引单表优化案例
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL ,
`views` INT(10) UNSIGNED NOT NULL ,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
insert into `article`(author_id,category_id,views,comments,title,content) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
查询category_id为1且comments大于1的情况下views最多的autho_id
索引两表优化案例
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
# 插入数据略（见文末）
左连接，索引建在右表；右连接，索引建在左表；左右位置可以调换
select * from book left join class on book.card=class.card
explain select * from book left join class on book.card=class.card
alter table `class` add index Y(`card`)
drop index Y on `class`
索引三表优化案例
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;
# 插入数据略（见文末）
这种情况怎么处理
explain select * from class left join book on class.card=book.card left join phone on class.card=phone.card;
alter table `book` add index Y(`card`)
alter table `phone` add index Z(`card`)
结论：
- 尽可能减少Join语句中NestedLoop的循环总次数（永远用小的结果集驱动大的结果集小表驱动大表）
- 优先优化NestedLoop的内层循环
- 保证join语句中被驱动表上join条件字段已经被索引
- 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置
索引失效
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
insert into staffs(NAME,age,pos,add_time) values('z3',22,'manager',NOW());
insert into staffs(NAME,age,pos,add_time) values('July',23,'dev',NOW());
insert into staffs(NAME,age,pos,add_time) values('2000',23,'dev',NOW());
alter table `staffs` add index idx_nameAgePos(`name`,age,pos);
CREATE TABLE tbl_user(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into tbl_user(NAME,age,email) values('1aa1',21,'b@163.com');
insert into tbl_user(NAME,age,email) values('2aa2',222,'a@163.com');
insert into tbl_user(NAME,age,email) values('3aa3',265,'c@163.com');
insert into tbl_user(NAME,age,email) values('4aa4',21,'d@163.com');
索引失效的情况
- 全值匹配我最爱：索引中的列和where中的条件刚好重合
- 最佳左前缀法则：根据复合索引，从左到右写where条件，最左边的不能少（带头大哥不能死，中间兄弟不能断）
- 不在索引列上做任何操作（计算，函数，自动或手动类型转换），会导致索引失效而转向全表扫描
- 不能使用索引中范围条件右边的列：and b>0用了范围条件了，b后面的c索引就失效了
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
- mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
- is null，is not null也无法使用索引
- like以通配符%开头，MySQL索引会失效而变成全表扫描（想解决用覆盖索引）
- 字符串不加单引号索引失效
- 少用or，用它来连接时索引会失效
定值（常量）、范围（后会失效）、排序，一般order by是给个范围，group by基本都需要进行排序，会产生临时表
一般性建议
- 对于单键索引，尽量选择针对当前索引query过滤性更好的索引
- 在选择组合索引时，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
- 在选择组合索引时，尽量选择可能包含当前query中的where子句中更多字段的索引
- 尽可能通过分析系统信息和调整query的写法来达到选择合适索引的目的
慢SQL处理流程
- 慢查询的开启并捕获
- explain + 慢SQL分析
- Show Profile查询SQL在mysql服务器里面的执行细节和生命周期情况
- sql数据库服务器的参数调优
查询截取分析
查询优化
1、永远小表驱动大表
2、order by关键字优化（为排序使用索引）
-
尽量使用index方式排序，避免使用filesort方式排序
-
filesort有两种算法：单路排序 和 双路排序
- 双路排序：老版本的mysql，两次扫描磁盘
- 单路排序：有风险
-
orderby时select*是大忌
-
数据库服务器参数设置sort_buffer_size/max_length_for_sort_data
3、group by关键字优化
- 实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大sort_buffer_size/max_length_for_sort_data
- where高于having，能写在where中的不要写在having中
慢查询日志
# 查看开启状况
show variables like '%slow_query_log%';
# 查看当前多少秒算慢sql
show variables like '%long_query_time%';
# 开启
set global slow_query_log=1;
# 设置慢的时间
set global long_query_time=3;
# 记录日志
select sleep(4);
# 另外mysqldumpslow工具
批量数据脚本
往表里插入一千万条数据
# 函数及存储过程（见文末）
show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators=1;
delimiter ;# 恢复设置
select * from dept;
call insert_dept(100,10);
select * from emp;
call insert_emp(100001,500000);
Show Profile
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优测量
# 默认是关闭，使用前需要开启
show variables like 'profiling';
set profiling=on;
show profiles;
show profile cpu,block io for query 3;
# 出现以下4项必须优化
# converting HEAP to MyISAM
# Creating tmp table
# Copying to tmp on disk
# locked
全局查询日志
永远不要在生产环境开启此功能
配置启用
# 在MySQL的my.cnf中，设置如下
# 开启
general_log=1
# 记录日志文件的路径
general_log_file-/path/logfile
# 输出格式
log_output-FILE
编码启用
set global general_log=1;
set global log_output='TABLE';
select * from mysql.general_log;
MySQL锁机制
读锁（共享锁）/写锁（排它锁）/表锁（偏读）/行锁（偏写）/页锁
- 表锁MyISAM
# 简而言之：读锁会阻塞写，但是不会阻塞读；而写锁则会把读和写都阻塞。
select * from mylock;
lock table mylock read ,book write;
show open tables;
unlock tables;
lock table mylock read;
select * from mylock;
# 当前session不能操作，其他session会阻塞等待，直到unlock tables;
update mylock set name='a2' where id=1;
select * from book;
lock table mylock write;
update mylock set name='a3' where id=1;
show status like 'table%';# 反映锁竞争的情况
- 行锁InnoDB
# 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；（InnoDB与MyISAM最大的不同：一是支持事务，二是采用了行级锁）
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
SET autocommit=0;# 不自动提交事务，commit之前就会自动锁住操作的行
select * from test_innodb_lock;
update test_innodb_lock set b='4001' where a=4;
COMMIT;
-
无索引行锁升级为表锁（varchar不加单引号索引失效进而行锁升级为表锁）
-
间隙锁危害（id不连续时，范围操作时，可能影响其他session执行）
-
如何锁定一行
SET autocommit=0;
begin；
select * from test_innodb_loc where a=8 for update；//锁定行
commit;
- 查看行锁情况
show status like 'innodb_row_lock%';
- 行锁相关优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理涉及索引，尽可能缩小锁的范围
- 尽可能使用较少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能低级别事务隔离
主从复制
slave会从master读取binlog来进行数据同步
- master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件binary log events
- slave将master的binary log events拷贝到它的中继日志（relay log）
- slave重做中继日志中的事件，将改变应用到自己的数据库中，MySQL复制是异步的且串行化的
配置文件进行配置主从复制（授权；show master/slave status;stop slave;）
击石乃有火，不击元无烟！！
