#创建主键索引1.建表时直接添加主键索引
mysql> create table student(id int not null primary key comment '学号');
mysql> create table student(id int not null comment '学号', primary key(id));2.已经建好的表,添加主键索引
mysql> alter table student2 add primary key pri_id(id);
mysql> alter table student2 add primary key suibianxieshenme(id);#查看索引
mysql> show index from student2;
2)唯一键索引
#创建唯一键索引1.建表时直接添加唯一键索引
mysql> create table student(id int not null unique key comment '学号');2.添加唯一键索引
mysql> alter table student add unique key uni_id(id);
mysql> create unique key uni_id on student(id);
扩展
#数据库添加唯一键索引时,该列不能有相同数据#数据条数查看 类似于 wc -l
mysql>select count(name) from city;#数据去重 类似于 uniq -c
mysql>select distinct(name) from city;#当以上两个值相同时才可以创建主键索引或者唯一键索引#国家表的name列可以创建唯一键索引
mysql> alter table country add unique key uni_name(name);
3)普通索引(辅助索引)
#添加普通索引
mysql> alter table city add index idx_name(name);
mysql> create index idx_dis on city(District);
4)全文索引
#创建全文索引
mysql> create table pull(id int, bookname varchar(10), content text, fulltext(content));#查询数据时
mysql>select * from pull where match(content) against('查找的字符串');
5)查看索引
1.方式一:
mysql> show index from city;2.方式二:
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11)| NO | PRI | NULL | auto_increment || Name | char(35)| NO | MUL |||| CountryCode | char(3)| NO | UNI |||| District | char(20)| NO | MUL |||| Population | int(11)| NO | MUL |0||
+-------------+----------+------+-----+---------+----------------+
5 rows inset(0.00 sec)
PRI:主键索引
UNI:唯一键索引
MUL:普通索引
#创建前缀索引
mysql> alter table student add index idx_name(sname(4));
2)联合索引
#1.创建一个库
mysql> create database xiangqin;#2.创建一个表
mysql> use xiangqin;
mysql> create table people(id int, name varchar(20), age tinyint, money int, gender enum('m','f'), hight int, weight int, figure varchar(10), looks varchar(10));#3.创建联合索引
mysql> alter table people add index idx_all(gender,looks,age,money);#4.查看索引
mysql> show index from people;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people |1| idx_all |1| gender | A |10| NULL | NULL | YES | BTREE |||| people |1| idx_all |2| looks | A |10| NULL | NULL | YES | BTREE |||| people |1| idx_all |3| age | A |10| NULL | NULL | YES | BTREE |||| people |1| idx_all |4| money | A |10| NULL | NULL | YES | BTREE |||
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows inset(0.00 sec)#5.联合索引当没有按照建立索引顺序查询时,不走索引,或者部分走索引
二、explain 使用
1.explain用法
#1.查询中国和美国的城市
mysql>select * from city where countrycode='CHN' or countrycode='USA';
mysql>select * from city where countrycode in('CHN','USA');
mysql>select * from city where countrycode='CHN' union all select * from city where countrycode='USA';#2.explain 用法
mysql> explain select * from city where countrycode='CHN' or countrycode='USA';
mysql> explain select * from city where countrycode in('CHN','USA');
2.explain 注解
id 执行顺序
table 查询的表
type 查询使用的类型
possible_keys 可能使用到的索引
key 真的使用到的索引
key_len 索引长度,可以使用前缀索引控制
ref 查询速度的注释
rows 查询数据的数量,有的时候会比真实数据偏高
Extra
Using temporary 使用grouop by的时候出现
Using filesort 使用order by 的时候出现
Using join buffer 使用join on的时候缓存
3.查询数据的种类
1)全表扫描
#1.什么是全表扫描
在explain语句结果中,type为ALL
#2.什么时候出现全表扫描1)查询所有数据的时候
mysql> explain select * from city;2)不走索引的时候
2.1 当查询条件没有索引的时候
mysql> explain select * from city where District='shanghai';2.2 索引创建有问题
2.3 语句有问题
2)索引扫描
#常见的索引扫描类型,前提条件,查询的条件必须有索引1)index 全索引扫描
mysql> explain select population from city;2)range 范围查询 (sql与只要达到range级别就ok了)
mysql> explain select * from city where countrycode='CHN' or countrycode='USA';
mysql> explain select * from city where countrycode in('CHN','USA');
mysql> explain select * from city where population >30000000;3)ref 精确查找
mysql> explain select * from city where population =30000000;4)eq_ref 类似于ref,使用join on的时候
mysql> explain select * from city join country on city.countrycode=country.code where city.population <100;5)const 查询的条件的列是唯一索引或主键索引
mysql> explain select * from country where name='Aruba'; 唯一键
mysql> explain select * from country where code='CHN'; 主键
6)system 跟const同级别
7)null 执行过程中不访问表或者索引
mysql> explain select min(population) from city;
#没有加查询条件
mysql> explain select * from city;#条件是全表
mysql> explain select * from city where 1=1;#条件没有索引
mysql> explain select * from city where District='shanghai';
2)查询的结果集是原表中的大部分数据,25%以上
#查询的数据占所有数据的98%,不走索引
mysql> explain select * from city where population <'3000000';#查询的数据占所有数据的12.5%,走索引
mysql>select * from city where population <'100000';#如果一定要查询大部分数据,为了让他走索引,使用limit
mysql> explain select * from city where population <'1000000000' limit 60;
mysql> explain select * from city where population <'1000000000' limit 60,60;
3)索引本身失效
反复的修改,添加索引,索引被玩坏了,每一次添加索引,都会对数据进行排序
4)查询条件使用函数在索引列上或者对索引列进行运算,运算包括(+,-,*等)
#在符号(=)前面进行运算时,不走索引
mysql> explain select * from city where id-1=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|1| SIMPLE | city | ALL | NULL | NULL | NULL | NULL |4188| Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row inset(0.00 sec)#在符号(=)后面进行运算时,走索引
mysql> explain select * from city where id=1+1;
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
|1| SIMPLE | city | const | PRIMARY,index_id | PRIMARY |4| const |1| NULL |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
1 row inset(0.00 sec)
5)隐式转换,会导致索引失效
#1.创建一个表
mysql> create table test(id int,name varchar(10),phonenum varchar(20));#2.创建索引
mysql> alter table test unique key idx_num(phonenum);#3.插入数据
mysql> insert into test values(1,'jc','110'),(2,'hj','119'),(3,'jm','120');
mysql> insert into test values(4,'cdh',114),(5,'USAjc',999),(6,'hc',12306);#4.测试查询数据走不走索引#不走索引:
mysql> explain select * from test where phonenum=110;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|1| SIMPLE |test| ALL | idx_num | NULL | NULL | NULL |3| Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row inset(0.00 sec)#走索引
mysql> explain select * from test where phonenum='110';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|1| SIMPLE |test| const | idx_num | idx_num |23| const |1| NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row inset(0.00 sec)#原因:因为数据库中看到的数字,是varchar数据类型,如果是字符类型,查询时必须加引号;
6)<> 和 not in 和 or 也不走索引
mysql> explain select * from test where phonenum <>'110';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|1| SIMPLE |test| ALL | idx_num | NULL | NULL | NULL |6| Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row inset(0.00 sec)
mysql> explain select * from test where phonenum not in('110','120','114','999','12306');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|1| SIMPLE |test| ALL | idx_num | NULL | NULL | NULL |6| Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row inset(0.00 sec)
mysql> explain select * from test where phonenum='110' or phonenum='120';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|1| SIMPLE |test| ALL | idx_num | NULL | NULL | NULL |6| Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row inset(0.00 sec)#使用union all代替or,可以走索引
mysql> explain select * from test where phonenum='110' union all select * from test where phonenum='120';
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|id| select_type | table |type| possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|1| PRIMARY |test| const | idx_num | idx_num |23| const |1| NULL ||2| UNION |test| const | idx_num | idx_num |23| const |1| NULL || NULL | UNION RESULT |<union1,2>| ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows inset(0.00 sec)
7)like模糊查询 % 位置决定走不走索引
#%在最前面一定不走索引
mysql> explain select * from city where countrycode like '%HN';#%在中间或者在最后面,可能走索引,也可能不走索引,取决于查询的数量多少
8)联合索引查询的条件不是按照顺序排列的,或者条件没有联合索引的第一个列
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m';#不走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';