Index
MySQL索引的基本操作
CREATE INDEX idx_price on OrderItems(item_price);
ALTER TABLE OrderItems DROP INDEX idx_order_num_price;Explain
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了。
Explain各列的含义如下:
id: 每个 SELECT 都会自动分配一个唯一的标识符
select_type: SELECT 查询的类型
table: 查询的是哪个表
partitions: 匹配的分区
type: 访问类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行 这个是一个估计值
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息其中Type是重点关注的字段，用以快速评价查询语句性能，常见type性能从差到好如下：
All < Index < Range < Ref < ConstDemo
以OrderItems表为例：
| OrderItems | CREATE TABLE `OrderItems` (
`order_num` int(11) NOT NULL,
`order_item` int(11) NOT NULL,
`prod_id` char(10) NOT NULL,
`quantity` int(11) NOT NULL,
`item_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`order_num`,`order_item`),
KEY `FK_OrderItems_Products` (`prod_id`),
KEY `idx_price` (`item_price`),
KEY `idx_order_num_price` (`order_num`,`item_price`),
CONSTRAINT `FK_OrderItems_Orders` FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`),
CONSTRAINT `FK_OrderItems_Products` FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> select * from OrderItems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
+-----------+------------+---------+----------+------------+
18 rows in set (0.03 sec)举例说明几个Explain type的查询：
all，直接全表查询
mysql> explain select item_price from OrderItems; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | OrderItems | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
index，利用索引
mysql> explain select order_num from OrderItems; +----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | OrderItems | NULL | index | NULL | FK_OrderItems_Products | 30 | NULL | 18 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
range，对索引进行范围查询，多见于between/in/<>等关键字
mysql> explain select order_num from OrderItems where order_num between 20003 and 20005; +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | OrderItems | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
ref，使用普通索引
mysql> explain select order_num from OrderItems where order_num = 20005; +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY | PRIMARY | 4 | const | 2 | 100.00 | Using index | +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数。
Optimization
通过分析Explain语句结果可以优化查询性能。一般关注点有：一是type往好的方向优化，二是有order by时，尽量不要在extra中出现Using filesort。
type优化，如 阿里巴巴Java编程规范 中定义的那样，一般不允许all和index的查询，会极大影响性能。尽量优化至range以上。方法一般就是建索引，不要为了节省插入性能而去缩减必要的索引。
mysql> CREATE INDEX idx_price on OrderItems(item_price);然后再执行关于item_price的查询：
mysql> explain select order_num from OrderItems where item_price between 5 and 8; +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | OrderItems | NULL | range | idx_price | idx_price | 4 | NULL | 2 | 100.00 | Using where; Using index | +----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
看到type位置变为range，possible_keys和key的位置出现了我们新建的索引
去除Using filesort
执行下列查询
mysql> explain select * from OrderItems where order_num = 20003 order by item_price; +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+ | 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using filesort | +----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
使用了fileSort。我们建立索引并再次执行查询如下：
mysql> CREATE INDEX idx_order_num_price on OrderItems(order_num, item_price); mysql> explain select * from OrderItems where order_num = 20003 order by item_price; +----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY,idx_order_num_price | idx_order_num_price | 4 | const | 1 | 100.00 | Using index condition | +----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)
如果建立了多重索引A-B(A和B是column name)，那么查询语句的where clause中仅使用了A也是可以利用该A-B索引的。事实上，只要查询条件从左至右依次匹配某索引，都是可以利用的。
Problems
另外遇到两个关于索引有序性使用的小坑。
查询语句中的字段匹配索引的前半部分，但如果它们是用于in/between，索引失效
比如在建立了order_num/item_price索引的情况下，还是会fileSort：
mysql> explain select order_num from OrderItems where order_num between 20003 and 20005 order by item_price;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+单一Order时使用索引不分升降序，但如果对多字段排序，则要求索引顺序和查询语句’一致’
mysql> explain select item_price from OrderItems ORDER BY order_num, item_price;
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_order_num_price | 8 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select item_price from OrderItems ORDER BY order_num, item_price desc;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_price | 4 | NULL | 18 | 100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select item_price from OrderItems ORDER BY order_num desc, item_price desc;
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_order_num_price | 8 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)如果理解了MySQL索引的物理实现（B+ Tree)，这些应该就比较容易理解了（TODO）。
原文 https://opticalix.github.io/2019/08/09/mysql-explain/
node如何和MySQL进行通信
Node.js与MySQL交互操作有很多库,常用最多的是mysql模块，mysql数注意：安装前先把目录cd到node.exe所在目录下，这样执行安装命令时，这篇文章主要介绍：链接mysql的流程、数据库连接参数说明、MYSQL CURD操作、连接池Pooling connections、断线重连、防止SQL注入
MySQL 常用30种SQL查询语句优化方法
MySQL查询语句优化方法:应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
一个案例彻底弄懂如何正确使用 mysql inndb 联合索引
有一个业务是查询最新审核的5条数据,查看当时的监控情况 cpu 使用率是超过了100%，show processlist看到很多类似的查询都是处于create sort index的状态。索引有一个audit_time在左边的联合索引，没有关于status的索引。
mysql数据库使用insert语句插入中文数据报错
在mysql的命令行模式中，通过insert语句插入中文数据的时候报错，类似于下面这样，造成这个错误通常是由于创建数据表的时候使用了不正确的编码格式,可以使用如下命令查看操作的目标数据表的编码格式。
MySQL技术内幕：InnoDB存储引擎
本文绝大部分内容来源《MySQL技术内幕：InnoDB存储引擎》一书。InnoDB存储引擎是多线程模型，其后台有多个不同的后台线程，负责处理不同的任务。
MYSQL中视图的使用
什么是视图?执行一条SQL，将结果集保存在一张虚拟表中。在视图创建后，可以用与表基本相同的方式使用（查询、过滤、排序数据、与其他视图或连结、（添加、更新））视图只是用来查看存储在别处的数据的设施，本身不包含数据，返回的数据也是从其他表检索出来的。
Mysql常用函数
MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。
mysql innodb索引原理
innodb存储引擎表是索引组织表，表中数据按照主键顺序存放。其聚集索引就是按照每张表的主键顺序构造一颗B+树，其叶子结点中存放的就是整张表的行记录数据，这些叶子节点成为数据页。
一条sql语句在mysql中是如何执行的
最近开始在学习mysql相关知识，自己根据学到的知识点，根据自己的理解整理分享出来，本篇文章会分析下一个sql语句在mysql中的执行流程，包括sql的查询在mysql内部会怎么流转，sql语句的更新是怎么完成的。
mysql统计行数时到底应该怎么count
每个人在写代码时都有遇到过要获取MYSQL表里数据行数的情况，多数人获取数据表行数时都用COUNT(*)，但同时也流传了不少其他方式，比如说COUNT(1)、COUNT(主键)、COUNT(字段)。
内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权或违规,请与小编联系!情况属实本人将予以删除!