#查看当前ubuntu版本
yourtreedad@yourtreedad:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.3 LTS
Release:        20.04
Codename:       focal
yourtreedad@yourtreedad:~$

#切换到root账户
yourtreedad@yourtreedad:~$ sudo passwd root
New password:
Retype new password:
passwd: password updated successfully
yourtreedad@yourtreedad:~$ su root
Password:
root@yourtreedad:/home/yourtreedad#

#查看电脑里有没有mysql
root@yourtreedad:/home/yourtreedad# service mysql status
 * MySQL is stopped.
root@yourtreedad:/home/yourtreedad# mysql --version
mysql  Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
root@yourtreedad:/home/yourtreedad# service mysql start
 * Starting MySQL database server mysqld                                                 su: warning: cannot change directory to /nonexistent: No such file or directory

#那就彻底卸载了
root@yourtreedad:/home/yourtreedad# sudo apt-get remove mysql-*
root@yourtreedad:/home/yourtreedad# sudo rm -rf /etc/mysql/
root@yourtreedad:/home/yourtreedad# dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
dpkg: error: --purge needs at least one package name argument
#如果出现这个错误 dpkg: error: --purge needs at least one package name argument，说明已经清空完毕了

#全部删除了
root@yourtreedad:/home/yourtreedad# service mysql status
mysql: unrecognized service
root@yourtreedad:/home/yourtreedad# mysql --version
bash: /usr/bin/mysql: No such file or directory
root@yourtreedad:/home/yourtreedad#

#更新到最新 安装rpm
root@yourtreedad:/opt# apt-get update
root@yourtreedad:/home/yourtreedad# apt-get upgrade
root@yourtreedad:/home/yourtreedad# apt-get install rpm

#使用rpm判断当前系统是否安装过mysql
root@yourtreedad:/home/yourtreedad# rpm -qa|grep -i mysql

#安装mysql
root@yourtreedad:/opt# sudo apt-get install mysql-server mysql-client

#查看是否安装成功
root@yourtreedad:/# id yourtreedad
uid=1000(yourtreedad) gid=1000(yourtreedad) groups=1000(yourtreedad),4(adm),20(dialout),24(cdrom),25(floppy),27(sudo),29(audio),30(dip),44(video),46(plugdev),117(netdev)
root@yourtreedad:/# cat /etc/passwd|grep mysql
mysql:x:112:119:MySQL Server,,,:/var/lib/mysql/:/bin/false
root@yourtreedad:/# cat /etc/group|grep mysql
mysql:x:119:
root@yourtreedad:/# mysqladmin --version
mysqladmin  Ver 8.0.26-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
root@yourtreedad:/#

root@yourtreedad:/# service mysql start

root@yourtreedad:/# ps -ef|grep mysql
mysql    18675     1  0 21:03 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql    18822 18675  0 21:03 ?        00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=yourtreedad.pid
root     19055 16743  0 21:14 tty1     00:00:00 grep --color=auto mysql

#查看运行时间
root@yourtreedad:/home/yourtreedad# top
top - 21:17:30 up  1:21,  0 users,  load average: 0.52, 0.58, 0.59
Tasks:   8 total,   1 running,   7 sleeping,   0 stopped,   0 zombie
%Cpu(s):  4.0 us,  2.9 sy,  0.0 ni, 92.7 id,  0.0 wa,  0.4 hi,  0.0 si,  0.0 st
MiB Mem :  32674.8 total,  21949.1 free,  10501.7 used,    224.0 buff/cache
MiB Swap:  38400.2 total,  38322.8 free,     77.3 used.  22042.5 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
    1 root      20   0    8944    328    288 S   0.0   0.0   0:00.18 init
16725 root      20   0    8952    232    184 S   0.0   0.0   0:00.00 init
16726 yourtre+  20   0   18096   3588   3484 S   0.0   0.0   0:00.13 bash
18675 mysql     20   0   10656    812    780 S   0.0   0.0   0:00.10 mysqld_safe
18822 mysql     20   0 2603072 299920  20628 S   0.0   0.9   0:00.75 mysqld
19087 root      20   0   18412   2592   2568 S   0.0   0.0   0:00.03 su
19088 root      20   0   17008   2396   2304 S   0.0   0.0   0:00.07 bash
19201 root      20   0   18924   2152   1528 R   0.0   0.0   0:00.04 top

#直接mysql 就进来了
root@yourtreedad:/home/yourtreedad# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24

root@yourtreedad:/home/yourtreedad# /usr/bin/mysqladmin -u root password 123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

mysql> create database student ;
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye
root@yourtreedad:/var/lib/mysql# ls -l
total 193956
-rw-r----- 1 mysql mysql   196608 Oct 12 21:33 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql  8585216 Oct 12 20:19 '#ib_16384_1.dblwr'
drwxr-x--- 1 mysql mysql      512 Oct 12 21:03 '#innodb_temp'
-rw-r----- 1 mysql mysql       56 Oct 12 20:19  auto.cnf
-rw-r----- 1 mysql mysql 12582912 Oct 12 21:33  ibdata1
-rw-r----- 1 mysql mysql 12582912 Oct 12 21:03  ibtmp1
drwxr-x--- 1 mysql mysql      512 Oct 12 20:19  mysql
-rw-r----- 1 mysql mysql 25165824 Oct 12 21:33  mysql.ibd
drwxr-x--- 1 mysql mysql      512 Oct 12 21:33  student
drwxr-x--- 1 mysql mysql      512 Oct 12 20:19  sys

mysql> insert into user values (2,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user ;
+----+--------+
| id | name   |
+----+--------+
|  1 | z3     |
|  2 | 张三   |
+----+--------+
2 rows in set (0.00 sec)

#有时候字符集没弄好 要弄成utf-8
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> show engines ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine              

#查看存储引擎
mysql> show variables like '%storage_engine%' ;
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

#创建索引
mysql> create index idx_beauty_name on beauty(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_beauty_name_sex on beauty(name,sex);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#查看索引
mysql> show index from beauty ;
+--------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| beauty |          0 | PRIMARY             |            1 | id          | A         |          13 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| beauty |          1 | idx_beauty_name     |            1 | name        | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| beauty |          1 | idx_beauty_name_sex |            1 | name        | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| beauty |          1 | idx_beauty_name_sex |            2 | sex         | A         |          12 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

mysql> CREATE TABLE tbl_dept (
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> deptName VARCHAR(30) DEFAULT NULL,
    -> locAdd VARCHAR(40) DEFAULT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql>   CREATE TABLE tbl_emp (
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) DEFAULT NULL,
    -> deptld INT(11) DEFAULT NULL,
    -> PRIMARY KEY(id),
    -> KEY fk_dept_id(deptld)
    -> #CONSTRAINT fk_dept_id FOREIGNKEY(deptld) REFERENCES tbl_dept(id)
    -> )ENGINE INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.02 sec)

mysql> select * from tbl_emp ;
+----+------+--------+
| id | name | deptld |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

mysql> select * from tbl_dept ;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)

#内连接 就么有8员工 和 5部门
mysql> select e.* , d.* from tbl_emp e join tbl_dept d on e.deptld = d.id ;
+----+------+--------+----+----------+--------+
| id | name | deptld | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+

#左外连接 没有5部门
mysql> select e.* , d.* from tbl_emp e left join tbl_dept d on e.deptid = d.id ;
+----+------+--------+------+----------+--------+
| id | name | deptid | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+

#右外连接 没有8员工
mysql> select e.* , d.* from tbl_emp e right join tbl_dept d on e.deptid = d.id ;
+------+------+--------+----+----------+--------+
| id   | name | deptid | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
 
#只有左边 只有8员工
mysql> select e.* , d.* from tbl_emp e left join tbl_dept d on e.deptid = d.id where d.id is null ;
+----+------+--------+------+----------+--------+
| id | name | deptid | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+

#只有右边 只有5部门
mysql> select e.* , d.* from tbl_emp e right join tbl_dept d on e.deptid = d.id where e.deptid is null ;
+------+------+--------+----+----------+--------+
| id   | name | deptid | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)

#全连接 都得有 mysql不支持outer join
mysql> select e.* , d.* from tbl_emp e outer join tbl_dept d on e.deptid = d.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join tbl_dept d on e.deptid = d.id' at line 1

#可以这么做  union默认去重  union all 就不会去重 
mysql> select e.* , d.* from tbl_emp e left join tbl_dept d on e.deptid = d.id union select e.* , d.* from tbl_emp e right join tbl_dept d on e.deptid = d.id;
+------+------+--------+------+----------+--------+
| id   | name | deptid | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)

#不要join重合的部分 要另外两半的
mysql> select e.* , d.* from tbl_emp e left join tbl_dept d on e.deptid = d.id where d.id is null union select e.* ,d.* from tbl_emp e right join tbl_dept d on e.deptid = d.id where e.deptid is null ;
+------+------+--------+------+----------+--------+
| id   | name | deptid | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

#主键外键唯一都是索引 我们这边讲的是那种普通索引
mysql> show index from tbl_emp ;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tbl_emp |          1 | fk_dept_id |            1 | deptid      | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

#使用explain关键字 进行性能分析
mysql>  explain select * from tbl_emp ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from tbl_emp a left join tbl_dept b on a.deptId = b.id union select * from tbl_emp a right join tbl_dept b on a.deptid = b.id ;
+----+--------------+------------+------------+--------+---------------+------------+---------+----------------+------+----------+-----------------+
| id | select_type  | table      | partitions | type   | possible_keys | key        | key_len | ref            | rows | filtered | Extra           |
+----+--------------+------------+------------+--------+---------------+------------+---------+----------------+------+----------+-----------------+
|  1 | PRIMARY      | a          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL           |    8 |   100.00 | NULL            |
|  1 | PRIMARY      | b          | NULL       | eq_ref | PRIMARY       | PRIMARY    | 4       | girls.a.deptid |    1 |   100.00 | NULL            |
|  2 | UNION        | b          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL           |    5 |   100.00 | NULL            |
|  2 | UNION        | a          | NULL       | ref    | fk_dept_id    | fk_dept_id | 5       | girls.b.id     |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL    | NULL          | NULL       | NULL    | NULL           | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+--------+---------------+------------+---------+----------------+------+----------+-----------------+
5 rows in set, 1 warning (0.00 sec)

#建表 单表示例
mysql> CREATE TABLE IF NOT EXISTS `article`(
    -> `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> `author_id` INT(10) UNSIGNED NOT NULL,
    -> `category_id` INT(10) UNSIGNED NOT NULL,
    -> `views` INT(10) UNSIGNED NOT NULL,
    -> `comments` INT(10) UNSIGNED NOT NULL,
    -> `title` VARBINARY(255) NOT NULL,
    -> `content` TEXT NOT NULL
    -> );
Query OK, 0 rows affected, 5 warnings (0.03 sec)

mysql>
mysql> INSERT INTO `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) VALUES
    -> (1,1,1,1,'1','1'),
    -> (2,2,2,2,'2','2'),
    -> (1,1,3,3,'3','3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from article;
+----+-----------+-------------+-------+----------+--------------+---------+
| id | author_id | category_id | views | comments | title        | content |
+----+-----------+-------------+-------+----------+--------------+---------+
|  1 |         1 |           1 |     1 |        1 | 0x31         | 1       |
|  2 |         2 |           2 |     2 |        2 | 0x32         | 2       |
|  3 |         1 |           1 |     3 |        3 | 0x33         | 3       |
+----+-----------+-------------+-------+----------+--------------+---------+
3 rows in set (0.00 sec)

#性能分析  type是all 说明是全表扫描
#显然 using filesort 也是最坏的情况 发生了文件内排序 
mysql> explain select id,author_id from article where category_id = 1 and comments >1 order by views desc limit 1 ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

#如果创建的索引是ccv  type 变成了range extra还是filesort 好了点
mysql> create index idx_ccv on article(category_id,comments,views) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,author_id from article where category_id = 1 and comments >1 order by views desc limit 1 ;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_ccv       | idx_ccv | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

#删除索引
mysql> drop index idx_ccv on article ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_cv on article(category_id,views) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#把范围的去掉
mysql> explain select id,author_id from article where category_id = 1 and comments >1 order by views desc limit 1 ;
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                            |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_cv        | idx_cv | 4       | const |    2 |    33.33 | Using where; Backward index scan |
+----+-------------+---------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

#两表 
mysql> CREATE TABLE IF NOT EXISTS `class`(
    -> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `card` INT(10) UNSIGNED NOT NULL,
    -> PRIMARY KEY(`id`)
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> CREATE TABLE IF NOT EXISTS `book`(
    -> `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `card` INT(10) UNSIGNED NOT NULL,
    -> PRIMARY KEY(`bookid`)
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

#分析 都是全表扫描 很几把恐怖
mysql> explain select * from book b right join class c on b.card = c.card ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

#优化 先给右连接的左表定索引 同理 或者给左连接的右表定索引
mysql> alter table book add index Y(card) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#type变成ref了 有点意思 using index 很好 
mysql> explain select * from book b right join class c on b.card = c.card ;
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL         |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | ref  | Y             | Y    | 4       | girls.c.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

#不知道给左边添加索引好还是给右边添加好 所以再试试
mysql> drop index Y on book ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#添加索引
mysql> alter table class add index Y(card) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

#发现没有用 为啥呢 因为右连接 没有where的话右边是肯定是全都有的 加了索引有啥用啊
mysql> explain select * from book b right join class c on b.card = c.card ;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | index | NULL          | Y    | 4       | NULL |   20 |   100.00 | Using index                                |
|  1 | SIMPLE      | b     | NULL       | ALL   | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

#删除索引
mysql> drop index Y on class ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0