#yyds干货盘点#简单化MySQL优化小妙招

@TOC

目录

?博主介绍

::: hljs-center

? 个人主页:苏州凯捷智能科技有限公司
?作者介绍:苏州凯捷智能科技有限公司创始人,主要目前与华为合作5G工业机器人领域开发,2D、3D视觉项目开发,政府项目投标开发。
? 有任何问题欢迎私信,看到会及时回复
微信公众号:苏州程序大白
?想加入技术交流群的可以数据库管理系统加我好mysql数据库命令大全友,群里会分享学习资mysql增删改查语句

:数据库设计::

SQL语句执行顺序

设置数据漫游大小写不敏感

  • 查看大小写是否敏感:show variables like '%lower_case_table_names%'; windows 系统默认大小写不敏感,但是 linux 系统是大小写敏感的。

  • 设置大数据漫游是什么意思小写不敏感:在 my.cn数据库是什么f 这个配置文件 [mysqld] 中加入 lower_case_table_nam数据分析师es = 1 ,然后重启服务器。
属性设置 描述
0 大小写敏感
1 大小写不敏感。创建的表,数据库都mysql怎么读是以小写形式存放在磁盘上,对于 sql 语句都是转换为小写数据库查询语句对表和 DB 进行查找
2 创建的数据库系统表和 DB 依据语句上格式存放,凡是查找都是转换为小写进行

<font color=#d32f2f size=5 face= "楷体"&g数据库管理系统t; 注意:在设置属性为大小写不数据库原理及应用敏感前就需要将原来的数据库和表转换为小写,否则会找不到数据库名。 </font>​

M数据透视表ySql 的用户和权限管理

<font colmysqlor=#03a9f4 size=5 face= "楷体"> 用户管理: </font>mysql创建表;​

-- 创建用户
create user ahzoo identified by '123456'; 
-- 查看用户和权限的相关信息
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user
-- 修改当前用户密码
set password =password('1234');
-- 修改其他用户密码
update mysql.user set password=password('123456') where user='ouo';
-- 所有通过user表的操作,都必须使用下面命令才能生效
flush privileges;
-- 修改用户名
update mysql.user set user='ahzoo' where user='ouo';
flush privileges;
-- 删除用户
drop user ouo;
-- 注意:删除用户时,不建议使用下面命令进行删除,因为系统会有残留信息保留
delete from user where user='ouo' 
flush privileges;

&数据透视表lt;font color=#03a9f4 size=5 face= "楷体"> 权限管理: </font>​

授予权限

grant 权限 1,权限 2,…权限 n on 数据库名称.表名称 to 用户名@用户地址 identified by '密码';
-- 授予数据库下所有表,所有权限
grant all privileges on testDB.* to ahzoo@localhost identified by '123456';
-- 授予所有库、表增删改查权限
grant select,insert,delete,drop on *.* to ahzoo@localhost identified by '123456';
-- 对网络用户授权;@'%' 表示对非本地主机用户授权,不包括localhost
grant all privileges on *.* to ouo@'%' identified by '123456'
-- 查看权限
show grants;

取消权限

revoke [权限 1,权限 2,…权限 n] on 库名.表名 from 用户名@用户地址;
revoke all privileges on testDB.* from ahzoo@localhost;

索引优化

数据恢复软件免费版数据之外,数数据恢复软件免费版据库系统还维护着满足特定查找算法的数数据库是什么据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找数据库是什么算法数据透视表。这种数据结构,就是索引。下数据库设计图就是一种可能的索引方式示例:

                                            #yyds干货盘点#简单化MySQL优化小妙招
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。为了加快 Col2 的查找,可以维护一个 右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针,这样就可以运数据分析师用 二叉查找在一定的数据库设计复杂度内获取到mysql面试题相应数据,从而快速的检索出符合条件的记录。 一般来说索引本身也很大数据恢复软件免费版,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。

<fo数据库nt color=#数据03a9f4 size=5 face= "楷体&mysql增删改查语句quot;> 索引优势: </font>​

  • 提高数据检索的效率,降低数据库的IO成本。

  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

<font color=#03a9f4 size=5 face= "楷体"> 索引劣势: </font>​

  • 虽然索引大大提高了查询速mysql数据库度,数据库系统工程师同时却会降低更新表的mysql安装配置教程速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新数据库设计添加了索引列的字段,都会调整因为 更新所带来的键值变化后的索引信息。

  • 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记mysql数据库命令大全录,所以索引列也是要占用空间的。

MySQL 索引

Btree

<font color=#03a9f4 size=5 face= &quo数据库技术t;楷体"> MySQL 使用的是 Btree 索引: </font&数据恢复gt;​

                                            #yyds干货盘点#简单化MySQL优化小妙招

一颗 b 树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝数据透视表色所示)和指针(黄色所示),如磁盘块数据库是什么 1 包含数据项 17 和 35,包含指针 Pmysql面试题1、P2、P3,P1 表示小于 17 的磁盘块,P2数据库技术 表示在 17 和 35 之间的磁盘块,P3 表示大于 35 的磁盘块。

mysql数据库实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如 17、35 并不真聚簇索引实存在于数据表中。

<font color=#03a9f4 size=5 face= "楷体"> 查找过程: </font>​

如果要查找数据项 29,那么首先会把磁盘块 1 由磁盘加载到内存,数据此时发生一次 IO,在内存中用二分查找确定 29在 17 和 35mysql安装配置教程 之间,锁定磁盘块 1 的 P2 指针,内存时间因为非常短(相比磁盘的 IO)可以忽略不计,通过磁盘块 1的 P2 指针mysql数据库命令大全的磁盘地址把磁盘块 3 由磁盘加载到内存,发生第二次 IO,29 在 26 和 30 之间,锁定磁盘数据恢复软件免费版块 3 的 P2 指针,通过指针加载磁盘块 8 到内存,发生第三次数据库管理系统 IO,同时内存中做二分查找找到 29,结束查询,总计三次 IO。

真实的情况是,3 层的 b+树可以表示上百万的数据,如果上百万的数据查找只需要三次 IO,性能提高将是巨大的,如果没有索引,mysql数据库命令大全每个数据项都要发生一次 IO,那么总共需要百数据库是什么万次的 IO,显然成本非常非常高。

B+tree


                                            #yyds干货盘点#简单化MySQL优化小妙招
<font color=#03a9f4 size=5 face= "mysql楷体"> B+Tree 与 B-Tree 的区别: </font>​

1、B-树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任数据结构何信息;B+树的非叶mysql面试题子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。

2、在 B-树中,越靠近根节点的记录查找时间越快,数据库管理系统只要找到关键字即可确定记数据库系统工程师录的存在;而 B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度数据分析师看 B- 树的性能好像要比 B+树好,而在mysql数据库命令大全实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比 B-树多,数据库管理系统树高比 B-树小,这样带来的好处是减少磁盘访问次数。尽mysql创建表管 B+树找到一个记录所需的比较次mysql数据库命令大全数要比 B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,数据库查询语句而且 B+树的叶子节点使mysql密码忘记了怎么办用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用 B+树的缘故。

<font color=#03a9f4 size=5 face= "楷体"> 为什么 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引: </font>​

  • B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容数据库管理系统纳的关键字mysql安装配置教程数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

  • B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点,而只mysql是叶子结点中关键字的索引。所以任何数据漫游关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,数据漫游是什么意思导致每一个数据的查询效率相当。

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储 在一起。
如下图,左侧的索引就是聚簇索引,因为数据行在磁盘的排列和索引排序保持一致。

                                            #yyds干货盘点#简单化MySQL优化小妙招

  • 聚簇索引的好处:

按照聚簇索引排列顺序,查询显示一定范围mysql数据库数据数据库系统工程师的时候,由于数据都是紧密相连,数据库mysql怎么读不不用从多 个数据块中提取数据,所以节省了大量的 io 操作。

  • 聚簇索引的限制:

对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引,而 Myisam 并不支持聚簇索引。 由于数据物理存储排序方式只能有一种,所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是 该表的主键。 为了充分利用聚簇索引的聚簇的特性,所以 innodb 表的主键列尽量选用有序的顺序 id,而不建议用 无序的 id,比如 uuid 这种

Mysql 索引分类

-- 创建
CREATE [UNIQUE] INDEX [indexName] ON table_name(column))
-- 删除 
DROP INDEX [indexName] ON tableName;
-- 查看 
SHOW INDEX FROM tableName;
-- 使用Alter命令:
-- 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为 NULL:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) 
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
-- 添加普通索引,索引值可出现多次:
ALTER TABLE tbl_name ADD INDEX index_name (column_list) 
--该语句指定了索引为 FULLTEXT ,用于全文索引:
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

单值索引

<font color=#03a9f4 size=5 face= "楷体"> 即一个索引只包含单个列数据恢复软件免费版,一个表可以有多个单列索引。 </font>​

-- 在表创建时直接创建索引
CREATE TABLE customer (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200), 
  PRIMARY KEY(id),  
  KEY (customer_name)
);
-- 单独创建索引:
CREATE INDEX idx_customer_name ON customer(customer_name);

数据恢复一索引

<font color=#03a9f4 si数据库系统的核心是ze=5 face= "楷体"> 索引列的值必须唯一,但允许有空值。 </font>​

-- 随表一起创建:
CREATE TABLE customer (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name
    VARCHAR(200), 
  PRIMARY KEY(id), 
  KEY (customer_name), 
  UNIQUE (customer_no)
);
-- 单独建唯一索引:
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no

主键索引

<font color=#03a9f4 size=5 face= "楷体"> 设定为mysql数据库命令大全主键后数据库会自动建立索引,innodb为聚簇索引。 </font>​

-- 随表创建
CREATE TABLE customer (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name
    VARCHAR(200), 
  PRIMARY KEY(id)
);
-- 单独建主键索引:
ALTER TABLE customer add PRIMARY KEY customer(customer_no)
-- 删除建主键索引:
ALTER TABLE customer drop PRIMARY

复合索引

<font color=#03a9f4 size=5 face= "楷体"> 即一个索引数据恢复包含多个列。 </font>​

-- 随表一起建索引:
CREATE TABLE customer (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name
    VARCHAR(200), 
  PRIMARY KEY(id), 
  KEY (customer_name), 
  UNIQUE (customer_name), 
  KEY (customer_no,customer_name)
);
-- 单独建索引:
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

索引数据库技术优化

  • 最佳左前缀法则

使用复合mysql安装索引时,需遵循最左前缀法则(查询从索数据库引的最左前列开始并且不跳过索引中的列)。即过滤条件要使用索引必须按照索引建立时的数据顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。

  • 不要在索引列上做任何计数据分析师

索引列上做【计算、函数、(自动\手动)类型转换】等操作时,会导致索引失效而转向全表扫描。

  • 索引列上不能有范围查询

执行mymysql增删改查语句sql命令时应将可能做范围查询的字段的索引顺序放在最后。

  • 尽量使用覆盖mysql怎么读索引

覆盖索引:SQL 只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查mysql数据库询数据。即查询列和索引列时不要使用 select *…而是使用select a,数据b,c….

1、使用不等于(!= 或者<>)时,有时会无法使用索引会导致全表扫描。

2、字段的 i数据透视表s null 可以用到索引 而 is not null 不会使用索引。

3、不能使用前缀进mysql密码忘记了怎么办行模糊匹配:

... like '%a%'  √... like '%a'  √... like 'a%'  

使用 union all 或者 uniomysql面试题n 来替代or示例:

假设ab数据库管理系统c为索引

-- 索引被使用:
where a = 3;
where a = 3 and b = 5;
where a = 3 and b = 5 and c = 4;
-- 索引未被使用:
where a <> 3;
where abs(a) =3;
where b = 3;
where b = 3 and c = 4;
where c = 4;
-- 使用到a索引,但是未使用b、c索引
where a = 3 and c = 5;
where a = 3 and b > 4 and c = 5;
where a is null and b is not null;

子查询优化

在范围判断时,尽量不要使用 not innot exists,使用 left join on xxx i

排序分组优化

  • 无过滤,不索引

where,limt 都相当于一种过滤条件,所以才能使用上索引。

  • 顺序错,必排序

where 两侧列的顺序可以变数据分析师换,效果相同,但是 order by 列的顺序不能随便变换。

  • 方向反,必排序

如果可以用上索引的字段都使用正序或者逆序,实际上是没有任何影响的,数据库原理及应用无非将结果集调换顺序。

-- 两个排序方式都是desc:
select * from mytest where name='ahzoo' order by deptid desc, name desc

数据库系统工程师果排序的字段,顺序有差异,就需要将差异的数据库系统部分,进行一次倒置顺序,因此还是需要手动排序的。

-- 两个排序方式相反,一个是降序一个是升序
select * from mytest where name='ahzoo' order by deptid desc, name asc