MySQL索引和性能优化

Question：mysql为什么需要索引？

Answer：对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。

1、MySQL索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

2、MySQL索引的类型

a）普通索引

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

创建方法：

直接创建索引
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 SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)

b）唯一索引
索引列的值必须唯一

创建方法和普通索引类似（多了UNIQUE关键字）

c）3. 全文索引（FULLTEXT）

   FULLTEXT索引

      d）组合索引（最左前缀）

SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。     

例如：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。

建立这样的组合索引，其实是相当于分别建立了这样两组组合索引：-title,time和–title（MySQL组合索引“最左前缀”的结果）

–使用到上面的索引
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE utitle='测试';
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;

3、索引的缺点

a）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 

b）建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

4、使用Mysql索引建议

1）在数据库设计时不要让字段的默认值为NULL。

由于列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们

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

   3） 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，我们可以改成：select * from users where adddate<’2007-01-01′。MySQL只对这些操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。