生猛干货
带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试
官方文档
如果英文不好的话，可以参考 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
https://artisan.blog.csdn.net/article/details/104139438?spm=1001.2014.3001.5502
