MySQL笔记

SQL注入的防范：login.php?user=admin&pwd=admin or 1=1

① 接收整型参数，使用intval()函数处理

② 接收字符串参数，使用addslashes() 过滤所有的引号和反斜线

③ 转义或者过滤一些特殊字符，如%

④ 做好数据备份

 

MySQL存储过程:

• 为以后的使用而保存的一条或多条SQL语句的集合，存储过程就是有业务逻辑和流程的集合。
• 可以在存储过程中创建表、更新数据、删除等。

 

整数类型 tinyint、smallint、mediumint、int、bigint：

• 属性 unsigned 设置是否为正数
• int(11)，指定宽度，不会限制值的合法范围，只会影响显示字符的个数。
• 设置了zerofill之后，存储“12”，int(3)的结果是“012”，int(5)的结果是“00012”

 

实数类型 float、double、decimal：

• decimal可以存储比bigint还大的整数，可以用于存储精确的小数；
• float和double类型支持使用标准的浮点进行近似计算。

 

字符串类型 varchar、char、text、blob：

• varchar类型用于存储可变长字符串，比定长类型更节省空间。
• char是定长的，根据定义的字符串长度分配足够的空间，会根据需要采用空格进行填充以方便比较。
• char适合存储很短的字符串，或者所有值都接近同一个长度。
• 对于经常变更的数据，char比varchar更好，char不容易产生碎片。对于非常短的列，char比varchar在存储空间上更有效率。
• varchar和char的长度，如果存储内容超出指定长度，都会被截断。
• 尽量避免使用BLOB/TEXT类型，查询会使用临时表，导致严重的性能开销。
• TEXT类型不能有默认值。

 

枚举类型（ENUM）

• 把不重复的数据存储为一个预定义的集合。
• 有时可以使用ENUM代替常用的字符串类型。
• ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。
• ENUM在内部存储时，其实存的是整数。
• 尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。
• 排序是按照内部存储的整数

 

日期和时间类型：

• 尽量使用TImestamp，空间效率高于dateTIme，
• 用整数保存时间戳通常不方便处理。
• 如果需要存储微秒，可以使用bigint存储。

 

使用JOIN实现连接查询：

JOIN / INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

UNION运算符用于组合两个或多个SELECT语句的结果集。

 

使用UNION来合并多个查询的记录会默认过滤掉重复的记录。

由于t1表和t2表都有(2, b)、(3, c)这两条记录，所以合并后的结果集就把他俩去重了。

如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询：

合并查询会把各个查询的结果汇总到一块，我们只能对最后总的结果集进行排序，而不能分别对各个查询进行排序。

由于最后的结果集展示的列名是第一个查询中给定的列名，所以ORDER BY子句中指定的排序列也必须是第一个查询中给定的列名，

 

常用 MySQL 函数，如：now()、md5()、concat()、uuid()等

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

定时清理MyISAM：optimize table 表名

 

MySQL的关联update语句：

(1) UPDATE A,B set A.c1=B.c1,A.c2=B.c2 WHERE A.id=B.id

(2) UPDATE A INNER JOIN B ON A.id=B.id SET A.c1=B.c1,A.c2=B.c2 WHERE ...

 

关联查询：

(1)交叉连接 CROSS JOIN，没有任何关联条件，结果是笛卡尔积:

SELECT * FROM A,B

(2)内连接 INNER JOIN，可以缩写为JOIN：

SELECT * FROM A,B WHERE A.id=B.id 或者

SELECT * FROM A INNER JOIN B ON A.id=B.id

内连接分为三类：

等值连接：ON A.id=B.id

不等值连接：ON A.id > B.id

自连接：SELCT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

(3)外连接：

LEFT JOIN 左表数据全部显示；

RIGHT JOIN 右表数据全部显示。

(4)联合查询 UNION / UNION ALL：

把多个结果集集中在一起，列数要相等，UNION相同的记录行会合并，UNION ALL相同的行不会合并。

SELECT * FROM A UNION SELECT * FROM B UNION …

(5)全连接 FULL JOIN：MySQL不支持全连接，但可以如下实现：

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id

 

 

1. MySQL导出数据：

mysqldump -uroot -p123456 test users --where="score>=60" > a.sql

2. MySQL获取group by 的记录总数：

SELECT COUNT(DISTINCT field) FROM tbname

3. like查询：

SELECT * FROM tbname WHERE name like 'java___';（java后面跟3个字符）

4. 分组：

group by 字段...

having 条件；

总数：count()；

总和：sum()；

平均值：avg()；

最大值：max()；

最小值：min();

5. 查看MySQL执行效率高低：

set @@profiling=1;[SQL语句]; show profiles;

6. 使用explain，返回的信息：

select_type[查询的类型],table,type[表的连接类型],possible_keys[可能使用的索引],key[实际使用的索引],key_len[索引字段的长度],ref,rows[扫描的行数],Extra[执行情况的描述和说明].

 

【数据库和表的操作】

创建数据库：CREATE DATABASE IF NOT EXISTS shop;

查看数据库：SHOW DATABASES;

删除数据库：DROP DATABASES IF EXISTS shop;

查看当前数据库中的数据表：SHOW TABLES;

查看表结构： DESC item;

查看注释标识：SHOW CREATE TABLE item;

删除数据表：DROP TABLE IF EXISTS item;

重命名表：RENAME TABLE tb_name TO new_tb_name, tb_name2 TO new_tb_name2, ⋯

配置文件中的变量：show variables

 

【ALTER操作字段】

添加字段：ALTER TABLE tbname ADD username varchar(5) NOT NULL DEFAULT '' COMMENT 'beizhu';

修改字段的信息：alter table tbname MODIFY username varchar(20) not null default '' comment '备注' [after name | first] ;

替换字段为新值：alter table tbname CHANGE username userinfo int(11) NOT NULL DEFAULT 0 COMMENT '备注';

删除字段：ALTER TABLE tbname DROP COLUMN userinfo; [或者 DROP field userinfo]

 

【ALTER操作索引】

添加主键索引：ALTER TABLE tbname ADD PRIMARY KEY s1(column)

添加唯一索引：ALTER TABLE tbname ADD UNIQUE s2(column)

添加普通索引：ALTER TABLE tbname ADD [INDEX|KEY] idx_name (column1,column2,column3)

添加全文索引：ALTER TABLE tbname ADD FULLTEXT s3(column)

删除索引： ALTER TABLE tbname DROP index s1;

 

【增删改查操作】

插入数据： INSERT INTO user(id,name) VALUES (1,’renxing’);

修改数据： UPDATE user SET name=’wahaha’ WHERE id=1;

批量修改数据：UPDATE tbName SET name = CASE myid WHEN 2 THEN 'Hello' WHEN 3 THEN 'world' END WHERE myid IN (2,3,8) .

删除数据： DELETE FROM user WHERE id=1;

清空表： TRUNCATE user;

内连接INNER JOIN：返回与连接条件相匹配的数据行。SELECT * FROM a INNER JOIN b ON a.id=b.id;

左外连接LEFT JOIN，左表的数据全部显示：SELECT * FROM a LEFT JOIN b ON a.id=b.id;

右外连接RIGHT JOIN，右表的数据全部显示：SELECT * FROM a RIGHT JOIN b ON a.id=b.id;

 

【系统相关的查询】

show variables like ‘%slow’ 慢查询日志是否打开

show variables like ‘max_connections’ 查看MySQL允许的最大连接数

show global status 可以得到系统当前状态。com_xxx表示xxx语句执行的次数，例如com_select.

show global status like ‘%slow’ 查看慢查询的条数

show status like 'Handler_read%' 查看索引的使用情况：

show processlist; 显示当前所有连接的工作状态

 

【MySQL中的内置系统函数】

[字符串函数]

CONCAT(S1,S2....SN)；

INSERT(str, x, y, insert)；

LOWER(str) UPPER(str)；

LEFT(str, x) RIGHT(str, x)；

LPAD(str, n, pad), RPAD(str,n,pad)；

TRIM (str) LTRIM(str) Rtrim(str);

replace(str, a,b);

strcmp(s1, s2);substring(str, x, y)。

 

[数值函数]

ABS(x)；ceil(x)；floor(x)；mod(x, y)；rand()；round(x,y)；truncate(x,y)。

 

[日期函数]

curdate()；curtime()；now()；unix_timestamp(date)；from_unixtime；week()；year()；hour()；minute()。

 

[流程控制函数]

if(value, t f)；

ifnull(value1, value2)；

case when [value1] then[result1]...else[default]end；

case when …then。

 

[其它函数]

database()；version()；user()；inet_aton(ip)；inet_ntoa()；password()；md5()。

 

【事务 transaction】

只有InnoDB和BDB这两种存储引擎支持事务。

开始事务：START TRANSACTION 或者 BEGIN

提交事务：COMMIT

回滚事务：ROLLBACK

 

【存储过程】

创建存储过程：CREATE PROCEDURE

执行存储过程：CALL

查看存储过程的创建语法： SHOW CREATE PROCEDURE

查看存储过程的特性：SHOW PROCEDURE STATUS

删除存储过程：DROP PROCEDURE [IF EXISTS] proc_name

 

【触发器】

创建触发器：CREATE TRIGGER

查看触发器的相关信息：SHOW TRIGGERS

删除触发器：DROP TRIGGER trigger_name

 

【视图】

创建视图：CREATE VIEW

修改视图：ALTER VIEW

删除视图: DROP VIEW

查看创建视图的语法： SHOW CREATE VIEW

 

【游标】

声明游标：DECLARE cursor_name CURSOR FOR select_statement

打开游标：OPEN cursor_name

从游标结果集中获取数据行。FETCH cursor_name INTO var_name [,var_name] ...

关闭游标：CLOSE 语句。 CLOSE cursor_name

 

【MySQL数据库优化的考虑点】

一、数据表数据类型优化

1. tinyint、smallint、int、bigint 考虑空间和范围的问题

2.char固定长度、varchar 可变长度

3.enum 的使用，底层存储的是数值

4.IP地址的存储 使用ip2long()函数，将IP地址转为整型

二、索引的优化

索引的创建原则：索引不是越多越好，在合适的字段上创建合适的索引

复合索引的前缀原则：like查询%问题、全表扫描优化、or条件索引使用情况、字符串类型索引失效的问题

三、SQL语句的优化

1.优化查询过程中的数据访问：使用Limit、返回列不用*

2.优化长难句的查询语句：变复杂为简单、切分查询、分解关联查询、

四、优化特定类型的查询语句

优化count()、优化关联查询、优化子查询、优化Group by 和 distinct、优化limit 和 union。

五、数据表结构设计的优化

1.分区操作：通过特定的策略对数据表进行物理拆分（partition by）

2.分库分表：水平拆分、垂直拆分

六、数据库架构的优化：主从复制、读写分离、双主热备、负载均衡。

七、MySQL的负载均衡：通过LVS的三种基本模式实现负载均衡；MyCat数据库中间件实现负载均衡。

 

【MySQL查询优化的技术点】

• 查找分析查询速度慢的原因
• 优化查询过程中的数据访问
• 优化长难的查询语句
• 优化特定类型的查询语句

 

查找分析查询速度慢的原因：

(1)记录慢查询日志；

(2)可以使用pt-query-digest工具分析；

(3)使用show profile：

① set profiling=1开启，执行的所有语句会检测消耗的时间并存到临时表中

② show profiles

③ show profile for query 临时表ID

(4) 使用show status:会返回一些计数器；使用show global status 查看服务器级别的所有计数

(5) 使用show processlist 观察是否有大量线程处于不正常的状态或者特征。

(6) 使用explain分析单条SQL语句，如果发现查询需要扫描大量的数据但是只返回少数的行，就需要加索引优化：

 

优化查询过程中的数据访问：

访问数据太多会导致查询性能下降；

确定应用程序是否在检索大量超过需要的数据，可能是太多的行或者列；

确认MySQL服务器是否在分析大量不必要的数据行。

重复查询相同的数据，可以使用缓存

改变数据库和表的结构，适当修改表范式(例如适当的冗余)

 

优化长难的查询语句：

问题：一个复杂的查询还是多个简单的查询？

MySQL内部每秒可以扫描内存中上百万行的数据，相比之下相应数据给客户端会比较慢

所以尽可能少的使用查询，但是有时候将一个大的查询分解为多个小的查询是很有必要的

切分查询：可以将一个大的查询分为多个小的相同的查询（一次性删除1000万条记录比一次删除1万然后暂停一会儿共1000次的方案更加损耗服务器开销）

分解关联查询：将一条关联语句拆分成多条SQL执行，让缓存的效率更高

 

优化特定类型的查询语句：

count(*) 的 * 会忽略所有的列，直接统计所有行数，因此不要使用count(列名)

优化关联查询：确定ON或者USING子句的列上有索引，确保GROUP BY和ORDER BY中只有一个表中的列，这样才有可能使用索引

如果不需要排序，可以使用ORDER BY NULL，这样就不会再进行文件排序。

优化LIMIT分页：LIMIT偏移量大的时候，查询效率会较低。可以记录上次查询的最大ID，下次查询时根据这个ID加where条件。

UNION ALL的效率比UNION高。

 

MySQL分区表的原理

分区表是一个独立的逻辑表，对用户来说是透明的，底层MySQL会将其分成多个物理子表，每一个分区表都会使用一个独立的表文件。

创建表的时候使用partition by 子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有需要的数据的分区，这样只需要查询数据所在分区即可。

 

分区适用场景：

① 表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据；

② 分区表的数据更易维护，可以对独立的分区进行独立的操作；

③ 分区表的数据可以分布在不同的机器上，从而高效使用资源；

④ 可以使用分区表来避免某些特殊的瓶颈；

⑤ 可以备份和恢复独立的分区。

分区的限制：

① 一个表最多只能有 1024 个分区；

② 5.1版本中，分区表表达式必须是整数， 5.5可以使用列分区；

③ 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来；

④ 分区表中无法使用外键约束；

⑤ 需要对现有表的结构进行修改；

⑥ 所有分区都必须使用相同的存储引擎；

⑦ 分区函数中可以使用的函数和表达式会有一些限制；

⑧ 某些存储引擎不支持分区；

⑨ 对于 MyISAM 的分区表，不能使用 load index into cache；

⑩ 对于 MyISAM 表，使用分区表时需要打开更多的文件描述符。

 

分库分表的原理：

通过一些 HASH算法 或者工具实现将一张数据表垂直或者水平进行物理切分。

适用场景：① 单表记录条数达到百万到千万级别时；② 解决表锁的问题。

 

分表方式：

① 水平分割：

表很大，分割后可以降低在查询时需要读取的数据和索引的页数，同时也降低了索引的层数，提高查询速度。

使用场景：表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用；

需要把数据存放在多个介质上（最新的数据放到不同服务器上，或者做缓存）。

缺点：给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需 UNION 操作；

在许多数据库应用中，这种复杂性会超过它带来的优点，查询时会增加读一个索引层的磁盘次数（从某种意义上来说，会降低效率）。

② 垂直分表：

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。

使用场景：如果一个表中某些列常用，而另外一些列不常用（可以把常用的列单独拆分出来，查询的时候只查询常用的列即可）；

可以使数据行变小，一个数据页能存储更多数据，查询时减少 I/O 次数。

缺点：管理冗余列，查询所有数据需要 JOIN 操作；

整体缺点：有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差；

对于应用层来说，逻辑算法无疑增加开发成本。

 

问题：设定网址的用户数量在千万级，但是活跃用户的数量只有 1%，如何通过优化数据库提高活跃用户的访问速度？

答案：

使用分区：可以使用MySQL的分区，因为MySQL分区可以帮助我们按照一个规则（把活跃用户分在一个区，不活跃的y用户分到另一个区），在进行查询的时候，可以进行操作活跃用户的时候，只操作活跃用户的那个区。

使用分库分表：通过 水平切分 的方式，把活跃用户的数据切分成一个表，不活跃用户的数据放到另外一张表中，查询的时候，只查询活跃用户的数据表即可。

 

延伸：MySQL的复制原理及负载均衡

1）MySQL主从复制工作原理

在 主库 上把 数据更改 记录到 二进制日志（binary log）；

从库 将 主库 的日志复制到自己的 中继日志；

从库 读取 中继日志 中的事件（增、删、改），将其重放到 从库 数据中。

 

2）MySQL主从复制解决的问题

数据分布：随意停止或开始复制，并在不同地理位置分布数据备份。

负载均衡：降低单个服务器的压力。

高可用和故障切换：帮助应用程序避免单点失败（如：现在突然有台服务器挂掉了，这时候也不用害怕，因为有其他的备份机器，有其他正在运行的从机，立马切换过来就可以了，服务时不会受到任何影响的）。

升级测试：可以使用更高版本的MySQL作为 从库（先看看查询方面有没有问题）。

 

【MySQL集群：主从复制】

mysql要做到主从复制，就是主数据库把自己所做的增删改的操作全都记录在日志中，从数据库就根据这份日志上面的操作在自己身上再操作一遍，这样就实现了主从复制；

 

mysql主从的作用：

1、数据热备：作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2、架构的扩展：业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3、读写分离使数据库能支撑更大的并发。如果网站访问量和并发量太大了，少量的数据库服务器是处理不过来的，会造成网站访问慢。数据写入会造成数据表或记录被锁住，锁住的意思就是其他访问线程暂时不能读写要等写入完成才能继续，这样会影响其他用户读取速度。采用主从复制可以让一些服务器专门读，一些专门写可以解决这个问题。

 

 

MySQL优化的基本方法

• 当只要一行数据时使用 LIMIT 1
• 为搜索字段建索引
• 不要 ORDER BY RAND()
• 用啥查啥，不要select *
• 尽量避免在列上运算，这样会导致索引失效
• 使用批量插入语句
• 不要使用rand函数获取多条随机记录
• 尽可能的使用 NOT NULL
• 把IP地址存成 UNSIGNED INT
• 太多的字段垂直分表
• 不要使用count(id),而用count(*)
• 使用order by null 禁用排序
• limit基数较大时使用between：order by id limit 1000,10 优化为：where id between 1000 and 1010 order by id
• 使用explain分析SQL语句

 

MySQL的瓶颈（千万级别数据量，性能会显著降低）：

① 增加MySQL配置中buffer和cache的数值，提高硬件配置

② 使用第三方引擎或者衍生版本

③ 迁移到其他数据库，如PostgreSQL、Oracle

④ 对数据库分区、分表。按日期和取模余数分表较常见，例如按照uid%10的计算方式。

⑤ 使用NoSQL等辅助解决方案，如Memcache、Redis

⑥ 使用数据库连接池技术

 

【存储引擎的选择】

MySQL的存储引擎：MyISAM、InnoDB、Memory、Archive、Blackhole、CSV。

 

InnoDB：

• 大量的读取和大量的更新操作，数据量大，并发量高
• 性能优秀，数据存储在共享表空间，支持高并发
• 支持崩溃后的安全恢复，支持行级锁，支持外键，支持事务。
• 从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区。
• 支持MVCC

 

MyISAM：

• 大量读取，少量更新，数据量小，并发不高，查询速度快
• 支持表级锁，不支持行级锁，不支持事务
• 表数据存储在两个文件 MYD 和 MYI
• 支持全文索引（仅限英文）

 

对比 InnoDB 和 MyISAM：

• InnoDB支持事务，而MyISAM不支持事务
• InnoDB支持行级锁，而MyISAM支持表级锁
• InnoDB支持MVCC（多版本并发控制）, 而MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持
• InnoDB数据存储在共享表空间，MyISAM数据存储在文件中；
• InnoDB支持崩溃后的恢复，MyISAM不支持；
• InnoDB使用了聚簇索引，MyISAM使用了非聚簇索引。

 

为什么MyISAM会比Innodb 的查询速度快？

InnoDB 在做SELECT的时候，要维护的东西比MYISAM引擎多很多：

1）InnoDB 要缓存数据和索引，MyISAM只缓存索引块，这中间还有换进换出的减少

2）innodb寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）InnoDB 还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

 

Memory：如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。数据的处理速度很快但是安全性不高。

Archive：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

 

 

【索引】

1.索引的本质是 数据结构，可以理解为“排好序的快速查找数据结构”

2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。

3.虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。

4.主键与唯一索引的区别：主键 绝对不能有空值，唯一索引 可以有空值。一个表中可以有多个唯一索引，但是主键只能有一个。

5.MySQL索引结构：BTree索引、Hash索引、full-text全文索引、R-Tree索引

 

索引的创建原则：

• WHERE条件和ORDER BY里面用到的字段创建索引
• 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
• 表记录太少则不建议创建索引
• 增删改操作比查询操作大得多的时候不建议创建索引（不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件）

 

索引对性能的影响：

• 大大减少服务器需要扫描的数据量；
• 帮助服务器避免排序和临时表；
• 缺点：大大提高查询速度，降低写的速度、占用磁盘空间。

 

索引的设计原则：最左原则。

假设索引(a,b,c)在如下查询条件下都会生效（没有顺序限制）：

① a=1 and b=1 and c=1 ② a=1 and b=1 ③ a=1 ④ b=1 and a=1 and c=1

如果查 a=1 and c=1 则只会部分索引生效。

 

索引的细节：

• 复合索引遵循最左原则
• like 查询，%不能在前
• column is null 可以使用索引
• 如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引
• 如果or前面的条件中的列有索引，后面的没有，索引都不会被用到
• 如果列的类型是字符串，查询时一定要给值加引号，否则索引会失效

 

从性能上讲，主键索引和普通索引差别在哪里？

• InnoDB使用的是聚簇索引，数据文件和索引文件是同一个文件,将主键组织到一棵B+tree中，而行数据就储存在叶子节点上。查询普通索引其实是先查找叶子节点对应的主键，再使用主键再执行一次B+tree检所操作。
• MyISM使用的是非聚簇索引（.frm、.myd、.myi），节点的结构完全一致只是存储的内容不同而已，由于索引树是独立的，索引指向数据的位置。通过普通索引检索无需访问主键的索引树。因此MyISAM的查询效率高。

 

Hash索引 和 B-tree索引？

Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。

BTree索引最常用，因为不仅可以用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符。

Hash索引只能用于对等比较，例如=操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

 

InnoDB会不会锁表？

如果where条件中的字段都加了索引，则加的是行锁；否则加的是表锁。

在加锁的时候，mysql有读锁和写锁：

读锁（共享锁）：允许其他线程上读锁，但是不允许上写锁;

写锁（排他锁）：不允许其他线程上任何锁。

死锁：指两个事务或者多个事务在同一资源上相互占用，并请求对方所占用的资源，从而造成恶性循环的现象。

乐观锁和悲观锁都是为了解决并发控制问题， 乐观锁可以认为是一种在最后提交的时候检测冲突的手段，而悲观锁则是一种避免冲突的手段。

共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

 

 

【事务】

事务的基本要素ACID：原子性，一致性，隔离性，持久性。

事务的并发问题：

• 脏读：指一个事务读取到了另外一个事务没有提交的数据。
• 不可重复读：指一个事务读取到了另外一个事务中提交的update的数据；两次读取同一数据，得到内容不同。
• 幻读：指一个事务读取到了另外一个事务中提交的insert的数据。

[说明] 不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

 

事务的隔离级别有4个，由低到高依次为Read uncommitted 未提交读 、Read committed 提交读 、Repeatable read 可重复读 、Serializable 串行化 ，

如下表示 √: 可能出现，×: 不会出现。

脏读 不可重复读 幻读

Read uncommitted √ √ √

Read committed × √ √

Repeatable read × × √

Serializable × × ×

--------------------------------------------

Serializable是最高的事务隔离级别，同时代价也最高，性能很低，一般很少使用。在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。

MySQL的默认隔离级别就是Repeatable read，因此有可能会出现幻读。InnoDB通过MVCC(多版本并发控制)解决幻读。

多版本并发控制（MVCC）：

InnoDB的每一行中都冗余了两个字断。一个是行的创建版本，一个是行的删除（过期）版本，版本号（trx_id）随着每次事务的开启自增。

事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

 

 

【MySQL安全设置】

SQL查询的安全方案：

1. 使用预处理语句防止SQL注入：

2. 写入数据库的数据要进行特殊字符的转义

3. 查询错误信息不要反回给用户，将错误记录到日志

注意：PHP端尽量使用 PDO 对数据库进行相关操作，PDO 拥有定义预处理语句很好的支持的方法，MySQLi 也有，但是可扩展性不如 PDO，效率略高于 PDO。

 

MySQL的其他安全设置：

1. 定期做好数据备份

2. 不给查询用户root权限，合理分配权限

3. 关闭远程访问数据库的权限

4. 修改root口令，不用默认口令，使用较复杂的口令

5. 删除多余的用户

6. 限制用户对数据文件的访问权限

 

问题：为什么使用PDO和MySQLi连接数据库会比MySQL函数库更加安全？

• 因为 PDO 和 MySQLi 本身支持预处理；
• 预处理本身就可以防止SQL注入，因此比MySQL函数库更加安全；
• MySQL函数库本身是不支持预处理的。

 

【MySQL的查询缓存】

启用MySQL查询缓存，极大地降低CPU使用率：

query_cache_type 查询缓存类型，有0、1、2三个取值。0则不使用查询缓存。1表示始终使用查询缓存。2表示按需使用查询缓存。

query_cache_type为1时，也可以手动关闭查询缓存：SELECT SQL_NO_CACHE * FROM my_table WHERE condition;

query_cache_type 为2时，按需查询缓存：SELECT SQL_CACHE * FROM my_table WHERE condition;

query_cache_size 默认情况下值为0，表示为查询缓存预留的内存为0，则无法使用查询缓存。

可以使用如下语句临时设置（或者在my.cnf永久设置）

SET GLOBAL query_cache_size = 134217728

注意事项：

• 查询缓存可以看做是SQL文本和查询结果的映射
• 第二次查询的SQL和第一次查询的SQL完全相同，则会使用缓存
• SHOW STATUS LIKE ‘Qcache_hits’; 查看命中次数
• 表的结构或数据发生改变时，查询缓存中的数据不再有效

清理缓存：

FLUSH QUERY CACHE; //清理查询缓存内存碎片

RESET QUERY CACHE; //从查询缓存中移出所有查询

FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容

 

 

【面试题整理】

数据库的三大范式：

• 第一范式：确保每列的原子性(强调的是列的原子性，即列不能够再分成其他几列).
• 第二范式：在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关(一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的部分)
• 第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖)。如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.

 

在record表中随机获取10条数据：

最慢：SELECT * FROM `record` ORDER BY RAND() limit 10

理想：SELECT * FROM `record` WHERE record_id >= RAND()*10000 ORDER BY record_id LIMIT 10;

（上面语句的10000可以提前查出max(record_id)，或根据实际情况设置 ）

 

MySQL字段设为not null的好处：

①查询效率高；②唯一索引会认为 null 和 “” 为两个值。

 

MySQL的Binlog：

MySQL的二进制日志记录了所有的DDL和DML(除了数据查询语句)语句，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

一般来说开启二进制日志大概会有1%的性能损耗。

因为有了数据更新的binlog，所以可以用于实时备份，与master/slave主从复制结合。

 

varchar与char的区别：

char是固定长度的字符类型，分配多少空间，就占用多长空间。

Varchar是可变长度的字符类型，内容有多大就占用多大的空间，能有效节省空间。

由于varchar类型是可变的，所以在数据长度改变的时候，服务器要进行额外的操作，所以效率比char类型低。

 

varchar(50) 中50的含义

答：最多存放50个字符，varchar(50) 和 varchar(200) 存储”hello"所占空间一样，但后者在排序时会消耗更多内存，

因为order by col采用fixed_length计算col长度（memory引擎也一样）。

 

char(4) 和 varchar(4) 的区别：

char定义的是固定长度，长度范围为0-255。varchar是变长长度，长度范围为0-65535。

 

char(10) 能否存进去10个中文文字？

nchar(10)能存10个汉字，char(10)只能存5个汉字，1个汉字占2个字节。

char(10)固定宽度10个字符

nchar(10)支持多种语言的固定宽度10个字符

varchar(10)最多宽度10个字符

nvarchar(10)支持多种语言最多宽度10个字符

 

int(20) 中20的含义

答：是指显示字符的长度，不影响内部存储，只是当定义了ZEROFILL时，前面补多少个 0

 

int(4) 和 int(8) 的区别：

假设插入”123”，使用zerofill后，int(4)其实是“0123”，int(8)是“00000123”。

当没有加zerofill时候两者是没有任何区别的。括号中的数字表示的是显示宽度，而不是限制列内保存值的范围的。