索引优化-基础篇

索引优化-索引类型（一）

• 查看表中已经存在 index：show index from table_name;
• 创建和删除索引
• 索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。
  （1）使用ALTER TABLE语句创建索引。
  语法如下：
• alter table table_name add index index_name (column_list) ;
• alter table table_name add unique (column_list) ;
• alter table table_name add primary key (column_list) ;
• 其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

创建索引的示例如下：

• mysql> use tpsc
• Database changed
• mysql> alter table tpsc add index shili (tpmc ) ;
• Query OK, 2 rows affected (0.08 sec)
• Records: 2 Duplicates: 0 Warnings: 0

(2）使用CREATE INDEX语句对表增加索引。
能够增加普通索引和UNIQUE索引两种。其格式如下：

• create index index_name on table_name (column_list) ;
• create unique index index_name on table_name (column_list) ;
• 说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

（3）删除索引。
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

• drop index index_name on table_name ;

• alter table table_name drop index index_name ;

• alter table table_name drop primary key ;

• 其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY 3KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

• 如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
  删除索引的操作，如下面的代码：

• mysql> drop index shili on tpsc ;

• Query OK, 2 rows affected (0.08 sec)

• Records: 2 Duplicates: 0 Warnings: 0

• 该语句删除了前面创建的名称为“shili”的索引。

• mysql支持的存储引擎：Innodb MyIsam NDB Memory Archive。

mysql索引按存储结构:

支持4种索引分别是：full-text，b-tree，hash，r-tree。

** mysql索引按值特性:唯一 普通 主键 **

• mysql索引按表列数：
  分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列

索引优化-索引类型（二）

MySQL索引类型包括：

• (1）普通索引
  这是最基本的索引，它没有任何限制。它有以下几种创建方式：
• （2）唯一索引
  它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
• （3）主键索引
  它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

索引优化-索引类型（三）

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

• CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

• ALTER TABLE mytable ADD INDEX name_city_age (username(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

• 如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

索引优化-索引类型（四）

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

• SELECT * FROM mytable WHREE username=“admin” AND city=“郑州” SELECT * FROM mytable WHREE username=“admin”

• 而下面几个则不会用到：
  SELECT * FROM mytable WHREE age=20 AND city=“郑州” SELECT * FROM mytable WHREE city=“郑州”

索引优化-full-text索引

• ull-text为全文索引，在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。
• full-text主要是用来代替like "%***%"效率低下的问题
• solr sphinx 全文检索
• 你是否一直在寻找比MySQL的LIKE语句更高效的方法的，下面我就为你介绍几种。
• LIKE语句：SELECT column FROM table where condition like `%keyword%’
  事实上，可以使用 locate(position) 和 instr 这两个函数来代替
• 一、LOCATE语句 ：SELECT column from table where locate(‘keyword’, condition)>0
• 二、或是 locate 的別名 position
  POSITION语句：SELECT column from table where position(‘keyword’ IN condition)
• 三、INSTR语句
  SELECT column from table where instr(condition, ‘keyword’ )>0
• locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个起始位置的参数外，两者是一样的。
  mysql> SELECT LOCATE(‘bar’, ‘foobarbar’,5);
  -> 7
• 速度上这三个比用 like 稍快了一点。

索引优化-b-tree索引

b-tree在myisam里的形式和innodb稍有不同

• 在 innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的b-tree差不多，只是还存放了指向主键的信息.

• 而在myisam里，主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

索引优化-hash索引

目前memory和ndb cluster支持这种索引.

• hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：
• (1)由于存放的是hash值，所以仅支持<=>以及in操作.
• (2)hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序.
• (3)在组合索引里，无法对部分使用索引.
• (4)不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，可能存在相同的hash值.
• (5)当存在大量相同hash值得时候，hash索引的效率会变低.

索引优化-r-tree索引

• r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
• 相对于b-tree，r-tree的优势在于范围查找.

索引优化-建立索引的时机

• 到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

• SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city=‘郑州’ 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

• 刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

• SELECT * FROM mytable WHERE username like’admin%’ 而下句就不会使用：

• SELECT * FROM mytable WHEREt Name like’%admin’ 因此使用LIKE时应注意以上区别。

索引优化-索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：
◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引优化-使用索引的注意事项（一）

◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.

◆使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

索引优化-使用索引的注意事项（二）

◆like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算
select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;

◆不使用not in和< >操作

索引优化-使用索引的注意事项（三）

• myisam里所有键的长度仅支持1000字节，innodb是767.
• blob和text字段支持前缀索引.
• 使用!=以及<>不等于的时候，mysql不使用索引.
• 当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索引里使用非第一个索引时也不使用索引.
• 在使用like的时候，以%开头，即"%***"的时候无法使用索引；在使用or的时候，要求or前后字段都有索引.
  有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引。可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽略掉index方法是ignore index.

索引优化-使用索引的注意事项（四）

会导致引擎放弃使用索引,改为进行全表的几种情况,都要在开发中尽量避免出现!

• (1)、where子句中使用like关键字时，前置百分号会导致索引失效（起始字符不确定都会失效）。如：select id from test where name like “%吉坤”。
• (2)、where子句中使用is null或is not null时，因为null值会被自动从索引中排除，索引一般不会建立在有空值的列上。
• (3)、where子句中使用or关键字时，or左右字段如果存在一个没有索引，有索引字段也会失效；而且即使都有索引，因为二者的索引存储顺序并不一致，效率还不如顺序全表扫描，这时引擎有可能放弃使用索引，所以要慎用or。
• (4)、where子句中使用in或not in关键字时，会导致全表扫描，能使用exists或between and替代就不使用in。
• (5)、where子句中使用!=操作符时，将放弃使用索引，因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描；
• (6)、where子句中应尽量避免对索引字段操作（表达式操作或函数操作），比如select id from test where num/2 = 100应改为num = 200。
• (7)、在使用复合索引时，查询时必须使用到索引的第一个字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致。
• (8)、查询时必须使用正确的数据类型。数据库包含了自动了类型转换，比如纯数字赋值给字符串字段时可以被自动转换，但如果查询时不加引号查询，会导致引擎忽略索引。

实例

• 使用索引（SELECT SQL_NO_CACHE * FROM exam_paper_c9 FORCE index(em_stu_type) WHERE ( stu_id=8798 and em_id=2484 and types=1 )）
  
• 不使用索引（SELECT SQL_NO_CACHE * FROM exam_paper_c9 WHERE ( stu_id=8798 and em_id=2484 and types=1） ）
  

索引优化-MyISAM与InnoDB的索引实现（一）

• 1、MyISAM 使用B+Tree 作为索引结构，叶子节点的data存放指针，也就是记录的地址。对于主键索引和辅助索引都是一样的。
• 2、InnoDB 也使用B+Tree作为索引结构，也别需要注意的是，对于主键索引，InnoDB 使用聚集索引，InnoDB的数据文件本身就是就是索引文件。而MyISAM，主键索引和数据文件是分离的。
• 3、InnoDB数据文件，要按主键聚集索引，这就要求InnoDB的表必须要有主键（MyISAM可以没有）。如果没有显式指定主键，InnoDB会自动选择一个可以唯一标识记录的字段作为主键，比如auto_increment的字段，如果不存在这样的列，InnoDB会自动生成一个隐含字段作为主键，这个隐含字段6个字节，是长整形。
• 4、对于InnoDB的辅助索引，叶子节点的data存放的是主键的值。这就意味着，使用辅助索引定位记录，需要使用两次索引：首先使用辅助索引找到主键的值，根据主键的值，使用主键索引找到记录。
• 5、InnoDB的辅助索引为什么要这样设计？
  如果辅助索引data存放的行指针，当行移动或者数据页分裂时，需要更新data域行指针的值，这就增加维护成本。data存在主键的值，就没有这个问题。行移动和数据页分裂，主键索引会自动更新。data关联主键的值，不需要更新，相当于增加一个间接层。这个间接层对性能的影响也很小，因为通过主键定位记录是非常快的。
• 6、了解了innoDB的索引实现，有几个地方需要注意：
  不要使用过长的字段作为主键，因为辅助索引都要使用主键索引定位记录，这个字段过长，使用内存更大，影响性能。
  使用单调的字段作为主键，特别是insert的时候，如果是非单调的，B+Tree维护成本很高。
• 7、这就能很好解释，隔离级别 repeatable-read, 不使用索引锁住整个表，使用索引（主键索引或者辅助索引）只会锁住对应的行。
• 8、在查询执行计划中，有一个字段 type， eq_ref 表示使用主键索引，直接定位到记录。而ref 表示先使用辅助索引，找到主键的值，再使用主键索引定位到记录。

索引优化-相关链接

• http://www.linuxidc.com/Linux/2011-07/39245.htm
• http://blog.sina.com.cn/s/blog_6fd335bb0100v1lm.html
• http://blog.csdn.net/cuidiwhere/article/details/8452997
• http://www.2cto.com/database/201302/188193.html
• http://blog.163.com/mysqldba@126/blog/static/1315356342009931103832261/
• http://www.thinkphp.cn/topic/3855.html