当前位置：Gxlcms > 数据库问题 > Mysql DBA 高级运维学习笔记-索引知识及创建索引的多种方法实战

Mysql DBA 高级运维学习笔记-索引知识及创建索引的多种方法实战

时间：2021-07-01 10:21:17 帮助过：10人阅读

本文是我学习老男孩老师的Mysql DBA 高级运维课程的学习笔记，老男孩老师讲的很好，非常感谢老男孩老师。刚刚接触运维有很多不懂得知识，如果我发表的文章有不正确的地方，请运维行业的精英，老师及时指点，呵呵~ 后面我还会更新自己学习ysql DBA 高级运维课程的学习笔记。

9.9.7 为表的字段创建索引

索引就像书的目录一样，如果在字段上建立索引，那么以索引为条件时可以加快查询数据的速度。

9.9.7.1 创建主键索引

查询数据库的内容，按主键查询是最快的，每个表只能有一个主键，但是可以有多个普通索引列，主键列要求所有内容必须唯一，而索引列不要求内容唯一。
我们无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引也可以对多列创建索引
建立主键索方法：

1.在创建表时，可以增加建立主键索引语句

  1. <code>system@ceshi 04:0932->create table student(
  2. -> id int(4) not null AUTO_INCREMENT,
  3. -> name char(20) not null,
  4. -> age tinyint(2) NOT NULL default ‘0‘,
  5. -> dept varchar(16) default NULL,
  6. -> primary key(id),
  7. -> KEY index_name(name)
  8. -> );</code>

提示：

a.AUTO_INCREMENT 自增

b.Primary key(id) 主键

c.KEY index_name(name) name 字段普通索引

操作演示

  1. <code>system@ceshi 04:2146->drop table student;
  2. Query OK, 0 rows affected (0.00 sec)
  3. system@ceshi 04:2333->create table student(
  4. -> id int(4) not null AUTO_INCREMENT,
  5. -> name char(20) not null,
  6. -> age tinyint(2) NOT NULL default ‘0‘,
  7. -> dept varchar(16) default NULL,
  8. -> primary key(id),
  9. -> KEY index_name(name)
  10. -> );
  11. Query OK, 0 rows affected (0.00 sec)
  12. system@ceshi 04:2609->desc student;
  13. +-------+-------------+------+-----+---------+----------------+
  14. | Field | Type| Null | Key | Default | Extra  |
  15. +-------+-------------+------+-----+---------+----------------+
  16. | id| int(4)  | NO   | PRI | NULL| auto_increment |
  17. | name  | char(20)| NO   | MUL | NULL||
  18. | age   | tinyint(2)  | NO   | | 0   ||
  19. | dept  | varchar(16) | YES  | | NULL||
  20. +-------+-------------+------+-----+---------+----------------+
  21. 4 rows in set (0.00 sec)</code>

提示：PRI为主键的标识，MUL为普通索引的标识。

2.建立表之后通过alter命令增加主键索引（不推荐这种做法）

a.主键列不能重复创建，必须先删除上面的配置

  1. <code>system@ceshi 04:3716->alter table student drop primary key;</code>

b.建表时忘记加主键了利用alter命令增加id列为自增主键列

  1. <code>system@ceshi 04:5948->alter table student change id id int primary key auto_increment;
  2. Query OK, 0 rows affected (0.00 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 05:0324->desc student;
  5. +-------+-------------+------+-----+---------+----------------+
  6. | Field | Type| Null | Key | Default | Extra  |
  7. +-------+-------------+------+-----+---------+----------------+
  8. | id| int(11) | NO   | PRI | NULL| auto_increment |
  9. | name  | char(20)| NO   | MUL | NULL||
  10. | age   | tinyint(2)  | NO   | | 0   ||
  11. | dept  | varchar(16) | YES  | | NULL||
  12. +-------+-------------+------+-----+---------+----------------+
  13. 4 rows in set (0.00 sec)</code>

提示：只有int类型且为primary key才可以使用auto_increment。

9.9.7.2 创建普通索引

1.在建表时，可以增加建立普通索引列的语句如下：

  1. <code>system@ceshi 04:0932->create table student(
  2. -> id int(4) not null AUTO_INCREMENT,
  3. -> name char(20) not null,
  4. -> age tinyint(2) NOT NULL default ‘0‘,
  5. -> dept varchar(16) default NULL,
  6. -> primary key(id),
  7. -> KEY index_name(name)
  8. -> );</code>

提示：

a.KEY index_name(name)name 字段普通索引

操作演示：

  1. <code>system@ceshi 04:2146->drop table student;
  2. Query OK, 0 rows affected (0.00 sec)
  3. system@ceshi 04:2333->create table student(
  4. -> id int(4) not null AUTO_INCREMENT,
  5. -> name char(20) not null,
  6. -> age tinyint(2) NOT NULL default ‘0‘,
  7. -> dept varchar(16) default NULL,
  8. -> primary key(id),
  9. -> KEY index_name(name)
  10. -> );
  11. Query OK, 0 rows affected (0.00 sec)
  12. system@ceshi 04:2609->desc student;
  13. +-------+-------------+------+-----+---------+----------------+
  14. | Field | Type| Null | Key | Default | Extra  |
  15. +-------+-------------+------+-----+---------+----------------+
  16. | id| int(4)  | NO   | PRI | NULL| auto_increment |
  17. | name  | char(20)| NO   | MUL | NULL||
  18. | age   | tinyint(2)  | NO   | | 0   ||
  19. | dept  | varchar(16) | YES  | | NULL||
  20. +-------+-------------+------+-----+---------+----------------+
  21. 4 rows in set (0.00 sec)</code>

2.建表后利用alter增加普通索引

删除建表时创建的index_name索引

  1. <code>system@ceshi 05:0333->alter table student drop index index_name;
  2. Query OK, 0 rows affected (0.01 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 06:0419->desc student;
  5. +-------+-------------+------+-----+---------+----------------+
  6. | Field | Type| Null | Key | Default | Extra  |
  7. +-------+-------------+------+-----+---------+----------------+
  8. | id| int(11) | NO   | PRI | NULL| auto_increment |
  9. | name  | char(20)| NO   | | NULL||
  10. | age   | tinyint(2)  | NO   | | 0   ||
  11. | dept  | varchar(16) | YES  | | NULL||
  12. +-------+-------------+------+-----+---------+----------------+
  13. 4 rows in set (0.00 sec)</code>

在name列上添加索引，索引名为index_name

  1. <code>system@ceshi 06:0431->alter table student add index index_name(name);
  2. Query OK, 0 rows affected (0.00 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 06:0456->desc student;
  5. +-------+-------------+------+-----+---------+----------------+
  6. | Field | Type| Null | Key | Default | Extra  |
  7. +-------+-------------+------+-----+---------+----------------+
  8. | id| int(11) | NO   | PRI | NULL| auto_increment |
  9. | name  | char(20)| NO   | MUL | NULL||
  10. | age   | tinyint(2)  | NO   | | 0   ||
  11. | dept  | varchar(16) | YES  | | NULL||
  12. +-------+-------------+------+-----+---------+----------------+
  13. 4 rows in set (0.00 sec)</code>

9.9.7.3 对字段的前n个字符创建普通索引

当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一时，这时可以对列的前n个字符建立索引而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低和更新维护消耗的系统资源。

对字段的前n个字符创建索引的语法：

Create index index_name on test(name(8)); 条件列前n个字符创建索引

实战操作：

  1. <code>system@ceshi 06:0838->create index index_dept on student(dept(8));
  2. Query OK, 0 rows affected (0.19 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 06:3650->desc student;
  5. +-------+-------------+------+-----+---------+----------------+
  6. | Field | Type| Null | Key | Default | Extra  |
  7. +-------+-------------+------+-----+---------+----------------+
  8. | id| int(11) | NO   | PRI | NULL| auto_increment |
  9. | name  | char(20)| NO   | MUL | NULL||
  10. | age   | tinyint(2)  | NO   | | 0   ||
  11. | dept  | varchar(16) | YES  | MUL | NULL||
  12. +-------+-------------+------+-----+---------+----------------+
  13. 4 rows in set (0.00 sec)
  14. system@ceshi 06:3847->show index from student\G
  15. *************************** 1. row ***************************
  16.    Table: student
  17.   Non_unique: 0
  18. Key_name: PRIMARY
  19. Seq_in_index: 1
  20.  Column_name: id
  21.    Collation: A
  22.  Cardinality: 0
  23. Sub_part: NULL
  24.   Packed: NULL
  25. Null: 
  26.   Index_type: BTREE
  27.  Comment: 
  28. *************************** 2. row ***************************
  29.    Table: student
  30.   Non_unique: 1
  31. Key_name: index_name
  32. Seq_in_index: 1
  33.  Column_name: name
  34.    Collation: A
  35.  Cardinality: NULL
  36. Sub_part: NULL
  37.   Packed: NULL
  38. Null: 
  39.   Index_type: BTREE
  40.  Comment: 
  41. *************************** 3. row ***************************
  42.    Table: student
  43.   Non_unique: 1
  44. Key_name: index_dept
  45. Seq_in_index: 1
  46.  Column_name: dept
  47.    Collation: A
  48.  Cardinality: NULL
  49. Sub_part: 8
  50.   Packed: NULL
  51. Null: YES
  52.   Index_type: BTREE
  53.  Comment: 
  54. 3 rows in set (0.00 sec)</code>

9.9.7.4 为表的多个字段创建联合索引

如果查询的条件是多列时，我们可以为多个查询的列创建联合索引，甚至可以为多列的前n个字符创建联合索引，实战演示如下：

为多个列创建索引

  1. <code>system@ceshi 06:4019->create index ind_name_dept on student(name,dept);
  2. Query OK, 0 rows affected (0.01 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 06:4726->show index from student\G
  5. *************************** 1. row ***************************
  6.    Table: student
  7.   Non_unique: 0
  8. Key_name: PRIMARY
  9. Seq_in_index: 1
  10.  Column_name: id
  11.    Collation: A
  12.  Cardinality: 0
  13. Sub_part: NULL
  14.   Packed: NULL
  15. Null: 
  16.   Index_type: BTREE
  17.  Comment: 
  18. *************************** 2. row ***************************
  19.    Table: student
  20.   Non_unique: 1
  21. Key_name: index_name
  22. Seq_in_index: 1
  23.  Column_name: name
  24.    Collation: A
  25.  Cardinality: NULL
  26. Sub_part: NULL
  27.   Packed: NULL
  28. Null: 
  29.   Index_type: BTREE
  30.  Comment: 
  31. *************************** 3. row ***************************
  32.    Table: student
  33.   Non_unique: 1
  34. Key_name: index_dept
  35. Seq_in_index: 1
  36.  Column_name: dept
  37.    Collation: A
  38.  Cardinality: NULL
  39. Sub_part: 8
  40.   Packed: NULL
  41. Null: YES
  42.   Index_type: BTREE
  43.  Comment: 
  44. *************************** 4. row ***************************
  45.    Table: student
  46.   Non_unique: 1
  47. Key_name: ind_name_dept
  48. Seq_in_index: 1
  49.  Column_name: name
  50.    Collation: A
  51.  Cardinality: NULL
  52. Sub_part: NULL
  53.   Packed: NULL
  54. Null: 
  55.   Index_type: BTREE
  56.  Comment: 
  57. *************************** 5. row ***************************
  58.    Table: student
  59.   Non_unique: 1
  60. Key_name: ind_name_dept
  61. Seq_in_index: 2
  62.  Column_name: dept
  63.    Collation: A
  64.  Cardinality: NULL
  65. Sub_part: NULL
  66.   Packed: NULL
  67. Null: YES
  68.   Index_type: BTREE
  69.  Comment: 
  70. 5   rows in set (0.00 sec)</code>

可以对多个列的前n个字符创建联合索引，实战演示如下：

  1. <code>system@ceshi 06:5144->drop index ind_name_dept on student; 删除索引的另一种方法。
  2. Query OK, 0 rows affected (0.01 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 06:5158->create index ind_name_dept on student(name(8),dept(10));
  5. Query OK, 0 rows affected (0.00 sec)
  6. Records: 0  Duplicates: 0  Warnings: 0</code>

提示：

按条件列查询数据时，联合索引是有前缀生效特性的

Index(a,b,c)仅a,ab,abc三个查询条件可以走索引。b,bc,ac,c不能走索引

9.9.7.5 创建唯一索引（非主键索引）

Create unique index index_age on student(age);

唯一索引是用来约束表的内容的，不能重复。

  1. <code>system@ceshi 07:0828->create unique index uni_ind_name on student(name);
  2. Query OK, 0 rows affected (0.00 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4. system@ceshi 07:1010->desc student;
  5. +-------+-------------+------+-----+---------+----------------+
  6. | Field | Type| Null | Key | Default | Extra  |
  7. +-------+-------------+------+-----+---------+----------------+
  8. | id| int(11) | NO   | PRI | NULL| auto_increment |
  9. | name  | char(20)| NO   | UNI | NULL||
  10. | age   | tinyint(2)  | NO   | | 0   ||
  11. | dept  | varchar(16) | YES  | MUL | NULL||
  12. +-------+-------------+------+-----+---------+----------------+
  13. 4 rows in set (0.00 sec)</code>

9.9.7.6 索引表的创建及生效条件

问题1：既然索引可以加快查询速度，那么是不是就要给所有的列创建索引呢？

解答：因为索引你不但占用系统空间，更新数据库时还需要维护索引数据。因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需创建索引，更新频繁，读取少的业务要少建立索引。

问题2：需要在哪些列上创建索引呢？

解答：Select user,host from mysql.user where host= ….索引一定要创建在where后的条件列上，而不是select后的选择数据的列上，另外我们要尽量选择在唯一值多的大表上建立索引。创建索引要和开发商量。

9.9.7.7 创建索引命令集合小结

1.创建索引相关命令集合

创建主键索引

  1. <code>alter table student change id id int primary key auto_increment;</code>

删除主键索引

  1. <code>alter table student drop primary key</code>

创建普通索引

  1. <code>alter table student add index index_dept(dept(8))</code>

根据列的前n个字符创建索引

  1. <code>create index index_dept on student(dept(8))</code>

根据多个列创建联合索引

  1. <code>create index ind_name_dept on student(name,dept);</code>

根据多个列的前n个字符创建索引

  1. <code>create index ind_name_dept on student(name(8),dept(10));</code>

删除普通索引

  1. <code>Alter table student drop index index_dept
  2. drop index ind_name_dept on student</code>

创建唯一索引

  1. <code>create unique index uni_ind_name on student(name);</code>

基本索引：

a.要在表的列上创建索引。

b.索引会加快查询速度，但是会影响更新的速度，因为要维护索引。

c.索引不是越多越好，要在频繁查询的where后的条件列上创建索引。

d.小表或唯一值极少的列上不建索引，要在大表以及不同内容多的列上创建索引。

Mysql DBA 高级运维学习笔记-索引知识及创建索引的多种方法实战

标签：unique   duplicate   ati   test   show   ima   war   ceshi   使用   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！