Mysql优化4-合适的索引

四中索引[普通索引、主键索引、唯一索引、全文索引]

一、索引的CURD

1、添加

1.1 主键索引添加：把一张表的一个字段设置为主键 该字段就位主键索引 id int unsigned primary key auto_increment

      unsigned 有符号和无符号的区别：

在MYSQL中整型范围：

类型                 大小            范围（有符号）                              范围（无符号） 用途

TINYINT           1 字节    (-128，127)                                    (0，255) 小整数值

SMALLINT        2 字节    (-32 768，32 767)                           (0，65 535) 大整数值

MEDIUMINT     3 字节    (-8 388 608，8 388 607)                  (0，16 777 215) 大整数值

INT或INTEGER  4 字节    (-2 147 483 648，2 147 483 647)     (0，4 294 967 295) 大整数值

1.2 普通索引

     一般先创建表 然后再创建普通索引 create index 索引名称  on table (列)

创建索引后比如再200W条数据 查询速度相当快

为什么创建索引之后查询速度回如此之快

index_type 是二叉树 Btree

1.3 全文索引 fulltext

      全文索引主要是针对文件、文本的检索 比如文章  主要针对MyISAM有用

      针对英文有效 中文使用sphinx mysql自己提供的

     

     

1.4 唯一索引

      unique 唯一索引  可以为空 并可以多个 有内容必须唯一

      主键字段不能为空 并且

2、查询索引

          1、desc 名表

          2、show index from 表明

          3、show keys from 表名

3、删除索引

       alter table 表名 drop index 索引名

         删除主键索引 alter table 表名 drop primary key 索引名称

4、修改

     先删除在新增

二、索引使用的注意事项

1、索引使用有磁盘的占用 有索引文件

2、索引查询速度回变快  增删修回去变慢 因为要平衡二叉树

三、再那些情况下适合创建索引

1、较为频繁作为查询条件的应该创建索引 肯定在where中经常出现 比如部门标号

2、唯一性太差的不适合作为索引 比如性别

3、更新频繁的字段不适合作为索引 比如登录次数

4、不会出现在where条件的不适合做为索引

四、如何高效使用索引

1、如果我们表中有复合索引(索引再多列上) 我们需要注意

比如 alter dept add index myind(dname,loc);

复合索引只要使用了左边的列 一般都会使用索引

select * from dept where loc = 'd22'

使用了右边的 没有使用到索引

2、使用模糊查询的时候 注意百分号位置 左边用不到索引

 select * from emp where dname like ‘%技术部门’ 用到索引  百分号再右边才能用到索引，如果一定要使用 则使用全文索引

3、

4、查看索引的使用情况

show status like ‘Handler_read%’

知道就好 下图：