Mysql 索引与优化 - 快车库

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里的所有记录的引用指针
更通俗的说，数据库索引就好比一本书的目录，能够加快数据库的查询速度

普通索引

这是这基本的索引，它没有任何限制，MyISAM 中默认的 B-tree 类型的索引

# 直接创建索引
CREATE INDEX index_name ON table(column(length))

# 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

# 创建表的时候同时创建索引
CREATE TABLE table_name ( \*,INDEX index_name title(length))

# 删除索引
DROP INDEX index_name ON table

唯一索引

与普通索引一致，不同的是索引值必须唯一，允许有空值

# 直接创建索引
CREATE UNIQUE INDEX index_name ON table(column(length))

# 修改表结构的方式添加索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

# 创建表的时候同时创建索引
CREATE TABLE table_name ( \*,UNIQUE index_name title(length))

全文索引

FULLTEXT 索引仅 MyISAM 引擎支持
他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加
不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法

# 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

# 修改表结构的方式添加索引
ALTER TABLE article ADD FULLTEXT index_content(content)

# 创建表的时候同时创建索引
CREATE TABLE table_name ( \*,FULLTEXT (content))

组合索引（最左前缀）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))
建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title

索引优化

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子：
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’
即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引