mysql

数据库优化（中高级）

1.索引底层数据结构

索引本质

索引是帮助MySQL高效获取数据的排好序的数据结构

为什么使用B+树？ 为了查询效率

比较二叉树 红黑树 b树 b+树 的数据结构

存储引擎

MyISAM存储引擎

非聚集：索引文件和数据文件是分离的

InnoDB存储引擎：

聚集：索引文件和数据文件是在一起的

为什么建议InnoDb表必须建主键？ 因为是B+tree存储，如果不建，mysql会自己找数据不相同的列为索引，如果没有自己建一个隐藏列来做这个事情，所以为了效率，

为什么推荐使用整型的？ 因为查找的时候，如果是字符串那要逐一比对ASCII码，整型效率肯定是高。

为什么要自增？ 插入无序，MySQL存储排序。自增不需要分裂，也无需平衡，效率高。

为什么非主键索引结构叶子节点存储的是主键值? (一致性和节省存储空间)

索引方法

1.HASH（哈希）

对索引的key进行一次hash计算就可以定位出数据存储的位置很多时候Hash索引要比B+树索引更高效仅能满足"=" , "IN" ,不支持范围查询hash冲突问题

2.BTREE（树）

非叶子节点不存储data,只存储,引(冗余),可以放更多的索引 叶子节点包含所有索引字段叶子节点用指针连接,提高区间问的性能

3.范围查找（B+tree通过指针查找，MySQL优化了，改为双箭头）

索引最左前缀原理

联合索引的原理：联合索引是按先后顺序排的，整张表来说后边索引不一定是排好序的，只有第一个相同，第二个才是排好序的。

2.Explain

CREATE TABLE `actor` ( 
 `id` int(11) NOT NULL, 
 `name` varchar(45) DEFAULT NULL, 
 `update_time` datetime DEFAULT NULL, 
 PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
 INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18'); 
 
CREATE TABLE `film` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(10) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `idx_name` (`name`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2'); 

CREATE TABLE `film_actor` (
`id` int(11) NOT NULL, 
`film_id` int(11) NOT NULL, 
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1); 

mysql> explain select * from actor;

在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行。

explain 两个变种

1）explain extended

在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

mysql> explain extended select * from film where id=1，

mysql> show warnings;

2）explain partitions

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

 

分库分表

方式：

垂直分片：

按照业务来对数据进行分片，又称为纵向分片。他的核心理念就是转库专用。在拆分之前，一个数据库由多个数据表组成，每个表对应不同的业务。而拆分之后，则是按照业务将表进行归类，分布到不同的数据库或表中，从而将压力分散至不同的数据库或表。

水平分片：

又称横向分片。相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段(或某几个字段)，根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。

分片策略

取余\取模 ： 优点 均匀存放数据，缺点 扩容非常麻烦

按照范围分片 ： 比较好扩容， 数据分布不够均匀

按照时间分片 ： 比较容易将热点数据区分出来。

按照枚举值分片 ： 例如按地区分片

按照目标字段前缀指定进行分区：自定义业务规则分片

水平分片从理论上突破了单机数据量处理的瓶颈，并且扩展相对自由，是分库分表的标准解决方案。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案

缺点：

1.事务一致性问题

2.跨节点关联查询问题

3.跨节点分页、排序函数

4.主键避重问题

5.公共表处理

6.运维工作量

分库分表组件：shardingsphere、mycat、DBLE

日常记录

主要运用一些函数

1.日期格式化

select * from test where date_format(create_time,'%Y-%m-%d') between '2018-07-30' and '2018-07-31';

按月查詢每天的sql数量

select date_format(operateDate,'%Y-%m-%d'),count(id) 
from t_log where datediff(now(),operateDate)<=30
GROUP BY date_format(operateDate,'%Y-%m-%d');

设置创建时间

类型 ：datetime DEFAULT值 ：CURRENT_TIMESTAMP

更新时间

类型 ：timestamp DEFAULT值 ：CURRENT_TIMESTAMP

2.FIND_IN_SET()

注意：mysql字符串函数 find_in_set(str1,str2)函数是返回str2中str1所在的位置索引，str2必须以","分割开。 总结：like是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔，Find_IN_SET查询的结果要小于like查询的结果。

case when 间隔取值、模运算 以30秒为间隔取数据

select * from tablename where time >='2017-1-1' and mod(second(real_time),2)=0

mybaitis返回主键

<select id="listSubsidiary"parameterType="com.contract.subsidiary.entity.Subsidiary" resultMap="SubsidiaryMap"> 
SELECT <include refid="Subsidiary_List"></include> 
FROM tb_subsidiary  WHERE del_state=0 
  <if test="subsidiaryName!=null and subsidiaryName!=''">   
    AND subsidiary_name like CONCAT('%',#{subsidiaryName},'%')  
  </if>  
  <if test="startTime!=null and startTime!=''">   
    AND create_time >= #{startTime}  
  </if>  
  <if test="endTime!=null and endTime!=''">   
    AND #{endTime} >= create_time  
  </if>  order by id desc  
  <if test="pageNumber!=null and pageNumber!='' and pageNumber!=0">   
    limit ${(pageNumber-1)*pageSize},${pageSize}  
  </if> 
</select> 

插入更新操作

sql

SET NAMES utf8mb4; 
SET FOREIGN_KEY_CHECKS = 0; 
-- ---------------------------- 
-- Table structure for user 
-- ---------------------------- 
DROP TABLE IF EXISTS `user`; 
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT, 
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`uid`) USING BTREE, 
UNIQUE INDEX ```username```(`username`) USING BTREE 
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- 
-- Records of user 
-- ---------------------------- 
INSERT INTO `user` VALUES (1, 'admin', '123'); 
INSERT INTO `user` VALUES (2, '123', '456'); 
SET FOREIGN_KEY_CHECKS = 1; 

username username UNIQUE BTREE

唯一主键判断，若唯一主键重复执行修改，若不重复则插入，mysql方言

INSERT INTO USER ( username, `password` ) VALUES ( 'demo', '111' ) ON DUPLICATE KEY UPDATE username = 'demo', `password` = 222 

不存在

drop table if exists excostbean2;
CREATE TEMPORARY TABLE if not EXISTS `excostbean2` (

id递增，如何找到最后一条记录 下边的效率高

select * from tt order by id desc limit 1

select a.* from tt a inner join (select max(id) as ma from tt) b  on a.id=b.ma
 #还可以加上条件 ，例如具体是谁的

 

设置mysql7允许远程连接

1、使用mysql use mysql;

2、查询 select host, user, authentication_string, plugin from user;

3、设置 update user set host='%' where user='root';

4、刷新权限 flush privileges;

如果是mysql8 mysql错误：mysql_native_password

mysql8.0 引入了新特性 caching_sha2_password；这种密码加密方式客户端不支持；客户端支持的是mysql_native_password 这种加密方式；

我们可可以查看mysql 数据库中user表的 plugin字段；select host,user,plugin from user;

可以使用命令将他修改成mysql_native_password加密模式

mysql> updates user set plugin='mysql_native_password' where user='root';
mysql> select host,user,plugin from user;

 

flush privileges;

远程备份 navicat

数据库的大小

1.查看所有数据库容量大小

select table_schema as '数据库', 
sum(table_rows) as '记录数', 
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', 
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc;

 2.查看所有数据库各表容量大小

select table_schema as '数据库', 
table_name as '表名', table_rows as '记录数', 
truncate(data_length/1024/1024, 2) as '数据容量(MB)', 
truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, 
index_length desc; 

3.查看指定数据库容量大小 例：查看mysql库容量大小

select table_schema as '数据库', 
sum(table_rows) as '记录数', 
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', 
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables where table_schema='mysql'; 

4.查看指定数据库各表容量大小 例：查看mysql库各表容量大小

select table_schema as '数据库', 
table_name as '表名', 
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)', 
truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='mysql' order by data_length desc, index_length desc; 

5.查询指定数据内表大于1M的表

SELECT table_schema AS database_name,
table_name AS table_name,
round(((data_length + index_length) / 1024 / 1024),2) AS size_in_mb  FROM information_schema.TABLES  WHERE (data_length + index_length) > 1024 * 1024  and table_schema = '数据库名'  ORDER BY size_in_mb DESC 

 据表名查所在数据库

select table_schema from information_schema.TABLES WHERE table_name = '表名';

我遇到的问题

1.MySql数据库导入sql错误 Unknown collationutf8mb4_0900_ai_ci

错误原因：高版本数据库（8.0）转存sql文件 并导入低版本数据库（5.7）

解决办法： 方案一：升级mysql至高版本 方案二：将需要导入的sql文件，把其中的 utf8mb4_0900_ai_ci全部替换为utf8_general_ci utf8mb4替换为utf8 -------------------------------------------------------------------------------- 重新执行sql文件

2.导入大文件sql MySQL server has gone away错误的解决办法

在我们使用mysql导入大文件sql时可能会报MySQL server has gone away错误，该问题是max_allowed_packet配置的默认值设置太小，只需要相应调大该项的值之后再次导入便能成功。该项的作用是限制mysql服务端接收到的包的大小，因此如果导入的文件过大则可能会超过该项设置的值从而导致导入不成功！下面我们来看一下如何查看以及设置该项的值。 查看 max_allowed_packet 的值。

#第一步
show global variables like 'max_allowed_packet';
#第二部
set global max_allowed_packet=157286400;
#第三步
show global variables like 'max_allowed_packet';

心得

关于常识，不要使用其他语言的 and、or、is null、is not null

一些函数

select CONCAT('前','拼接字符串','后');

select locate('111111','是否包含前边的字符串 111,包含返回位置，不包含结果是0');

select substring_index('是否包含第二个字符串,然后截断,多个由第三个参数选择截断第几个',',',1);

回车换行符

char(13) char(10)

替换字符串

select REPLACE("1234",'1','5')

正则匹配

REGEXP 匹配中文

select '111asda自hlk' REGEXP '[一-龥]'