#添加前缀索引为4(前缀索引并不是越小越好)
太小的索引值会是数据查找的精确度降低
无论字段有没有其他索引,都可以添加前缀索引
#添加主键和前缀索引
mysql> alter table test3 add primary key p_id(name(4));
#添加唯一键和前缀索引
mysql> alter table test3 add unique key prrri_id(name(4));
#添加普通索引和前缀索引
mysql> alter table test3 add index suo_name(name(4));
联合索引(复合索引)(字段)
#命名规则:表名_字段名
1、需要加索引的字段,在'where条件中使用'
2、'数据量少'的字段不需要加索引
3、如果where条件中是'OR关系',加索引不起作用
4、符合最左原则('有特例',解决办法是将索引字段的顺序调换一下)
create table xq(id int primary key,name varchar(4),gender enum('m','f'),age int);
insert xq values(1,'xx','m','18');
insert xq values(2,'xx','m','18');
insert xq values(3,'xx','m','18');
insert xq values(4,'xx','m','18');
show index from xq;
+-------+------------+----------+--------------+
| Table | Non_unique | Key_name | Seq_in_index
+-------+------------+----------+--------------+
| xq | 0 | PRIMARY | 1
#创建联合索引(联合索引有联合顺序)(最常用的字段创建联合索引)
alter table xq add index ls_key(id,name,gender);
show index from xq;
| xq | 0 | PRIMARY | 1 id
| xq | 1 | ls_key | 1 id
| xq | 1 | ls_key | 2 name
| xq | 1 | ls_key | 3 gender
#联合索引3种情况
1.全部走索引(1开头)
2.部分走索引(1开头)
3.不走索引 (不以1开头)
explain
#作用
explain显示了MySQL如何使用'索引'来处理select语句以及'连接表'。可以帮助选择更好的索引和写出更优化的查询语句。
#语法
explain + select语句
mysql> explain select * from test2;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test2 | index | NULL | uni_key | 5 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
mysql> explain select * from test2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test2 #表type: index #最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all
possible_keys: NULL #表中的索引
key: uni_key #实际使用的索引,强制(use index index_name),忽略(ignore index index_name)
key_len: 5 #使用的索引的长度(越小越好)
ref: NULL #显示索引的哪一列被使用了,如果可能的话,是一个常数
rows: 1 #必须检查的用来返回请求数据的行数
Extra: Using index #查询的次数,当查询的数据量较大的时候,并不准确
extra:
using temporary 使用了group by出现这个
using filesort 使用了order by出现这个
using join buffer 使用了join on出现这个
#查询中国和美国的数据
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';
速度: union all > in > or
#使用explain查看
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
| 'id' | select_type | table | 'type' | possible_keys | key | key_len | 'ref' | 'rows' | Extra |
+----+--------------+------------+------+---------------+-------------+-----
| 1 | PRIMARY | city | 'ref' | CountryCode | CountryCode | 3 | const | 363 | Using index condition |
| 2 | UNION | city | ref | CountryCode | CountryCode | 3 | const | 274 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary
#id与执行顺序有关#select_type 索引类型#type 是explain的类型#key_len 是前缀索引的长度#rows 查询的次数,当查询的数据量较大的时候,并不准确(合并重复项)
扩展group by
create database fang;
use fang;
#建表
mysql> create table jixiao(id int,name varchar(20) charset utf8,jixiao int,product varchar(10) charset utf8);
show create table fz;
show tables;
desc fz;
#插入数据
mysql> insert jixiao values(1,'qiudao','1000','房地产'),(2,'niulei','10','房地产'),(3,'lijianpeng','100','汽车'),(4,'qiandao','200','汽车');
select count(*) from fz;
#查询不同行业绩效最高的人
mysql> select name,sum(jixiao),product from jixiao group by product;
+------------+-------------+-----------+
| name | sum(jixiao) | product |
+------------+-------------+-----------+
| qiudao | 1010000 | 房地产 |
| lijianpeng | 300000 | 汽车 |
+------------+-------------+-----------+
#查询房地产行业绩效最高的人
mysql> select name,sum(jixiao),product from jixiao group by product having product='房地产';
+--------+-------------+-----------+
| name | sum(jixiao) | product |
+--------+-------------+-----------+
| qiudao | 1010000 | 房地产 |
+--------+-------------+-----------+
select name,sum(jixiao),product from jixioa group by product;
select name,sum(jixiao),product from jixioa group by product having 字段='值';
#1.对group by后面的字段去重#1.sum() 根据group by的值 相加#having条件语句
select 字段1,sum(字段2),字段3... from 表1 ... group by 字段x having 字段='值';
1.全表扫描(type类型是all),不走索引,查询速度最慢
#查看结果来判断查询级别
mysql> explain select * from country;
+----+-------------+---------+------+---------------+------+---------+------
| id | select_type | table | type |
+----+-------------+---------+------+---------------+------+---------+------
| 1 | SIMPLE | country | ALL |
#查看字段是否有索引
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
查看结果来判断查询级别
mysql> explain select District from city;
+----+-------------+-------+------+
| id | select_type | table | type |
+----+-------------+-------+------+
| 1 | SIMPLE | city | ALL |
#全表扫描的情况
一:查询数据库所有数据的时候(select *)
二:有索引,但是没有走索引
没有设置索引 (desc xx;)
索引损坏
explain select name from country;
desc
---------------------------------------------------------------
2.索引扫描'级别'
一:index 慢,全索引扫描(!=)
explain select * from city;
explain select * from city where countrycode !='CHN'
二:range 范围查询(or ><)(范围要在总数的 20%以内左右),limit,older by
explain select * from city where countrycode='CHN'or countrycode='USA';
explain select * from city where countrycode='CHN'or countrycode='USA' or countrycode='BRA'limit 100;
三:ref 精确查询(=)
explain select * from city where countrycode='CHN'
四:eq_ref 使用join on
五:const 查询条件是唯一索引或者主键索引,#列
explain select * from city whereid=1;
六:system 快,查询级别与const一样,当数据很少的时候为该级别,#列
七:null 使用索引查询表的数量的时候,#列
desc city;
alter table city add index aa(population);
explain select max(population) from city;
explain select min(population) from city;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步