mysql树状查询优化_mysql 优化

编辑推荐:

文章讲解数据结构Hash、平衡二叉树、B树、B+树区别

，Myisam与Innodb B+树的区别

，MySQL中的索引什么数据结构

，B+树中的节点到底存放多少,希望对您有所帮助,

本文来自csdn，由火龙果软件Delores编辑、推荐。

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_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');

总结

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

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，所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。

缺点：因为有冗余节点数据，会比较占内存。