mysql 优化
mysql执行流程
- sql执行流程图
- 客户端：
- 发送连接请求，然后发送增删改查sql
- 服务端：
- 连接层：提供和客户端连接的服务，在tcp协议下，提供多线程并发的技术，让多个用户登录到mysql中。
- show processlist;查看所有登录到mysql的用户进程
- 服务器：
- 提供了各种借口(增删改查...)，分析器组件会解析用户的sql语句，如果发现sql语句执行的效率较低，会提交给优化器组件进行优化，然后再执行；(查询缓存：把上次搜过的数据，或者把提前存储的数据直接返回，加快效率)；(优化器:mysql query optimizer)
- 存储引擎：
- 存储或者提取数据
- innodb：支持事物处理，支持行锁，支持高并发
- myisam：支持表锁，不支持高并发
- 日志文件
- 产生binlog日志(二进制文件)
##了解：
create table ceshi_table(
id int primary key auto_increment,
name varchar(255)
) engine = myisam auto_increment=2 charset=utf8
mysql> desc ceshi_table;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into ceshi_table values(null,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into ceshi_table values(null,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into ceshi_table values(null,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into ceshi_table values(null,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from ceshi_table;
+----+------+
| id | name |
+----+------+
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+----+------+
4 rows in set (0.00 sec)sql 卡顿原因
- 硬盘读写数据，io延迟高，sql语句性能低，导致sql执行的时间漫长
- 表中的数据没有任何索引，并且数据量较大，也会造成sql语句查询速度慢
- 编写：
select .. from .. join on where .. group by ..having .. order by ..- 解析：
from .. join on where group by having select order by limit ..索引
- 索引(index)概念：
- 是一个树状的数据结构，即(B树结构，分支节点>2)
- 相当于字段的目录，功效是加快查询速度
- 常用树：B树(banlance-tree)，二叉树，红黑树，hash树
二叉树：
B树：
B+树：
- 树节点概念：
- 根节点（最顶级节点）
- 分支节点（父节点，子节点）
- 叶子节点（最后一层存储数据的节点）
- 树的高度（树的层级，理想情况下三级，任何数据最多需要3次查到，支持百万级别的数据查询，追求树的矮胖结构）
- B+树：在相邻的叶子节点上，加入双向联表(指针)，当前叶子节点不但保存了数据，还保存了上下两个节点地址[小范围数据中，加快了查询数据]
- B++树\B*：在相连的分支节点，加入双向链表(指针)，当前叶子节点不但保存了数据，还保存了上下两个节点地址[大范围数据中，加快了查询数据]
- 磁盘块 = block = 数据页 存储数据16k
- myisam和innodb都是b+树结构
#准备
#1.准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50),
first_name char(10),
last_name char(10)
);
#2.创建存储过程，实现批量插入记录
delimiter $$ #申明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
decalare i int default 1;
while(i<300000) do
insert into s1 values(i,'xboyww','man',concat('xboyww',i,'@olydboy'),concat('王',i),concat('文',i));
set i=i+1;
end while;
END$$ #$$结束
delimiter; #重新申明分号为结束符号
#3.查看存储过程
show create procedure auto_insert1\G
#4.调用存储过程
call auto_insert1()innodb 和 myisam 的索引结构
- 聚集索引[innodb存储引擎的特点，myisam不支持]
- 如果有主键，自动以主键创建聚集索引的数据结构(树状结构)
- 如果没有主键，选择唯一键
- 都没有，自动生成隐藏聚集索引，也会分出一个字段占用6各字节长整型；
- 分支节点存储的是索引的最小值，用来划分范围
- 叶子节点上面直接存储真实数据（索引和数据捆绑在一起）
- 在数据量变大的时候，尽量在树层级高度不变的情况下，横向发展，好处：查询次数少，提升效率，减少io阻塞；
- 非聚集索引（辅助索引，二级索引，普通索引）
- 先对创建索引的该字段划分区间进行排序，把索引值分布在叶子节点上
- 存储的是该字段的值以及对应映射出的主键id(primary key)，没有存真实数据
- 通过主键id，再去从其他文件中找数据..
- 两者区别：
- myisam 和 innodb 使用的索引结构都是b+树，但是叶子节点存储的数据不同
- innodb文件结构中只有frm和ibd 直接把数据存在叶子节点上
- myisam文件结构中有frm,myi,myd，叶子节点上存储的索引值，通过索引找id，通过id找数据。
- 性能优化：
- 利用索引查询时，可以增快查询速度，但是增删改速度变慢，会改变树状结构
- 追求尽量让叶子节点存储的数据类型小一点，让高度变矮，让数据页变少。
索引
常用索引
- 单个字段索引
- 主键索引 primary key :非空且唯一
- 唯一索引 unique ：唯一
- 普通索引 index ：单纯加个索引，为了提升查询效率
- 联合索引
• primary key (字段1，字段2...)：联合主键索引
• unique(字段1，字段2..)：联合唯一索引
• index(字段1，字段2..)：联合普通索引应用场景
• 编号：int
• 姓名：varchar(255)
• 身份号：char(18)
• 电话：char(11)
• 地址：varchar(255)
• 备注：text
• 姓：varchar(10)
• 名：varchar(10)
• 编号：主键
• 姓名：普通索引(注意在区别分度高的字段上加)
• 身份证：unique
• 电话：unique
• 备注：全文索引，借助第三方软件sphinx来运行
• 姓和名：联合索引，联合在一起查，加快速度不同的存储引擎支持的数据结构
• innodb：支持b-tree fulltext 不支持hash类型索引结构
• myisam：至此b-tree fulltext 不支持hash类型索引结构
• memory：支持b-tree hash类型 不支持fulltext索引
• hash类型索引：数据放内存中，通过键来获取值，单条数据查询快，一个范围内的数据慢
• b-tree：最理想的三层结构，理论上可支撑百万条数据的查询建立索引
- 方法1，建表的时候，直接创建索引 index 索引名(索引字段)
create table t1(
id int primary key,
name char(10),
index index_name(name)
);- 方法2，建表之后，创建索引 create index 索引名 on 表名(索引字段)
create table t2(
id int primary key,
name char(10)
);
create index index_name on t2(name);- 方法3，改字段变索引 alter table 表名 add index 索引名(索引字段)
create table t3(
id int primary key,
name char(10)
);
alter table t3 add index index_name(name);- 删除所有
drop index index_name on t3;- 正确使用所有
alter table s1 add index index_id(id);
select * from s1 where id = 5;
#发现加索引和不加索引速度差别巨大，加了索引之后,ibd文件变大- 把频繁作为搜素条件的字段作为索引，查单条数据，如果查询的结果是一个大范围中的数据，不能命中索引；表达范围的符号：> < >= <= != like between and .. in
select * from s1 where id >5;
select * from s1 where id <5; #表达一个小范围的数据可以命中- 选一个区分度较高的字段作为索引
#选区分度较低的字段做了索引，在查询数据的时候，先走索引建好的树状结构，在把数据搜出来；因为树状结构中有大量的重复数据，会增加树的高度，反而速度不快，冗余数据过多
#默认系统会把主键或者unique标识的约束，自动创建索引，因为区分度较高，没有冗余数据；
create index index_name on s1(name); #不推荐把分分度不高的字段加索引- 在搜索条件中，不能让索引字段参与计算，不能命中索引
select * from s1 where id*3 = 3000 #id = 1000- 条件当中含有and，sql语句会通过优化器进行优化
#1.如果有and相连，找到第一个有索引的 并且树的高度最矮的字段进行优化
select count(*) from s1 where email = xboyww1000@oldboy;
select count(*) from s1 where email = xboyww1000@oldboy and id=1000;
select count(*) from s1 where email = xboyww1000@oldboy and name='xboyww';
select count(*) from s1 where email = xboyww1000@oldboy and name='xboyww' and id=1000;
#2.如果有or相连，没有优化，所有语句从左到右执行，让索引失去意义
select count(*) from s1 where id=1000 or email='xboyww1000@oldboy';- 联合索引：遵循最左前缀原则 index(字段1，字段2，...)
drop index index_id on s1;
drop index index_name on s1;
create index union_index on s1(first_name,last_name);
select count(*) from s1 where first_name = "王6" and last_name="文6" #命中索引
select count(*) from s1 where last_name="文6" and first_name = "王6" #命中索引
select count(*) from s1 where last_name="文6" #不能命中索引
select count(*) from s1 where first_name = "王6" and gender="man"; #命中索引
select count(*) from s1 where first_name = "王6" and gender="man" and name="xboyww" #命中索引
#最左前缀原则：被标记成MUL这个字段，必须存在在搜索条件中，就命中索引
first_name + ...(必须该字段存在) 联合索引会更加精确的命中想要的数据，数据结构更合理；- 其他
#数据类型不匹配，不能命中索引
select count(*) from s1 where first_name = 100;
#使用了函数不能命中
select count(*) from s1 where reverse(first_name) = "6文";
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
