Mysql数据库索引

索引原理

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息
数据库索引是用于提高数据库表的数据访问速度的
数据库索引的特点：
（1）避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据（2）聚集索引可以避免数据插入操作，集中于表的最后一个数据页面
（3）在某些情况下，索引可以避免排序操作
索引背后的数据结构基础是B+tree，一个表只能有一个主键，一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。
如果把数据表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据，速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。因此，利用索引会使数据库查询有惊人的性能提升。
索引能让数据库查询数据的速度上升，而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构，因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。
如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。
非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据，不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。
有一种例外可以不使用聚集索引就能查询出所需要的数据，这种非主流的方法称之为「覆盖索引」查询，也就是平时所说的复合索引或者多字段索引查询。当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

索引分类

普通索引，这是最基本的索引，它没有任何限制

 CREATE INDEX indexName ON mytable(username(length));
 或
 ALTER mytable ADD INDEX [indexName] ON (username(length))
 或创建表时指定
 CREATE TABLE mytable(  
 ID INT NOT NULL,   
 username VARCHAR(16) NOT NULL,  
 INDEX [indexName] (username(length))  
 ); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length 删除索引

 DROP INDEX [indexName] ON mytable;

唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建方式如下：
```
 CREATE UNIQUE INDEX indexName ON mytable(username(length))
```
其余创建方法与上述普通索引相同
主键索引，是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
```
 CREATE TABLE mytable(  
 ID INT NOT NULL,   
 username VARCHAR(16) NOT NULL,  
 PRIMARY KEY(ID)  
 ); 
```
当然也可以用ALTER命令。记住：一个表只能有一个主键。
组合索引，为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将多个列建到一个索引里，减少创建索引时列长度会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。MySQL组合索引“最左前缀”就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引
在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。

索引缺点及使用注意事项

缺点：
（1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
（2）建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
索引注意事项：
（1）索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
（2）使用短索引，对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
（3）索引列排序，MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
（4）like语句操作，一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
（5）不要在列上进行运算，如select * from users where YEAR(adddate)<2007;,将在每个行上进行运算，这将导致索引失效而进行全表扫描
（6）不使用NOT IN和<>操作