数据库系列课程（12）-MySQL数据库优化及注意事项

1.MySQL数据库优化方案

Mysql的优化，大体可以分为三部分：

• 索引的优化
• 慢查询优化
• 表优化

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

还可以使用腾讯云MySQL数据库监控：

• 腾讯云数据库地址:https://console.cloud.tencent.com/cdb
• 实例监控https://console.cloud.tencent.com/cdb?ins=4-cdb-0g6a8qct

2.MySQL数据库配置慢查询

参数说明:

• 「slow_query_log」 慢查询开启状态
• 「slow_query_log_file」 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
• 「long_query_time」 查询超过多少秒才记录

例子：

1. 查询慢查询配置

show variables like 'slow_query%';

2. 查询慢查询限制时间

show variables like 'long_query_time';

3. 将 slow_query_log 全局变量设置为“ON”状态

set global slow_query_log='ON'; 

4. 查询超过1秒就记录

set global long_query_time=1;

查询日志：cat /var/lib/mysql/localhost-slow.log
重启MySQL服务：service mysqld restart

3.索引注意事项

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

CREATE TABLE `user_details` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user_details` ADD INDEX user_name_index ( `user_name` )


insert into user_details values(1,'ylw1','15921009245');
insert into user_details values(2,'ylw2','15921009245');
insert into user_details values(3,'ylw3','15921009245');
insert into user_details values(4,'ylw4','15921009245');
insert into user_details values(5,'ylw5','15921009245');
insert into user_details values(6,'ylw6','15921009245');


EXPLAIN select * from user_details WHERE id=1
EXPLAIN select * from user_details WHERE id=1 and  user_name='ylw1';
EXPLAIN select * from user_details WHERE id like '%sss' 
EXPLAIN select * from user_details WHERE id like '%1' 
EXPLAIN select * from user_details WHERE user_name like '1%' 
EXPLAIN select * from user_details WHERE user_name =1; 
EXPLAIN select * from user_details WHERE user_name ='1';

1. 索引无法存储null值。

2. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。

3. 对于多列索引，不是使用的第一部分，则不会使用索引。

4. like查询以%开头。

5. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。

6. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

4. 联合索引为什么需要遵循左前缀原则？

对于多列索引，不是使用的第一部分，则不会使用索引：

CREATE TABLE `user_details` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (id,user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into user_details values(1,'ylw1','15921009245');
insert into user_details values(1,'ylw2','15921009245');
insert into user_details values(2,'ylw1','15921009245');
insert into user_details values(2,'ylw2','15921009245');
insert into user_details values(3,'ylw1','15921009245');
insert into user_details values(3,'ylw2','15921009245');


EXPLAIN select * from user_details WHERE id=1
EXPLAIN select * from user_details WHERE id=1 and  user_name='ylw1';
EXPLAIN select * from user_details WHERE  user_name='ylw1';
EXPLAIN select * from user_details WHERE  user_name='ylw1' and  id=1

(1,ylw1 1,ylw2),(2,ylw1 2,ylw2),(3,ylw1 3,ylw2)

如果在一张表中，存在联合索引的话，在根据条件查询的时候必须要加上第一个索引条件。

EXPLAIN select * from user_details WHERE id=1 and  user_name=‘ylw1’;---索引生效
EXPLAIN select * from user_details WHERE  user_name=‘ylw1’; 索引是不生效的

因为索引底层采用B+树叶子节点顺序排列，必须通过左前缀索引才能定位到具体的节点范围。

(1,ylw1 1,ylw2),(2,ylw1 2,ylw2),(3,ylw1 3,ylw2)

5. 表分库为什么能够提高数据库查询效率？

分表分库为什么提高查询的效率？因为会将一张表的数据拆分成多个n张表进行存放，然后再使用第三方中间件（MyCat或者Sharding-JDBC）并行同时查询，然后在交给第三方中间进行组合返回给客户端。

6. MySQLb+树能够存放多少字节数据

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

这样做的理论依据是计算机科学中著名的局部性原理：

• 当一个数据被用到时，其附近的数据也通常会马上被使用。
• 程序运行期间所需要的数据通常比较集中。
• 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
• 预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存 储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统 会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行(一般操作系统一页大小为4kb；getconf PAGE_SIZE)。

假设一个节点为1页:

• 如果从磁盘读取超过1页大小，根据局部性原理与磁盘预读会读出2页大小
• 如果从磁盘读取小于1页大小，根据局部性原理与磁盘预读会读出1页大小

根据以上规则，如果读取整哈是页的倍数，这样就可以不用浪费，所以B+树的每一个节点是页的倍数是最佳的。

在MySQL中我们的InnoDB页的大小默认是16k，当然也可以通过参数设置：

show variables like 'innodb_page_size';//16384/1024=16kb;

MySQL b+树能够存放多少字节数据:

假设一行为1kb，那么一页可以读取16行数据，一个叶子节点可以存放16条数据
那么非叶子节点存放多少条数据？非叶子节点存放索引值(bigint 8b)和指针(6b)

那么一页 ： 16*1024/(8+6)=1170指针

B+树高度为2： 1170*16=18720条数据
B+树高度为3： 1170*1170*16=21902400 条数据

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

7. 为什么MySQL底层使用b+树？

B+树索引 ：具有范围查找和前缀查找的能力，相当于二分查找。

Hash索引 ：只能支持等于查询，无法支持范围查询