mysql 优化
一、 表的优化与列类型选择
1. 表的优化
1.1 定长与变长
核心且常用的字段，宜建成定长放在一个表中
而varchar、text、blob等变长类型的字段，适合放在另外的表中，用主键进行关联
1.2 常用字段和不常用字段要分离
根据场景将不常用字段单拆出来
1.3 在一对多，需要关联统计的字段上添加冗余字段
2. 列类型的选择
2.1 字段类型选择优先级
int > date、time > enum、char > varchar > blob、text
比如：tinyint和char(1)，从空间上讲，都占一个字节，但是考虑到order by的时候，字符串要考虑字符集和校对集(就是排序规则)
time : 定长、运算快
enum : 内部采用整形存储
varchar : 不定长，要考虑字符集的转换和排序时的校对集
text/blob : 无法使用内存临时表(排序等操作只能在磁盘上进行)
2.2 够用就行，不要慷慨
以年龄为例，tinyint unsigned not null，可以存储255岁，足够
以varchar(10)和varchar(300)为例，虽然存的内容是一样的，但是在表联查时，varchar(300)需要花更多内存
2.3 尽量避免用NULL
NULL不利于索引，要用特殊的字符标识
二、 索引优化
索引能提高查询、排序、分组统计的速度
1. 索引类型
1.1 B-tree索引
myisam、innodb中都默认采用b-tree索引
1.2 hash索引
在memory引擎里默认是hash索引
hash索引的问题：
① hash的结果随机，如果是在磁盘上放数据，位置随机性比较大
② 无法对范围查询优化
③ 无法引用前缀索引，比如在b-tree中，某列上“helloworld”可以加索引查询，则"hello"也可以加索引查询，但是hash索引就不行
④ 排序也无法优化
⑥ 必须回行，也就是说，通过索引拿到数据位置必须回到数据表中取数据
2. B-tree的常见误区
2.1 在B-tree索引的常用列上都加上索引
例如：where cat_id = 3 and price > 50
误：cat_id和price列上都加索引
原因：只能用上cat_id或者price的索引，因为是独立的索引，只能用上一个
2.2 在多列上建立索引后查询哪个列都将发挥作用
误：要满足左前缀规则
例如：a-b-c三列加联合索引
where a = 1 and b > 7 and c = 8 a索引能用、b索引能用、c索引用不到
where a = 1 and b like 'aa%' and c = 10 a索引能用、b索引能用、c索引用不到
例：假设某个表有一个联合索引(c1,c2,c3,c4)以下——只能使用该联合索引的c1,c2,c3部分
A. where c1=1 and c2=2 and c4>3 and c3=4
B. where c1=1 and c2=2 and c4=3 order by c3
C. where c1=1 and c4= 2 group by c3, c2
D. where c1=1 and c4=2 order by c2, c3
E. where c1=1 and c2=2 and c4=3 order by c2, c3
解答：
A. 由于该语句会被mysql优化成 where c1=x and c2=x and c3=x and c4>x，所以所有索引都可以用到
B. c1、c2上的索引用于查询，c3上的索引用于排序，c4的索引用不到
C. c1上的索引用于查询，c2、c3由于顺序反了，所以索引不能用于分组，c4上的索引也用不到
D. c1上的索引用于查询，c2、c3上的索引用于排序，c4上的索引用不到
E. c1、c2上的索引用于查询，c3上的索引用于排序，c4上的索引用不到(c2上的查询条件已经使用了等号，说明被严格限制，排序是不必要的，所以mysql会优化掉后面的 order by c2)
实践：
准备数据
create table t(
c1 tinyint(1) not null default 0,
c2 tinyint(1) not null default 0,
c3 tinyint(1) not null default 0,
c4 tinyint(1) not null default 0,
c5 tinyint(1) not null default 0,
index c1234(c1, c2, c3, c4)
);
insert into t values (1, 3, 5, 6, 7), (2, 3, 9, 8, 3), (4, 3, 2, 7, 5);
A 的执行计划
mysql> explain select * from t where c1=1 and c2=2 and c4>3 and c3=4 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: c1234
key: c1234
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
建表的时候每列是1个长度，执行计划里key_len:4说明用到了4个字段上的索引，加起来是4
B 的执行计划
mysql> explain select * from t where c1=1 and c2=2 and c4=3 order by c3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
filtered: 33.33
Extra: Using index condition
C 的执行计划
mysql> explain select c3, c4 from t where c1=1 and c4= 2 group by c3, c2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
filtered: 33.33
Extra: Using where; Using index; Using temporary; Using filesort
D 的执行计划
mysql> explain select * from t where c1=1 and c4=2 order by c2, c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
filtered: 33.33
Extra: Using index condition
E 的执行计划
mysql> explain select * from t where c1=1 and c2=2 and c4=3 order by c2, c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
filtered: 33.33
Extra: Using index condition
3. 聚簇索引与非聚簇索引
myisam 的数据和索引是分开的，都是一个单独的文件，查找数据的时候先在索引树上找，找到后再到数据上拿数据。这就是非聚簇索引
myisam 主索引和次级索引都指向行在磁盘上的位置
innodb索引的叶子节点比较大，上面有索引对应的整条记录，所以查找数据的时候找到了索引后立马能拿到对应的数据，不用再回行到数据文件去拿数据。这就是聚簇索引
innodb 主索引直接存该行文件，次级索引指向主键的引用
innodb 如果没有主键(primary key)，则会用unique key做主键，如果没有unique key，则系统生成一个内存的rowid做主键
聚簇索引
优势：根据主键索引查询条目比较少时，不用回行
劣势：不规则数据插入时，造成频繁的页分裂
页分裂
聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂
4. 索引覆盖
如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再查找数据。这种查询速度非常快，称为索引覆盖
实例分析：
create table A(
id varchar(64) primary key,
ver int,
...
index idx_id_ver
)
1000条数据，表有几个很大的字段——text(3000)
为什么 select id from A order by id 比较慢，而 select id from A order by id, ver比较快？
思路：inndb聚簇索引和myisam索引的不同，索引覆盖
分析：
1. myisam在两个索引上查数据的时候由于索引覆盖，速度不会有明显差异
2. innodb表因为聚簇索引，主键索引要在磁盘上跨多个块，导致速度慢
3. 即使innodb引擎，如果没有那几个大的字段，两个语句的查询速度也不会有明显差异
5. 理想的索引
5.1 查询频繁
5.2 区分度高
5.3 长度小
针对列中的值，从左往右截取部分来建索引
① 截的越短，重复读越高，区分度越小，索引效果越不好
② 截的越长，重复读越低，区分度越高，索引效果越好，但带来的影响越大——增删改变慢，并且影响查询速度
所以要在区分度和长度上取得一个平衡。惯用手法：截取不同长度，并测试其区分度
区分度公式：count(distinct col)/count(*)
一般要求join的字段上索引的区分度在0.1以上
对dict表的word字段的前N个字符加索引时的区分度统计sql：
mysql> select (select count(distinct left(word, 3) from dict ) / (select count(*) from dict))
5.4 尽量覆盖常用字段
5.5 索引列不能参与计算
6. 索引实战
6.1 对于左前缀不易区分的列如何建立索引
如url列：
http://www.baidu.com
http://www.zixue.it
① 可以把内容倒置，这样区分度高
② 伪hash索引效果
在存url的时候同时存url的crc32值，crc32是一种哈希算法，能把字符串算为32位整数，这时候对urlcrc做索引的效率会非常高
id | url | urlcrc
----|------|----
foo | foo | foo
6.2 多列索引
多列索引的考虑因素：①列的查询效率 ②列的区分度 ③实际业务场景
有时候某列A的分区度比较高，但是实际的查询场景中另一列B在查询条件中要优先于A，这时候就不能只考虑区分度，而要结合实际场景
7. 索引与排序的关系
1.
对于覆盖索引，直接在索引上查询时就是有序的，using index
在innodb引擎中，沿着索引字段排序，是自然有序的
对于myisam引擎，如果按某索引字段排序，如id，但取出来的字段中，有未索引字段，它的做法不是 【索引->回行 …… 索引 -> 回行】，而是先取出所有行再进行排序
2.
先取出数据，形成临时表做filesort(文件排序，文件可能在磁盘，也可能在内存中)
8. 重复索引与冗余索引
重复索引：在同一个列或者在顺序相同的几个列上建立索引。重复索引没有任何用处，只会增大索引文件、拖慢更新速度，应该去掉
冗余索引：指两个索引覆盖的列有重叠
例如文章与标签表：
id | artid | tag
----|--------|----
1 | 1 | PHP
2 | 1 | JAVA
3 | 2 | MySql
4 | 2 | Oracle
在实际使用中，有 tag -> atrid 和 atrid ->tag 的查询：
select tag from tt where artid = 1;
select artid from tt where tag = 'PHP';
索引：这时候可以分别给tag和artid单独建立索引。但是查询的时候会产生回行
优化：分别建立tag_artid和artid_tag两个联合索引，达到索引覆盖的目的，using index
9. 索引碎片与维护
在长期的数据更改过程中，索引文件和数据文件将产生空洞，形成碎片。我们可以通过nop操作来修改表
比如：表的引擎为innodb，alter table xxx engine innodb
optimize table xxx，也可以修复
注意：修复表的数据及索引碎片，就会把所有数据文件重新整理一遍，使之对齐，这个过程，如果数据量比较大，也是很耗费资源的操作。如果一个表的update比较频繁，可以按周/月来修复，如果不频繁，可以更长的周期来修复
10.索引优化策略
1. 索引列上不能使用表达式或函数
2. 前缀索引和索引列的选择性
mysql中b-tree引擎对索引的键值大小是有限制的，innodb索引键的大小不能超过767字节，myisam索引键的大小不能超过1000字节。所以比较长的字符串列上建立的索引可能会比较大，进而影响到查询效率，所以mysql支持对字符串的前缀建立索引
eg: CREATE INDEX idx_name ON tb (col_name(n))
3. 联合索引如何选择索引列的顺序
① 经常会被使用的列优先
② 区分度高的列优先
③ 宽度小的列优先(IO小，效率高)
4. 覆盖索引：包含了所有需要查询的列的索引
覆盖索引优点：
① 优化缓存，减少磁盘io
② 减少随机io，变随机io操作为顺序io操作
③ 避免innodb索引回行
④ 避免myisam索引进行系统调用
注：innodb二级索引会自动加入主键，以下这种查询会使用到索引覆盖
mysql> explain select actor_id, last_name from actor where last_name = 'joe' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 1
filtered: 100.00
Extra: Using index
5. 模拟hash索引
eg:
给 film 表中格式为varchar(255)的 title 字段新增一个hash列 title_crc32，可以给title_crc32加索引进行查询
缺点：只能处理键值全值匹配的查询
11. 使用索引优化查询
1. 使用索引来优化排序
① 索引列的顺序和order by子句顺序完全一致
② 索引中所有列的方向(升序、降序)和order by子句完全一致
③ order by中的字段全部在关联表中的第一张表中
2. 索引优化锁
eg:
select * from emp where emp_no = '111' for update; --排它锁
这个sql在 emp_no 上没有索引的时候会锁整个表，而加上索引只锁定当前行
三、 SQL语句优化
1. 思路
1.1 sql的时间花在了哪里 ———— 等待时间、执行时间
1.2 sql的执行时间又花在了哪里 ———— a.查找—沿着索引/全表扫描 b.取出—查到行后取出数据
查询快 —— 索引(联合索引的区分度、长度)
取的快 —— 索引覆盖
传的少 —— 传输更少的行和列
切分查询 —— 例：查1000行，没100条作为一个单位
分解查询 —— 按逻辑把多表关联查询分成多个简单的sql
1.3 sql优化思路 —— 不查 > 少查(精准数据、少取行) > 高效查(索引)
2. explain的列分析
四、binlog
1. binlog日志有两种格式【binlog_format】
1.
statement(基于段的格式)
日质量相对较小，但是容易引起主备复制的数据不一致
2.
row(基于行的日志格式)
基于行的日志在数据发生变动的时候会记录所有受影响行的数据修改，基于段的格式只记录该条语句。
因此，在主备复制的时候row只复制一行只影响一行，而statement的执行会持续比较长的时间，所以row格式的复制效率会更高
而且记录的日质量比较大
可以修改参数：binlog_row_image = [FULL | MINIMAL | NOBLOB]
3.
mix(集statement和row)
根据sql语句确定使用哪种格式
====================================================================================================================================================
--TYPE--------------------------------------------------------------------------------------------------------
system：表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引
因为只匹配一行数据，所以很快，如果将主键置于where列表中，mysql就能将该查询转换为一个const
eg：explain select * from (select * from a where id = 1) t
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行
然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引
一般就是在where语句中出现between、、in等的查询
这种范围扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
index：full index scan，index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小
也就是说，all和index都是读全表，但是index是从索引中读取，而all是从硬盘读取
all：full table scan，将遍历全表以找到匹配的行
--KEY_LEN--------------------------------------------------------------------------------------------------------
表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据标定义计算而得，不是通过表内检查出的
--REF------------------------------------------------------------------------------------------------------------
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
--EXTRA----------------------------------------------------------------------------------------------------------
using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
mysql中无法利用索引完成的排序操作称为“文件排序”
eg：create table t1(col1 varchar(20), col2 varchar(20), col3 varchar(20), key col1_col2_col3(col1, col2, col3))
explain select col1 from t1 where col1 = 'ac' order by col3
优化：select col1 from t1 where col1 = 'ac' order by col2, col3
using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表
常见于order by和分组查询group by
eg：create table t1(col1 varchar(20), col2 varchar(20), col3 varchar(20), key col1_col2(col1, col2))
explain select col1 from t1 where col1 in ('aa', 'bb', 'cc') group by col2
优化：select col1 from t1 where col1 in ('aa', 'bb', 'cc') group by col1, col2
using index：表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错
如果同时出现using where，表明索引被用来执行索引键值的查找
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
using where：使用了where进行过滤
using join buffer：使用了连接buffer
impossible where：where子句的值总是false，不能用来获取元组
range类型查询字段后面的索引无效
left join条件用于确定如何从右表搜索行，左边一定都有，所以右边是关键点，一定需要建立索引
小表驱动大表
在无法保证被驱动表的join条件字段被索引且内存充足的情况下，不要吝啬JoinBuffer的设置
--索引失效-------------------------------------------------------------------------------------------------------
1.全值匹配我最爱
2.最佳左前缀法则
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
4.存储引擎不能使用范围条件右边的列
5.尽量使用索引覆盖，减少select *
6.mysql在使用不等于(!=、<>)的时候无法使用索引导致全表扫描
7.is null，is not null也无法使用索引
8.like以通配符开头，mysql索引失效会全表扫描。一般采用索引覆盖的方式提高'%xxx'的查询效率
9.字符串不加单引号索引失效
10.少用or，用它来连接时索引会失效
--order by 优化--------------------------------------------------------------------------------------------------
using file sort的时候会有两种排序策略
mysql4.1以前是双路排序，读取行指针和order by列，然后扫描已经排好序的列表重新读取需要的数据输出(即：从磁盘读取排序字段，在buffer排序，再从磁盘取出其他字段)
单路排序。从磁盘读取所有需要的列，按照order by列在buffer上排序，然后扫描排序后的列表输出
优缺点：单路排序避免了第二次读取数据，并且把随机io变成顺序io，但是它会使用更多的空间，糟糕的是，如果数据量太大，超出sort_buffer，则需要多次进行数据读取，会导致更大的开销
优化：尝试增大sort_buffer_size、max_length_for_sort_data
--group by 优化--------------------------------------------------------------------------------------------------
--慢查询---------------------------------------------------------------------------------------------------------
long_query_time
show variables like '%slow_query_log%'
set global slow_query_log = 1 需要重新连接或者新开会话才能看到设置，针对当前数据库，重启数据库后失效。永久生效要改my.conf文件中的slow_query_log、slow_query_log_file
show global status like '%slow_queries%'
mysqldumpslow:
s：按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：返回前面多少条的数据
g：后面搭配正则表达式匹配模式
得到返回记录集最多的10个sql：
mysqldumpslow -s r -t 10 /slowlog.log
得到访问次数最多的10个sql：
mysqldumpslow -s c -t 10 /slowlog.log
得到按照时间排序的前10条里面含有左链接的sql：
mysqldumpslow -s t -t 10 -g "left join" /slowlog.log
另外，建议在使用一些美丽时结合|和more使用，否则可能出现爆屏情况：
mysqldumpslow -s r -t 10 /slowlog.log | more
--show profile---------------------------------------------------------------------------------------------------
show variables like 'profiling'
set profiling = ON
show profile cpu, block io for query query_id
type: all
block io
context switches
cpu
ipc 发送和接收相关开销信息
memory
page faults 页面错误相关开销
source
swaps 交换次数相关开销
如果show profile结果出现如下四种结果，说明该sql存在问题比较大：
converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬
creating tmp table：创建临时表(拷贝数据到临时表，用完再删除)
copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
locked：
--mysql 锁-------------------------------------------------------------------------------------------------------
表锁：偏向myisam
(加了读锁之后自己只能读不能插改，也不能查询其他没有锁定的表，会报错，其他session也只能读，插改的时候会阻塞)
(加了写锁之后自己可以进行任何操作，但是不能操作其他没有锁定的表，会报错，其他session任何对当前表的操作都会被阻塞)
简而言之，读锁只阻塞写，写锁会阻塞读写
eg：lock mytable lock read;
show open tables; 查看哪些表被锁了
show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 162 |
| Table_locks_waited | 0 | Table_locks_waited：出现表级锁定争用而发生的等待次数，此值高则说明存在着比较严重的表级锁争用情况
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
此外，myisam是读写锁调度是写优先，所以myisam不适合做写为主的表引擎
行锁：偏向innodb引擎。开销大加锁慢，会出现死锁，粒度最小，冲突概率低，并发高
innodb和myisam的最大不同点：一是支持事务，二是支持行级锁
无索引行锁升级为表锁
间隙锁：当用范围条件而不是相等条件检索数据，并请求共享或者排它锁时，innodb会给符合条件的已有数据记录的索引项加行锁
对于键值在条件范围内但并不存在的记录，叫做间隙，该间隙也会给锁定
某些场景下会对系统性能造成很大伤害
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之前的引用
每张表有多少行被优化器查询
扫一扫
891
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
