Mysql-多表查询as索引
2019-08-23 本文已影响0人
你好_请关照
1、Mysql多表查询
2、information_schema 虚拟库
3、索引
1、多表查询
方法
(1) 根据需求找到关联表
(2)找到关联条件
1.1有关联条件的多表查询
mysql> select city.name,country.name,city.population,country.surfacearea from
-> city join country
-> on city.countrycode=country.code
-> where city.population<100;
+-----------+----------+------------+-------------+
| name | name | population | surfacearea |
+-----------+----------+------------+-------------+
| Adamstown | Pitcairn | 42 | 49.00 |
+-----------+----------+------------+-------------+
1 row in set (0.37 sec)
mysql>
1.2两张表没有关联条件，通过第三张表找到关联条件
A B C
select a.**,b.**,c.** from
a join c
on a.xx=c.yy
join B
on c.xx=b.yy
where xxx
2、information_schema 虚拟库
重点的表tables
tables表下常用列
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引的长度
*需求1：统计world库下有几个表
mysql> select table_schema,table_name
from tables
where table_schema='world' ;
+--------------+-----------------+
| table_schema | table_name |
+--------------+-----------------+
| world | city |
| world | country |
| world | countrylanguage |
+--------------+-----------------+
3 rows in set (0.00 sec)
mysql>
需求2:统计所有库下表的个数
mysql> select table_schema,count(table_name)
from tables
group by table_schema;
+--------------------+-------------------+
| table_schema | count(table_name) |
+--------------------+-------------------+
| information_schema | 61 |
| mysql | 31 |
| performance_schema | 87 |
| sys | 101 |
| test | 1 |
| world | 3 |
+--------------------+-------------------+
需求3:统计每个库的总数据大小
--单表占空间：AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
mysql> select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
-> from tables
-> group by table_schema;
+--------------------+--------------------------------------------------+
| table_schema | sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 |
+--------------------+--------------------------------------------------+
| information_schema | NULL |
| mysql | 2306.7715 |
| performance_schema | 0.0000 |
| sys | 15.9961 |
| test | 48697.9980 |
| word | 15.9990 |
| world | 779.7744 |
+--------------------+--------------------------------------------------+
7 rows in set (0.41 sec)
mysql>
3、MySQL索引
3.1索引简介:索引就好像一本书的目录一样，帮助人们更便捷的去查看书中的内容,可以起到优化查询的作用。
3.2 索引种类
什么使索引：索引其实就是一种算法
BTree
HASH
Rtree
Fulltext
3.3 Btree索引简介
B+tree.png
3.4 B树索引功能性上的细分
辅助索引
辅助索引只提取索引列作为叶子节点
聚集索引
聚集索引提取整行数据作为叶子节点
1、辅助索引和聚集索引最大的区别就在于叶子节点，枝节点和根节点原理相同
2、辅助索引会记录主键值，一般情况（除等值查询），最终都会通过聚集索引(主键)来找到需要的数据
3.5 影响索引数高度的原因
1.数据量大--解决办法--分库分表(分布式架构)、分区表
2.索引列值太长--解决办法--前缀索引
3.主键过长--解决办法--尽量数字列作为主键
4.数据类型--解决办法--选用合适的数据类型
4、MySQL索引管理
4.1 MySQL 索引查询
Key里常见的几种索引：
PRI --主键索引
MUL --辅助索引
UNI --唯一索引
第一种
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 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
第二种
mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | ID | A | 4188 | NULL | NULL | | BTREE | | |
| city | 1 | CountryCode | 1 | CountryCode | A | 232 | NULL | NULL | | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql>
mysql>
4.2创建索引
一般经常用来查询的列作为索引
索引可以有多个，但是索引名不可重名
第一种:单列索引
mysql> mysql> alter table city add idx_name(name);
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
第二种：前缀索引
前缀索引只能应用到字符串列，数字列不能用前缀索引
mysql> alter table city add index idx_district(district(5));
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | MUL | | |
| District | char(20) | NO | MUL | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
第三种:联合索引**
联合索引说明：如果在一个表内对A、B、C三个列创建联合索引那么创建索引将按照如下情况创建索引表：
A
AB
ABC
mysql> alter table city add index idx_c_p(CountryCode,Population);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | MUL | | |
| District | char(20) | NO | MUL | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
======================================================
5、获取执行计划
5.1获取执行计划desc、explain选择其一即可
mysql> desc select countrycode from city where id <100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 99 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
5.2执行计划分析
执行计划主要列信息
table : 表名
type : 查询类型
possible_keys : 可能会用的索引
key : 最终使用的索引
key_len : 查询时,索引的覆盖长度(联合索引)
extra : 额外的信息
执行计划中type的几种情况
(1)查询不到
(2)全表扫描--ALL
(3)索引扫描，索引扫描最优到最差的几种类型
null
const(system)
eq_ref
ref
range
index
all
5.2.1 ALL--全表扫描可能出现的情况
(1)查询列无索引
(2)语句不符合走走索引条件
mysql> desc select District from city where countrycode like '%HN' ;
(3)需要查看全表
mysql>select * from city;
5.2.2 index--全索引遍历
即把有索引的列全便利一遍
mysql> desc select countrycode from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | index | NULL | CountryCode | 3 | NULL | 4188 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
5.2.3 range--索引范围扫描
>、 <、 >=、 <=、 like、 between and 在范围扫描中，这些会受到B+tree索引叶子节点上额外的优化，因为这些是连续取值的
or、in 这两个不是连续的取值，所以不能受到B+tree索引的额外优化，使用时相当于Btree索引
!=、 not in 只有在主键列才走索引也是range级别
(1)>、 <、 >=、 <=、 like、 between and
mysql> desc select * from world.city where id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
(2)or、in
mysql> desc select * from city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 3 | NULL | 637 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#改写为如下，性能会更好一些
mysql> desc select * from city where countrycode='CHN'
-> union all
-> select * from city where countrycode='USA';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | PRIMARY | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
| 2 | UNION | city | NULL | ref | CountryCode | CountryCode | 3 | const | 274 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql>
(3)!=、 not in
mysql> desc select *from world.city where id !=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2103 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
5.2.4 ref--辅助索引等值查询
mysql> desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 3 | const | 363 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
5.2.5 eq_ref
多表连接查询，非驱动表连接条件是主键或唯一键
一般多表查询的时，最左侧的表为驱动表，右侧的为非驱动表，下边的例子中country标为非驱动表
mysql> desc SELECT city.name,country.name,city.population,country.SurfaceArea
-> FROM city JOIN country
-> ON city.CountryCode=country.Code
-> WHERE city.Population<100;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | CountryCode | NULL | NULL | NULL | 4188 | 33.33 | Using where |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.city.CountryCode | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
5.2.6 const(system)--主键或者唯一键的等值查询(最好的性能)
mysql> desc select *from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
7 key_len 联合索引覆盖长度
7.1 如何计算key_len
7.1.1 数字类型
| not null | 非not null | |
|---|---|---|
| n1 tinyint(1字节) | 1 | 1+1 |
| n2 int(4字节) | 4 | 4+1 |
create table keylen(n1 int ,n2 int not null )charset utf8mb4;
mysql> alter table keylen add index idx_n2(n2);
mysql> desc keylen;
mysql> desc select * from keylen where n1=10;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | keylen | NULL | ref | idx_n1 | idx_n1 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from keylen where n2=10;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | keylen | NULL | ref | idx_n2 | idx_n2 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
7.1.2 字符串类型：
字符集
中文
gbk 2字节
utf8 3字节
utf8mb4 4字节
utf8mb4 为例:
| not null | 非not null | |
|---|---|---|
| char(5) | 4*5 | 4*5+1 |
| varchar(5) | 4*5+2 | 4*5+2+1 |
8、如何判断联合索引覆盖范围
联合索引使用注意事项：
1、建立联合索引时，优先按照语句的执行顺序建立索引;
2、建立联合索引时将唯一值多的列放在索引的最左侧；
3、如果在查询条件中是,所有索引是列等值的查询,无关这几个列的排列顺序
4、使用联合索引过滤多个条件时，当查询条件中存在非等值查询时，key_len会被非等值索引截断（解决办法，在建立索引时将经常使用非等值索引的列放到最后边）
5、在相同列如果有多个联合索引时，在查询时影响索引的使用
举例(1)联合索引等值查询
mysql> alter table keylen add index idx_c1_c2_c3_c4(c1,c2,c3,c4);
mysql> desc select * from keylen where c1='a';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | keylen | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 20 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from keylen where c1='a' and c2='a';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | keylen | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 41 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from keylen where c1='a' and c2='a' and c3='a';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | keylen | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 63 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from keylen where c1='a' and c2='a' and c3='a' and c4='a';
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | keylen | NULL | ref | idx_c1_c2_c3_c4 | idx_c1_c2_c3_c4 | 86 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
举例(2) 联合索引中有不等值查询
mysql> desc select * from keylen where c1='a' and c2 > 'a' and c3='a' and c4='a';
针对此语句进行索引优化：
mysql> alter table keylen add index idx_c1_c3_c4_c2(c1,c3,c4,c2);
mysql> alter table keylen drop index idx_c1_c2_c3_c4 ;
9、判断Extra列内容
如果Extra列出现Using temporary、Using filesort,两项内容，那么考虑以下语句的问题。
group by
order by
distinct
join on
union
10 建立索引和不走索引的情况
10.1建立索引的原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期,pt-osc(扩展)
(7) 联合索引,唯一值多的放在最左侧,多子句按照子句顺序进行创建联合索引
10.2不走索引的情况
(1) 没有查询条件，或者查询条件没有建立索引
mysql> desc select * from city;
mysql> desc select * from city where population<100;
(2) 查询结果集是原表中的大部分数据，应该是25％以上（只针对辅助索引）。
(3) 索引本身失效，统计数据不真实
重建
mysql> optimize table city;
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
desc select * from city where id-1=10;
(5) 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
mysql> desc select * from aa where telnum='110';
mysql> desc select * from aa where telnum=110;
(6) <> ，not in ,like '%aa' 不走索引（辅助索引）
