MySQL基础优化之索引管理

MySQL数据库中索引的类型介绍

 

• BTREE:B+树索引 （日常所见大部分为此种索引）
• HASH：HASH索引
• FULLTEXT：全文索引
• RTREE：R树索引

 

MySQL索引管理

• 索引建立在表的列上(字段)的。
• 在where后面的列建立索引才会加快查询速度。
• pages<---索引（属性）<----查数据。
• 索引分类：

　　主键索引

　　普通索引

　　唯一索引

 

 

添加索引和删除索引的两种方式

 

第一种：

alter table test add index index_name(name);
alter table test drop index idx_name;

　　

 

第二种：

create index index_name on test(name);
drop index idx_stu_name on t1;

　　

查看索引的两种方式

 

第一种方式：

mysql> desc t1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(5)      | YES  |     | NULL    |       |
| stu_name | varchar(20) | YES  | MUL | NULL    |       |
| age      | int(3)      | YES  |     | 28      |       |
+----------+-------------+------+-----+---------+-------+

　　

 

第二种方式：

mysql> show index from t1G;
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_stu_name
 Seq_in_index: 1
  Column_name: stu_name
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

第二种方式的好处就是可以查看到索引的名称，索引的类型为BTREE索引，便于删除索引时指定删除哪个索引。

 

 

用explain查看SQL的执行计划

mysql> select * from t1;
+------+----------+------+
| id   | stu_name | age  |
+------+----------+------+
|    1 | tom      |   28 |
|    2 | liliy    |   28 |
|    3 | lucy     |   28 |
|    4 | lintao   |   28 |
|    5 | alex     |   28 |
+------+----------+------+
5 rows in set (0.00 sec)

mysql> show index from t1G;
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_stu_name
 Seq_in_index: 1
  Column_name: stu_name
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

#  只有stu_name这一列有索引

mysql> explain select * from t1 where id=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where stu_name='lintao';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t1    | ref  | idx_stu_name  | idx_stu_name | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

由上述可知由于t1表只有一个stu_name列由索引，用id来查询的属于全表扫描，因为类型为:ALL，而用stu_name查找时则命中了索引，类型为: ref

 

 

MySQL中的约束索引

1. 主键索引：只能有一个主键。

• 主键索引:列的内容是唯一值,高中学号.
• 表创建的时候至少要有一个主键索引，最好和业务无关。
• 走主键索引的查询效率是最高的，我们尽量每张表都有一个主键，并且将来查询的时候最好能用主键进行查询。

 

创建主键的方式：

 

1. 建表时创建

CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

　　

 

2. 建立表后创建

CREATE TABLE `test` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
# 增加自增主键
alter table test change id id int(4) primary key not null auto_increment;

　　

2. 普通索引

• 加快查询速度，工作中优化数据库的关键。
• 在合适的列上建立索引，让数据查询更高效。

create index index_name on test(name);
alter table test add index index_name(name);

用了索引，查一堆内容。

在where条件关键字后面的列建立索引才会加快查询速度.

select id,name from test where state=1 order by id group by name;　

 

3、唯一索引

内容唯一，但不是主键。

需要在普通索引前面加一个unique

 

create unique index index_name on test(name);

　　

 

4. 前缀索引

含义：根据字段的前N个字符建立索引。

存在的原因是因为索引在建立的时候一般会把自动对当前列进行排序。如果该列的字符列特别长，那么建索引排序会非常耗时，同时索引也会非常的大，索引我们会选该列前几个字符创建索引。

 

create index index_name on test(name(8));

　　

5. 联合索引

含义：多个字段建立一个索引

 

where a女生 and b身高165 and c身材好

index(a,b,c)

特点：前缀生效特性。

a,ab,abc  可以走索引(必须以a开头)。

b ac bc c 不走索引。

原则：把最常用来作为条件查询的列放在前面。

 

走索引：

select * from people where a='nv' and b>=165 and tizhong<=120;

select * from people where a='nv' and b>=165;

select * from people where a='nv';

select * from people where a='nv' and tizhong<=120;

 

alter table test add index union_idx_name(name, age, gender);

　　

 6. 联合主键是联合索引的特殊形式

PRIMARY KEY (`Host`,`User`)
alter table test add sex char(4) not null;
create index ind_name_sex on test(name,sex);

　　 

7. 前缀加联合索引

create index index_name on test(name(8),sex(2));

 

 

 

索引的企业应用场景（1）

 

企业SQL优化思路：

1、把一个大的不使用索引的SQL语句按照功能进行拆分

2、长的SQL语句无法使用索引，能不能变成2条短的SQL语句让它分别使用上索引。

3、对SQL语句功能的拆分和修改

4、减少“烂”SQL

　　–  由运维（DBA）和开发交流（确认），共同确定如何改，最终由DBA执行

5、制定开发流程 

 

 

索引的企业应用场景（2）

 

不适合走索引的场景：

1、唯一值少的列上不适合建立索引或者建立索引效率低。例如：性别列

2、小表可以不建立索引，100条记录。

3、对于数据仓库，大量全表扫描的情况，建索引反而会慢

查看表的唯一值数量：

select count(distinct user) from mysql.user; select count(distinct user,host) from mysql.user;

　　

 

索引的企业应用场景（3）

 

建索引流程：

1、找到慢SQL。

   show processlist;

   记录慢查询日志。

 

2、explain select句,条件列多。

 

3、查看表的唯一值数量：

select count(distinct user) from mysql.user; select count(distinct user,host) from mysql.user;

条件列多。可以考虑建立联合索引。

 

4、建立索引(流量低谷)

force index

 

5、拆开语句（和开发）。

 

6、like '%%' 不用mysql