Mysql 复合索引记录


Posted on September 14th 2014, 09:51


Mysql不用多介绍,很好很强大,这篇文章主要记录innodb中复合索引的引用, 本文中的缩影指的都是二级索引.

概述

索引在innodb中对缩短查询时间起到至关重要的作用, 有了索引我们可以不进行全表扫描而是对一小部分的数据进行扫描.随着业务的复杂性的增加, 单一的索引已经无法满足日常的需求.因此, 我们需要建立复合索引满足需求, 当然所有的优化必然有开销, 建立索引我们会消耗很多的磁盘空间, 同样也影响插入的速度.
innodb的索引是将索引的列和主键关联的, 所以每个索引都会拷贝一份主键!!!

场景

先来看看如下场景

create table testexplain (
  f0 varchar(10) primary key,
  f1 varchar(10),
  f2 varchar(10),
  f3 varchar(10) 
);

假使我们常用的查询sql如下

1. select * from testexplain f0 = 'xxx';  
2. select * from testexplain f1 = 'xxx' and f2 = 'xxxx';  
3. select * from testexplain f2 = 'xxx' and f3 = 'xxxx';  
4. select * from testexplain f1 = 'xxx' or f2 = 'xxxx';  
5. select * from testexplain f1 = 'xxx';  
6. select * from testexplain f2 = 'xxx';  

那么我们建立如下索引能满足要求

create index index_testexplain_f1_f2 on testexplain(f1,f2,f3);

分析

事实是否如此呢?我们需要mysql告诉我们.说到索引分析,我们必须要用到explain:

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+  
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |  
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+  
|  1 | SIMPLE      | testexplain | const | PRIMARY       | PRIMARY | 32      | const |    1 |       |  
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+

很明显走了主键索引, 和预期相同.

+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------------+------+-------------+  
| id | select_type | table       | type | possible_keys           | key                     | key_len | ref         | rows | Extra       |  
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------------+------+-------------+  
|  1 | SIMPLE      | testexplain | ref  | index_testexplain_f1_f2 | index_testexplain_f1_f2 | 66      | const,const |    1 | Using where |  
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------------+------+-------------+

同样也符合预期.

+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+  
| id | select_type | table       | type  | possible_keys | key                        | key_len | ref  | rows   | Extra                    |  
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+  
|  1 | SIMPLE      | testexplain | index | NULL          | index_testexplain_f1_f2_f3 | 99      | NULL | 481495 | Using where; Using index |  
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+

虽然mysql选择扫描了使用index, 但是依然遍历了481495行, 并没有起到加速的作用.因为mysql虽然没遍历全表但是遍历了全索引.

+----+-------------+-------------+-------+----------------------------+----------------------------+---------+------+--------+--------------------------+  
| id | select_type | table       | type  | possible_keys              | key                        | key_len | ref  | rows   | Extra                    |  
+----+-------------+-------------+-------+----------------------------+----------------------------+---------+------+--------+--------------------------+  
|  1 | SIMPLE      | testexplain | index | index_testexplain_f1_f2_f3 | index_testexplain_f1_f2_f3 | 99      | NULL | 481495 | Using where; Using index |  
+----+-------------+-------------+-------+----------------------------+----------------------------+---------+------+--------+--------------------------+

mysql选择扫描了全索引.

+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-------------+  
| id | select_type | table       | type | possible_keys           | key                     | key_len | ref   | rows | Extra       |  
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-------------+  
|  1 | SIMPLE      | testexplain | ref  | index_testexplain_f1_f2 | index_testexplain_f1_f2 | 33      | const |    1 | Using where |  
+----+-------------+-------------+------+-------------------------+-------------------------+---------+-------+------+-------------+

mysql选择了索引.

+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+  
| id | select_type | table       | type  | possible_keys | key                        | key_len | ref  | rows   | Extra                    |  
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+  
|  1 | SIMPLE      | testexplain | index | NULL          | index_testexplain_f1_f2_f3 | 99      | NULL | 481495 | Using where; Using index |  
+----+-------------+-------------+-------+---------------+----------------------------+---------+------+--------+--------------------------+

mysql选择扫描了全索引.

总结

由上面的测试结果我们可以看出原先的结论是不成立的, 也就是说要想使用复合索引必须按照一定的规则.查看mysql官方文档: 想要利用符合索引, sql语句的where条件必须是符合从左最匹配索引原则.这样翻译确实太抽象.
假使我们有上面的表结构, 要想利用复合索引, where语句的条件必须是f1,f2,f3|f1,f2|f1|f1,f3的形式(从而满足索引的顺序f1,f2,f3), 并且使用and, 条件的顺序可以乱序.or的话代表是两个不同的查询条件, mysql会使用index merge去优化查询, 简单说明就是两个不同的查询过程, 然后做merge.


Chrome Unsafe Port 浅析


Posted on August 10th 2014, 09:30


最近一段时间在和docker愉快的玩耍, 但卡在一个非常奇怪的问题上面--新建了一个container, 基于centos6的image上安装了nginx一个app, 把host的6001端口映射到container的80端口, 把host的6000端口映射到6000端口.

问题

开起这个container后, 在chrome中访问本地6001端口, 可以看到nginx的默认欢迎页面. 而访问6000端口则不能连接--是tcp无法建立连接的那种页面. 这就非常奇怪了.
用命令查看

docker port xxx 6000  

输出结果是绑定了本地的6000端口没有任何异议.可是为什么无法访问呢?

思路

如此只能用nc工具查看端口是否开起:

nc -v localhost 6000  

端口是有输出的, 这就意味着端口是开启的. 接下来只能看是不是nginx的配置问题了, 当然在部署docker的时候nginx.conf文件是经过nginx -t检验的. 这时候就得用curl命令来检验了.

curl http://localhost:6000  

由于nginx的配置了autoIndex on, 所以返回的页面中会有文件目录的内容. curl的结果是在预期内的, 也就是说docker的配置和nginx的配置是完全正确的.
是什么引发了这个问题?

方案

由表面证据可以看到, 区别在chrome和curl. 为什么这两个agent会有什么区别? 好吧, 身为一个web开发者, 必须立马打开chrome://net-internals/#events页面查看打开6000页面的时候发生了什么问题. 结果是看到了ERR_UNSAFE_PORT错误, 而这个错误不会在错误页面出现, 当然console里也看不到. 迅速google了一下, 尼玛这确实是chrome干的好事情, 在Chromium的源代码中确实内置这么一个功能--屏蔽一些已知的端口.
这是为了什么呢?

反思

经过一系列的搜罗, 网上大致给出的解释是出于安全的考虑. 那到底会有怎么样的安全问题呢?(web开发者要有安全意识啊!!!)

安全问题例子

假使我们有一个server listen在6000端口, 并接受request和response的模式, server也在防火墙后. 那么恶意攻击者可以怎么做才能伪造请求攻击server?(这里可以先思考几分钟, 看看有没有黑客的潜质!!!) |
|
|
|
|
|
|
|
|
|
|
好吧谜底揭晓:
假如有一个pc和server在同一个内网, 恶意攻击者通过建立恶意网站利用javacript调用模拟发送请求到内网的server, 从而达到攻击的目的. 虽然要实施这个步骤需要很多条件, 但是确实是可行的, 因为chrome处于对自身安全的考虑, 建立了一个黑名单列表, 禁止访问这些端口, 这就是上面所述问题的原因.

如何开放这个性质

chrome作为强大google的产品, 有入口当然会有出口, 既然默认是屏蔽的, 那么必须的可以解除这个屏蔽:

chorme --explicitly-allowed-ports=port1,port2,port3  

结论

虽然无意间踩到这个坑, 也学到了不少. 年轻的chrome在浏览器里是顶尖的. 当然我们web开发者在开发环境做端口选择的时候还是选一些10000以上的端口. 有兴趣的朋友还可以延伸阅读一下这篇wiki.


Mysql使用index基本原则


Posted on July 23rd 2014, 09:24


用了蛮久的mysql,竟然对如何优化index还没有掌握,今天闲下来看看这块东西,然后总结以下.这里所表述的mysql指的是innodb的engine.

mysql index分类

Mysql的index分为cluster index和secondary index, 可以翻译为聚簇索引和二级索引.所谓的聚簇索引是指主键栏作为索引值的索引, 而所有非聚簇索引则是二级索引.

cluster index

Mysql根据如下规则建立聚簇索引:

  • 如果有定义主键, 则使用主键建立索引
  • 如果没有定义主键, 选取第一个UNIQUE的栏建立索引
  • 如果以上两个条件均不满足, 则mysql默认建立一个隐藏的rowid作为建立索引的依据

secondary index

二级索引是建立在cluster index之上的索引, 它包含建立自身索引的列和主键, 因此, 主键过大会造成二级索引过大, 最终导致磁盘占用量变大.
Myql如果选择使用二级索引, 那么它先根据二级索引查找主键, 由于主键和数据在同一个页上, 从而加快了数据的查找和比较.

mysql如何使用index

Mysql首先根据查询语句做优化, 如果table的数据量很小(比如几条数据),那么mysql会选择遍历整个表.如果数据量很大, 它优先选择根据索引过滤后数据量较小的索引.那么我们建立索引索引的时候应该遵循哪些规则?

尽可能的覆盖查询语句中的查询条件

由于mysql可以选取部分index的列作为索引条件,因此如下两个查询条件可以共用同一个索引但是不要忘记, 增加列意味着容量的增加.

select * from db.tbl where c1 = 1 and c2 = 2;  

select * from db.tbl where c1 = 1;  

所以语句

create index inx_c1_c2 on db.tbl(c1, c2);  

工具

理论上的理解还不够, 现实的问题需要显示来解决, 所以在每次使用sql前, 可以用explain的看下使用的所以是不是我们所期望的, explain只能用于select语句.非select语句,比如update和delete, 我们可以把where语句后的条件放入select进行explain.


尝试Tmux


Posted on July 19th 2014, 19:41


如果不是无鼠标操作控就可以忽略这篇文章了.很高兴你也是个无鼠标操作控, 今天就来看看tmux的神奇魔力.

现状

在介绍tmux之前,还是先来说说场景--当我们需要开起多个ssh来观察不同服务器的状态的时候,很多个terminal将不得不被打开,如果你是window,那么你要很辛苦的拖动个个框保持满屏,或者也可以使用第三方的tab功能,而如果你是linux,我恐怕你也不得不这么做.当然如果你是利用类似xmonad等的titling的窗口管理器,这也是可以得到解决的.
目前,本人也是利用xmonad和urxvt来达到这个效果, 使用久了也会觉得这样是如此烦,每次想要在terminal里面干点别的事情就不得不打开新的terminal.

tmux--爽

首先, 我们需要确定的事情是tmux不是terminal!!!它只是terminal的管理器,更准确的说是terminal复用器.
有什么直接的好处呢?Tmux可以帮助我们解决在只打开一个terminal的时候,既可以管理多个ssh,还可以管理多个vi或者top.或许你会问,这个功能通过后台进程不是可以搞定么?回答是是的,但是tmux do better.

tmux概念

在使用tmux之前,我们需要理解tmux中的四个概念client,session,window,pane.

client

client指的是我们的terminal,也就是能直接和session打交道的部分.

session

session则是进程的集合,也就是我们所说的多个ssh或者vi.

window

window是session的具体展现,可以理解为我们能看到的session.

pane

pane是把window切割成多个部分,也就是说我们可以在一个window中看到多个ssh.

使用后感受

使用了tmux大概有3天, 总体感觉得心应手, 特别是session的概念, 让多个程序管理起来有序,清晰, 非常使用.
下面罗列下常用的组合(所有组合都是默认的均未特别设置过):

  1. ctrl+b + [, 进入scroll模式, 默认用emacs的按键来移动, 也可配置成vi的模式
  2. ctrl+b + [ 进入scroll模式后科使用space进入复制选择模式, 然后用enter复制选中的文本
  3. ctrl+b + :, 进入tmux的命令行, 比如可以重开session, killsession等
  4. ctrl+b + %, 新建pane并按垂直分割, ctrl+b + %, 水平新建pane
  5. ctrl+b + s, 查看所有session
  6. ctrl+b + ?, 查看按键绑定

MQ的尝试


Posted on May 15th 2014, 19:14


long long ago, 就听说过Message Queue(mq), 一直没去尝试, 毕竟用到这种工具的都是分布式的场景了. 这次碰到一个很适合的场景决定适用一把mq.

场景

这次的场景需要由一个总控端将特定的任务分配给执行段, 当执行端完成作业后, 将这个任务从总控端中剔除.这个分配的动作则是由执行端主动去总控端获取.
所以我把这个场景设计为三个部分:

  1. 总控端, 负责生成任务并插入mq, 并监测mq的长度, 保证适度的长度
  2. 执行端, 负责从mq中获取任务并执行, 当任务失败或者程序崩溃的时候, 刚才获取的任务则重新回归总控端, 交给其他执行端执行, 保证任务一定被完整的执行.
  3. mq, 负责任务必须被一个执行端完整的执行

选择MQ

从google搜索, 可以有很多mq, 比如RabbitMQ, ActiveMQ, MSMQ, ZeroMQ等等, 一时间还比较难选择.
首先可以从google搜索结果的排名上排除几个, 剩下RabbitMQ和ActiveMQ.然后下载build文件的时候, RabbitMQ只要4M左右的大小而ActiveMQ则要40M, 对于没有耐性的我果断选择了RabbitMQ.
再细看RabbitMQ的文档, 它是用erlang写的--看过一篇文章, 这门语言是Ericsson为了通信行业写的, 所以对于他的可靠性和高效比较认可.最后看看他是不是支持业务场景--RabbitMQ支持message的ack模式, 也就是说receiver可以先获取message, 然后再任务处理完后, 确认这个message被消耗.
这下完美了, 可以开始动工了.

安装和体验

对于第一次上手RabbitMQ, 过程还是比较顺利的.在Gentoo和Centos下只需要1分钟的时间就安装完毕了.
再看配置文件, RabbitMQ的配置文件就是一个erlang的item, 格式如下

[ {app, {key, value}}, .. ]  

RabbitMQ基本不需要过多的配置, 采用默认的也可以, 非常方便.

问题

在开发过程中, 遇到了一个比较蛋疼的问题. RabbitMQ对于链接有一个heartbeat机制, 也就是说, 当接收不到这个heartbeat的时候, 链接就会自动断开, message就重新被分配. 由于任务处理的时候比较长, 所以经常碰到任务处理完后, 链接已经断开, 无法ack这个message.
而这个heartbeat可以在两个地方设置, 一个是server段的config文件, 一个是client段的参数.经过几次实验后, server段的配置优于client的参数, 也就是说当server配置为30s的时候, client即使配置成31s, 也会被重置为30s.

惊喜

RabbitMQ还提供了很多plugin, 方便管理和获取更多的特性.这里就尝试了一个管理的plugin, 它自动生成一个webui的界面, 让mq的状态--队列长度, 队列连接数等等的信息一目了然.


博客搜索