mysql优化索引基本用法

r囧r小猫 2022-07-12 06:23 164阅读 0赞

*  使用索引为什么会快
 *  索引的类型
 *  哪些列上适合添加索引
 *  索引的使用
    
     *  建立索引
     *  添加主键索引
     *  查询索引均可
        
         *  下列几种情况下有可能使用到索引
         *  下列的表将不使用索引
 *  查看索引的使用情况
 *  简述mysql四种索引的区别
    
     *   *   *  复合索引
             *  全文索引案例
         *  全文索引查询

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的’create index’，查询速度就可能提高百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。

# 使用索引为什么会快? #

*  索引的代价
 *  磁盘占用
 *  对dml(update delete insert)语句的效率影响，变慢

# 索引的类型 #

*  主键索引，主键自动的为主索引 (类型Primary)
 *  唯一索引 (UNIQUE)
 *  普通索引 (INDEX)
 *  全文索引与停止词 (FULLTEXT) \[适用于MyISAM\] `sphinx` \+ 中文分词 `coreseek` \[sphinx 的中文版 \]
 *  复合索引

# 哪些列上适合添加索引 #

*  较频繁的作为查询条件字段应该创建索引
    
    select \* from emp where empno = 1
 *  唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    
    select \* from emp where sex = ‘男‘
 *  更新非常频繁的字段不适合创建索引
    
    select \* from emp where logincount = 1
 *  不会出现在WHERE子句中字段不该创建索引

# 索引的使用 #

## 建立索引 ##

create [UNIQUE|FULLTEXT]  index index_name on tbl_name
    (col_name [(length)] [ASC | DESC] , …..);
    #或则
    alter table table_name ADD INDEX [index_name]  (index_col_name,...);

## 添加主键(索引) ##

#添加联合主键
     ALTER TABLE 表名 ADD PRIMARY KEY(列名,..);

`ALTER` `TABLE` ```表名 ``ADD``` `PRIMARY` ```KEY``(列名,..);```联合主键

删除索引

DROP INDEX index_name ON tbl_name;
    #或则
    alter table table_name drop index index_name;

删除主键(索引)比较特别:

alter table t_b drop primary key;

## 查询索引：(均可) ##

show index(index_name) from table_name;
    #或则
    show index_name from table_name;
    #或则
    desc table_Name;

**修改索引，我们一般是先删除在重新创建.**

查询要使用索引最重要的条件是查询条件中需要使用索引。

### 下列几种情况下有可能使用到索引： ###

*  对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
 *  对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引，‘aaa%’ 会使用到索引

### 下列的表将不使用索引： ###

1.  如果条件中有or，即使其中有条件带索引也不会使用。
2.  对于多列索引，不是使用的第一部分，则不会使用索引。
3.  like查询是以%开头
4.  如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)
5.  如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

# 查看索引的使用情况 #

show status like ‘Handler_read%’;

大家可以注意：`handler_read_key`:这个值越高越好，越高表示使用索引查询到的次数。  
`handler_read_rnd_next`:这个值越高，说明查询低效。

# 简述mysql四种索引的区别 #

**PRIMARY** 索引 =》在主键上自动创建

**UNIQUE** 索引=> 只要是UNiQUE 就是Unique索引.

**INDEX** 索引=>就是普通索引

**FULLTEXT** => 只在MYISAM 存储引擎支持, 目的是全文索引，在内容系统中用的多， 在全英文网站用多(英文词独立). 中文数据不常用，意义不大 国内全文索引通常 使用 sphinx 来完成.

#### 复合索引 ####

create index 索引名 on 表名(列1，列2);

#### 全文索引案例 ####

1.创建表

create table news(id int , title varchar(32),con varchar(1024)) engine=MyISAM;

2.建立全文索引

create fulltext index ful_inx on news (con);

3.插入数据

这里要注意，对于常见的英文 fulltext 不会匹配,而且插入的语句本身是正确的.

4.看看匹配度

mysql> select match(con) against('poverty') from news；
    
    +-------------------------------+
    
    | match(con) against('poverty') |
    
    +-------------------------------+
    
    |                             0 |
    
    |                             0 |
    
    |                             0 |
    
    |            0.9853024482727051 |
    
    +-------------------------------+

0表示没有匹配到，或者你的词是停止词，是不会建立索引的.

使用全文索引,不能使用like语句，这样就不会使用到全文索引了.

### 全文索引查询 ###

select * from `tableName` where match(字段1,字段2...) against(‘关键词’);