大数据技术之_29_MySQL 高級面试重点串讲_02

rpm -qa | grep mysql

使用 yum 命令卸载
yum remove mysql mysql-server mysql-libs mysql-common

使用 rpm 命令卸载
rpm -e RPM软件包名（该名字是第一步命令查出来的名字）
rm -rf /var/lib/mysql
rm -f /etc/my.cnf

1）下载 mysql 的源包：
    我们是 centos6.8 对应的 rpm 包为：mysql-community-release-el6-5.noarch.rpm
    命令：wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
2）安装 mysql 下载源：
    yum localinstall mysql-community-release-el6-5.noarch.rpm
3）在线安装社区版的 mysql：
    yum install mysql-community-server
    或者在线安装收费版本的 mysql：
    yum install mysql-server

    两种方式均可，建议安装社区版。

service mysql start

/usr/bin/mysqladmin -u root password 'new-password'　　#为 root 账号设置密码

[root@localhost ~]# mysql -uroot -p

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

Mysql 5.5 版本：cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

Mysql 5.6 版本：cp /usr/share/mysql/my-default.cnf /etc/my.cnf

mysql> show variables like 'character%';  或者
mysql> show variables like '%char%';

[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

default-character-set=utf8

# The MySQL server
[mysqld]
port            = 3306

character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci

socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

lower_case_table_names=1
max_connections=1000

[mysql]
no-auto-rehash

default-character-set=utf8

service mysql stop
service mysql start

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
);

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');

SELECT * FROM article;

# 查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id。 
# 1.0 第 1 次EXPLAIN
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+

#结论：很显然，type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort，也是最坏的情况。优化是必须的。


# 开始优化：
# 1.1 新建索引+删除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv (`category_id`, `comments`, `views`);
CREATE INDEX idx_article_ccv ON article (category_id, comments, views);
DROP INDEX idx_article_ccv ON article;

# 1.2 第 2 次EXPLAIN
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 3 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| id | select_type | table   | type | possible_keys   | key             | key_len | ref         | rows | Extra       |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+

# 结论：
# type 变成了 range，这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
# 但是我们已经建立了索引，为啥没用呢?
# 这是因为按照 BTree 索引的工作原理：（分组之前必排序）
# 先排序 category_id，
# 如果遇到相同的 category_id 则再排序 comments，如果遇到相同的 comments 则再排序 views。
# 当 comments 字段在联合索引里处于中间位置时，
# 因 comments > 1 条件是一个范围值(所谓 range)，
# MySQL 无法利用索引再对后面的 views 部分进行检索，即 range 类型查询字段后面的索引无效。

# 1.3 删除第 1 次建立的索引
DROP INDEX idx_article_ccv ON article;

# 1.4 第 2 次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv (`category_id`, `views`);
CREATE INDEX idx_article_cv ON article (category_id, views);

# 1.5 第 3 次 EXPLAIN
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+

# 结论：可以看到，type 变为了 ref，Extra 中的 Using filesort 也消失了，结果非常理想。
# 删除第 2 次建立的索引
DROP INDEX idx_article_cv ON article;

CREATE TABLE IF NOT EXISTS `class` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
  `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`bookid`)
);

INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO class (card) VALUES (FLOOR(1 + (RAND() * 20)));

INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book (card) VALUES (FLOOR(1 + (RAND() * 20)));

select * from class;
select * from book;

# 下面开始第 2 次 explain 分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

# 结论：type 有 All

# 添加索引优化
ALTER TABLE `book` ADD INDEX Y (`card`);

# 第 2 次 explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL              |   20 |             |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db2019.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+

# 可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。
# 这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左表一定都有，
# 所以右表是我们的关键点，一定需要建立索引。

# 删除旧索引 + 新建新索引 + 第 3 次 explain
DROP INDEX Y ON book;
ALTER TABLE `class` ADD INDEX X (`card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | class | index | NULL          | X    | 4       | NULL |   20 | Using index |
|  1 | SIMPLE      | book  | ALL   | NULL          | NULL | NULL    | NULL |   20 |             |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+


# 然后来看一个右连接查询（同理）：
# 优化较明显。这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行，右表一定都有，
# 所以左表是我们的关键点，一定需要建立索引。
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
DROP INDEX X ON class;
ALTER TABLE `book` ADD INDEX Y (`card`);
# 右连接，基本无变化
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

在上面二表的基础上再加一张表

CREATE TABLE IF NOT EXISTS `phone` (
  `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`phoneid`)
) ENGINE = INNODB;

INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO phone (card) VALUES (FLOOR(1 + (RAND() * 20)));

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

以左连接为例：（将右边对应的字段建立索引）
ALTER TABLE `phone` ADD INDEX z (`card`);
ALTER TABLE `book` ADD INDEX Y (`card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL              |   20 |             |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db2019.class.card |    1 | Using index |
|  1 | SIMPLE      | phone | ref  | z             | z    | 4       | db2019.book.card  |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+

# 后 2 行的 type 都是 ref 且总 rows 优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

===============================================================================================

【结论】
join 语句的优化：
    1、尽可能减少 join 语句中的 Nested Loop(嵌套循环) 的循环总次数；“永远用小结果集驱动大的结果集”（小表驱动大表）；
    2、优先优化 Nested Loop(嵌套循环) 的内层循环；
    3、保证 join 语句中的被驱动表上 join 条件字段已经被索引；
    4、当无法保证被驱动表的 join 条件字段被索引且内存资源充足的前提下，不要太吝惜 join buffer 的设置（即将该值调大些）；

【建表语句】
create table test03 (
  id int primary key not null auto_increment,
  c1 char(10),
  c2 char(10),
  c3 char(10),
  c4 char(10),
  c5 char(10)
);

insert into test03 (c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03 (c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03 (c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03 (c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03 (c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

select * from test03;

【建索引】
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;

问题：我们创建了复合索引 idx_test03_c1234，根据以下 SQL 分析下索引使用情况？

explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+

+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+

+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 93      | const,const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-------------+

+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+

===================================================================================================================================

1） explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';     # 索引都使用到了，因为 MySQL 底层进行了优化
2） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';     # 索引都使用到了，因为 MySQL 底层进行了优化
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+

+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-------------+

结论：索引的使用顺序与索引的创建顺序无关，但是最好按照创建索引的顺序使用索引。这样可以避免一次的 sql 自身的查询优化。

3） explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';     # 使用到了 c1、c2、c3索引，c3 范围之后全失效
4） explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';     # 索引都使用到了，因为 MySQL 底层进行了优化，c4 范围之后全失效，但是 c4 之后没有范围了
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+

+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 | Using where |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-------------+

5） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;     # 索引 c3 作用在排序而不是查找（只是用到了一半，但是不算）
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
6） explain select * from test03 where c1='a1' and c2='a2' order by c3;      # 使用到了 c1、c2 索引
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
7） explain select * from test03 where c1='a1' and c2='a2' order by c4;     # 使用到了 c1、c2 索引，出现了 filesort，性能下降
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                       |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------------+

8）
8.1  explain select * from test03 where c1='a1' and c5='a5' order by c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
只用到了 c1 一个字段索引，但是 c2、c3 用于排序，无 filesort。

8.2  explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
只用到了 c1 一个字段索引，且出现了 filesort，我们建的索引是 1234，它没有按照顺序来，3 2 颠倒了。

9） explain select * from test03 where c1='a1' and c2='a2' order by c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
用到了 c1、c2 两个字段索引，但是 c2、c3 用于排序，无 filesort。

10）
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2, c3;
结果同 （9），用到了 c1、c2 两个字段索引，但是 c2、c3 用于排序，无 filesort。

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-------------+
本例有常量 c2 的情况，和 8.2 进行对比，无 filesort。
小结：一般来说，order by 的字段顺序没有跟创建索引的字段顺序一致时，一般会出现 filesort，但是排序字段有常量的情况时，那么 order by 的字段顺序没有跟创建索引的字段顺序一致时 则不会出现 filesort，案例如上。

explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------------+
只用到了 c1 一个字段索引，且出现了 filesort。

11）explain select * from test03 where c1='a1' and c4='a4' group by c2, c3;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-------------+
只用到了 c1 一个字段索引，但是 c2、c3 用于排序，无 filesort。

12）explain select * from test03 where c1='a1' and c4='a4' group by c3, c2;
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                                        |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------+
只用到了 c1 一个字段索引，且出现了 temporary 和 filesort。因为分组之前必排序（内部排序）。

CREATE TABLE tblA (
  #id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL
);

INSERT INTO tblA (age,birth) VALUES (22, NOW());
INSERT INTO tblA (age,birth) VALUES (23, NOW());
INSERT INTO tblA (age,birth) VALUES (24, NOW());

CREATE INDEX idx_A_ageBirth ON tblA(age, birth);

SELECT * FROM tblA; 

mysql> explain select * from tblA where age > 20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tblA where age > 20 order by age, birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tblA where age > 20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tblA where age > 20 order by birth, age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> 

mysql> explain select * from tblA order by birth;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tblA where birth > '2019-06-12 00:00:00' order by birth;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tblA where birth > '2019-06-12 00:00:00' order by age;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from tblA order by age asc, birth desc;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

提高 order by 的速度：

1、order by 时 select * 是一个大忌只 query 需要的字段，这点非常重要。在这里的影响是：
    1.1 当 query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法--单路排序，否则用老算法--多路排序。
    1.2 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size。

2、尝试提高 sort_buffer_size
    不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

3、尝试提高 max_length_for_sort_data
    提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。

CREATE TABLE tbl_new (
  id INT NOT NULL PRIMARY KEY,
  title VARCHAR(20) NOT NULL DEFAULT  ''
  ) ENGINE MYISAM CHARSET utf8
  PARTITION BY RANGE(id) (
  PARTITION t0 VALUES LESS THAN(10),
  PARTITION t1 VALUES LESS THAN(20),
  PARTITION t2 VALUES LESS THAN(MAXVALUE)
);

INSERT INTO tbl_new VALUES (1,'z3');
INSERT INTO tbl_new VALUES (2,'z4');
INSERT INTO tbl_new VALUES (3,'z5');
INSERT INTO tbl_new VALUES (4,'z6');

create table area (
  id INT NOT NULL PRIMARY KEY,
  region varchar(20)
)engine myisam charset utf8;

insert into area values (1,'bj');
insert into area values (2,'sh');
insert into area values (3,'gz');
insert into area values (4,'sz');


create table user (
  uid int not null,
  userName varchar(20),
  area_id int
  )engine myisam charset utf8
  partition by list(area_id) (
  partition bj values in (1),
  partition sh values in (2),
  partition gz values in (3),
  partition sz values in (4)
);

insert into user (uid, userName, area_id) values (1,'z3',1);
insert into user (uid, userName, area_id) values (2,'z4',2);
insert into user (uid, userName, area_id) values (3,'z5',3);

【表级锁分析--建表 SQL】
create table mylock (
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;

insert into mylock (name) values('a');
insert into mylock (name) values('b');
insert into mylock (name) values('c');
insert into mylock (name) values('d');
insert into mylock (name) values('e');

select * from mylock;

【手动增加表锁】
lock table tableName1 read/write, tableName2 read/write, 其它;

【查看表上加过的锁】
show open tables;

【释放表锁】
unlock tables;

事务是由一组 SQL 语句组成的逻辑处理单元，事务具有以下 4 个属性，通常简称为事务的 ACID 属性。 
    原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 
    一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。 
    隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。 
    持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。 

更新丢失(Lost Update)：最后的更新覆盖了由其他事务所做的更新。如果在一个程序员在完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。 

脏读(Dirty Reads)：事务 A 读取到了事务 B 已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果 B 事务回滚，A 读取的数据无效，不符合一致性要求。（读到了错误的数据）

不可重复读(Non-Repeatable Reads)：事务 A 读取到了事务 B 已经提交的修改数据，不符合隔离性。（有个数据读多次，居然读的不一样）

幻读(Phantom Reads)：事务 A 读取到了事务 B 体提交的新增数据，不符合隔离性。（读的数据变多了，出现了幻觉）

多说一句：幻读和脏读有点类似：
    脏读是事务 B 里面修改了数据，
    幻读是事务 B 里面新增了数据。

脏读”、“不可重复读” 和 “幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。 

查看当期数据库的事务隔离级别命令：show variables like 'tx_isolation';

create table test_innodb_lock (a int(11), b varchar(16)) engine=innodb;

insert into test_innodb_lock values (1,'b2');
insert into test_innodb_lock values (3,'3');
insert into test_innodb_lock values (4,'4000');
insert into test_innodb_lock values (5,'5000');
insert into test_innodb_lock values (6,'6000');
insert into test_innodb_lock values (7,'7000');
insert into test_innodb_lock values (8,'8000');
insert into test_innodb_lock values (9,'9000');
insert into test_innodb_lock values (1,'b1');

create index test_innodb_a_ind on test_innodb_lock (a);
create index test_innodb_lock_b_ind on test_innodb_lock (b);

select * from test_innodb_lock;