mysql优化

发表于 |

#mysql 优化策略

目前看到喜欢的mysql优化策略来自美团点评技术团队

防身的mysql优化策略：

  1. 索引

    1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

    2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

    3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

    4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
    5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

  2. 更省力的索引检查
    还是美团点评技术团队的case：

用例如下，适合对mysql优化无力深入的。。。。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
./sqladvisor -h dev-be-mysql.co904fphklgb.ap-southeast-1.rds.amazonaws.com -P 3306 -u langapi -p '!ang!iv*b*' -d db_billing -q "SELECT ca.tid, ca.pfid, ca.cash, ca.gold, ca.order_time, ca.status, ca.op_id, ca.op_time, ca.to_account, ch.name, ch.phone, ch.birthday, ch.address, ch.id_card_address, ch.id_card_img_0, ch.id_card_img_1, ch.bank_card_img, ch.bank_code, ch.bank_name, ch.email, ch.bank_branch_name, cr.company_id, cp.company AS company, ch.cashier_type, ch.op_id AS cashier_op_id FROM tb_cashier AS ca LEFT JOIN tb_cashier_hot AS ch ON ca.tid = ch.tid LEFT JOIN tb_company_relationship AS cr ON cr.pfid = ca.pfid LEFT JOIN tb_company AS cp ON cp.id = cr.company_id LIMIT 100" -v 1
2017-05-12 17:48:12 31770 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `ca`.`tid` AS `tid`,`ca`.`pfid` AS `pfid`,`ca`.`cash` AS `cash`,`ca`.`gold` AS `gold`,`ca`.`order_time` AS `order_time`,`ca`.`status` AS `status`,`ca`.`op_id` AS `op_id`,`ca`.`op_time` AS `op_time`,`ca`.`to_account` AS `to_account`,`ch`.`name` AS `name`,`ch`.`phone` AS `phone`,`ch`.`birthday` AS `birthday`,`ch`.`address` AS `address`,`ch`.`id_card_address` AS `id_card_address`,`ch`.`id_card_img_0` AS `id_card_img_0`,`ch`.`id_card_img_1` AS `id_card_img_1`,`ch`.`bank_card_img` AS `bank_card_img`,`ch`.`bank_code` AS `bank_code`,`ch`.`bank_name` AS `bank_name`,`ch`.`email` AS `email`,`ch`.`bank_branch_name` AS `bank_branch_name`,`cr`.`company_id` AS `company_id`,`cp`.`company` AS `company`,`ch`.`cashier_type` AS `cashier_type`,`ch`.`op_id` AS `cashier_op_id` from (((`db_billing`.`tb_cashier` `ca` left join `db_billing`.`tb_cashier_hot` `ch` on((`ca`.`tid` = `ch`.`tid`))) left join `db_billing`.`tb_company_relationship` `cr` on((`cr`.`pfid` = `ca`.`pfid`))) le
2017-05-12 17:48:12 31770 [Note] 第2步：开始解析join on条件:ca.tid=ch.tid 
2017-05-12 17:48:12 31770 [Note] 第3步：开始解析join on条件:cr.pfid=ca.pfid 
2017-05-12 17:48:12 31770 [Note] 第4步：开始解析join on条件:cp.id=cr.company_id 
2017-05-12 17:48:12 31770 [Note] 第5步：开始选择驱动表,一共有1个候选驱动表 
2017-05-12 17:48:12 31770 [Note] explain select * from tb_cashier 
2017-05-12 17:48:12 31770 [Note] 第6步：候选驱动表tb_cashier的结果集行数为:182 
2017-05-12 17:48:12 31770 [Note] 第7步：选择表tb_cashier为驱动表 
2017-05-12 17:48:12 31770 [Note] 第8步：表tb_cashier 的SQL太逆天,没有优化建议 
2017-05-12 17:48:12 31770 [Note] 第9步：开始验证 字段tid是不是主键。表名:tb_cashier_hot 
2017-05-12 17:48:12 31770 [Note] show index from tb_cashier_hot where Key_name = 'PRIMARY' and Column_name ='tid' and Seq_in_index = 1 
2017-05-12 17:48:12 31770 [Note] 第10步：字段tid不是主键。表名:tb_cashier_hot 
2017-05-12 17:48:12 31770 [Note] 第11步：开始验证 字段tid是不是主键。表名:tb_cashier_hot 
2017-05-12 17:48:12 31770 [Note] show index from tb_cashier_hot where Key_name = 'PRIMARY' and Column_name ='tid' and Seq_in_index = 1 
2017-05-12 17:48:12 31770 [Note] 第12步：字段tid不是主键。表名:tb_cashier_hot 
2017-05-12 17:48:12 31770 [Note] 第13步：开始验证表中是否已存在相关索引。表名:tb_cashier_hot, 字段名:tid, 在索引中的位置:1 
2017-05-12 17:48:12 31770 [Note] show index from tb_cashier_hot where Column_name ='tid' and Seq_in_index =1 
2017-05-12 17:48:12 31770 [Note] 第14步：开始输出表tb_cashier_hot索引优化建议: 
2017-05-12 17:48:12 31770 [Note] Create_Index_SQL：alter table tb_cashier_hot add index idx_tid(tid) 
2017-05-12 17:48:12 31770 [Note] 第15步：开始验证 字段pfid是不是主键。表名:tb_company_relationship 
2017-05-12 17:48:12 31770 [Note] show index from tb_company_relationship where Key_name = 'PRIMARY' and Column_name ='pfid' and Seq_in_index = 1 
2017-05-12 17:48:12 31770 [Note] 第16步：字段pfid不是主键。表名:tb_company_relationship 
2017-05-12 17:48:12 31770 [Note] 第17步：开始验证 字段pfid是不是主键。表名:tb_company_relationship 
2017-05-12 17:48:12 31770 [Note] show index from tb_company_relationship where Key_name = 'PRIMARY' and Column_name ='pfid' and Seq_in_index = 1 
2017-05-12 17:48:12 31770 [Note] 第18步：字段pfid不是主键。表名:tb_company_relationship 
2017-05-12 17:48:12 31770 [Note] 第19步：开始验证表中是否已存在相关索引。表名:tb_company_relationship, 字段名:pfid, 在索引中的位置:1 
2017-05-12 17:48:12 31770 [Note] show index from tb_company_relationship where Column_name ='pfid' and Seq_in_index =1 
2017-05-12 17:48:12 31770 [Note] 第20步：索引(pfid)已存在 
2017-05-12 17:48:12 31770 [Note] 第21步：开始验证 字段id是不是主键。表名:tb_company 
2017-05-12 17:48:12 31770 [Note] show index from tb_company where Key_name = 'PRIMARY' and Column_name ='id' and Seq_in_index = 1 
2017-05-12 17:48:12 31770 [Note] 第22步：字段id是主键。表名:tb_company 
2017-05-12 17:48:12 31770 [Note] 第23步：表tb_company 经过运算得到的索引列首列是主键,直接放弃,没有优化建议 
2017-05-12 17:48:12 31770 [Note] 第24步: SQLAdvisor结束!