MySQL优化——索引使用

今天药忘吃喽~ 2022-06-01 12:42 159阅读 0赞

# 前提 #

在项目上线过程中发现表中没有加索引，造成服务器cpu迅速飙升，影响了系统的线上环境。趁着这个计划温故一下索引的使用方式。

# 使用索引好处 #

使用索引会提升速度主要原因是利用btree方式对数据进行维护，检索的时候使用索引的次数就是log2N次数

# 索引种类 #

##      主键索引 ##

把一张表某个列设为主键的时候，则该列则就是主键索引。

操作方式

-- 方式一（创建的时候建立主键索引）
    create TABLE test1(
    id int UNSIGNED PRIMARY key auto_increment,
    name VARCHAR(32) not null DEFAULT""
    );
    -- 方式二（创建之后修改主键索引）
    CREATE TABLE test2 (
    	id INT,
    	NAME VARCHAR (32) NOT NULL DEFAULT ""
    );
    
    alter TABLE test2 add PRIMARY KEY(id);

##      唯一索引 ##

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

-- 方式一(创建的时候添加唯一索引)
     CREATE TABLE test3(id int PRIMARY key auto_increment,name VARCHAR(32) UNIQUE);
    -- 方式二（创建之后修改添加唯一索引）
    CREATE TABLE test4(id int PRIMARY key auto_increment,name VARCHAR(32))
    CREATE UNIQUE INDEX name on test4(name);
    create UNIQUE index 索引名 on 表名（列名）;

##       普通索引 ##

-- 方式一 普通索引
    CREATE TABLE test5(id int UNSIGNED,name VARCHAR(32));
    create index 索引名 on 表名(列)；

##     全文索引 ##

这个主要针对在长文本中查询关键信息等内容

-- -- 方式一 创建全文 
    CREATE TABLE `test7` (
      `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;
    -- 方式二 先建表后创建索引
    CREATE TABLE `test8` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(200) DEFAULT NULL,
      `content` text,
      PRIMARY KEY (`id`)
    ) ENGINE=myisam DEFAULT CHARSET=utf8;
    -- 给现有的test8表的title和content字段创建全文索引
    -- 索引名称为fulltext_article
    ALTER TABLE article
    ADD FULLTEXT INDEX fulltext_article (title, content)

小编通过这篇文章主要讲解一下全文索引

# 如何使用全文索引？ #

**错误用法：**

select \* from test8 where content like ‘%mysql%’；

可以利用执行计划分析一下 explain select \* from test8 where content like ‘%mysql%’；就会知道没有用到执行计划了。

**正确的用法是：**

select \* from test8 where match(title,body) against('database');

**注意 **

全文索引在MySQL5.6.4之前Innodb是不支持全文索引的，只有myisam支持全文索引，而且仅支持英文字符的全文索引，但是可以通过使用sphinx或者coreseek帮助实现对中文的索引。在mysql5.6.4以及之后的版本中innodb也开始支持全文索引，所以使用全文索引的时候同样需要注意mysql的版本问题。

# 如何选择创建索引的字段 #

1、where中经常使用的条件

2、唯一性比较好的字段，即该字段不是唯一的几个值

3、字段内容不是频繁变化的

# 索引使用 #

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

**特殊情况**

对于创建了多列索引的，只有查询条件使用了最左边的列，索引一般就会被使用；

对于like查询，如果查询条件是'%aaa'则不会使用索引，但是‘aaa%'就会使用索引

以下情况将不会使用索引

如果条件中有or，即使有条件带索引也不会使用；

对于多列索引，不是使用的第一部分，则不会使用索引