Mysql相关知识

By youfang at: 2024-09-01

Mysql

存储引擎： MyISAM与InnoDB

  1. InnoDB支持事务，MyISAM不支持。

  2. InnoDB支持外键，而MyISAM不支持。

  3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

    MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

  5. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

InnoDB为什么推荐使用自增ID作为主键？
答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

索引

  1. 索引原理
    InnoDB的索引实现说起，InnoDB有两大类索引：
    聚集索引(clustered index)
    普通索引(secondary index)

InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB普通索引的叶子节点存储主键值。
普通索引需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

列查询回表优化
将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

  1. 索引类型
  • NORMAL 普通索引
  • UNIQUE 唯一索引
  • FULLTEXT 全文索引，性能比较差，一般不使用。
  1. 索引方法
  • BTREE
    是默认方法，不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符
  • HASH
    Hash索引只能用于对等比较，”IN”，”=”

    在InnoDB中如果通过主键来访问数据效率是非常高的，而如果是通过非主键来访问数据的话，会先检索到
    InnoDB首先查询Secondary Index的相关信息，通过相应的索引键检索到Leaf Nodes之后，需要再通过Leaf Nodes中存放的主键值再通过主键索引来获取相应的数据行。

常见问题

  1. MySQL单表最大记录数不能超过多少？
    其实，MySQL本身并没有对单表最大记录数进行限制，这个数值取决于你的操作系统对单个文件的限制本身。
    业界流传是500万行。超过500万行就要考虑分表分库了。

SQL SELECT条件判断

  1. 语法一：CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result..n END
    select (CASE WHEN type=1 THEN ‘收入’ WHEN type=2 THEN ‘支出’ ELSE ‘转账’ END) AS ‘类型’ from t_record;
  2. 语法二：IF(expr1,expr2,expr3) expr1为表达式；expr2,expr3为条件值。true返回expr2,否则返回expr3
    select IF(type=1,’收入’,’支出’) AS ‘类型’ from t_record;
  3. 语法三：IFNULL(expr1,expr2);expr1为变量值；expr2为条件值，如果该表达式为NULL，则返回expr2，否则返回expr1
    select IFNULL(FNAME,’未知名称’) as name from tableA;

MySQL8.0 修改远程访问权限

select user, host from mysql.user;

use mysql;

update user set host = ‘%’ where user = ‘root’;

FLUSH PRIVILEGES;

ALTER USER ‘root‘@’%’ IDENTIFIED WITH mysql_native_password BY ‘root123’; #修改加密规则

ALTER USER ‘root‘@’%’ IDENTIFIED BY ‘root123’ PASSWORD EXPIRE NEVER; #更新一下用户的密码

FLUSH PRIVILEGES; #刷新权限