索引用于快速找出在某个列中有一特定值的行,假如不运用索引MySQL有必要从第l条记载开端读完整个表,直到找出相关的行.表越大,查询数据所花费的时刻越多,假如表中查询的列有一个索引,MySQL能快速抵达某个方位去查找数据文件,而不必检查所有数据,可加快数据查询的查询速度进步功率,索引可在创立表时增加,也可动态调整已有表.
♥ 文章声明 ♥ 该系列文章部分文字描述,参阅于以下文献,化繁为简. 《MySQL5.7从入门到精通》 – 刘增杰
通俗的来说索引是一种数据结构,是帮助MySQL进行高效检索数据的一种机制,你可以简单理解为排好序的快速查找数据结构,
索引都是B+树(多路查找树)结构安排的索引,包括集合索引,复合索引,前缀索引,仅有索引,都是b+树索引.
优势：1.进步数据检索功率，降低数据库IO成本，降低CPU消耗。 劣势: 2.索引是一张表，索引也占空间，虽然进步了查询速度，但也会降低表的更新速度，假如新加数据，索引也会主动更新。
CPU在饱和的时候一般产生在数据装入内存或从磁盘上读取数据时。 IO磁盘安静，服务器硬件安静。
创立一般索引: 在创立表时指定索引类型,如下在u_id字段增加一个一般索引,该索引作用仅仅加对快数据的访问速度.
MariaDB [lyshark]> create table book
-> (
-> u_id int not null,
-> u_book varchar(20) not null,
-> index(u_id)
-> );
-- 运用show index句子检查指定表中创立的索引
MariaDB [lyshark]> show index from book;
MariaDB [lyshark]> show create table book \G;
MariaDB [lyshark]> explain select * from book where u_id=1 \G;
创立仅有索引: 创立仅有索引的首要原因是削减查询索引列操作的履行时刻,尤其是对比较庞大的数据表.它与前面的一般索引类似,不同的便是:索引列的值有必要仅有,但允许有空值.假如是组合索引,则列值的组合有必要仅有.
MariaDB [lyshark]> create table table_1
-> (
-> id int not null,
-> name char(30) not null,
-> unique index UniqIdx(id)
-> );
Query OK, 0 rows affected (0.02 sec)
MariaDB [lyshark]> show create table table_1 \G;
*************************** 1. row ***************************
Table: table_1
Create Table: CREATE TABLE `table_1` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
UNIQUE KEY `UniqIdx` (`id`) #id字段现已成功树立了一个名为UniqIdx的仅有索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
创立单列索引: 单列索引是在数据表中的某一个字段上创立的索引,一个表中可以创立多个单列索引.前面两个比如中创立的索引都为单列索引.
MariaDB [lyshark]> create table table_2
-> (
-> id int not null,
-> name char(50) null,
-> index SingleIdx(name(20))
-> );
Query OK, 0 rows affected (0.03 sec)
MariaDB [lyshark]> show create table table_2 \G;
*************************** 1. row ***************************
Table: table_2
Create Table: CREATE TABLE `table_2` (
`id` int(11) NOT NULL,
`name` char(50) DEFAULT NULL,
KEY `SingleIdx` (`name`(20)) #name字段上现已成功树立了一个单列索引,名称为SingleIdx
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
创立组合索引: 组合索引便是在多个字段上创立多个索引.
MariaDB [lyshark]> create table table_3
-> (
-> id int not null,
-> name char(30) not null,
-> age int not null,
-> info varchar(255),
-> index MultiIdx(id,name,age)
-> );
创立全文索引: 全文索引可以用于全文查找,全文索引合适用于大型数据集,现在只要MyISAM存储引擎支持FULLTEXT索引,而且只为CHAR、VARCHAR和TEXT列创立索引.索引总是对整个列进行,不支持部分(前缀)索引.
-- 留意：MySQL5.7默认存储引擎室是InnoDB,在这里咱们要改成MyISAM,不然索引会报错
MariaDB [lyshark]> create table table_4(
-> id int not null,
-> name char(40) not null,
-> age int not null,
-> info varchar(255),
-> fulltext index FullTxtIdx(info)
-> )engine=MyISAM;
创立空间索引: 空间索引有必要在MyISAM类型的表中创立,且空间类型的字段有必要为空,可以看到,table_5表的g字段上创立了名称为spatIdex的空间索引,留意:创立时刻指定空间类型字段值的非空约束,而且表的存储引擎有必要为MyISAM.
MariaDB [lyshark]> create table table_5
-> (
-> g geometry not null,
-> spatial index spatIdx(g)
-> )engine=MyISAM;
增加索引: 上面的几种方法都是在新建表中增加索引,假如需要在已存在表中增加则需要运用以下命令了.
MariaDB [lyshark]> create table book
-> (
-> bookid int not null,
-> bookname varchar(255) not null,
-> authors varchar(255) not null,
-> info varchar(255) null,
-> comment varchar(255) null,
-> year_public year not null
-> );
-- 增加一般索引
MariaDB [lyshark]> alter table book add index BKNameIdx(bookname(30));
-- 增加仅有索引
MariaDB [lyshark]> alter table book add unique index UniqidIdx(bookId);
-- 增加单列索引
MariaDB [lyshark]> alter table book add index BkcmtIdx(comment(50));
-- 增加组合索引
MariaDB [lyshark]> alter table book add index BkAuAndInfoIdx(authors(30),info(50));
-- 通过索引名字删去索引
MariaDB [lyshark]> alter table book drop index UniqidIdx;
MariaDB [lyshark]> alter table book drop index BKNameIdx;
explain 字段的状况:
MariaDB [lyshark]> explain select s_name,s_city from suppliers where s_id IN (select Gid from lyshark where Uid='a1');
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | lyshark | const | PRIMARY | PRIMARY | 30 | const | 1 | |
| 1 | PRIMARY | suppliers | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
MariaDB [lyshark]> explain select Course.CID,Course.Cname from Course join(
select CID from lyshark.StudentScore where SID = (select SID from lyshark.Student where Sname='周梅')
)as StudentScore on Course.CID = StudentScore.CID;
+---------+-------------+--------------+------+---------------+------+---------+------+------+----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+-------------+--------------+------+---------------+------+---------+------+------+----------+
| 1(1) | SIMPLE | Course | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1(2) | SIMPLE | StudentScore | ALL | NULL | NULL | NULL | NULL | 18 | |
| 3 | SUBQUERY | Student | ALL | NULL | NULL | NULL | NULL | 10 | |
+---------+-------------+--------------+------+---------------+------+---------+------+------+----------+
ID字段的理解:
1.当ID字段相同的状况下履行数据是从上到下,例如第一张表中会由上至下履行下来.
2.当ID不同的状况,假如是子查询,id越大的将在最前面被履行,例如第二张表履行次序为3->1(1)->1(2)
select_type
MariaDB [lyshark]> explain select * from tbl_emp a left join tbl_dept b on a.deptld=b.id where b.id is null
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptld = b.id where a.deptld is null;
+------+--------------+------------+--------+---------------+---------+---------+------------------+------+---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+--------+---------------+---------+---------+------------------+------+---------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 8 | |
| 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | lyshark.a.deptld | 1 | |
| 2 | UNION | b | ALL | NULL | NULL | NULL | NULL | 5 | |
| 2 | UNION | a | ALL | fk_dept_id | NULL | NULL | NULL | 8 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+--------+---------------+---------+---------+------------------+------+---------+
SIMPLE => 标志着简单的select查询恳求,查询中不包括子查询或许union查询.
PRIMARY => 查询中任何杂乱的查询中,最外层的查询句子,便是最终加载的句子.
SUBQUERY => 子查询类型,在select或where列表中包括了子查询.
DERIVED => 在FROM列表中包括子查询,会被标记为DERIVED(衍生),此时会递归履行子查询,并存储在暂时表中.
UNION => 若第二个SELECT呈现在UNION之后,则标记为UNION.
UNION RESULT => 从UNION表中获取成果的SELECT
type 访问类型排列，只要可以到达ref，range级别就现已不错了，性能功率。
system -> const -> eq_ref -> ref -> range ->index -> all
system -> 表中只要一条记载，这是const类型的特里，平时不会呈现。
const -> 主键仅有索引：标明通过索引一次就找到数据，例如查询一个常量。
MariaDB [lyshark]> explain select * from lyshark where Uid="a1";
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | lyshark | const | PRIMARY | PRIMARY | 30 | const | 1 | |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
eq_ref -> 仅有性索引扫描，对于每个索引键，表中只要一条记载与之匹配，常用于主键或仅有索引扫描。
ref-> 非仅有性索引扫描，回来匹配某个独自值的所有行，被之上也是一种索引访问。查找扫描混合体
MariaDB [lyshark]> create table t1(col1 int,col2 int);
MariaDB [lyshark]> alter table t1 add index idx_col1_col2(col1,col2);
MariaDB [lyshark]> explain select * from t1 where col1=1;
+------+-------------+-------+------+---------------+---------------+---------+-------+------+--------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+---------------+---------+-------+------+--------+
| 1 | SIMPLE | t1 | ref | idx_col1_col2 | idx_col1_col2 | 5 | const | 1 | |
+------+-------------+-------+------+---------------+---------------+---------+-------+------+--------+
range -> 规模扫描，只检索给定规模的行，key列显现运用了那个索引。 where，between，<>，in 等查询中运用。
explain select * from t1 where col1 between 1 and 2;
explain select * from t1 where col1 in (1,2,3);
index -> 全索引扫描，全表索引扫描，比all要好一些。
MariaDB [lyshark]> explain select * from t1;
all -> 全表扫描。最差的性能。
possible_keys，key 是否运用到了索引，possible_keys 显现或许
possible_keys => 显现或许应用在这张表中的索引，一个或多个，该索引会被列出，但不必定被实践查询运用。
key => 实践应用到本次查询的索引类型。最重要的。假如为NULL，则说明没有运用索引。
MariaDB [lyshark]> explain select col1,col2 from t1;
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
| 1 | SIMPLE | t1 | index | NULL | idx_col1_col2 | 10 | NULL | 1 | |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
------------------------------------------------------------------------------
查询中若运用了覆盖索引，则该索引仅呈现key列表中 覆盖索引以下便是原理
MariaDB [lyshark]> create table t1(col1 int,col2 int,col3 int);
MariaDB [lyshark]> alter table t1 add index idx_col1_col2(col1,col2);
-- 树立的索引与，查询的行数，有必要一致，col1,col2是有索引的。
MariaDB [lyshark]> explain select col1,col2 from t1;
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | idx_col1_col2 | 10 | NULL | 1 | Using |
+------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
-- 扫描三个值，不会呈现运用索引的状况。
MariaDB [lyshark]> explain select col1,col2,col3 from t1;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
possible_keys，key,ken_len
key_len 标明索引中运用的字节数，这个长度用的越少越好，kenLen长度是依据表的界说核算得出，而不是表中数据检索出的。
ref 显现索引的那一列被运用了，假如或许的话，是一个常数，那些列或常量被用于查找索引列上的值。界说了引用了那些库。
rows 依据表统计信息及索引选用状况，大致估算出找到所需的记载所需要读取的行数，该值当然也是越小越好。 每张表有多少行被优化器查询。
MariaDB [lyshark]> explain select * from lyshark;
+------+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | lyshark | ALL | NULL | NULL | NULL | NULL | 17 | |
+------+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
MariaDB [lyshark]> create table tt1(id int primary key,col1 varchar(10),col2 varchar(10));
MariaDB [lyshark]> create table tt2(id int primary key,col1 varchar(10),col2 varchar(10));
MariaDB [lyshark]> create index idt_col1_col2 on tt2(col1,col2);
MariaDB [lyshark]> explain select * from tt1,tt2 where tt1.id = tt2.id and tt2.col1 = 'admin';
+------+-------------+-------+--------+-----------------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+-----------------------+---------+---------+----------------+------+-------------+
| 1 | SIMPLE | tt1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | tt2 | eq_ref | PRIMARY,idt_col1_col2 | PRIMARY | 4 | lyshark.tt1.id | 1 | Using where |
+------+-------------+-------+--------+-----------------------+---------+---------+----------------+------+-------------+
2 rows in set (0.00 sec)
extra 扩展列
using filesort 产生了文件内排序，完蛋了，mysql无法运用索引进行排序，运用了外部的索引排序，而不是依照表内的索引次序进行读取。mysql无法利用索引完结排序，操作成为文件排序。
MariaDB [lyshark]> create table tab1(id int primary key,col1 int,col2 int,col3 int);
MariaDB [lyshark]> insert into tab1 values(1,1,2,3),(2,4,5,6),(3,7,8,9);
MariaDB [lyshark]> create index tab1_col1_col2_col3 on tab1(col1,col2,col3);
MariaDB [lyshark]> explain select col1 from tab1 where col1 order by col3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: tab1_col1_col2_col3
key: tab1_col1_col2_col3
key_len: 15
ref: NULL
rows: 3
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)
MariaDB [lyshark]> explain select col1 from tab1 where col1 order by col2,col3 \G
查询运用索引，没问题的，仅仅说，咱们没有针对order by 树立排序索引，或许是树立了索引，你没用上！！
以下咱们加上悉数索引字段，从此下面这条sql性能更高了。
MariaDB [lyshark]> explain select col1 from tab1 where col1 order by col1,col2,col3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: tab1_col1_col2_col3
key: tab1_col1_col2_col3
key_len: 15
ref: NULL
rows: 3
Extra: Using where; Using index
1 row in set (0.00 sec)
假如可以，尽快优化。
using temporary 完全完犊子，这个会新建了一个内部暂时表，然后操作完后再把暂时表删去，动作更凶险。
运用暂时表保存中心成果，mysql在对查询成果排序时运用暂时表，常用于排序order by 和分组查询group by .
MariaDB [lyshark]> explain select * from tab1 where col1 in(1,2,3) group by col2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: tab1_col1_col2_col3
key: tab1_col1_col2_col3
key_len: 15
ref: NULL
rows: 3
Extra: Using where; Using index; Using temporary; Using filesort 完全完犊子
1 row in set (0.00 sec)
处理办法，你给我树立的索引个数和次序，必定要按次序来。
MariaDB [lyshark]> explain select * from tab1 where col1 in(1,2,3) group by col1,col2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: tab1_col1_col2_col3
key: tab1_col1_col2_col3
key_len: 15
ref: NULL
rows: 3
Extra: Using where; Using index 处理了。
1 row in set (0.00 sec)
using index 这种状况是功德，标明相应的操作运用了 covering index 运用了覆盖索引，功率不错，。
假如一起呈现了using where 标明索引被用来履行索引键值的查找。
假如没有一起呈现using where 标明索引用来读取数据而非履行查找动作。
MariaDB [lyshark]> explain select col1,col2 from tab1;
+------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | tab1 | index | NULL | tab1_col1_col2_col3 | 15 | NULL | 3 | Using index | using 标明直接从索引上找到了数据。
+------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
覆盖索引，便是说你树立的复合索引是 x,y,z 那么你在查询是应该要覆盖这些索引，这样才能让索引，发挥其最大的性能，否则索引等于白树立。
覆盖索引，尽量不要写星号，这种低功率的查询。
select * from lyshark where id=1; -- 完蛋的。
select x,y,z from lyshark wehre id =1; -- 覆盖到主键上，查询功率提高许多。
using where -> 运用了where using join buffer -> 用到了缓存buffer
完成单表索引优化
create table if not exists article(
id int(10) unsigned not null primary key auto_increment,
author_id int(10) unsigned not null,
category_id int(10) unsigned not null,
views int(10) unsigned not null,
comments int(10) unsigned not null,
title varbinary(255) not null,
content text not null );
insert into article(author_id,category_id,views,comments,title,content) values(1,1,1,1,'1','1'),(1,1,1,2,'1','1'),(2,2,2,2,'2','2'),(3,3,3,3,'3','3');
MariaDB [lyshark]> explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;
-- 创立复合索引 all 变为了range 只处理了全表扫描问题
MariaDB [lyshark]> create index idx_article_ccv on article(category_id,comments,views);
-- 最终一个完整版的
MariaDB [lyshark]> drop index idx_article_ccv on article;
MariaDB [lyshark]> create index idx_article_cv on article(category_id,views);
完成两表索引优化
create table class
(
id int(10) auto_increment,
card int(10) not null,
primary key(id)
);
create table book
(
bookid int(10) auto_increment,
card int(10) not null,
primary key(bookid)
);
MariaDB [lyshark]> insert into class(card) values(floor(1+(rand()*20))); * 10
MariaDB [lyshark]> insert into book(card) values(floor(1+(rand()*20))); * 10
MariaDB [lyshark]> select * from book inner join class on book.card = class.card;
-- 左衔接的特性是左表全都有,衔接右表的部分
-- 左右链接总有一张表是用来驱动的,左衔接链接的是右表,如下左表class右表是book
MariaDB [lyshark]> select * from class left join book on book.card = class.card;
-- 左衔接状况下,将索引树立在右表上面功率是最高的,如下右表是book
MariaDB [lyshark]> alter table book add index left_index(card);
-- 右链接,需要将索引加到左面表上,也便是加到class表的card字段上.
MariaDB [lyshark]> show index from book;
MariaDB [lyshark]> drop index left_index on book;
MariaDB [lyshark]> select * from class right join book on book.card = class.card;
MariaDB [lyshark]> alter table class add index right_index(card);
三张表索引优化
create table phone
(
phoneid int(10) auto_increment,
card int(10) not null,
primary key(phoneid)
);
MariaDB [lyshark]> insert into phone(card) values(floor(1+(rand()*20))); * 10
-- 最简单的链接查询
MariaDB [lyshark]> select * from class inner join book on class.card=book.card inner join phone on book.card=phone.card;
-- 左衔接查询
MariaDB [lyshark]> explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
alter table book add index book_left_index(card);
alter table phone add index phone_left_index(card);
-- join 句子优化主张
-- 1. 尽或许削减join句子中的NestedLoop的循环次数: 永久用小成果集,驱动大的成果集.
-- 2. 优先优化NestedLoop的内层循环
-- 确保join句子中被驱动表上join条件字段现已被索引.
-- 当无法确保被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝惜joinbuffer 的设置.
-- 假如是三表,左查询,那么咱们应该将索引,树立在左衔接表中.
-- 右衔接查询
MariaDB [lyshark]> explain select * from class right join book on class.card=book.card right join phone on book.card=phone.card;
alter table book add index book_left_index(card);
alter table phone add index phone_left_index(card);
处理中文乱码问题:
[root@localhost mysql]# cp -a /usr/share/mysql/my-huge.cnf /etc/my.cnf
[root@localhost mysql]# vim /etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
-- 查询字符集编码
MariaDB [lyshark]> select * from information_schema.character_sets;
MariaDB [lyshark]> show character set like 'utf8%';
MariaDB [lyshark]> show variables like 'character_set%';
-- 设置大局字符集
set global character_set_client=utf8;
set global character_set_connection=utf8;
set global character_set_database=utf8;
set global character_set_results=utf8;
set global character_set_server=utf8;
-- 更新指定表为utf8格局
MariaDB [lyshark]> alter database lyshark default character set utf8 collate utf8_general_ci;
MariaDB [lyshark]> alter table lyshark.user convert to character set utf8 collate utf8_general_ci;
-- 过错日志
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-error="/var/log/mariadb/mariadb.log"
MariaDB [(none)]> show variables like 'log%';
[root@localhost ~]# cat /var/log/mariadb/mariadb.log |head -n 10
MariaDB [(none)]> flush logs;
-- 二进制日志
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin="/tmp" #设置敞开日志,也可不指定日志保存方位
expire_logs_days = 10 #设置日志主动清理天数
max_binlog_size = 100M #界说了单个文件的大小限制
-- 删去日志
MariaDB [(none)]> show binary logs;
MariaDB [(none)]> purge master logs to "mariadb-bin.000001";
MariaDB [(none)]> purge master logs before "20180101";
[root@localhost ~]# mysqlbinlog mariadb-bin.000001
-- 慢查询日志
MariaDB [lyshark]> show variables like '%slow_query_log%';
MariaDB [lyshark]> set global slow_query_log=1;
MariaDB [lyshark]> show variables like '%long_query_time%';
MariaDB [lyshark]> set global long_query_time=3;
MariaDB [lyshark]> show global status like '%Slow_queries%';
[root@localhost mysql]# cat /var/lib/mysql/localhost-slow.log
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-slow-queries="/var/lib/mysql/localhost-slow.log"
long_query_time=10
log_output=FILE
-s 排序方法
-c 访问次数
-l 确定时刻
-r 回来记载
-t 查询时刻
-al 均匀确定时刻
-ar 均匀回来记载数
-at 均匀查询时刻
-t 回来前面多少条记载
-g 匹配正则
-- 得到回来记载集最多的10个SQL
[root@localhost mysql]# mysqldumpslow -s -r -t 10 /var/lib/mysql/localhost-slow.log
-- 得到访问次数最多的10个SQL
[root@localhost mysql]# mysqldumpslow -s -c -t 10 /var/lib/mysql/localhost-slow.log
-- 得到依照时刻排序的前十条里面含有左衔接的查询句子.
[root@localhost mysql]# mysqldumpslow -s -t -t 10 -g 'left join' /var/lib/mysql/localhost-slow.log
show variables like 'profiling';
set profiling=on;
MariaDB [lyshark]> select * from tbl_emp;
MariaDB [lyshark]> show profiles; // 查询体系中履行的sql
-- 查询3号记载中的问题，得到3号句子的查询生命周期。
MariaDB [lyshark]> show profile cpu,block io for query 3;
