标签： mysql

索引就是目录检索系统

IOT : index cluster table

表要聚集索引排序后存储

sql语句中带where条件的都会用到索引

idx_abc(a,b,c)—–》(a), (a,b), (a,b,c)

select * from tb where a=xxx; 可以用到索引

select * from tb where b=xxx; 不能用到索引

加速join操作

where a.c_id = b.d_id 要求2个数据类型是一样的

如果不一样也用不到索引比如c_id mediumint，d_id int 2个去join不能进行转换成为临时表

char和varchar 大小一样的情况下可以用到索引 但是只要size定义不一样则不能用到索引

join的场景要求两个类型一样的同时要求size一样才能用到索引

排序可以用到索引

idx_abc(a,b,c)

select a,b,c from tb where a=xxx;

索引包含（Innodb的B+tree，索引内包含数据）

一个表有多列索引怎么选择

select * from tb where c1=xxxx and c2=xxx;

c1 varchar(10)

c2 int(10)

idx_c1(c1)

idx_c2(c2)

查询优化里有一个原则：基于成本优化

统计视图

假设成查询成本是１

c1 string 　0.9

c2 int 0.6

mysql认为这个计划都符合要求，就会从上到下取第一个，而我们这里c1比c2靠前所以优先用c1

这就是mysql的 成本优化器

5.7这块可能有变化

trace

《sql server 查询》

基于成本优化内容

R-Tree只在MyISAM里面的地理数据里支持

主建和唯一索引有什么区别吗

主键不允许null，唯一索引允许null

普通索引对null没有要求

使用上会有区别

where col1  =xxx ;

普通索引不能保证数据不重复还会往下面继续查

所以普通索引会比主键和唯一索引多一点io开销 

核心类和高并发尽可能用主键或者唯一索引

（tokudb可以有多个主键多个class index）

Innodb只允许有一个主建

数据是以主键为顺序排序存储的

Innodb表以主建排序存储，如果没有主建了怎么办？

如果没有主建，会选择第一个（创建的顺序）唯一索引，升级为主建

如果这个表连唯一索引也没有怎么办？

自动创建一个6个字节长度的主键

6个字节是很长的成本比较高

idx_

udx_

fdx_

前缀索引貌似是mysql的一个特色

email(64) 用20位就已经可以区别出来了就可以创建

create index idx_email on tb(email(20));

让索引使用最少的空间来减少io

联合索引

(c3,c4)

update tb set c6=xxx where c3=xxx and c4=xxx;

如果创建索引了 就不要再这个列上经常更新

update set email

改索引后需要先改字段然后进行索引排序然后再把数据弄过去，可能涉及到拆页分页

所以要避免更新索引列

全文索引

目前（5.6）不支持中文

5.7后貌似支持中文了

http://www.actionsky.com/docs/archives/163

CJK亚洲文字

create fulltext index idx_c5 on yw(c5);

MySQL 5.6 innodb增加了全文搜索

唯一索引（=）效过最好 ，但是也支持区间（>,between, in）

主键定义删除创建智能用lalter table一般都是在表创建的时候去定义，这个动作很大需要copy表空间，生产环境中基本不去动主键这个事情。

isopen

type

flag

这些列不太适合做索引，基数太少了

比如说type就3个值那也就是30%的比例

mysql扫描不低于30%（打比方的值）都不会走索引

这个索引是无效的

如果标志达到4个或者5个以上索引可能还是可以用到的

选择基数比较大的列做索引，或者重复比较少的。

我们目前的status_id呀，type_id呀,action_id呀之类的这些标志我得找个时间去测试一下效果如何

只有基于唯一索引或主键上等于类的操作才能做到行级锁，这部分非常复杂

gap

next key

idx_ab(a,b)

idx_a(a)

后面会有工具来帮助排查

《基于Innodb最佳实践》

无序主键 rand出来

写入速度很慢 1s只能一两百

顺序索引/s能上万

索引无序写入会造成写入速度降低

update 尽可能不要update索引列

5.7对这块update做了一些合并的维护

字符型的索引，是怎么查找的？

字符串比较btree的字符串比较定位到值在哪

逻辑结构

1. 客户端：php，java…应用程序

2. 通过drive连接到mysql，mysql线程池

3. sql形成MD5/hash的key去查缓存（query cache）是否有这个结果

4. sql分析器，进行优化找到最优执行路径

5. 到存储引擎拿数据

服务器进程

1. 连接层：连接接入，认证

2. SQL层：语法校验

3. 存储层：DBA应该最关注的地方

数据库优化的本质: 

减少IO

把随机IO想办法转成顺序IO

连接层

通信协议

线程

验证

OneSQL就是在连接层把thread-pool做了一个优化，官方的thread-pool也差不多

SQL层：SQL解析，授权，优化器，查询执行，查询日志等等

SQL语句处理过程

连接mysql后查询query cache

query cache是没有经过语法解析的

select * from tb1 where id=1; 和select * from tb1 where Id=1; 

实际生产中是禁掉query cache

query cache是mysql一个性能瓶颈形成全局锁

整体模块结构

Oracle官方有一个去IOE团队推ndb cluster 哈哈哈

日志

1. 错误日志

2. 慢日志（slow_query_log）

3. 二进制日志（binary log）

4. 一般日志（general log）

slow_query_log_file 

set global general_log=1; 

select sleep(3); 

set global general_log=0;

MySQL基于存储引擎的一个解决方案

官方的存储引擎

不支持事物：MyISAM, Memory, Archive(只写，不允许修改删除，日志审计) 

锁的粒度：Innodb,NDB 行级锁

是否支持压缩

innodb 5.1.37引入innodb plugin 这个开支持压缩

myisam 5.6开始支持英语全文搜索

Innodb 5.6 开始支持英语全文搜索

批量写的速度

MyISAM,Memory的批量写入比较快

Innodb的Bulk insert (load data) 在5.7之前没有MyISAM,Memory快

原因是因为不支持批量更新索引

5.7之后改善

恢复到某个点 binlog

TokuDB

带压缩支持高速写入的一个引擎

Innodb/Tokudb压缩比是10:1

innodb表每秒2万

Tokudb可以达到4W

tokudb的insert特别快

percona enterpise

Percona MySQL 5.6 包含了TokuDB

MariaDB 中也含的有Tokudb

engine=Tokudb;

Percona Server TokuDB做为他的一个默认引擎

缺点

Tokudb不适合update多的场景

tokudb表20G

update col2=xxx ;

表由原来的20G表变成40G了

多引擎混用注意join

会形成大量移植表不推荐2种不同引擎join

MySQL不适合做OLAP?

Infrobright/InfiniDB

MySQL 5.1的时间都出现了

group ,order ,count之类聚集函数

OLAP 里的表,很多都是宽表

统计类的库（OLAP）一张表好几百列

针对业务去选择引擎

常用推荐：InnoDB

如果有大数据写入批量读取的操作Tokudb

针对OLAD可以考虑使用InfiniDB/Infrobright

推荐研究: InfiniDB，完全开源

infrobright 分企业版和社区版，社区版只支持load data操作

如果对数据量小，要求速度，无持久化要求：Memory

拒绝选用MyISAM

1. 只能用单个cpu

2. 内存只能用到4G

3. 内存里只有索引（B-tree）

4. 并发能力差

需求分析

创表出来表结构

核心SQL

总结分析: 可以会遇到什么样的性能瓶颈

表

存储过程

触发器

事件

表:字段，　索引(约束)

高并发不允许触发器和存储过程

要背下来：占用空间，每个值的范围

固定小数点类型

Decimal 

salary decimal(8,2)

最大65位

可以用来记录工资

不要用四舍五入

做彩票时: decimal 

每次钱会少个几百块或是多个几百块

交易量5千万左右

int 单位按份来存储

单位: 分

导出乱码

共享表空间转成独立表空间

浮点数

浮点数用二进制表示

小数点后复杂，移位补位，算法符号变法

尽可能减少浮点数出现，运算占开销很大

不能做精确值比较

Floathe（M,D）

    小数点后：0-23位精度，存储占用<=4个字节

DOUBLE（M,D）

    小数点后：24-53位精度，存储占用8个字节

BIT

status&0/10

BIT（M）#M用于表示有多少个二进制位.M可以支持0-64位

对于bit写入会左填充0.如果bit（8）写入b'1001' 查询得到的值 ：00001001

数据类型

数据类型支持属性

    int(4)

    int(4)zerofill 写入数字5，显示：0005

    int(11)auto_increment 每次自增加一

int（1）存100是可以存的 只是有坑，1是宽度

数据溢出 

5.5和5.6有区别 

Set sql_mode = ‘’;
Select cast(0 as UNSIGNED) – 1;

SELECT 9223372036854775807 + 1;
SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;

timestamp占用字节数更少

varchar 

utf8汉字需要占用3个字节

255 byte能存多少个汉字

varchar 低于255 byte需在一个字节 用来记录长度大于255 2个字节

varchar最大长度65535 

如果存的字母就按ascII 2字节 

用记录长度

char 定长的

varchar变长

其他类型能记住存多大就行

Enum

5.6在线表空间定义

减少join

核心sql尽可能不要join

配置好配置文件然后到/usr/local/mysql/bin

执行mysql_multi start 就可以了

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /opt/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = mixed
server-id = 203306
log-bin = /data/mysql/mysql_3306/logs/mybinlog
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

tmpdir = /data/mysql/mysql_3306/tmp

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#端口号为3307的实例特殊配置
[mysqld3307]
port =3307 
server-id=203307
#指定本实例相应版本的basedir和datadir
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3307/data
log-bin = /data/mysql/mysql_3307/logs/mybinlog
socket  = /tmp/mysql_3307.sock
#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
innodb_data_file_path = ibdata1:100M:autoextend
#transaction_isolation = REPEATABLE-READ

#端口号为3306的实例特殊配置
[mysqld3306]
port =3306
server-id=203306
#指定本实例相应版本的basedir和datadir
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
log-bin = /data/mysql/mysql_3306/logs/mybinlog
socket  = /tmp/mysql_3306.sock
#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
#transaction_isolation = REPEATABLE-READ

之前3307启动不起来是因为 手动配置3307的时候修改过3307配置文件里innodb的一些大小，然后用/etc/my.cnf的时候没有按一样大小 就出错了

创建多实例目录并修改权限

vim /data/mysql/mysql_3307/my.cnf

修改配置文件

:%s/3306/3307/g

socket          = /tmp/mysql_3307.sock

要是想启用多版本mysql就修改basedir使用

basedir = /usr/local/mysql

修改socket

执行编译

./scripts/mysql_install_db –user=mysql –datadir=/data/mysql/mysql_3307/data/ –default-file=/data/mysql/mysql_3307/my.cnf

/usr/local/mysql/bin/mysqld_safe –defaults-file=/data/mysql/mysql_3306/my3306.cnf &

3306启动成功了

启动3307

./mysqld_safe –defaults-file=/data/mysql/mysql_3307/my3307.cnf &

启动成功

我发现这个netstat的这个参数这样记比较好记“按你老婆” 啊哈哈哈哈哈

可以看到3306，和3307都监听了

这个时候想要登录到3306

可以使用： mysql -S /tmp/mysql_3306.sock

关闭的话直接用

mysqladmin -S /tmp/mysql_3306.sock shutdown

就可以了

看错误日志

xtrabackup

./scripts/mysql_install_db

mysql -S /tmp/mysql.sock #进入mysql

root@localhost:mysql.sock  08:06:41 [(none)]>show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.03 sec)

root@localhost:mysql.sock  08:07:41 [(none)]>select user,host,password from mysql.user;
+------+-----------------------+----------+
| user | host                  | password |
+------+-----------------------+----------+
| root | localhost             |          |
| root | localhost.localdomain |          |
| root | 127.0.0.1             |          |
| root | ::1                   |          |
|      | localhost             |          |
|      | localhost.localdomain |          |
+------+-----------------------+----------+
6 rows in set (0.00 sec)

root@localhost:mysql.sock  08:08:10 [(none)]>delete from mysql.user where user!='root' or host!='localhost'
    -> ;
Query OK, 5 rows affected (0.02 sec)

root@localhost:mysql.sock  08:09:32 [(none)]>select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
+------+-----------+----------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  08:09:55 [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  08:10:38 [(none)]>select * from db
    -> ;
ERROR 1046 (3D000): No database selected
root@localhost:mysql.sock  08:11:03 [(none)]>use mysql;
Database changed
root@localhost:mysql.sock  08:11:11 [mysql]>select * from db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %    | test    |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| %    | test\_% |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

root@localhost:mysql.sock  08:11:19 [mysql]>truncate table db;
Query OK, 0 rows affected (0.02 sec)

root@localhost:mysql.sock  08:11:42 [mysql]>select * from db;
Empty set (0.00 sec)

root@localhost:mysql.sock  08:11:50 [mysql]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  08:11:56 [mysql]>grant all privileges on *.* to 'root'@'%' identified by 'you-password';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: mysql

Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  08:18:13 [mysql]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

/etc/init.d/mysql stop

 mysqladmin -S /tmp/mysql.sock shutdown -uroot -p

mysqladmin -S /tmp/mysql_3306.sock -uroot -p shutdown

mysqld_safe方式启动

/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &

mysqld方式启动

/usr/local/mysql/bin/mysqld –defaults-file=/etc/my.cnf &

这种方式一定要申明 data和user

5.6初始化ibdata

查询mysql进程

netstat -nalp | grep mysql

ps aux | grep mysql

1.mysql启动配置文件加载路径

/etc/my.cnf
/etc/mysql/my.cnf
/etc/local/mysql/etc/my.cnf
~/.my.cnf

从上到下的顺序 后面的变量覆盖前面的变量

MySQL可以读取到的配置文件

    –defaults-file 指定只读取该配置文件，不在读取其他配置文件

    –defaults-extra-file 指定mysqld在读取完指定的配置文件后，还需要读取用户指定的特殊的配置文件

    –print-defaults输出现在mysqld指定的参数

2.mysql启动的方式

    标准方式centos7 以下

    service mysqld start|stop|restart|status

    /etc/init.d/mysqld start

    mysqld 是 从 support-file/mysql.server copy过来的

    

    

mysql启动的内部流程

常见启动故将及分析过程

环境:centos 6

先下载mysql安装包

打开 http://dev.mysql.com/downloads/mysql/ 

选择 linux – Generic

再选择

下载完毕后,得到安装包 mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

此包不再需要make编译源代码(真是扛扛的),之前的mysql-5.6.4.tar.gz包是源码包,需要先cmake,再make & make install

上传到centos6.4的/software目录下(可以改为你需要的目录,这里只是演示)

ssh登录到centos6.4,开始执行以下命令

1.解压tar包

---

cd /software

tar -xzvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.21

2.添加用户与组

---

groupadd mysql

useradd -r -g mysql mysql

chown -R mysql:mysql mysql-5.6.21

3.安装数据库

---

su mysql

cd mysql-5.6.21/scripts

./mysql_install_db –user=mysql –basedir=/software/mysql-5.6.21 –datadir=/software/mysql-5.6.21/data

exit

4.配置文件

---

cd /software/mysql-5.6.21/support-files

cp my-default.cnf /etc/my.cnf

cp mysql.server /etc/init.d/mysql

vim /etc/init.d/mysql          #若mysql的安装目录是/usr/local/mysql,则可省略此步

修改文件中的两个变更值

basedir=/software/mysql-5.6.21
datadir=/software/mysql-5.6.21/data

5.配置环境变量

---

vim /etc/profile

export MYSQL_HOME="/software/mysql-5.6.21"

export PATH="$PATH:$MYSQL_HOME/bin"

保存退出

. /etc/profile

6.添加自启动服务

---

chkconfig –add mysql

chkconfig mysql on

7.启动mysql

---

service mysql start

8.登录mysql及改密码与配置远程访问

---

mysqladmin -u root password 'your_password'     #修改root用户密码

mysql -u root -p     #登录mysql,需要输入密码

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;     #允许root用户远程访问

mysql>FLUSH PRIVILEGES;     #刷新权限

mysql>exit

存放路径: /opt/mysql/xxx -> /usr/local/mysql

Datadir: /data/mysql/项目_prot/{data,logs,tmp}

配置文件：/etc/my.cnf

手动安装mysql真的遇到了很多问题

Starting MySQL....... ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.loc

尤其是一些参数配置

主要有以下几个文件

/etc/my.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/init.d/mysql

总结一下错误

主要还是配置好各个路径以及权限

mysql涉及到的目录都要chown给mysql:mysql