11、mysql索引详解
1、索引介绍:
2、建立索引的方法:
注意:索引名称不要相同;
(1)在建表的时候,可以增加主键索引的语句如下:
1)例一:
create table student1 (
id int(4) not null auto_increment,
name varchar(20) not null,
primary key (id),
key index_name (name)
);
primary key(id), #设置主键索引,主键索引是id字段;
key index_name(name) #设置普通索引,index_name代表是索引的名称,name是索引列;
mysql> desc student1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
|name| varchar(20)| NO | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
PRI:表示的是主键索引;
MUL:表示的普通索引;
2)例二:
3)唯一索引:
在建表时在需要列中加 uniq 参数;
(2)在建表后给表添加索引:
1)添加主键索引:
mysql> alter table student change id id int primary key auto_increment;
提示:删除主键的方法:alter table student drop primary key;
当表有主键索引时不好删除,不会去动删除主键的;
2)添加普通索引:
删除名为"index_name"的普通索引(因为前面在建表时已经添加过了索引);
方法一:
mysql> alter table student1 drop index index_name;
方法二:
mysql> drop index index_name on student1;
添加名为"index_name"的普通索引:
方法一:
mysql> alter table student1 add index index_name (name);
方法二:
mysql>create index index_name on student1 (name);
3)对字段的前n个字符创建索引:
方法一:
mysql>alter table student1 add index index_name1 (name(8));
index_name1 :代表索引的名字;
student(name(8)):代表取student表name字段的前8个字符;
方法二:
mysql> create index index_name1 on student1 (name(8));
4)查看表的详细索引:
mysql> show index from student1\G
*************************** 1. row ***************************
Table: student1
Non_unique: 0 #是否唯一:是
Key_name: PRIMARY #索引名称为"PRIMARY"主建索引;
Seq_in_index: 1 #联合索引;
Column_name: id #列名;
Collation: A
Cardinality: 0
Sub_part: NULL #索引范围;
Packed: NULL
Null:
Index_type: BTREE #btree索引类型(默认);
Comment: #字段描述;
Index_comment:
*************************** 2. row ***************************
Table: student1
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student1
Non_unique: 1
Key_name: index_name1
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
5)为表的多个字段字段创建联合索引:
联合索引允许表的字段上有自己的索引:
方法一:
mysql> alter table student add index index_name_dept (name,dept(8));
方法二:
mysql> create index index_name_dept on student (name,dept(8));
查看索引:
mysql> show index from student\G;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_name_dept
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_name_dept
Seq_in_index: 2
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
ERROR:
No query specified
6)创建唯一索引(非主键):
方法一:
mysql> alter table student1 add unique index uniq_name (name);
方法二:
mysql> create unique index uniq_name on student1 (name);
查看:
mysql> desc student1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name| varchar(20) |NO | UNI | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show index from student1\G;
*************************** 2. row ***************************
Table: student1
Non_unique: 0
Key_name: uniq_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
show index from table\G;参数详解:
Table
#表的名称。
Non_unique
#如果索引不能包括重复词,则为0。如果可以,则为1。
Key_name
#索引的名称。
Seq_in_index
#索引中的列序列号,从1开始。
Column_name
#列名称。
Collation
#列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。[InnoDB都是A,Heap表都是NULL]
Cardinality
#非常关键的一个参数。表示的是索引中唯一值的数目的估计值。
#Cardinality/n_rows_in_table的值应尽可能接近1,如果非常小,那么用户需要考虑是否可以删除该索引。
Sub_part
#如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
Packed
#指示关键字如何被压缩。如果没有被压缩,则为NULL。
Null
#索引的列中含有NULL。含有NULL则为YES。如果没有,则这里显示为空。
Index_type
#索引的类型(BTREE, FULLTEXT, HASH, RTREE)。这里是InnoDB存储引擎,所以显示的都是BTREE
Comment
#多种评注。
3、主键索引和唯一索引的区别:
主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。
主键创建后一定包含一个列不为空的唯一性索引,唯一性索引并不一定就是主键。
唯一性索引列允许空值,而主键列不允许为空值。
主键可以被其他表引用为外键,而唯一索引不能。
一个表最多只能创建一个主键,但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。
在 RBO 模式下,主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。
4、索引列的创建及生效的条件:
5、创建索引命令总结:
(1)创建主键索引:
alter table student change id id int primary key auto_increment;
(2)删除主键索引:
alter table student drop primary key;
(3)创建普通索引:
alter table student add index index_name (name);
create index index_name on student (name);
(4)删除普通索引:
alter table student drop index index_name;
drop index index_name on student;
(5)根据前n个字符创建索引:
alter table student add index index_name (name(8));
create index index_name on student (name(8));
(6)根据多个列创建联合索引:
alter table student add index index_name_dept (name,dept(8));
create index index_name_dept on student (name,dept (8));
(7)创建唯一索引:
alter table student add unique index uniq_name (name);
create unique index uniq_name on student (name);
(8)查看索引:
1)通过表结构查看:
desc student;
2)通过索引命令查看:
show index from student;
(9)创建索引要注意的时间:
1)要在表的列上创建索引;
2)索引会加快查询的速度,但是会影响更新速度;
3)索引不是越多多好,要在频繁查询的where后的条件列上创建索引;
4)小表或唯一值极少的列上不建索引,要在大表及不同内容多的列上创建索引;
5)一般"="查询会走索引,一般模糊查询不会走索引;
6)一般不会使用mysql的索引,而是使用其它的索引软件,因为mysql自带的索引效果很差;
11.1、DML之select语句:
1、语法:
(1)根据指定的字段查询数据:
select <字段1,字段2,.....> from <表名> where <表达式>;
#select,from,where是不能够随便改的,是关键字,支持大小写;
(2)查询表结构中所有字段的数据:
select * from <表名> where <表达式>;
(3)优化:最好不要用"*"去查询数据,因为太慢了,最好是指定字段去查询数据;
2、应用实例:
(1)查询表中的所有数据:
mysql> select * from test;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | lc | 20 |
| 2 | lc1 | 21 |
| 3 | lc3 | 22 |
| 4 | lc4 | 23 |
+----+------+------+
4 rows in set (0.00 sec)
(2)根据指定的字段查询数据:
mysql> select id,name,age from test where name='lc3' or age='21' order by id desc;
+----+------+------+
| id | name | age |
+----+------+------+
| 5 | lc3 | 22 |
| 3 | lc3 | 22 |
| 2 | lc1 | 21 |
+----+------+------+
3 rows in set (0.00 sec)
#or:代表"或",并集;and:代表"与",交集;desc:代表降序;asc:代表升序,默认的就是升序;
(3)select多表连表查询:
1)连表查询说明:
连表查询时表要至少有一个外键(外键是指:一张表的主键字段和另一张表主键的字段相同),或者
两个表中有相同的字段,并且是唯一值且不为空;
连表查询时一般运维不会用到,了解即可,这些一般是开发在用;
2)建表时建立外键时使用的参数语句是:
foreign key(id) references outTable(id) on delete cascade on update cascade
#表示把id列设为外键,参照外表outTable的id列,当外键的值删除,本表中对应的列筛除,当外键的值改变,本表中对应的列值改变。
3)连表查询语句:
mysql> select student.id,test.name from student,test where student.id = test.id and student.name = test.name order by name;
(4)查看执行计划,判断查询语句是否走索引:
1)没有建立索引的时候:
mysql> explain select * from test where name='lc'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE #查询类型为简单查询;
table: test #查询的表;
type: ALL
possible_keys: NULL #可能走的索引,没有;
key: NULL #没有索引;
key_len: NULL
#显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
#使用的索引的长度。在不损失精确性的情况下,长度越短越好。
ref: NULL
#显示使用哪个列或常数与key一起从表中选择行,主要判断索引的利用率,一个字段表示一个const;
rows: 5 #扫描的行数;
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
2、在name字段上建立索引后查询:
mysql> alter table test add index index_name (name);
mysql> explain select * from test where name='lc'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name
key: index_name
key_len: 22
ref: const
rows: 2
Extra: Using where
1 row in set (0.01 sec)
ERROR:
No query specified