Mysql实例mysql索引与优化

《Mysql实例mysql索引与优化》要点：
本文介绍了Mysql实例mysql索引与优化，希望对您有用。如果有疑问，可以联系我们。

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

更通俗的说,数据库索引就好比一本书的目录,能够加快数据库的查询速度MYSQL应用

普通索引

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

# 直接创建索引
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

唯一索引

与普通索引一致,不同的是索引值必须唯一,允许有空值MYSQL应用

# 直接创建索引
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被添加
不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法MYSQL应用

# 直接创建索引
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
–titleMYSQL应用

索引优化

• 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如：
  select id from t where num is nullMYSQL应用

• 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询：
  select id from t where num=0MYSQL应用

• 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描.优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行.MYSQL应用

• 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如：
  select id from t where num=10 or num=20MYSQL应用

• 可以这样查询：
  select id from t where num=10
  union all
  select id from t where num=20MYSQL应用

• in 和 not in 也要慎用,因为IN会使系统无法使用索引,而只能直接搜索表中的数据.如：
  select id from t where num in(1,2,3)MYSQL应用

• 对于连续的数值,能用 between 就不要用 in 了：
  select id from t where num between 1 and 3MYSQL应用

• 尽量避免在索引过的字符数据中,使用非打头字母搜索.这也使得引擎无法利用索引.
  见如下例子：
  SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
  SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
  SELECT * FROM T1 WHERE NAME LIKE ‘L%’MYSQL应用

• 即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务.而第三个查询能够使用索引来加快操作MYSQL应用

• 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描MYSQL应用

• 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描MYSQL应用

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引MYSQL应用

转载请注明本页网址：
http://www.vephp.com/jiaocheng/6162.html