1. SQL语句查询分析:

(1).explain查询语句的分析:

查看表索引是否命中,及命中条数

参考示例:https://www.cnblogs.com/xpp142857/p/7373005.html  https://blog.csdn.net/gynumber1/article/details/81536401

(2).profiling查询语句的分析

# 查看是否开启
select @@profiling;

# 开启
set profiling = 1;

# 执行目标SQL
SELECT * FROM table_name

# 查询SQL QUERY ID
show profiles;

# 查询分析详情 show profile for query 「execute_sql_num」;
show profile for query 1;

# 分析结果如下:
starting	0.000160
checking permissions	0.000005
checking permissions	0.000002
checking permissions	0.000005
Opening tables	0.000047
After opening tables	0.000008
System lock	0.000004
Table lock	0.000003
After table lock	0.000005
init	0.000069
optimizing	0.000041
statistics	0.000075
preparing	0.000041
executing	0.000004
Sorting result	0.010422
Sending data	0.001561
end	0.000007
query end	0.000006
closing tables	0.000009
freeing items	0.000016
updating status	0.000054
cleaning up	0.000004

2.查询结果排序,把NULL值的排在后面:

默认order by排序如下,按距离排序:

mysql> select * from city order by distance asc;
+----+------+--------------+
| id | city |   distance   |
+----+------+--------------+
|  1 | 北京 | NULL         |
|  3 | 广州 | NULL         |
|  4 | 河北 | NULL         |
|  6 | 河南 | NULL         |
|  2 | 上海 |          100 |
|  5 | 天津 |          200 |
+----+------+--------------+

排完序后NULL的排在前面,我们要的结果是NULL值的排在后面。

处理如下,添加字段值distance IS NULL:

mysql> select * from city order by distance is null, distance asc;
+----+------+--------------+
| id | city |   distance   |
+----+------+--------------+
|  2 | 上海 |          100 |
|  5 | 天津 |          200 |
|  1 | 北京 | NULL         |
|  3 | 广州 | NULL         |
|  4 | 河北 | NULL         |
|  6 | 河南 | NULL         |
+----+------+--------------+

3.数据库存储emoji表情设置:

首先表的编码要为utf8mb4,然后再设置对应字段为UTF-8-mb4-Unicode

4.什么是索引下推,有什么用处?

索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。

在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。

在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率。

5.聚簇索引和非聚簇索引有什么区别?

聚簇索引也叫聚集索引;非聚簇索引也叫非聚集索引。(Innodb使用的是聚簇索引,MyISam使用的是非聚簇索引)

一、聚簇索引(聚集索引)

聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。

二、非聚簇索引(非聚集索引、辅助索引)

在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

6.什么是间隙锁,跟共享锁和排他锁有什么区别:

间隙锁(N锁):

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。

共享锁(S锁):

如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。获准共享锁的事务只能读数据,不能修改数据。

排他锁(X锁):

如果事务T对数据A加上排他锁后,则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。

简要说明为什么会发生死锁?解决死锁的主要方法是什么?

若干事务相互等待释放封锁,就陷入无限期等待状态,系统就进入死锁

解决死锁的方法应从预防和解除的两个方面着手:

(1)死锁的预防方法:①要求每一个事务必须一次封锁所要使用的全部数据(要么全成功,要么全不成功)②规定封锁数据的顺序,所有事务必须按这个顺序实行封锁。

(2)允许死锁发生,然后解除它,如果发现死锁,则将其中一个代价较小的事物撤消,回滚这个事务,并释放此事务持有的封锁,使其他事务继续运行。

7.MySQL对于高并发做了哪些优化处理

mysql高并发的解决方法有:优化SQL语句,优化数据库字段,加缓存,分区表,读写分离以及垂直拆分,解耦模块,水平切分等。

高并发大多的瓶颈在后台,在存储mysql的正常的优化方案如下:

(1)代码中sql语句优化

(2)数据库字段优化,索引优化

(3)加缓存,redis/memcache等

(4)主从,读写分离

(5)分区表

(6)垂直拆分,解耦模块

(7)水平切分

具体数据库设计可参考:本站

MySQL分表、分区方法

ITpub:https://www.sohu.com/a/206982525_671058

8. insert的两种方式:①、insert方式;②、set方式

insert…on duplicate key update ….插入时如果存在记录则更新
replace into 用法

9. 没有密码情况,为MYSQL添加密码(MYSQL装完如何修改密码):  mysql  -uroot -p
回车
提示输入密码,为空回车

update mysql.user set password=PASSWORD('122198') where user='root';

刷新权限表,输入如下命令

flush privileges;

退出

quit

10. 存储过程:
DELIMITER //  
CREATE PROCEDURE demo_in_parameter(IN start_num int, IN end_num int)  
BEGIN   
   while start_num<=end_num do
     -- TODO 处理业务
     set start_num=start_num+1;
   end while;
END;   
//  
DELIMITER ; 

-- 调用存储过程
call demo_in_parameter(1, 100000);
11. Navicat 客户端使用查询的时候出现错误navicatdesignquery.sql.bak 系统找不到指定路径

具体操作:

在连接—属性—高级。修改一下路径,改成你现在安装的navicat目录就好了

12.查看Mysql慢查询的日志

在/var/log/mysqld/slow.log:在服务器上面执行 mysqldumpslow /var/log/mysqld/slow.log  -t 10

13.查看版本命令:show VARIABLES like “%version%”

网友经验:http://blog.itpub.net/29510932/sid-179716-list-1/

14.SQL中WHERE的IF条件判断:

WHERE  IF(条件,  true执行条件, false执行条件 )

SELECT *   
FROM  `table`   
WHERE IF(  `parentID` =1,  `plan_id` <10,  `plan_id` >500 )   
LIMIT 0 , 30

15.mysql事务和锁InnoDBhttp://www.cnblogs.com/zhaoyl/p/4121010.html

16.查询死锁记录,并杀死:

以下查询锁等待及锁的信息

show processlist

NoSQL说明文档:http://blog.path8.net/wp-content/uploads/2012/01/NoSQL_database_note.pdf

17.MySQL5.7及以上版本安装后找不到默认密码文件,没办法修改连接密码:

(1).MySQL5.7之后加强了root用户的安全性,因此在第一次安装后会初始化一个随机密码,以下为查看初始随机密码的方式,执行完该命令后则会看到一组随机字符串为初始密码,以下是查找默认密码的位置:

grep 'temporary password' /var/log/mysqld.log

执行以后有存在log文件,可是里面是空的,这是个坑,后面在网上找到跳过mysql授权直接登录了。将以下两行代码加到/etc/my.cnf文件的[mysqld]下面。重启mysql服务,就可以直接进入MySQL命令行:

skip-grant-tables #(跳过授权,只需要这句就好)
skip-networking #(本机运行,关闭MySQL的TCP/IP连接方式)

添加以上代码后重启MySQL服务:systemctl restart mysqld,然后重新登录mysql,mysql -uroot就可以直接进入修改密码;

但是修改密码还有版本号的区别,不同版本号不同的修改语句:

set password for 'root'@'localhost'=password('123456');(错误)
update user set password=password('123456') where user='root' and host='localhost';(错误)

在mysql5.7以后已经将原来的password字段改为了authentication_string了

update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';(正确)

记得刷新表的权限:

flush privileges;

更新后再把原来跳过授权的两句mysql配置去掉,重启mysql服务即可。

配置后经常想要作为远程连接,或者在虚拟机vagrant的MySQL,在PC端本地想连接,就需要设置权限:

update user set host = '%' where user ='root';

或者执行

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

※同时MySql5.7 子查询 order by 失效,MySQL为了优化内部结构调整子查询的排序无效,毕竟在嵌套里面确实用得也不多,那就在外层排序了。

如果没有root用户的情况,可以先置为不需要账户登录,然后插入root用户数据,再设置权限;

(2).MySql8.0.x无法远程连接的解决办法:

MariaDB出现连接错误: Authentication plugin 'caching_sha2_password' cannot be loaded

出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password。

解决问题方法有两种:一种是升级navicat驱动;一种是把mysql用户登录密码加密规则还原成mysql_native_password.

登录MySQL后:
   修改账户密码加密规则并更新用户密码

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;   #修改加密规则 

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';   #更新一下用户的密码 

   刷新权限并重置密码
   FLUSH PRIVILEGES;   #刷新权限 

本人尝试过:现在再次打开 Navicat Premium 12 或 SequelPro 连接MySQL问题数据库就会发现可以连接成功了。
(单独重置密码命令:alter user 'root'@'localhost' identified by '111111';)

18.查看MySQL版本号的5种办法

一、使用命令行模式进入MySQL,看到MySQL相关信息:

mysql -uroot -p123456

二、直接在MySQL命令行输入status命令:

status

三、使用MySQL的查询函数:

select version();

四、使用shell方式,MySQL帮助命令:

mysql --help | grep Distrib

五、使用系统套件管理命令查看:

rpm -qa | grep mysql

19.MySQL计算经纬度距离(两个位置距离)的两种办法:

一、使用GeomFromText地理位置文本函数:

(GLength(GeomFromText(CONCAT('LineString({$where['lat']} {$where['lon']},',b.latitude,' ',b.longitude,')')))/0.0000092592666666667) as distance

二、使用地理位置对象Geometry函数:

插入使用:geom => ST_GeomFromText('POINT(118.082 24.4458)');

查询使用:ST_DISTANCE_SPHERE(POINT(118.1727416, 24.4857075), geom) AS distance

可查看Mr Ming的示例:https://sevming.github.io/Mysql/mysql-geometry.html

三、使用弧度函数计算距离,经纬度范围比较法优化,过滤不需要的数据(附加:经纬度加索引搜索)

1.使用条件查询:table.lng – $lng_diff < $lng < table.lng + $lng_diff(纬度类似)

2.再计算出距离;

round(ASIN(SQRT((COS(0.017453292519943 * 24.490474) * COS(0.017453292519943 * 118.11022) - COS(0.017453292519943 * mm.lat) * COS(0.017453292519943 * mm.lng)) * (COS(0.017453292519943 * 24.490474) * COS(0.017453292519943 * 118.11022) - COS(0.017453292519943 * mm.lat) * COS(0.017453292519943 * mm.lng)) + (COS(0.017453292519943 * 24.490474) * SIN(0.017453292519943 * 118.11022) - COS(0.017453292519943 * mm.lat) * SIN(0.017453292519943 * mm.lng)) * (COS(0.017453292519943 * 24.490474) * SIN(0.017453292519943 * 118.11022) - COS(0.017453292519943 * mm.lat) * SIN(0.017453292519943 * mm.lng)) + (SIN(0.017453292519943 * 24.490474) - SIN(0.017453292519943 * mm.lat)) * (SIN(0.017453292519943 * 24.490474) - SIN(0.017453292519943 * mm.lat)))/ 2.0) * 12742001.579854, 0) AS distance