mysql 索引

mysql索引是mysql一般优化比不可少的话题，但并不是索引越多性能就越好，索引也需要开销。

PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` ) 

UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE (`col`)

INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEX index_name (`col`)

FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT ( `col` )
组合索引   ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` )

索引的优点:

       可以提高大数据量下查询速度

索引的缺点:

       会影响insert、update、delete等的速度,会占用空间磁盘大小，在mysql安装路径的data、数据库名下，文件后缀是.MYI的文件格式,以表名为文件名的格式,见下图

1.索引要创建在where和join用到的字段上.

2.以下操作符号可以应用索引

：<，<=，=，>，>=，BETWEEN，IN，LIKE不以%_开头

以下需要注意不使用索引

<>, NOT IN, LIKE %_开头

<> 可以用 a>1 or a<3 代替,NOT IN 可以用NOT exists代替

3.在使用max() min()时最好加上索引.

4.单索引要创建在确实需要的地方

5多列索引 有最佳左前缀特性 所以尽量左边的字段是最常用的.

6.索引不会包括有NULL值,就是有NULL,索引就失效

7.使用短索引,一个字段字太多,可以建立部分索引,只取前十个字.节约空间.

8.不要在列上运算.例如:where MD5("password") = "myz"

●索引的创建

主键索引:

创建表时设置某列为主键，该列就是主键索引.看下图

表名是salgrade、key_name 指的是索引的名称，即主键(PRIMARY)索引   当然我是在这张表的grade列设置为主键的.index_type是用的索引方法，BTREE指的是二叉树算法，索引底层调用的二叉树算法来进行查询速度的提升，相关算法请自行百度,

二叉树大概原理

  比如11条数据，首先从中间取6，如果是，就取出，不是再分为12345和789 10 11，再从12345取中间就是3  然后分为12 45   然后12中向上取整就是2 剩余1 原理大概就是这样。

普通索引:

一般来说普通索引的创建是先建表，再创建普通索引   create index 索引名 on 表(列名)

    这张表我有5条数据

再次查看索引show index from 表名

创建好了，索引名是aaa、bbb，其他的就不解释了.本地我用400W数据查询的时候用了1秒多，用索引后一秒不到 基本是0.00SEC，有兴趣可以自行测试，测试请用数据量百万级上的，不然不好看出差别

全文索引:

mysql引擎为MyISAM才会生效，innodb不会生效，即使创建了全文索引在innodb下也不会生效，好像也只对英文有效哦，汉字不生效的，用分词处理

例如：我们有一个文章表（article），其中有主键ID（id）、文章标题（title）、文章内容（content）三个字段。现在我们希望能够在title和content两个列上创建全文索引，article表及全文索引的创建SQL语句如下：

CREATE TABLE `article` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `title` varchar(200) DEFAULT NULL,

  `content` text,

  PRIMARY KEY (`id`),

  FULLTEXT KEY `title` (`title`,`content`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

再查看索引

可以看到索引名都是title，但是运用在title和content列上的。

如何使用全文索引？

select * from article WHERE title like 'mysql%'；这种不能用到全文索引，错误用法

我们可以验证一下，用 EXPLAIN select * from article WHERE title like 'mysql%'   看结果

没有用到索引。其他的列表示的意思以及EXPLAIN关键字的用法网上很多资料

正确的用法:

select * from articles where match(title,content) against(‘database’);

看看是否用到索引:

用到了索引

 

唯一索引:

当表的某列被指定为unique约束时，这列就是一个唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

这时, name 列就是一个唯一索引.

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.其实主键也是唯一索引，一般主键都是不重复的

语法：create unique index 索引名  on 表名 (列表);

 

查看表的索引

desc 表名 【该方法的缺点是：　不能够显示索引名.】

show index from 表名

show keys from 表名

删除索引

alter table 表名 drop index 索引名;

如果删除主键索引。

alter table 表名 drop primary key

修改索引

先删除，再重新创建