mysql优化索引基本用法

最新推荐文章于 2017-12-01 15:09:41 发布

kwinH

最新推荐文章于 2017-12-01 15:09:41 发布

阅读量298

点赞数

分类专栏： mysql 文章标签： mysql 数据库优化索引性能

本文链接：https://blog.csdn.net/kwinH/article/details/55100785

版权

mysql 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

使用索引为什么会快
索引的类型
哪些列上适合添加索引
索引的使用
查看索引的使用情况
简述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%’ 会使用到索引

下列的表将不使用索引：

如果条件中有or，即使其中有条件带索引也不会使用。
对于多列索引，不是使用的第一部分，则不会使用索引。
like查询是以%开头
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)
如果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(‘关键词’);