devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL 索引与性能调优_mysql索引和调优

作者：IT小白 | 2024-05-20 20:09:50

赞

踩

mysql索引和调优

索引用于快速找出在某个列中有一特定值的行,如果不使用索引MySQL必须从第l条记录开始读完整个表,直到找出相关的行.表越大,查询数据所花费的时间越多,如果表中查询的列有一个索引,MySQL能快速到达某个位置去搜寻数据文件,而不必查看所有数据,可加快数据查询的查询速度提高效率,索引可在创建表时增加,也可动态调整已有表.

通俗的来说索引是一种数据结构,是帮助MySQL进行高效检索数据的一种机制,你可以简单理解为排好序的快速查找数据结构,

索引都是B+树(多路搜索树)结构组织的索引,包括聚集索引,复合索引,前缀索引,唯一索引,都是b+树索引.

优势：1.提高数据检索效率，降低数据库IO成本，降低CPU消耗。 劣势: 2.索引是一张表，索引也占空间，虽然提高了查询速度，但也会降低表的更新速度，如果新加数据，索引也会自动更新。

CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时。 IO磁盘平静，服务器硬件平静。

创建普通索引: 在创建表时指定索引类型,如下在u_id字段添加一个普通索引,该索引作用只是加对快数据的访问速度.

  1. MariaDB [lyshark]> create table book
  2.     -> (
  3.     -> u_id int not null,
  4.     -> u_book varchar(20) not null,
  5.     -> index(u_id)
  6.     -> );
  7.  
  8. -- 使用show index语句查看指定表中创建的索引
  9. MariaDB [lyshark]> show index from book;
  10. MariaDB [lyshark]> show create table book \G;
  11. MariaDB [lyshark]> explain select * from book where u_id=1 \G;
  12. 复制代码

创建唯一索引: 创建唯一索引的主要原因是减少查询索引列操作的执行时间,尤其是对比较庞大的数据表.它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一.

  1. MariaDB [lyshark]> create table table_1
  2.     -> (
  3.     -> id int not null,
  4.     -> name char(30) not null,
  5.     -> unique index UniqIdx(id)
  6.     -> );
  7. Query OK, 0 rows affected (0.02 sec)
  8.  
  9. MariaDB [lyshark]> show create table table_1 \G;
  10. *************************** 1. row ***************************
  11.        Table: table_1
  12. Create Table: CREATE TABLE `table_1` (
  13.   `id` int(11) NOT NULL,
  14.   `name` char(30) NOT NULL,
  15.   UNIQUE KEY `UniqIdx` (`id`)             #id字段已经成功建立了一个名为UniqIdx的唯一索引
  16. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  17. 1 row in set (0.00 sec)
  18. 复制代码

创建单列索引: 单列索引是在数据表中的某一个字段上创建的索引,一个表中可以创建多个单列索引.前面两个例子中创建的索引都为单列索引.

  1. MariaDB [lyshark]> create table table_2
  2.     -> (
  3.     -> id int not null,
  4.     -> name char(50) null,
  5.     -> index SingleIdx(name(20))
  6.     -> );
  7. Query OK, 0 rows affected (0.03 sec)
  8.  
  9. MariaDB [lyshark]> show create table table_2 \G;
  10. *************************** 1. row ***************************
  11.        Table: table_2
  12. Create Table: CREATE TABLE `table_2` (
  13.   `id` int(11) NOT NULL,
  14.   `name` char(50) DEFAULT NULL,
  15.   KEY `SingleIdx` (`name`(20))          #name字段上已经成功建立了一个单列索引,名称为SingleIdx
  16. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  17. 1 row in set (0.00 sec)
  18. 复制代码

创建组合索引: 组合索引就是在多个字段上创建多个索引.

  1. MariaDB [lyshark]> create table table_3
  2.     -> (
  3.     -> id int not null,
  4.     -> name char(30) not null,
  5.     -> age int not null,
  6.     -> info varchar(255),
  7.     -> index MultiIdx(id,name,age)
  8.     -> );
  9. 复制代码

创建全文索引: 全文索引可以用于全文搜索,全文索引适合用于大型数据集,目前只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列创建索引.索引总是对整个列进行,不支持局部(前缀)索引.

  1. -- 注意：MySQL5.7默认存储引擎室是InnoDB,在这里我们要改成MyISAM,不然索引会报错
  2. MariaDB [lyshark]> create table table_4(
  3.     -> id int not null,
  4.     -> name char(40) not null,
  5.     -> age int not null,
  6.     -> info varchar(255),
  7.     -> fulltext index FullTxtIdx(info)
  8.     -> )engine=MyISAM;
  9. 复制代码

创建空间索引: 空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为空,可以看到,table_5表的g字段上创建了名称为spatIdex的空间索引,注意:创建时间指定空间类型字段值的非空约束,并且表的存储引擎必须为MyISAM.

  1. MariaDB [lyshark]> create table table_5
  2.     -> (
  3.     -> g geometry not null,
  4.     -> spatial index spatIdx(g)
  5.     -> )engine=MyISAM;
  6. 复制代码

添加索引: 上面的几种形式都是在新建表中添加索引,如果需要在已存在表中添加则需要使用以下命令了.

  1. MariaDB [lyshark]> create table book
  2.     -> (
  3.     -> bookid int not null,
  4.     -> bookname varchar(255) not null,
  5.     -> authors varchar(255) not null,
  6.     -> info varchar(255) null,
  7.     -> comment varchar(255) null,
  8.     -> year_public year not null
  9.     -> );
  10.  
  11. -- 添加普通索引
  12. MariaDB [lyshark]> alter table book add index BKNameIdx(bookname(30));
  13.  
  14. -- 添加唯一索引
  15. MariaDB [lyshark]> alter table book add unique index UniqidIdx(bookId);
  16.  
  17. -- 添加单列索引
  18. MariaDB [lyshark]> alter table book add index BkcmtIdx(comment(50));
  19.  
  20. -- 添加组合索引
  21. MariaDB [lyshark]> alter table book add index BkAuAndInfoIdx(authors(30),info(50));
  22.  
  23. -- 通过索引名字删除索引
  24. MariaDB [lyshark]> alter table book drop index UniqidIdx;
  25. MariaDB [lyshark]> alter table book drop index BKNameIdx;
  26. 复制代码

explain 字段的情况:

  1. MariaDB [lyshark]> explain select s_name,s_city from suppliers where s_id IN (select Gid from lyshark where Uid='a1');
  2. +------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
  3. | id   | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
  4. +------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
  5. |    1 | PRIMARY     | lyshark   | const | PRIMARY       | PRIMARY | 30      | const |    1 |       |
  6. |    1 | PRIMARY     | suppliers | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
  7. +------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
  8.  
  9. MariaDB [lyshark]> explain select Course.CID,Course.Cname from Course join(
  10. select CID from lyshark.StudentScore where SID = (select SID from lyshark.Student where Sname='周梅')
  11. )as StudentScore on Course.CID = StudentScore.CID;
  12. +---------+-------------+--------------+------+---------------+------+---------+------+------+----------+
  13. | id      | select_type | table        | type | possible_keys | key     | key_len | ref  | rows | Extra |
  14. +---------+-------------+--------------+------+---------------+------+---------+------+------+----------+
  15. |    1(1) | SIMPLE      | Course       | ALL  | NULL          | NULL | NULL    | NULL |    3 |          |
  16. |    1(2) | SIMPLE      | StudentScore | ALL  | NULL          | NULL | NULL    | NULL |   18 |          |
  17. |    3    | SUBQUERY    | Student      | ALL  | NULL          | NULL | NULL    | NULL |   10 |          |
  18. +---------+-------------+--------------+------+---------------+------+---------+------+------+----------+
  19.  
  20. ID字段的理解:
  21. 1.当ID字段相同的情况下执行数据是从上到下,例如第一张表中会由上至下执行下来.
  22. 2.当ID不同的情况,如果是子查询,id越大的将在最前面被执行,例如第二张表执行顺序为3->1(1)->1(2)
  23. 复制代码

select_type

  1. MariaDB [lyshark]> explain select * from tbl_emp a left join tbl_dept b on a.deptld=b.id where b.id is null
  2.     -> union
  3.     -> select * from tbl_emp a right join tbl_dept b on a.deptld = b.id where a.deptld is null;
  4. +------+--------------+------------+--------+---------------+---------+---------+------------------+------+---------+
  5. | id   | select_type  | table      | type   | possible_keys | key     | key_len | ref              | rows | Extra   |
  6. +------+--------------+------------+--------+---------------+---------+---------+------------------+------+---------+
  7. |    1 | PRIMARY      | a          | ALL    | NULL          | NULL    | NULL    | NULL             |    8 |         |
  8. |    1 | PRIMARY      | b          | eq_ref | PRIMARY       | PRIMARY | 4       | lyshark.a.deptld |    1 |         |
  9. |    2 | UNION        | b          | ALL    | NULL          | NULL    | NULL    | NULL             |    5 |         |
  10. |    2 | UNION        | a          | ALL    | fk_dept_id    | NULL    | NULL    | NULL             |    8 |         |
  11. | NULL | UNION RESULT | <union1,2> | ALL    | NULL          | NULL    | NULL    | NULL             | NULL |         |
  12. +------+--------------+------------+--------+---------------+---------+---------+------------------+------+---------+
  13.  
  14. SIMPLE       => 标志着简单的select查询请求,查询中不包含子查询或者union查询.
  15. PRIMARY      => 查询中任何复杂的查询中,最外层的查询语句,就是最后加载的语句.
  16. SUBQUERY     => 子查询类型,在select或where列表中包含了子查询.
  17. DERIVED      => 在FROM列表中包含子查询,会被标记为DERIVED(衍生),此时会递归执行子查询,并存储在临时表中.
  18. UNION        => 若第二个SELECT出现在UNION之后,则标记为UNION.
  19. UNION RESULT => 从UNION表中获取结果的SELECT
  20. 复制代码

type 访问类型排列，只要能够达到ref，range级别就已经不错了，性能效率。

  1. system -> const -> eq_ref -> ref -> range ->index -> all
  2.  
  3.  
  4. system -> 表中只有一条记录，这是const类型的特里，平时不会出现。
  5.  
  6. const  -> 主键唯一索引：表示通过索引一次就找到数据，例如查询一个常量。
  7. MariaDB [lyshark]> explain select * from lyshark where Uid="a1";
  8. +------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
  9. | id   | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
  10. +------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
  11. |    1 | SIMPLE      | lyshark | const | PRIMARY       | PRIMARY | 30      | const |    1 |       |
  12. +------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
  13.  
  14.  
  15. eq_ref -> 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描。
  16.  
  17. ref-> 非唯一性索引扫描，返回匹配某个单独值的所有行，被之上也是一种索引访问。查找扫描混合体
  18.  
  19. MariaDB [lyshark]> create table t1(col1 int,col2 int);
  20. MariaDB [lyshark]> alter table t1 add index idx_col1_col2(col1,col2);
  21. MariaDB [lyshark]> explain select * from t1 where col1=1;
  22. +------+-------------+-------+------+---------------+---------------+---------+-------+------+--------+
  23. | id   | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra  |
  24. +------+-------------+-------+------+---------------+---------------+---------+-------+------+--------+
  25. |    1 | SIMPLE      | t1    | ref  | idx_col1_col2 | idx_col1_col2 | 5       | const |    1 |        |
  26. +------+-------------+-------+------+---------------+---------------+---------+-------+------+--------+
  27.  
  28. range -> 范围扫描，只检索给定范围的行，key列显示使用了那个索引。 where，between，<>，in 等查询中使用。
  29.  
  30. explain select * from t1 where col1 between 1 and 2;
  31. explain select * from t1 where col1 in (1,2,3);
  32.  
  33. index -> 全索引扫描，全表索引扫描，比all要好一些。
  34. MariaDB [lyshark]> explain select * from t1;
  35.  
  36. all -> 全表扫描。最差的性能。
  37. 复制代码

possible_keys，key 是否使用到了索引，possible_keys 显示可能

  1. possible_keys => 显示可能应用在这张表中的索引，一个或多个，该索引会被列出，但不一定被实际查询使用。
  2. key => 实际应用到本次查询的索引类型。最重要的。如果为NULL，则说明没有使用索引。
  3.  
  4.  
  5. MariaDB [lyshark]> explain select col1,col2 from t1;
  6. +------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  7. | id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra |
  8. +------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  9. |    1 | SIMPLE      | t1    | index | NULL          | idx_col1_col2 | 10      | NULL |    1 |       |
  10. +------+-------------+-------+-------+---------------+---------------+---------+------+------+-------+
  11.  
  12.  
  13. ------------------------------------------------------------------------------
  14. 查询中若使用了覆盖索引，则该索引仅出现key列表中 覆盖索引以下就是原理
  15.  
  16. MariaDB [lyshark]> create table t1(col1 int,col2 int,col3 int);
  17. MariaDB [lyshark]> alter table t1 add index idx_col1_col2(col1,col2);
  18.  
  19. -- 建立的索引与，查询的行数，必须一致，col1,col2是有索引的。
  20. MariaDB [lyshark]> explain select col1,col2 from t1;
  21. +------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  22. | id   | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra   |
  23. +------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  24. |    1 | SIMPLE      | t1    | index | NULL          | idx_col1_col2 | 10      | NULL |    1 | Using  |
  25. +------+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
  26.  
  27. -- 扫描三个值，不会出现使用索引的情况。
  28. MariaDB [lyshark]> explain select col1,col2,col3 from t1;
  29. +------+-------------+-------+------+---------------+------+---------+------+------+-------+
  30. | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  31. +------+-------------+-------+------+---------------+------+---------+------+------+-------+
  32. |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
  33. +------+-------------+-------+------+---------------+------+---------+------+------+-------+
  34. 复制代码

possible_keys，key,ken_len

  1. key_len 表示索引中使用的字节数，这个长度用的越少越好，kenLen长度是根据表的定义计算得出，而不是表中数据检索出的。
  2.  
  3.  
  4.  
  5.  
  6. ref 显示索引的那一列被使用了，如果可能的话，是一个常数，那些列或常量被用于查找索引列上的值。定义了引用了那些库。
  7.  
  8. rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，该值当然也是越小越好。 每张表有多少行被优化器查询。
  9.  
  10. MariaDB [lyshark]> explain select * from lyshark;
  11. +------+-------------+---------+------+---------------+------+---------+------+------+-------+
  12. | id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
  13. +------+-------------+---------+------+---------------+------+---------+------+------+-------+
  14. |    1 | SIMPLE      | lyshark | ALL  | NULL          | NULL | NULL    | NULL |   17 |       |
  15. +------+-------------+---------+------+---------------+------+---------+------+------+-------+
  16. 1 row in set (0.00 sec)
  17.  
  18.  
  19. MariaDB [lyshark]> create table tt1(id int primary key,col1 varchar(10),col2 varchar(10));
  20. MariaDB [lyshark]> create table tt2(id int primary key,col1 varchar(10),col2 varchar(10));
  21. MariaDB [lyshark]> create index idt_col1_col2 on tt2(col1,col2);
  22.  
  23. MariaDB [lyshark]> explain select * from tt1,tt2 where tt1.id = tt2.id and tt2.col1 = 'admin';
  24. +------+-------------+-------+--------+-----------------------+---------+---------+----------------+------+-------------+
  25. | id   | select_type | table | type   | possible_keys         | key     | key_len | ref            | rows | Extra       |
  26. +------+-------------+-------+--------+-----------------------+---------+---------+----------------+------+-------------+
  27. |    1 | SIMPLE      | tt1   | ALL    | PRIMARY               | NULL    | NULL    | NULL           |    1 |             |
  28. |    1 | SIMPLE      | tt2   | eq_ref | PRIMARY,idt_col1_col2 | PRIMARY | 4       | lyshark.tt1.id |    1 | Using where |
  29. +------+-------------+-------+--------+-----------------------+---------+---------+----------------+------+-------------+
  30. 2 rows in set (0.00 sec)
  31. 复制代码

extra 扩展列

using filesort 产生了文件内排序，完蛋了，mysql无法使用索引进行排序，使用了外部的索引排序，而不是按照表内的索引顺序进行读取。mysql无法利用索引完成排序，操作成为文件排序。

  1. MariaDB [lyshark]> create table tab1(id int primary key,col1 int,col2 int,col3 int);
  2. MariaDB [lyshark]> insert into tab1 values(1,1,2,3),(2,4,5,6),(3,7,8,9);
  3. MariaDB [lyshark]> create index tab1_col1_col2_col3 on tab1(col1,col2,col3);
  4.  
  5. MariaDB [lyshark]> explain select col1 from tab1 where col1 order by col3 \G
  6. *************************** 1. row ***************************
  7.            id: 1
  8.   select_type: SIMPLE
  9.         table: tab1
  10.          type: index
  11. possible_keys: tab1_col1_col2_col3
  12.           key: tab1_col1_col2_col3
  13.       key_len: 15
  14.           ref: NULL
  15.          rows: 3
  16.         Extra: Using where; Using index; Using filesort
  17. 1 row in set (0.00 sec)
  18.  
  19. MariaDB [lyshark]> explain select col1 from tab1 where col1 order by col2,col3 \G
  20.  
  21. 查询使用索引，没问题的，只是说，我们没有针对order by 建立排序索引，或者是建立了索引，你没用上！！
  22.  
  23.  
  24. 以下我们加上全部索引字段，从此下面这条sql性能更高了。
  25. MariaDB [lyshark]> explain select col1 from tab1 where col1 order by col1,col2,col3 \G
  26. *************************** 1. row ***************************
  27.            id: 1
  28.   select_type: SIMPLE
  29.         table: tab1
  30.          type: index
  31. possible_keys: tab1_col1_col2_col3
  32.           key: tab1_col1_col2_col3
  33.       key_len: 15
  34.           ref: NULL
  35.          rows: 3
  36.         Extra: Using where; Using index
  37. 1 row in set (0.00 sec)
  38.  
  39.  
  40. 如果可以，尽快优化。
  41. 复制代码

using temporary 彻底完犊子，这个会新建了一个内部临时表，然后操作完后再把临时表删除，动作更凶险。

使用临时表保存中间结果，mysql在对查询结果排序时使用临时表，常用于排序order by 和分组查询group by .

  1. MariaDB [lyshark]> explain select * from tab1 where col1 in(1,2,3) group by col2 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: tab1
  6.          type: index
  7. possible_keys: tab1_col1_col2_col3
  8.           key: tab1_col1_col2_col3
  9.       key_len: 15
  10.           ref: NULL
  11.          rows: 3
  12.         Extra: Using where; Using index; Using temporary; Using filesort  彻底完犊子
  13. 1 row in set (0.00 sec)
  14.  
  15. 解决办法，你给我建立的索引个数和顺序，一定要按顺序来。
  16. MariaDB [lyshark]> explain select * from tab1 where col1 in(1,2,3) group by col1,col2 \G
  17. *************************** 1. row ***************************
  18.            id: 1
  19.   select_type: SIMPLE
  20.         table: tab1
  21.          type: index
  22. possible_keys: tab1_col1_col2_col3
  23.           key: tab1_col1_col2_col3
  24.       key_len: 15
  25.           ref: NULL
  26.          rows: 3
  27.         Extra: Using where; Using index   解决了。
  28. 1 row in set (0.00 sec)
  29. 复制代码

using index 这种情况是好事，表示相应的操作使用了 covering index 使用了覆盖索引，效率不错，。

如果同时出现了using where 表示索引被用来执行索引键值的查找。

如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。

  1. MariaDB [lyshark]> explain select col1,col2 from tab1;
  2. +------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
  3. | id   | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra       |
  4. +------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
  5. |    1 | SIMPLE      | tab1  | index | NULL          | tab1_col1_col2_col3 | 15      | NULL |    3 | Using index |  using 表明直接从索引上找到了数据。
  6. +------+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. 复制代码

覆盖索引，就是说你建立的复合索引是 x,y,z 那么你在查询是应该要覆盖这些索引，这样才能让索引，发挥其最大的性能，否则索引等于白建立。

覆盖索引，尽量不要写星号，这种低效率的查询。

  1. select * from lyshark where id=1;     -- 完蛋的。
  2. select x,y,z from lyshark wehre id =1;   -- 覆盖到主键上，查询效率提升很多。
  3. 复制代码

using where -> 使用了where using join buffer -> 用到了缓存buffer

实现单表索引优化

  1. create table if not exists article(
  2. id int(10) unsigned not null primary key auto_increment,
  3. author_id int(10) unsigned not null,
  4. category_id int(10) unsigned not null,
  5. views int(10) unsigned not null,
  6. comments int(10) unsigned not null,
  7. title varbinary(255) not null,
  8. content text not null );
  9.  
  10. 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');
  11.  
  12.  
  13. MariaDB [lyshark]> explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;
  14.  
  15. -- 创建复合索引 all 变为了range 只解决了全表扫描问题
  16. MariaDB [lyshark]> create index idx_article_ccv on article(category_id,comments,views);
  17.  
  18. -- 最后一个完整版的
  19. MariaDB [lyshark]> drop index idx_article_ccv on article;
  20. MariaDB [lyshark]> create index idx_article_cv on article(category_id,views);
  21. 复制代码

实现两表索引优化

  1. create table class
  2. (
  3.     id int(10) auto_increment,
  4.     card int(10) not null,
  5.     primary key(id)
  6. );
  7. create table book
  8. (
  9.     bookid int(10) auto_increment,
  10.     card int(10) not null,
  11.     primary key(bookid)
  12. );
  13.  
  14. MariaDB [lyshark]> insert into class(card) values(floor(1+(rand()*20)));  * 10
  15. MariaDB [lyshark]> insert into book(card) values(floor(1+(rand()*20)));  * 10
  16. MariaDB [lyshark]> select * from book inner join class on book.card = class.card;
  17.  
  18. -- 左连接的特性是左表全都有,连接右表的部分
  19. -- 左右链接总有一张表是用来驱动的,左连接链接的是右表,如下左表class右表是book
  20. MariaDB [lyshark]> select * from class left join book on book.card = class.card;
  21.  
  22. -- 左连接情况下,将索引建立在右表上面效率是最高的,如下右表是book
  23. MariaDB [lyshark]> alter table book add index left_index(card);
  24.  
  25. -- 右链接,需要将索引加到左边表上,也就是加到class表的card字段上.
  26. MariaDB [lyshark]> show index from book;
  27. MariaDB [lyshark]> drop index left_index on book;
  28.  
  29. MariaDB [lyshark]> select * from class right join book on book.card = class.card;
  30. MariaDB [lyshark]> alter table class add index right_index(card);
  31. 复制代码

三张表索引优化

  1. create table phone
  2. (
  3.     phoneid int(10) auto_increment,
  4.     card int(10) not null,
  5.     primary key(phoneid)
  6. );
  7.  
  8. MariaDB [lyshark]> insert into phone(card) values(floor(1+(rand()*20)));  * 10
  9.  
  10.  
  11. -- 最简单的链接查询
  12. MariaDB [lyshark]> select * from class inner join book on class.card=book.card inner join phone on book.card=phone.card;
  13.  
  14.  
  15. -- 左连接查询
  16. MariaDB [lyshark]> explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
  17.  
  18. alter table book add index book_left_index(card);
  19. alter table phone add index phone_left_index(card);
  20.  
  21. -- join 语句优化建议
  22.  
  23. -- 1. 尽可能减少join语句中的NestedLoop的循环次数: 永远用小结果集,驱动大的结果集.
  24. -- 2. 优先优化NestedLoop的内层循环
  25. -- 保证join语句中被驱动表上join条件字段已经被索引.
  26.  
  27. -- 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝惜joinbuffer 的设置.
  28. -- 如果是三表,左查询,那么我们应该将索引,建立在左连接表中.
  29.  
  30.  
  31. -- 右连接查询
  32. MariaDB [lyshark]> explain select * from class right join book on class.card=book.card right join phone on book.card=phone.card;
  33.  
  34. alter table book add index book_left_index(card);
  35. alter table phone add index phone_left_index(card);
  36. 复制代码

解决中文乱码问题:

  1. [root@localhost mysql]# cp -a /usr/share/mysql/my-huge.cnf /etc/my.cnf
  2. [root@localhost mysql]# vim /etc/my.cnf
  3. [client]
  4. default-character-set=utf8
  5.  
  6. [mysqld]
  7. character_set_server=utf8
  8. character_set_client=utf8
  9. collation-server=utf8_general_ci
  10.  
  11. [mysql]
  12. default-character-set=utf8
  13.  
  14. -- 查询字符集编码
  15. MariaDB [lyshark]> select * from information_schema.character_sets;
  16. MariaDB [lyshark]> show character set like 'utf8%';
  17. MariaDB [lyshark]> show variables like 'character_set%';
  18.  
  19. -- 设置全局字符集
  20. set global character_set_client=utf8;
  21. set global character_set_connection=utf8;
  22. set global character_set_database=utf8;
  23. set global character_set_results=utf8;
  24. set global character_set_server=utf8;
  25.  
  26. -- 更新指定表为utf8格式
  27. MariaDB [lyshark]> alter database lyshark default character set utf8 collate utf8_general_ci;
  28. MariaDB [lyshark]> alter table lyshark.user convert to character set utf8 collate utf8_general_ci;
  29. 复制代码
  1. -- 错误日志
  2. [root@localhost ~]# vim /etc/my.cnf
  3.  
  4. [mysqld]
  5. log-error="/var/log/mariadb/mariadb.log"
  6.  
  7. MariaDB [(none)]> show variables like 'log%';
  8. [root@localhost ~]# cat /var/log/mariadb/mariadb.log |head -n 10
  9.  
  10. MariaDB [(none)]> flush logs;
  11.  
  12.  
  13. -- 二进制日志
  14. [root@localhost ~]# vim /etc/my.cnf
  15.  
  16. [mysqld]
  17. log-bin="/tmp"              #设置开启日志,也可不指定日志保存位置
  18. expire_logs_days = 10       #设置日志自动清理天数
  19. max_binlog_size = 100M      #定义了单个文件的大小限制
  20.  
  21.  
  22. -- 删除日志
  23. MariaDB [(none)]> show binary logs;
  24. MariaDB [(none)]> purge master logs to "mariadb-bin.000001";
  25. MariaDB [(none)]> purge master logs before "20180101";
  26.  
  27. [root@localhost ~]# mysqlbinlog mariadb-bin.000001
  28.  
  29.  
  30.  
  31.  
  32.  
  33. -- 慢查询日志
  34. MariaDB [lyshark]> show variables like '%slow_query_log%';
  35. MariaDB [lyshark]> set global slow_query_log=1;
  36.  
  37. MariaDB [lyshark]> show variables like '%long_query_time%';
  38. MariaDB [lyshark]> set global long_query_time=3;
  39. MariaDB [lyshark]> show global status like '%Slow_queries%';
  40.  
  41. [root@localhost mysql]# cat /var/lib/mysql/localhost-slow.log
  42. [root@localhost ~]# vim /etc/my.cnf
  43.  
  44. [mysqld]
  45. log-slow-queries="/var/lib/mysql/localhost-slow.log"
  46. long_query_time=10
  47. log_output=FILE
  48.  
  49.  
  50.  
  51. -s 排序方式
  52. -c 访问次数
  53. -l 锁定时间
  54. -r 返回记录
  55. -t 查询时间
  56. -al 平均锁定时间
  57. -ar 平均返回记录数
  58. -at 平均查询时间
  59. -t 返回前面多少条记录
  60. -g 匹配正则
  61.  
  62. -- 得到返回记录集最多的10个SQL
  63. [root@localhost mysql]# mysqldumpslow -s -r -t 10 /var/lib/mysql/localhost-slow.log
  64.  
  65. -- 得到访问次数最多的10个SQL
  66. [root@localhost mysql]# mysqldumpslow -s -c -t 10 /var/lib/mysql/localhost-slow.log
  67.  
  68. -- 得到按照时间排序的前十条里面含有左连接的查询语句.
  69. [root@localhost mysql]# mysqldumpslow -s -t -t 10 -g 'left join' /var/lib/mysql/localhost-slow.log
  70.  
  71. show variables like 'profiling';
  72. set profiling=on;
  73.  
  74. MariaDB [lyshark]> select * from tbl_emp;
  75. MariaDB [lyshark]> show profiles;   // 查询系统中执行的sql
  76. -- 查询3号记录中的问题，得到3号语句的查询生命周期。
  77. MariaDB [lyshark]> show profile cpu,block io for query 3;

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/599118
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号