MySQL基础优化一

修改数据库编码字符集

MySQL客户端和服务器默认采用latin1编码，也可能版本不同采用不同的编码，总之这种情况下插入中文为乱码或不允许插入中文，所以我们要修改默认字符集。

1.查看字符集，下面两种方式任选其一

show variables  like '%char%'
show variables like 'character%'

2.修改MySQL配置文件

# 已省略其余默认配置
[mysql]
no-beep
++ default-character-set=utf8

[mysqld]
port=3306
++ character_set_server=utf8
++ collation-server=utf8_general_ci

• Windows下配置文件地址，注意：此文件地址默认隐藏，手动查找前需打开隐藏文件选项：

  C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
  

• Linux下配置文件地址

  /etc/mysql/my.cnf
  

注意：这种方式修改完之后，必须要重启sql服务，而且经本人测试，不知道为什么，原有的数据库依然不能存储中文，改了编码集也不行，如果说非要用原始库来存储中文数据，可以在建表时显示指定字符编码和存储引擎。

MyISAM与InnoDB存储引擎的区别

对比项	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
行表锁	支持行锁，操作时可以只锁住一行，不影响其他行。适合高并发操作	只支持表锁，即使操作一条记录也会锁住整张表。不适合高并发。
缓存	既缓存索引也缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。	只缓存索引，不缓存真实数据。
表空间	大	小

MySQL执行顺序

MySQL语句书写顺序

SELECT DISTINCT 
	<select_list>
FROM 
	<left_table><join_type> 
JOIN <right_table>  ON  <join_condition>
WHERE
	<where_condition>
GROUP BY 
	<group_by_list>
HAVING 
	<having_condition>
ORDER BY 
	<order_by_condition>
LIMIT 
	<limit_number>

MySQL语句执行顺序

FROM  <left_table>
ON   <join_condition>
<join_type> JOIN <right_table>  
WHERE  <where_condition>
GROUP BY   <group_by_list>
HAVING  <having_condition>
SELECT  
DISTINCT <select_list>  
ORDER BY <order_by_condition>
LIMIT <limit_number>

MySQL逻辑架构分层

SQL效率低下的常见原因

索引

索引是一种数据结构，目的在于提高查找效率。

什么是索引

在数据之外，数据库系统还维护者一些满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级特定查找算法。这种数据结构，就是索引。

索引本身也很大，不可能全部存储到内存中，一般以文件的形式存储在磁盘上。

我们平时所说的索引如果没有特别说明，都是指B树（多路搜索树，并不一定是二叉树）组织结构的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

索引的优势劣势

优势	劣势
大大加快数据的检索速度;	索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
创建唯一性索引，保证数据库表中每一行数据的唯一性;	当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
加速表和表之间的连接;	我们需要花费一定的时间去研究建立最优秀的索引。
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

常见索引分类

索引	简介
主键索引	数据记录里面不能有 null,数据内容不能重复，在一张表里面只能有一个主键索引。
普通索引	使用字段关键字建立的索引，主要是提高查询速度。
唯一索引	字段数据是唯一的，允许在唯一索引的字段中出现多个null值的。可以添加多个唯一索引。
复合索引	一个索引包含多个列。

索引的基本语法

# 创建索引
# 以下两种方式任选其一即可
CREATE INDEX indexName ON table_name(column_name)
ALTER TABLE table_name ADD index_type index_name(column_name) 

# 删除索引
DROP INDEX index_name ON table_name 

# 查看索引
SHOW INDEX FROM table_name\G

索引的建立场景

俗话说是药三分毒，索引并不是想建就可以建的，它也有自己的创建时机。在合适的时间合适的地点创建合适的索引，才能有效的提高查询效率，不然可能会适得其反。

适合建立索引	不适合建立索引
频繁作为查询条件的字段适合建立索引	频繁更新的字段不适合建立索引
关联字段需要建立索引，例如外键字段	很少被访问到的字段不适合建立索引
排序字段可以建立索引，增排序速度	唯一性太差的字段不适合建立索引，例如性别，真假值
分组字段可以建立索引，因为分组的前提是排序	表记录太少，则不需要创建索引
统计字段可以建立索引，例如count(),max()	一张表不要建立过多的索引

EXPLAIN关键字

使用EXPLAIN关键字可以模拟SQL优化器执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句，比如执行顺序如何？是否使用索引？受影响的行数有多少？我们可以借此来分析查询语句或是表结构的性能瓶颈以及优化方案。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5dE6VfXO-1588570781624)(C:\Users\zhangshuai\Desktop\EXPLAIN.png)]

id：用来表示读取表的顺序，以优先级划分。

• 如果id值相同，则依次从上往下执行。
• 如果id值不相同，比如子查询，id的序号会出现递增的情况，id值越大优先级越高，越先被查询。
• 如果id值有相同也有不相同的，优先级大的先执行，同级优先级依次执行。

select_type：标识查询的类型，比如你用的是普通查询，联合查询还是子查询或其他的查询。

• SIMPLE：表示简单的select查询，查询中不包含子查询或者UNION。
• PRIMARY：如果查询中包含任何复杂的子部分，那么最外层查询被标记为PRIMARY。
• SUBQUERY：表示在SELECT或WHERE列表中包含了子查询。
• DERIVED：在FROM列表中包含的子查询会被标记为DERIVED（衍生），MYSQL会地柜执行这些子查询，并把结果放在临时表里。

type：对表的访问方式，它用来表示MySQL是通过哪种方式找到我们所需的数据的，是全表扫描还是通过索引？它也称为访问类型。一般来说，得保证查询至少达到ranger级别，最好能达到ref级别。

从最好到最坏依次是

system > const > eq_ref > ref > ranger > index > ALL

• system：表只有一行记录，它属于const类型的特殊情况，一般不会出现，可以忽略不计。

• const：它表示通过索引仅一次就找到了 ，const用于比较promary key 或 unique索引。因为只匹配一行数据，所以速度非常快。

• eq_ref：唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

• ref：结果有多条，但是数据要么是0条要么是多条，如果只有一条，那应该为eq_ref。

• ranger：检索指定范围的行，where面是一个范围查询（between，in，>，<），in有时候会失效，转为无索引。

  select t.* from teacher t where t.id in (1,2)
  # where后面为泛微查询，则此时type的值为ranger
  

• index：查询全部索引的数据

  explain select id from teacher;
  # 若id为索引，则type字段的值为index
  

• ALL：查询表中所有数据

  explain select tname from teacher;
  # tname 不是索引，因此会全表扫描，type值为ALL
  

possible_keys：可能会用到的索引，一种预测，一个或多个。查询的字段上若存在索引，则该索引将被列出，但不一定被实际查询所使用。

key：真正所实际所使用的索引，如果为NULL，则表示没有使用索引。

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

创建示例表：

-- 创建表
create table test(
	name char(20) NOT NULL, 
	address char(20)
)ENGINE=INNODB CHARSET=utf8

-- 为字段添加单值索引
alter table test add index name_index(name)
alter table test add index name_index(address)

-- 查看执行计划
explain select * from test where name = ''      -- key_len：60 （20*3）
explain select * from test where address = ''   -- ken_len：61 （20*3 + 1）

-- 创建复合索引，首先删除上面的单值索引
DROP index name_index on test
DROP index address_index on test
alter table test add index na_index(name, address)

-- 查看执行计划
explain select * from test where name = ''      -- key_len：60 （20*3）
explain select * from test where address = ''   -- ken_len：121 （20*3 + 20*3 + 1）
	

分析单值索引执行结果：

使用字符集为utf8，一个字符占三个字节。
我们在查询中使用到了name的索引，那么key_len的值为 20*3= 60，当然这是name字段不允许为空的情况。
如果name字段允许为空，则长度应为61，MySQL底层会用一个字节标识一下该字段允许为NULL。

分析复合索引执行结果：

我们创建的复合索引顺序为name,address，当我们仅仅使用name索引的时候，不会使用到address索引，所以key_len仅为60（20*3）。

而当我们使用address索引的时候，必先使用name索引，再使用address索引，又因为address字段允许为空，所以ken_len的值为121（ 20*3 + 20 *3 + 1）

如果是可变长度VARCHAR结果又该怎样？

以上我们用的CHAR类型为定长，如果是VARCHAR结果会怎么样，创建示例表：

create table test2(
	name varchar(20) NOT NULL,
	address varchar(20) 
)ENGINE=INNODB CHARSET=utf8

-- 为字段添加单值索引
alter table test add index name_index(name)
alter table test add index name_index(address)

-- 查看执行计划
explain select * from test where name = ''      -- key_len：62 （20*3 + 2）
explain select * from test where address = ''   -- ken_len：63 （20*3 + 1 + 2）

-- 创建复合索引，首先删除上面的单值索引
DROP index name_index on test
DROP index address_index on test
alter table test add index na_index(name, address)

-- 查看执行计划
explain select * from test where name = ''      -- key_len：62 （20*3 + 2）
explain select * from test where address = ''   -- ken_len：125 （20*3 + 20*3 + 1 +2 +2）

可以看到，因为我们使用了可变长度VARCHAR，MySQL底层不仅使用1个字节标识字段允许为NULL，还会使用2个字节标识字段为可变长度。所以在计算ken_len的时候，会比原来再多出两个字节。

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

select .. from ... where a.c = b.x 
-- 在上面的例子中我们可以说a表的c字段引用（ref）了b表的x字段，其中b.x可以为常量。如果真的是常量，则ref的值为const。

**rows：实际通过索引查到的数据条数 **

Extra：该列包含上述列之外的同样重要的SQL执行信息**

• using where：使用了where查询。
• using filesort：性能消耗大，需要额外的排序。如果是单索引，查询的字段和排序字段不一致，就会出现using filesort，我们应尽量满足where的字段和order by 的字段一致，如果是复合索引，应该按照复合索引的顺序使用，不要跨列使用或顺序使用。
• using temporary：这表示MySQL用到了临时表来存储结果集，性能损耗大，常见于group by语句中。
• uding index：不读取源文件，只读取索引的数据即可，不需要回表查询，性能有一定的提升。这被称为“索引覆盖”：所有使用到的列都在复合索引里。而且，如果真的发生了索引覆盖，会对possible_keys和key的值产生影响：如果语句中没有where，则索引只出现在key中，不会出现在possible_keys中，如果有where，则都会出现。

ere的字段和order by 的字段一致，如果是复合索引，应该按照复合索引的顺序使用，不要跨列使用或顺序使用。

• using temporary：这表示MySQL用到了临时表来存储结果集，性能损耗大，常见于group by语句中。
• uding index：不读取源文件，只读取索引的数据即可，不需要回表查询，性能有一定的提升。这被称为“索引覆盖”：所有使用到的列都在复合索引里。而且，如果真的发生了索引覆盖，会对possible_keys和key的值产生影响：如果语句中没有where，则索引只出现在key中，不会出现在possible_keys中，如果有where，则都会出现。