mysql性能优化之索引优化_MySQL

　　作为免费又高效的数据库，mysql基本是首选。良好的安全连接，自带查询解析、sql语句优化，使用读写锁（细化到行）、事物隔离和多版本并发控制提高并发，完备的事务日志记录，强大的存储引擎提供高效查询（表记录可达百万级），如果是InnoDB，还可在崩溃后进行完整的恢复，优点非常多。即使有这么多优点，仍依赖人去做点优化，看书后写个总结巩固下，有错请指正。

　　完整的mysql优化需要很深的功底，大公司甚至有专门写mysql内核的，sql优化攻城狮，mysql服务器的优化，各种参数常量设定，查询语句优化，主从复制，软硬件升级，容灾备份，sql编程，需要的不是一星半点的知识与时间来掌握，作为一名像俺这样的菜鸟开发，强吃这么多消化不了也没意义：没地儿用啊，况且还有运维和dba，还不如把手头的业务写好，也就是写好点的sql，而且很多sql语句优化跟索引还是有很大关系的。

　　首先，mysql的查询流程大致是：mysql客户端通过协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用API获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。然后，mysql默认使用的BTREE索引，并且一个大方向是，无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

　　mysql通过存储引擎取数据，自然跟存储引擎有很大关系，不同的存储引擎索引也不一样，如MyISAM的全文索引，即便索引叫一个名字内部组织方式也不尽相同，最常用的当然就是InnoDB了（还有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），这里写的是InnoDB引擎。而索引的实现也跟存储引擎，按照实现方式分，InnoDB的索引目前只有两种：BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引，InnoDB的BTREE索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。至于B树与B+树的区别，原谅的俺数据结构没好好学，也是需要补的地方。

　　使用了BTREE索引，意味着所有的索引是按顺序排列存储的（升序），mysql就是这么干的，mysl中的BTREE索引抽象结构如下图（参考高性能mysql）。

　　结构中，每一层节点均从左往右从小到大排列，key1 e5bfa4ad379cb3fe7c3eb47c032e633e 'Mary'，但俺在测试时发现>可以，>=却不行，至少在字符串列上不行（测试mysql版本5.5.12），然而在时间类型（timestamp）上却可以，不测试下还真不能确定说就用到了索引==

　　出于好奇测了下整型字段的索引（idx_cn(count, name)，count为整型），发现整型受限制少很多，下面的都能用到索引，连前模糊匹配的都行

select * from indexTest1 where count > '10'
  select * from indexTest1 where count >= '10'
  select * from indexTest1 where count > '10%'
  select * from indexTest1 where count >= '10%'
  select * from indexTest1 where count > '%10%'
  select * from indexTest1 where count >= '%10%'

　　5. 精确匹配一列并范围匹配右侧相邻列，即前一列是固定值，后一列是范围值，它用了name与age两个列的索引（key_len推测）

　　如select * from staffs where name = 'July' and age > 25

　　6. 只访问索引的查询，比如staffs表的情况，索引建立在(name,age,pos)上面，前面一直是读取的全部列，如果我们用到了哪些列的索引，查询时也只查这些列的数据，就是只访问索引的查询，如

select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'
  select name,age from staffs where name = July and age > 25

　　第一句用到了全部索引列，第二句只用了索引前两列，select的字段就最多只能是这两列，这种查询情况的索引，mysql称为覆盖索引，就是索引包含（覆盖）了查询的全部字段。是不是用到了索引查询，在explain中需要看最后一个Extra列的信息，Using index表明使用了覆盖索引，同时Using where表明也使用了where过滤

　　7. 前缀索引

　　区别于列前缀（类似like 'J%'形式的模糊匹配）和最左列索引（顺序取索引中靠左的列的查询），它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时，一个明显的区别是：MyISAM支持全文索引，而InnoDB不行，甚至对于text、blob这种超长的字符串或二进制数据时，MyISAM会取前多少个字符作为索引，InnoDb的前缀索引跟这个类似，某些列，一般是字符串类型，很长，全部作为索引大大增加存储空间，索引也需要维护，对于长字符串，又想作为索引列，一个可取的办法就是取前一部分（前缀），代表一整列作为索引串，问题是：如何确保这个前缀能代表或大致代表这一列？所以mysql中有个概念是索引的选择性，是指索引中不重复的值的数目（也称基数）与整个表该列记录总数（#T）的比值，比如一个列表（1,2,2,3），总数是4，不重复值数目为3，选择性为3/4，因此选择性范围是[1/#T, 1]，这个值越大，表示列中不重复值越多，越适合作为前缀索引，唯一索引（UNIQUE KEY）的选择性是1。

　　比如有一列a varchar(255)，以它作前缀索引，比如以7个测试，逐个增加看看选择性值增长到那个数基本不变，就表示可以代表整列了，再结合这个长度的索引列是否存储数据太多，做个权衡，基本就行了。但如果这个选择性本来就小的可怜还是算了

select count(distinct left(a, 7))/count(*) as non_repeat from tab;

　　定好一个前缀数目，如9，添加索引时可以这样

alter table tab add index idx_pn(name(9)) --单独前缀索引
  alter table tab add index idx_cpn(count, name(9)) --复合前缀索引

　　以上为常见的使用索引的方式，有这么些情况不能用或不能全用，有的就是上面情况的反例，以key(a, b, c)为例

　　1. 跳过列，where a = 1 and c = 3，最多用到索引列a；where b = 2 and c = 3，一个也用不到，必须从最左列开始

　　2. 前面是范围查询，where a = 1 and b > 2 and c = 3，最多用到 a, b两个索引列；

　　3. 顺序颠倒，where c = 3 and b = 2 and a = 1，一个也用不到；

　　4. 索引列上使用了表达式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表达式是一大忌讳，再简单mysql也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选以'cp1001'开头的订单，而不是写sql过滤它；

　　5. 模糊匹配时，尽量写 where a like 'J%'，字符串放在左边，这样才可能用得到a列索引，甚至可能还用不到，当然这得看数据类型，最好测试一下。

　　排序对索引的影响

　　order by是经常用的语句，排序也遵循最左前缀列的原则，比如key(a, b)，下面语句可以用到（测试为妙）

select * from tab where a > 1 order by b
  select * from tab where a > 1 and b > '2015-12-01 00:00：00' order by b
  select * from tab order by a, b

　　以下情况用不到

　　1. 非最左列，select * from tab order by b;

　　2. 不按索引列顺序来的，select * from tab where b > '2015-12-01 00:00:00' order by a;

　　3. 多列排序，但列的顺序方向不一致，select * from tab a asc, b desc。

　　聚簇索引与覆盖索引

　　前面说到，mysql索引从结构上只有两类，BTREE与HASH，覆盖索引只是在查询时，要查询的列刚好与使用的索引列完全一致，mysql直接扫描索引，然后就可返回数据，大大提高效率，因为不需再去原表查询、过滤，这种形式下的索引称作覆盖索引，比如key(a,b)，查询时select a,b from tab where a = 1 and b > 2，本质原因：BTREE索引存储了原表数据。

　　聚簇索引也不是单独的索引，前面简要写到，BTREE索引会把数据放在索引中，即索引的叶子页中，包括主键，主键是跟表数据紧挨着放在一起的，因为表数据只有一份，一列键值要跟每一行数据都紧挨在一起，所以一张表只有一个聚簇索引，对于mysql来说，就是主键列，它是默认的。

　　聚簇索引将表数据组织到了一起（参考前面主键索引简略图），插入时严重依赖主键顺序，最好是连续自增，否则面临频繁页分裂问题，移动许多数据。

　　哈希索引

　　简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，也是对索引列计算一个散列值（类似md5、sha1、crc32），然后对这个散列值以顺序（默认升序）排列，同时记录该散列值对应数据表中某行的指针，当然这只是简略模拟图

　　　　　　

　　比如对姓名列建立hash索引，生成hash值按顺序排列，但是顺序排列的hash值并不对应表中记录，从地址指针可反应出来，而且，hash索引可能建立在两列或者更多列上，取得是多列数据后的hash值，它不存储表中数据。它先计算列数据的hash值，与索引中的hash值比较，找到了然后比对列数据是否相等，可能涉及其他列条件，然后返回数据。hash当然会有冲突，即碰撞，除非有很多冲突，一般hash索引效率很高，否则hash维护成本较高，因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点：

　　1. hash索引只是hash值顺序排列，跟表数据没有关系，无法应用于order by；

　　2. hash索引是对它的所有列计算哈希值，因此在查询时，必须带上所有列，比如有(a, b)哈希索引，查询时必须 where a = 1 and b = 2，少任何一个不行；

　　3. hash索引只能用于比较查询 = 或 IN，其他范围查询无效，本质还是因不存储表数据；

　　4. 一旦出现碰撞，hash索引必须遍历所有的hash值，将地址所指向数据一一比较，直到找到所有符合条件的行。

　　填坑

　　前面提到通过explain的key_len字段，可大致估计出用了哪些列，索引列的长度跟索引列的数据类型直接相关，一般，我们说int是4字节，bigint8字节，char是1字节，考虑到建表时要指定字符集，比如utf8，还跟选的字符集有关（==!），在utf8下边，一个char是3字节，但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦？事实总有点区别，测试方法比较机械（以下基于mysql 5.5.2）

　　建表，加索引，int型

--测试表
  create table keyLenTest1(
    id int primary key auto_increment,
    typeKey int default 0 ,
    add_time timestamp not null default current_timestamp
  ) charset utf8
  --添加索引
  alter table keyLenTest1 add index idx_k(typeKey);

　　可知int型索引默认长度为5，在4字节基础上+1

　　char型

 --改为char型，1个字符
  alter table keyLenTest1 modify typeKey char(1);

--改为char型，2个字符
  alter table keyLenTest1 modify typeKey char(2);

　　可知，char型初始是4字节（3+1 bytes），后续按照3字节递增

　　varchar型

--改为varchar型，1个字符
  alter table keyLenTest1 modify typeKey varchar(1);

--改为varchar型，2个字符
  alter table keyLenTest1 modify typeKey varchar(2);

　　可知，varchar型，1个字符时，key_len为6，以后以3字节递增

　　所以，如果一个语句用到了int、char、varchar，key_len如何计算以及用了哪些索引列应该很清楚了。

　　如果想了解的更详细点，explain各字段意义，索引的更多细节，除了explain，还有show profiles、慢查询日志等（没细看），推荐看高性能mysql，毕竟俺写的太肤浅。