对于联合索引，要注意索引字段在order by中出现的方式，否则可能会导致索引失效。下面举例说明。 下面这张表有个联合索引: <a1,a2,a3>，并且表里有100万行数据 create table a123( id bigint unsigned auto_increment not null, a1 varchar(50) not null, a2 varchar(50) not null, a3 varchar(50) not null, b1 varchar(50) not null, b2 varchar(50) not null, b3 varchar(50) not null, primary key(id), index idx_a_123(a1, a2, a3) ) explain select * from a123 order by a1 asc limit 100; …
check table a123 检查索引状态是否正常，没有重复唯一键之类的 repair table a123 修复索引。InnoDB不支持这个命令，可以通过执行一次alter table engine达到同样的目的： alter table a123 engine = INNODB analyze table a123 为优化器提供本表索引的统计数据 optimize table a123 把分散存储（fragmented）的数据和索引重新挪到一起(defragmentation)，对I/O速度有好处。 InnoDB的索引无法使用这个命令来deframent, 你也可以通过执行一次alter table engine达到同样的目的： alter table a123 engine = INNODB
select … order by a a上有索引时执行上面的语句，比a上没有索引时要快。因为B-树索引中各结点已经排好了序，数据库只需要沿着树中的叶子结点链路朝左或朝右走即可。
文本字段越长，在这个字段上建索引的代价就越高。 为了解决这个问题，可以为这个字段配一个hash字段，然后在这个hash字段上建索引。 如， select * from log where url_hash= CRC32(‘http://bing.com?query=xxx&tp=xxx’ ) crc32()返回的值一般比较短，比较合适； 如果用md5或sha1做hash, 返回的值也很长，那就失去意义了。 不过，像上面这样写可能存在hash collision问题，所以应该加个原字段上的搜索条件，确保搜出来的是自己想要的： select * from log where url_hash= CRC32(‘http://bing.com?query=xxx&tp=xxx’ ) and url = ”http://bing.com?query=xxx&tp=xxx’ 另一种类似的方案是： 不用hash值，而用前缀。 这里有提到。
一个索引的Selectivity是： select count(distinct 姓)/count(*) from … 即 索引字段上所有值的相异程度。相异程度越高(中国人每人1个姓)，意味着一个普通lookup查询返回的值越少(姓陈的只有1个), 性能越好； 相异程度越低（中国人只允许100个姓），则相反（姓陈记录数=有13亿/100 =130万) ，性能不会好。 知道这个又有什么用呢？ 举例来说，一个文本字段越长，Selectivity一般就越高，但同时在它上面建索引的代价也越高。 避免给长文本建索引的方法之一是给它的前缀（或其他截取子串的方式）建立索引。 问题是，前缀的长度定多少合适？ 这个就取决于前缀的Selectivity了。很短的前缀，比如只取第1个字母，那建索引肯定很快，但Selectivty也会很低，查询性能会很弱。 前缀长度要做到：Selectivity可以接受，同时长度仍然适于直接索引。
Clustered Index: 把索引和数据存储在一起。说白了，就是B-树的叶子结点上直接挂了数据。 它对性能的好处是： 1. 从存储的角度来看，索引即表。索引找到了，数据也就找到了，少做一次i/o 2. 如果索引值相邻，记录也相邻；获取一堆索引值相邻的数据时，由于存储地点相邻没有分散，I/O性能会比较高。比如说，获取 id between (3,10)的数据，可能只需要读取一个page. Innodb中总是有一个Clustered Index; 如果你定义了primary key, 那么primary key就是Clustered Index; 如果没定义，那innodb也会跟你生成一个隐式的primary key, 并使它作为Clustered Index. 在Innodb中, secondary index没有指针直接指向数据，它只会记录primary index的值。 1. 坏处：基于secondary index的查找实际上是两次查找：先按secondary index值找到primary index的值；再按primary index的值找到记录。 2. 好处：由于secondary index没有指针直接指向数据，但数据的存储位置变化时(值不变)，secondary index不需要更新。 Innodb使用Primary Index作为Clustered Index隐含了一个结论：应该先插入主键值小的记录，再插入主键值大的记录。否则，会导致插入过程中的page split和最终存储时的data fragmentation，影响插入性能和查询性能。 例如，假设1条记录占半个page; 把id=1的记录插入到page1的上半部分后，再插入id=3的记录，很自然地，系统会把它安排在page1的下半部分，以保证相邻数据放在一起；最后要插入id=2的记录时，你不能把它安排在page2,因为id=2和id=1的记录必须相邻，不能让id=3挡在中间，这时系统就会把page1拆成两个page, 以把id=2插入到id=1和id=3之间; 这三条记录也可能各占一个page, 并且有些空白的page可能间隔其中（取决于磁盘上数据移动的具体策略）。 如果严格按照id=1,id=2,id=3的顺序来，就不会有这些问题，因为数据总是插在当前最后一个存储位置的后面，不需要拆散谁。 所以说， 主键最好是auto incremented并且不可修改的，这样就可以确保数据移动不会发生；如果做不到这一点，也应该时不时地运行一下optimize table，使得已经有所分散的数据能够重新紧凑地聚合在一起。 至于具体的性能区别，High Performance MySQL做了一下benchmark，1个用自增主键，另1个用UUID，结论是插入性能相差四倍。当然，主键数据类型及长度差异也是性能差别的原因之一，但主要因素还是非顺序插入导致的数据移动。
我用Super Smack做过几次MySQL性能测试。虽然可以用，但这种用c写的程序存在安装困难的问题：在阿里云的centos上可以装的上，在aws上未必就装的上；配置起来也很难受，那些个smack文件，太古怪。 后来我发现，其实用JMeter的jdbc request就可以对mysql性能测试。我用的模式是： 引用 1. 在开发机器上(mac/ubuntu/windows)以gui方式配置好Test Plan, 并试运行。 2. 将test plan文件上传到无干扰无界面的linux测试机上，以non-gui方式在测试机上执行测试，再收集结果。 JMeter支持server模式：用本地的jmeter控制测试机上的jmeter, 直接在本机上启动、停止、查看结果，用起来非常顺手。但它有个问题：在测试过程中，测试机上的jmeter要不停地从本地机器获取测试数据，并返回采样数据到本地机器。这些数据流动会增加测试机上JMeter的负担，使qps偏低。 所以我还是选择了在测试机上使用non-gui模式这种方案。下面给个完整的例子，例示jmeter jdbc request的使用。 例示：用JMeter对MySQL进行压力测试 测试目标 使用普通varchar作为主键，比起使用自增数字类型作为主键，到底会慢多少？这个性能损失是不是可以忽略不计? 测试数据准备 建两张差不多的表，一个使用数字user_id作主键，一个直接使用user_name drop table if exists user_id_pk; drop table if exists user_name_pk; create table user_id_pk ( user_id bigint unsigned not null auto_increment , user_name varchar(50) not null description varchar(200) default null created_when datetime not null …
‘High Performance MySQL’ 虽然说了 “Avoid NULL if possible”，但也说了 引用 “The performance improvement from changing NULL to NOT NULL is usually small”. 我针对这个做了下benchmarking. 性能差别确实很小。请看这里的末尾部分。 业务上说，有很多字段确实既要索引，又可能为空。对于这种情况，该NULLABLE就NULLABLE吧，没必要为了一点点性能上的收益而使用各种默认值给代码留坑。
datetime: 1001年到9999年，无时区 timestamp: 1970年到2038年，支持时区的概念 timestamp比datetime更省空间。
MySQL中一个被索引了的列，如果在某些行的数据是null，这对性能的影响倒底有多大？ 这个需要做下benchmarking才知道。 所有相关工具中, mysqlslap应该是最轻量的; 但它的可控性实在太弱，连让测试者自己造数据都做不到。 经过比较后，我发现最适合的工具应该是Super Smack. 用它做完测试后，结论很简单：被索引列是否可空，对性能的影响很小。 对我来说，更大的收获是学会了用专门的工具进行sql Benchmarking. 由于Super Smack是一个非常简陋的软件，安装、使用它的过程非常痛苦，痛苦到不停地想放弃。 然而。。。还是。。。 废话少说，先说下安装。 在64位CentOS上安装Super Smack 1.下载 传说中的 Tony Bourke维护的版本已经废了(网站都关了)，我们只能从这里下载： https://github.com/tmountain/Super-Smack 2.安装mysql client相关的lib 直接./configure会出一堆错。你要先安装 yum install mysql-devel 由于Super Smack不会去lib64目录下找lib, 你还得把相关lib“复制”到/usr/lib目录中。 ln -s /usr/lib64/mysql/libmysqlclient_r.so.16.0.0 /usr/lib/libmysqlclient.so 3. 还要改一下源码中的一个头文件 在dictionary.h加入 #include <string.h> 4. 接下来正常安装 ./configure –with-mysql make make install 5.试运行 #从自带的smack中复制一个出来，免得把原来的改坏了 cp smacks/select-key.smack smacks/my.smack 接下来修改my.smack里面的mysql配置，包括client admin和client smacker1的user, pass, …
一次使用Super Smack进行MySQL benchmarking的完整经历 – 上篇：安装 Read More »