Mysql-多表查询as索引

作者: 你好_请关照 | 来源:发表于2019-08-23 14:46 被阅读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' 不走索引（辅助索引）