生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

安装演示数据库sakila

[root@artisan ~]# wget http://downloads.mysql.com/docs/sakila-db.tar.gz
.....
.....
.....
2020-02-01 21:31:15 (2.74 KB/s) - ‘sakila-db.tar.gz’ saved [732161/732161]

[root@artisan ~]# tar -xvzf sakila-db.tar.gz 
sakila-db/
sakila-db/sakila-data.sql
sakila-db/sakila-schema.sql
sakila-db/sakila.mwb
[root@artisan ~]# cd sakila-db
[root@artisan sakila-db]# ls
sakila-data.sql  sakila.mwb  sakila-schema.sql
[root@artisan sakila-db]# mysql -uroot -p < sakila-schema.sql 
Enter password: 
[root@artisan sakila-db]# mysql -uroot -p < sakila-data.sql 
Enter password: 
[root@artisan sakila-db]# 

索引优化策略

索引列上不能使用表达式或者函数

举个例子

select  .... from t_order
where to_days(out_date) - to_days(current_date) < = 30 

即使我们在out_date建立了 B树索引，因为使用了函数to_days，无法走索引。

那该如何改造呢？ ------------> 如下

select  .... from t_order
where out_date <= data_add(current_date , interval 30 day) ; 

前缀索引和索引列的选择性

当索引是很长的字符序列（比如BLOB,TEXT,或者很长的VARCHAR）时，这个索引将会很占内存，而且会很慢，这时候就会用到前缀索引了。所谓的前缀索引就是去索引的前面几个字母作为索引，但是要降低索引的重复率，索引我们还必须要判断前缀索引的重复率

前缀索引的创建

创建前缀索引

create index index_name on table(col_name(n)); 

注意建立索引的区别 col_name(n)

这个n的长度，取悦于存储引擎

• innodb 最大767个字节

• myIsam 最大1000个直接

索引列的选择性

索引的选择性是指不重复的索引值和表的记录数的比值

选择性越高，查询效率越快。 因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

举个例子：

有4条记录

如果前缀索引，我们创建的时候，长度设置的是2 ， 那么

不重复的索引为 2 ，总记录数为4 ， 索引选择性 0.5

如果设置为 3 ，则

不重复的索引为 4 ，总记录数为4 ， 索引选择性1 . 此时，性能最高，因为不用过滤数据啊。

合理选择，对提高查询性能帮助很大

前缀索引的优缺点

优点：

• 前缀索引是一种能使索引更小，更快的有效办法 。

缺点：

• mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

联合索引

如何选择索引列的顺序

• 经常会被使用到的列优先，放到联合索引的最左边 。

  但也不是绝对的，举个例子 有个状态 state，就几个值， 选择性很差（因为根据每个state，筛选出来的数据太多了。。。）就不适合放到联合索引的最左边 ，放到了最左边，mysql也不一定使用

• 选择性高的列优先 。

  啥叫选择性高的列？ 比如根据这个列 经过筛选后，能够把大部分的数据都过滤掉，之剩下很少的数据，那么就可以把这列称为选择性高的列。

• 宽度小的列优先

  当然了有个前提，不违反选择性。 宽度小意味着I/O 少，效率高

覆盖索引

定义

覆盖索引: 如果一个索引包含(或覆盖)所有需要查询的字段的值 ,简言之----->只需扫描索列而无须回表查非索引列的字段。

优点

• 可优化缓存，减少磁盘I/O操作

  举个例子： 一个表 15个字段， 索引字段 3个， 我们就查询这3个索引列的值，而不用回表，查询的字段少，可以缓存更多的数据，同时从内存中获取，可以极大的减少磁盘I/O操作

• 可以减少随机I/O, 变随机I/O为顺序I/O操作

• 可以避免对Innodb主键索引的二次查询

• 可以避免MyISAM表进行系统调用

无法使用覆盖索引的情况

• 有些存储引擎不支持覆盖索引
• 如果查询中使用了太多的列，尤其是那种查询全部字段的，或者 select * 的
• 使用了双% 号的like查询

演示

我们用刚才导入的 sakila 数据 来演示下

select * 无法使用覆盖索引的演示：

[root@artisan sakila-db]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.7.29-log MySQL Community Server (GPL)
.......
.......
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| artisan            |
| artisanBinLog      |
| data               |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
8 rows in set (0.01 sec)

mysql> use sakila;
No connection. Trying to reconnect...
Connection id:    64
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> desc film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field                | Type                                                                | Null | Key | Default           | Extra                       |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| film_id              | smallint(5) unsigned                                                | NO   | PRI | NULL              | auto_increment              |
| title                | varchar(128)                                                        | NO   | MUL | NULL              |                             |
| description          | text                                                                | YES  |     | NULL              |                             |
| release_year         | year(4)                                                             | YES  |     | NULL              |                             |
| language_id          | tinyint(3) unsigned                                                 | NO   | MUL | NULL              |                             |
| original_language_id | tinyint(3) unsigned                                                 | YES  | MUL | NULL              |                             |
| rental_duration      | tinyint(3) unsigned                                                 | NO   |     | 3                 |                             |
| rental_rate          | decimal(4,2)                                                        | NO   |     | 4.99              |                             |
| length               | smallint(5) unsigned                                                | YES  |     | NULL              |                             |
| replacement_cost     | decimal(5,2)                                                        | NO   |     | 19.99             |                             |
| rating               | enum('G','PG','PG-13','R','NC-17')                                  | YES  |     | G                 |                             |
| special_features     | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES  |     | NULL              |                             |
| last_update          | timestamp                                                           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
13 rows in set (0.00 sec)


#  查询 索引列  language_id    重点看  Extra 



mysql> explain select language_id from film where language_id = 1 \G ;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref
possible_keys: idx_fk_language_id  -----> 可能用的索引 
          key: idx_fk_language_id     ----------------> 实际使用的索引
      key_len: 1
          ref: const
         rows: 1000
     filtered: 100.00
        Extra: Using index  ---------------->使用了索引，因为仅查询了索引列，这里就是覆盖索引
1 row in set, 1 warning (0.07 sec)

ERROR: 
No query specified

# 查看执行计划   重点看  Extra 

mysql> explain select *  from film where language_id = 1 \G ;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL  ---------------------->  连接类型
possible_keys: idx_fk_language_id  -----> 可用的索引
          key: NULL    ----------------> 实际的索引
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where  -------------------->   using where：表示优化器需要通过索引回表查询数据；
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 

在来看个例子

mysql> show create table actor \G ;
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> explain select actor_id , last_name from actor where last_name = 'Joe' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index   ------> using index 表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；
1 row in set, 1 warning (0.30 sec)

主键 actor_id ，默认就是索引 ，所以虽然增加了 actor_id , last_name也是索引列（创建primary key的时候肯定会创建一个unique index。），所以这个查询也是使用了覆盖索引。

explain的几个参数的说明

        id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index 

• id

• select_type: 查询中每个select子句的类型

  (1) SIMPLE(简单SELECT,不使用UNION或子查询等)

  (2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

  (3) UNION(UNION中的第二个或后面的SELECT语句)

  (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

  (5) UNION RESULT(UNION的结果)

  (6) SUBQUERY(子查询中的第一个SELECT)

  (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

  (8) DERIVED(派生表的SELECT, FROM子句的子查询)

  (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• type 代表连接类型

  常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL

  从左到右，性能从差到好 ， ALL 最差， NULL最好

  ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

  index: Full Index Scan，index与ALL区别为index类型只遍历索引树

  range:只检索给定范围的行，使用一个索引来选择行

  ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

  const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

  NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

• possible_keys: 可用的索引 ,实际不一定用

• keys : MyQL实际的索引

• key_len: 表示索引中使用的字节数

• extra （需重点关注）
  1) using tempoaray : 中间使用了临时表
  2) using index 表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；
  3) using index condition：5.6加入 ，会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
  4) using where： 未使用索引，通过where条件过滤

搞定MySQL