四,mysql优化——sql语句优化之索引二

时间:2021-05-30 06:05:14

1,在什么列适合添加索引

(1)较频繁的作为查询条件字段应该添加索引

      select * from emp where empid = 2; 

(2)唯一性太差的字段不适合添加索引,即时频繁作为查询条件。

      select * from emp where sex = '男';

(3)更新非常频繁的字段不适合创建索引。

      select * from emp where logincount = 2;

(4)不会出现在where条件中的字段,不应该创建索引。

2,索引的种类

 (1)主键索引,把某列设为主键,此列就成为主键索引。

(2)唯一索引(unique),该列具有唯一性,还是索引。

(3)普通索引(index),

(4)全文索引(FULLTEXT),是有MyISAM存储引擎支持全文索引。

(5)复合索引,多列组成一个索引。

3,索引的操作

(1)添加索引

       create [unique|FULLTEXT] index 索引名 on 表名(列名)

       alter table 表名 add index 索引名(列名)

       以上两种方式没法创建主键索引,创建主键索引语句:alter table 表名 add primary key(列)

(2)删除索引

      drop index 索引名 on 表名

      alter table 表名 drop index index_name

     以上没法删除主键索引,删除主键索引语句如下:alter table 表名 drop primary key

(3)显示索引

       show indexes from 表名

       show keys from  表名

 4,索引的使用

(1)下列几种情况有可能使用索引

     (a)对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。

            news数据表 id title content author ,

            设定title和author为索引title_author,那么查询条件中有title字段时候,使用到title_author索引;如果查询条件没有title,只有author,则使用不到title_author索引。

            alter table news add index title_author (title,author)。

            select * from news where title = '体操奥运会夺冠';  //用到索引

            select * from news where author = '李哲'; //使用不到索引

     (b)对于使用like的查询,查询如果是'%aaa'不会使用到索引,'aaa%'会使用到索引。

            news数据表 id title content author ,

            设定title为索引title_index,在查询条件中title字段开头是%,则不能启用title_index索引;字段开头必须不为%,才会启用索引。

           alter  table news add index title_index(index);

            select * from news where title like '%奥运会夺金';  //没法使用索引

            select * from news where title like '奥运会%夺金';  //使用索引

            select * from news where title like '%奥运会夺金%';  //使用索引

(2)下列几种情况使用不到索引

       (a)如果条件中由or,即使其中有条件带索引也不会使用。

       (b)对于多列索引,不是使用的第一部分,则不会使用索引。

       (c)like查询是以%开头。

       (d)如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。

       (e)如果mysql估计使用全表扫描要比使用索引快,则不使用索引。比如数据表中记录特别少,那可以直接扫描,不用索引。

5,查看索引的使用情况

    show status like 'Handle_read%'

其中,Handle_read_key,这个值越高越好,越高表示使用索引查询到的次数越多;

        Handle_read_rnd_next,这个值越高,说明查询低效;