MySQL
1 SQL应用
1.1 DCL数据控制语言
DCL主要负责授权与撤权
grant
revoke
1.2 DML数据操纵语言
1.2.1 insert
尽量批量插入，减少发起的事务。
1.2.2 update
建议：
- 将需要更新的数据的主键先查出来，然后按主键更新，这样做可以减少锁的征用，更加高效
- 如果无法达到以上需求，对于where条件后的列，需要设置合理索引
- 减少更新范围
- 尽量不要跨多表更新
1.2.3 delete
建议：
- 可以使用"伪删除"，即增加一列表示是否删除，然后定期归档
- 批量删除，希望减少对业务的影响，推荐使用pt-archiver
- delete操作比较频繁，建议定期做碎片整理(innodb)
alter table t1 engine=innodb;
delete \ truncate \ drop 区别
表面区别：删除后主键是否重置。
delete
逐行进行删除，删除时为数据打上delete标签，数据仍在磁盘中存在；
truncate
删除时直接清空数据页，全部重置，删除速度快且彻底，很难回复；
drop
删除时会清空数据页和表空间，也会清空表定义，删除更加彻底。
1.2.4 select
语句排序
select select_list from where group by having order by limit
where条件可使用
比较操作符：= > < >= <= <>
逻辑连接符：and or
模糊查询：like
其它：in not in beteween and
group by + 聚合函数（sum, count, avg, max, min, group_concat）
5.7版本以后，SQL_MODE
SQL_MODE=only_full_group_by
- SQL_MODE 作用
约束SQL行为，保证数据有意义。
mysql> select @@SQL_MODE;
+-----------------------------------------------------------------------------------------------------------------------+
| @@SQL_MODE |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- only_full_group_by
select后面的字段必须出现在group by后面，或者被聚合函数包裹，否则会报错。
group by执行时会取出其后面的字段数据，进行排序，去重，再执行其它操作(聚合函数等)。
mysql> select countrycode, count(name), name from city group by countrycode;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方法，使用group_concat将各组内的指定字段数据转成一行显示。
mysql> select countrycode, count(name), group_concat(name) from city group by countrycode;
1.3 元数据获取
元数据一般指的是表的属性状态等。
mysql> use information_schema;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEYWORDS |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| TABLES |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TABLES_EXTENSIONS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEWS |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
+---------------------------------------+
78 rows in set (0.00 sec)
1.3.1 统计业务数据库库名和表名
TABLES表保存了数据库中所有的表信息。
查看所有库及其下的所有表。
mysql> select table_schema, table_name from information_schema.tables;
查看业务库
mysql> select table_schema, table_name from information_schema.tables where table_schema not in ('mysql', 'sys', 'information_schema', 'performance_schema');
+--------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+-----------------+
| test | t100w |
| world | city |
| world | country |
| world | countrylanguage |
+--------------+-----------------+
4 rows in set (0.00 sec)
1.3.2 统计系统中所有非InnoDB表
mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine != 'innodb';
1.3.3 将所有非InnoDB表转换成InnoDB表
mysql> alter table world.t1 engine=innodb;
批量操作，将结果输出到/tmp/alter.sql。
使用concat拼接SQL语句。
mysql> select concat("alter table ", table_schema, ".", table_name, " engine=innodb;") from information_schema.tables where table_schema not in ('mysql', 'sys', 'information_schema', 'performance_schema') and engine != 'innodb' into outfile '/tmp/alter.sql';
报错：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方法，配置文件中将/tmp设定为安全路径。
cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
secure-file-priv=/tmp
[mysql]
socket=/tmp/mysql.sock
修改后重启数据库
/etc/init.d/mysqld restart
cat /tmp/alter.sql
source /tmp/alter.sql
1.3.4 统计每个业务库的数据量
索引占用空间 + 数据占用空间
mysql> select table_schema, sum(INDEX_LENGTH+DATA_LENGTH)/1024 from information_schema.tables where table_schema not in ('mysql', 'sys', 'information_schema', 'performance_schema') group by table_schema;
+--------------+------------------------------------+
| TABLE_SCHEMA | sum(INDEX_LENGTH+DATA_LENGTH)/1024 |
+--------------+------------------------------------+
| world | 784.0000 |
| test | 48720.0000 |
+--------------+------------------------------------+
2 rows in set (0.01 sec)
1.3.5 数据字典信息统计
查看表的列信息。
mysql> select table_schema, table_name, group_concat(column_name) from information_schema.columns where table_schema not in ('mysql', 'sys', 'informmation_schema', 'performance_schema') group by table_schema, table_name;
2 索引及执行计划
2.1 介绍
索引相当于一本书中的目录，可以加速查询。
MySQL中的索引类型
-
Btree 平衡多叉树（btree\b+tree\b++tree），适合用于范围查找。
Btree 平衡多叉树查找算法的本质是二分法。 -
HASH 散列索引，适合用于等值查询。
-
RTREE R树
-
Fulltext 全文索引
2.2 Btree查找算法
Btree查找算法可以简单理解为是基于平衡多叉树理念的二分查找法。
2.2.1 Btree
将所有数据行均匀地存储于固定大小的存储区域中，即数据页(page)，这些区域是树的叶子节点。
根据叶子节点生成中间节点，图中的方案是将叶子节点中的第一个数据(范围+指针)放入中间节点，即中间节点存储其下各个叶子节点的最小数据。
根据中间节点生成根节点，根节点存储其下各个中间节点的最小数据(范围+指针)。
B树的重点是平衡，即查找任何数据所需的时间复杂度相同，经历的步骤取决于树的层数。
2.2.2 B+tree
与B树相比，B+树在叶子节点中添加了双向指针，适合范围查找。
如果需要查找一定范围的值，可以向找到范围边界的叶子节点，再利用双向指针逐个找到所需的叶子节点。
2.2.3 B++tree
B++树(或B*树)，是在B+树的基础上，在中间节点(分支节点)上添加双向指针。
2.3 索引介绍
2.3.1 索引分类
2.3.1.1 聚簇索引
聚簇索引，Clustered Index
extent 区、簇：连续的 64个 pages(16KB)，默认大小为1MB(64*16KB)。
IOT表：索引组织表，就是按照聚簇索引将表中数据组织存储在多个区中。
生成前提：
- 如果建表时显式地设置了主键(PK)，主键列为聚簇索引结构，组织存储数据。
- 如果没有主键，自动按照第一个不为空的UK(唯一键)作为聚簇索引，组织存储数据。
- 如果都不满足，会生成一个6字节的隐藏ROW_ID，作为聚簇索引，组织存储数据。
一般建议建表时，需要显式地设置主键。
生成节点过程
叶子节点：原表数据所在的数据页，就是聚簇索引的叶子节点
枝节点：存储下层叶子节点id的范围+指针
根节点：存储下层枝节点id的范围+指针
2.3.1.2 辅助索引
辅助索引是用于普通列的索引构建。
生成节点过程
叶子节点： 提取辅助索引列+ID主键值，根据辅助索引列排序，生成叶子节点。
枝节点：提取叶子结点辅助索引列值范围+指针
根节点：提取枝结点辅助索引列值范围+指针
按照辅助索引列条件查询时，首先扫描辅助索引树，得到ID值，然后回到聚簇索引扫描，中查找数据据行。
回表，回到聚簇索引查找的动作被称之为回表查询。
2.3.2 索引树高度
一般不建议超过4层。
索引树高度的影响因素
- 数据行太多，例如达到2000w+
一般将索引树高度控制在3-4层。
处理方案：分表、归档、分库分表 - 列值长度
对于聚簇索引，一般使用自增的ID列作为索引，如果ID列的数据类型是BigInt，可以存储20位数字。
对于辅助索引，列值长度可能各不相同。
处理方案：前缀索引，只使用前15-20位数据作为索引的构建条件。 - 数据类型
推荐使用可变长度的数据类型，例如对于char和varchar，推荐使用varchar。
处理方案：选择简短的、合适的数据类型。
2.3.3 回表问题
回表产生的问题：IO次数、IO吞吐量、随机IO
减少回表
- 不回表
如果需要查询的值都在辅助索引里有，就不需要回表了。 - 少量回表
通过合适的联合索引，精细化查询条件。
使用数量级达到100万的数据进行压力测试，评估索引效果。
2.3.4 压力测试
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int | YES | | NULL | |
| num | int | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | char(4) | YES | | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
表没有建立任何索引。
提前压测
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
--concurrency=100 模拟100名用户登录
--number-of-queries=2000 指定命令执行2000次
2.4 索引管理
查询索引
mysql> desc city;
mysql> show index from city;
创建索引
mysql> ALTER TABLE city ADD INDEX i_name(name)
mysql> ALTER TABLE city DROP INDEX i_name;
基础管理
# 创建前缀索引，name列前10个字符
mysql> ALTER TABLE city ADD INDEX i_name(name(10));
# 创建联合索引
mysql> ALTER TABLE city ADD INDEX ix(population, countrycode);
# 删除索引
mysql> ALTER TABLE city DROP INDEX i_name;
# 查看索引
mysql> show index from city;
2.5 执行计划
2.5.1 介绍
优化器会根据解析器获得的解析树，计算查询代价，做出评估并决定执行方案。
查看优化器生成的最终执行计划，有助于判断语句执行效率低，速度慢的原因，评估SQL执行会不会有问题。
2.5.2 操作
mysql> desc select * from test.t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from test.t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
2.5.3 分析
table：语句操作的表
type：判断语句获取数据的方式，包括ALL、index、range、ref、eq_ref、const(system)
ALL：全表扫描
index：全索引扫描，主键列上的全索引扫描相当于全表扫描
range：索引范围扫描，包括：> < >= <= like or in between and，例外情况：使用主键做不等值查询
ref：辅助索引常量查询，即等值查询 =
eq_ref：多表连接中在非驱动表上的关联条件是主键或唯一键。
const(system)：主键或者唯一键的常量查询(等值查询)。
possible_keys：可能使用的索引
key：最终选择使用的索引
rows：需要扫描的行数
Extra：额外信息，注意额外排序using filesort，order by、group by等会触发排序
2.6 联合索引应用细节
2.6.1 联合索引遵循“最左原则”
-
建立联合索引时，要选择重复值少的列放在最左侧；
重复值少的列，选择度高。 -
查询条件中必须要包含最左列条件，才能使用联合索引。
2.6.2 辅助索引覆盖长度
全部覆盖
查询条件中只要包含最左列条件，无论其顺位，MySQL都会将其自动转化为最优的执行计划对应的排序。
尽量避免条件中间出现不等值查询。
idx(a, b, c );
select * from t1 where a=? and b=? and c=?
select * from t1 where b=? and a=? and c=?
select * from t1 where c=? and b=? and a=?
select * from t1 where c=? and a=? and b=?
select * from t1 where a=? and b=? and c>?
部分覆盖
select * from t1 where a=? and b=?
select * from t1 where a=? and c=?
select * from t1 where b=? and a=?
select * from t1 where c=? and a=?
select * from t1 where a=? and b>? and c=?
select * from t1 where a>? and b=? and c=?
不覆盖
select * from t1 where b=? and c=?
可以通过参数format=json，查看 "used_key_parts"来获取使用了哪些索引。
mysql> desc format=json select * from t100w where num=279106 and k1 >'dd' and k2='VWtu';
2.7 建立索引的原则（DBA运维规范）
(1) 必须要有主键，无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引
(4) 列值长度较长的索引列，建议使用前缀索引.
(5) 降低索引条目，一方面不要创建没用索引，不常使用的索引清理，percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc
(7) 联合索引最左原则
2.8 不走索引的情况（开发规范）
(1) 没有查询条件，或者查询条件没有建立索引
(2) 查询结果集是原表中的大部分数据，应该是15-25%以上
(3) 索引本身失效，统计信息不真实（过旧）
现象：有一条select语句平常查询时很快，突然有一天很慢，会是什么原因
select ---> 索引失效，统计数据不真实
innodb_index_stats
innodb_table_stats
mysql> ANALYZE TABLE world.city;
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询
(5) 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
(6) <> ，not in 不走索引（辅助索引）
(7) like “%_” 百分号在最前面不走
403
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
