13.1 mysql索引优化基本要点（即4.mysql应用管理实践(1)下半部16开始）

=============================
16.索引知识及索引创建多种方法实践
为表的字段创建索引
索引像书的目录一样，如果在字段上建立了索引，以索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。
索引分类：
（1）创建主键索引
查询数据库，按照主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一。
主键类似于我们的学号，班级内唯一。整个表的每一条记录的主键值在表内是唯一的，用来唯一标识一条记录。
主键就像班级的学号，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列建索引，也可以对多列创建索引。

mysql> drop table student;//删除之前建好的表
Query OK, 0 rows affected (0.05 sec)

mysql> desc student;
ERROR 1146 (42S02): Table 'oldboy.student' doesn't exist
法一：建表时增加主键索引：
mysql> create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char (20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id), //主键索引
-> KEY index_name (name)//普通索引
-> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc student; //查看新建立的表
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
法二：建表后通过alter命令增加主键索引（之前忘加了）：
mysql> drop table student;
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;
ERROR 1146 (42S02): Table 'oldboy.student' doesn't exist
mysql> create table student(
-> id int(4) not null,
-> name char (20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> KEY index_name (name)
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student change id id int primary key auto_increment;//建表后通过alter命令增加主键索引
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

（2）创建普通索引（包含唯一索引和不唯一索引（真正普通索引））
法一：
同上
法二：建表后利用alter增加普通索引
mysql> alter table student drop index index_name;//删除建表时创建的index_name普通索引
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table student add index index_name(name);//在name列上添加索引，索引名为index_name
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

（3）对字段的前n个字符创建普通索引
当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一时，这时可以对列的前n个字符建立索引，而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低读取和更新维护索引消耗的系统资源。
mysql> create index index_dept on student(dept(8));//dept列的前8个字符建立普通索引
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from student\G //查看索引更详细。

（4）为表的多个字段创建联合索引
若查询数据的条件是多列时，可以为多个查询的列创建联合索引；甚至可以为多列的前n个字符列创建联合索引。
联合索引允许列上面有自己的索引。
mysql> create index ind_name_dept on student(name,dept);//a.多个查询的列创建联合索引
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from student \G //查看索引更详细。

mysql> drop index ind_name_dept on student;//删除联合索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index ind_name_dept on student(name(8),dept(10));//b.多列的前n个字符列创建联合索引
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student \G

（5）创建唯一索引（非主键）
mysql> create unique index uni_ind_name on student(name);//创建唯一索引
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | UNI | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

=============================
17.索引列的创建及生效条件
索引不但占用系统空间，更新数据库时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好。
例如：数十到几百行的小表上无需建立索引，写频繁，读少的业务要少建立索引。

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

小结：
（1）要在表的列上创建索引；
（2）索引会加快查询速度，但是会影响更新的速度；
（3）索引不是越多越好，要在频繁查询的where后的条件列上创建索引；
（4）小表或者唯一值极少的列上不建索引，要在大表以及不同内容多的列上创建索引。
（5）
grant all privileges on test.* to oldboy@localhost identified by 'oldboy123';

create user oldgirl@localhost identified by 'oldgirl123';
grant all on test.* to oldgirl@localhost;

show grants for oldboy@localhost;
（6）
desc student;
建表后通过alter命令增加主键索引，主键名为key（法二）：
alter table student change id id int primary key auto_increment;
删除建表时创建的主键索引：
alter table student drop primary key;//法一

在name列上添加普通索引，索引名为index_name（法二）：
alter table student add index index_name(name);
删除建表时创建的普通索引:
alter table student drop index index_name;

dept列的前8个字符建立普通索引:
create index index_dept on student(dept(8));
a.多个查询的列创建联合索引:
create index ind_name_dept on student(name,dept);
删除联合索引:
drop index ind_name_dept on student;//法二

b.多列的前n个字符列创建联合索引:
create index ind_name_dept on student(name(8),dept(10));
删除联合索引:
drop index ind_name_dept on student;

创建唯一索引:
create unique index uni_ind_name on student(name);

posted @ 2018-02-07 19:37 bkycrmn 阅读(96) 评论(0) 收藏举报

刷新页面返回顶部

13.1 mysql索引优化基本要点（即4.mysql应用管理实践(1)下半部16开始）

公告