MySQL基础知识

发表于 | 更新于 | 字数统计3.7k字 | 阅读时长14分钟

MySQL函数

  1. SQL中操作日期和年月的函数：SELECT DAYOFWEEK(‘2013-12-1’), DAYOFMONTH(‘2013-12-1’), WEEKDAY(‘2013-12-1’),DAYOFYEAR(‘2013-12-1’), DAYNAME(‘2013-12-1’), MONTHNAME(‘2013-12-1’), WEEK(‘2013-12-1’)\G;
  2. 字符串操作函数：length(), concat(), concat_ws(), RTRIM(), LTRIM(), RPAD(), LPAD(), LOCATE(), SUBSTRING(), LCASE(), UCASE(), REPEAT();
  3. 时间处理函数：HOUR(), MINUTE(), SECOND(); 时间格式处理函数：SELECT DATE_FORMAT(‘2013-12-01 02:02:00’, ‘%h:%i:%S’) AS sample_time
  4. 日期算数函数：SELECT DATE_ADD(NOW(), INTERVAL 21 DAY); DATE_SUB(), CURDATE(), CURTIME(), NOW(), UNIX_TIMESTAMP(), FROM_UNIXTIME()
  5. SQL事务处理：BEGIN—-ROLLBACK—-COMMIT

  6. PHP与SQL交互：

    $mysqli = mysqli_connect("hostname(localhost)", "uername", "password", "database");
$sql = "SQL语句";
$res = mysqli_query($mysqli, $sql);//执行mysqli_query函数，如果正确执行则返回true，否则返回false；
可以使用mysqli_error($mysqli)输出
mysqli_free_result();//在关闭之前释放空间
mysqli_close($mysqli)；
mysqli_fetch_array()函数获取每一行记录作为一个数组，使用while循环可以遍历一次所有记录

MySQL概念

  1. 主键—唯一标识一条记录，不能有重复的，不允许为空。一个表的主键只能有一个。用来保证数据完整性
  2. 外键—表的外键是另一表的主键, 外键可以有重复的, 可以是空值。一个表可以有多个外键。用来和其他表建立联系用的
  3. 索引—该字段没有重复值，但可以有一个空值。一个表可以有多个唯一索引。提高查询排序的速度

  4. 外键取值规则：空值或参照的主键值。

    (1)插入非空值时，如果主键表中没有这个值，则不能插入。

    (2)更新时，不能改为主键表中没有的值。

    (3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

    (4)更新主键记录时，同样有级联更新和拒绝执行的选择

MySQL常用操作

  1. 建立主键:

    alter table 表名
add constraint PK_字段名--"PK"为主键的缩写，字段名为要在其上创建主键的字段名,'PK_字段名'就为约束名
primary key (字段名) --字段名同上

  2. 增加唯一约束:

    alter table 表名
add constraint UQ_字段名
unique (字段名)

  3. 增加外键约束：

    alter table 表名
add constraint FK_字段名--"FK"为外键的缩写
foreign key (字段名) references 关联的表名(关联的字段名) --注意'关联的表名'和'关联的字段名'

  4. 常见join方式

    • LEFT(OUTER) JOIN
    • RIGHT(OUTER) JOIN
    • INNER JOIN
  5. 建立索引：ALTER TABLE table_name ADD INDEX index_name(‘name’, ‘age’);
  6. 删除索引的2种方法
    • DROP INDEX index_name on table_name;
    • ALTER table table_name drop key index_name; 可以删除被外键约束的索引
  7. select * from mysql.user \G; 查看数据库用户权限
  8. show warnings\G; —- 查看mysql优化过程

MySQL索引的建立规则

  1. 在 where 及 order by 涉及的列上建立索引,避免全表扫描
  2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，尤其是is not null会导致全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
  3. 当索引列有大量数据重复时,也就是选择性极低时，查询可能不会去利用索引，还不如直接全表扫描；如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
  4. 索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引；一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  5. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
  6. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
  7. 避免频繁创建和删除临时表，以减少系统表资源的消耗
  8. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
  9. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定
  10. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
  11. (新版本已经会使用到索引)应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20
  12. in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3
  13. 下面的查询也将导致全表扫描： select id from t where name like ‘%abc%’
  14. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num
  15. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2

  16. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： 

    select id from t where substring(name,1,3)=’abc’   –name以abc开头的id 
select id from t where datediff(day,createdate,’2005-11-30′)=0       –‘2005-11-30’生成的id 
应改为: select id from t where name like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

  17. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

  18. 很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where
    exists(select 1 from b where num=a.num)
  19. 任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
  20. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
  21. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
  22. 尽量避免大事务操作，提高系统并发能力。
  23. 最有可能影响SQL性能的是磁盘和网络吞吐量
  24. 能够采用存储过程实现的对数据库的操作尽量通过存储过程来实现
  25. 不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高
  26. 在不加WHERE限制条件的情况下，COUNT()与COUNT(COL)基本可以认为是等价的；但是在有WHERE限制条件的情况下，COUNT()会比COUNT(COL)快非常多；
  27. COUNT(*)通常是对主键进行索引扫描，而COUNT(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的COL的纪录数。
  28. 两表join时，inner join 和 left join 差不多,都需要优化右表(join字段加索引)，而 right join 需要优化左表

MySQL explain/explain extended含义

  1. id列：MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

  2. select_type 列
    MySQL 把 SELECT 查询分成简单和复杂两种类型，复杂类型又可以分成三个大类：简单子查询、所谓的衍生表（子查询在 FROM 子句里）和 UNION 。SIMPLE：查询中不包含子查询或者UNION；
    PRIMARY:子查询上层；
    SUBQUERY：子查询；
    DERIVED：在FROM列表中包含子查询， MySQL会递归执行这些子查询，把结果放在临时表里

  3. type 列
    MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：| All | index | range | ref | eq_ref | const,system | null

    • ALL：全表扫描，MySQL 从头到尾扫描整张表查找行
    • index：按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。
    • range：以范围的形式扫描索引
    • ref：非唯一性索引访问
    • eq_ref：使用有唯一性索引查找（主键或唯一性索引）；唯一性索引才会出现 eq_ref （非唯一性索引会出现 ref ），因为唯一，所以最多只返回一条记录，找到后无需继续查找，因此比 ref 更快。
    • const：被称为“常量”，这个词不好理解，不过出现 const 的话就表示发生下面两种情况：在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行，只需读取一次表数据便能取得所需的结果，且表数据在分解执行计划时读取。例如：select * from a where id=1\G ；只有唯一性索引才能保证表中最多只有一条记录，只有这样 type 才有可能为 const 。为 id 加普通索引后， type 变为 ref ，改为加唯一或主键索引后， type 便变为 const 了。
    • system：system 是 const 类型的特例，当表只有一行时就会出现 system 。
    • NULL：在优化过程中就已得到结果，不用再访问表或索引。例如：select min(a_id) from a\G

  4. key_len 列：索引字段最大可能使用的长度。

  5. key 列：查询过程中实际使用的索引；可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引
  6. ref 列：指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时，此值也相应的为 NULL 。
  7. rows 列：MySQL 估计的需要扫描的行数

  8. Extra 列：

    • Using index: 此查询使用了覆盖索引(Covering Index)，即通过索引就能返回结果，无需访问表
    • Using where：表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。
    • Using temporary：使用到临时表；MySQL 使用临时表来实现 distinct 操作；常见于排序 order by 和分组查询 group by
    • Using filesort：若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，否则，在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现 Using filesort
    • 当 type 为 rang、 ref 或者 index 的时候才有可能利用索引排序，其它，如 ALL ，都无法通过索引排序，此时若有 order by ，如上例，便会出现 Using filesort 。 但是当扫描的行过多时，MySQL 直接改用全表扫描，再 Using filesort ，不再使用索引。也就是说， MySQL 会根据表中的信息及查询来决定使用任种方式。
    • Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

    • 优化器会在索引存在的情况下，通过符合 RANGE 范围的条数和总数的比例来选择是使用索引还是进行全表遍历,这是为了避免过多的 random disk.

      — 不使用索引(customer_id)
      EXPLAIN SELECT FROM rental WHERE customer_id<103;
      — 使用索引
      EXPLAIN SELECT       FROM rental WHERE customer_id<102;
      — 其中 id < 103 的行数为 2767, id < 102 的行数为 2734

    • “Using Index; Using where” it means that first the index is used to retrieve the records (an actual access to the table is not needed) and then on top of this result set the filtering of the where clause is done. For example: SELECT id FROM test WHERE id > 5;

    • “Using index condition” 的理解是, 首先 mysql server 和 storage engine 是两个组件, server 负责 sql的parse, 执行; storage engine 去真正的 做 数据/index的 读取/写入. 以前是这样: server 命令 storage engine 按 index 把相应的 数据 从 数据表读出, 传给server, server来按 where条件 做选择; 现在 ICP则是在 可能的情况下, 让storage engine 根据index 做判断, 如果不符合 条件 则无须 读 数据表. 这样 节省了disk IO. Link

MySQL数据类型选择

varchar和text

  1. 当超过255的长度之后，使用varchar和text没有本质区别，text没有默认值；推荐使用varchar（10000），毕竟这个还有截断，可以保证字段的最大值可控，如果使用text那么如果code有漏洞很有可能就写入数据库一个很大的内容，会造成风险；

  2. 当varchar大于某些数值的时候，其会自动转换为text，大概规则如下：

    • 大于varchar（255）变为 tinytext
    • 大于varchar（500）变为 text
    • 大于varchar（20000）变为 mediumtext

  3. char的上限为255字节，varchar的上限65535字节，text的上限为65535。char在存储的时候会截断尾部的空格，varchar和text不会。MySQL之char、varchar和text的设计

参考链接

  1. Mysql explain结果解读