索引概述
1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2.让获取的数据更有目的性，从而提高数据库检索数据的性能。
1.BTREE: BTREE B+TREE B*TREE
2.HASH：HASH索引（只有memery存储引擎支持）
3.FULLTEXT：全文索引
4.RTREE：R树索引
1.主键索引（聚集索引）PRI
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#创建主键索引 1.建表时创建主键索引 1）方法1： mysql> create table student(id int unsigned primary key auto_increment comment '学生id'); 2）方法2： mysql> create table student1(id int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', PRIMARY KEY (id)); 2.已经有的表，添加主键索引 #建表 mysql> create table student2(id int unsigned NOT NULL COMMENT '学生id',name varchar(10) NOT NULL COMMENT '学生姓名'); #添加主键索引 mysql> alter table student2 add primary key pri_key(id); |
2.唯一建索引 UNI
创建表时添加唯一键索引
|
1 2 3 4 5 |
1.方法1： mysql> create table student3(id int unsigned NOT NULL unique key COMMENT '学生id'); 2.方法2： mysql> create table student4(id int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', unique key (id)); |
已经有的表，添加唯一键索引
|
1 2 3 4 |
#建表 mysql> create table student5(id int unsigned NOT NULL COMMENT '学生id',name varchar(10) NOT NULL COMMENT '学生姓名'); #添加主键索引 mysql> alter table student2 add unique key uni_key(id); |
如何确定哪一列可以创建唯一键
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#1.查看表中某一字段的总数据 mysql> select count(name) from country; +-------------+ | count(name) | +-------------+ | 239 | +-------------+ 1 row in set (0.00 sec) #2.去重查看表中某一列数据 mysql> select distinct(name) from country; mysql> select distinct(count(name)) from country; +---------------+ | (count(name)) | +---------------+ | 239 | +---------------+ 1 row in set (0.00 sec) #3.查看以上两个值是否完全相同 #4.如果相等则可以创建唯一建索引 mysql> alter table country add unique key uni_key(name); |
函数
|
1 2 3 4 5 6 7 8 9 10 |
now() #当前时间的函数 database() #查看当前库的函数 count() #查看数据总数的函数，count(主键) distinct() #去重数据的函数 PASSWORD() #数据库密码加密函数 max() #取某一列最大值 min() #取某一列最小值 avg() #取某一列平均值 sum() #取某一列总数 |
3.普通索引（辅助索引）MUL
创建索引
|
1 2 3 4 5 |
1.方法1： mysql> alter table city add index index_key(name); 2.方法2： mysql> create index idx_key on city(District); |
4.全文索引 MUL
建表时创建全文索引
|
1 |
mysql> create table xiaoshuo(id int,bookname varchar(20),author varchar(10),content text,FULLTEXT(content)); |
插入数据
|
1 2 3 4 5 6 7 8 9 10 |
mysql> insert into xiaoshuo values('1','西游演义','lhd','上回书说到张飞长坂坡三打白骨精救宋江'); Query OK, 1 row affected (0.00 sec) mysql> select * from xiaoshuo; +------+--------------+--------+--------------------------------------------------------+ | id | bookname | author | content | +------+--------------+--------+--------------------------------------------------------+ | 1 | 西游演义 | lhd | 上回书说到张飞长坂坡三打白骨精救宋江 | +------+--------------+--------+--------------------------------------------------------+ 1 row in set (0.00 sec) |
使用索引查询数据
|
1 2 3 4 5 6 7 |
mysql> select * from xiaoshuo where match(content) against('上回书说到张飞长坂坡三打白骨精救宋江'); +------+--------------+--------+--------------------------------------------------------+ | id | bookname | author | content | +------+--------------+--------+--------------------------------------------------------+ | 1 | 西游演义 | lhd | 上回书说到张飞长坂坡三打白骨精救宋江 | +------+--------------+--------+--------------------------------------------------------+ 1 row in set (0.00 sec) |
5.查看索引
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
1.方式1： mysql> desc city; +-------------+----------+------+-----+ | Field | Type | Null | Key | +-------------+----------+------+-----+ | ID | int(11) | NO | PRI | #主键索引 | Name | char(35) | NO | MUL | #普通索引 | CountryCode | char(3) | NO | UNI | #唯一键索引 | District | char(20) | NO | MUL | | Population | int(11) | NO | | +-------------+----------+------+-----+ 2.方式2： mysql> show index from city; |
6.删除索引
|
1 |
mysql> alter table city drop index idx_key; |
根据设置方法分类
1.创建索引时会对数据进行重新排序
2.建立索引会占用磁盘空间，所以索引不是越多越好
3.在同一列避免创建多个索引
4.避免在大数据的列创建索引，如果非要创建就创建前缀索引
前缀索引
|
1 2 3 4 5 |
#创建根据District列前三个字符进行排序的索引 mysql> alter table city add index District_key(District(3)); #查看表的索引 mysql> show index from city; |
联合索引
案例
|
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 |
#1.创建一个库 mysql> create database xiangqin; #2.创建一个表 mysql> use xiangqin; mysql> create table user(id int,name varchar(10),sex enum('male','fmale'),age tinyint,weight int,height int,money int,look varchar(10)); #3.插入数据 mysql> insert into user values('1','qiudao','male','38','180','120','-100000','very ugly'),('2','dawei','male','18','130','182','10000','Handsome'),('3','bingbing','fmale','30','110','170','100000000','beautiful'); #4.查看数据 mysql> select * from user; +------+----------+-------+------+--------+--------+-----------+-----------+ | id | name | sex | age | weight | height | money | look | +------+----------+-------+------+--------+--------+-----------+-----------+ | 1 | qiudao | male | 38 | 180 | 120 | -100000 | very ugly | | 2 | dawei | male | 18 | 130 | 182 | 10000 | Handsome | | 3 | bingbing | fmale | 30 | 110 | 170 | 100000000 | beautiful | +------+----------+-------+------+--------+--------+-----------+-----------+ 3 rows in set (0.00 sec) #5.建立联合索引 mysql> alter table user add index index_all(sex,age,money,look); #6.查看索引 mysql> show index from user; |
注意
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
where sex='' and age='' and money='' and look=''; index(sex,age,money,look) 特点：前缀生效特性 #可以走索引 sex sex,age sex,age,money sex,age,money,look #部分走索引： 只要包含联合索引的第一个条件的查询语句都部分走索引 #不走索引的情况 age,money,look age,look money,look money look age,sex ... #原则：把最常用来做为条件查询的列放在最前面 |