Mysql索引设计

 前言

　　索引是数据库中用来提高性能的最常用工具，所有的MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。

一、设计索引的原则

1. 最适合添加索引的列是出现在where字句中的列，或连接子句中指定的列（join in），而不是select关键字后面的列。
2. 使用唯一索引。考虑某列的分布，索引的列的基数越大，索引的效果越好。例如，对性别M/F列做索引没多大用处。
3.  使用短索引,如果对字符串进行索引,应该指定一个前缀长度,只要有可能就应该这样做,例如:如果有一个char(200)的列,如果在前10个或者20个字符内,多数值是唯一的,那么就不要对整个列进行索引.对前10个20个字符进行索引能够节省大量索引的空间,也可能会使查询更快.较小的索引设计的磁盘I/O较少,较短的值比较起来更快.更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,mysql也可以在内存中容纳更多的值,这样就增加了找到行而不用读取索引中较多快的可能性。
4. 利用最左前缀。尽量将使用频繁且过滤效果好的字段放“左边”，指的是组合索引。
5. Innodb默认会按照一定的顺序保存数据，如果明确定义了主键，则按照主键顺序保存。如果没有主键，但有唯一索引，就按照唯一索引的顺序保存。如果有几个列都是唯一的，都可以作为主键的时候，为了提高查询效率，应选择最常用访问的列作为主键。另外，innodb的普通 index都会保存主键的键值，所有主键要尽可能选择较短的数据类型。可以看出，应当尽量避免对主键的修改。经过dba的测试，保证主键的递增可以提高插入性能。
6. 不要过度索引.索引并不是越多越好,每个额外的索引都要占用额外的磁盘空间,并降低操作的性能.在修改表内容的时候,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长.如果一个索引很少使用或从不使用,建议删除该索引.当索引太多时,也可能会使mysql选择不到所要使用的最好索引,只保持所需的索引有利于查询优化。

二、索引的分类

　　1、普通索引

　　　　普通索引是最基本的索引

　　　　直接创建索引

　　　　CREATE INDEX index_name ON table(column(length))

　　　　修改表结构创建索引

　　　　ALTER TABLE table_name ADD INDEX index_name ON (column(length))

　　　　创建表的同时创建索引

     CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length)))

　　　　删除索引

　　　　DROP INDEX index_name ON table
　　　　ALTER TABLE table DROP INDEX index_name

　　2、唯一索引

　　　　与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

　　　　直接创建索引

     CREATE UNIQUE INDEX indexName ON table(column(length))

　　　　修改表结构创建索引

　　　　ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

　　　　创建表的同时创建索引

　　　　CREATE TABLE `table` (
　　　　`id` int(11) NOT NULL AUTO_INCREMENT ,
　　　　`title` char(255) CHARACTER NOT NULL ,
　　　　`content` text CHARACTER NULL ,
　　　　`time` int(10) NULL DEFAULT NULL ,
　　　　UNIQUE indexName (title(length)));

　　3、主键索引

　　　　是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

     CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
　　　　);

　　4、组合索引

　　　　指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合（顾名思义，就是最左优先，比如我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。）

　　ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

　　5、全文索引

　　　　主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

　　　　直接创建索引　

　　　　CREATE FULLTEXT INDEX index_content ON article(content)

　　　　修改表结构方式创建索引

     ALTER TABLE article ADD FULLTEXT index_content(content)

　　　　创建表的时候同时创建索引

     CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content));

　　　　使用语法

　　　　使用全文索引的格式：  MATCH (columnName) AGAINST ('string')

     SELECT * FROM article WHERE MATCH(content) AGAINST (‘查询字符串’);

三、使用索引的缺点

　　1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
　　2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句