数据库的组成部分

名称	含义
table	表
Views	视图
Stored Procs	存储过程
Functions	自定义存储函数
Triggers	触发器
Events	时间触发器

查询优化

存储大量数据

-- 创建表
CREATE TABLE `user`(
	id INT,
	username VARCHAR(32),
	`password` VARCHAR(32),
	sex VARCHAR(6),
	email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert()  
BEGIN
    DECLARE i INT DEFAULT 1; 
	START TRANSACTION; -- 开启事务
    WHILE(i<=4000000)DO
        INSERT INTO `user` VALUES(i,CONCAT('杨晨',i),MD5(i),'男',CONCAT('杨晨',i,'@gmail.com'));
        SET i=i+1;
    END WHILE;
	COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 查看存储过程
SHOW CREATE PROCEDURE auto_insert;
-- 调用存储过程
CALL auto_insert();

慢查询日志慢查询日志将会对操作时间过长的sql进行记录

-- 开启慢查询日志
set global slow_query_log=on;
-- 会话设置查询时间超过2秒将被记录日志
set long_query_time=1;
-- 查看慢查询时间配置
show variables like '%slow_query_log%';
-- 查看慢查询日志开启情况
show variables like '%long_query_time%';
-- 指定id查询
SELECT * FROM USER WHERE id = 3999999;
-- 指定name精准匹配
SELECT * FROM USER WHERE `username` = '杨晨3888888';
-- 指定email模糊匹配
SELECT * FROM USER WHERE email LIKE '杨晨3777777%';

添加索引优化查询速度

索引是一个整理排序的过程 索引的原理(BTree,B+Tree)

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的。
位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
INNODB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。INNODB 存储引擎中默认每个页的大小为 16KB。
INNODB 引擎将若干个地址连接磁盘块，以此来达到页的大小 16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。 BTree

BTREE 数据结构:

每个节点中不仅包含 KEY 值，还有数据。会增加查询数据时磁盘的 IO 次数。

B+Tree 数据结构

  非叶子节点只存储 KEY 值。
  所有数据存储在叶子节点。
  所有叶子节点之间都有连接指针。

B+Tree 好处

  提高查询速度。
  减少磁盘的 IO 次数。
  树型结构较小。

索引的创建原则

字段内数据的辨识度不能低于70%
在经常需要搜索的列上建索引，这样会大大加快查找速度
在经常需要连接的列上建索引(外键字段)，可以加快连接的速度。
在经常需要排序的列上建索引，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。
注意:索引的建立和维护都是需要耗时的,创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率

索引的优势

提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

修改表指定索引

①添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

ALTER TABLE 表名 ADD PRIMARY KEY(字段); -- 索引名：primary

ALTER TABLE user ADD PRIMARY KEY(id);

②添加唯一索引（除了NULL外，NULL可能会出现多次）

ALTER TABLE 表名 ADD UNIQUE (字段); -- 索引名：字段名

ALTER TABLE USER ADD UNIQUE (username);

③添加普通索引，索引值可以出现多次。

ALTER TABLE 表名 ADD INDEX (字段); -- 索引名：字段名

ALTER TABLE USER ADD INDEX (email);

创建索引

-- 创建普通索引
CREATE INDEX 索引名 ON 表名(列名);
CREATE INDEX index_name ON `user`(`password`);
-- 创建唯一索引
CREATE UNIQUE INDEX  索引名 ON 表名(列名);
CREATE UNIQUE INDEX index_name ON `user`(`password`);
-- 创建普通组合索引
CREATE INDEX 索引号 ON 表名(列名1,列名2);
CREATE INDEX index_name ON `user`(id,username);
-- 创建唯一组合索引
CREATE UNIQUE INDEX  索引号 ON 表名(列名1,列名2);
CREATE UNIQUE INDEX index_name ON `user`(id,username);

创建表时指定索引

CREATE TABLE `user`(
      id INT,
      username VARCHAR(20),
      email VARCHAR(20),
	
      PRIMARY KEY(id),
      UNIQUE(email),
      INDEX(username)
);

索引的失效情况

模糊查询的时候,若在索引列上的左边加上了"%" ,索引失效
WHERE username LIKE 'jack123%' -- 索引有效

WHERE username LIKE '%jack123%' -- 索引失效
使用OR查询,其中一个条件上没有索引也会降低查询效率

SELECT * FROM USER WHERE id = 123456 OR sex = 'female';
在索引列上进行计算的,索引失效

SELECT * FROM USER WHERE id + 1 = 1234567;
使用 !=、 <> 、IS NOT NULL、NOT 等也会失效尽量不要出现NULL
组合索引要注意最左匹配原则(必须按照顺序出现),否则也会失效
- 例如: 给USER表添加了组合索引 (id,NAME,email).就相当于添加了三个索引
  - id
  - id,NAME
  - id,NAME,email
- 若我们查询的时候
  
  SELECT * FROM USER WHERE id = 1;-- 有效
  
  SELECT * FROM USER WHERE id = 1 AND NAME LIKE 'jack%';-- 有效
  
  SELECT * FROM USER WHERE id = 1 AND NAME LIKE 'jack%' AND email LIKE '%jack';-- 有效
  
  SELECT * FROM USER WHERE NAME LIKE 'jack%';-- 无效
  
  SELECT * FROM USER WHERE id = 1 OR email LIKE 'jack%';--无效
  
  SELECT * FROM USER WHERE NAME LIKE 'jack%' AND email LIKE 'jack%';-- 无效
- 类型的自动提升导致索引失效 : 尽量保证类型一致(比如int转varcher)

sql-查询优化

数据库的组成部分

查询优化