MySQL 性能优化的 9 种姿势 MySQL 性能优化的 9 种姿势1、选择最合适的字段属性Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。例如：在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。2、尽量把字段设置为NOT NULL在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。3、使用连接(JOIN)来代替子查询(Sub-Queries)MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如：我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询，如下图所示：连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。另外，如果你的应用程序有很多JOIN查询，你应该确认两个表中JOIN的字段是被建立过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。而且这些被用来JOIN的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和一个INT字段JOIN在一起，MySQL就无法使用他们的索引。对于那些STRING类型，还需要有相同的字符集才行。(两个表的字符集可能不相同)。inner join内连接也叫做等值连接，left/right join是外链接。SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id; SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id; SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id; 经过多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id; sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。例如我们有两张表：Orders表通过外键Id_P和Persons表进行关联。inner join（内连接），在两张表进行连接查询时，只保留两张表中完全匹配的结果集。我们使用inner join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p INNER JOIN Orders o ON p.Id_P=o.Id_P and 1=1  --用and连接多个条件 ORDER BY p.LastName 查询结果集：此种连接方式Orders表中Id_P字段在Persons表中找不到匹配的，则不会列出来。注意：单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了，此时就等价于：select * from a inner join b on a.id = b.id。 -- 即就是内连接。 但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。内连接查询 (select * from a join b on a.id = b.id) 与 关联查询 (select * from a , b where a.id = b.id)的区别left join,在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。我们使用left join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p LEFT JOIN Orders o ON p.Id_P=o.Id_P ORDER BY p.LastName 查询结果如下：可以看到，左表（Persons表）中LastName为Bush的行的Id_P字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。right join,在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。我们使用right join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p RIGHT JOIN Orders o ON p.Id_P=o.Id_P ORDER BY p.LastName 查询结果如下：Orders表中最后一条记录Id_P字段值为65，在左表中没有记录与之匹配，但依然保留。full join,在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。我们使用full join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p FULL JOIN Orders o ON p.Id_P=o.Id_P ORDER BY p.LastName 查询结果如下：查询结果是left join和right join的并集。4、使用联合(UNION)来代替手动创建的临时表MySQL从4.0版本开始支持union查询，他可以把需要使用临时表的两条或更多的select查询合在一个查询中。在客户端查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用union作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。下面一个例子就演示了一个使用union额查询。当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候尽量使用union all而不是union，因为union和union all的差异主要是前者需要将两个或者多个结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，增大资源消耗及延迟。5、事务尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。BEGIN;  INSERTINTOsalesinfoSETCustomerID=14; UPDATEinventorySETQuantity=11WHEREitem='book'; COMMIT; 事务的另一个作用是当多个用户同时使用相同的数据源时，他可以使用锁定数据库的方式来为用户提供一种安全的访问机制，这样可以保证用户的操作不被其它的用户所干扰。一般来说，事务必须满足四个条件(ACID)：原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability).原子性：一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执行过一样。一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。事务的并发问题：1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据2、不可重复读：事务A多次读取同一事物，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。3、幻读：系统管理员A将数据库中的所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表MySQL事务隔离级别事务控制语句：BEGIN或START TRANSACTION：显式的开启一个事物。COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的。Rollback：也可以使用Rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有很多个SAVEPOINT；RELEASE SAVEPOINT identifier：删除一个事物的保存点,当没有指定的保存点时，执行该语句会抛出一个异常。ROLLBACK TO inditifier：把事务回滚到标记点。SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。6、使用外键锁定表的方法可以维护数据的完整性，但是他却不能保证数据的关联性。这个时候我们可以使用外键。例如：外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.CREATE TABLE customerinfo(customerid int primary key) engine = innodb; CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb; 注意例子中的参数“on delete cascade”.该参数保证当customerinfo表中的一条客户记录也会被自动删除。如果要在mysql中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是mysql表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=innoDB。你还在到处找面试题，点击这个面试库进行刷题，各类面试题太齐全了。7、锁定表尽管事务是维护数据库完整性的一个非常好的方法，但却因为他的独占性，有时会影响数据库的性能，尤其是很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方式来获得更好的性能。下面的例子就是锁定表的方法来完成前面一个例子中事务的功能。这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其他访问来对inventory进行插入、更新或者删除的操作。8、使用索引索引是提高数据库性能的常用方法，他可以令数据库服务器比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对那些字段进行索引呢？一般来说，索引应该建立在那些将用于join，where判断和orderby排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引，对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。9、优化de的查询语句不使用子查询例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id; 但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询避免函数索引例：SELECT * FROM t WHERE YEAR(d) >= 2016; 由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。应改为—–>SELECT * FROM t WHERE d >= ‘2016-01-01’; 用IN来替换OR低效查询SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30; —–\> 高效查询SELECT * FROM t WHERE LOC_IN IN (10,20,30); LIKE双百分号无法使用到索引SELECT * FROM t WHERE name LIKE ‘%de%’; —–>SELECT * FROM t WHERE name LIKE ‘de%’; 目前只有MySQL5.7支持全文索引（支持中文）读取适当的记录LIMIT M,NSELECT * FROM t WHERE 1; —–>SELECT * FROM t WHERE 1 LIMIT 10; 避免数据类型不一致SELECT * FROM t WHERE id = ’19’; —–>SELECT * FROM t WHERE id = 19; 分组统计可以禁止排序SELECT goods_id,count(*) FROM t GROUP BY goods_id; 默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。另外，MySQL 系列面试题和答案全部整理好了，微信搜索民工哥技术之路，可以查看MySQL企业面试题，在线阅读。—–>SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL; 避免随机取记录SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4; MySQL不支持函数索引，会导致全表扫描 —–>SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4; 禁止不必要的ORDER BY排序SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC; —–>SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id; 批量INSERT插入INSERT INTO t (id, name) VALUES(1,’Bea’); INSERT INTO t (id, name) VALUES(2,’Belle’); INSERT INTO t (id, name) VALUES(3,’Bernice’); —–>INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’); 链接：blog.csdn.net/weixin_42047611/article/details/81772149

MySQL太慢？试试这些诊断思路和工具 MySQL太慢？试试这些诊断思路和工具作者 | 黄炎原文｜http://www.sohu.com/a/249994941_683048如果遇到 MySQL 慢的话，你的第一印象是什么，如果MySQL 数据库性能不行，你是如何处理的？MySQL 慢怎么办如果遇到 MySQL 慢的话，你的第一印象是什么，MySQL 数据库如果性能不行，你是如何处理的？我咨询了一些同行, 得到了以下反馈：第一反应是再试一次第二个反应是优化一下 SQL第三个反应是调大 buffer pool，然后开始换硬件了，换一下 SSD最后实在不行了找个搜索引擎搜索一下“MySQL 慢怎么办”。如果大家用的是国内的搜索引擎的话，搜索引擎会推荐某某知道或者某某乎, 推荐一些 MySQL 调优经验, 调大参数 A, 调低参数 B, 诸如此类，类似的网站能告诉你 MySQL 慢怎么办。我们来分析一下这些现象背后隐藏的意义：如果再试一次能够成功的话, 意味着你可能碰到了不可复现的外界因素的影响，导致 MySQL 会慢。如果优化 SQL 能解决，就意味着 SQL 的执行复杂度远远大于它的需求复杂度。如果调大 buffer pool 能解决，就意味着 MySQL 碰到了自身的某些限制。如果换 SSD 能解决，那么意味着服务器资源受到了一定的限制。如果需要搜索引擎，意味着调优这事已经变成了玄学。本文向大家分享我对 MySQL 慢的诊断思路，以及向大家介绍系统观测工具。MySQL 慢的诊断思路MySQL 慢的诊断思路，一般会从三个方向来做：MySQL 内部的观测外部资源的观测外部需求的改造下面依次看一下这几个思路。MySQL 内部观测常用的 MySQL 内部观测手段是这样的：第一步是 Processlist，看一下哪个 SQL 压力不太正常；第二步是 explain，解释一下它的执行计划；第三步要做 Profilling，如果这个 SQL 能再执行一次的话, 就做一个 Profilling；高级的 DBA 会直接动用 performance\_schema ，MySQL 5.7 以后直接动用 sys\_schema，sys_schema 是一个视图，里面有便捷的各类信息，帮助大家来诊断性能；再高级一点，会动用 innodb_metrics 进行一个对引擎的诊断。除了这些手段以外，还有一些乱七八糟的手段就不列在这了，这些是常规的 MySQL 内部状态观测的思路。外部资源观测这里引用国外一个大神写的文章，标题是《60 秒的快速巡检》（参考链接在文末）。我们来看一下它在 60 秒之内对服务器到底做了一个什么样的巡检。一共十条命令，下面一条一条来看一下。uptime，uptime 告诉我们这个机器活了多久，以及它的平均负载是多少。dmesg -T | tail，告诉我们系统日志里边有没有什么报错。vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap 有没有使用。mpstat -P ALL 1，告诉我们 CPU 压力在各个核上是不是均匀的。pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。iostat-xz 1，查看 IO 的问题。free-m 内存使用率；sar-n DVE 1，sar-n TCP, ETCP 1，8 和 9 两条按设备网卡设备的维度，看一下网络的消耗状态，以及总体看 TCP 的使用率和错误率是多少。top，看一下大概的进程和线程的问题。这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。外部需求改造第三个诊断思路是外部的需求改造，在这里引用了 MySQL 官方文档中的一章，《Examples of Common Queries 》（ https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html），文档中介绍了常规的 SQL 怎么写, 给出了一些例子。下面看一下它其中提到的一个例子。这张表有三列，article、dealer、price。它做的事情是从这个表里选取每个作者最贵的商品列在结果集中，这是它最原始的 SQL，非常符合业务的写法，但是它是个关联子查询。关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。第三步，会教大家直接把子查询拿掉，然后转成这样一个 SQL，这个就叫业务改造，前后三个 SQL 的成本都不一样，把关联子查询拆掉的成本，拆掉以后 SQL 会跑得非常好，但这个 SQL 已经不能良好表义了，只有在诊断到 SQL 成本比较高的情况下才建议大家使用这种方式。为什么它能够把一个关联子查询拆掉？这背后的原理是关系代数，所有的 SQL 都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。总结一下，对于 MySQL 慢的诊断思路如下：第一，MySQL 本身提供了很多命令来观察 MySQL 自身的各类状态，从上往下检一般能检到 SQL 的问题或者服务器的问题。第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，把服务器的资源全部都观察一圈就可以了。第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写 SQL，这个成本会下降的非常快，这个是常规的 MySQL 慢的诊断思路。下面重点介绍为大家介绍系统观测工具。系统观测工具介绍先从诊断思路的讨论切换到系统的观测工具，首先了解什么叫系统观测工具并且看一下它的举例，然后再回到诊断思路上，看看新的工具的引入能为我们的思路到底带来怎样的改变。什么叫系统观测工具这里也参考了一篇外国人写的文档：https://jvns.ca/blog/2017/07/05/linux-tracing-systems/把这个文档拆开，中间描述了三件事情：系统观测工具的数据源来自于哪里；数据采集过程，因为采集的是系统的运行状况，所以到底如何采集这是一个难点；应该怎么看数据，是用图来看，还是用表来看，它就叫数据处理前端。第一步，我们来看一下数据源，Linux 给我们提供的数据源包括操作系统内核态提供的观测点和用户态提供的观测点，MySQL 很早之前就提供了用户态的观测点。第二步，如何把数据抽出来。以下这些工具中大家最熟悉的应该是 perf 和 ftrace，sysdig 也有人在用，其它的可能有所耳闻，这是从操作系统里抽取数据的方法。第三步，数据处理前端，前端常用的也是 perf 和 ftrace。如果大家对 perf 很熟悉的话会知道 perf 出来的数据是一个树形的数据，并可以跟这棵树进行交互，比如说: 查看某个函数运行了多久，哪一个函数的时间最长，这个是数据处理前端。我们来对比一下常规的四类系统观测工具：ftrace, perf_events，eBPF 和 Systemtap，这四个工具到底有什么不同，看看 Linux 为什么提供这么多观测工具。ftrace：ftrace 是 sysfs 中的一个桩，通过这个桩内核提供了一种观测的形式——把想观测的函数签名打到这个桩里，然后操作系统就会提供这个函数运行的状况。ftrace 的结构如左图, 数据处理前端和采集端是 ftrace, 数据源是下面这一堆。perf：常用的 perf 的原理是操作系统提供了一个系统调用可以将数据写到一个缓存中, 然后客户端把这些数据端抽取出来然后呈现在显示器上。eBPF：这是本文想重点推荐的。以上两种方案一种是操作系统提供的文件系统上的桩，一种是操作系统提供的系统调用，而 eBPF 是将一段代码直接插到操作系统内核某一个位置上的机制。Systemtap：它的原理是将一段 C 的代码编译成一个内核模块，然后将这个模块嵌到内核里边去，它不是由内核提供的一个机制，而是由内核的模块机制提供的一种功能。介绍了这四种观测工具的不同，大家在选取观测工具的时候就知道应该怎么选。这四种观测工具对系统伤害最轻的是谁？对系统伤害最轻的是系统调用，这是系统承诺出来的服务。然后是 ftrace，这是系统在文件系统层面提供的一个口，告诉你可以通过这个口跟系统交互。对系统侵入性最强的是谁？对系统侵入性最强的应该是 eBPF，因为它直接将一根代码嵌入到系统里边去做，最不稳定的应该是 System Tap，因为它是系统的一个模块, 又提供了非常复杂的功能。上图是 eBPF 的架构图，eBPF 先将一段程序编译成二进制代码，然后插入到操作系统里，操作系统运行这段代码的时候，将采集到的数据吐到操作系统本身的空间里，然后再做统一返回。eBPF 结构最核心的部分在于把代码插入到操作系统中运行，它需要做各种安全保护才能完成这一点，所以这也是这个机制复杂的地方。下面引用一个开源的 eBPF 脚本集 bcc, 快速看一下 eBPF 能做什么, 这些功能都是开箱即用的。bcc (eBPF 脚本集) 使用举例MySQL 的请求延迟分析：一个 MySQL 承担了很多业务，上千个并发˙中，哪一个 SQL 最慢，到底有哪些 SQL 在一秒以上，除了 slow log 以外，还可以用这种方法来看。这个命令的结果分为三列，它的第一列是请求的延迟，指数级递增，单位是微秒，中间一列是它的命中数，如果有一个请求命中了 64-127 微秒这个区间，命中数会加一，最后一列是它的分布图，它在同一个报告里提供了数值的方式和图的方式，可以很容易看到结果。对于这台服务器来说，我下了一个 select 的性能压力，它大部分的请求集中于 64 到 127 微秒之间。这个数据库的性能可能还不错。再来看另外一种压力，我下了一个 select+insert 的混合压力在一个数据库里，它的图又变了，它呈现了一个非常好的双峰图，我将两个峰值用另外一种颜色标明，这两个峰值的意思是很有可能有混合压力在一个数据库里，或者是上面的这部分压力是命中了某些缓存，而下面的某些压力是由于没有命中缓存，导致这部分请求更慢一些, 形成另一个峰值，所以通过这种峰值分析可以看到数据库大概的一个运行状态。如果能做得更好，你可以抽检自己的数据库然后做环比图，比如今天和昨天同样的时间，同样的业务压力下对数据库的延迟进行分析，如果数据库的延迟峰一直在往后延，就意味着数据库的状态在变得更糟糕一些。这是 bcc 第一个能做的事情，需要再次强调的是它开箱即用直接下载过来就可以使用。MySQL 的慢查询：MySQL 本身提供很好的慢查询，为什么还要用另外一个机制来获取 MySQL 的慢查询呢？MySQL 的慢查询可能很难做，与 MySQL 的慢日志相比, 它可以低成本地完成:获取少量慢查询获取某种模式的慢查询获取某个用户的慢查询比如说获取少量的慢查询，为什么是少量呢？因为不确定现在的线上延迟是多少，慢查询只开一秒可能日志瞬间就被堆上去，性能就会下来，但是如果慢查询开个十秒左右，没有请求在这个区间命中，所以要一点一点的去调这个值，比如说线上 1% 的最慢的查询能够命中，但是在这个脚本里面，可以取一定区间的最大的几个查询把它拎出来。通过脚本还可以命中某种模式的慢查询, 比如说我们只关心 update 的慢查询, 那么获取 select 的结果就没有太大的意义，或者是我一定要获取某一些特定表的相关的查询，我都可以通过脚本来做。第三种情况，想获取某个用户的慢查询，这个一般对于多租户系统，因为多租户系统只想针对某一个用户进行慢查询分析的时候，这种脚本就比较好用。VFS 延迟分析：对 VFS 做延迟分析，这是对数据库进行了一个写压力，可以明显看到一个双峰图，这是写的两个峰，是数据库对于内核的写压力的反馈。这个意味着什么呢？这个可能意味着因为这部分的写是命中了操作系统文件系统的缓存，而下面这部分写是真正的写穿到设备的，所以他们俩的延迟不一样，这是一个典型的双峰图，大家需要把两个峰拆开来去行这样的分析。换一个说法，如果写压力都集中在这里，而没有第二个峰的情况下，需不需要去更换物理设备？有可能不需要，因为所有的东西都命中了操作系统的缓存。短生命周期的临时文件检测：这个不一定常见，MySQL 会在某些情况下动用临时表, 如果 SQL 没写好就会创建临时表，这些临时表的生命周期很短，但是量很大，所以一定要写文件而不能内存里。在这种情况下会对操作系统造成一些压力，而这个压力又不太好诊断，因为临时文件的生存周期短，所以这个脚本可以帮大家提供一个方案，这个方案的结果是这样子。我做了一个临时表，这个临时表活了 5.3 秒左右，于是它展现在了脚本的结果里。如果扫描自己的线上 MySQL 发现这里有大量的东西说明在大量的使用临时表，如果 IO 压力在此时比较大, 就可能受了临时表的影响。短连接分析：好一点的应用都会用连接池，但是我们很多的时候没有那么好的运气，老碰到那么好的应用，所以经常业务会扔过来大量的短连接。这个例子中, sysbench 上了一个大并发，但是只活了 300 多毫秒，这些连接都只活了 300 多毫秒，反复运行这个 sysbench 就可以将数据库打死，建立一千个连接，300 毫秒以后也会销毁，再建立一千个连接，你的业务就会忽上忽下，通过这个脚本就可以抓到这个压力从哪个服务器来的，哪个端口来的，然后把它搞定就可以了。长连接分析：除了短连接分析，还有长连接分析，哪一个业务端老在搞我的数据，老在往里写，总在往里读，搞的网络特别慢。可以帮大家提供这样一个视角，它有读有写。以上几个 bcc 相关的例子都是现成的脚本。bcc 可以观测操作系统的各个方面，比如说如果有东西被 OOM kill 掉了，内存有泄露的也可以看，它基本上是我们这几年发现的一个宝库，大家直接调用这些脚本就可以完成很多的别人完成不了的分析，它的技术用的是 eBPF，直接在 github 上直接搜就行了。eBPF 使用方法 / 限制如果这里边脚本满足不了要求, 那可以自己写。这里介绍一下脚本的写法以及 eBPF 的限制。拿上面提到过的 MySQL 延迟分析举例，一个 MySQL 上面有一千个 query，这些 query 大概都落在哪个延迟时间里面那张图，为了完成这个需求, 我需要写两段程序，其中第一段程序是运行在内核里边的程序。这段程序的逻辑是这样的：在 query 开始的时候截获一下，让它记录一个时间戳；请求结束的时候再截获一下记录一个时间戳；把两个时间戳相减获得一个延迟；把这个延迟扔到结果集里边去，程序就完成了。我用结束时间减开始时间，减一下得到一个延迟，然后把延迟扔到一个统计容器里面，这个事就结束了。这是我要写的第一个程序，是嵌到内核里的程序，但是需要一个外壳的程序负责嵌入。这个外壳程序的逻辑也非常简单，把刚才那段内核的程序嵌到 MySQL 的观测点上，嵌到内核里面去，然后把结果集拿出来，打印出来就结束了，这是如何写一个 eBPF 的脚本，大家唯一需要做的事情就是这两个程序，然后运行一下。这个程序的核心只有 45 行，中间忽略了负责差错处理的一部分。只需要把现在的脚本拿下来抄一抄，改一改就可以完成很多的功能了。这么好的方法为什么很多人不知道呢？操作系统内核的限制，这个功能是 Linux 4.4 引进来的，但是在 Linux 4.4 上存在统计的 bug，我们推荐的是 Linux 4.9+，部分好用的功能是在 4.13+ 上才开放，这个是 eBPF 最大的限制。怎么办呢？只能祝大家长寿吧！活到 Linux 4.x 内核能在生产环境上使用的那一天。它的第二个最大的限制是 MySQL 的编译参数，MySQL 虽然在很早的时候已经提供了 dtrace 的观测点，这些观测点是公用的，但是它在默认的编译出来的官方发布的包里边是不带观测点编译的，所以在直接官方发布的二进制的包里边是用不了这个功能的，大家需要自己编译一下。编译的时候需要带这个参数，这个可能也是属于一个比较大的限制。所以如果大家受到限制，再推荐换一个工具：systemtap。Linux 2.6 就已经有了，但是它的机制是写一个内核模块，这种机制其实不是特别稳定，它为了解决不是特别稳定的问题增加了若干限制，比如说能在内核中使用的内存大小有限制，采集频率也有限制，对整个内核性能的影响百分比也有限制，在这些限制参数都开起来的情况下，它还是比较安全的。但是很多观测功能就必须要把这些限制关掉，一旦关掉内核就不是很稳定，所以这个工具，我没有敢把它的缺点写在上面因为确实是个好的工具，我们也很难说它的这个缺点是个致命的缺陷，但是不太推荐在生产环境上使用，但是在测试环境上确实是非常好玩的一个工具，如果大家用不了 eBPF 的话可以用 systemtap 来做一些诊断。还有很多其他的工具：至于如何选择，大家直接谷歌一下有专门的文章教大家怎么选择这些观测工具。但是总的来说没有一个科学的思路，只有尝试，不停的尝试。参考链接：60 秒的快速巡检：https://medium.com/netflix-techblog/linux-performance-analysis-in-60-000-milliseconds-accc10403c55本文 PPT 下载链接：github.com/actiontech/slides

MySQL 常用30种SQL查询语句优化方法 MySQL 常用30种SQL查询语句优化方法1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=04、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=205、下面的查询也将导致全表扫描：(不能前置百分号)select id from t where name like ‘%c%’下面走索引select id from t where name like ‘c%’若要提高效率，可以考虑全文检索。6、in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 37、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*29、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ –name以abc开头的idselect id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id应改为:select id from t where name like ‘abc%’select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。12、不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)13、很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。19、任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。21、避免频繁创建和删除临时表，以减少系统表资源的消耗。22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，较好使 用导出表。23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONEINPROC 消息。29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。30、尽量避免大事务操作，提高系统并发能力。

MySQL 的索引是什么？怎么优化？ MySQL 的索引是什么？怎么优化？索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。一、导致SQL执行慢的原因1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。2.没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）3.数据过多（分库分表）4.服务器调优及各个参数设置（调整my.cnf）二、分析原因时，一定要找切入点1.先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢SQL），在生产环境跑上个一天过后，看看哪些SQL比较慢。2.Explain和慢SQL分析。比如SQL语句写的烂，索引没有或失效，关联查询太多（有时候是设计缺陷或者不得以的需求）等等。3.Show Profile是比Explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。4.找DBA或者运维对MySQL进行服务器的参数调优。三、什么是索引？MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree索引和Hash索引。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。索引如图所示：最外层浅蓝色磁盘块1里有数据17、35（深蓝色）和指针P1、P2、P3（黄色）。P1指针表示小于17的磁盘块，P2是在17-35之间，P3指向大于35的磁盘块。真实数据存在于子叶节点也就是最底下的一层3、5、9、10、13……非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35。查找过程：例如搜索28数据项，首先加载磁盘块1到内存中，发生一次I/O，用二分查找确定在P2指针。接着发现28在26和30之间，通过P2指针的地址加载磁盘块3到内存，发生第二次I/O。用同样的方式找到磁盘块8，发生第三次I/O。真实的情况是，上面3层的B+Tree可以表示上百万的数据，上百万的数据只发生了三次I/O而不是上百万次I/O，时间提升是巨大的。四、Explain 分析前文铺垫完成，进入实操部分，先来插入测试需要的数据：CREATE TABLEuser_info(idBIGINT(20)NOTNULLAUTO_INCREMENT,nameVARCHAR(50)NOTNULLDEFAULT'',ageINT(11)DEFAULTNULL,PRIMARY KEY(id),KEYname_index(name))ENGINE = InnoDB DEFAULTCHARSET = utf8;INSERT INTO user_info(name,age)VALUES('xys',20);INSERT INTO user_info(name,age)VALUES('a',21);INSERT INTO user_info(name,age)VALUES('b',23);INSERT INTO user_info(name,age)VALUES('c',50);INSERT INTO user_info(name,age)VALUES('d',15);INSERT INTO user_info(name,age)VALUES('e',20);INSERT INTO user_info(name,age)VALUES('f',21);INSERT INTO user_info(name,age)VALUES('g',23);INSERT INTO user_info(name,age)VALUES('h',50);INSERT INTO user_info(name,age)VALUES('i',15);CREATE TABLEorder_info(idBIGINT(20)NOTNULLAUTO_INCREMENT,user_idBIGINT(20)DEFAULTNULL,product_nameVARCHAR(50)NOTNULLDEFAULT'',productorVARCHAR(30)DEFAULTNULL,PRIMARY KEY(id),KEYuser\_product\_detail_index(user_id,product_name,productor))ENGINE = InnoDB DEFAULTCHARSET = utf8;INSERT INTO order_info(user_id,product_name,productor)VALUES(1,'p1','WHH');INSERT INTO order_info(user_id,product_name,productor)VALUES(1,'p2','WL');INSERT INTO order_info(user_id,product_name,productor)VALUES(1,'p1','DX');INSERT INTO order_info(user_id,product_name,productor)VALUES(2,'p1','WHH');INSERT INTO order_info(user_id,product_name,productor)VALUES(2,'p5','WL');INSERT INTO order_info(user_id,product_name,productor)VALUES(3,'p3','MA');INSERT INTO order_info(user_id,product_name,productor)VALUES(4,'p1','WHH');INSERT INTO order_info(user_id,product_name,productor)VALUES(6,'p1','WHH');INSERT INTO order_info(user_id,product_name,productor)VALUES(9,'p8','TE');初体验，执行Explain的效果：索引使用情况在possible\_keys、key和key\_len三列，接下来我们先从左到右依次讲解。1.id--id相同,执行顺序由上而下explain selectu.,o. from user_infou,order_infoowhereu.id=o.user_id;--id不同,值越大越先被执行explain select *from  user_info  where id=(select user\_id from order\_info where  product_name ='p8');2.select_type可以看id的执行实例，总共有以下几种类型：SIMPLE： 表示此查询不包含 UNION 查询或子查询PRIMARY： 表示此查询是最外层的查询SUBQUERY： 子查询中的第一个 SELECTUNION： 表示此查询是 UNION 的第二或随后的查询DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询UNION RESULT, UNION 的结果DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）3.tabletable表示查询涉及的表或衍生的表：explain select tt. from (select u. from user\_info u,order\_info o where u.id=o.user_id and u.id=1) ttid为1的的表示id为2的u和o表衍生出来的。4.typetype 字段比较重要，它提供了判断查询是否高效的重要依据依据。 通过 type 字段，我们判断此次查询是 全表扫描 还是 索引扫描等。 type 常用的取值有:system: 表中只有一条数据， 这个类型是特殊的 const 类型。const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快， 因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的：explain select * from user_info where id = 2；eq\_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user\_info, order\_info where user\_info.id = order\_info.user\_id;ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询：explain select * from user\_info, order\_info where user\_info.id = order\_info.user\_id AND order\_info.user_id = 5range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info  where id between 2 and 8；index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说， 我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询， 那么一般来说可以对相应的字段添加索引来避免。通常来说, 不同的 type 类型的性能关系如下:ALL < index < range ~ index\_merge < ref < eq\_ref < const < system ALL 类型因为是全表扫描， 因此在相同的查询条件下，它是速度最慢的。而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引，因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据，因此可以过滤部分或大部分数据，因此查询效率就比较高了。5.possible_keys它表示 mysql 在查询时，可能使用到的索引。 注意，即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 mysql 使用到。 mysql 在查询时具体使用了哪些索引，由 key 字段决定。6.key此字段是 mysql 在当前查询时所真正使用到的索引。比如请客吃饭,possible_keys是应到多少人，key是实到多少人。当我们没有建立索引时：explain selecto.* from order_infoowhereo.product_name= 'p1'ando.productor='whh';create index idx\_name\_productor on order_info(productor);drop index idx\_name\_productor on order_info;建立复合索引后再查询：7.key_len表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。8.ref这个表示显示索引的哪一列被使用了，如果可能的话,是一个常量。前文的type属性里也有ref，注意区别。9.rowsrows 也是一个重要的字段，mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好。可以对比key中的例子，一个没建立索引钱，rows是9，建立索引后，rows是4。10.extraexplain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。using where ：表名使用了where过滤。五、优化案例explain select u.,o. from user\_info u LEFT JOIN  order\_info o on u.id=o.user_id;执行结果，type有ALL，并且没有索引：开始优化，在关联列上创建索引，明显看到type列的ALL变成ref，并且用到了索引，rows也从扫描9行变成了1行：这里面一般有个规律是：左链接索引加在右表上面，右链接索引加在左表上面。六、是否需要创建索引？索引虽然能非常高效的提高查询速度，同时却会降低更新表的速度。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。我是个普通的程序猿，水平有限，文章难免有错误，欢迎牺牲自己宝贵时间的读者，就本文内容直抒己见，我的目的仅仅是希望对读者有所帮助。来源：我叫刘半仙 my.oschina.net/liughDevelop/blog/1788148

MySQL索引的种类 MySQL索引的种类索引的种类1、概述2、索引种类2.1、逻辑功能划分2.1.1、普通索引2.1.2、唯一索引2.1.3、主键索引2.1.4、全文索引2.2、物理实现上划分2.2.1、聚簇索引2.2.2、非聚簇索引2.2.3、聚簇索引与非聚簇索引区别2.3、作用字段个数划分2.3.1、单列索引2.3.2、组合索引3、总结1、概述大家好，我是欧阳方超，可以关注我的公众号“欧阳方超”，后续内容将在公众号首发。 今天要介绍的内容是索引的种类。 MySQL索引是一种提高查询效率的重要手段，它能够快速定位需要的数据，从而减少查询的开销。MySQL支持多种索引类型，每种类型都有其特点和适用场景。本文将介绍MySQL常见的索引类型及其特点。2、索引种类MySQL常见的索引种类有普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引等。索引可以从不同角度去划分，一般来说主要有以下三个划分角度：2.1、逻辑功能划分2.1.1、普通索引普通索引是 MySQL 中最基本的索引类型之一，它可以加快对表中数据的查询速度，并且它只是用于提高查询效率。下面是在user表中创建普通索引的例子： user表结构CREATE TABLE users (   id INT(11) NOT NULL AUTO_INCREMENT,   username VARCHAR(50) NOT NULL,   email VARCHAR(100) NOT NULL,   password VARCHAR(255) NOT NULL,   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (id) );可以为 “username” 和 “email” 列创建普通索引，以提高对这两列的查询速度。下面是创建普通索引的例子：ALTER TABLE users ADD INDEX idx_username (username); ALTER TABLE users ADD INDEX idx_email (email);这将为 “username” 和 “email” 列创建名为 “idx_username” 和 “idx_email” 的普通索引。创建普通索引后，我们可以在查询时使用这些索引来加快查询速度。例如：SELECT * FROM users WHERE username = 'john';2.1.2、唯一索引使用unique参数可以设置为唯一索引，创建唯一索引后，相应列的值在全表必须是唯一的，可以为空。 还有user表为例，可以为 user表的"username" 和 “email” 列创建唯一索引，以确保这两列中的每个值都是唯一的。下面是创建唯一索引的示例：ALTER TABLE users ADD UNIQUE INDEX idx_username (username); ALTER TABLE users ADD UNIQUE INDEX idx_email (email);这将为 “username” 和 “email” 列创建名为 “idx_username” 和 “idx_email” 的唯一索引。创建唯一索引后，如果尝试插入重复的值，则会引发错误，这可以帮助确保表中的数据是唯一的。2.1.3、主键索引主键索引是 MySQL 中的一种特殊的索引类型，它是用于标识每个表中唯一行的索引。主键索引要求主键列中的每个值都必须唯一且不能为空值。 还以user表为例，其表结构如下：CREATE TABLE users (   id INT(11) NOT NULL AUTO_INCREMENT,   username VARCHAR(50) NOT NULL,   email VARCHAR(100) NOT NULL,   password VARCHAR(255) NOT NULL,   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (id) );在这个例子中id列被指定为主键列，并且使用了auto_increment属性来自动为每个新行生产唯一的id值。因此，id列中的每个值都是唯一的，且不能为空值。通过将id列指定为主键列，MySQL将自动为该列创建主键索引。这将确保id类中的每个值都是唯一的，并且可以在查询时更快地定位和访问所需的数据。需要注意的是，每个表只能有一个主键，因此，在为表创建主键索引时，需要选择一个唯一的列作为主键列。2.1.4、全文索引全文索引是MySQL中的一种特殊索引类型，用于对文本字段进行全文搜索，全文索引可以帮助加快对文本数据的搜索速度，并支持全文搜索的高级功能，例如模糊搜索和关键词匹配。下面是一个创建全文索引的示例：CREATE TABLE articles (   id INT(11) NOT NULL AUTO_INCREMENT,   title VARCHAR(255) NOT NULL,   content TEXT NOT NULL,   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (id) );可以为 “content” 列创建全文索引，以便在文章内容中进行全文搜索。下面是创建全文索引的示例：ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content) WITH PARSER ngram;注意，上面创建全文索引时我们指定了使用名为“ngram”的分词器来为content列创建全文索引。假设表内有如下的数据： 当我们使用两个关键字进行查询时，可以查询记录：mysql> select * from articles where match (content) against('不认'); +----+-------+------+-+ | id | title | content                  | created_at          | +----+-------+------+-+ |  2 | 1     | 中文搜索，不认知         | 2023-07-14 14:49:24 | +----+-------+------+-+ 1 row in set (0.00 sec)但是如果使用的关键词只有一个字，则查不出任何记录：mysql> select * from articles where match (content) against('不'); Empty set (0.00 sec)这是因为MySQL中ngram_token_size变量的值默认为2，即要查询的词的最少个数为2，如果用一个词去查询自然查不到任何内容。如果要搜索单字，就要把ngram_token_size设置为1。在 MySQL 中，ngram_token_size 是一个全文索引配置选项，用于指定 ngram 索引中单个词语的长度。ngram 索引是一种全文索引算法，它将文本分成连续的 n 个字母或单词，以便更高效地进行搜索。ngram_token_size 决定了 ngram 索引中单个词语的长度，从而影响了全文索引的性能和搜索结果。 使用中文分词器时，需要确保MySQL的字符集设置与文本的字符集匹配，以确保正确的分词和搜索。此外，中文分词器的性能可能会受到一些限制，因此在使用中文分词器时，需要进行适当的性能测试。然而遗憾的是，在ElasticSearch等专门搜索引擎面前，关系型数据库的全文检索功能使用的并不多。2.2、物理实现上划分2.2.1、聚簇索引在 MySQL 中，聚簇索引是一种特殊的索引类型，它将表中的数据按照索引键的顺序存储在磁盘上，以提高数据访问的效率。聚簇索引的特点是，索引和数据存储在一起，因此在查询时可以直接访问数据而无需再次查找磁盘上的数据块。2.2.2、非聚簇索引非聚簇索引是一种索引类型，它将索引和数据分开存储在磁盘上。与聚簇索引不同，非聚簇索引将索引和数据存储在不同的位置，因此在查询时需要先访问索引，再根据索引中的指针访问磁盘上的数据块，从而增加了查询的开销。非聚簇索引常见的类型有 B-tree 索引、哈希索引和全文索引等。 B-tree 索引是一种常见的非聚簇索引类型，它将索引键和指向数据的指针存储在一棵平衡树中。B-tree 索引的特点是支持快速的范围查询和排序操作，因此常用于对范围较大的列进行索引，例如日期、价格等。 哈希索引是另一种常见的非聚簇索引类型，它将索引键通过哈希函数计算出一个唯一的哈希值，并将哈希值和指向数据的指针存储在哈希表中。哈希索引的特点是支持快速的等值查询，但不支持范围查询和排序操作。 全文索引是一种特殊的非聚簇索引类型，它可以对文本内容进行索引和搜索。全文索引的特点是支持对文本内容进行快速的关键字搜索和匹配，因此常用于搜索引擎和文本处理应用中。 需要注意的是，非聚簇索引通常需要占用更多的磁盘空间，因为需要存储索引和指向数据的指针。同时，非聚簇索引的查询效率可能会受到磁盘 I/O 速度的限制，因此需要仔细评估其适用性和性能，以选择最合适的索引类型。2.2.3、聚簇索引与非聚簇索引区别聚簇索引和非聚簇索引的区别主要有以下几个：聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。 聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引。 聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制。2.3、作用字段个数划分2.3.1、单列索引单列索引是一种索引类型，它只包含一个列的值，如我们在上面创建的普通索引就是单列索引。2.3.2、组合索引组合索引是一种索引类型，它包含多个列的值。与单列索引不同，组合索引将多个列的值组合在一起作为索引键，以提高多列查询的效率。组合索引可以根据多个列的值来快速定位需要的数据，从而减少查询的开销。下面是一个组合索引的示例： 假设有一个名为 “users” 的表，表结构如下：CREATE TABLE users (   id INT(11) NOT NULL AUTO_INCREMENT,   name VARCHAR(255) NOT NULL,   age INT(11) NOT NULL,   gender VARCHAR(10) NOT NULL,   PRIMARY KEY (id),   INDEX age_gender_index (age, gender) ) ENGINE=InnoDB;在这个表中，id 列是主键，因此自动创建了一个聚簇索引。同时，我们手动创建了一个名为 “age_gender_index” 的组合索引，以提高根据年龄和性别查询的效率。例如，查询年龄等于 30 且性别为男的用户记录：SELECT * FROM users WHERE age = 30 AND gender = 'male';MySQL 将使用 “age_gender_index” 索引来快速定位符合条件的用户记录，而无需扫描整个表。这样可以大大提高查询效率，尤其是在数据量较大时。需要注意的是，组合索引的顺序很重要，因为索引键的顺序影响查询效率。在上面的例子中，我们将 age 列放在前面，因为根据年龄查询的条件更加常见。此外，组合索引的列数也需要仔细考虑，过多的列可能会降低索引的效率。因此，需要仔细评估索引的适用性和性能，以选择最合适的索引类型和索引键的顺序。3、总结MySQL支持多种索引类型，每种类型都有其特点和适用场景。在使用索引时，需要根据查询的特点和数据的结构来选择合适的索引类型和索引键的顺序，以提高查询效率和减少查询的开销。ps：不够全面，这个详细些 MySQL 索引