Mysql实例mysql数据库索引与索引优化

时间:2021-11-25

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

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

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

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

普通索引

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

  1. # 直接创建索引 
  2.  
  3. CREATE INDEX index_name ON table(column(length)) 
  4.  
  5. # 修改表结构的方式添加索引 
  6.  
  7. ALTER TABLE table_name ADD ON (column(length)) 
  8.  
  9. # 创建表的时候同时创建索引 
  10.  
  11. TABLE table_name ( *,INDEX index_name title(length)) 
  12.  
  13. # 删除索引 
  14.  
  15. DROP table 

唯一索引

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

UNIQUE 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)

# 修改表结构的方式添加索引

TABLE article ADD FULLTEXT index_content(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应用

可以这样查询:

  1. select id from t where num=10 
  2.  
  3. union all 
  4.  
  5. select id from t where num=20MYSQL应用 

in 和 not in 也要慎用,因为IN会使系统无法使用索引,而只能直接搜索表中的数据.如:

select id from t where num in(1,2,3)MYSQL应用

对于连续的数值,能用 between 就不要用 in 了:

  1. select id from t where num between 1 and 3MYSQL应用 

尽量避免在索引过的字符数据中,使用非打头字母搜索.这也使得引擎无法利用索引.

见如下例子:

  1. SELECT * FROM T1 WHERE NAME LIKE ‘%L%’ 
  2.  
  3. SELECT * FROM T1 WHERE SUBSTING(NAME,1)=’L’ 
  4.  
  5. SELECT * FROM T1 WHERE NAME LIKE ‘L%’MYSQL应用 

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

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

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

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

    收藏