mysql 优化

1. 需要了解的知识
   1、为什么需要使用索引？
   2、数据结构Hash、平衡二叉树、B树、B+树区别
   3、Myisam与Innodb B+树的区别
   4、MySQL中的索引什么数据结构
   5、B+树中的节点到底存放多少
2. 
3. 
4. 
   mysql 数据与索引文件位置
   
   
   
   mysql 优化三部分 ：索引的优化，sql 慢查询的优化，表的优化

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;
一般慢查询日志是一个随机数字加字母的 如果需要调整测试可以做如下处理
 set global slow_query_log_file ="/var/lib/mysql/localhost-slow.log";
5.查询cat /var/lib/mysql/localhost-slow.log
service mysqld restart

测试慢查询语句

CREATE TABLE `user_details` (
  `id` int(11),
  `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,'testliuliu1','15921009245');
insert into user_details values(2,'testliuliu2','15921009245');
insert into user_details values(3,'testliuliu3','15921009245');
insert into user_details values(4,'testliuliu4','15921009245');
insert into user_details values(5,'testliuliu5','15921009245');
insert into user_details values(6,'testliuliu6','15921009245');


EXPLAIN select * from user_details WHERE id=1
EXPLAIN select * from user_details WHERE id=1 and  user_name='yushengjun1';
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';

测试慢查询语句

CREATE TABLE `user_details` (
  `id` int(11),
  `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,'testliuliu1','15921009245');
insert into user_details values(2,'testliuliu2','15921009245');
insert into user_details values(3,'testliuliu3','15921009245');
insert into user_details values(4,'testliuliu4','15921009245');
insert into user_details values(5,'testliuliu5','15921009245');
insert into user_details values(6,'testliuliu6','15921009245');


EXPLAIN select * from user_details WHERE id=1
EXPLAIN select * from user_details WHERE id=1 and  user_name='testliuliu1';
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';

测试联合索引语句

CREATE TABLE `user_details1` (
  `id` int(11),
  `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_details1 values(1,'testliuliu1','15921009245');
insert into user_details1 values(1,'testliuliu2','15921009245');
insert into user_details1 values(2,'testliuliu1','15921009245');
insert into user_details1 values(2,'testliuliu2','15921009245');
insert into user_details1 values(3,'testliuliu1','15921009245');
insert into user_details1 values(3,'testliuliu2','15921009245');


EXPLAIN select * from user_details1 WHERE id=1
EXPLAIN select * from user_details1 WHERE id=1 and  user_name='testliuliu1';
EXPLAIN select * from user_details1 WHERE  user_name='testliuliu1';
EXPLAIN select * from user_details1 WHERE  user_name='testliuliu1' and  id=1

(1,testliuliu1 1,testliuliu2),(2,testliuliu1 2,testliuliu2),(3,testliuliu1 3,testliuliu2)


测试MYISAM引擎 
CREATE TABLE `user_details2` (
  `id` int(11),
  `user_name` varchar(50) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


insert into user_details2 values(1,'testliuliu1','15921009245');
insert into user_details2 values(2,'testliuliu2','15921009245');
insert into user_details2 values(3,'testliuliu3','15921009245');
insert into user_details2 values(4,'testliuliu4','15921009245');
insert into user_details2 values(5,'testliuliu5','15921009245');
insert into user_details2 values(6,'testliuliu6','15921009245');

参考文章：
https://www.cnblogs.com/yycc/p/7338894.html

测试链接地址
官网首页:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
Avl数据结构https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

总结
全表扫描：会将整张表数据全部扫描一遍，这样的话效率非常低。

Hash索引
优点：通过字段的值计算的hash值，定位数据非常快。
缺点：不支持范围查询
为什么不支持范围查询？
因为底层数据结构是散列的，无法进行比较大小

平衡二叉树 会取一个中间值，中间值左边称为左子树 ，中间值右边称为右子树 。
左子树比中间小，右子树比中间值。

平衡二叉树 查询原理

假设查询10 （需要经历4次IO操作）
1次 从硬盘中读取4 （内存），判断下10>4，取右指针
2次 从硬盘中读取8 （内存），判断下10>8，取右指针
3次 从硬盘中读取9 （内存），判断下10>，取右指针
4次 从硬盘中读取10 （内存），判断下10=10，定位到数据

平衡二叉树 查询效率还可以，缺点：虽然支持范围查询，但是回旋查询效率低。
规律：如果树的高度越高，那么查询IO次数会越多。

如何去减少查询IO次数？
B树在平衡二叉树中，减少树的高度

结论：B树比平衡二叉树减少了一次IO操作

B树查询效率比平衡二叉树效率要高，因为B树的节点中可以有多个元素，从而减少树的高度，减少IO操作，从而提高查询效率，缺点：范围查询效率还是比较低。

B+树 解决范围查询问题、减少IO查询的操作。

B+树相比B树，新增叶子节点与非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只是包含了key，不包含value。

B+树算法： 通过继承了B树的特征，B+树相比B树，新增叶子节点与非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只是包含了key，不包含value。通过非叶子节点查询叶子节点获取对应的value，所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。
缺点：因为有冗余节点数据，会比较占内存。