MySQL学习笔记整理（三）

发表于 | 分类于

前言

       上篇已经建立了测试数据库，然后通过相关工具找到了慢查询的语句，这些就是我们优化的对象，本篇就从索引的角度去改善我们的数据库性能。共四种索引，主键索引、唯一索引、普通索引、全文索引。索引是有消耗的，占空间的，MYI格式文件在data文件夹下。

       索引能使查询变快的原理：先建了一个索引文件，就是MYI文件，然后建立查找二叉树（也叫B树，二叉搜索树，二叉排序树）。利用了二叉树算法，使搜索性能逼近二分查找，但是他比连续内存空间的二分查找优点是，插入和删除不需要移动大量数据，通常是常数开销。记录下了对应索引数值的物理位置，所以数据库换了路径，索引也失效了。

       学会用并利用explain来分析语句。

建立适当的索引

主键索引

       当一张表，把某个列设为主键的时候，则该列就是主键索引。主键不能为null且不允许重复。

create table aaa
(id int unsigned primary key auto_increment ,
name varchar(32) not null defaul ‘’);

1

       这是id 列就是主键索引。

       如果你创建表时，没有指定主键索引，也可以在创建表后，在添加指令:

alter table 表名 add primary key (列名);

2

       举例:

create table bbb (id int , name varchar(32) not null default ‘’);

alter table bbb add primary key (id);

3

       添加之后发现库文件MYI文件变大。我们在我们测试样本的emp库建立主键索引，且前后查询对比如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from emp where empno=3456789;
+---------+--------+----------+-----+------------+---------+--------+--------+
| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+---------+--------+----------+-----+------------+---------+--------+--------+
| 3456789 | mFmwwU | SALESMAN |   1 | 2017-05-05 | 2000.00 | 400.00 |     82 |
+---------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.85 sec)
mysql> alter table emp add primary key(empno);
Query OK, 4000000 rows affected (9.47 sec)
Records: 4000000  Duplicates: 0  Warnings: 0
mysql> select * from emp where empno=3456789;
+---------+--------+----------+-----+------------+---------+--------+--------+
| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+---------+--------+----------+-----+------------+---------+--------+--------+
| 3456789 | mFmwwU | SALESMAN |   1 | 2017-05-05 | 2000.00 | 400.00 |     82 |
+---------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.00 sec)

       发现时间明显变少，这就是主键索引的作用，按主键单位时间定为到准确位置，而不是从头开始找。

普通索引

       一般来说，普通索引的创建，是先创建表，然后在创建普通索引。

       比如:

//先建表
create table ccc(
id int unsigned,
name varchar(32)
)
//添加索引
create index 索引名 on 表 (列1,列名2);

创建全文索引

       全文索引，主要是在很针对文件、文本的检索、比如文章，全文索引仅针对MyISAM有用。我们在测试样本temp库中创建下表，并插入测试数据如下：

1

mysql> CREATE TABLE articles (
    ->        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->        title VARCHAR(200),
    ->        body TEXT,
    ->        FULLTEXT (title,body)
    ->      )engine=myisam charset utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO articles (title,body) VALUES
    ->      ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ->      ('How To Use MySQL Well','After you went through a ...'),
    ->      ('Optimizing MySQL','In this tutorial we will show ...'),
        ->      ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ->      ('MySQL vs. YourSQL','In the following database comparison ...'),
    ->      ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

2

然后使用下面命令看是否成功,其中\G代表按行打印：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> show index from articles\G;
*************************** 1. row ***************************
        Table: articles
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: title
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: body
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

3

       使用explain来分析一个错误使用全文索引的语句。

mysql> explain select * from articles where body like "%mysql%"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: ALL
possible_keys: NULL
      key: NULL
  key_len: NULL
      ref: NULL
     rows: 6
 filtered: 16.67
    Extra: Using where
1 row in set, 1 warning (0.00 sec)

可看到possible_keys: NULL，key: NULL，说明没用到，下面例子就看到使用到了title。

4

正确的用法：WHERE MATCH(title,body) against(‘database’); 
mysql> explain select * from articles where match(title,body)     against('database')\G; 
*************************** 1. row ***************************
       id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
     type: fulltext
possible_keys: title
          key: title
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

       说明：

  1. 在mysql中fulltext 索引只针对 myisam生效
  2. mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
  3. 使用方法是 match(字段名..) against(‘关键字’)
  4. 全文索引一个 叫 停止词, 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select match(title,body) against('a') from articles;
+--------------------------------+
| match(title,body) against('a') |
+--------------------------------+
|                              0 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
+--------------------------------+
6 rows in set (0.00 sec)

唯一索引

       当表的某列被指定为unique约束时，这列就是一个唯一索引。按照以下方式建表，name列就是唯一索引。唯一索引允许为空，而且允许多个。

1

mysql> create table ddd(id int primary key auto_increment , name varchar(32)     unique);
Query OK, 0 rows affected (0.02 sec)

2

       在创建表后，再去创建唯一索引，见如下指令：

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

create unique index 索引名  on 表名 (列表..);

查询索引

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

show index(es) from 表名

show keys from 表名

1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
mysql> desc articles
    -> ;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  | MUL | NULL    |                |
| body  | text             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show index from articles\G;
*************************** 1. row ***************************
        Table: articles
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: title
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: body
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)
ERROR: 
No query specified
mysql> show keys from articles\G;
*************************** 1. row ***************************
        Table: articles
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: title
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: body
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

删除索引

alter table 表名 drop index 索引名;

如果删除主键索引。

alter table 表名 drop primary key

修改索引

先删除再重新创建。

小结

本节介绍的如何操作索引和索引能加快查询的原理。下篇我们看看怎么去分析索引的性能，比如explain用法，然后我们该怎么去适用索引最恰当。

说明

       文中出现的图片，文字描述有些来自互联网，但是出处无法考究，如果侵犯您的相关权益，请联系我，核实后我会马上加上转载说明。谢谢！！！