mysql基础

Lyndon1107

已于 2024-06-18 11:43:14 修改

阅读量2.3k

点赞数

分类专栏：数据库文章标签： mysql

于 2019-07-21 15:38:41 首次发布

本文链接：https://blog.csdn.net/qq_19636353/article/details/96726656

版权

数据库专栏收录该内容

7 篇文章 1 订阅

订阅专栏

第一部分基础篇

书

《深入浅出mysql》学习数据库工具书，突破对数据处理工具的各种理解障碍，形象的联系实际，体裁新颖，立意突出。

1.下载安装与配置
1.1 下载地址
https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.18-el7-x86_64.tar

 安装（windows）
1.解压到目录 D:\mysql，添加到PATH环境变量 D:\mysql\bin。
2.安装 D:\mysql\bin> mysqld --install  
3.生成data文件
D:\mysql\bin>mysqld --initialize --console    # 获得密码（kZLQwwG)E5%i）
4.登录、修改mysql8密码
D:\mysql\bin>mysql -uroot -pkZLQwwG)E5%i
mysql> use mysql;
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';

卸载

#windows卸载
D:\mysql\bin> net stop mysql
D:\mysql\bin> D:\mysql\bin>mysqld --remove

#linux卸载
1.停止服务
[root@localhost /]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service
2.查找并执行卸载
[root@localhost /]# rpm -qa|grep -i mysql
rpm -ev mysql80-community-release-el7-1.noarch --nodeps
rpm -ev mysql-community-common-8.0.11-1.el7.x86_64 --nodeps
rpm -ev mysql-community-server-8.0.11-1.el7.x86_64 --nodeps
rpm -ev mysql-community-libs-8.0.11-1.el7.x86_64 --nodeps
rpm -ev mysql-community-libs-compat-8.0.11-1.el7.x86_64 --nodeps
4.查看之前安装的目录并删除
[root@localhost /]# find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
5.删除my.cnf
rm -rf /etc/my

启动

查看进程
netstat -ano |find “3306” # windows
ps -ef |grep 3306

查看版本
mysql> select version();

配置
mysql启动原理_MySQL读取配置文件的顺序、启动方式、启动原理
https://blog.csdn.net/weixin_34878397/article/details/113253720

第3章数据类型

tinyint和int区别

https://blog.csdn.net/qq_36204764/article/details/103871105

Decimal 数值类型

tinyint
从 -2^7 (-128) 到 2^7 - 1 (123) 的整型数据。存储大小为 1 个字节。
unsigned 是从 0 到 255 的整型数据。
所以建表的时候 只能是tinyint(3),哪怕你建tinyint(100)，他最大还是3位这么多。

DECIMAL(M,D)

DECIMAL(M,D)中，M范围是1到65，D范围是0到30。
M默认为10，D默认为0，D不大于M。
DECIMAL(5,2)可存储范围是从-999.99到999.99，超出存储范围会报错。
存储数值时，小数位不足会自动补0，首位数字为0自动忽略。
小数位超出会截断，产生告警，并按四舍五入处理。
使用DECIMAL字段时，建议M，D参数手动指定，并按需分配。
ALTER TABLE cm_order ADD COLUMN pay_money DECIMAL(10,2) DEFAULT 0 AFTER coupon

创建数据库

show databases;
create database if not exists test; #创建数据库test
CREATE DATABASE test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

查看表创建语句
use test;
SHOW CREATE TABLE test #查看表创建语句

查看数据库使用大小

1.切换数据库
use information_schema;
2.查看数据库使用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name';
3.查看表使用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name' and table_name='Table_Name';

#查看所有表使用情况
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='DB_Name'
order by data_length desc, index_length desc;

表和视图命名规则

表和视图命名规则
https://blog.csdn.net/DreamMessi/article/details/78914051

创建表

反引号，是定义的字段名与系统关键字发生冲突。

show tables from testdb;
drop table if exists `tb_member`;

create table if not exists `tb_member`(
  id  varchar(10)  not null  primary key,
  name  varchar(10),
  age  int,
  salary  double(16,2) ,
  date  datetime,
  `descx`  varchar(10)
) Engine=InnoDB DEFAULT charset=utf8 COMMENT 'Member成员表';

insert into tb_member values('1001', 'Lyndon', 18, 1800.0, '2019-07-21', '周六');

创建表

新增-插入多条记录
insert into 表名（字段名1，字段名2）values(值a1，值b1), (值a2,值b2);

修改表名称

ALTER TABLE 原表名 RENAME TO|AS 目标表名;

更新列名

查看列名
SHOW FULL COLUMNS FROM permission

1.修改列定义（modify col_name）
alter table user change uuid token varchar(32);
2.修改列类型（modify col_name）
3.修改列名称（change col_name）
4.删除列名称（drop col_name）

ALTER TABLE wxt_user ADD field2 varchar(10);
ALTER TABLE wxt_user ADD COLUMN field1 varchar(10) NULL COMMENT 'field1' after field2;
ALTER TABLE wxt_user modify field2 INT after field1;
ALTER TABLE wxt_user DROP COLUMN field2;

无符号INT
ALTER TABLE permission CHANGE code id INT UNSIGNED NOT NULL;

alter语句中change和modify的区别：

ALTER TABLE t1 CHANGE a b INTEGER; -- 把一个INTEGER列的名称从a变更到b
# 更改列的类型，CHANGE语法仍然要求旧的和新的列名称，即使旧的和新的列名称是一样的。
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
使用MODIFY来改变列的类型，此时不需要重命名：
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

分表语句

alter table t_lock_trajectory DROP PRIMARY KEY,ADD PRIMARY KEY (`id`, `g_time`) USING BTREE;

alter table t_lock_log PARTITION BY RANGE (to_days(add_date))(
	PARTITION p202201 VALUES LESS THAN (to_days('20220101')),
	PARTITION p202202 VALUES LESS THAN (to_days('20220201')),
	PARTITION p202203 VALUES LESS THAN (to_days('20220301')),
	PARTITION p202204 VALUES LESS THAN (to_days('20220401')),
	PARTITION p202205 VALUES LESS THAN (to_days('20220501')),
	PARTITION p202206 VALUES LESS THAN (to_days('20220601')),
	PARTITION p202207 VALUES LESS THAN (to_days('20220701')),
	PARTITION p202208 VALUES LESS THAN (to_days('20220801')),
	PARTITION p202209 VALUES LESS THAN (to_days('20220901')),
	PARTITION p202210 VALUES LESS THAN (to_days('20221001')),
	PARTITION p202211 VALUES LESS THAN (to_days('20221101')),
	PARTITION p202212 VALUES LESS THAN (to_days('20221201')),

	PARTITION pmax VALUES LESS THAN MAXVALUE
);

SQL查询

distinct 查询去掉重复记录

select distinct 字段名1，字段名2，字段名3 from 表名

ISNULL 与 NULLIF

NULLIF(expr1,expr2)  的用法：如果expr1 = expr2成立，那么返回值为NULL，否则返回值为expr1。
ISNULL(Expression1,Expression2)：给定两个参数Expression1和Expression2，如果Expression1是NULL，那么返回Expression2，否则返回Expression1。

CASE、WHEN、THEN、ELSE、END

SELECT user_name,
CASE
	WHEN role = 2 THEN '工厂'
	WHEN role = 5 THEN '代理'
	WHEN role = 6 THEN '门店'
	ELSE 0
END
AS role_name
FROM mall.ecs_users WHERE user_name='15100010011' limit 10

####### UPDATE ########
UPDATE cm_rent_order SET rent_way_id = 
CASE dev_id
		WHEN dev_id IN ('2201000008','2201000031')
		THEN 3389
END,
	create_time = 
CASE
	WHEN dev_id IN ('2201000008','22010000031')
	THEN UNIX_TIMESTAMP(NOW())
END
WHERE dev_id IN ('2201000008','2201000031') LIMIT 5

百分比统计计算

SELECT
	m.id,
	m.name,
	d.not_rent_count,
	d.rented_count,
	d.rent_total_count,
	ROUND( d.rented_count / d.rent_total_count * 100, 1 ) AS rent_rate --出租比率
FROM (
	SELECT
		owner_number,
		SUM( CASE rent_status WHEN 0 THEN 1 ELSE 0 END ) AS not_rent_count,-- 可租
		SUM( CASE rent_status WHEN 3 THEN 1 ELSE 0 END ) AS rented_count,-- 已租
		SUM( CASE rent_status WHEN 6 THEN 0 ELSE 1 END ) AS rent_total_count -- 总数量
	FROM cm_dev 
	WHERE modle = 10 -- and d.owner_number IN (962)
	GROUP BY member_id
	) AS d
	JOIN cm_member m ON d.owner_number = m.id

第5章常用函数

5.1 字符函数

5.1.1 concat函数

用于将两个或多个字符串拼接成一个字符串并返回，语法为“CONCAT(string1,string2, … );”；该函数需要至少一个参数，否则会引起错误。concat()函数在拼接之前会将所有参数转换为字符串类型；如果任何参数为NULL，则CONCAT()函数返回NULL值。

5.1.2 group_concat 函数

mysql 将list转逗号
SELECT GROUP_CONCAT(column_name SEPARATOR ',') as list_string FROM table_name WHERE condition

group_concat用法详解 https://blog.csdn.net/qq_33323054/article/details/125193170

5.2 数值函数

5.3 日期和时间函数

学习链接 https://www.cnblogs.com/php12-cn/p/8882221.html

MySQL中如何表示当前时间？汇总如下：

    CURRENT_TIMESTAMP
    CURRENT_TIMESTAMP()
    NOW()
    LOCALTIME
    LOCALTIME()
    LOCALTIMESTAMP
    LOCALTIMESTAMP()

例 update_time 字段

+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

日期date型和int型相互转换

1.date型换int型
SELECT UNIX_TIMESTAMP(‘2020-7-13 21:51:45’)
2.int型转date型
SELECT FROM_UNIXTIME(1594648305)

LEFT函数
UPDATE alm_dev_current_data SET login_time = LEFT(login_time, 10)

TO_DAYS函数

SELECT TO_DAYS(‘1997-10-07′); #从0年开始到1997年10月7号之间的天数。结果 729669

#查询订单到期时间2天
mysql> select * from order where to_days(now())-to_days(end_time) < 2

5.50 其他函数

ROUND 函数

Point 函数
MySQL中的point用于表示GIS中的地理坐标，在GIS中广泛使用。
https://blog.csdn.net/qq_40722604/article/details/87358227

更新参数
UPDATE t_parking SET `location` = ST_GeomFromText('POINT(108.923163 34.23147)')

st_distance 函数

SELECT *, ROUND((st_distance (point(lat, lng), point(#{lat}, #{lng})) * 111195), 0) AS distance FROM t_parking

ST_UNION
MySQL提供了名为ST_UNION函数，它可以用于聚集多个点、线或面。

mysql给查询结果额外添加自增列

SELECT @rownum:=@rownum+1 AS rownum, aliaA.*
FROM (
select user_id, user_name from t_user
) aliaA, (SELECT @rownum:=0) aliaB

第8章字符集

编码方式
mysql默认编码latin1不支持中文字符，通过设置编码字符为utf8，从而支持中文字符。
SHOW CREATE TABLE test #查看表创建语句
1）修改表的编码方式改为utf8：
ALTER TABLE test DEFAULT CHARACTER SET utf8;
2）修改字段的编码方式：
ALTER TABLE test CHANGE name name VARCHAR(36) CHARACTER SET utf8 NOT NULL;
将表test中name字段的编码方式改为utf8。

时区

EDT为美国东部dao夏令时间，提前bai1小时。如果正常情况中du国和美国相差12小时的zhi话，用EDT就相差11小时。
UTC指的是世界协调时间，是世界标准时间。
CST如你设的区域是中国的话，就为中国标准时间。

select now();
show variables like '%time_zone%';
mysql> set global time_zone = '+8:00';
mysql> set time_zone = '+8:00';
mysql> flush privileges;

方法二：通过修改my.cnf配置文件来修改时区
# vim /etc/my.cnf ##在[mysqld]区域中加上
default-time_zone = '+8:00'
# /etc/init.d/mysqld restart ##重启mysql使新时区生效

方法三：如果不方便重启mysql，又想临时解决时区问题，可以通过php或其他语言在初始化mysql时初始化mysql时区

mysql关系模型

主键、外键、索引

主键

自增值 AUTO_INCREMENT

查询表名为tableName的auto_increment值：
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name='tableName';
修改表名为tableName的auto_increment值：
ALTER TABLE tableName auto_increment=number ;
解决auto_increment 编号不连续的问题
ALTER TABLE wxt.`role` ADD id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

CREATE TABLE IF NOT EXISTS wxt.`role`(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(10)
)Engine=InnoDB DEFAULT charset=utf8;

ALTER TABLE wxt.`role` ADD id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE wxt.`role` AUTO_INCREMENT=100
ALTER TABLE wxt.`role` CHANGE id id INT UNSIGNED NOT NULL PRIMARY KEY  AUTO_INCREMENT FIRST;
ALTER TABLE wxt.`role` MODIFY id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

#设置id初始值
ALTER TABLE tableName AUTO_INCREMENT=10000

删除主键（含自增列）

在MySQL中删除主键需要两步：
1.如果pid有auto_increment，先删除之；
alter table products change pid pid int;
2.删除pid主键约束 primary key.
alter table products drop primary key;

删除列、主键，再重新设置id为UUID()
-- SELECT * FROM alm_dev
ALTER TABLE alm_dev DROP COLUMN id;
alter table alm_dev add COLUMN id varchar(32) not null FIRST;
UPDATE alm_dev SET id = REPLACE(UUID(),'-','') WHERE id ='' LIMIT 1
ALTER TABLE alm_dev add PRIMARY key(id);
使用SQL语句“alter table alm_dev modify id int auto_increment primary key; 来修改主键列

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。对于联合主键，只要不是所有主键列都重复即可。

DROP TABLE IF EXISTS wxt.user_dev;
CREATE TABLE IF NOT EXISTS wxt.`user_dev`(
  `uid` INT NOT NULL  COMMENT '用户id',
  `dev_id` VARCHAR(10) NOT NULL  COMMENT '设备id',
  PRIMARY KEY(uid, `dev_id`) -- 联合主键
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '用户与设备关联表';

外键

外键的作用：保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

外键约束的作用

1）从表不能添加，主表不存在的数据
2）主表不能删除从表中引用的数据

阻止执行：
1.从表插入新行，其外键值不是主表的主键值便阻止插入；
2.从表修改外键值，新值不是主表的主键值便阻止修改；
3.主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
4.主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

CASCADE 级联执行：
1.主表删除行，连带从表的相关行一起删除；
2.主表修改主键值，连带从表相关行的外键值一起修改。两种方法提供给用户选择。无论选取哪种方法，从表里都不会有多余行。从另一个角度理解，用拒绝同一事物在从表中的标志与主表不一致来实现与主表中的标志一致。

CREATE TABLE `a_table` (
  `a_id` int(11) PRIMARY KEY,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b_table` (
  `b_id` int(11) PRIMARY KEY,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL,
  FOREIGN KEY(b_id) REFERENCES a_table(a_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#添加索引索引
ALTER TABLE students 
ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE CASCADE;

#查看表的外键关系：
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  where REFERENCED_TABLE_NAME='students' 

#删除索引
ALTER TABLE students DROP FOREIGN KEY fk_class_id;

设置失败的原因，有以下几点：

1，两张表里要设主键和外键的字段的数据类型或者数据长度不一样 （例如这个是int 另外一个是tinyint，或者都是int，但是设置的长度不同）
ALTER TABLE students DEFAULT CHARACTER SET utf8;
2，某个表里已经有记录了
3、两个表的引擎不一样，查看表的引擎语句：
show table status from 数据库名 where name='表名';
4、要设置外键的字段不能为主键
5、改建所参考的字段必须为主键
6、两个字段必须具有相同的数据类型和约束

https://www.freesion.com/article/558483217/

多对多

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。
多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

HAVING

mysql根据两个字段查询重复的记录
select * from order_payment group by order_no, serial_no having count(*) > 1

第二部分开发篇

第6章表类型（存储引擎）的选择 98

6.2 各种存储引擎的特性 100

InnoDB引擎的4大特性：链接：https://www.cnblogs.com/zhs0/p/10528520.html
1.插入缓冲（insert buffer)；2.二次写(double write)；3.自适应哈希ahi（Adaptive Hash index）；4.预读(read ahead)。

第9章索引的设计和使用

在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

B+Tree

什么是B+Tree

B+Tree不同点
1.非叶子节点的子树指针与关键字个数相同；
2.非叶子节点的子树指针指向关键字值k[i],k[i+1]的子树；
3.非叶子节点的仅用来做索引，数据都保存在叶子节点中；
4.所有叶子节点均有一个链指针指向下一个叶子节点:链接起来方面我们直接在叶子节点做范围统计；而不是再回到子节点中；一旦定位到某个叶子节点，便可以从该叶子节点横向的去跨子树去做统计

采用B+Tree作为主流索引数据结构的原因？

1.更适合做存储索引
2.B+树的磁盘读写代价更低
内部的结构并没有指向关键字的具体指针；不存放数据，只存放索引信息；内部节点相对B树更小；

3.B+树的查询效率更加稳定
内部节点并不是最终指向文件内容的节点，只是叶子节点中关键字的索引；
所以它任何关键字的查找，必须走一条从根节点到叶子节点的路；
所有关键字查询的长度相同，导致每一个数据查询的效率也几乎是相同。

4.B+树更有利于对数据库的扫描.
B树在提高IO性能同时，并没有解决元素遍历效率低的问题；
B+树只需要遍历叶子节点，就可以解决对全部关键字信息的扫描；
对数据库中，频繁使用的范围查询，性能更高。

使用索引的优势

1.提高查询效率，降低数据库的IO成本；
2.通过索引对数据项进行排序，降低数据排序成本，降低CPU消耗。

索引的分类

主键索引：Primary Key
唯一索引：索引列的值必须唯一，但允许有空值
普通索引：（二级索引/辅助索引）name age是2个单独的索引即为2个单独的B+树
全文索引：Mysql全文检索是l利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引提高匹配的速度
组合索引：复合索引（联合索引），给(name,age)创建索引，此时的key包含了两个列的值，所以叫组合索引

索引的劣势

1.一般来说，索引本身也很大，以文件的形式存储在磁盘上；
2.索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也占用磁盘空间；
3.虽然索引提高了查询速度，但是会降低更新表的速度（如索引位置发生变化）；
4.因为更新表时，mysql不仅要保存数据，还要保存一下索引文件（每次更新索引列的字段）；
5.会调整因为更新所带来的键值变化后索引的信息。

索引建立与选择

设计索引的原则：1.要在条件列上创建索引 2.尽量使用唯一索引 3.使用短索引 4.利用最左前缀 5.尽量手工指定索引

适合建立索引
	1.主键自动建立唯一索引 primary
	2.频繁作为查询条件的字段应创建索引，如账号、手机号
	3.查询中与其他表关联的字段，外键关系建立索引，如订单号外键
	4.频繁更新的字段不适合建立索引。（每次既要更新数据，又要更新索引）
	5.where条件里用不到的字段不建立索引
	6.查询中排序的字段，若通过索引去访问将大大提升排序速度
	7.查询中统计或分租的字段（分租的前提是必排序）

不适合建立索引
	1.经常增删改的表，mysql不仅要保存数据，还要保存一下索引文件。
	2.数据重复的表字段：比如表中的某个一字段为省 市 性别，数据的差异率和重复率不高，建立索引没有太大意义。

总结：
	1、表的主键、外键必须有索引;
	2、数据量超过300w的表应该有索引；
	3、经常与其他表进行连接的表，在连接字段上应该建立索引；
	4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
	5、索引应该建在选择性高的字段上；
	6、索引应该建在小字段上，对于大的文本字段甚至超回长字段，不要建索引；
	7、频繁进行数据修改删除操作的表，不要建立太多的索引；
	8、删除无用的索引，避免对执行计划造成负面影响；

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建表直接指定索引
drop table if exists `role_permission`;
create table if not exists `role_permission`(
	rid int(10) not null,
	pid int(10) not null,
	UNIQUE `uk_rid_pid`(rid,pid)
);

查看表的索引
show index from table_name

创建索引
CREATE UNIQUE INDEX uk_rid_pid ON role_permission(rid, pid);

添加索引（修改表结构）
ALTER TABLE cm_dev ADD UNIQUE INDEX dev_id(dev_id)
ALTER TABLE role_permission ADD UNIQUE uq_rid_pid(rid, pid);

删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

普通索引

多字段唯一索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2)

外键约束+唯一索引约束实现多对多引用

地址：https://www.cnblogs.com/sienbo/p/11397214.html

--员工表
create table userinfo2(
    id int auto_increment primary key,
    name char(10) not null,
    gender char(10),
    )engine=innodb default charset=utf8;

--主机表
create table host(
    id int auto_increment primary key,
    hostname char(64) not null
    )engine=innodb default charset=utf8;

--员工主机关系表
create table user2host(
    id int auto_increment primary key,
    user_id int not null,
    host_id int not null,
    constraint fk_u2h_user foreign key (user_id) references userinfo2(id),
    constraint fk_u2h_host foreign key (host_id) references host(id),
    unique uq_user_host (user_id,host_id)
    )engine=innodb default charset=utf8;

查询所有外键及索引

mysql 查询指定数据库所有表, 指定表所有列，指定列所有表所有外键及索引，以及索引的创建和删除

查询指定数据库中所有表的外键

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE
    //注意指定约束类型, 是为了过滤掉 "PRIMARY KEY, UNIQUE"这两个索引
    CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND CONSTRAINT_SCHEMA = '数据库名'
    //如果只查询外键是以 fk_开头命名的 (如果你的外键命名以 fk_开头的话)
    AND CONSTRAINT_NAME LIKE 'fk_%';

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '数据库名'; //TABLE_SCHEMA, 表所在库
//NON_UNIQUE, 该索引能否包含重复, 1代表可以, 0代表不可以, 注意PRIMARY ,UNIQUE 为0, 
//用INDEX_NAME 字段将两者区分
//INDEX_NAME,  索引名
//COLUMN_NAME  表字段名,(表示此字段上加了索引)

第三部分优化篇

第15章 SQL优化

开发过程中，初期数据量小，写sql语句时更重视功能上的实现，但是当系统上线后，随着生产数据量的急剧增长，很多sql语句开始显露出性能问题，对生产的影响也越来越大，此时有些有问题的sql语句就成为整个系统性能瓶颈，因此我们必须进行优化。

15.1 优化sql语句的一般步骤

当面对一个有性能问题的数据库时，我们应从何处入手来进行系统的分析，使得能够尽快定位问题SQL并尽快解决。

优化分析

分析方法：通过慢查询分析、执行计划分析、索引使用统计，来确定索引使用情况[P133]

1.sql性能下降的表现

2.性能下降的几个原因：
（1）查询语句写不好：各种连接，各种子查询导致未使用也未建立索引
（2）索引失效：建立了索引，在真正执行时，没有用上建立的索引
（3）关联查询太多JOIN：JOIN表太多。
（4）服务器调优和参数配置错误：配置参数设置的比例不合适，导致性能下降，sql变慢

性能分析

表设计准则
1NF 数据库表得每一列都是不可分割的基本数据项，同一列中不能有多个值
第一范式（1NF）是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库
2NF 要求数据库表中的每个实例或行必须可以被唯一的区分，如设置主键
3NF 要求一个数据库表中不包含已在其他表中已包含的非主关键字信息

SQL的执行过程
1.客户端发送一条查询给mysql
2.服务器通过权限检查后，先去[缓存]中寻找，如果命中缓存，则返回缓存中的结果，否则解析器执行解析
3.[解析器]进行sql校验、解析、预处理，然后由[优化器]根据该sql所涉及到的数据表的统计信息进行计算，生成对应的执行计划；（预处理是通过索引把字段按顺序调节）
4.根据[查询执行计划]将sql交给[查询执行引擎]执行，执行引擎调用通过API调用[存储引擎InnoDB]来执行查询。
5.将结果缓存，同时返回给客户端


查询优化器
写的任何sql，到底是怎样真正执行，按照什么条件查询，最后执行的顺序，可能都会有多个执行方案
查询优化器根据对数据库表的统计信息（如索引，有多少条数据），在真正执行一条sql之前，会根据自己内部的数据，进行综合查询
根据mysql自身统计信息，从多种执行方案当中，选择一个它认为是最优的执行方案去执行


做优化，做什么？
1.让查询优化器按照我们的想法，选择最优的执行方案。
2.让优化器选择符合程序员计划的执行语句，来减少查询过程中产生的IO

15.1.1 通过show status命令了解各种SQL的执行频率

mysql> show status like ‘com_%’
…

15.1.2 定位执行效率较低的SQL语句

可以通过以下两种方式定位执行效率较低的SQL语句。

1.通过慢查询日志定位效率低的SQL
…

2. SHOW PROCESSLIST
使用show processlist命令查看当前Mysql在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。使用kill id，可以立即结束掉这个mysql链接的线程。

在Host列有来源IP和端口号，然后我们从连接机器查看端口号是谁的
[root@localhost ~]# netstat -ntp | grep 7437

MySQL> show processlist;
+----+------+--------------------+------+---------+-------+-------+------------------+
| Id | User | Host               | db   | Command | Time  | State | Info             |
+----+------+--------------------+------+---------+-------+-------+------------------+
|  1 | root | localhost          | NULL | Sleep   |    12 |       | NULL             |
|  2 | root | 192.168.100.1:7437 | test | Sleep   |  8035 |       | NULL             |
|  3 | root | 192.168.100.1:7438 | NULL | Sleep   | 24348 |       | NULL             |
|  5 | root | 192.168.100.1:7443 | NULL | Sleep   | 24317 |       | NULL             |
|  7 | root | 192.168.100.1:7450 | test | Sleep   | 24272 |       | NULL             |
|  9 | root | 192.168.100.1:5152 | test | Query   |     0 | init  | show processlist |
+----+------+--------------------+------+---------+-------+-------+------------------+
6 rows in set

各列的含义和用途：
id：一个标识，使用kill id，可以立即结束掉这个mysql链接的线程。
user：显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
host：显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。
db：显示这个进程目前连接的是哪个数据库。
command：显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）
Time：线程处在当前状态的时间，单位是秒
State：显示使用当前连接的sql语句的状态
Info: 线程执行的sql语句，如果没有语句执行则为null。
---------------------------------------------

-- 查询非 Sleep 状态的链接，按消耗时间倒序展示，自己加条件过滤
select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc

15.1.3 EXPLAIN分析低效率SQL的执行计划

通过EXPLAIN或者DESC命令获取Mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

使用explain关键字，可以模拟优化器执行的SQL语句，从而知道MySQL是如何处理sql语句；通过Explain可以分析查询语句或表结构的性能瓶颈。

作用：
	查看表的读取顺序
	数据读取操作的操作类型（是子查询还是普通的查询）
	查看哪些索引能够使用
	查看哪些索引被实际使用
	查看表之间的引用关系
	查看每张表有多少行被优化器执行

Explain sql 语句

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100310 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+

列说明：

【id】是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序；
id值的3种情况（总结：id相同顺序走；id不同看谁大；id大的先执行）
（1）id相同，执行顺序由上到下。
  Explain select * from employee e, dept d, customer c WHERE e.dept_id=d.id and e.cus_id = c.id
（2）id不同，如子查询，id的序号会递增，id值越大优先级越高，优先被执行。
  Explain select * FROM dept WHERE id=(select id from employee WHERE id=(select id from customer where id=1))
（3）相似从上往下顺序执行；id值越大，优先级越高，越先执行；derived是衍生出来的虚表
  Explain select * from dept d, (select * from employee group by dept_id) t WHERE d.id= t.dept_id

【select_type】表示SELECT类型。主要区别于普通查询，联合查询，子查询等复杂查询。
常见的取值有：
（1）SIMPLE（简单表，既不是用表连接或者子查询）
（2）PRIMARY：（主查询、即外层的查询）查询中若包含子查询，最外层查询则会被标记为primary
（3）SUBQUERY：（子查询中的第一个SELECT）等
（4）DERIVED：在from列表中包含的子查询被标记为derived；把结果放在临时表中
（5）UNION：（UNION中的第二个或者后面的查询语句）
（6）UNION RESULT：从union表获取结果select；两个union合并的结果集在最后
    
【table】输出结果集的表（显示这一行的数据是哪张表的）。

【partitions】如果查询是基于分区表的话，会显示查询访问的分区

type：表示Mysql在表中找到所需行的方式，或者叫访问类型（显示了连接使用了哪种类别,有无使用索引。）
访问类型排列，结果值从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL。
优化要求：保证查询至少达到range级别
常见类型：
（1）ALL：全表扫描，mysql遍历全表来找到匹配的行。
（2）index：索引全扫描，Mysql遍历整个索引来查询匹配的行。表示该语句是通过索引查询，影响行数roles=1。
（3）range：索引范围扫描，常见于<、<=、>、>=、between等操作符。检索给定范围的行，使用一个索引来选择行；key列显示使用了哪个索引；
（4）ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行。ref 非唯一性索引扫描，返回匹配某个单独值得所有行；可能会找到多个符合条件的行；它属于查找和扫描的混合体。
（5）eq_ref：唯一性索引扫描，对于每个索引键值，表中有一条记录匹配。常见于主键或唯一索引扫描。
（6）const/system：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中得其他列的值可以被优化器在当前查询中当作常量来处理。
system是const类型的特例。const 表示通过索引一次性就找到了，const用于比较primary或unique索引.直接查询主键或唯一索引，迅速匹配一行数据。
（7）NULL：Mysql不用访问表或者索引，直接就能够得到结果。

【possible_keys】表示查询时可能使用的索引。
【key】表示实际使用的索引。key与keys作用是查看是否使用了建立的索引，即判断索引是否失效；在建立多个索引时，MySQL最终用到了哪个索引。
【key_len】使用到索引字段的长度，可通过该列计算查询中使用的索引长度，这个值不一定准确，也泛指可能出现的最大值。
【rows】扫描行的数量。值越小越好。根据表统计信息及索引选用情况，大致估算出找到所需的记录及读取的行数。即每张表有多少行被优化器查询（扫描）过。
【filltered】满足查询的记录数量的百分比，不是具体记录数。filltered值越大越好，最好100%，filtered列的值依赖统计信息，并不十分准确。


【Extra】执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。 Extra内容一般如下：

（1）using index：覆盖索引，查询的字段和建立的字段刚好吻合，称为覆盖索引。
（2）Using filesort（必须优化项）：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序执行。Mysql中无法利用索引完成排序操作称为“文件排序”。
（3）Using temporary（优化项）：使用了临时表保存中间结果，mysql在对查询结果排序时使用了临时表。常见于排序order by和分组group by。
	例：Explain select name from employee where dept_id in(1,2,3) group by cus_id
（4）using where：使用了where语句。表示优化器需要通过索引回表查询数据。
（5）using join buffer（优化项）：使用了连接缓存
（6）impossible where（优化项）：表示where的子句总是false不能用来获取任何元组（如 where条件错误）
	explain select name from employee where name='张三' and name='李四'
（7）Using index condition 表示mysql使用了ICP来进一步优化查询，在检索时，把条件customer_id的过滤操作下推到存储引擎层来完成。这样能够降低不必要的IO访问。

写个测试（来自网络）

create table user (
  id   int(11) auto_increment,
  name varchar(100),
  age  int(11),
  primary key (id),
  key index_age(age),
  key index_name(name)
);
explain select * from user where name = 'tom'; -- use
explain select * from user where name in ('tom'); -- use
explain select * from user where name between 'tom' and 'jerry'; -- not use
explain select * from user where name <> 'tom'; -- not use
explain select * from user where name = 'tom' or name = 'jerry'; -- use
explain select * from user where name like 'om%'; -- use
explain select * from user where name like 'tom'; -- use, 这里的like相当于等号
explain select * from user where name like '%to'; -- not use

explain select * from user where age between 0 and 1; -- use
explain select * from user where age not between 0 and 1; -- not use
explain select * from user where age > 50; -- not use
explain select * from user where age < 50; -- not use
explain select * from user where age != 3; -- not use
explain select * from user where age in (1, 99); -- use
explain select * from user where age = 1 or age = 3; -- use
explain select * from user where age like '1%'; -- not use
explain select * from user where age like '%1'; -- not use

有时，仅仅通过explain分析执行计划并不能很快定位SQL的问题，这时还可以选择profile联合分析。

15.1.4 通过show profile分析SQL

通过have_profiling参数，查看mysql是否支持profile
mysql> select @@have_profiling
YES

默认profiling是关闭的，可以通过set语句在Session级别开启profiling
mysql> select @@profiling
1
通过profile，用户能更清楚地了解SQL执行的过程。例如，我们知道M有ISAM表有表元数据的缓存（例如行数，即count(*)值），对于InnoDB来说，就没有这种元数据缓存，count(*)执行得较慢。

首先，在一个InnoDB引擎的payment表上，执行一个count()查询。
mysql> select count(*) from payment;
…
执行完后通过show profiles看到当前SQL的Query ID为4。
mysql> show profiles
…
通过show profile for query 4 看到执行过程中线程的每个状态和消耗的时间。
mysql> show profile for query 4
…

注意：Sending data状态表示Mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，Mysql线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，Mysql支持进一步选择all、cpu、block io、context switch、page faults 等明细类型来查看Mysql在使用什么资源上耗费了过高的时间。
mysq> show profile block io, cpu for query 4;
…

show profile能够在做SQL优化时帮我们了解时间都耗费到那里了。而通过trace文件进一步向我们展示了优化器是如何选择执行计划的。

15.1.5 通过trace分析优化器如何选择执行计划

通过trace文件能进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。

15.1.6 确定问题并采取相应的优化措施

…

15.2 索引问题

通过索引通常可以帮助用户解决大多数的SQL性能问题。本节将讨论mysql中索引的分类、存储、和使用方法。

15.2.1 索引的存储分类

索引是在Mysql的存储引擎层中实现的，而不是在服务器层实现的。

B-Tree索引，最常见的索引类型，大部分引擎都支持B-Tree索引。

HASH索引：只有Memory/NDB引擎支持，使用场景简单。Hash索引适用于Key-value查询，通过Hash索引要比通过B-Tree索引查询更迅速。

R-Tree索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型。

Full-text：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引。

（1）前缀索引，即对列的前面某一部分进行索引。这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序Order by和分组Group by操作时无法使用。
mysql> create index idx_title on film(title(10))
（2）虚拟列索引，在Mysql5.7之后，可以通过创建虚拟列索引来实现函数索引的功能。

15.2.2 mysql如何使用索引

B-Tree索引能根据键值提供一行或一个行级的快速访问，通常只需要很少的读操作就可以找到正确的行。B-Tree索引是平衡树（balanced）。

B-Tree的结构：根节点root下有多个分支（Branch节点），Branch分支下面就是明细的叶子节点（Leaf叶子）。
可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。

1. mysql中能够使用索引的典型场景

（1）全值匹配

（2）范围查询（Match a range of values），对索引的值能够进行范围查找。

（3）最左匹配（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找。查询从最左前列开始，并且不跳过索引中的列。

（4）索引覆盖（Index only query），查询的列都在索引的字段中，查询的效率更高。
    Extra为Using index，不需要通过索引回表。

（5）匹配索引列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
    mysql> EXPLAIN select * FROM cm_dev WHERE dev_id like '2201%' 
    Extra值为 Using index condition
    
（6）能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）
    mysql> EXPLAIN select dev_id FROM cm_dev WHERE dev_id like '2201%' 
    Extra值为 Using where表示优化器需要通过索引回表查询数据。

（8）索引下推（Index Condition Pushdown ICP）某些操作情况下的条件过滤操作下放到存储引擎
	mysql> EXPLAIN select * FROM user WHERE name like 'zhang%' 
	Extra值为 Using index condition就表示mysql使用了ICP来进一步优化查询，在检索时，把条件name的过滤操作下推到存储引擎层来完成

ICP（优化器）尽可能的把index condition的处理从server层下推到storage engine层。
storage engine使用索引过过滤不相关的数据，仅返回符合index condition条件的数据给server层。
也是说数据过滤尽可能在storage engine层进行，而不是返回所有数据给server层，然后再根据where条件进行过滤。

2. 存在索引但不能使用索引的典型场景

索引失效

导致索引失效的情况：
（1）LIKE以通配符%开头索引失效变为全表扫描。查询时不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引。
（2）数据类型隐式转换，不会使用索引，特别是当列的类型是字符串，那么在where条件中把字符串常量值使用引号，否则即便这个列上有索引，MySQL也不会用到。（如：字符串不加引号索引失效）
（3）复合索引的情况下，若查询条件不包含索引列最左边部分，若不满足最左匹配原则Leftmost，不会使用复合索引的。
（4）若使用索引比全表扫描更慢，则不使用。
（5）用or分割开的条件，如果or前的条件列有索引，后面的列没有索引，则涉及的索引都不会被用到。在存在全表扫描的情况下，就没有必要对一次索引扫描增加IO访问，一次全表扫描过滤条件就够了。
（6）is null或is not null无法使用索引
（7）mysql在使用不等于(!=或<>)无法使用索引导致全表扫描。

总结：
尽量使用全值匹配。
不要在索引列上做其他操作,如:计算、函数、类型转换，会导致索引失效而转向全表扫描
范围条件，右边的索引会失效
where查询条件从索引最左列开始，并且不能跳过索引中的列。如：跳过第一个，索引失效；跳过前两个，索引失效；跳过中间一个，只有第一个生效。三个索引的顺序可以乱。

15.2.3 查看索引使用情况

如果索引的正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rmd_next的值越高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正大量的表扫描，Hankler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

mysql> show status like 'handler_read%'
Variable_name	Value
Handler_read_first	0
Handler_read_key	1113
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	160
Handler_read_rnd_next	2265

15.3 两个简单实用的方法

15.3.1 定期分析表和检查表

分析表的语法如下：
mysql> analyze table table_name;
mysql> check table table_name;
检查表的作用是检查一个或多个表是否有错误。check table 也可以检查试图是否有错误。

15.3.2 定期优化表

mysql> optimize table table_name;
delete操作后使用optimize table table_name 释放磁盘空间，优化表期间会锁表。optimize命令优化表不要频繁操作

开启 --skip-new
删除数据后释放磁盘空间
drop table table_name 立刻释放磁盘空间，不管是 Innodb和MyISAM ；
truncate table table_name立刻释放磁盘空间，不管是 Innodb和MyISAM;
delete from table_name 删除表的全部数据，对于MyISAM 会立刻释放磁盘空间，而InnoDB 不会释放磁盘空间；
delete from table_name where xx 带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间；

15.4 常用SQL的优化

对INSERT、GROUP BY、ORDER BY等的优化。

15.4.1 大批量插入数据

mysql插入数据的三种方式：1.INSERT INTO；2.REPLACE INTO；3.INSERT IGNORE

15.4.2 优化INSERT语句

15.4.1 优化ORDER BY

优化order by语句之前，首先来了解下MySQL中的排序方式。

#ORDER BY 出现 Using filesort的情况：

1.索引作为排序时，where子条件中的索引字段不会同时使用到order by中
2.索引常量进行大小计算，导致索引断开
3.使用order by排序时，如果没有按照索引顺序，会出现Using filesort
4.用select * 时，order by即使使用了全部索引，也会出现Using filesort
5.排序的两个字段出现一升一降

1. Mysql两种排序方式 236

第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析时值为Using Index，不需要额外排序，操作效率较高。

第二种是通过对返回数据进行排序，即Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于Mysql服务器对排序参数的设置和需要排序数据的大小。

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区种进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以同一时刻Mysql种存在多个sort buffer排序区。

了解了Mysql排序的方式，优化目标就清晰了，尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或都是降序（不要一升一降），否则需要额外的排序操作会出现Filesort。

2. Filesort的优化

通过创建合适的索引能减少Filesort出现，但是在某些情况下，条件限制不能让Filesort消时，那就需要加快Filesort的操作。对于Filesort，MySQL有两种排序算法。

两次扫描算法（Two Passes）

首先根据条件取出排序字段和行指针信息，之后在排序区sort buffer种排序。若排序区sort buffer不够，则在临时表Temporary Table种存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL4.1之前采用的算法，需要两次访问数据库，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机IO操作；优点是排序时内存开销较少。

一次扫描算法（Single Passes）

一次性取出满足条件的行的所有字段，然后再排序区sort buffer 中排序后直接输出结果集。排序时内存开销比较大，但排序效率比两次扫描算法要高。

MySQL通过比较相同变量max_length_for_sort_data的大小和Query语句去除的字段总大小来判断使用哪种排序算法。如果max_length_for_sort_data更大，则使用第二种优化之后的算法；否则使用第一种算法。

适当加大系统变量max_length_for_sort_data的值，能够让Mysql选择更优化的Filesort排序算法。但max_length_for_sort_data设置过大，会造成CPU利用率过低和磁盘IO过高。

适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。

SELECT *

尽量使用SELECT 具体的字段名称，而不是 SELECT * 所有字段，这样可以减少排序区的使用，提高SQL性能。

15.4.4 优化 GROUP BY

默认情况下MySQL对所有GROUP BY col1,col2, …的字段进行排序。这与在查询中指定ORDER BY col1,col2…类似。因此，如果显示包括一个包含相同列的ORDER BY子句，则对Mysql实际执行性能没有什么影响。

注：使用group by 排序时，如果未按索引顺序，不仅会出现Using filesort，同时也会出现Using temporary临时表
explain select * from employee group by name

ORDRE BY NULL

如果查询包括GROUP BY 但用户想要避免排序结果的消耗，则可以指定ORDRE BY NULL 禁止排序

15.4.5 优化 JOIN 操作

15.4.6 优化嵌套查询

15.4.7 优化 OR 条件

15.4.8 优化分页查询

一个常见又非常头痛的分页场景是“limit 1000,20”，此时Mysql排序出前1020条记录后仅仅返回第1001到1020这20条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

1.第一种优化思路
在索引上完成排序分页的操作，最后根据主键关联返回原表查询所需要的其他列内容。

2.第二种优化思路
last_page_record参数，用来记录上一页最后一行的编号1000。那么在翻到下一页查询时，可以根据这个最后记录编号向后追溯20条进行查询。

1.使用子查询优化（把最耗时的查询走索引）
select * from employee where id >=(select id from employee limit 500000,1) limit 10
select * from employee e inner join (select id from employee limit 500000, 10) et ON e.id = et.id

2.使用id限定优化（麻烦一点，但效率比第1个高很多）
传递上一页最大值的id，使用范围查询
限定是只能使用于明确知道id的情况，一般建立表时，都会添加id字段
select * from employee where id >= 1000001 limit 100
select * from employee where id between 1000000 and 1000100 limit 100

3.限制分页数据的最大值，如只允许查询到500页（优化思路）

其他优化

#最大值优化max(age)
explain select max(age) from employee #100W条数据查询6s
添加索引后，rows=null，说明查询时默认通过优化器的统计信息中获取

#COUNT()使用注意点
select count(*) FROM employee #统计所有，包含null #结果9条
select count(name) FROM employee #统计所有，但不包含null #结果8条

#小表驱动大表：
即小的数据集驱动大的数据集
查询时类似循环嵌套，小的在外层循环，如连接数据库5次，内部操作次数1000次；防止相反操作浪费资源，增加消耗

#in与exits
使用IN(大表驱动小表)
select * from employee where id IN (select id from department)
相当于
for select * from dept d 
  for select * from emp e where e.dept_id = d.id

#exists可以替代in
使用Exists(小表驱动大表)
select * from employee e where EXISTS(select 1 from dept d where d.id=e.dept_id)
相当于
for select * from emp e
  for select * from dept d where d.id=e.dept_id

#in与exists选择
1.当A表数据多于B表，使用IN优先于EXISTS
2.当B表数据多余A表，使用Exists优先于IN
3.如果两张表数据差不多，使用in或exists差不多
4.EXISTS子查询只返回TRUE或FALSE，因此子查询中的select * 可以是select 1 或者其他

15.4.9 使用SQL提示248

15.5 直方图

15.6 使用查询重写

15.7 常用SQL技巧

15.7.5 数据库名、表名大小写问题

15.7.6 使用外键需要注意的问题

…

第16章锁

16.1 MySQL锁概述 267

16.2.3 如何加表锁 269

16.2.4 并发插入（Concurrent Inserts） 271

16.3 InnoDB锁问题 273

16.3.3 InnoDB的行锁模式及加锁方法

共享锁（S）
排他锁（X）
…

16.3.9 关于死锁

…

第17章优化MySQL Server

17.2 MySQL内存管理及优化

[root@localhost~]#  free -h
              total        used        free      shared  buff/cache   available
Mem:          7.6Gi       3.7Gi       215Mi       158Mi       3.7Gi       3.5Gi
Swap:            0B          0B          0B

total: 内存总数
used: 已经使用内存数
free: 完全空闲内存
shared: 多个进程共享的内存
buffers: 用于块设备数据缓冲，记录文件系统metadata（目录，权限，属性等)
cached: 用于文件内容的缓冲
available：真正剩余的可被程序应用的内存数

17.2.1 内存优化原则

17.2.3 InnoDB内存优化

1.InnoDB缓存机制

17.4 调整MySQL并发相关的参数

第18章磁盘IO问题

第19章应用优化

19.1 优化数据表的设计

19.1.1 优化表的数据类型

19.1.2 通过拆分提高表的访问效率

19.2 数据库应用优化

19.2.1 使用连接池

19.2.3 负载均衡

第20章 PS/SYS 数据库

第四部分管理维护篇

第五部分架构篇

略

Mysql 查看系统连接数,状态

# 查看默认最大连接数
mysql > show variables like '%max_connections%'; 
Variable_name	Value
max_connections	214

# 查看当前连接状态
mysql > show status like 'Threads%';
Variable_name	Value
Threads_cached	0
Threads_connected	106
Threads_created	1617
Threads_running	6

# 查看这次mysql服务启动到现在，同一时刻最多有多少连接。
SHOW STATUS LIKE 'MAX_USED_CONNECTIONS';

#调整max_connections参数的值
mysql> set GLOBAL max_connections=1000;

#查看文件打开数量
show global status like 'open%';
show global variables like '%open%';

问题

数据库表被损坏Error Code: 145

报错：Error Code: 145. Table ‘./mall/ecs_wechat_msg_log’ is marked as crashed and should be repaired

解决：执行语句 repair table ecs_wechat_msg_log;

错误产生原因，有网友说是频繁查询和更新dede_archives表造成的索引错误，因为我的页面没有静态生成.
MYSQL数据库因为某种原因而受到了损坏，如：数据库服务器突发性的断电、在提在数据库表提供服务时对表的原文件进行某种操作都有可能导致MYSQL数据库表被损坏而无法读取数据。
总之就是因为某些不可测的问题造成表的损坏。